Come creare una funzione definita dall'utente in vba?

Probabilmente uno dei vantaggi più interessanti dell’apprendimento del VBA è la capacità di creare le proprie funzioni.

In Excel ci sono più di 450 funzioni e alcune di esse sono molto utili nel lavoro quotidiano. Ma Excel ti dà la possibilità di creare una funzione personalizzata utilizzando VBA. Si hai ragione. Funzione DEFINITA DALL’UTENTE, abbreviata in UDF, oppure puoi anche chiamarla funzione VBA personalizzata.

E c’è una cosa che posso dire con sicurezza: ogni utente VBA in erba vuole imparare come creare una funzione definita dall’utente. Non tu? Dì “Sì” nella sezione commenti, se sei una di quelle persone che vogliono creare una funzionalità personalizzata.

Sono felice di dirti che questa è una GUIDA COMPLETA per aiutarti a creare la tua prima funzione personalizzata utilizzando VBA e, a parte questo, ho condiviso alcuni esempi di FUNZIONI DEFINITE DALL’UTENTE per aiutarti a ispirarti.

1. Qui utilizzerò le parole funzione definita dall’utente, funzione personalizzata e UDF in modo intercambiabile. Quindi resta con me, diventerai una rock star VBA nei prossimi minuti.
2. Per creare il codice per la funzione personalizzata VBA è necessario scriverlo, non è possibile registrarlo utilizzando il registratore di macro .

Perché dovresti creare una funzione Excel personalizzata

Come ho detto, in Excel sono presenti molte funzioni integrate che possono aiutarti a risolvere quasi tutti i problemi ed eseguire tutti i tipi di calcoli. Ma a volte in situazioni specifiche è necessario creare una UDF.

E di seguito ho elencato alcuni dei motivi o delle situazioni in cui dovresti utilizzare una funzione personalizzata.

1. Quando non è presente alcuna funzione per questo

Questo è uno dei motivi più comuni per creare una UDF con VBA, perché a volte è necessario calcolare qualcosa e non esiste una funzione specifica per questo. Posso darti un esempio di conteggio delle parole da una cella e per questo ho scoperto che un UDF può essere una soluzione perfetta.

2. Sostituisci una formula complessa

Se lavori con le formule, sono sicuro che sai che le formule complesse sono difficili da leggere e talvolta più difficili da comprendere per gli altri. Quindi, una funzione personalizzata può essere una soluzione a questo problema perché una volta creata una UDF, non è necessario scrivere questa formula complessa ancora e ancora.

3. Quando non desideri utilizzare la routine SUB

Sebbene sia possibile utilizzare il codice VBA per eseguire un calcolo, i codici VBA non sono dinamici*. È necessario eseguire nuovamente questo codice se si desidera aggiornare il calcolo. Ma se converti questo codice in una funzione, non è necessario eseguirlo più e più volte perché puoi semplicemente inserirlo come funzione.

Come creare la tua prima funzione definita dall’utente in Excel

Ok, allora guarda. Ho diviso l’intero processo in tre fasi:

1. Dichiara la tua procedura come una funzione
2. Definire i suoi argomenti e il relativo tipo di dati
3. Aggiungi il codice per calcolare il valore desiderato

Ma lascia che ti dia:

È necessario creare una funzione in grado di restituire il nome del giorno da un valore di data. Bene, abbiamo una funzione che restituisce il numero del giorno della settimana ma non il nome. Capisci quello che sto dicendo? SÌ?

Quindi, seguiamo i passaggi seguenti per creare la prima funzione definita dall’utente:

1. Prima di tutto, apri il tuo editor Visual Basic utilizzando la scorciatoia da tastiera ALT + F11 oppure vai alla scheda Sviluppatore e fai semplicemente clic sul pulsante “Visual Basic”.
   
2. La prossima cosa da fare è inserire un modulo, quindi fare clic con il pulsante destro del mouse sulla finestra del progetto VBA, quindi andare su Inserisci, quindi fare clic su “Modulo”. (AVVISO: è necessario inserire una FUNZIONE DEFINITA DALL’UTENTE solo nei moduli standard. Entrambi i moduli Foglio e Questa cartella di lavoro sono un tipo speciale di modulo e se si immette una UDF in questi due moduli, Excel non riconosce che si sta creando una UDF) .
   
