Creare funzioni personalizzate in Excel

Excel include numerose funzioni del foglio di lavoro predefinite, tuttavia è possibile che non abbia una funzione per ogni tipo di calcolo che si vuole eseguire. I progettisti di Excel non possono prevedere le esigenze di calcolo di ogni utente. Excel però consente di creare funzioni personalizzate, che vengono descritte in questo articolo.

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.

Esempio di un modulo d'ordine senza una funzione personalizzata

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

Nota: Per rendere più leggibile il codice, è possibile usare TAB per il rientro delle righe. I rientri servono solo a semplificare la lettura, ma il codice viene eseguito con o senza di essi. Dopo aver digitato una riga rientrata, Visual Basic Editor presuppone che la riga successiva avrà lo stesso rientro. Per spostarsi a sinistra di un carattere di tabulazione, premere MAIUSC+TAB.

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.

Esempio di un modulo d'ordine con una funzione personalizzata

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 a quello per le macro. Le funzioni personalizzate non possono eseguire altre azioni oltre 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 per il tipo di carattere, il colore o il motivo per il testo in una cella. Se si include un codice "Action" di questo tipo in una routine Function, la funzione restituisce il messaggio di errore #VALORE!.

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.

Esempio di una funzione VBA con commenti

Un apostrofo indica a Excel di ignorare tutti gli elementi a destra nella stessa riga. In questo modo si possono creare commenti in righe distinte o sul lato destro delle righe che contengono il codice VBA. Si può iniziare un blocco di codice relativamente lungo con un commento che ne descrive lo scopo generale e usare i commenti incorporati per le istruzioni dei singoli documenti.

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:

Finestra di dialogo Inserisci funzione

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:

  1. Dopo aver creato le funzioni desiderate, fare clic su File > Salva con nome.

    In Excel 2007 fare clic sul pulsante Microsoft Office e quindi su Salva con nome

  2. Nella finestra di dialogo Salva con nome aprire l'elenco a discesa Tipo file e selezionare Componente aggiuntivo di Excel. Salvare la cartella di lavoro con un nome riconoscibile, ad esempio MieFunzioni, nella cartella Componenti aggiuntivi. La finestra di dialogo Salva con nome suggerirà tale cartella, quindi sarà sufficiente accettare il percorso predefinito.

  3. Dopo aver salvato la cartella di lavoro, fare clic su File > Opzioni di Excel.

    In Excel 2007 fare clic sul pulsante Microsoft Office e su Opzioni di Excel.

  4. Nella finestra di dialogo Opzioni di Excel fare clic sulla categoria Componenti aggiuntivi.

  5. Nell'elenco a discesa Gestisci selezionare Componenti aggiuntivi di Excel. Quindi, fare clic sul pulsante Vai.

  6. Nella finestra di dialogo Componenti aggiuntivi selezionare la casella di controllo accanto al nome usato per salvare la cartella di lavoro, come illustrato di seguito.

    Finestra di dialogo Componenti aggiuntivi

  1. Dopo aver creato le funzioni desiderate, fare clic su File > Salva con nome.

  2. Nella finestra di dialogo Salva con nome aprire l'elenco a discesa Tipo file e selezionare Componente aggiuntivo di Excel. Salvare la cartella di lavoro con un nome riconoscibile, ad esempio MieFunzioni.

  3. Dopo aver salvato la cartella di lavoro, fare clic su Strumenti > Componenti aggiuntivi di Excel.

  4. Nella finestra di dialogo Componenti aggiuntivi selezionare il pulsante Sfoglia per trovare il componente aggiuntivo, fare clic su Apri, quindi selezionare la casella accanto al componente aggiuntivo nella casella Componenti aggiuntivi disponibili.

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.

Modulo denominato in VBE

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.

Servono altre informazioni?

È sempre possibile rivolgersi a un esperto nella Tech Community di Excel, ottenere supporto nella community Microsoft o suggerire una nuova caratteristica o un miglioramento in Excel UserVoice.

Amplia le tue competenze su Office
Esplora i corsi di formazione
Ottieni in anticipo le nuove caratteristiche
Partecipa al programma Office Insider

Queste informazioni sono risultate utili?

Grazie per il feedback!

Grazie per il tuo feedback! Potrebbe essere utile metterti in contatto con uno dei nostri operatori del supporto di Office.

×