In questo tutorial impareremo a conoscere la funzione VBA di Excel
1) Che cos'è Visual Basic in Excel?
2) Come usare VBA in Excel?
3) Come creare una funzione definita dall'utente?
4) Come scrivere Macro?
Come scrivere il codice VBA
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 svolgesse un lavoro particolare e non riesci a trovare nulla di adatto nell'elenco. Hai bisogno di un UDF. Una UDF (User Defined Function) è 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 componente aggiuntivo contenente una o più funzioni che puoi installare in Excel proprio come un componente aggiuntivo 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 qualsiasi funzione, l'UDF può essere semplice o complesso 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. Supponiamo di aver bisogno di una funzione per calcolare l'area di un rettangolo. Guardi attraverso la raccolta di funzioni di Excel, ma non ce n'è una adatta. Questo è il calcolo da fare:
AREA = LUNGHEZZA x LARGHEZZA
Aprire una nuova cartella di lavoro e quindi aprire l'editor di Visual Basic (Strumenti > Macro > Editor di Visual Basic o ALT+F11).
Avrai bisogno di un modulo in cui scrivere la tua funzione quindi scegli Inserisci > Modulo. Nel tipo di modulo vuoto: Area delle funzioni e premi ACCEDERE.L'editor di Visual Basic completa la riga per te e aggiunge una riga End Function come se stessi creando una subroutine. Finora sembra così…
Funzione Area() Fine Funzione
Posiziona il cursore tra le parentesi dopo "Area". Se ti sei mai chiesto a cosa servono le parentesi, lo stai per scoprire! Specifichiamo gli "argomenti" che la nostra funzione assumerà (an discussione è un'informazione necessaria per eseguire il calcolo). Tipo Lunghezza doppia, larghezza doppia e fare clic nella riga vuota sottostante. Nota che mentre digiti, viene visualizzata una casella di scorrimento che elenca tutte le cose appropriate a ciò che stai digitando.
Questa funzione si chiama Membri dell'elenco automatico. Se non compare neanche è spento (accendilo alle Strumenti > Opzioni > Editor) o potresti aver commesso un errore di battitura in precedenza. È un controllo molto utile sulla tua sintassi. Trova l'articolo che ti serve e fai doppio clic su di esso per inserirlo nel codice. Puoi ignorarlo e digitare se vuoi. Il tuo codice ora assomiglia a questo…
Area funzione (lunghezza doppia, larghezza doppia) Fine funzione
Dichiarare il tipo di dati degli argomenti non è obbligatorio ma ha senso. Avresti potuto digitare Lunghezza larghezza e l'ho lasciato così, ma avvisare Excel del tipo di dati da aspettarsi aiuta il tuo codice a essere eseguito più rapidamente e rileva gli errori nell'input. Il Doppio il tipo di dati si riferisce al numero (che può essere molto grande) e consente le frazioni. Ora per il calcolo stesso. Nella riga vuota premere prima il tasto TAB chiave per indentare il codice (rendendone più facile la lettura) e digitare Area = Lunghezza * Larghezza. Ecco il codice completato…
Area funzione (lunghezza doppia, larghezza doppia) Area = lunghezza * larghezza Fine funzione
Noterai un'altra delle funzionalità di aiuto dell'editor di Visual Basic apparire mentre stavi digitando, Informazioni rapide automatiche…
Non è rilevante qui. Il suo scopo è aiutarti a scrivere funzioni in VBA, indicandoti 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)…
A volte, gli argomenti di una funzione possono essere facoltativi. In questo esempio potremmo fare il Larghezza argomento facoltativo. Supponendo 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 utilizzi quel valore due volte (cioè moltiplichi Lunghezza x Lunghezza). Quindi la funzione sa quando può farlo, dobbiamo includere an Dichiarazione IF per aiutarlo a decidere. Cambia il codice in modo che assomigli a questo…
Funzione Area (Lunghezza come doppio, Larghezza opzionale come variante) If IsMissing(Larghezza) Then Area = Lunghezza * Lunghezza Altrimenti Area = Lunghezza * Larghezza End If End Funzione
Nota che il tipo di dati per Larghezza è stato modificato in Variante per consentire valori nulli. La funzione ora consente all'utente di inserire un solo argomento, ad es. =area(A1).L'istruzione IF nella funzione verifica se l'argomento Larghezza è stato fornito e calcola di conseguenza…
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 registra le prestazioni dell'auto. Il calcolo è il numero di miglia che l'auto ha percorso dall'ultimo rifornimento diviso per il numero di litri di carburante consumato…
MPG = (MIGLIA QUESTO RIEMPIMENTO - MIGLIA ULTIMO RIEMPIMENTO) / GALLONI DI CARBURANTE
ma perché il carburante arriva in litri e in un gallone ci sono 4.546 litri…
MPG = (MIGLIA QUESTO RIEMPIMENTO - MIGLIA ULTIMO RIEMPIMENTO) / LITRI DI CARBURANTE x 4.546
Ecco come ho scritto la funzione…
Funzione MPG(StartMiles As Integer, FinishMiles As Integer, Liters As Single) MPG = (FinishMiles - StartMiles) / Litri * 4.546 End Function
ed ecco come appare sul foglio di lavoro…
Non tutte le funzioni eseguono calcoli matematici. Eccone uno che fornisce informazioni…
Una funzione che dà il nome del giorno
Spesso mi viene chiesto se esiste una funzione data che dia il giorno della settimana come testo (es. lunedì). La risposta è no*, ma è abbastanza facile crearne uno. (*Addendum: Ho detto di no? Controlla la nota qui sotto per vedere la funzione che ho dimenticato!). Excel ha la funzione WEEKDAY, che restituisce il giorno della settimana come numero da 1 a 7. Puoi scegliere quale giorno è 1 se non ti piace l'impostazione predefinita (domenica). Nell'esempio seguente la funzione restituisce "5" che mi è capitato di sapere significa "giovedì".
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 il tutto autonomo con più istruzioni IF nidificate. Troppo complicato! La risposta è una funzione personalizzata…
Funzione DayName(InputDate As Date) Dim DayNumber As Integer DayNumber = Weekday(InputDate, vbSunday) Select Case DayNumber Case 1 DayName = "Domenica" Case 2 DayName = "Lunedì" Case 3 DayName = "Martedì" Case 4 DayName = "Mercoledì" Caso 5 DayName = "Giovedì" Caso 6 DayName = "Venerdì" Caso 7 DayName = "Sabato" Fine Seleziona Fine Funzione
Ho chiamato la mia funzione "DayName" e richiede un singolo argomento, che 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 intero (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.
- Finalmente un Dichiarazione del caso esamina il valore della variabile e restituisce la parte di testo appropriata.
Ecco come appare sul foglio di lavoro…
Accesso alle tue 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 denominata strumento Incolla funzione). Utilizzare la procedura guidata per inserire una funzione nel modo normale (Inserisci > Funzione).
Scorri verso il basso l'elenco delle categorie di funzioni da trovare Definito dall'utente e selezionalo per vedere un elenco di UDF disponibili…
Puoi vedere che le funzioni definite dall'utente mancano di qualsiasi descrizione oltre al messaggio inutile "Nessuna guida 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! Nel Nome macro casella nella parte superiore della finestra di dialogo, digitare il nome della funzione, quindi fare clic su della finestra di dialogo Opzioni pulsante. Se il pulsante è disattivato o hai digitato male il nome della funzione, o sei nella cartella di lavoro sbagliata, o non esiste! Si apre un'altra finestra di dialogo nella quale è possibile inserire una breve descrizione della funzione. Clic ok per salvare la descrizione e (ecco un po' di confusione) fare clic Annulla per chiudere la finestra di dialogo Macro. Ricorda di salvare la cartella di lavoro contenente la funzione. La prossima volta che andrai alla funzione guidata, la tua UDF avrà una descrizione…
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 è una buona pratica farlo. L'inserimento della funzione in un'altra cartella di lavoro non è semplice. Devi aggiungere il nome della cartella di lavoro host al nome della funzione. Questo non è difficile se ti affidi alla funzione guidata, ma goffo da scrivere manualmente. La funzione guidata mostra i nomi completi di qualsiasi UDF in altre cartelle di lavoro…
Se si apre la cartella di lavoro in cui è stata utilizzata la funzione in un momento in cui la cartella di lavoro contenente la funzione è chiusa, verrà visualizzato un messaggio di errore nella cella in cui è stata utilizzata la funzione. Excel se ne è dimenticato! Apri la cartella di lavoro dell'host della funzione, ricalcola e tutto è 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 Excel Aggiungere. Scopri come farlo nel tutorial Crea un componente aggiuntivo di Excel.
Addendum
Dovrei proprio saperlo meglio! Mai, mai, dire mai! Avendoti detto che non esiste una funzione che fornisca il nome del giorno, ora mi sono ricordata quella che può. Guarda questo esempio…
La funzione TESTO restituisce il valore di una cella come testo in un formato numerico specifico. Quindi nell'esempio avrei potuto scegliere =TESTO(A1,"gg") per restituire "gio", =TESTO(A1,"mmmm") per restituire "settembre" ecc. L'aiuto di Excel contiene altri esempi di modi per utilizzare questa funzione.
Se ti è piaciuto il nostro blog, condividilo con i tuoi amici su Facebook. E puoi anche seguirci su Twitter e Facebook.
Ci piacerebbe avere tue notizie, facci sapere come possiamo migliorare, completare o innovare il nostro lavoro e renderlo migliore per te. Scrivici a sito di posta elettronica