3. La terza cosa è definire un nome per la funzione e qui sto usando “myDayName”. Quindi devi scrivere “Funzione mydayName”. Perché la funzione prima del nome? Quando crei una funzione VBA, l’utilizzo della parola “Funzione” indica a Excel di trattare questo codice come una funzione (assicurati di leggere l’ambito di una UDF prima nel messaggio).
4. Dopodiché devi definire gli argomenti per la tua funzione. Quindi inserisci le parentesi iniziali e scrivi “InputDate As Date”. Qui, InputDate è il nome dell’argomento e la data è il suo tipo di dati. È sempre meglio definire un tipo di dati per l’argomento.
   
5. Ora chiudi le parentesi e scrivi “As String”. Qui definisci il tipo di dati del risultato restituito dalla funzione e come vuoi il nome del giorno che è un testo, quindi il suo tipo di dati deve essere “String”. Se vuoi che il risultato sia qualcosa di diverso da una stringa, assicurati di impostare il tipo di dati di conseguenza. (funzione myDayName(InputDate As Date) As String).
   
6. Al termine premere INVIO. A questo punto, il nome della funzione, il suo argomento, il tipo di dati dell’argomento e il tipo di dati della funzione sono impostati e nel tuo modulo hai qualcosa di simile di seguito:
   
7. Ora in “Funzione” e “Funzione finale” è necessario definire il calcolo oppure si può dire il funzionamento di questa UDF. In Excel esiste una funzione del foglio di lavoro chiamata “Testo” e qui utilizziamo la stessa. E per questo devi scrivere il codice qui sotto e con questo codice definisci il valore che dovrebbe essere restituito dalla funzione. mioGiornoNome = WorksheetFunction.Text(InputDate, “dddddd”)
   
8. Ora chiudi il tuo editor VB e torna al foglio di lavoro e nella cella B2 inserisci “=myDayName(A2)” premi Invio e avrai il nome del giorno.
   

Congratulazioni! Hai appena creato la tua prima funzione definita dall’utente. Questo è il momento della vera Gioia. Non è vero? Digita “Gioia” nella sezione commenti.

Come funziona questa funzione e restituisce il valore in una cella

La tua prima funzione personalizzata è qui, ma il punto è che devi capire come funziona. Se dico in termini semplici è codice VBA ma lo hai usato come procedura di funzione. Dividiamolo in tre parti:

• Lo inserisci in una cella come funzione e specifichi il valore di input.
• Excel esegue il codice dietro la funzione e utilizza il valore a cui fai riferimento.
• Hai il risultato nella cella.

Ma devi capire come funziona questa funzione dall’interno. Quindi ho diviso l’intero processo in tre parti diverse in cui puoi vedere come funziona effettivamente il codice che hai scritto per la funzione.

Poiché hai specificato “InputDate” come argomento della funzione e quando inserisci la funzione nella cella e specifichi una data, VBA prende quel valore di data e lo fornisce alla funzione di testo che hai utilizzato nel codice.

E nell’esempio che ho menzionato sopra, la data che hai nella cella A1 è 01-gen-2019.

Successivamente, la funzione TESTO converte questa data in un giorno utilizzando il codice di formato “dddddd” che hai già menzionato nel codice della funzione. E questo giorno restituito dalla funzione TEXT viene assegnato a “myDayName”.

Quindi, se il risultato della funzione TEXT è martedì, questo valore verrà assegnato a “myDayName”.

E qui il funzionamento della funzione termina. “myDayName” è il nome della funzione, quindi qualsiasi valore assegnato a “myDayName” sarà il valore del risultato e la funzione inserita nel foglio di lavoro lo restituirà nella cella.

Quando scrivi il codice per una funzione personalizzata, devi assicurarti che il valore restituito da quel codice sia assegnato al nome della funzione.

Come migliorare una UDF per sempre

Bene, sai come creare una funzione VBA personalizzata.

ORA…

C’è una cosa di cui devi assicurarti che il codice su cui hai lavorato sia abbastanza buono da gestire tutte le possibilità. Se stai parlando della funzione che hai appena scritto sopra, puoi restituire il nome del giorno da una data.

Scopo…

Cosa succede se il valore specificato non è una data? Cosa succede se la cella a cui ti riferisci è vuota? Potrebbero esserci altre possibilità, ma sono sicuro che capirai il mio punto.

GIUSTO? Quindi proviamo a migliorare questa funzione personalizzata che potrebbe essere in grado di risolvere i problemi di cui sopra. BENE. Innanzitutto, devi modificare il tipo di dati dell’argomento e utilizzare:

 InputDate As Variant

