Creare funzioni personalizzate in Excel

Le funzioni personalizzate, come le macro, usano il linguaggio di programmazione Microsoft Visual Basic, Applications Edition (VBA). Si differenziano dalle macro per due aspetti principali. Prima di tutto, usano le routine Function invece di Sub. Significa che iniziano con un'istruzione Function invece che Sub e terminano con End Function invece che End Sub. In secondo luogo, eseguono calcoli invece di azioni. Alcuni tipi di istruzioni, ad esempio quelle per selezionare e formattare gli intervalli, sono escluse dalle funzioni personalizzate. In questo articolo è spiegato come creare e usare le funzioni personalizzate. Per creare funzioni e macro, si usa Visual Basic Editor (VBE), che si apre in una nuova finestra separata da Excel.

Si supponga che l'azienda offra uno sconto del 10% per la vendita di un prodotto, purché l'ordine sia superiore alle 100 unità. Nei paragrafi seguenti verrà illustrata una funzione per calcolare lo sconto.

L'esempio seguente mostra un modulo d'ordine che elenca tutti gli articoli, le quantità, i prezzi, gli sconti (se presenti) e il prezzo complessivo risultante.

Per creare una funzione DISCOUNT personalizzata in questa cartella di lavoro, procedere come segue:

1. PremereALT+F11 per aprire Visual Basic Editor (sul Mac premere FN+ALT+F11), quindi fare clic su Inserisci > Modulo. Una nuova finestra del modulo viene visualizzata sul lato destro di Visual Basic Editor.

2. Copiare e incollare il codice seguente nel nuovo modulo.

   Function DISCOUNT(quantity, price)
      If quantity >=100 Then
        DISCOUNT = quantity * price * 0.1
      Else
        DISCOUNT = 0
      End If
    
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   

Ora è possibile usare la nuova funzione DISCOUNT. Chiudere Visual Basic Editor, selezionare la cella G7 e digitare quanto segue:

=DISCOUNT(D7,E7)

Excel calcola lo sconto del 10% su 200 unità a un costo unitario di € 47,50 e restituisce € 950,00.

Nella prima riga del codice VBA, la funzione DISCOUNT(quantity, price), è indicato che la funzione DISCOUNT richiede due argomenti, quantity e price. Quando si chiama la funzione in una cella del foglio di lavoro, è necessario includere questi due argomenti. Nella formula =DISCOUNT(D7,E7), D7 è l'argomento quantity, mentre E7 è l'argomento price. Ora è possibile copiare la formula DISCOUNT in G8:G13 per ottenere i risultati illustrati di seguito.

Vediamo in che modo Excel interpreta la routine Function. Quando si preme INVIO, Excel cerca il nome DISCOUNT nella cartella di lavoro corrente e trova che è una funzione personalizzata in un modulo VBA. I nomi degli argomenti racchiusi tra parentesi, quantity e prezzo, sono segnaposto per i valori su cui si basa il calcolo dello sconto.

L'istruzione If nel blocco di codice seguente esamina l'argomento quantity e determina se il numero di articoli venduti è maggiore o uguale a 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Se il numero di articoli venduti è maggiore o uguale a 100, VBA esegue l'istruzione seguente, che moltiplica il valore quantity per il valore price, quindi moltiplica il risultato per 0,1:

Discount = quantity * price * 0.1

Il risultato viene archiviato come variabile Discount. Un'istruzione VBA che archivia un valore in una variabile viene chiamata istruzione assignment perché valuta l'espressione a destra del segno di uguale e assegna il risultato al nome della variabile a sinistra. La variabile Discount ha lo stesso nome della routine Function, quindi il valore archiviato nella variabile viene restituito nella formula del foglio di lavoro da cui è stata richiamata la funzione DISCOUNT.

Se quantity è minore di 100, VBA esegue l'istruzione seguente:

Discount = 0

Infine, l'istruzione seguente arrotonda il valore assegnato alla variabile Discount a due cifre decimali:

Discount = Application.Round(Discount, 2)

Al contrario di Excel, VBA non ha alcuna funzione ROUND. Di conseguenza, per usare la funzione ROUND in questa istruzione, VBA deve cercare il metodo Round (funzione) nell'oggetto Application (Excel). Per farlo, aggiungere la parola Application prima della parola Round. Usare questa sintassi quando si deve accedere a una funzione di Excel da un modulo VBA.

Una funzione personalizzata deve iniziare con un'istruzione Function e terminare con un'istruzione End Function. Oltre al nome della funzione, l'istruzione Function specifica in genere uno o più argomenti. È tuttavia possibile creare una funzione senza argomenti. Excel include numerose funzioni predefinite, ad esempio CASUALE e ORA, che non usano argomenti.

Dopo l'istruzione Function, una routine Function include una o più istruzioni di VBA che consentono di prendere decisioni ed eseguire calcoli usando gli argomenti passati alla funzione. Infine, in un punto qualsiasi di una routine Function è necessario includere un'istruzione che assegna un valore a una variabile con lo stesso nome della funzione. Questo valore viene restituito alla formula che chiama la funzione.

