In questo tutorial impareremo a conoscere la funzione VBA di Excel {vuoto} 1) Cos’è Visual Basic in Excel?

{vuoto} 2) Come utilizzare VBA in Excel?

{vuoto} 3) Come creare una funzione definita dall’utente?

{vuoto} 4) Come scrivere Macro?

Come scrivere VBAcode Excel fornisce all’utente una vasta raccolta di funzioni già pronte, più che sufficienti per soddisfare l’utente medio. Molti altri possono essere aggiunti installando i vari componenti aggiuntivi disponibili. La maggior parte dei calcoli può essere eseguita con ciò che viene fornito, ma non passa molto tempo prima che ti ritrovi a desiderare che ci fosse una funzione che ha svolto un lavoro particolare e non riesci a trovare nulla di adatto nell’elenco. Hai bisogno di un UDF. Una UDF (funzione definita dall’utente) è semplicemente una funzione che crei tu stesso con VBA. Le UDF sono spesso chiamate “Funzioni personalizzate”. Una UDF può rimanere in un modulo di codice allegato a una cartella di lavoro, nel qual caso sarà sempre disponibile quando quella cartella di lavoro è aperta. In alternativa puoi creare il tuo add-in contenente una o più funzioni che puoi installare in Excel proprio come un add-in commerciale. È possibile accedere alle UDF anche dai moduli di codice. Spesso le UDF vengono create dagli sviluppatori per funzionare esclusivamente all’interno del codice di una procedura VBA e l’utente non è mai a conoscenza della loro esistenza. Come ogni funzione, l’UDF può essere semplice o complessa come desideri. Cominciamo con uno facile …​

Una funzione per calcolare l’area di un rettangolo Sì, lo so che potresti farlo nella tua testa! Il concetto è molto semplice quindi puoi concentrarti sulla tecnica. Supponi di aver bisogno di una funzione per calcolare l’area di un rettangolo. Guardi la raccolta di funzioni di Excel, ma non ce n’è una adatta. Questo è il calcolo da fare:

AREA = LENGTH x WIDTH Aprire una nuova cartella di lavoro e quindi aprire Visual Basic Editor (Strumenti> Macro> Visual Basic Editor o ALT + F11).

Avrai bisogno di un modulo in cui scrivere la tua funzione, quindi scegli Inserisci> Modulo. Nel modulo vuoto digitare: Function Area e premere INVIO. Visual Basic Editor completa la riga per te e aggiunge una riga di End Function come se stessi creando una subroutine. Finora sembra così …​

Function Area()

End Function

Posiziona il cursore tra le parentesi dopo “Area”. Se ti sei mai chiesto a cosa servono le parentesi, stai per scoprirlo! Stiamo per specificare gli “argomenti” che la nostra funzione prenderà (un argument è un’informazione necessaria per fare il calcolo). Digita Length as double, Width as double e fai clic nella riga vuota sottostante. Nota che durante la digitazione, viene visualizzata una casella di scorrimento che elenca tutte le cose appropriate a ciò che stai digitando.

img2

Questa funzione è chiamata Membri elenco automatico. Se non viene visualizzato, significa che è spento (attivalo in Strumenti> Opzioni> Editor) o potresti aver commesso un errore di digitazione in precedenza. È un controllo molto utile sulla sintassi. Trova l’elemento che ti serve e fai doppio clic su di esso per inserirlo nel codice. Puoi ignorarlo e digitare se lo desideri. Il tuo codice ora assomiglia a questo …​

Function Area(Length As Double, Width As Double)

End Function

La dichiarazione del tipo di dati degli argomenti non è obbligatoria ma ha senso. Avresti potuto digitare Length, Width e lasciarlo così, ma avvertire Excel quale tipo di dati aspettarsi aiuta il tuo codice a funzionare più rapidamente e rileva gli errori nell’input. Il tipo di dati double si riferisce al numero (che può essere molto grande) e consente le frazioni. Ora per il calcolo stesso. Nella riga vuota premi prima il tasto TAB per far rientrare il tuo codice (rendendolo più facile da leggere) e digita Area = Length * Width. Ecco il codice completato …​

Function Area(Length As Double, Width As Double)

Area = Length * Width

End Function

Noterai un’altra delle funzionalità di aiuto di Visual Basic Editor apparire mentre digiti, Auto Quick Info …​

img31

