Creazione di una funzione definita dall'utente (UDF) in Excel VBA (Guida definitiva)

Con VBA, puoi creare una funzione personalizzata (chiamata anche funzione definita dall'utente) che può essere utilizzata nei fogli di lavoro proprio come le normali funzioni.

Questi sono utili quando le funzioni di Excel esistenti non sono sufficienti. In questi casi, puoi creare la tua funzione definita dall'utente (UDF) personalizzata per soddisfare le tue esigenze specifiche.

In questo tutorial, tratterò tutto sulla creazione e l'utilizzo di funzioni personalizzate in VBA.

Se sei interessato a imparare VBA nel modo più semplice, dai un'occhiata a my Formazione online su VBA di Excel.

Che cos'è una procedura di funzione in VBA?

Una routine Function è un codice VBA che esegue calcoli e restituisce un valore (o una matrice di valori).

Utilizzando una procedura Function, è possibile creare una funzione da utilizzare nel foglio di lavoro (proprio come qualsiasi normale funzione di Excel come SOMMA o CERCA.VERT).

Dopo aver creato una procedura Function utilizzando VBA, è possibile utilizzarla in tre modi:

1. Come una formula nel foglio di lavoro, dove può accettare argomenti come input e restituire un valore o una matrice di valori.
2. Come parte del codice della subroutine VBA o di un altro codice funzione.
3. Nella formattazione condizionale.

Sebbene ci siano già oltre 450 funzioni Excel integrate disponibili nel foglio di lavoro, potresti aver bisogno di una funzione personalizzata se:

• Le funzioni integrate non possono fare ciò che vuoi ottenere. In questo caso, puoi creare una funzione personalizzata in base alle tue esigenze.
• Le funzioni integrate possono portare a termine il lavoro, ma la formula è lunga e complicata. In questo caso, puoi creare una funzione personalizzata di facile lettura e utilizzo.

Tieni presente che le funzioni personalizzate create utilizzando VBA possono essere significativamente più lente delle funzioni integrate. Quindi, questi sono più adatti per situazioni in cui non è possibile ottenere il risultato utilizzando le funzioni integrate.

Funzione vs. Sottoprogramma in VBA

Una "Subroutine" consente di eseguire un insieme di codice mentre una "Funzione" restituisce un valore (o un array di valori).

Per farti un esempio, se hai una lista di numeri (sia positivi che negativi) e vuoi identificare i numeri negativi, ecco cosa puoi fare con una funzione e una subroutine.

Una subroutine può scorrere ogni cella dell'intervallo e può evidenziare tutte le celle che contengono un valore negativo. In questo caso, la subroutine finisce per modificare le proprietà dell'oggetto intervallo (cambiando il colore delle celle).

Con una funzione personalizzata, puoi usarla in una colonna separata e può restituire VERO se il valore in una cella è negativo e FALSO se è positivo. Con una funzione, non puoi modificare le proprietà dell'oggetto. Ciò significa che non è possibile modificare il colore della cella con una funzione stessa (tuttavia, è possibile farlo utilizzando la formattazione condizionale con la funzione personalizzata).

Quando crei una funzione definita dall'utente (UDF) utilizzando VBA, puoi utilizzare quella funzione nel foglio di lavoro proprio come qualsiasi altra funzione. Tratterò di più su questo nella sezione "Diversi modi di utilizzare una funzione definita dall'utente in Excel".

Creazione di una semplice funzione definita dall'utente in VBA

Permettetemi di creare una semplice funzione definita dall'utente in VBA e mostrarvi come funziona.

Il codice seguente crea una funzione che estrarrà le parti numeriche da una stringa alfanumerica.

Funzione GetNumeric(CellRef As String) as Long Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Avanti i GetNumeric = Funzione di fine risultato

Quando hai il codice sopra nel modulo, puoi usare questa funzione nella cartella di lavoro.

Di seguito è riportato come questa funzione - Ottieni Numerico - può essere utilizzato in Excel.

Ora, prima di dirti come viene creata questa funzione in VBA e come funziona, ci sono alcune cose che dovresti sapere:

• Quando crei una funzione in VBA, diventa disponibile nell'intera cartella di lavoro proprio come qualsiasi altra funzione normale.
• Quando digiti il ​​nome della funzione seguito dal segno di uguale, Excel ti mostrerà il nome della funzione nell'elenco delle funzioni corrispondenti. Nell'esempio sopra, quando ho inserito =Get, Excel mi ha mostrato un elenco con la mia funzione personalizzata.

Credo che questo sia un buon esempio in cui puoi utilizzare VBA per creare una funzione semplice da usare in Excel. Puoi fare la stessa cosa anche con una formula (come mostrato in questo tutorial), ma diventa complicato e difficile da capire. Con questa UDF, devi solo passare un argomento e ottieni il risultato.

Anatomia di una funzione definita dall'utente in VBA

Nella sezione precedente, ti ho fornito il codice e ti ho mostrato come funziona la funzione UDF in un foglio di lavoro.

Ora approfondiamo e vediamo come viene creata questa funzione. È necessario inserire il codice seguente in un modulo nell'editor VB. Tratto questo argomento nella sezione "Dove inserire il codice VBA per una funzione definita dall'utente".

Function GetNumeric(CellRef As String) as Long ' Questa funzione estrae la parte numerica dalla stringa Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Risultato & Mid(RifCell, i, 1) Avanti i GetNumeric = Funzione Fine Risultato

La prima riga del codice inizia con la parola - Funzione.

Questa parola dice a VBA che il nostro codice è una funzione (e non una subroutine). La parola Function è seguita dal nome della funzione - GetNumeric. Questo è il nome che utilizzeremo nel foglio di lavoro per utilizzare questa funzione.

• Il nome della funzione non può contenere spazi. Inoltre, non puoi nominare una funzione se si scontra con il nome di un riferimento di cella. Ad esempio, non è possibile denominare la funzione ABC123 poiché fa riferimento anche a una cella nel foglio di lavoro di Excel.
• Non dovresti dare alla tua funzione lo stesso nome di una funzione esistente. Se lo fai, Excel darebbe la preferenza alla funzione integrata.
• Puoi usare un trattino basso se vuoi separare le parole. Ad esempio, Get_Numeric è un nome accettabile.

Il nome della funzione è seguito da alcuni argomenti tra parentesi. Questi sono gli argomenti di cui la nostra funzione avrebbe bisogno dall'utente. Questi sono proprio come gli argomenti che dobbiamo fornire alle funzioni integrate di Excel. Ad esempio in una funzione CONTA.SE, ci sono due argomenti (intervallo e criteri)

All'interno delle parentesi, è necessario specificare gli argomenti.

Nel nostro esempio, c'è solo un argomento: CellRef.

È anche una buona pratica specificare che tipo di argomento si aspetta la funzione. In questo esempio, poiché forniremo alla funzione un riferimento di cella, possiamo specificare l'argomento come tipo "Intervallo". Se non specifichi un tipo di dati, VBA lo considererà una variante (il che significa che puoi utilizzare qualsiasi tipo di dati).

Se hai più di un argomento, puoi specificarli nella stessa parentesi, separati da una virgola. Vedremo più avanti in questo tutorial come utilizzare più argomenti in una funzione definita dall'utente.

Si noti che la funzione è specificata come tipo di dati "Stringa". Questo direbbe a VBA che il risultato della formula sarebbe del tipo di dati String.

Mentre posso usare un tipo di dati numerico qui (come Long o Double), farlo limiterebbe l'intervallo di numeri che può restituire. Se ho una stringa lunga 20 numeri che devo estrarre dalla stringa complessiva, dichiarare la funzione come Long o Double darebbe un errore (poiché il numero sarebbe fuori dal suo intervallo). Quindi ho mantenuto il tipo di dati di output della funzione come String.

La seconda riga del codice, quella in verde che inizia con un apostrofo, è un commento. Durante la lettura del codice, VBA ignora questa riga. Puoi usarlo per aggiungere una descrizione o un dettaglio sul codice.

La terza riga del codice dichiara una variabile "StringLength" come tipo di dati Integer. Questa è la variabile in cui memorizziamo il valore della lunghezza della stringa che viene analizzata dalla formula.

La quarta riga dichiara la variabile Risultato come tipo di dati String. Questa è la variabile in cui estrarremo i numeri dalla stringa alfanumerica.

La quinta riga assegna la lunghezza della stringa nell'argomento di input alla variabile "StringLength". Si noti che "CellRef" si riferisce all'argomento che verrà fornito dall'utente durante l'utilizzo della formula nel foglio di lavoro (o l'utilizzo in VBA, che vedremo più avanti in questo tutorial).

La sesta, la settima e l'ottava riga sono la parte del ciclo For Next. Il ciclo viene eseguito tante volte quanti sono i caratteri presenti nell'argomento di input. Questo numero è dato dalla funzione LEN ed è assegnato alla variabile 'StringLength'.

Quindi il ciclo va da "1 a Stringlength".

All'interno del ciclo, l'istruzione IF analizza ogni carattere della stringa e, se è numerico, aggiunge quel carattere numerico alla variabile Risultato. Usa la funzione MID in VBA per farlo.

La penultima riga del codice assegna il valore del risultato alla funzione. È questa riga di codice che garantisce che la funzione restituisca il valore "Risultato" nella cella (da dove viene chiamata).

L'ultima riga del codice è End Function. Questa è una riga di codice obbligatoria che indica a VBA che il codice della funzione termina qui.

Il codice sopra spiega le diverse parti di una tipica funzione personalizzata creata in VBA. Nelle sezioni seguenti, approfondiremo questi elementi e vedremo anche i diversi modi per eseguire la funzione VBA in Excel.

Argomenti in una funzione definita dall'utente in VBA

Negli esempi precedenti, in cui abbiamo creato una funzione definita dall'utente per ottenere la parte numerica da una stringa alfanumerica (GetNumeric), la funzione è stata progettata per accettare un singolo argomento.

In questa sezione, tratterò come creare funzioni che non accettano argomenti rispetto a quelle che accettano più argomenti (richiesti e opzionali).

Creazione di una funzione in VBA senza argomenti

Nel foglio di lavoro di Excel, abbiamo diverse funzioni che non accettano argomenti (come CASUALE, OGGI, ORA).

Queste funzioni non dipendono da alcun argomento di input. Ad esempio, la funzione TODAY restituirebbe la data corrente e la funzione RAND restituirebbe un numero casuale compreso tra 0 e 1.

Puoi creare una funzione simile anche in VBA.

Di seguito è riportato il codice che ti darà il nome del file. Non richiede alcun argomento poiché il risultato che deve restituire non dipende da alcun argomento.

Function WorkbookName() As String WorkbookName = ThisWorkbook.Name End Function

Il codice sopra specifica il risultato della funzione come tipo di dati String (poiché il risultato che vogliamo è il nome del file, che è una stringa).

Questa funzione assegna il valore di "ThisWorkbook.Name" alla funzione, che viene restituito quando la funzione viene utilizzata nel foglio di lavoro.

Se il file è stato salvato, restituisce il nome con l'estensione del file, altrimenti dà semplicemente il nome.

Quanto sopra ha un problema però.

Se il nome del file cambia, non si aggiornerà automaticamente. Normalmente una funzione si aggiorna ogni volta che c'è un cambiamento negli argomenti di input. Ma poiché non ci sono argomenti in questa funzione, la funzione non viene ricalcolata (anche se cambi il nome della cartella di lavoro, la chiudi e poi la riapri).

Se lo desideri, puoi forzare un ricalcolo utilizzando la scorciatoia da tastiera - Control + Alt + F9.

Per fare in modo che la formula venga ricalcolata ogni volta che viene apportata una modifica nel foglio di lavoro, è necessario inserire una riga di codice.

Il codice seguente fa ricalcolare la funzione ogni volta che si verifica una modifica nel foglio di lavoro (proprio come altre funzioni simili del foglio di lavoro come la funzione TODAY o RAND).

Function WorkbookName() As String Application.Volatile True WorkbookName = ThisWorkbook.Name End Function

Ora, se cambi il nome della cartella di lavoro, questa funzione si aggiornerà ogni volta che viene apportata una modifica al foglio di lavoro o quando riapri questa cartella di lavoro.

Creazione di una funzione in VBA con un argomento

In una delle sezioni precedenti, abbiamo già visto come creare una funzione che accetta un solo argomento (la funzione GetNumeric trattata sopra).

Creiamo un'altra semplice funzione che accetta un solo argomento.

La funzione creata con il codice seguente converte il testo di riferimento in maiuscolo. Ora abbiamo già una funzione per questo in Excel e questa funzione serve solo per mostrarti come funziona. Se devi farlo, è meglio usare la funzione UPPER incorporata.

Funzione ConvertToUpperCase(CellRef As Range) ConvertToUpperCase = UCase(CellRef) End Function

Questa funzione utilizza la funzione UCase in VBA per modificare il valore della variabile CellRef. Quindi assegna il valore alla funzione ConvertToUpperCase.

Poiché questa funzione accetta un argomento, non è necessario utilizzare la parte Application.Volatile qui. Non appena l'argomento cambia, la funzione si aggiorna automaticamente.

Creazione di una funzione in VBA con più argomenti

Proprio come le funzioni del foglio di lavoro, puoi creare funzioni in VBA che accettano più argomenti.

Il codice seguente creerebbe una funzione che estrarrà il testo prima del delimitatore specificato. Richiede due argomenti: il riferimento di cella che contiene la stringa di testo e il delimitatore.

Funzione GetDataBeforeDelimiter(CellRef As Range, Delim As String) as String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 Risultato = Left(CellRef, DelimPosition) GetDataBeforeDelimiter = Risultato End Function

Quando è necessario utilizzare più di un argomento in una funzione definita dall'utente, è possibile avere tutti gli argomenti tra parentesi, separati da una virgola.

Si noti che per ogni argomento è possibile specificare un tipo di dati. Nell'esempio precedente, "CellRef" è stato dichiarato come tipo di dati di intervallo e "Delim" è stato dichiarato come tipo di dati di tipo String. Se non specifichi alcun tipo di dati, VBA li considera un tipo di dati variant.

Quando si utilizza la funzione sopra nel foglio di lavoro, è necessario fornire il riferimento di cella che ha il testo come primo argomento e i caratteri delimitatori tra virgolette come secondo argomento.

Quindi verifica la posizione del delimitatore utilizzando la funzione INSTR in VBA. Questa posizione viene poi utilizzata per estrarre tutti i caratteri prima del delimitatore (usando la funzione LEFT).

Infine, assegna il risultato alla funzione.

Questa formula è tutt'altro che perfetta. Ad esempio, se inserisci un delimitatore che non si trova nel testo, darebbe un errore. Ora puoi utilizzare la funzione SE ERRORE nel foglio di lavoro per eliminare gli errori oppure puoi utilizzare il codice seguente che restituisce l'intero testo quando non riesce a trovare il delimitatore.

Funzione GetDataBeforeDelimiter(CellRef As Range, Delim As String) as String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 If DelimPosition < 0 Then DelimPosition = Len(CellRef) Risultato = Left( CellRef, DelimPosition) GetDataBeforeDelimiter = Funzione di fine risultato

Possiamo ottimizzare ulteriormente questa funzione.

Se inserisci il testo (da cui vuoi estrarre la parte prima del delimitatore) direttamente nella funzione, ti darebbe un errore. Vai avanti… provalo!

Ciò accade poiché abbiamo specificato "CellRef" come tipo di dati di intervallo.

Oppure, se vuoi che il delimitatore sia in una cella e usi il riferimento di cella invece di codificarlo nella formula, non puoi farlo con il codice sopra. È perché il Delim è stato dichiarato come un tipo di dati stringa.

Se si desidera che la funzione abbia la flessibilità di accettare input di testo diretto o riferimenti di cella dall'utente, è necessario rimuovere la dichiarazione del tipo di dati. Ciò finirebbe per rendere l'argomento un tipo di dati variante, che può accettare qualsiasi tipo di argomento ed elaborarlo.

Il codice seguente farebbe questo:

Funzione GetDataBeforeDelimiter(CellRef, Delim) As String Dim Risultato As String Dim DelimPosition As Integer DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 Se DelimPosition < 0 Then DelimPosition = Len(CellRef) Risultato = Left(CellRef), DelimPosition GetDataBeforeDelimiter = Funzione di fine risultato

Creazione di una funzione in VBA con argomenti facoltativi

Esistono molte funzioni in Excel in cui alcuni argomenti sono facoltativi.

Ad esempio, la leggendaria funzione VLOOKUP ha 3 argomenti obbligatori e un argomento facoltativo.

Un argomento facoltativo, come suggerisce il nome, è facoltativo da specificare. Se non specifichi uno degli argomenti obbligatori, la tua funzione ti darà un errore, ma se non specifichi l'argomento facoltativo, la tua funzione funzionerà.

Ma gli argomenti facoltativi non sono inutili. Ti permettono di scegliere tra una gamma di opzioni.

Ad esempio, nella funzione CERCA.VERT, se non si specifica il quarto argomento, CERCA.VERT esegue una ricerca approssimativa e se si specifica l'ultimo argomento come FALSO (o 0), viene eseguita una corrispondenza esatta.

Ricorda che gli argomenti opzionali devono sempre venire dopo tutti gli argomenti richiesti. Non puoi avere argomenti facoltativi all'inizio.

Vediamo ora come creare una funzione in VBA con argomenti facoltativi.

Funzione con solo un argomento opzionale

Per quanto ne so, non esiste una funzione incorporata che accetta solo argomenti opzionali (posso sbagliarmi qui, ma non riesco a pensare a nessuna di queste funzioni).

Ma possiamo crearne uno con VBA.

Di seguito è riportato il codice della funzione che ti darà la data corrente nel formato gg-mm-aaaa se non inserisci alcun argomento (cioè lascialo vuoto), e nel formato "gg mmmm, aaaa" se inserisci qualcosa come argomento (cioè, qualsiasi cosa in modo che l'argomento non sia vuoto).

Funzione CurrDate(Optional fmt As Variant) Dim Result If IsMissing(fmt) Then CurrDate = Format(Date, "dd-mm-yyyy") Else CurrDate = Format(Date, "dd mmmm, yyyy") End If End Funzione

Nota che la funzione sopra usa "IsMissing" per verificare se l'argomento è mancante o meno. Per utilizzare la funzione IsMissing, l'argomento facoltativo deve essere del tipo di dati variant.

La funzione sopra funziona indipendentemente da ciò che inserisci come argomento. Nel codice, controlliamo solo se l'argomento opzionale è fornito o meno.

Puoi renderlo più robusto prendendo solo valori specifici come argomenti e mostrando un errore nel resto dei casi (come mostrato nel codice seguente).

Funzione CurrDate(Optional fmt As Variant) Dim Result If IsMissing(fmt) Then CurrDate = Format(Date, "dd-mm-yyyy") ElseIf fmt = 1 Then CurrDate = Format(Date, "dd mmmm, yyyy") Else CurrDate = CVErr(xlErrValue) End If End Function

Il codice sopra crea una funzione che mostra la data nel formato "gg-mm-aaaa" se non viene fornito alcun argomento e nel formato "gg mmmm,aaaa" quando l'argomento è 1. Dà un errore in tutti gli altri casi.

Funzione con argomenti obbligatori e facoltativi

Abbiamo già visto un codice che estrae la parte numerica da una stringa.

Ora diamo un'occhiata a un esempio simile che accetta argomenti sia obbligatori che facoltativi.

Il codice seguente crea una funzione che estrae la parte di testo da una stringa. Se l'argomento facoltativo è VERO, fornisce il risultato in maiuscolo e se l'argomento facoltativo è FALSO o viene omesso, fornisce il risultato così com'è.

Funzione GetText(CellRef As Range, Optional TextCase = False) As String Dim StringLength As Integer Dim Risultato As String StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Risultato = Risultato & Metà (RifCell, i, 1) Avanti i Se CasoTesto = Vero Allora Risultato = UCase(Risultato) GetText = Funzione Fine Risultato

Nota che nel codice sopra, abbiamo inizializzato il valore di "TextCase" come False (guarda tra parentesi nella prima riga).

In questo modo, ci siamo assicurati che l'argomento facoltativo inizi con il valore predefinito, che è FALSE. Se l'utente specifica il valore come TRUE, la funzione restituisce il testo in maiuscolo e se l'utente specifica l'argomento facoltativo come FALSE o lo omette, il testo restituito è così com'è.

Creazione di una funzione in VBA con un array come argomento

Finora abbiamo visto esempi di creazione di una funzione con argomenti opzionali/richiesti, in cui questi argomenti erano un singolo valore.

Puoi anche creare una funzione che possa accettare un array come argomento. Nelle funzioni del foglio di lavoro di Excel, ci sono molte funzioni che accettano argomenti di matrice, come SOMMA, CERCA.VERT, SOMMA.SE, CONTA.SE, ecc.

Di seguito è riportato il codice che crea una funzione che fornisce la somma di tutti i numeri pari nell'intervallo di celle specificato.

Funzione AddEven(CellRef as Range) Dim Cell As Range Per ogni cella in CellRef If IsNumeric(Cell.Value) Then If Cell.Value Mod 2 = 0 Then Result = Risultato + Cell.Value End If End If Next Cell AddEven = Risultato End Funzione

Puoi usare questa funzione nel foglio di lavoro e fornire l'intervallo di celle che hanno i numeri come argomento. La funzione restituirebbe un singolo valore, la somma di tutti i numeri pari (come mostrato di seguito).

Nella funzione sopra, invece di un singolo valore, abbiamo fornito un array (A1:A10). Perché funzioni, è necessario assicurarsi che il tipo di dati dell'argomento possa accettare un array.

Nel codice sopra, ho specificato l'argomento CellRef come Range (che può accettare un array come input). Puoi anche utilizzare il tipo di dati variante qui.

Nel codice, c'è un ciclo For Each che passa attraverso ogni cella e controlla se è un numero di no. In caso contrario, non succede nulla e si sposta alla cella successiva. Se è un numero, verifica se è pari o meno (utilizzando la funzione MOD).

Alla fine si sommano tutti i numeri pari e si riassegna la somma alla funzione.

Creazione di una funzione con un numero indefinito di argomenti

Durante la creazione di alcune funzioni in VBA, potresti non conoscere il numero esatto di argomenti che un utente desidera fornire. Quindi la necessità è creare una funzione che possa accettare tanti argomenti forniti e usarli per restituire il risultato.

Un esempio di tale funzione del foglio di lavoro è la funzione SOMMA. Puoi fornire più argomenti ad esso (come questo):

=SOMMA(A1,A2:A4,B1:B20)

La funzione sopra dovrebbe aggiungere i valori in tutti questi argomenti. Inoltre, tieni presente che possono essere una singola cella o un array di celle.

Puoi creare una tale funzione in VBA avendo l'ultimo argomento (o potrebbe essere l'unico argomento) come facoltativo. Inoltre, questo argomento facoltativo dovrebbe essere preceduto dalla parola chiave "ParamArray".

'ParamArray' è un modificatore che ti consente di accettare tutti gli argomenti che desideri. Si noti che l'uso della parola ParamArray prima di un argomento rende l'argomento facoltativo. Tuttavia, non è necessario utilizzare la parola Facoltativo qui.

Ora creiamo una funzione che può accettare un numero arbitrario di argomenti e aggiungere tutti i numeri negli argomenti specificati:

Funzione AddArguments(ParamArray arglist() As Variant) For Each arg In arglist AddArguments = AddArguments + arg Next arg End Function

La funzione sopra può prendere qualsiasi numero di argomenti e aggiungere questi argomenti per dare il risultato.

Tieni presente che puoi utilizzare solo un singolo valore, un riferimento di cella, un valore booleano o un'espressione come argomento. Non puoi fornire un array come argomento. Ad esempio, se uno dei tuoi argomenti è D8: D10, questa formula ti darebbe un errore.

Se vuoi essere in grado di utilizzare entrambi gli argomenti multi-cella, devi utilizzare il codice seguente:

Funzione AddArguments(ParamArray arglist() As Variant) Per ogni arg In arglist Per ogni cella in arg AddArguments = AddArguments + Cell Next Cell Next arg End Function

Tieni presente che questa formula funziona con più celle e riferimenti a matrice, tuttavia, non può elaborare valori o espressioni hardcoded. Puoi creare una funzione più robusta controllando e trattando queste condizioni, ma non è questo l'intento qui.

L'intento qui è di mostrarti come funziona ParamArray in modo da poter consentire un numero indefinito di argomenti nella funzione. Nel caso in cui desideri una funzione migliore di quella creata dal codice sopra, usa la funzione SUM nel foglio di lavoro.

Creazione di una funzione che restituisce un array

Finora abbiamo visto funzioni che restituiscono un singolo valore.

Con VBA, puoi creare una funzione che restituisce una variante che può contenere un intero array di valori.

Le formule di matrice sono disponibili anche come funzioni integrate nei fogli di lavoro di Excel. Se hai familiarità con le formule di matrice in Excel, saprai che queste vengono immesse utilizzando Ctrl + Maiusc + Invio (anziché solo Invio). Puoi leggere di più sulle formule di matrice qui. Se non conosci le formule di matrice, non preoccuparti, continua a leggere.

Creiamo una formula che restituisca un array di tre numeri (1,2,3).

Il codice seguente farebbe questo.

Funzione ThreeNumbers() As Variant Dim NumberValue(1 To 3) NumberValue(1) = 1 NumberValue(2) = 2 NumberValue(3) = 3 ThreeNumbers = NumberValue End Function

Nel codice sopra, abbiamo specificato la funzione "TreNumeri" come variante. Ciò gli consente di contenere un array di valori.

La variabile 'NumberValue' è dichiarata come un array con 3 elementi. Contiene i tre valori e lo assegna alla funzione "TreNumeri".

Puoi utilizzare questa funzione nel foglio di lavoro inserendo la funzione e premendo il tasto Ctrl + Maiusc + Invio (tieni premuti i tasti Ctrl e Maiusc e poi premi Invio).

Quando lo fai, restituirà 1 nella cella, ma in realtà contiene tutti e tre i valori. Per verificarlo, usa la formula seguente:

=MAX(TreNumeri())

Usa la funzione sopra con Ctrl + Maiusc + Invio. Noterai che il risultato ora è 3, poiché sono i valori più grandi nell'array restituito dalla funzione Max, che ottiene i tre numeri come risultato della nostra funzione definita dall'utente - ThreeNumbers.

Puoi utilizzare la stessa tecnica per creare una funzione che restituisca una matrice di nomi di mesi come mostrato dal codice seguente:

Funzione Mesi() As Variant Dim NomeMese(da 1 a 12) NomeMese(1) = "Gennaio" NomeMese(2) = "Febbraio" NomeMese(3) = "Marzo" NomeMese(4) = "Aprile" NomeMese(5) = "Maggio" NomeMese(6) = "Giugno" NomeMese(7) = "Luglio" NomeMese(8) = "Agosto" NomeMese(9) = "Settembre" NomeMese(10) = "Ottobre" NomeMese(11) = "Novembre " NomeMese(12) = "Dicembre" Mesi = Funzione Fine NomeMese

Ora quando inserisci la funzione = Mesi () nel foglio di lavoro di Excel e usi Ctrl + Maiusc + Invio, restituirà l'intera matrice di nomi dei mesi. Nota che vedi solo gennaio nella cella poiché questo è il primo valore nell'array. Ciò non significa che l'array restituisca solo un valore.

Per mostrarti il ​​fatto che sta restituendo tutti i valori, fai questo: seleziona la cella con la formula, vai alla barra della formula, seleziona l'intera formula e premi F9. Questo ti mostrerà tutti i valori che la funzione restituisce.

Puoi usarlo usando la formula INDICE sottostante per ottenere un elenco di tutti i nomi dei mesi in una volta sola.

=INDICE(Mesi(),RIGA())

Ora, se hai molti valori, non è una buona pratica assegnare questi valori uno per uno (come abbiamo fatto sopra). Invece, puoi usare la funzione Array in VBA.

Quindi lo stesso codice in cui creiamo la funzione "Mesi" diventerebbe più breve come mostrato di seguito:

Funzione Months() As Variant Months = Array("Gennaio", "Febbraio", "Marzo", "Aprile", "Maggio", "Giugno", _ "Luglio", "Agosto", "Settembre", "Ottobre" , "Novembre", "Dicembre") Fine Funzione

La funzione precedente utilizza la funzione Array per assegnare i valori direttamente alla funzione.

Nota che tutte le funzioni create sopra restituiscono un array orizzontale di valori. Ciò significa che se selezioni 12 celle orizzontali (diciamo A1: L1) e inserisci la formula = Mesi () nella cella A1, ti darà tutti i nomi dei mesi.

Ma cosa succede se vuoi questi valori in un intervallo verticale di celle.

Puoi farlo usando la formula TRANSPOSE nel foglio di lavoro.

Seleziona semplicemente 12 celle verticali (contigue) e inserisci la formula seguente.

Comprensione dell'ambito di una funzione definita dall'utente in Excel

Una funzione può avere due ambiti: Pubblico o Privato.

• UN Ambito pubblico significa che la funzione è disponibile per tutti i fogli della cartella di lavoro e per tutte le procedure (Sub e Function) in tutti i moduli della cartella di lavoro. Questo è utile quando vuoi chiamare una funzione da una subroutine (vedremo come farlo nella prossima sezione).
• UN ambito privato significa che la funzione è disponibile solo nel modulo in cui esiste. Non puoi usarlo in altri moduli. Inoltre, non lo vedrai nell'elenco delle funzioni nel foglio di lavoro. Ad esempio, se il nome della tua funzione è "Mesi ()" e digiti funzione in Excel (dopo il segno =), non ti mostrerà il nome della funzione. Tuttavia, puoi ancora usarlo se inserisci il nome della formula.

Se non specifichi nulla, la funzione è una funzione pubblica per impostazione predefinita.

Di seguito è riportata una funzione che è una funzione Privata:

Private Function WorkbookName() As String WorkbookName = ThisWorkbook.Name End Function

Puoi usare questa funzione nei sottoprogrammi e nelle procedure negli stessi moduli, ma non puoi usarla in altri moduli. Anche questa funzione non verrebbe visualizzata nel foglio di lavoro.

Il codice seguente renderebbe pubblica questa funzione. Verrà visualizzato anche nel foglio di lavoro.

Function WorkbookName() As String WorkbookName = ThisWorkbook.Name End Function

Diversi modi di utilizzare una funzione definita dall'utente in Excel

Dopo aver creato una funzione definita dall'utente in VBA, puoi utilizzarla in molti modi diversi.

Vediamo prima come utilizzare le funzioni nel foglio di lavoro.

Utilizzo di UDF nei fogli di lavoro

Abbiamo già visto esempi di utilizzo di una funzione creata in VBA nel foglio di lavoro.

Tutto quello che devi fare è inserire il nome della funzione e viene visualizzato in intellisense.

Si noti che affinché la funzione venga visualizzata nel foglio di lavoro, deve essere una funzione pubblica (come spiegato nella sezione precedente).

È inoltre possibile utilizzare la finestra di dialogo Inserisci funzione per inserire la funzione definita dall'utente (usando i passaggi seguenti). Funzionerebbe solo per le funzioni pubbliche.

• Vai alla scheda Dati.
• Fare clic sull'opzione "Inserisci funzione".
• Nella finestra di dialogo Inserisci funzione, selezionare Definito dall'utente come categoria. Questa opzione viene visualizzata solo quando hai una funzione nell'editor VB (e la funzione è Pubblica).
• Selezionare la funzione dall'elenco di tutte le funzioni definite dall'utente pubblico.
• Fare clic sul pulsante Ok.

I passaggi precedenti inserirebbero la funzione nel foglio di lavoro. Visualizza anche una finestra di dialogo Argomenti funzione che fornisce dettagli sugli argomenti e sul risultato.

È possibile utilizzare una funzione definita dall'utente proprio come qualsiasi altra funzione in Excel. Ciò significa anche che puoi usarlo con altre funzioni di Excel integrate. Per esempio. la formula seguente darebbe il nome della cartella di lavoro in maiuscolo:

=MAIUSC(Nomecartella di lavoro())

Utilizzo delle funzioni definite dall'utente nelle procedure e funzioni VBA

Una volta creata una funzione, è possibile utilizzarla anche in altre sottoprocedure.

Se la funzione è Public, può essere utilizzata in qualsiasi procedura nello stesso modulo o in un altro. Se è Privato, può essere utilizzato solo nello stesso modulo.

Di seguito è una funzione che restituisce il nome della cartella di lavoro.

Function WorkbookName() As String WorkbookName = ThisWorkbook.Name End Function

La procedura seguente chiama la funzione e quindi visualizza il nome in una finestra di messaggio.

Sub ShowWorkbookName() MsgBox WorkbookName End Sub

Puoi anche chiamare una funzione da un'altra funzione.

Nei codici seguenti, il primo codice restituisce il nome della cartella di lavoro e il secondo restituisce il nome in maiuscolo chiamando la prima funzione.

Function WorkbookName() As String WorkbookName = ThisWorkbook.Name End Function

Funzione WorkbookNameinUpper() WorkbookNameinUpper = UCase(WorkbookName) End Function

Chiamare una funzione definita dall'utente da altre cartelle di lavoro

Se hai una funzione in una cartella di lavoro, puoi chiamare questa funzione anche in altre cartelle di lavoro.

Ci sono diversi modi per farlo:

1. Creazione di un componente aggiuntivo
2. Funzione di salvataggio nella cartella di lavoro delle macro personali
3. Riferimento alla funzione da un'altra cartella di lavoro.

Creazione di un componente aggiuntivo

Creando e installando un componente aggiuntivo, avrai la funzione personalizzata disponibile in tutte le cartelle di lavoro.

Supponiamo di aver creato una funzione personalizzata - "GetNumeric" e di volerla in tutte le cartelle di lavoro. Per fare ciò, crea una nuova cartella di lavoro e inserisci il codice della funzione in un modulo in questa nuova cartella di lavoro.

Ora segui i passaggi seguenti per salvarlo come componente aggiuntivo e quindi installarlo in Excel.

• Vai alla scheda File e fai clic su Salva con nome.
• Nella finestra di dialogo Salva con nome, cambia il tipo "Salva con nome" in .xlam. Il nome che assegni al file sarà il nome del tuo componente aggiuntivo. In questo esempio, il file viene salvato con il nome GetNumeric.
  • Noterai che il percorso del file in cui viene salvato cambia automaticamente. Puoi usare quello predefinito o cambiarlo se vuoi.
• Apri una nuova cartella di lavoro di Excel e vai alla scheda Sviluppatore.
• Fare clic sull'opzione Componenti aggiuntivi di Excel.
• Nella finestra di dialogo Componenti aggiuntivi, sfoglia e individua il file che hai salvato e fai clic su OK.

Ora il componente aggiuntivo è stato attivato.

Ora puoi utilizzare la funzione personalizzata in tutte le cartelle di lavoro.

Salvataggio della funzione nella cartella di lavoro delle macro personali

Una cartella di lavoro macro personale è una cartella di lavoro nascosta nel sistema che si apre ogni volta che si apre l'applicazione Excel.

È un luogo in cui è possibile memorizzare codici macro e quindi accedere a queste macro da qualsiasi cartella di lavoro. È un ottimo posto per memorizzare quelle macro che vuoi usare spesso.

Per impostazione predefinita, non esiste una cartella di lavoro macro personale nel tuo Excel. È necessario crearlo registrando una macro e salvandolo nella cartella di lavoro delle macro personali.

Puoi trovare i passaggi dettagliati su come creare e salvare le macro nella cartella di lavoro macro personale qui.

Riferimento alla funzione da un'altra cartella di lavoro

Sebbene i primi due metodi (creazione di un componente aggiuntivo e utilizzo di una cartella di lavoro macro personale) funzionino in tutte le situazioni, se si desidera fare riferimento alla funzione da un'altra cartella di lavoro, quella cartella di lavoro deve essere aperta.

Supponiamo di avere una cartella di lavoro con il nome "Quaderno con Formula', e ha la funzione con il nome 'Ottieni Numerico'.

Per utilizzare questa funzione in un'altra cartella di lavoro (mentre il Cartella di lavoro con formula è aperto), è possibile utilizzare la formula seguente:

='Cartella di lavoro con formula'!GetNumeric(A1)

La formula sopra utilizzerà la funzione definita dall'utente nel Cartella di lavoro con formula file e ti do il risultato.

Tieni presente che poiché il nome della cartella di lavoro contiene spazi, è necessario racchiuderlo tra virgolette singole.

Utilizzo dell'istruzione della funzione Exit VBA

Se vuoi uscire da una funzione mentre il codice è in esecuzione, puoi farlo usando l'istruzione 'Exit Function'.

Il codice seguente estrae i primi tre caratteri numerici da una stringa di testo alfanumerica. Non appena ottiene i tre caratteri, la funzione termina e restituisce il risultato.

Funzione GetNumericFirstThree(CellRef As Range) As Long Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If J = 3 Then Exit Function If IsNumeric(Mid(CellRef, i, 1)) Then J = J + 1 Risultato = Risultato e Metà (RifCell, i, 1) GetNumericFirstThree = Risultato End If Next i End Function

La funzione sopra controlla il numero di caratteri numerici e quando ottiene 3 caratteri numerici, esce dalla funzione nel ciclo successivo.

Debug di una funzione definita dall'utente

Esistono alcune tecniche che puoi utilizzare durante il debug di una funzione definita dall'utente in VBA:

Debug di una funzione personalizzata utilizzando la finestra dei messaggi

Usa la funzione MsgBox per mostrare una finestra di messaggio con un valore specifico.

Il valore visualizzato può essere basato su ciò che si desidera testare. Ad esempio, se si desidera verificare se il codice viene eseguito o meno, qualsiasi messaggio funzionerà e se si desidera verificare se i cicli funzionano o meno, è possibile visualizzare un valore specifico o il contatore dei cicli.

Debug di una funzione personalizzata impostando il punto di interruzione

Imposta un punto di interruzione per poter passare attraverso ciascuna riga una alla volta. Per impostare un punto di interruzione, selezionare la riga dove si desidera e premere F9, oppure fare clic sull'area verticale grigia a sinistra delle righe di codice. Ognuno di questi metodi inserirebbe un punto di interruzione (vedrai un punto rosso nell'area grigia).

Una volta impostato il punto di interruzione ed eseguita la funzione, questa va fino alla linea del punto di interruzione e poi si ferma. Ora puoi scorrere il codice usando il tasto F8. Premendo F8 una volta si passa alla riga successiva nel codice.

Debug di una funzione personalizzata utilizzando Debug.Print nel codice

È possibile utilizzare l'istruzione Debug.Print nel codice per ottenere i valori delle variabili/argomenti specificati nella finestra immediata.

Ad esempio, nel codice seguente, ho utilizzato Debug.Print per ottenere il valore di due variabili: "j" e "Risultato"

Funzione GetNumericFirstThree(CellRef As Range) As Long Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If J = 3 Quindi esci dalla funzione If IsNumeric(Mid(CellRef, i, 1)) Then J = J + 1 Risultato = Risultato & Metà(RifCell, i, 1) Debug.Stampa J, Risultato GetNumericFirstThree = Risultato End If Next i End Function

Quando questo codice viene eseguito, mostra quanto segue nella finestra immediata.

Funzioni integrate di Excel vs. Funzione VBA definita dall'utente

Ci sono alcuni forti vantaggi nell'usare le funzioni integrate di Excel rispetto alle funzioni personalizzate create in VBA.

• Le funzioni integrate sono molto più veloci delle funzioni VBA.
• Quando crei un report/dashboard utilizzando le funzioni VBA e lo invii a un cliente/collega, non dovrebbero preoccuparsi se le macro sono abilitate o meno. In alcuni casi, i clienti/clienti si spaventano vedendo un avviso nella barra gialla (che chiede semplicemente loro di abilitare le macro).
• Con le funzioni Excel integrate, non devi preoccuparti delle estensioni dei file. Se nella cartella di lavoro sono presenti macro o funzioni definite dall'utente, è necessario salvarle in .xlsm.

Sebbene ci siano molti validi motivi per utilizzare le funzioni integrate di Excel, in alcuni casi è meglio utilizzare una funzione definita dall'utente.

• È meglio usare una funzione definita dall'utente se la tua formula incorporata è enorme e complicata. Ciò diventa ancora più rilevante quando è necessario che qualcun altro aggiorni le formule. Ad esempio, se hai una formula enorme composta da molte funzioni diverse, anche cambiare un riferimento a una cella può essere noioso e soggetto a errori. Invece, puoi creare una funzione personalizzata che accetta solo uno o due argomenti e fa tutto il lavoro pesante sul back-end.
• Quando devi fare qualcosa che non può essere fatto dalle funzioni integrate di Excel. Un esempio di ciò può essere quando si desidera estrarre tutti i caratteri numerici da una stringa. In tali casi, il vantaggio di utilizzare una funzione gar definita dall'utente supera i suoi aspetti negativi.

Dove inserire il codice VBA per una funzione definita dall'utente

Quando si crea una funzione personalizzata, è necessario inserire il codice nella finestra del codice per la cartella di lavoro in cui si desidera la funzione.

Di seguito sono riportati i passaggi per inserire il codice per la funzione "GetNumeric" nella cartella di lavoro.

1. Vai alla scheda Sviluppatore.
2. Fare clic sull'opzione Visual Basic. Questo aprirà l'editor VB nel backend.
3. Nel riquadro Project Explorer nell'editor VB, fare clic con il pulsante destro del mouse su qualsiasi oggetto per la cartella di lavoro in cui si desidera inserire il codice. Se non vedi Esplora progetti, vai alla scheda Visualizza e fai clic su Esplora progetti.
4. Vai su Inserisci e fai clic su Modulo. Questo inserirà un oggetto modulo per la tua cartella di lavoro.
5. Copia e incolla il codice nella finestra del modulo.

Potrebbero piacerti anche i seguenti tutorial VBA di Excel:

• Lavorare con celle e intervalli in Excel VBA.
• Lavorare con i fogli di lavoro in Excel VBA.
• Lavorare con le cartelle di lavoro utilizzando VBA.
• Come utilizzare i loop in Excel VBA.
• Eventi VBA di Excel: una guida semplice (e completa)
• Utilizzo delle istruzioni IF Then Else in VBA.
• Come registrare una macro in Excel.
• Come eseguire una macro in Excel.
• Come ordinare i dati in Excel utilizzando VBA (una guida passo passo).
• Funzione Excel VBA InStr - Spiegata con esempi.