excel-vba
Пользовательские функции (UDF)

Синтаксис

1. Функция functionName (argumentVariable As dataType, argumentVariable2 As dataType, Необязательный аргументVariable3 В качестве dataType) В качестве функцииReturnDataType
   Базовое объявление функции. Каждая функция нуждается в имени, но ей не нужно принимать какие-либо аргументы. Он может принимать 0 аргументов или может принимать заданное количество аргументов. Вы также можете объявить аргумент как необязательный (это означает, что не имеет значения, укажите ли вы его при вызове функции). Рекомендуется использовать тип переменной для каждого аргумента, а также возвращать тип данных, которые сама функция будет возвращать.

2. functionName = theVariableOrValueBeingReturned
   Если вы используете другие языки программирования, вы можете использовать ключевое слово Return . Это не используется в VBA - вместо этого мы используем имя функции. Вы можете установить его на содержимое переменной или на какое-то прямое значение. Обратите внимание, что если вы задали тип данных для возврата функции, переменная или данные, которые вы поставляете на этот раз, должны быть из этого типа данных.

3. Конечная функция
   Обязательный. Указывает конец Function блока и должен быть таким образом в конце. VBE обычно предоставляет это автоматически при создании новой функции.

замечания

UDF - Hello World

1. Открыть Excel
2. Откройте редактор Visual Basic (см. Раздел Открытие редактора Visual Basic )
3. Добавьте новый модуль, нажав Insert -> Module:

4. Скопируйте и вставьте следующий код в новый модуль:

Public Function Hello() As String
'Note: the output of the function is simply the function's name
Hello = "Hello, World !"
End Function

Чтобы получить :

5. Вернитесь к своей книге и введите «= Hello ()» в ячейку, чтобы увидеть «Hello World».

Разрешить полную колонку без штрафа

Легче реализовать некоторые UDF на листе, если в качестве параметров можно передать полные ссылки на столбцы. Однако из-за явного характера кодирования любая петля, включающая эти диапазоны, может обрабатывать сотни тысяч ячеек, которые полностью пусты. Это уменьшает ваш проект VBA (и рабочую книгу) до замороженного беспорядка, в то время как ненужные не-значения обрабатываются.

Цикл через ячейки листа является одним из самых медленных методов выполнения задачи, но иногда это неизбежно. Сокращение работы, выполняемой до того, что на самом деле требуется, имеет смысл.

Решение состоит в том, чтобы обрезать полный столбец или ссылки полной строки на свойство Worksheet.UsedRange с помощью метода Intersect . Следующий образец будет свободно реплицировать собственную SUMIF-функцию рабочего листа, так что критерий_range также будет изменен в соответствии с sum_range, поскольку каждое значение в sum_range должно сопровождаться значением в критерие_range .

Application.Caller для UDF, используемого на листе, является ячейкой, в которой он находится. Свойство .Parent ячейки является листом . Это будет использоваться для определения .UsedRange.

В листе кода модуля:

Option Explicit

Function udfMySumIf(rngA As Range, rngB As Range, _
                    Optional crit As Variant = "yes")
    Dim c As Long, ttl As Double
    
    With Application.Caller.Parent
        Set rngA = Intersect(rngA, .UsedRange)
        Set rngB = rngB.Resize(rngA.Rows.Count, rngA.Columns.Count)
    End With
    
    For c = 1 To rngA.Cells.Count
        If IsNumeric(rngA.Cells(c).Value2) Then
            If LCase(rngB(c).Value2) = LCase(crit) Then
                ttl = ttl + rngA.Cells(c).Value2
            End If
        End If
    Next c
    
    udfMySumIf = ttl

End Function

^{Синтаксис:}
=udfMySumIf(*sum_range*, *criteria_range*, [*criteria*])

Хотя это довольно упрощенный пример, он адекватно демонстрирует передачу двух полных столбцов (1 048 576 строк каждая), но обрабатывает только 15 строк данных и критериев.

_{Связанная официальная документация MSDN по отдельным методам и свойствам предоставлена ​​Microsoft ™.}

Count Уникальные значения в диапазоне

Function countUnique(r As range) As Long
    'Application.Volatile False ' optional
    Set r = Intersect(r, r.Worksheet.UsedRange) ' optional if you pass entire rows or columns to the function
    Dim c As New Collection, v
    On Error Resume Next   ' to ignore the Run-time error 457: "This key is already associated with an element of this collection".
    For Each v In r.Value  ' remove .Value for ranges with more than one Areas
        c.Add 0, v & ""
    Next
    c.Remove "" ' optional to exclude blank values from the count
    countUnique = c.Count
End Function

Коллекции