Il numero di parole chiave VBA che è possibile usare nelle funzioni personalizzate è inferiore al numero che è possibile usare nelle macro. Le funzioni personalizzate non possono fare altro che restituire un valore a una formula in un foglio di lavoro o a un'espressione usata in un'altra macro o funzione VBA. Ad esempio, le funzioni personalizzate non possono ridimensionare le finestre, modificare una formula in una cella o modificare le opzioni relative a tipo di carattere, colore o motivo per il testo in una cella. Se si include codice "action" di questo tipo in una routine Function, la funzione restituisce il #VALUE! .

L'unica azione che una routine Function può eseguire, ad eccezione dei calcoli, è la visualizzazione di una finestra di dialogo. È possibile usare un'istruzione InputBox in una funzione personalizzata come mezzo per ottenere un input dall'utente che esegue la funzione. È possibile usare un'istruzione MsgBox come mezzo per comunicare informazioni all'utente. È anche possibile usare le finestre di dialogo personalizzate, o UserForm, ma si tratta di un argomento che esula dall'ambito di questa introduzione.

Anche le macro e le funzioni personalizzate più semplici possono risultare difficili da leggere. È possibile semplificarle digitando un testo descrittivo sotto forma di commenti. Per aggiungere commenti, digitare un testo esplicativo anteponendo un apostrofo. Ad esempio, l'esempio seguente mostra la funzione DISCOUNT con commenti. L'aggiunta di commenti come quelli visualizzati semplifica la gestione del codice VBA col passare del tempo. Se è necessario apportare una modifica al codice in un secondo momento, risulterà più semplice comprendere le azioni eseguite in origine.

Un apostrofo indica a Excel di ignorare tutti gli elementi a destra nella stessa riga, in modo da poter creare commenti sulle righe da sole o sul lato destro delle righe contenenti codice VBA. È possibile iniziare un blocco di codice relativamente lungo con un commento che ne spiega lo scopo generale e quindi usare i commenti incorporati per documentare singole istruzioni.

Un altro modo per documentare le macro e le funzioni personalizzate consiste nell'assegnare nomi descrittivi. Ad esempio, invece di Etichette, è possibile assegnare a una macro il nome EtichetteMese per descrivere in modo più specifico lo scopo della macro. L'uso di nomi descrittivi per le macro e le funzioni personalizzate è particolarmente utile se sono state create molte procedure, specialmente se si creano procedure con scopi simili ma non identici.

Il modo con cui si documentano le macro e le funzioni personalizzate dipende dalle proprie preferenze. La cosa importante è adottare un unico metodo di documentazione e usarlo in modo coerente.

Per usare una funzione personalizzata, la cartella di lavoro che contiene il modulo in cui è stata creata la funzione deve essere aperta. Se la cartella di lavoro non è aperta, viene visualizzato un errore #NOME? quando si prova a usare la funzione. Se si fa riferimento alla funzione in altre cartelle di lavoro, è necessario anteporre il nome della cartella di lavoro in cui si trova la funzione al nome della funzione. Ad esempio, se si crea una funzione DISCOUNT in una cartella di lavoro denominata Personal.xlsb e si richiama la funzione da un'altra cartella di lavoro, è necessario digitare =personal.xlsb!discount() e non solo =discount().

È possibile evitare alcune sequenze di tasti ed eventuali errori di digitazione selezionando le funzioni personalizzate nella finestra di dialogo Inserisci funzione. Le funzioni personalizzate vengono visualizzate nella categoria Definite dall'utente:

Per rendere sempre disponibili le funzioni personalizzate in modo semplice, è possibile archiviarle in un'altra cartella di lavoro e salvare la cartella di lavoro come componente aggiuntivo. Il componente aggiuntivo sarà così sempre disponibile quando si esegue Excel. Ecco come fare:

Dopo aver completato questi passaggi, le funzioni personalizzate saranno disponibili ogni volta che si esegue Excel. Per aggiungerle alla libreria di funzioni, tornare a Visual Basic Editor. Sotto l'intestazione VBAProject in Gestione progetti di Visual Basic Editor sarà visualizzato un modulo denominato come il file del componente aggiuntivo. Il componente aggiuntivo avrà l'estensione xlam.

Se si fa doppio clic su questo modulo in Gestione progetti, Visual Basic Editor visualizzerà il codice della funzione. Per aggiungere una nuova funzione, posizionare il punto di inserimento dopo l'istruzione End Function che termina l'ultima funzione nella finestra del codice e iniziare a digitare. È possibile creare tutte le funzioni necessarie in questo modo, che saranno sempre disponibili nella categoria Definite dall'utente della finestra di dialogo Inserisci funzione.

Il contenuto originale è stato creato da Mark Dodge e Craig Stinson e fa parte del libro Microsoft Office Excel 2007 Inside Out. È stato aggiornato per essere applicato anche alle versioni più recenti di Excel.