excel-vba
Funzioni definite dall'utente (UDF)

Sintassi

1. Funzione functionName (argumentVariable As dataType, argumentVariable2 As dataType, facoltativo argumentVariable3 As dataType) As functionReturnDataType
   Dichiarazione di base di una funzione. Ogni funzione ha bisogno di un nome, ma non deve prendere alcun argomento. Potrebbero essere necessari 0 argomenti o potrebbe essere necessario un determinato numero di argomenti. Puoi anche dichiarare un argomento come facoltativo (cioè non importa se lo fornisci quando chiami la funzione). È consigliabile fornire il tipo di variabile per ogni argomento e, analogamente, restituire il tipo di dati restituito dalla funzione stessa.

2. functionName = theVariableOrValueBeingReturned
   Se provieni da altri linguaggi di programmazione, potresti essere abituato alla parola chiave Return . Questo non è usato in VBA - invece, usiamo il nome della funzione. È possibile impostarlo sul contenuto di una variabile o su un valore fornito direttamente. Nota che se hai impostato un tipo di dati per il ritorno della funzione, la variabile oi dati che stai fornendo questa volta devono essere di quel tipo di dati.

3. Fine Funzione
   Obbligatorio. Significa la fine del codice di blocco della Function e deve quindi essere alla fine. Generalmente il VBE lo fornisce automaticamente quando si crea una nuova funzione.

Osservazioni

UDF - Hello World

1. Apri Excel
2. Aprire l'editor di Visual Basic (vedere Apertura dell'editor di Visual Basic )
3. Aggiungi un nuovo modulo facendo clic su Inserisci -> Modulo:

4. Copia e incolla il codice seguente nel nuovo modulo:

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

Ottenere :

5. Torna alla cartella di lavoro e digita "= Hello ()" in una cella per visualizzare "Hello World".

Consenti riferimenti a colonne complete senza penalità

È più semplice implementare alcune UDF nel foglio di lavoro se i riferimenti a colonne completi possono essere passati come parametri. Tuttavia, a causa della natura esplicita della codifica, qualsiasi loop che coinvolge questi intervalli può elaborare centinaia di migliaia di celle completamente vuote. Questo riduce il tuo progetto VBA (e la cartella di lavoro) a un pasticcio congelato mentre i non-valori non necessari vengono elaborati.

Il looping delle celle di un foglio di lavoro è uno dei metodi più lenti per eseguire un'attività, ma a volte è inevitabile. Tagliare il lavoro eseguito fino a ciò che è effettivamente necessario ha perfettamente senso.

La soluzione è di troncare la colonna completa oi riferimenti di riga completa alla proprietà Worksheet.UsedRange con il metodo Intersect . Nell'esempio riportato di seguito sarà liberamente replicare la funzione SUMIF nativa di un foglio di lavoro in modo che il criteri_intervallo anche essere ridimensionato per adattarsi alla int_somma poiché ogni valore nell'int_somma deve essere accompagnato da un valore nel criteri_intervallo.

Application.Caller per una UDF utilizzata su un foglio di lavoro è la cella in cui risiede. La proprietà .Parent della cella è il foglio di lavoro. Questo sarà usato per definire .UsedRange.

In un foglio di codice del modulo:

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

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

Mentre questo è un esempio abbastanza semplicistico, dimostra adeguatamente il passaggio di due riferimenti a colonne complete (1.048.576 righe ciascuno) ma l'elaborazione solo di 15 righe di dati e criteri.

_{Documentazione ufficiale MSDN collegata dei singoli metodi e proprietà forniti da Microsoft ™.}

Contare i valori unici nell'intervallo

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

collezioni