excel-vba
Funkcje zdefiniowane przez użytkownika (UDF)

Składnia

1. Funkcja functionName (argumentVariable As dataType, argumentVariable2 As dataType, Opcjonalny argumentVariable3 As dataType) As functionReturnDataType
   Podstawowa deklaracja funkcji. Każda funkcja wymaga nazwy, ale nie musi przyjmować żadnych argumentów. Może przyjmować 0 argumentów lub może przyjmować określoną liczbę argumentów. Możesz także zadeklarować argument jako opcjonalny (co oznacza, że nie ma znaczenia, jeśli podasz go podczas wywoływania funkcji). Najlepszą praktyką jest podanie typu zmiennej dla każdego argumentu, a także, aby zwrócić typ danych, który zwróci sama funkcja.

2. functionName = theVariableOrValueBeingReturned
   Jeśli pochodzisz z innych języków programowania, możesz być przyzwyczajony do słowa kluczowego Return . Nie jest to używane w VBA - zamiast tego używamy nazwy funkcji. Możesz ustawić go na zawartość zmiennej lub na pewną bezpośrednio podaną wartość. Zauważ, że jeśli ustawiłeś typ danych dla powrotu funkcji, zmienna lub dane, które podajesz tym razem, muszą być tego typu danych.

3. Funkcja zakończenia
   Obowiązkowy. Oznacza koniec kodu blokady Function i dlatego musi znajdować się na końcu. VBE zwykle dostarcza to automatycznie po utworzeniu nowej funkcji.

Uwagi

UDF - Hello World

1. Otwórz program Excel
2. Otwórz edytor Visual Basic (patrz Otwieranie edytora Visual Basic )
3. Dodaj nowy moduł, klikając Wstaw -> Moduł:

4. Skopiuj i wklej następujący kod w nowym module:

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

Pozyskać :

5. Wróć do skoroszytu i wpisz „= Hello ()” w komórce, aby zobaczyć „Hello World”.

Zezwalaj na pełne odwołania do kolumn bez kary

Łatwiej jest zaimplementować niektóre UDF w arkuszu, jeśli można podać pełne odwołania do kolumn jako parametry. Jednak ze względu na wyraźny charakter kodowania, każda pętla obejmująca te zakresy może przetwarzać setki tysięcy komórek, które są całkowicie puste. Zmniejsza to twój projekt VBA (i skoroszyt) do zamrożonego bałaganu podczas przetwarzania niepotrzebnych nie-wartości.

Pętla w komórkach arkusza roboczego jest jedną z najwolniejszych metod wykonania zadania, ale czasem jest to nieuniknione. Cięcie wykonanej pracy do tego, co jest rzeczywiście wymagane, ma sens.

Rozwiązaniem jest obcięcie pełnych odwołań do kolumn lub pełnych wierszy do właściwości Worksheet.UsedRange metodą Intersect . Poniższy przykładowy będzie luźno replikować natywną funkcję SUMA.JEŻELI arkuszu jest więc criteria_range będzie również zmieniany odpowiednio do zakres_sumy ponieważ każda wartość w zakres_sumy musi towarzyszyć wartości w criteria_range.

Application.Caller dla UDF używanego w arkuszu jest komórką, w której się on znajduje. Właściwością .Parent komórki jest arkusz. Będzie to wykorzystane do zdefiniowania .UsedRange.

W arkuszu kodu modułu:

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

^Składnia:
=udfMySumIf(*sum_range*, *criteria_range*, [*criteria*])

Chociaż jest to dość uproszczony przykład, odpowiednio pokazuje przejście w dwóch pełnych odniesieniach do kolumn (1048 576 wierszy każdy), ale tylko przetwarzanie 15 wierszy danych i kryteriów.

_{Powiązana oficjalna dokumentacja MSDN poszczególnych metod i właściwości dzięki uprzejmości Microsoft ™.}

Policz unikalne wartości w zakresie

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

Kolekcje