Con questo, la tua funzione personalizzata può accettare qualsiasi tipo di dato come input. Successivamente, dobbiamo utilizzare l’istruzione VBA IF per verificare la presenza di determinate condizioni in InputDate. La prima condizione è se la cella è vuota o meno. E per questo, devi utilizzare il codice seguente:

 If InputDate = "" Then myDayName = ""

Ciò renderà la funzione vuota se la cella a cui fai riferimento è vuota.

Risolto un problema, passiamo al successivo. Oltre a una data, potresti avere un numero o un testo. Quindi per questo devi anche creare una condizione che dovrebbe verificare se il valore di riferimento è una data reale o meno.

Il codice sarebbe:

 If IsDate(InputDate) = False Then myDateName = ""

Qui sto utilizzando uno spazio vuoto per entrambe le condizioni in modo che se disponi di dati di grandi dimensioni puoi facilmente filtrare i valori in cui il valore di input non è valido. Quindi, dopo aver aggiunto le condizioni di cui sopra, il codice sarebbe simile a:

 Function myDayName(InputDate As Variant) As String If InputDate = "" Then myDayName = "" Else If IsDate(InputDate) = False Then myDateName = "" Else myDayName = WorksheetFunction.Text(InputDate, "dddddd") End If End If End Function

Ed ecco come funziona ora: sono sicuro che puoi ancora apportare modifiche a questa funzione, ma sono sicuro che capirai chiaramente il mio punto.

Come utilizzare una funzione VBA personalizzata

A questo punto, sai approssimativamente come creare una funzione VBA in Excel. Ma una volta che lo hai, devi sapere come puoi usarlo. E in questa parte del post condividerò con te come e dove puoi usarlo. Quindi, entriamo.

1. Semplicemente in un foglio di calcolo

Perché stiamo creando una funzione personalizzata? Semplice. Per usarlo nel foglio di calcolo. Puoi semplicemente inserire una UDF in un foglio di calcolo utilizzando il segno di uguale e il nome del tipo di funzione, quindi specificarne gli argomenti.

È inoltre possibile immettere una funzione definita dall’utente dalla libreria delle funzioni. Andare alla scheda Formula ➜ Inserisci funzione ➜ Definito dall’utente.

Da questa lista puoi scegliere la UDF che vuoi inserire.

2. Utilizzo di altre sottoprocedure e funzioni

È inoltre possibile utilizzare una funzione all’interno di altre funzioni o in una procedura “Sub”. Di seguito è riportato un codice VBA in cui hai utilizzato la funzione per ottenere il nome del giorno per la data corrente.

 Sub todayDay() MsgBox "Today is " & myDayName(Date) End Sub

Assicurarsi di leggere “Ambito di una UDF” più avanti in questo articolo per ulteriori informazioni sull’utilizzo di una funzione in altre procedure.

3. Accedere alle funzioni da un’altra cartella di lavoro

Se in una cartella di lavoro è presente una UDF e si desidera utilizzarla in un’altra cartella di lavoro o in tutte le cartelle di lavoro, è possibile farlo creando un relativo componente aggiuntivo. Segui questi semplici passaggi:

• Innanzitutto, devi salvare il file (in cui hai il codice della funzione personalizzata) come componente aggiuntivo.
• Per fare ciò, vai alla scheda File ➜ Salva con nome ➜ “Componenti aggiuntivi di Excel (.xalm).

• Successivamente, fai doppio clic sul componente aggiuntivo e installalo.

Questo è tutto. Ora puoi utilizzare tutte le funzioni VBA in qualsiasi cartella di lavoro.

Diversi modi per creare una funzione VBA personalizzata [livello avanzato]

A questo punto sai come creare una funzione personalizzata in VBA. Ma il fatto è che quando utilizziamo le funzioni integrate, vengono fornite con diversi tipi di argomenti.

Quindi, in questa sezione di questa guida imparerai come creare una UDF con i diversi tipi di argomento.

• Senza alcuna discussione
• Con un unico argomento
• Con diversi argomenti
• Utilizzo dell’array come argomento

… andando avanti.

1. Senza argomenti

Ricordi funzioni come ADESSO e OGGI in cui non è necessario inserire un argomento?