Non è rilevante qui. Il suo scopo è aiutarti a scrivere funzioni in VBA, dicendoti quali argomenti sono richiesti. Puoi testare subito la tua funzione. Passa alla finestra di Excel e inserisci le cifre per Lunghezza e Larghezza in celle separate. In una terza cella inserisci la tua funzione come se fosse una di quelle integrate. In questo esempio, la cella A1 contiene la lunghezza (17) e la cella B1 la larghezza (6.5). In C1 ho digitato _ = area (A1, B1) _ e la nuova funzione ha calcolato l’area (110.5) …​

img3

A volte, gli argomenti di una funzione possono essere opzionali. In questo esempio potremmo rendere facoltativo l’argomento Larghezza. Supponiamo che il rettangolo sia un quadrato con Lunghezza e Larghezza uguali. Per evitare che l’utente debba inserire due argomenti, potremmo fargli inserire solo la Lunghezza e fare in modo che la funzione usi quel valore due volte (cioè moltiplicare Lunghezza x Lunghezza). Quindi la funzione sa quando può farlo, dobbiamo includere un’istruzione IF per aiutarla a decidere. Cambia il codice in modo che assomigli a questo …​

Function Area(Length As Double, Optional Width As Variant)

If IsMissing(Width) Then

Area = Length * Length

Else

Area = Length * Width

End If

End Function

Si noti che il tipo di dati per Larghezza è stato modificato in Variant per consentire valori nulli. La funzione ora consente all’utente di inserire un solo argomento, ad es. _ = area (A1) _. L’istruzione IF nella funzione controlla se l’argomento Width è stato fornito e calcola di conseguenza …​

img4

Una funzione per calcolare il consumo di carburante Mi piace tenere sotto controllo il consumo di carburante della mia auto, quindi quando compro carburante prendo nota del chilometraggio e della quantità di carburante necessaria per riempire il serbatoio.

Qui nel Regno Unito il carburante viene venduto in litri. Il contachilometri dell’auto (OK, quindi è un contachilometri) registra la distanza in miglia. E poiché sono troppo vecchio e stupido per cambiare, capisco solo MPG (miglia per gallone). Ora, se pensi che sia tutto un po ‘triste, che ne dici di questo. Quando torno a casa apro Excel e inserisco i dati in un foglio di lavoro che calcola l’MPG per me e traccia le prestazioni dell’auto. Il calcolo è il numero di miglia che l’auto ha percorso dall’ultimo rifornimento diviso per il numero di galloni di carburante utilizzati …​

MPG = (MILES THIS FILL – MILES LAST FILL) / GALLONS OF FUEL ma perché il carburante arriva in litri e ci sono 4.546 litri in un gallone ..

MPG = (MILES THIS FILL – MILES LAST FILL) / LITERS OF FUEL x 4.546 Ecco come ho scritto la funzione …​

Function MPG(StartMiles As Integer, FinishMiles As Integer, Litres As Single)

MPG = (FinishMiles - StartMiles) / Litres * 4.546

End Function

ed ecco come appare sul foglio di lavoro …​

img5

Non tutte le funzioni eseguono calcoli matematici. Eccone uno che fornisce informazioni …​

Una funzione che fornisce il nome del giorno Spesso mi viene chiesto se esiste una funzione di data che fornisce il giorno della settimana come testo (ad esempio lunedì). La risposta è no, ma è abbastanza facile crearne uno. (Addendum: Ho detto di no? Controlla la nota sotto per vedere la funzione che ho dimenticato!). Excel ha la funzione WEEKDAY, che restituisce il giorno della settimana come un numero da 1 a 7. Puoi scegliere quale giorno è 1 se non ti piace l’impostazione predefinita (domenica). Nell’esempio sotto la funzione restituisce “5” che mi capita di sapere significa “giovedì”.

img6

Ma non voglio vedere un numero, voglio vedere “giovedì”. Potrei modificare il calcolo aggiungendo una funzione CERCA.VERT che faceva riferimento a una tabella da qualche parte contenente un elenco di numeri e un elenco corrispondente di nomi di giorni. Oppure potrei avere l’intera cosa autonoma con più istruzioni IF annidate. Troppo complicato! La risposta è una funzione personalizzata …​

Function DayName(InputDate As Date)

Dim DayNumber As Integer

DayNumber = Weekday(InputDate, vbSunday)

Select Case DayNumber

Case 1

DayName = "Sunday"

Case 2

DayName = "Monday"

Case 3

DayName = "Tuesday"

Case 4

DayName = "Wednesday"

Case 5

DayName = "Thursday"

Case 6

DayName = "Friday"

Case 7

DayName = "Saturday"

End Select

End Function

