excel-vba
User Defined Functions (UDF's)

Syntaxis

1. Functie functionName (argumentVariable As dataType, argumentVariable2 As dataType, Optioneel argumentVariable3 As dataType) As functionReturnDataType
   Basisverklaring van een functie. Elke functie heeft een naam nodig, maar er zijn geen argumenten nodig. Het kan 0 argumenten nodig hebben, of het kan een bepaald aantal argumenten bevatten. U kunt een argument ook optioneel opgeven (wat betekent dat het niet uitmaakt of u het opgeeft wanneer u de functie aanroept). Het is de beste praktijk om het variabele type voor elk argument op te geven en op dezelfde manier terug te geven welk gegevenstype de functie zelf zal retourneren.

2. functionName = theVariableOrValueBeingReturned
   Als u uit andere programmeertalen komt, bent u misschien gewend aan het trefwoord Return . Dit wordt niet gebruikt in VBA - in plaats daarvan gebruiken we de functienaam. U kunt deze instellen op de inhoud van een variabele of op een direct opgegeven waarde. Merk op dat als u een gegevenstype hebt ingesteld voor de terugkeer van de functie, de variabele of gegevens die u deze keer opgeeft, van dat gegevenstype moet zijn.

3. Einde functie
   Verplicht. Betekent het einde van de Function codeblok en moet aldus worden aan het eind. De VBE levert dit meestal automatisch wanneer u een nieuwe functie maakt.

Opmerkingen

UDF - Hallo wereld

1. Open Excel
2. Open de Visual Basic Editor (zie Visual Basic Editor openen )
3. Voeg een nieuwe module toe door op Invoegen -> Module te klikken:

4. Kopieer en plak de volgende code in de nieuwe module:

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

Verkrijgen :

5. Ga terug naar uw werkmap en typ "= Hallo ()" in een cel om de "Hallo wereld" te zien.

Sta volledige kolomverwijzingen toe zonder boete

Het is gemakkelijker om enkele UDF's op het werkblad te implementeren als volledige kolomverwijzingen als parameters kunnen worden doorgegeven. Vanwege de expliciete aard van codering, kan elke lus met deze bereiken echter honderdduizenden cellen verwerken die volledig leeg zijn. Dit reduceert uw VBA-project (en werkmap) tot een bevroren puinhoop terwijl onnodige niet-waarden worden verwerkt.

Lussen door de cellen van een werkblad is een van de langzaamste methoden om een taak uit te voeren, maar soms is het onvermijdelijk. Het is volkomen logisch om het uitgevoerde werk terug te brengen tot wat werkelijk nodig is.

De oplossing is om de volledige kolom of volledige rijverwijzingen naar de eigenschap Worksheet.UsedRange af te kappen met de methode Intersect . In het volgende voorbeeld wordt de native SUMIF-functie van een werkblad losjes gerepliceerd, zodat het criteriumbereik ook wordt aangepast aan het sombereik omdat elke waarde in het sombereik vergezeld moet gaan van een waarde in het criteriumbereik .

De Application.Caller voor een UDF die op een werkblad wordt gebruikt, is de cel waarin deze zich bevindt. De eigenschap .Parent van de cel is het werkblad. Dit wordt gebruikt om de .UsedRange te definiëren.

In een modulecodeblad:

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

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

Hoewel dit een vrij simplistisch voorbeeld is, laat het voldoende zien om twee volledige kolomverwijzingen in te voeren (elk 1.048.576 rijen), maar slechts 15 rijen met gegevens en criteria te verwerken.

_{Gekoppelde officiële MSDN-documentatie van individuele methoden en eigenschappen met dank aan Microsoft ™.}

Tel unieke waarden in bereik

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

collecties