SÌ. È possibile creare una funzione definita dall’utente in cui non è necessario inserire alcun argomento. Facciamolo con un esempio:

Creiamo una funzione personalizzata che possa restituire la posizione del file corrente. Ed ecco il codice:

 Function myPath() As String Dim myLocation As String Dim myName As String myLocation = ActiveWorkbook.FullName myName = ActiveWorkbook.Name If myLocation = myName Then myPath = "File is not saved yet." Else myPath = myLocation End If End Function

Questa funzione restituisce il percorso della posizione in cui è archiviato il file corrente e se la cartella di lavoro non è archiviata da nessuna parte, mostrerà un messaggio che dice “Il file non è ancora stato salvato”.

Ora, se presti molta attenzione al codice di questa funzione, non avrai bisogno di definire un argomento (tra parentesi). Hai appena definito il tipo di dati per il risultato della funzione.

La regola di base per creare una funzione senza argomenti è un codice in cui non è necessario digitare nulla.

In poche parole, il valore che vuoi ottenere dalla funzione dovrebbe essere calcolato automaticamente.

E in questa funzione hai la stessa cosa.

Questo ActiveWorkbook.FullName restituisce il percorso del file e questo ActiveWorkbook.Name restituisce il nome. Non devi inserire nulla.

2. Con un unico argomento

Abbiamo già trattato questo argomento imparando come creare una funzione definita dall’utente. Ma scaviamo un po’ più a fondo e creiamo una funzione diversa. Questa è la funzione che ho creato qualche mese fa per estrarre l’URL da un collegamento ipertestuale .

 Function giveMeURL(rng As Range) As String On Error Resume Next giveMeURL = rng.Hyperlinks(1).Address End Function

Ora in questa funzione hai solo un argomento.

Quando lo inserisci in una cella e quindi specifichi la cella in cui è presente un collegamento ipertestuale, verrà restituito l’URL del collegamento ipertestuale. Ora in questa funzione il lavoro principale è svolto da:

 rng.Hyperlinks(1).Address

Ma l’rng è quello che devi specificare. Di’ “Facile” nella sezione commenti se ritieni che la creazione di una UDF sia facile.

3. Con diversi argomenti

Normalmente, la maggior parte delle funzioni integrate di Excel hanno più argomenti. Quindi è essenziale che tu impari come creare una funzione personalizzata con più argomenti.

Facciamo un esempio: vuoi rimuovere determinate lettere da una stringa di testo e vuoi mantenere il resto della parte.

Bene, hai funzioni come DESTRA e LEN che utilizzerai in questa funzione personalizzata. Ma qui non ne abbiamo bisogno. Tutto ciò di cui abbiamo bisogno è una funzione personalizzata utilizzando VBA.

Quindi, ecco la funzione:

 Function removeFirstC(rng As String, cnt As Long) As String removeFirstC = Right(rng, Len(rng) - cnt) End Functio

Ok, allora guarda:

In questa funzione hai due argomenti:

• rng: in questo argomento è necessario specificare la cella da cui si desidera rimuovere il primo carattere di un testo.
• cnt: E nell’argomento devi specificare il numero di caratteri da rimuovere (se vuoi rimuovere più caratteri dal testo).

Quando lo inserisci in una cella, funziona come di seguito:

3.1 Creazione di una funzione definita dall’utente con argomenti facoltativi e obbligatori

Se pensi alla funzione che abbiamo appena creato nell’esempio sopra in cui hai due argomenti diversi, beh, entrambi sono necessari. E, se ne perdi uno, otterrai un errore come questo.

Ora, se pensi in modo logico, la funzione che abbiamo creato è rimuovere il primo carattere. Ma qui devi specificare il numero di caratteri da rimuovere. Quindi il mio punto è che questo argomento dovrebbe essere facoltativo e dovrebbe accettarne uno come predefinito.

Cosa ne pensi?

Dì “Sì” nella sezione commenti se sei d’accordo con me su questo.

Ok, allora guarda. Per rendere un argomento facoltativo, aggiungi semplicemente “Facoltativo” prima di esso. Proprio così:

Ma la cosa importante è far funzionare il codice con o senza il valore di questo argomento. Quindi il nostro nuovo codice per la stessa funzione sarebbe simile a questo: Ora nel codice, se ignori la specifica del secondo argomento.

4. Utilizzare Array come argomento

Esistono alcune funzioni integrate che possono accettare argomenti di array e puoi anche creare la tua funzione VBA personalizzata per farlo.