Ho chiamato la mia funzione “DayName” e richiede un singolo argomento, che io chiamo “InputDate” che (ovviamente) deve essere una data. Ecco come funziona …​

  • La prima riga della funzione dichiara una variabile che ho chiamato “DayNumber” che sarà un Integer (cioè un numero intero).

  • La riga successiva della funzione assegna un valore a quella variabile utilizzando la funzione WEEKDAY di Excel. Il valore sarà un numero compreso tra 1 e 7.

Sebbene il valore predefinito sia 1 = domenica, l’ho incluso comunque per chiarezza.

Infine, un’istruzione Case * esamina il valore della variabile e restituisce la parte di testo appropriata.

Ecco come appare sul foglio di lavoro …​

img7

=== === Accesso alle funzioni personalizzate Se a una cartella di lavoro è collegato un modulo di codice VBA che contiene funzioni personalizzate, tali funzioni possono essere facilmente indirizzate all’interno della stessa cartella di lavoro come illustrato negli esempi precedenti. Si utilizza il nome della funzione come se fosse una delle funzioni integrate di Excel.

È inoltre possibile trovare le funzioni elencate nella Creazione guidata funzione (a volte chiamata strumento Incolla funzione). Utilizzare la procedura guidata per inserire una funzione nel modo normale (Inserisci> Funzione).

Scorri l’elenco delle categorie di funzioni per trovare Definito dall’utente e selezionalo per vedere un elenco di UDF disponibili …​

img8

img9

Puoi vedere che le funzioni definite dall’utente non hanno alcuna descrizione oltre al messaggio inutile “Nessun aiuto disponibile”, ma puoi aggiungere una breve descrizione …​

Assicurati di essere nella cartella di lavoro che contiene le funzioni. Vai a Strumenti> Macro> Macro. Non vedrai le tue funzioni elencate qui ma Excel le conosce! Nella casella Nome macro nella parte superiore della finestra di dialogo, digitare il nome della funzione, quindi fare clic sul pulsante Opzioni della finestra di dialogo. Se il pulsante è disattivato o hai scritto il nome della funzione in modo errato, o ti trovi nella cartella di lavoro sbagliata, oppure non esiste! Si apre un’altra finestra di dialogo in cui è possibile inserire una breve descrizione della funzione. Fare clic su OK per salvare la descrizione e (ecco il bit di confusione) fare clic su Annulla per chiudere la finestra di dialogo Macro. Ricordarsi di salvare la cartella di lavoro contenente la funzione. La prossima volta che andrai alla procedura guidata delle funzioni, la tua UDF avrà una descrizione …​

img10

Come le macro, le funzioni definite dall’utente possono essere utilizzate in qualsiasi altra cartella di lavoro purché la cartella di lavoro che le contiene sia aperta. Tuttavia non è buona pratica farlo. Immettere la funzione in una cartella di lavoro diversa non è semplice. È necessario aggiungere il nome della cartella di lavoro host al nome della funzione. Questo non è difficile se ti affidi alla procedura guidata delle funzioni, ma è goffo da scrivere manualmente. La Creazione guidata funzione mostra i nomi completi di qualsiasi UDF in altre cartelle di lavoro …​

img11

Se apri la cartella di lavoro in cui hai utilizzato la funzione in un momento in cui la cartella di lavoro contenente la funzione è chiusa, vedrai un messaggio di errore nella cella in cui hai utilizzato la funzione. Excel se ne è dimenticato! Apri la cartella di lavoro host della funzione, ricalcola e tutto va di nuovo a posto. Fortunatamente c’è un modo migliore.

Se si desidera scrivere funzioni definite dall’utente da utilizzare in più di una cartella di lavoro, il metodo migliore è creare un componente aggiuntivo di Excel. Scopri come eseguire questa operazione nel tutorial Creare un componente aggiuntivo di Excel.

Addendum Dovrei davvero saperne di più! Mai, mai, dire mai! Dopo averti detto che non esiste una funzione che fornisce il nome del giorno, ora ho ricordato quella che può farlo. Guarda questo esempio …​

img12

La funzione TESTO restituisce il valore di una cella come testo in un formato numerico specifico. Quindi nell’esempio avrei potuto scegliere = TEXT (A1, “ddd”) per restituire “Thu”, = TEXT (A1, “mmmm”) per restituire “September” ecc. La guida di Excel ha alcuni altri esempi di modi di utilizzo questa funzione.

_ _Se i nostri blog ti sono piaciuti, condividili con i tuoi amici su Facebook. E anche tu puoi seguirci su Twitter e Facebook.

_ Ci piacerebbe sentire la tua opinione, facci sapere come possiamo migliorare, integrare o innovare il nostro lavoro e renderlo migliore per te. Scrivici a [email protected]_ __