Oggi riparliamo delle macro di Excel, quindi dell’automazione del foglio di calcolo. Cerchiamo di profilare le macro Excel e di capire come leggerle per comprendere cosa fanno. Perché in fondo non sono altro che istruzioni che diamo a Excel per farlo lavorare al posto nostro.
Cos’è una macro?
Ripetiamo i concetti di base. Il termine “macro” a livello informatico indica una routine, una procedura, cioè un insieme di comandi e istruzioni che forniamo al programma, nel nostro caso Excel, perché li svolga. Le macro fanno svolgere a Excel una serie di attività a seguito di un singolo comando: quello dell’esecuzione.
Tutti i programmi di Office hanno un motore interno che permette di eseguire macro vba che possono agire su tutti i programmi della suite e limitatamente su programmi e oggetti esterni.
Le macro in Excel sono composte da istruzioni scritte con il linguaggio vba, visual basic for application, un linguaggio semplice e potente basato su termini inglesi che agisce sugli oggetti dell’ambiente della suite e del sistema operativo.
Riassumendo le macro sono sequenze di istruzioni in codice che danno ordini a Excel perché esegua azioni o modifiche sugli oggetti dei fogli di calcolo.
Dove trovi le macro
Le macro sono codice scritto, quindi possiamo trovarle ovunque possiamo inserire un testo, ma se vogliamo farle eseguire a Excel dobbiamo inserire il codice all’interno dei “moduli”, contenitori appositi inseriti all’interno dei fogli di calcolo e degli altri documenti della suite.
Come si accede ai moduli e agli strumenti per lo sviluppo delle macro?
Attraverso il visual basic editor (Vbe), l’ambiente di sviluppo integrato che possiamo richiamare premendo Alt+F11. Il vbe è un vecchio ambiente di sviluppo, rodato ed equipaggiato con gli strumenti che servono alla scrittura e al debugging. Per conoscere meglio il l’editor vba o Visual Basic Editor ti invito a studiare la guida introduttiva all’editor Vba.
I moduli sono pagine bianche in cui inserire il testo del codice e possono essere associate a oggetti specifici, come il foglio Excel, le sue pagine o le maschere utente (userform), o possono essere moduli standard. Per essere eseguito da Excel il codice va inserito nei moduli, scrivendolo direttamente, copiandolo, o importandolo attraverso i comandi relativi.
Come riconosci una macro
Un modulo può contenere centinaia di macro e migliaia di istruzioni, ma per essere eseguite le macro devono essere identificate in modo chiaro e univoco. Come? Attraverso un nome univoco, un inizio e una fine.
Per questo le istruzioni di una macro vengono scritte tra due comandi “marcatori” di inizio e fine delle routine, diversi a seconda che stiamo realizzando routine o funzioni.
Il vba distingue le macro tra funzioni (Function) e routine (Sub). Le funzioni sono procedure che ricevono dati, li elaborano e li restituiscono sotto forma di risultati. Sono identificati da un marcatore di inizio:
“Function NOME_FUNZIONE(param1 As Tipo_param, …) As Tipo_Valore”
che indica quali dati richiede e quali tipo di risultato restituirà, e uno di fine:
“End Function”.
Le routine sono procedure che eseguono azioni su oggetti e svolgono attività nell’ambiente di lavoro. Sono definite da una dichiarazione iniziale:
“Sub NOME_SUB()”
e una finale:
“End Sub”.
Il codice deve essere inserito all’interno dei marcatori, con precise eccezioni (es: dichiarazioni di variabili di modulo o globali). Le routine possono essere definite come pubbliche (Public) o private (Private) a seconda delle loro funzioni e utilità. Una routine dichiarata come privata (es: “Private Sub NOME_SUB()”) non sarà visibile da dentro Excel, per esempio non sarà elencata nella finestra di dialogo “Macro”, e non potrà essere assegnata a controlli su pagina, ma sarà disponibile e richiamabile da altre routine del modulo.
I comandi di inizio e fine delimitano in modo chiaro il codice di una macro, ma la macro per essere eseguita da Excel deve essere identificata, quindi deve avere un nome unico.
I nomi delle macro
Sono etichette che identificano le macro e permettono a Excel di eseguirle senza sbagliare. I nomi delle routine devono rispettare regole precise, per esempio, il nome delle macro:
- può essere costituito da lettere, numeri o caratteri di sottolineatura, e deve essere costituito da una stringa di caratteri contigui,
- non può iniziare con un numero,
- può avere una lunghezza massima di 255 caratteri,
- non può contenere spazi,
- deve escludere caratteri di punteggiatura (es: , .),
- non può contenere caratteri speciali (es: @ # $% ^ & * () + – = [] {}; ‘:! “/ <> \ |? `~ ),
- non deve corrispondere alle parole chiave riservate (es: Sub, And, Or, Loop, If, Do, Len, Close, Else, Select, etc) che fanno parte del Vba.
Se non si rispettano le regole dei nomi, sarà lo strumento di controllo della sintassi del vbe che ci avvertirà della violazione di queste regole, evidenziando la riga con il colore rosso, spostando il cursore e selezionando l’errore, facendo comparire una finestra di dialogo che descrive l’errore.
I nomi delle macro vanno inseriti nella prima riga di una macro di seguito a Sub o Function, per esempio Sub Nome_Macro(). In questo modo le macro saranno individuate e riconosciute da Excel all’apertura del foglio.
Come si realizza una macro
Il codice sostanzialmente si crea in due modi:
- usi il registratore di macro di Excel, uno strumento apposito dall’uso intuitivo che legge le attività svolte su Excel e le traduce nel corrispondente codice vba,
- scrivi il codice direttamente nell’editor Vba con l’ausilio degli strumenti descritti nella guida al visual basic editor.
Usare il registratore di macro è semplice, deleghiamo allo strumento il compito di scrivere il codice, con risultati elementari, ma utilizzabili, realizzando macro operative e poco intelligenti che possono essere utilizzate per automatizzare i fogli Excel. Per farlo devi imparare a usare il registratore, le basi degli strumenti dell’automazione e poco altro. Per approfondire l’argomento ti invito a leggere la guida al registratore di macro.
Invece scrivere il codice richiede la conoscenza del linguaggio vba e degli strumenti per lo sviluppo. Apprendere il vba richiede decine di ore di studio e molte decine anzi centinaia di ore di applicazione, esperienza nella scrittura del codice.
D’altra parte conoscere gli strumenti di automazione permettono di realizzare gli automatismi migliori per soddisfare i nostri bisogni, per esempio per rendere efficienti e professionali i fogli Excel, per risparmiare tempo che possiamo dedicare a cosa più importanti, per sviluppare strumenti personalizzati, per ottenere il controllo di ogni aspetto del foglio di calcolo.
Come si leggono le macro
Si leggono come si scrivono, attraverso la conoscenza del vba, ma limitarsi al linguaggio, sarebbe come sostenere che per leggere un libro è sufficiente sapere l’italiano. In realtà con l’italiano possiamo realizzare la lista della spesa, un annuncio, un racconto, l’articolo di un giornale, un saggio di filosofia, un romanzo, le istruzioni di un impianto di raffinazione.
Questo per spiegarti che per quanto il codice in vba sia semplice e limitato al mondo di Excel, una macro potrà essere una singola istruzione che svolge un’attività, o una lista di centinaia di istruzioni con cui svolgere una serie di compiti complessi, o una serie di macro interconnesse che si richiamano tra loro che possono trasformare un foglio in un vero e proprio applicativo.
Nella maggior parte dei casi, le macro che soddisfano i bisogni di noi utenti sono di complessità ridotta e di facile comprensione. Il secondo passo per leggere le macro è comprendere che raccontano una storia e questa storia si chiama algoritmo.
Le macro e gli algoritmi
Una macro è una serie di istruzioni elementari che Excel deve svolgere con un obiettivo preciso da raggiungere. È come la ricetta per preparare una carbonara in cucina, una lista di istruzioni che spiegano a chi non conosce la ricetta cosa fare passo per passo per realizzare un piatto di spaghetti alla carbonara.
Un algoritmo è un processo mentale con cui affrontiamo un problema, lo dividiamo in parti e risolviamo una parte alla volta. Come? Spiegando con precisione ogni singola azione elementare necessaria per farlo, necessaria per raggiungere il risultato desiderato. L’insieme di tutte le azioni elementari è la soluzione del problema, il modo per raggiungere il nostro obiettivo, quindi l’algoritmo.
Quindi le istruzioni di una macro sono la traduzione di un algoritmo nel linguaggio utilizzato. Per le ricette usiamo l’italiano, per le macro usiamo il Vba.
Un algoritmo e quindi una macro sono composti da:
- un inizio e una fine,
- attività da svolgere su dati o oggetti,
- input, ricezione di dati o informazioni necessarie per eseguire le attività, ricevute attraverso interazioni con l’operatore, da dati contenuti negli oggetti di Excel, o in altri modi,
- output, comunicazione di dati o informazioni necessari all’operatore o altri automatismi, forniti attraverso interazioni con l’operatore, inserendo informazioni e risultati negli oggetti di Excel,
- iterazioni, cioè ripetizioni di una o più attività, gestiti tramite controlli di condizioni precise,
- decisioni, che permettono di scegliere tra una o più attività alternative preimpostate a seconda di condizioni precise.
E altro ancora. Tutto questo naturalmente attraverso la scrittura di istruzioni, l’uso di comandi appositi con cui eseguire controlli, gestire cicli e decisioni con cui gestire situazioni incerte alternative.
Dichiarazioni e comandi del vba
Non posso spiegare un linguaggio come il vba in poche pagine, non a caso vengono pubblicati volumi di molte centinaia di pagine sull’argomento, ma possiamo farne un profilo.
Una macro è composta principalmente da:
- dichiarazioni di metodi e proprietà,
- comandi del vba per gestire il flusso,
- funzioni del vba per l’interazione,
- espressioni di vario tipo,
- altri comandi del vba,
- dichiarazioni di variabili,
- inizializzazioni e assegnazioni,
- altre istruzioni e funzioni del vba.
È fondamentale comprendere che la maggior parte delle istruzioni sono o contengono dichiarazioni di metodi o proprietà, o più raramente dichiarazioni di oggetti.
Cosa sono queste dichiarazioni?
Sono l’indicazione dell’azione che vogliamo che un oggetto svolga, o la modifica che vogliamo fare a una caratteristica di un oggetto di Excel. Gli oggetti in Excel sono pagine, celle, grafici, tabelle e via dicendo. Tutto nel foglio di calcolo è un oggetto e tutto può essere fatto attraverso il vba.
Dal punto di vista del linguaggio, gli oggetti sono contenitori strutturati di dati, che possono contenere o essere contenuti in altri oggetti e che quindi hanno una gerarchia. Gli oggetti possiedono numerose caratteristiche o proprietà, e possono agire grazie a un set di comandi chiamati metodi.
Le dichiarazioni di metodi e proprietà
Le dichiarazioni non sono altro che la concatenazione di più oggetti, metodi e proprietà uniti da punti (.) e servono a indicare a Excel su quale oggetto o insieme di oggetti deve agire e in quale modo.
Queste sono dichiarazioni:
- Activesheet.Name, mostra il nome della pagina attiva;
- Activesheet.Delete, rimuove la pagina attiva;
- Workbooks.Count, conta i fogli Excel aperti;
- Worksheets(“Foglio1”).Activate, attiva la pagina “Foglio1”;
- Worksheets(1).Name, restituisce il nome della pagina con indice 1;
- Application.Workbooks.Close, chiude tutti i fogli di lavoro aperti;
- Worksheets(“Report”).Range(“A2:F40”).Font.Bold, imposta il grassetto nell’intervallo di celle selezionato;
- Worksbooks(“KpiProd”).Worksheets(“Dboard”).PrintOut, stampa la pagina dashboard;
L’ultima parola a destra della catena è il metodo o la proprietà, la penultima parola della catena è l’oggetto specifico e univoco o l’insieme di oggetti su cui lavoriamo. Tutti gli altri termini a sinistra dei precedenti due indicano la gerarchia degli oggetti, cioè gli oggetti che li contengono.
La gerarchia definisce il rapporto tra gli oggetti, cioè a quale oggetto più “grande” appartengono o di cui fanno parte, quali oggetti contengono, o li compongono. Puoi vedere la gerarchia come il rapporto dei diversi elementi di una matrioska.
Per esempio, la pagina di excel (worksheet) è un elemento dell’oggetto foglio di lavoro (workbook) che è un elemento di Excel (application). Application è l’oggetto più grande e di più alto livello che contiene tutti gli altri.
Il modo completo per dichiarare un metodo o una proprietà è in realtà questo:
Application.Insieme(OGGETTO).Insieme(OGGETTO). Insieme(OGGETTO).Metodo/Proprietà
Come vedi è una catena di oggetti che parte dall’oggetto che contiene tutti, cioè Excel (application), e di seguito tutti gli altri oggetti intermedi fino a quello su cui agiamo o modifichiamo. In realtà raramente dichiariamo tutta la gerarchia delle dichiarazioni. Il vba permette di omettere gli oggetti se il codice si riferisce agli oggetti attivi. Cioè in pratica si omette quasi sempre Application, ma anche tutti gli oggetti che seguono se sono attivi.
Per approfondire l’argomento degli oggetti e delle dichiarazioni ti invito a leggere la guida vba e questo post sugli oggetti di Excel dal punto di vista del Vba.
I comandi importanti
Le dichiarazioni sono la parte operativa delle macro, la maggior parte, ed è facile individuarle cercando i punti che separano oggetti, proprietà e metodi. Dopo le dichiarazioni, le istruzioni più frequenti nel codice della macro sono:
- strutture iterative, o cicli,
- strutture decisionali, o condizionali,
- funzioni per l’interazione con l’operatore, per ricevere input o fornire output.
A queste segue un fiume di comandi, istruzioni e funzioni del vba dalle funzionalità e dall’utilità variegata, che richiedono approfondimenti specifici che non possiamo fare qui.
I cicli
I cicli sono tra gli strumenti più utilizzati del vba, in quanto permettono di modificare il flusso del codice, ripetendo più volte porzioni dello stesso. Sono strutture che contengono una o più istruzioni che devono essere ripetute più volte e sono realizzate usando comandi appositi.
Ci sono molte funzioni del vba che generano cicli (loop), ma For/Next è quella più usata perché permette un elevato controllo sulle modalità di uscita dal ciclo. In breve For/Next genera un ciclo a interazione fissa, cioè esegue le istruzioni un numero impostato di volte, definito e conteggiato da un indicatore.
Le funzioni tradizionali (es: Do/Loop) per l’iterazione invece determinano l’uscita dai cicli attraverso la verifica di una o più condizioni logiche, generando quindi un numero di ripetizioni varabile e non predefinito.
La sintassi di For/Next è questa:
For VARIABILE_CONTATORE = inizio To fine [Step passo]
…Istruzioni da eseguire
Next VARIABILE_CONTATORE
La variabile contatore è numerica e di solito di tipo Integer o Long, di cui viene definito subito un valore iniziale e un valore finale. Questo può essere fatto con numeri, espressioni, dichiarazioni, variabili, eccetera. Di solito le variabili usate come contatori sono nominate con una singola lettera (es: I, J, K, ecc) e di default l’incremento del contatore è di 1 ogni volta che esegue le istruzioni di un ciclo. Next è il marcatore di fine ciclo.
Quando viene eseguito il ciclo For, prima viene assegnato il valore iniziale alla variabile, quindi vengono eseguite tutte le istruzioni fino a Next, poi viene incrementato il valore della variabile e il focus ritorna all’inizio del ciclo dove viene confrontato il valore della variabile col valore finale. Se il contatore è minore o uguale al valore finale, la funzione esegue nuovamente il ciclo. Se invece contatore è maggiore del valore finale, il ciclo finisce e continua l’esecuzione della macro dopo Next.
Strutture decisionali
Le strutture decisionali permettono di eseguire controlli su condizioni precise e di conseguenza prendere decisioni, o dal punto di vista del codice, di eseguire codice aggiuntivo o alternativo predisposto.
La funzione del vba più utilizzata per costruire strutture decisionali è If/Then. Serve a controllare l’esecuzione di porzioni di codice attraverso la verifica preliminare di una o più condizioni. Il suo funzionamento di base è piuttosto semplice: se la condizione risulta vera allora verranno eseguite le istruzioni, o eventualmente ne verranno eseguite altre.
La sintassi può variare notevolmente, da una versione minima su una singola riga: “If CONDIZIONE=Vera Then Istruzione”, dove la funzione controlla se la condizione è vera e in caso affermativo esegue l’istruzione dopo lo “Then”. Per esempio “If Età<18 Then Minorenne =True”.
Ma può distendersi nella classica sintassi multiriga gestisce due blocchi di codice alternativi:
If CONDIZIONE=Vera Then
… Istruzioni
Else
… istruzioni alternative
End If
Per esempio, queste istruzioni verificano se è stato assegnato un nome dall’operatore e in caso alternativo ne assegnano uno di deafult:
If nomepaginadati = “” Then
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = “Dati”
Else
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = nomepaginadati
End If
La funzione If/Then può gestire controlli a condizione multipla, a controlli multipli e anche alternativi, come nella seguente sintassi:
If CONDIZIONE1=Vera And CONDIZIONE2=Vera Then
… Istruzioni
ElseIf CONDIZIONE3=Vera Then
… Istruzioni
ElseIf CONDIZIONE4=Vera Then
… Istruzioni
Else
… istruzioni alternative
End If
Queste funzioni “condizionali” sono una componente fondamentale del codice di una macro e sono usate per realizzare controlli, per prendere decisioni come per gestire le decisioni dell’operatore, per gestire gli errori e gli imprevisti e più in generale per realizzare codice intelligente e predittivo.
Richiedono una conoscenza approfondita dell’uso degli operatori (matematici, booleani, di confronto, ecc), per la definizione efficace delle condizioni, e della gestione delle variabili coinvolte, e richiedono esperienza per gestire strutture annidate e strutture miste con i cicli senza incorrere in errori.
Gli strumenti per l’interazione
L’interazione delle macro con l’operatore è una funzionalista spesso necessaria per gestire input e output necessari al corretto o migliore funzionamento dell’automatismo. Tra i comandi del vba più utilizzati c’è MsgBox, la finestra dei messaggi.
La funzione MsgBox() richiama una finestra di dialogo con cui è possibile comunicare informazioni all’operatore, ma anche ricevere conferme, o raccogliere le sue decisioni su quesiti a risposta chiusa.
Viene usata in moltissime situazioni nelle diverse fasi di esecuzione della macro:
- all’inizio per chiedere conferma dell’esecuzione,
- dopo i controlli preliminari per comunicargli imprevisti o la mancanza delle condizioni per l’esecuzione del codice,
- nella gestione degli errori per comunicare il problema, ma anche per chiedergli se e come proseguire il codice,
- prima dell’esecuzione di operazioni irreversibili (rimozioni, cancellazioni, ecc),
- alla fine della macro per comunicargli l’avvenuta esecuzione,
- durante l’esecuzione delle attività per chiedergli di prendere una decisione precisa,
e in molte altre occasioni.
La sintassi della funzione è piuttosto semplice:
MsgBox(prompt, [buttons], [title], [helpfile], [context])
dove “prompt” è il messaggio che sarà visualizzato nella finestra di dialogo scritto tra virgolette (“”), “buttons” è il tipo di pulsanti da visualizzare nella finestra di dialogo, scelti da una lista di costanti tipo “vbOkOnly”, “vbYesNo”, ecc, “title” è il titolo della finestra di dialogo scritto tra virgolette (“”), “helpfile” identifica il file della guida da usare e “context” indica il punto della guida a cui puntare.
Non possiamo controllare la posizione e la grandezza della finestra di dialogo, ma possiamo controllarne contenuto e titolo, possiamo assegnargli uno tra numerosi set di pulsanti e possiamo scegliere l’icona che appare nella finestra. Per esempio, se vogliamo usare la finestra dei messaggi per comunicare un’informazione all’operatore, basterà scrivere qualcosa tipo:
MsgBox “MESSAGGIO”, vbQuestion + vbOKOnly, “TITOLO”
come vedi senza parentesi, variabili o altro.
Se invece vuoi avere conferme o decisioni dall’operatore:
NOME_VARIABILE = MsgBox(“MESSAGGIO E DOMANDA”, vbYesNo, “TITOLO”)
dove è necessario raccogliere l’interazione dell’operatore con i pulsanti della finestra di dialogo per poi andare a verificare la scelta presa e agire di conseguenza.
Orientarsi nelle macro
Se non conosci il vba non potrai leggere correttamente i comandi della macro, ma con quelli che abbiamo visto finora puoi iniziare ad orientarti. Ora sai:
- riconoscere una macro,
- riconoscere il suo nome,
- dove inizia e dove finisce,
- riconoscere la tipologia della maggior parte delle istruzioni
- riconoscere le dichiarazioni, sono le catene di termini uniti da punti,
- che i cicli sono strutture che ripetono il codice all’interno,
- riconoscere le strutture If/then eseguono il codice all’interno solo a precise condizioni,
- individuare le finestre dei messaggi, Msgbox, e di conseguenza il suo cugine Inputbox.
Inoltre se il codice della macro è scritto come si deve, puoi identificare i gruppi funzionali separati o accompagnati dai commenti verdi, riconoscere i livelli del codice e quindi le strutture che gestiscono il flusso, identificare i principali comandi e non solo grazie al colore blu delle parole.
Questo naturalmente è solo un inizio e se conosci l’inglese potrai iniziare a leggere le parole del codice in quella lingua e riconoscere oggetti e verbi e un inizio di significato.
La struttura delle macro
Abbiamo visto che le macro hanno una struttura fine composta da istruzioni, prevalentemente dichiarazioni, e sono la traduzione di un algoritmo composto da attività elementari, input e output, iterazioni e decisioni. Ma se guardiamo il codice delle macro da più lontano, possiamo iniziare a cogliere unità funzionali e strutture più grandi che dipendono da diversi fattori, per esempio dal foglio in cui le routine lavorano, e che influenzano la forma del codice, l’ordine di esecuzione, le sue caratteristiche.
Le macro hanno un inizio, un corpo e una fine, un preciso obiettivo, partono da condizioni iniziali necessarie al loro funzionamento e seguono un ordine, una struttura logica di attività o blocchi di codice con relazioni precise. Questa struttura non è visibile nelle macro piccole, dirette, operative, mentre diventa evidente nelle macro più grandi.
A queste “grandezze” sono definibili attività tra cui:
- controlli di varia natura;
- interazioni con l’operatore per comunicare informazioni o ricevere input,
- attività operative dirette o condizionate, anche complesse,
- decisioni basate sulle condizioni preliminari o di lavoro, a cui è connesso codice aggiuntivo o alternativo,
- dichiarazioni delle variabili, contenitori per gestire informazioni che devono essere utilizzate più volte,
- codice per la gestione degli errori
Queste naturalmente sono semplificazioni delle attività realizzate dal codice e vogliono solamente spiegare a chi si avvicina alla lettura e scrittura delle macro il ruolo e la funzione delle unità funzionali che costruiamo nel codice. Per approfondimenti consiglio la lettura della guida alle macro e della guida al vba.
Conclusioni
Come abbiamo visto le macro sono istruzioni scritte nel linguaggio comprensibile a Excel. Servono a dare comandi e indicazioni al foglio di calcolo perché svolga serie di attività al posto nostro.
Le macro in Excel sono quindi elenchi di istruzioni codificate con il visual basic for application, un vecchio linguaggio semplice e potente basato su termini inglesi che agisce sugli oggetti dell’ambiente della suite e non solo.
Il codice delle routine è semplice: ha un inizio e una fine, un nome e un ordine di esecuzione, codice operativo fatto da dichiarazioni di metodi e proprietà e comandi per controllarne il flusso con cui costruire controlli, interagire con l’operatore, gestire decisioni e molto altro.
Questa vuole essere un’introduzione al vba, un modo per avvicinarti al linguaggio e agli strumenti dell’automazione. Spero di averti incuriosito o semplicemente di averti aiutato a comprendere meglio il vba e l’automazione di Excel.
Ti piacerebbe imparare molto di più sull’automazione di Excel?
Questo corso ti guida da zero all’automazione dei tuoi fogli:
Grazie dell’attenzione e a presto ;D
PS: Se questo post ti è piaciuto o ti è stato utile, condividilo con gli amici sui social.
Puoi farlo attraverso i pulsanti qui di seguito. Grazie