Facciamolo con un semplice esempio in cui devi creare una funzione in cui sommi i valori di un intervallo in cui sono presenti numeri e testo. Eccoci qui.

 Function addNumbers(CellRef As Range) Dim Cell As Range For Each Cell In CellRef If IsNumeric(Cell.Value) = True Then Result = Result + Cell.Value End If Next Cell addNumbers = Result End Function

Nel codice della funzione precedente, abbiamo utilizzato un intervallo di numeri interi A1:A10 invece di un singolo valore o riferimento di cella.

Utilizzando il ciclo FOR EACH, controllerà ogni cella nell’intervallo e sommerà il valore se la cella contiene un numero.

L’ambito di una funzione definita dall’utente

In termini semplici, l’ambito di una funzione indica se può essere richiamata o meno da altre procedure. Una FDU può avere due diversi tipi di ambiti.

1. Pubblico

Puoi rendere pubblica la tua funzione personalizzata in modo da poterla richiamare in tutti i fogli di lavoro della cartella di lavoro. Per rendere pubblica una funzione, utilizzare semplicemente la parola “Pubblico”, come di seguito.

Ma una funzione è pubblica per impostazione predefinita se non la rendi privata. In tutti gli esempi che abbiamo trattato, sono tutti pubblici.

2. Privato

Quando rendi privata una funzione, puoi utilizzarla nelle procedure nello stesso modulo.

Diciamo che se hai la tua UDF in “Modulo1”, puoi usarla solo nelle procedure che hai in “Modulo1”. E non apparirà nell’elenco delle funzioni del foglio di lavoro (quando usi il segno = e provi a digitare il nome) ma puoi comunque usarlo digitandone il nome e specificando gli argomenti.

Limitazioni della funzione definita dall’utente [UDF]

Le UDF sono estremamente utili. Ma sono limitati in determinate situazioni. Ecco alcune cose che voglio che tu tenga presente e ricordi quando crei una funzione personalizzata in VBA.

• Non è possibile modificare, eliminare o formattare celle e intervalli utilizzando una funzione personalizzata.
• Inoltre, non è possibile spostare, rinominare, eliminare o aggiungere fogli di lavoro a una cartella di lavoro.
• Modificare il valore di un’altra cella.
• Inoltre, non è possibile modificare le opzioni dell’ambiente.

C’è differenza tra una funzione incorporata e una funzione definita dall’utente?

Sono felice che tu l’abbia chiesto. Bene, per rispondere a questa domanda, voglio condividere alcuni punti che ritengo importanti per te.

1. Più lento di quello integrato: se confronti la velocità delle funzioni integrate e delle funzioni VBA, scoprirai che la prima è veloce. Il motivo è che le funzioni integrate sono scritte in C++ o FORTRAN.
2. Difficoltà nel condividere file: spesso condividiamo file tramite e-mail e cloud. Se stai utilizzando una qualsiasi delle funzioni personalizzate, devi condividere questo file in formato “xlam” in modo che anche qualcun altro possa utilizzare la tua funzione personalizzata.

Ma come ho detto sopra in “Perché dovresti creare una funzione Excel personalizzata”, ci sono situazioni specifiche in cui puoi optare per una funzione VBA personalizzata.

Conclusione

Creare una funzione definita dall’utente è semplice. Tutto quello che devi fare è utilizzare “Funzione” prima del nome per definirlo come una funzione, aggiungere argomenti, impostare il tipo di dati degli argomenti e quindi impostare il tipo di dati per il valore restituito.

Alla fine, aggiungi il codice per calcolare il valore che vuoi ottenere dalla funzione. Questa guida che ho condiviso con te oggi è la più semplice per imparare a creare una funzione personalizzata in VBA e sono sicuro che l’hai trovata utile.

Ma adesso dimmi una cosa.

Le UDF sono utili, cosa ne pensi?

Per favore condividi le tue opinioni con me nella sezione commenti. Mi piacerebbe sentire la tua opinione e, per favore, non dimenticare di condividere questo post con i tuoi amici, sono sicuro che lo apprezzeranno.

Tutorial correlati

• Elenco delle funzioni VBA – spiegato con esempi
• Come eseguire una macro in Excel
• Come utilizzare l’istruzione IF in VBA
• Come lavorare con intervalli e celle in VBA
• Aggiungi un nuovo foglio utilizzando il codice VBA