Microsoft Excel:

  Таблицы и VBA. Справочник.
  Вопросы и Ответы. Советы. Примеры.
Меню FAQ | Макросы | Функции & Формулы


Rambler's Top100


Counter CO.KZ


    [1] [2]

  1. Как вставить функцию в ячейку ? 2004
  2. Как воспользоваться стандартной функцией (без использования ячейки) ? 2004
  3. Как ввести формулу в ячейку ? 2004
  4. Как ввести сразу несколько различных формул ? 27.02.2007
  5. Как скопировать формулу средствами VBA ? (тиражирование формул средствами VBA) 2004
  6. Как заменить формулы на результаты их вычислений ? 08.03.2007
  7. Как во всех рабочих листах заменить формулы на результаты их вычислений ? 03.05.2015
  8. Как заменить внешние ссылки на значения, которые эти формулы возвращают (во всех листах) ? 08.01.2016
  9. Как найти все ячейки, содержащие нужную формулу ?
    Как получить диапазон содержащий только те ячейки, в которых находится нужная формула ?
    15.09.2007
  10. Как заархивировать формулы нужного рабочего листа, и при необходимости, восстановить эти формулы ? 19.02.2008
  11. Как определить содержит ли ячейка формулу или нет ? 30.09.2006
  12. Как определить содержит ли ячейка формулу массива или нет ? 30.09.2006
  13. Как определить адрес всех ячеек, содержащих формулу массива, исходя из любой ячейки этого массива ? 30.09.2006
  14. Как создать подсказки к пользовательским функциям ? 16.05.2009
  15. Как добавить пользовательскую функцию в любую категорию ? 05.09.2016
  16. Как сделать, чтобы пользовательская функция пересчитывалась вместе с другими формулами ? 16.03.2006
  17. Как управлять пересчётом пользовательской функции ? 02.10.2007
  18. Kак вычислить формулы, в т.ч. и пользовательские функции, только в активном листе или во всех листах сразу ? 29.09.2007
  19. Как программно вычислить формулы, в т.ч. и пользовательские функции ? 16.05.2009
  20. Как определить, когда произошёл пересчёт нужной пользовательской функции ? 04.01.2008
  21. Как определить адрес ячейки, в которой находится пользовательская функция ? 13.02.2007
  22. Как создать комментарий, содержащий время последнего пересчёта нужной пользовательской функции ? 01.07.2007
  23. Как передать пользовательской функции ссылку на диапазон несмежных ячеек ? 02.10.2007
  24. Как в пользовательской функции создать один или несколько необязательных аргументов ? 13.02.2007
  25. Как в пользовательской функции создать произвольное количество аргументов ? 13.02.2007
  26. Как создать функцию, которая будет возвращать несколько значений ? 13.04.2008
  27. Как создать функцию, которая сможет возвращать нужное значение ошибки ? 15.03.2011
  28. Как определить какой из аргументов функции (Excel, VB) является необязательным ? 14.09.2008
  29. Как присвоить имя формуле средствами VBA, чтобы это имя не отображалось в списке имён ? 2004
  30. Как вывести все имена, в т.ч. именованные формулы в отдельный список, в виде имени и адреса ? 18.07.2005
    [1] [2]


  • Ответ :
  • Range("A1").Formula = "=SUM(B5:B10)"
    Вы можете вводить функцию в привычном варианте :
    Range("A1").FormulaLocal = "=СУММ(B5:B10)"
    Если функция находится в другой ячейке, то :
    iFormula = Range("A5").Formula
    Range("A1") = iFormula

    iFormula = Range("A5").FormulaLocal
    Range("A1").FormulaLocal = iFormula
    Предполагается, что :
  • "A1" - это ячейка, куда будет помещена функция
  • "A5" - это ячейка, где находится нужная функция
  • =СУММ(B5:B10) - это функция, в ячейке "A5"
    Примечание :
    При использовании в функциях "" их необходимо заменить на """"
    Если в функциях имеются аргументы разделённые ; то в VBA их необходимо заменить на , но это касается только функций "созданных" без использования FormulaLocal, см. ниже пример ...
  • = "=IF(A5>13,A5,""<>"")"
    = "=ЕСЛИ(A5>13;A5;""<>"")"
    Совет : Если у Вас всё-таки возникли трудности с созданием формулы на VBA, то выберите одну (а лучше две, например A1, A2) пустые ячейки, и введите нужную формулу вручную, а затем просто выполните :
    MsgBox Range("A1").Formula, , "Узрите :"
    Range("A2") = "'" & Range("A1").Formula

  • Ответ :
  • iResult = Application.Sum(Range("B5:B10"))

    iResult = WorksheetFunction.Sum(Range("B5:B10"))

    iResult = Excel.Application.Sum(Range("B5:B10"))

    iResult = Excel.WorksheetFunction.Sum(Range("B5:B10"))

    iResult = Application.WorksheetFunction.Sum(Range("B5:B10"))
    важно При необходимости результат вычислений можно поместить в необходимую ячейку, например :
    [A1] = Application.Sum([B5:B10])

  • Ответ :
  • Range("A1").Value = "=B1+10"
    Range("A1").Formula = "=B1+10"
    Range("A1").Formula = "=B1+C1"
    Range("A1").Formula = "=RC[1]+RC[2]"
    Range("A1").FormulaR1C1 = "=RC[1]+RC[2]"

  • Ответ :
  • Range("F1:H1").FormulaLocal = Array("=СУММ(A1:E1)", "=МИН(A1:E1)", "=МАКС(A1:E1)")
    Range("F1:H1").Formula = Array("=SUM(A1:E1)", "=MIN(A1:E1)", "=MAX(A1:E1)")
    Range("F1:H1").Replace What:="=", Replacement:="=", LoolAt:=xlPart

    Range("A10:A12").FormulaLocal = Application.Transpose _
    (Array("=СУММ(A2:A9)", "=МИН(A2:A9)", "=МАКС(A2:A9)"))
    Range("A10:A12").Formula = Application.Transpose _
    (Array("=SUM(A2:A9)", "=MIN(A2:A9)", "=MAX(A2:A9)"))
    Range("A10:A12").Replace What:="=", Replacement:="=", LoolAt:=xlPart

  • Ответ :
  • Range("B2") = Range("A1").Formula ' Вариант III.
    Range("B2").Value = Range("A1").Formula ' Вариант II.
    Range("B2").Formula = Range("A1").Formula ' Вариант I.
    Предполагается, что :
  • Range("B2") - это ячейка, где будет формула
  • Range("A1") - это исходная ячейка с формулой

    Комментарий : Этот вариант предназначен для тиражирования обычных формул, для того, чтобы он работал и с формулами массива, необходимо использовать :
  • If Not Range("A1").HasArray Then
       Range("B2").Formula = Range("A1").Formula
    Else
       If Range("A1").CurrentArray.Count = 1 Then
          Range("B2").FormulaArray = Range("A1").Formula
       Else
          MsgBox "Эта формула массива, по замыслу создателя, должна занимать более одной ячейки"
       End If
    End If

  • Ответ :

    Вариант I.
  • Range("A1:A10").Value = Range("A1:A10").Value
    Вариант II.
    Range("A1:A10").Copy
    Range("A1:A10").PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    Комментарий : В некоторых случаях, например, при работе с датами, результаты выполнения двух макросов, могут различаться.
  • Ответ :

    Для того, чтобы во всех рабочих листах активной рабочей книги, заменить формулы на значения, которые они возвращают, можно использовать любой из нижеопубликованных вариантов. Обратите внимание на то, что активная книга используется только в качестве примера и может быть заменена на любую открытую книгу. Кроме того, не лишним будет напомнить, что если ячейки + лист защищены, то попытка замены приведёт к ошибке, которой, впрочем, можно избежать.

    Вариант I.
  • Private Sub WSReplaceFormulasOnValues()
        Dim iList As Worksheet
        For Each iList In ActiveWorkbook.Worksheets
            iList.UsedRange = iList.UsedRange.Value
        Next
    End Sub
    Вариант II.
    Private Sub WSReplaceFormulasOnValues2()
        Dim iList As Worksheet
        For Each iList In ActiveWorkbook.Worksheets
            iList.UsedRange.Copy
            iList.UsedRange.PasteSpecial xlValues
        Next
        Application.CutCopyMode = False
    End Sub

  • Ответ :

    Для того, чтобы получить диапазон содержащий все ячейки, в которых находится нужная формула, достаточно использовать нижеприведённый вариант, указав, естественно, свою рабочую книгу, рабочий лист, диапазон и формулу.
  • Private Sub getRangeWithFormulas()
        iMaskFormula$ = "=SUBTOTAL(*)"
        'Здесь необходимо указать свой шаблон искомой формулы
    
        With ThisWorkbook.Worksheets(1).UsedRange
             Dim iCell As Range, iDiapazon As Range
             Set iCell = .Find( _
             What:=iMaskFormula$, LookIn:=xlFormulas, LookAt:=xlWhole)
    
             If Not iCell Is Nothing Then
                iAddress$ = iCell.Address
                Set iDiapazon = iCell
                Do
                    Set iCell = .FindNext(After:=iCell)
                    Set iDiapazon = Union(iCell, iDiapazon)
                Loop While iCell.Address <> iAddress$
                'Здесь Вы можете работать с полученным диапазоном,
                'который всегда будет содержать, как минимум, одну ячейку
             End If
        End With
    End Sub
    Предполагается, что :
  • ThisWorkbook - это текущая рабочая книга, т.е. книга в которой содержится выполняемый, в настоящий момент, код.
  • Worksheets(1) - это первый рабочий лист указанной книги.
  • UsedRange - это диапазон, в котором осуществляется поиск.
  • "=SUBTOTAL(*)" - это текст искомой формулы, в моём примере, это стандартная функция рабочего листа =ПРОМЕЖУТОЧНЫЕ.ИТОГИ()
  • Ответ : Скачать пример

    Для того, чтобы создать "архив" формул нужного рабочего листа, можно сохранить формулы и адреса ячеек, которые содержат эти формулы, например, в текстовый файл. Для этого можно воспользоваться процедурой ExportFormulas, а для того, чтобы восстановить сохранённые формулы, достаточно всего лишь воспользоваться процедурой ImportFormulas
  • Private Sub ExportFormulas() 'XL2000(и старше)
        iFormulas$ = ThisWorkbook.Path & "\Formulas.txt"
        If Table.ProtectContents = True Then
           MsgBox "Рабочий лист защищён", _
           vbCritical, "Ошибка пользователя !!!"
           Exit Sub
        End If
        On Error GoTo ErrHandler
        Dim iSource As Range, iCell As Range
        Set iSource = Table.UsedRange.SpecialCells(xlFormulas)
        
        Open iFormulas$ For Output As #1
             For Each iCell In iSource
                 Write #1, iCell.Address, Replace(iCell.Formula, """", "'$")
             Next
        Close #1
    ErrHandler:
        If Err.Number <> 0 Then
           MsgBox Err.Description, vbCritical, Err.Number
        End If
    End Sub
    
    Private Sub ImportFormulas() 'XL2000(и старше)
        iFormulas$ = ThisWorkbook.Path & "\Formulas.txt"
        If Dir(iFormulas$) = "" Then
           MsgBox "Архивный файл изволит отсутствовать", _
           vbCritical, "Ошибка пользователя !!!"
           Exit Sub
        End If
        If Table.ProtectContents = True Then
           MsgBox "Рабочий лист защищён", _
           vbCritical, "Ошибка пользователя !!!"
           Exit Sub
        End If
        With Application
             .EnableCancelKey = xlDisabled
             .ScreenUpdating = False
             .DisplayAlerts = False
             .EnableEvents = False
             .Calculation = xlManual
             Open iFormulas$ For Input As #1
                  Do While Not EOF(1)
                     Input #1, iAddress$, iFormula$
                     iFormula$ = Replace(iFormula$, "'$", """")
                     Table.Range(iAddress$) = iFormula$
                  Loop
             Close #1
             If MsgBox("Вы хотите сохранить изменения ?", _
             vbYesNo, "") = vbYes Then ThisWorkbook.Save
             .Calculation = xlAutomatic
             .EnableEvents = True
             .DisplayAlerts = True
             .ScreenUpdating = True
             .EnableCancelKey = xlInterrupt
        End With
    End Sub
    Предполагается, что :
    Table - это кодовое(программное) имя рабочего листа (см.пример), формулы которого необходимо сохранить. Использование именно кодового имени не носит обязательного характера, поэтому, Вы вправе использовать и другие варианты, например, имя листа или его индекс(номер)
    Для экспорта и импорта формул необходимо, чтобы рабочий лист не был защищён, т.к. в противном случае, при экспорте, это может привести к тому, что скрытые формулы не будут заархивированы, а при импорте, к тому, что для ввода формул потребуется использование данного совета.

    Комментарий : Данный пример не предназначен для работы с формулами массива и именованными формулами, однако, он позволит восстановить формулу и получить правильный результат вычислений, если именованная формула не была удалена и текст этой формулы не был изменён.
  • Ответ :
  • If Range("A1").HasFormula = True Then
       MsgBox "Ячейка содержит формулу"
    Else
       MsgBox "Ячейка не содержит формулы"
    End If
    Комментарий : Настоятельно не рекомендуется использовать следующий вариант(ы), так как :
  • Вы получите ошибку, если формула скрыта, а рабочий лист защищён.
  • Вы получите ошибку, если ячейка будет пустая или там будет один апостроф (актуально только для второго варианта)
  • Вы получите некорректный результат, если в ячейке установлен текстовый формат и введён текст, например = ТЕКСТ =
  • Вы получите некорректный результат, если в ячейке введён текст, например '= Знак равенства является оператором сравнения, например: A1=F5
  • If Left(Range("A1").Formula, 1) = "=" Then
       MsgBox "Ячейка содержит формулу"
    Else
       MsgBox "Ячейка не содержит формулы"
    End If
    If Asc(Range("A1").Formula) = 61 Then
       MsgBox "Ячейка содержит формулу"
    Else
       MsgBox "Ячейка не содержит формулы"
    End If
    If Range("A1").Formula Like "=*" Then
       MsgBox "Ячейка содержит формулу"
    Else
       MsgBox "Ячейка не содержит формулы"
    End If
    If InStr(Range("A1").Formula, "=") = 1 Then
       MsgBox "Ячейка содержит формулу"
    Else
       MsgBox "Ячейка не содержит формулы"
    End If

  • Ответ :
  • If Range("A1").HasArray = True Then
       MsgBox "Ячейка содержит формулу массива"
    Else
       MsgBox "Ячейка не содержит формулы массива"
    End If

  • Ответ :
  • If Range("A1").HasArray = True Then
       iRangeArray = Range("A1").CurrentArray.Address
       MsgBox "Формула массива введена в ячейки : " & iRangeArray
    Else
       MsgBox "Ячейка не содержит формулы массива"
    End If
    Dim iCell As Range, iDiapazon As Range
    Set iCell = Range("A1")
    If iCell.HasArray = True Then
       Set iDiapazon = iCell.CurrentArray
       MsgBox "Формула массива введена в ячейки : " & iDiapazon.Address
    Else
       MsgBox "Ячейка не содержит формулы массива"
    End If

  • Ответ :

    Для того, чтобы создать небольшую подсказку к пользовательской функции, которую можно увидеть при вводе функции с помощью Мастера функций, следует воспользоваться любым из нижеопубликованных способов :

    Вариант I. В меню Сервис выберите пункт Макрос, затем выберите команду Начать запись. Теперь, в поле Имя макроса введите имя будущей пользовательской функции, к примеру СуммаВид, а в поле Описание введите необходимый комментарий, например, предназначение функции и/или краткое описание её аргументов. После этого, используя список Сохранить в, выберите место хранения функции и нажмите кнопку ОК. Затем остановите запись макроса, в редакторе VBA найдите созданный макрос и просто замените Sub на Function, добавьте нужные аргументы, и все необходимые инструкции.

    Вариант II. Если пользовательская функция уже создана, то в таком случае в меню Сервис выберите пункт Макрос и команду Макросы [ALT+F8] Затем, в поле Имя макроса введите имя своей функции и нажмите кнопку Параметры (если кнопка будет заблокирована, то это означает, что UDF с указанным именем, найдена не была) Теперь, в поле Описание введите необходимый комментарий, например, предназначение функции и/или краткое описание её аргументов и кликните кнопку ОК, после чего закройте диалоговое окно Макрос.

    Вариант III. Для того, чтобы добиться аналогичного эффекта, но с помощью VBA, достаточно воспользоваться методом MacroOptions об'екта Application, например :
  • Application.MacroOptions Macro:="СуммаВид", _
    Description:="Суммирует только числовые аргументы, находящиеся в ячейках, которые не являются скрытыми. Имеет всего один обязательный аргумент."

  • Ответ :

    Если Вы создавали или просто имели опыт "общения" с пользовательской функцией (UDF), то видели, что все собственные функции находятся в одной категории, а именно, Определённые пользователем
    Однако, это не является обязательным условием их использования, проще говоря, если воспользоваться методом MacroOptions об'екта Application , то пользовательскую функцию (UDF) можно "переместить" в другую категорию

    Синтаксис же этого метода такой :

    Sub MacroOptions([Macro], [Description], [HasMenu], [MenuText], [HasShortcutKey], [ShortcutKey], [Category], [StatusBar], [HelpContextID], [HelpFile])

    Но для перемещения функции в нужную категорию, нам достаточно использовать всего два аргумента Macro (имя функции) и Category (индекс категории)

    Пример добавления пользовательской функции НОМЕРКВАРТАЛ, находящейся в стандартном модуле, в категорию Дата и время
  • Application.MacroOptions Macro:="НОМЕРКВАРТАЛ", Category:=2
    Особенности Microsoft Excel 2003
    Начиная с этой версии, можно указывать не только индекс(номер) категории, но и её название(текст), см. пример и таблицу соответствий
    Application.MacroOptions Macro:="НОМЕРКВАРТАЛ", Category:="Date & Time"
    1   Финансовые                    Financial
    2   Дата и время                  Date & Time
    3   Математические                Math & Trig
    4   Статистические                Statistical
    5   Ссылки и массивы              Lookup & Reference
    6   Работа с базой данных         Database
    7   Текстовые                     Text
    8   Логические                    Logical
    9   Проверка свойств и значений   Information

    Примечание : Используя этот метод, Вы также можете добавить подсказки к пользовательской функции. Смотрите третий вариант из предыдущего совета.
  • Ответ :

    Для этого в пользовательской функции необходимо использовать метод Volatile об'екта Application, например :
  • Function TemplatePath()
    Application.Volatile True

    TemplatePath = "Шаблоны находятся здесь : " & _
    Application.TemplatesPath & " [" & Now & "] "
    End Function

  • Ответ :

    Для того, чтобы управлять пересчётом пользовательской функции, можно воспользоваться вышеприведённым советом и создать следующий "шаблон" (см. ниже) Подобный подход, позволит Вам создать функцию, которая будет пересчитываемой (volatile) по умолчанию, т.к. значение необязательного аргумента Пересчёт, который и отвечает за метод пересчёта этой функции, равно True. Когда же надобность в подобном способе пересчёта отпадёт, Вам достаточно будет передать эту аргументу значение ЛОЖЬ или 0.
  • Function Имя_функции(Аргумент1 As Тип, Аргумент2 As Тип, ..., Optional Пересчёт As Boolean = True) As Тип

    Application.Volatile Пересчёт

    'Здесь выполняются необходимые вычисления
    Имя_функции = Результат вычислений

    End Function
    Далее следует пример пользовательской функции, созданной на основании предложенного шаблона, а также примеры её вызова :
    Function NamesOfSheets(IndexWorkbook As Integer, Optional VolatileOn As Boolean = True) As String
        Application.Volatile VolatileOn

        If Workbooks.Count >= IndexWorkbook Then
           Dim iSheet As Object
           For Each iSheet In Workbooks(IndexWorkbook).Sheets
               NamesOfSheets = NamesOfSheets & ";" & iSheet.Name
           Next
           NamesOfSheets = Mid(NamesOfSheets, 2)
        Else
           NamesOfSheets = "нет книги с указанным индексом"
        End If
    End Function
    =NamesOfSheets(3)
    =NamesOfSheets(3;1)
    =NamesOfSheets(3;ИСТИНА)
    =NamesOfSheets(3;A1)


    Четыре примера вызова пересчитываемой функции. В последнем случае желательно, чтобы ячейка A1 содержала значение или формулу, возвращающую ИСТИНА или 1

    =NamesOfSheets(3;0)
    =NamesOfSheets(3;ЛОЖЬ)
    =NamesOfSheets(3;A1)


    Три примера вызова непересчитываемой функции. В последнем случае нужно, чтобы ячейка A1 содержала значение или формулу, возвращающую ЛОЖЬ или 0 (допускается также ссылка на пустую ячейку)

    =NamesOfSheets(2;ДЕНЬНЕД(ТДАТА();2)<6)

    А этот пример демонстрирует как можно обойтись без дополнительной ячейки и дать "отдохнуть" UDF в выходные, т.е. в субботу и воскресенье.
  • Ответ :

    Для того, чтобы программно пересчитать формулы, в т.ч. и volatile - пользовательские функции, можно использовать метод Calculate, который позволяет пересчитывать формулы :
  • во всех открытых рабочих книгах
  • в нужном рабочем листе
  • только в определённом диапазоне
  • Calculate 'Application.Calculate

    Worksheets(1).Calculate 'Workbooks(...).Worksheets(...)

    Range("A1:C3").Calculate 'Workbooks(...).Worksheets(...).Range(...)
    Для пересчёта же всех формул, включая все пользовательские(собственные) функции, нужно использовать комбинацию клавиш CTRL + ALT + F9, т.е.
    Application.SendKeys "^%{F9}" 'SendKeys "^%{F9}"
    Актуально только для MS Excel 2000, XP
    В этой версии, вместо имитации нажатия горячих клавиш, можно использовать
    Application.CalculateFull
    Вычисление формул массива : Если формула массива введена в одну единственную ячейку, то её вычисление ничем не отличается от вычисления обычной формулы, т.е. Range("A1").Calculate Если же она введена в несколько ячеек, допустим A1:C1 , то в версиях 95, 97, 2000 для пересчёта всех ячеек можно указать как все ячейки, содержащие эту формулу, т.е. Range("A1:C1").Calculate так и только часть ячеек, например, Range("A1").Calculate или Range("C1:C3").Calculate
    Однако, уже в MS Excel XP разработчики изменили принцип пересчёта, о чём, кстати, честно признались на своём сайте, и теперь, необходимо указывать все ячейки, иначе возникнет ошибка. Определить, какие именно ячейки содержат формулу массива, можно воспользовавшись следующим [FAQ142]
  • Ответ : Скачать пример

    Для того, чтобы узнать, когда произошёл пересчёт необходимой пользовательской функции, можно :

    - воспользоваться переменной, значение которой False/True будет зависеть от пересчёта нужной UDF. И, конечно же, не забыть об'явить эту переменную как Public.
    - добавить в нужную пользовательскую функцию инструкцию, которая будет отвечать за изменение значения вышеупомянутой переменной.
    - использовать, например, событие рабочего листа Worksheet_Calculate() вкупе с небольшой проверкой и восстановлением первоначального значения переменной.
  • Public iCalculate As Boolean
    
    Public Function MyFunction()
        'Здесь производятся необходимые вычисления 
        'MyFunction = Result 
    
        iCalculate = True
    End Function
    Private Sub Worksheet_Calculate()
        If iCalculate = True Then
           iCalculate = False
    
           MsgBox "Произошёл пересчёт нужной UDF", , Time$
           'Функцию MsgBox можно заменить на нужные инструкции
        End If
    End Sub

  • Ответ :

    Вариант I. Создайте дополнительный аргумент и укажите нужную ячейку вручную, например, введите эту функцию =CellAddress(C3) в ячейку "C3"
  • Function CellAddress$(iCell As Range)
        CellAddress$ = iCell.Address
    End Function
    Вариант II. Используйте свойство Caller об'екта Application, которое возвращает ячейку, если пользовательская функция была введена в одну ячейку и диапазон ячеек, если функция была введена как формула массива или является частью другой формулы массива.
    Function CellAddress$()
        CellAddress$ = Application.Caller.Address
    End Function
    Вариант III. Используйте свойство ThisCell об'екта Application, которое будет возвращать одну ячейку. Актуально только для MS Excel XP
    Function CellAddress$()
        CellAddress$ = Application.ThisCell.Address
    End Function

  • Ответ :

    Для того, чтобы получить дату и время последнего пересчёта пользовательской функции, добавьте в нужную функцию нижеприведённый код. Теперь, примечание, принадлежащее ячейке с функцией, будет содержать дату и время последнего пересчёта, при условии, что рабочий лист не будет защищён.
  • Function MyFunction()
        'Здесь производятся необходимые вычисления
        'MyFunction = Result

        iDateCalculate$ = Format(Now, "General Date")
        With Application.Caller
             If Not .Comment Is Nothing Then
                .Comment.Text Text:=iDateCalculate$
             Else
                .AddComment.Text Text:=iDateCalculate$
             End If
        End With
    End Function
    Источником (Автором) информации о возможности создания/изменения примечания пользовательскими функциями, вызываемыми из ячеек рабочего листа, является David Hager
  • Ответ :

    Если пользовательская функция имеет всего один аргумент, но Вам необходимо обработать диапазон несмежных ячеек, то Вы можете перечислить все необходимые ссылки, заключив их в скобки, например =NalogSumma((A2:A100;C2:C100;F1)) или указать предварительно созданное имя [FAQ] диапазона этих несмежных ячеек, например =NalogSumma(Налог)

    Комментарий : Данный совет будет работать при условии, что пользовательская функция использует правильный, в таких случаях, способ перебора, а именно цикл For Each ... Next
  • Ответ :

    Для создания необязательных аргументов следует использовать ключевое слово Optional. Обратите внимание на то, что все необязательные аргументы должны располагаться последними в списке аргументов и иметь тип Variant, если Вы планируете использовать функцию IsMissing() (см. далее)
  • Function CellText(Arg1 As String, Optional Arg2) As String

    End Function
    Для того, чтобы проверить, было ли передано значение необязательному аргументу, можно использовать функцию IsMissing()
    Function CellText(Arg1 As String, Optional Arg2) As String
        If IsMissing(Arg2) = True Then
           CellText = Arg1 & "A"
        Else
           CellText = Arg1 & Arg2
        End If
    End Function
    Два примера вызова этой пользовательской функции из ячеек рабочего листа :

    =CellText("Текст")
    =CellText("Текст";"B")


    При необходимости можно указать значение, которое будет использоваться по умолчанию :
    Function CellText$(Arg1$, Optional Arg2$ = "C", Optional Arg3$ = ";")
        CellText = Arg1 & Arg3 & Arg2
    End Function
    Три примера вызова этой пользовательской функции из ячеек рабочего листа :

    =CellText("Текст")
    =CellText("Текст";"B")
    =CellText("Текст";"B";" ")

  • Ответ :

    Для создания произвольного количества аргументов следует использовать ключевое слово ParamArray. Обратите внимание на то, этот аргумент должен быть последним в списке аргументов, иметь тип Variant и он не может использоваться вкупе со словами ByVal, ByRef или Optional.
  • Function CellText(ParamArray Massiv())
        For Each vItem In Massiv
            CellText = CellText & CStr(vItem)
        Next
    End Function
    Три примера вызова этой пользовательской функции из ячеек рабочего листа :

    =CellText("Текст";"A";"B";"C")
    =CellText("ОШИБКА: ";НД())
    =CellText("Текст";;;"M")


    Если существует вероятность, что один или несколько аргументов могут быть пропущены (см. третий пример), то в этом случае, также можно использовать функцию IsMissing()
    Function CellText(ParamArray Massiv())
        For Each vItem In Massiv
            If Not IsMissing(vItem) Then _
            CellText = CellText & CStr(vItem)
        Next
    End Function

  • Ответ : Скачать пример

    Вариант I. Для того, чтобы Ваша функция возвращала несколько значений, можно воспользоваться возможностями, которые "предоставляет" тип Variant, т.е.
  • Function MyFunction() 'As Variant
        MyFunction = Array("Иванов Ю.В.", "г.Ростов-на-Дону, ул.Ленина,12", 1961)
    End Function
    или
    Function MyFunction() 'As Variant
        Dim iMassiv(1 To 3) 'As Variant
        iMassiv(1) = "Иванов Ю.В."
        iMassiv(2) = "г.Ростов-на-Дону, ул.Ленина,12"
        iMassiv(3) = 1961
        
        MyFunction = iMassiv
    End Function
    Вызвать же программно эту функцию и получить все значения можно, использовав любой из двух нижеопубликованных вариантов (кстати, второй способ имеет смысл использовать тогда, когда нет необходимости в использовании переменной)
    Private Sub Call_MyFunction()
        iMassiv = MyFunction
        For iCount& = LBound(iMassiv) To UBound(iMassiv)
            MsgBox iMassiv(iCount&), , "Способ I"
        Next
        
        
        For Each vItem In MyFunction
            MsgBox vItem, , "Способ II"
        Next
    End Sub
    Если же Вы планируете вызвать свою функцию из ячеек рабочего листа, то :
    - подобную функцию необходимо вводить как формулу массива
    - при вводе формулы в ячейки столбца желательно воспользоваться стандартной функцией рабочего листа =ТРАНСП()
    - для получения только нужных значений (фактически отдельных элементов массива) следует использовать стандартную функцию рабочего листа =ИНДЕКС()
    Если Вы используете функцию =ИНДЕКС() для получения всех значений возвращаемых Вашей функцией, то первый пункт автоматически становится неактуален

    Вариант II. Если нет необходимости вызывать функцию из ячеек рабочего листа, то для получения аналогичного результата можно просто воспользоваться обычной коллекцией / Collection, т.е.
    Function MyFunction() As Collection
        Set MyFunction = New Collection
        MyFunction.Add "Иванов Ю.В."
        MyFunction.Add "г.Ростов-на-Дону, ул.Ленина,12"
        MyFunction.Add 1961
    End Function
    Вызвать же программно эту функцию и получить все значения можно, использовав любой из двух нижеопубликованных вариантов (второй способ можно использовать в том случае, когда нет необходимости в использовании переменной)
    Private Sub Call_MyFunction()
        Dim iCollection As Collection
        Set iCollection = MyFunction
        
        For iCount& = 1 To iCollection.Count
            MsgBox iCollection(iCount&), , "Способ I"
        Next
        
        Dim vItem As Variant
        For Each vItem In MyFunction
            MsgBox vItem, , "Способ II"
        Next
    End Sub

    Вариант III. Автор : Сергей Гергерт
    Начиная с Microsoft Excel XP можно использовать и такой способ :
    Function MyFunction() As Single()
        Dim sngTempArr(1 To 3) As Single
        sngTempArr(1) = 100.123
        sngTempArr(2) = 100.456
        sngTempArr(3) = 100.789
        MyFunction = sngTempArr
    End Function
    Разумеется массив может быть динамический, т.е.
    Function MyFunction2(lngCount As Long) As Double()
        Dim dblTempArr() As Double
        ReDim dblTempArr(1 To lngCount) As Double
        For lngCount = 1 To lngCount
            dblTempArr(lngCount) = Rnd() * 10000
        Next
        MyFunction2 = dblTempArr
    End Function

  • Ответ :

    Для того, чтобы Ваша функция, при необходимости, могла возвращать нужное значение ошибки, следует использовать функцию CVErr() К примеру, следующая функция =MyFunction(1000;0) будет возвращать #ДЕЛ/0! , в случае, когда делитель равен 0
  • Function MyFunction(A#, B#) 'As Variant
        If B <> 0 Then
           MyFunction = A / B ' A \ B
        Else
           MyFunction = CVErr(xlErrDiv0)
        End If
    End Function
    Вполне естественно, что кроме деления на нуль, Вам могут понадобиться и другие ошибки, поэтому перечень прилагается :
    Константа          Значение               Ошибка
    xlErrDiv0          2007 (&H7D7)           #ДЕЛ/0!
    xlErrNA            2042 (&H7FA)           #Н/Д
    xlErrName          2029 (&H7ED)           #ИМЯ?
    xlErrNull          2000 (&H7D0)           #ПУСТО!
    xlErrNum           2036 (&H7F4)           #ЧИСЛО!
    xlErrRef           2023 (&H7E7)           #ССЫЛКА!
    xlErrValue         2015 (&H7DF)           #ЗНАЧ!

  • Ответ :

    Если возникнет потребность в определении какой из аргументов функции, процедуры является обязательным/необязательным, то сделать это будет довольно просто, т.к. после ввода имени функции на экране появится подсказка, где все необязательные аргументы будут заключены в квадратные скобки (см. скриншоты)

    , , ,

    В случае, если подсказка не появится, Вы, после ввода имени, можете :
    - использовать клавиши CTRL + I
    - в меню Правка / Edit выбрать команду Сведения / Quick Info
    - или в меню Сервис / Tools выбрать команду Параметры / Options, затем выделить закладку Редактор / Editor и установить "флажок" напротив Краткие сведения / Auto Quick Info и нажать кнопку OK (установка этой опции позволит, в дальнейшем, выводить на экран подсказку автоматически)

    Если же для получения информации о нужной функции, Вы используете клавишу F1, то просто имейте ввиду, что описание всех обязательных аргументов начинается со слова Required, а всех необязательных со слова Optional,
  • Ответ :
  • Names.Add Name:="СуммаНалог", RefersTo:="=SUM(Лист1!$B$5:$B$10)", Visible:=False

    Names.Add Name:="СуммаВзятка", RefersToLocal:="=СУММ(Лист1!$C$5:$C$10)", Visible:=False
    Примечание :
    a) рекомендуется использовать абсолютные ссылки на адреса ячеек.
    б) [см. выше]

    Для того чтобы скрыть уже существующее имя, используйте этот код :
    Names("СуммаНалог").Visible = False
    Names("СуммаВзятка").Visible = False

  • Ответ :

    Вариант I.
  • For Each iName In ThisWorkbook.Names
       Cells(iName.Index, 1).Value = iName.Name
       Cells(iName.Index, 2).Value = "'" & iName.RefersToLocal
    Next
    Вариант II.
    With ThisWorkbook.Names
       For iCount = 1 To .Count
           Cells(iCount, 1).Value = .Item(iCount).Name
           Cells(iCount, 2).Value = "'" & .Item(iCount).RefersToLocal
       Next
    End With
    Bonus.
    For Each iName In ThisWorkbook.Names
        With iName
             iIndex = .Index + 1
             Cells(iIndex, 1).Value = .Name
             Cells(iIndex, 2).Value = .Parent.Name
             Cells(iIndex, 3).Value = TypeName(.Parent)
             Cells(iIndex, 4).Value = "'" & .RefersTo
             Cells(iIndex, 5).Value = "'" & .RefersToLocal
             Cells(iIndex, 6).Value = .Visible
        End With
    Next
    With Range(Cells(1, 1), Cells(1, 6)) 'Range("A1:F1")
         .Value = Array("Name", "Parent", "NameLevel", _
         "RefersTo", "RefersToLocal", "Visible")
         .Font.Bold = True
         .EntireColumn.AutoFit
    End With

    Воспроизведение любых опубликованных здесь материалов возможно только с письменного разрешения автора : Microsoft Excel 95, 97, 2000, XP

    © 2004-2016 Климов П.Ю. Все права защищены. WebDesign & Error's Klimoff