Benvenuto ad una nuova lezione di MasterExcel.it. In questa lezione vedremo un esempio di UDF (User Defined Function), ovvero vedremo come scrivere una Funzione Excel personalizzata in VBA. In altre parole, hai presente la Funzione che non esiste ma che è in grado di fare il calcolo che ti interessa? Bene, oggi vedremo di crearla ed averla disponibile tra le altre Funzioni di Excel.

Guarda il Video: Excel VBA User Defined Function

Per spiegarti in quale situazione ti può tornare utile una funziona personalizzata, ti parlerò di una situazione in cui mi sono trovato creando il Corso Excel Base. Alcuni dei miei studenti avevano versioni di Excel diverse tra loro, anche in inglese. Ciò significa che gli esercizi preparati per loro, tramite le Macro, non potevano essere eseguiti su tutte le loro versioni. O meglio, la auto-valutazione non poteva essere eseguita su tutte le versioni in maniera automatica. Per farti capire il problema utilizzerò delle immagini.

Come puoi vedere ho creato degli esercizi auto-valutati (esercizi di Excel che puoi scaricare qui), tramite delle funzioni dinamiche. Mentre i punteggi sono eseguiti attraverso un mix di Macro e Formule dinamiche.

Però avevo un problema. Immagina di trovarti davanti ad una situazione di questo tipo.

Questa funzione di fatto cerca la parola “SOMMA”. Serve per verificare che il mio studente abbia inserito la funzione corretta dell’esercizio pertinente. Problema, che succede se il mio studente ha una versione in inglese? Succede che scrive SUM e non SOMMA per fare la stessa identica cosa. Devo quindi fare qualcosa di dinamico e che capisca che è stata utilizzata la funzione Somma a prescindere dalla lingua utilizzata.

Il problema è semplice: come si può creare una funzione che rimanga efficiente su tutte le versioni di Excel?

Ecco una situazione ideale in cui creare una funzione personalizzata.

Editor del VBA

Andiamo su SVILUPPO (in alto sulla barra multifunzione)> VISUAL BASIC. In questo menu andiamo su: Inserisci> Modulo

Si aprirà un riquadro bianco in cui agire, molto simile al blocco note. Scriviamo la nostra funzione.

ROSSO: “Function” significa ovviamente “funzione”, mentre “TF” è il suo nome. Potevo anche chiamarla “Gino”, o “Pippo”, il nome non è importante.

BLU: diciamo semplicemente che il “bersaglio” è la mia cella. Nulla di complicato, sarebbe a dire: “applica questa funzione personalizzata alla mia cella selezionata”

VERDE: diciamo che la “TF” si trova in una mia cella come “Formula”. Ovvero, dico al VBA di dire ad Excel che TF significa mostrare la formula presente nella mia cella.

ARANCIONE: la fine della funzione. Viene creata automaticamente quando scriviamo “Function”. Serve solo a dire al VBA e ad Excel che la funzione finisce qui.

Bene, andiamo a vedere cosa succede nel nostro foglio di lavoro:

Come puoi vedere, la nostra funzione TF viene riconosciuta come tale da Excel ed è localizzabile nel nostro “elenco” di funzioni, come se fosse una normale funzione di Excel.

Indichiamo come “bersaglio” la cella “B2” che contiene la nostra funzione “Somma”.

BLU: l’aspetto della nostra funzione.

ARANCIONE: la cella bersaglio, che contiene la funzione “Somma”.

ROSSO: il testo della funzione presente nella cella “bersaglio” (cella B2 in questo caso). Come puoi vedere la formula riportata è scritta in inglese. Perché? perché il VBA “ragiona” in inglese mentre (il mio) Excel “ragiona” in italiano. Abbiamo risolto il nostro problema. Questo perché il VBA è scritto solo in inglese. Adesso, a prescindere dalla lingua dell’utilizzatore, se è applicata la Funzione SOMMA (in italiano nel nostro esempio) essa verrà tradotta in SUM in inglese.

Problema 2

Avevo anche un altro problema: la funzione NUMERO.VALORE, importante per i miei scopi, non era supportata su ogni versione di Excel. Per esempio non è presente nella 2003, nella 2007, e nella 2010. Vediamo come ho risolto il problema:

Cosa fa questa funzione?

ROSSO: “Function” per creare una funzione, “TN” è il suo nome. Potevo dare il nome che volevo

BLU: “la mia cella è il bersaglio”.

VERDE: “as double” nel VBA significa “è un numero grosso”. Serve per specificare che la cella “range” deve essere considerata come un numero. O meglio, serve a dire di considerare il valore della cella range, come uno numero (che può anche essere un decimale), se non lo specifichiamo è possibile che Excel non capisca come debba trattare il valore.

ARANCIONE: sto dicendo ad Excel, di darmi il valore della cella.

VIOLA: la fine della funzione.

Cosa fa quindi questa funzione? Semplice, fa quello che fa la NUMERO.VALORE, ma è in grado di essere eseguita anche nelle versioni di Excel (2010, 2007, e 2003) dove il Numero.Valore non è supportata.

Abbiamo risolto i nostri problemi con 3 righe di codice in VBA. Ricordati che per salvare queste funzioni dobbiamo salvare il file con un’estensione particolare chiamata “Cartella di lavoro con attivazione macro di Excel”. In questo modo potremo “conservare” le nostre funzioni personalizzate.