excel-vba
Benutzerdefinierte Funktionen (UDFs)

Syntax

1. Funktion functionName (ArgumentVariable As Datentyp, ArgumentVariable2 Als Datentyp, Optionales ArgumentVariable3 Als Datentyp) Als FunktionReturnDataType
   Grunddeklaration einer Funktion. Jede Funktion benötigt einen Namen, muss jedoch keine Argumente enthalten. Es können 0 Argumente oder eine bestimmte Anzahl von Argumenten verwendet werden. Sie können ein Argument auch als optional deklarieren (dh es spielt keine Rolle, ob Sie es beim Aufruf der Funktion angeben). Es ist empfehlenswert, den Variablentyp für jedes Argument anzugeben und auch zurückzugeben, welchen Datentyp die Funktion selbst zurückgeben wird.

2. functionName = theVariableOrValueBeingReturned
   Wenn Sie aus anderen Programmiersprachen kommen, sind Sie möglicherweise an das Return Schlüsselwort gewöhnt. Dies wird in VBA nicht verwendet - stattdessen verwenden wir den Funktionsnamen. Sie können den Inhalt einer Variablen oder einen direkt bereitgestellten Wert festlegen. Wenn Sie einen Datentyp für die Rückgabe der Funktion festgelegt haben, müssen die Variablen oder Daten, die Sie zu diesem Zeitpunkt bereitstellen, diesen Datentyp haben.

3. Funktion beenden
   Verpflichtend. Bezeichnet das Ende des Function Codeblocks und muss somit am Ende sein. Die VBE liefert dies normalerweise automatisch, wenn Sie eine neue Funktion erstellen.

Bemerkungen

UDF - Hallo Welt

1. Öffnen Sie Excel
2. Öffnen Sie den Visual Basic-Editor (siehe Öffnen des Visual Basic-Editors ).
3. Fügen Sie ein neues Modul hinzu, indem Sie auf Einfügen -> Modul klicken:

4. Kopieren Sie den folgenden Code, und fügen Sie ihn in das neue Modul ein:

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

Erhalten :

5. Gehen Sie zurück zu Ihrer Arbeitsmappe und geben Sie "= Hello ()" in eine Zelle ein, um "Hello World" anzuzeigen.

Erlauben Sie vollständige Spaltenverweise ohne Strafe

Es ist einfacher, einige UDFs im Arbeitsblatt zu implementieren, wenn vollständige Spaltenverweise als Parameter übergeben werden können. Aufgrund der expliziten Art der Codierung kann jedoch jede Schleife, die diese Bereiche umfasst, Hunderttausende von Zellen verarbeiten, die vollständig leer sind. Dies reduziert Ihr VBA-Projekt (und Ihre Arbeitsmappe) auf ein eingefrorenes Durcheinander, während unnötige Nichtwerte verarbeitet werden.

Das Durchlaufen der Zellen eines Arbeitsblatts ist eine der langsamsten Methoden, um eine Aufgabe auszuführen, aber manchmal ist dies unvermeidlich. Es ist absolut sinnvoll, die geleistete Arbeit auf das Notwendige zu reduzieren.

Die Lösung besteht darin, mit der Intersect-Methode die vollständigen Spalten- oder vollständigen Zeilenverweise auf die Worksheet.UsedRange-Eigenschaft abzuschneiden . Das folgende Beispiel wird lose ein Arbeitsblatt nativen SUMIF Funktion repliziert , so dass die Kriterien_Bereich auch die Summe_Bereich anpassen Größe verändert werden , da jeder Wert in der Summe_Bereich muss von einem Wert in der Kriterien_Bereich begleitet werden.

Der Application.Caller für eine in einem Arbeitsblatt verwendete UDF ist die Zelle, in der sie sich befindet. Die .Parent- Eigenschaft der Zelle ist das Arbeitsblatt. Dies wird zur Definition des .UsedRange verwendet.

In einem Modul-Codeblatt:

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

^Syntax:
=udfMySumIf(*sum_range*, *criteria_range*, [*criteria*])

Dies ist zwar ein ziemlich simpeles Beispiel, zeigt jedoch das Übergeben von zwei vollständigen Spaltenreferenzen (jeweils 1.048.576 Zeilen), wobei jedoch nur 15 Zeilen mit Daten und Kriterien verarbeitet werden.

_{Verknüpfte offizielle MSDN-Dokumentation einzelner Methoden und Eigenschaften mit freundlicher Genehmigung von Microsoft ™.}

Einzelne Werte in Bereich zählen

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

Sammlungen