Una buona introduzione al Visual Basic per Office...

 Da ExcelProfessionale

Guida VBA Excel

22 feb 2020

Cos’è il Vba? Cosa si può realizzare con questo linguaggio? Chi dovrebbe usarlo e perché? Quali sono le basi del vba? Questa guida vba è un’introduzione al linguaggio con cui possiamo realizzare automatismi per Excel.

Cos’è il Vba?

Vba è l’acronimo di Visual Basic for Application e in sintesi è l’utilizzo del Visual Basic per la realizzazione e l’esecuzione di routine dentro l’ambiente di lavoro di Microsoft Office.

Il Visual Basic è il linguaggio a oggetti realizzato da Microsoft all’inizio degli anni 90 per la creazione di programmi per windows, il suo sistema operativo, derivato a sua volta dal vecchio Basic. Nel 1992 Microsoft rilasciò il Visual Basic e nel 1995 impose un linguaggio standard di programmazione per i suoi software a partire dall’Office: il Visual Basic for Applications.

Nel 2008 Microsoft ha abbandonato il visual basic per linguaggi più performanti. E il Vba? Dopo 20 anni il vba è ancora presente e utilizzato direttamente e indirettamente da centinaia di milioni di utenti nel mondo.

Nonostante circolino da tempo voci sulla sua sostituzione per altri linguaggi, o perfino sull’abbandono del Vba, Microsoft non ha mai accennato nulla a proposito e difficilmente lo farà nel breve termine, soprattutto in considerazione delle decine di milioni di clienti/utenti al mondo che usano il vba per sviluppare macro.

Quale differenza c’è tra il Visual basic e il Vba?

A livello di linguaggio non ci sono differenze significative, se non quelle relative al diverso ambito di lavoro. Il Vb era il linguaggio con cui si realizzavano programmi per le vecchie versioni di Windows, il sistema operativo, mentre il Vba è il linguaggio con cui si realizzano procedure che vengono usate internamente al pacchetto Office.

Cos’è una macro?

Il termine “macro” a livello informatico indica una procedura, cioè un insieme di comandi e istruzioni svolti durante l’esecuzione di un programma. Le macro permettono di realizzare una serie di attività con un singolo comando, quello della loro esecuzione.

Tutti i programmi di Microsoft Office hanno un motore interno che permette di eseguire macro vba che possono interagire con tutti i programmi di office (es: una macro su Excel può agire su un documento Word) e anche agire limitatamente su programmi e file esterni.

In realtà le macro non sono un’esclusiva di Microsoft Office. Questo è il più famoso, ma solo uno tra software esistiti ed esistenti che prevedono la possibilità di lanciare procedure per automatizzare attività. Come il Vba è solo uno dei linguaggi che vengono o sono stati impiegati per farlo, anche se forse è il più semplice e performante.

Ci sono o ci sono stati numerosi software con la stessa possibilità di creare ed eseguire macro con diversi linguaggi di programmazione, con l’obiettivo di automatizzare le attività ripetitive. Tra i più noti, ricordiamo OpenOffice, pacchetto opensource per l’ufficio, nato da Staroffice e dotato di un’interfaccia apposita con cui creare e gestire macro in diversi linguaggi di programmazione. E il suo successore, Libreoffice, altro pacchetto opensource in grado anche di supportare le macro sviluppate in Vba. Inoltre esistono e sono esistiti anche editor di macro, cioè programmi dedicati con cui creare macro in diversi linguaggi in grado di agire sul sistema operativo e altri software (es: AutoHotkey, AutoIt, iMacros, ecc).

Per approfondire l’argomento macro ti invito a leggere la guida alle macro di Excel.

Cosa si può realizzare con il Vba?

Automatismi. Procedure di comandi che realizzano automaticamente attività, elaborazioni, eccetera.

In particolare il vba permette di realizzare strumenti con cui:

1.       automatizzare attività operative e decisionali che gli operatori svolgono dentro Excel, allo scopo di far risparmiare tempo all’utente;

2.       realizzare nuovi strumenti di elaborazione per l’ambiente di lavoro, classicamente nuove funzioni che eseguono calcoli specifici e personalizzati;

3.       realizzare nuovi strumenti di gestione per l’ambiente di lavoro, per esempio finestre di dialogo personalizzate, o controlli più evoluti con cui gestire gli automatismi e gli altri strumenti del foglio di lavoro.

Perché imparare il Vba?

Il Vba viene studiato e usato da decine di milioni di utenti nel mondo per le seguenti ragioni:

1.       fa risparmiare tempo, soprattutto a chi usa Excel per molte ore alla settimana;

2.       è il modo che il produttore mette a disposizione per automatizzare Excel (a meno di non voler realizzare componenti aggiuntivi da installare in Office); attenzione non è l’unico modo in assoluto, per esempio puoi farlo anche con il python, ma il vba è il modo offerto dal produttore e per ora è anche il più efficiente;

3.       è un linguaggio piuttosto semplice e intuitivo da imparare e utilizzare, anche per chi non è un programmatore, si impara in pochi mesi e si ottengono risultati fin da subito;

4.       è efficace e performante, anche se relativamente efficiente e sicuro; con il vba si possono replicare tutte le attività che si possono svolgere su excel e gli altri software di Office, e altre che non possiamo realizzare direttamente.

Per esempio, hai realizzato con Excel uno strumento con cui segui le tue spese, gli investimenti finanziari, gli indicatori della tua attività professionale, con cui controlli i risultati del tuo lavoro, del tuo team, del reparto o ufficio che ti è stato affidato? Funziona bene, è efficace, ma spendi molto tempo per gestirlo?

Realizzare macro con il vba può essere una soluzione per ridurre in modo significativo il tempo che spendi per gestire e aggiornare i tuoi fogli di lavoro. Non è certamente l’unica e potrebbe non essere la migliore, per esempio in diverse situazioni sarebbe consigliabile abbandonare Excel per un software dedicato, ma se hai deciso di usare Excel e vuoi rendere più efficienti i tuoi fogli di lavoro allora le macro sono il più efficace degli strumenti con cui farlo.

I limiti del visual basic

Il visual basic non è perfetto, anzi, ha una lunga lista di limiti e lacune, tra cui: lentezza, minore efficienza, ridondanza, sicurezza, limiti nelle dimensioni delle variabili e nella gestione della memoria, eccetera. Se sei un programmatore lo sai meglio di me.

Ma ha senso parlare di limiti per il vba?

Il visual basic non sarebbe stato abbandonato da Microsoft per altri linguaggi se non fossero superiori, più performanti, sicuri ed efficienti. Il vba invece è dedicato alla realizzazione di routine all’interno dell’ambiente Office da parte di non–programmatori di solito con obiettivi precisi e limitati, in primis risparmiare tempo. È difficile che l’utente medio di Excel tocchi e inizi a percepire i limiti del vba come ostacoli per raggiungere i propri obiettivi.

Dove si inserisce il codice Vba?

Le macro vengono gestite attraverso il visual basic editor (Vbe), richiamabile dentro Excel premendo Alt+F11 o dalla scheda sviluppo della barra multifunzione.

Il vbe è un vecchio ambiente di sviluppo, rodato ed equipaggiato con gli strumenti che servono alla scrittura e al debugging.

Ha diverse lacune, ma non significative e in diversi casi compensabili seguendo le buone pratiche o attraverso l’installazione di componenti aggiuntivi, anche sviluppati da terze parti.

Ci sono altri “ide”, ambienti di sviluppo integrati, ed editor esterni?

Certo, ci sono alternative alla gestione del codice, dai vecchi strumenti microsoft per il vb (es: VB6 e Vb.Net) agli editor multilinguaggio, ma li sconsiglio, perché meno efficienti e perché alla fine dovrai sempre gestire le macro tramite vbe.

Il codice va inserito in un modulo, un contenitore teorico che puoi immaginare come una pagina bianca su cui scrivere i comandi delle routine.

I moduli sono di diverso tipo e possono essere associati, per esempio alle pagine di Excel, al foglio intero o alle maschere, per gestire le macro collegate agli eventi relativi all’oggetto. Oppure i moduli possono essere standard, dove le macro possono essere richiamate tramite controlli, per esempio pulsanti su pagine o su maschere, o tramite gli strumenti predisposti di excel, come la finestra “Macro”.

Infine, esistono moduli di classe in cui vengono definiti nuovi oggetti, le loro caratteristiche, le proprietà, i metodi che li riguardano, eccetera.

Questi sono i tipi di moduli disponibili:

1.       Modulo standard,

2.       ThisWorkbook o Questa cartella di lavoro,

3.       Moduli pagina (sia sheets che chart)

4.       Moduli Form, o moduli maschere,

5.       Moduli di classe

Il codice delle macro viene scritto tra due marcatori di inizio e fine delle macro, diversi a seconda che stiamo realizzando routine o funzioni.

Funzioni e Subroutine

Il vba distingue 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” o assegnabile a controlli su pagina, ma sarà disponibile e richiamabile da altre routine del modulo.

Questa è un’utile differenziazione per la gestione di macro modulari funzionali a un progetto più grande o altri automatismi.

Il vba e i nomi

I nomi delle routine, ma in generale tutti i nomi usati dal linguaggio Vba devono rispettare regole precise.

In particolare il nome delle macro:

1.       può essere costituito da lettere, numeri o caratteri di sottolineatura, e deve essere costituito da una stringa di caratteri contigui,

2.       può avere una lunghezza massima di 255 caratteri,

3.       non può iniziare con un numero,

4.       non può contenere spazi,

5.       non può contenere caratteri di punteggiatura (es: , .),

6.       non può contenere caratteri speciali (es: @ # $% ^ & * () + – = [] {}; ‘:! “/ <> \ |? `~ ),

7.       non può 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, 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.

Anche gli altri nomi correlati al linguaggio hanno regole analoghe, per esempio quello delle variabili, ma non le elencheremo in questa piccola guida vba.

Aldilà delle regole che devono essere rispettate, consiglio sempre di assegnare nomi parlanti e descrittivi delle funzioni, azioni, attività, eccetera.

Nel caso si voglia usare più parole, il consiglio è separarle con un carattere sottolineato, questo “_”.

Oppure usare le maiuscolo per evidenziare l’inizio della parole della frase, per esempio così: “NomeRoutine()”, o cosi: “NOMEroutine()”.

Il vba e l’inglese

Come sai o avrai già intuito il vba è un linguaggio “anglosassone”, cioè è composto da termini, oggetti, metodi, proprietà, funzioni espresse attraverso parole della lingua inglese, in forma estesa o contratta.

Per esempio: charts, worksheets, range, select, msgbox (message box, scatola dei messaggi), application, delete, open, close, windows, eccetera.

È naturale che sia così ed è anche un aiuto se conosci l’inglese, in quanto potrai riconoscere più facilmente ruolo e funzione delle parole, facilitandoti nella costruzione del tuo “vocabolario” e con la sintassi del linguaggio. Per esempio, una dichiarazione come questa:

Application.Workbooks(“Report”).Worksheets(“Mensile”).ChartObjects(“Fatturato”).PrintOut

Può essere letta o tradotto come: stampa il grafico “Fatturato” della pagina “Mensile” del foglio “Report” di Excel.

Se non conosci l’inglese avrai più difficoltà iniziale a costruirti il vocabolario e ad associare alle singole parole il loro ruolo o funzione, ma da un altro punto di vista ti darà l’occasione di apprendere un poco di inglese.

Come si scrivono le macro?

Il codice sostanzialmente si crea in due modi:

1.       usando 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, realizzando macro operative e poco intelligenti che però possono esseree utilizzate per automatizzare i fogli Excel. Per approfondire l’argomento ti invito a leggere la guida al registratore di macro di Excel professionale.

2.       scrivendo il codice attraverso la tastiera, direttamente nell’editor Vba con l’ausilio degli strumenti descritti nella guida al visual basic editor.

Mentre il primo modo è semplice, di fatto deleghiamo a uno strumento, il registratore di macro, il compito di scrivere il codice, con risultati elementari, ma utilizzabili, 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.

Studiando un linguaggio di programmazione, si impara che in realtà ci sono modi diversi di scrivere il codice, per esempio puoi farlo riga per riga, oppure puoi prendere codice realizzato in precedenza e adattarlo all’obiettivo presente, oppure puoi lavorare con codice predisposto anche scritto da altri. Cosi come ci sono strategie diverse per progettare e sviluppare il codice e perfino per usare il registratore di macro. Approfondiremo questi argomenti con post dedicati.

La struttura delle macro

Come abbiamo visto le macro sono una sequenza di istruzioni e come tale hanno un inizio, un corpo e una fine, ma non solo, essendo procedure operative le routine hanno un preciso obiettivo, per esempio agire su uno o più oggetti di Excel, partendo da condizioni iniziali definite e seguendo un ordine di scrittura, una struttura logica che rispetti le regole della programmazione, del linguaggio e dell’ambiente di lavoro.

Ci sono macro composte da una sola o una manciata di righe di codice che eseguono attività dirette e non hanno bisogno di altro, ma nella maggior parte dei casi le macro evolute:

1.       richiedono la dichiarazione di una o più variabili, informazioni che devono essere utilizzate più volte, elaborate, ecc;

2.       possono richiedere la verifica di una o più condizioni iniziali,

3.       spesso richiedono l’interazione con l’operatore per ricevere input (conferme, informazioni, o decisioni),

4.       possono richiedere il controllo degli input, o delle condizioni di lavoro,

5.       agiscono come necessario per raggiungere l’obiettivo definito, seguendo un ordine logico preciso,

6.       e per farlo possono prendere decisioni basate sulle condizioni di lavoro,

7.       se necessario comunicano l’avvenuta esecuzione e i risultati,

8.       gestiscono gli eventuali errori imprevisti.

Tutte queste attività e le altre che non ho elencato hanno una posizione nel codice, devono rispettare un ordine preciso e le relazioni che esistono tra loro e tutto questo definisce una struttura logica e teorica. Anzi una gamma di strutture che seguiamo e replichiamo quando scriviamo il codice anche senza che ce ne rendiamo conto.

Le strutture del codice

Conoscere queste strutture, imparare a riconoscerle e usarle per gestire lo sviluppo del codice ci semplifica la vita, aiuta a gestire e a standardizzare il nostro lavoro con notevoli vantaggi.

Queste strutture soprattutto sono di notevole aiuto ai principianti, danno loro la giusta prospettiva, una falsariga da seguire nello sviluppo. È qualcosa di analogo alle sequenze di mosse che nelle arti marziali i maestri fanno eseguire agli allievi per comprenderne natura, limiti e possibilità. Ciò che deve essere prima, ciò che può seguire dopo, qual è il risultato, l’efficacia e l’efficienza del movimento, comprendere i limiti delle regole che dobbiamo rispettare (il corpo umano, la fisica dei movimenti, eccetera).

Ma queste strutture sono utili anche per i più esperti che vedono linguaggio e programmazione come strumenti da usare nel modo migliore per ottenere il risultato richiesto nel modo più efficiente.

Introduzione agli oggetti

Excel, Vba. Excel è un ambiente di lavoro composto da oggetti e il linguaggio Vba essenzialmente lavora su questi oggetti. Gli oggetti in Excel possono essere visti come contenitori strutturati di dati, che possono contenere o essere composti da altri oggetti, e che quindi hanno una gerarchia. Gli oggetti possiedono numerose caratteristiche o proprietà, possono realizzare attività grazie a un set di comandi chiamate metodi, e possono essere associati a eventi.

Per esempio, oggetti sono il foglio di lavoro, il pulsante di comando, le pagine, le tabelle, gli intervalli di celle, i grafici, eccetera.

Cosa sono le proprietà di un oggetto?

Le proprietà rappresentano gli attributi e le caratteristiche che definiscono l’oggetto e lo distinguono dagli altri oggetti simili (es: grandezza del font, colore della cella, altezza della riga, tipo del grafico, nome della pagina, ecc). Ogni oggetto ha una lista di proprietà che lo descrivono.

Cosa sono i metodi?

Sono le funzionalità degli oggetti, cioè l’elenco delle azioni che l’oggetto può compiere o può subire, per esempio l’apertura di un foglio, il suo salvataggio, la selezione di una tabella, l’attivazione di una pagina, eccetera. Ogni oggetto ha una lista di metodi che lo riguardano.

Cosa sono gli eventi?

Sono momenti e situazioni generate da certe condizioni, conseguenze delle attività dell’operatore, a cui è possibile associare metodi che modificano proprietà di oggetti e non solo. Per esempio evento è l’apertura di un foglio excel, l’attivazione di una pagina, la selezione di un oggetto, il clic del mouse su un controllo e via dicendo.

Cosa sono le gerarchie di oggetti e gli insiemi di oggetti?

Gli insiemi sono gruppi di oggetti con le stesse caratteristiche, per esempio la pagina “Dati” fa parte dell’insieme delle pagine del foglio di lavoro. Ogni volta che possono esserci oggetti simili, allora si possono definire degli insiemi di questi oggetti, dove i singoli componenti possono essere distinguibili e richiamabili usandone il nome.

Si può dichiarare il singolo oggetto dell’insieme indicandone tra parantesi il nome, per esempio: “Workbooks(“Budget”) è il foglio Budget.

Cos’è la gerarchia degli oggetti?

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.

Conoscere gli insiemi e la gerarchia degli oggetti è importante per la comprensione e la scrittura del Vba.

Riassumiamo tutto con un esempio:

una pagina di excel (worksheet) è un oggetto parte di un insieme di pagine (worksheets), contenute nel foglio di lavoro (workbook), ed è composta e contiene oggetti come intervalli di celle (range), tabelle (listobject, pivottables, ecc) e grafici (chartobjects), per esempio. Tutti questi oggetti hanno una lista di proprietà, per esempio il valore delle celle (cells.value), o il nome delle pagine (worksheet.name), e una lista di funzionalità, i metodi, con cui è possibile agire sugli oggetti e sulle loro proprietà, come per esempio aggiungere una pagina (worksheets.add), selezionarla (worksheets.select), copiarla (worksheets.copy) e via dicendo.

Infine l’oggetto, per esempio la pagina, dispone di eventi specifici (activate, change, ecc) a cui è possibile associare codice vba, per esempio per aggiornare le tabelle pivot contenute.

Come si scrive il codice?

Partendo da questo, il codice che agisce sugli oggetti viene scritto come dichiarazioni che descrivono l’oggetto, o un insieme di oggetti, e la gerarchia relativa, di cui si modifica o richiama una proprietà, o su cui si esercita un metodo, per esempio in questi modi:

1.       Proprietà (es: Activesheet.Name, mostra il nome della pagina attiva);

2.       Metodo (es: Activesheet.Delete, rimuove la pagina attiva);

3.       Insieme(oggetto).Proprietà (es: Application.Workbooks.Count, conta i fogli Excel aperti);

4.       Insieme(oggetto).Metodo (es: Worksheets(“Foglio1”).Activate, attiva la pagina “Foglio1”);

5.       Insieme(oggetto).Proprietà (es: Worksheets(1).Name, restituisce il nome della pagina con indice 1);

6.       Insieme(oggetto).Metodo (es: Workbooks.Close, chiude tutti i fogli di lavoro aperti);

7.       Insieme(oggetto).Insieme(oggetto).Proprietà (es: Worksheets(“Report”).Range(“A2:F40”).Font.Bold, imposta il grassetto nell’intervallo di celle selezionato);

8.       Insieme(oggetto). Insieme(oggetto).Metodo (es: worksbooks(“Db Kpi Prod”).Worksheets(“Dboard”).PrintOut, stampa la pagina dashboard);

Come? Tutti questi modi? Come faccio a sapere quale usare, quando e come?

L’omissione degli oggetti padri e l’oggetto attivo

Il modo corretto per dichiarare un oggetto è in realtà:

Application.Insieme(OGGETTO).Insieme(OGGETTO).OGGETTO

Cioè un’espressione che parte dall’oggetto che contiene tutti, cioè Excel (application), per poi dichiarare gli altri oggetti fino a quello su cui vuoi agire o di cui vuoi richiamare o modificare una proprietà. Per esempio per riferirsi a un intervallo di celle preciso, la notazione corretta dovrebbe essere:

Application.Workbooks(“RepProd”).Worksheets(“Dati”).Range(“C1:C10”)

In realtà farlo ogni volta sarebbe una perdita di tempo, perciò 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.

Prendiamo l’ultimo esempio, possiamo scriverlo solo come Worksheets(“Dati”).Range(“C1:C10”) se ci stiamo riferendo a un range di una pagina precisa (Dati) del foglio di lavoro.

Ma potremmo scrivere solo Range(“C1:C10”) se siamo all’interno della pagina, o sarebbe meglio dire se la macro verrà lanciata e agirà all’interno della pagina mantenendola attiva, cioè senza cambiare focus o selezione.

L’oggetto padre attivo

Da un altro punto di vista, sarebbe più corretto dire che quando Excel non trova l’indicazione degli oggetti padre, interpreta il codice come se si riferisse a quelli attivi.

Quindi è importante quando progetti e scrivi il codice avere sempre chiaro il focus, cioè quale foglio è o dovrebbe attivo.

Uno degli errori classici dei principianti è dimenticarselo e far lavorare il codice sulle pagine attive anche se non sono quelle su cui deve realmente agire.

Nel codice vba in realtà ci sono oggetti specifici per indicare gli oggetti attivi (es: ActiveWorkbook, ActiveSheet, Activecell, ecc) che però vanno usati con attenzione e precisione. Per esempio, “ActiveSheet.Name” restituisce il nome della pagina attiva, mentre “ActiveWorkbook.Save” salva il foglio Excel attivo.

In realtà potresti omettere questi oggetti se ti riferisci a un oggetto figlio, per esempio “ActiveSheet .Range(“C1:C10”)” e “Range(“C1:C10”)” sono equivalenti.

Questa regola dell’omissione è generale, ma ha delle eccezioni nel caso di macro inserite in moduli associati a oggetti, in particolare:

1.       se ometti la pagina in una macro in un modulo pagina (“sheets”), excel dedurrà che ti stai riferendo alla pagina della macro e non a quella attiva;

2.       se ometti il foglio nel modulo foglio (ThisWorkbook), excel penserà che ti stai riferendo al foglio che contiene la macro e non a quello attivo.

Quali sono gli oggetti principali di Excel e Vba?

Gli oggetti fondamentali di Excel e vba sono i seguenti:

1.       Application indica la sessione di Excel, quindi tutti i fogli aperti. Di solito la vedi poco nel codice, perché nella maggior parte dei casi si lavora su oggetti più piccoli e come abbiamo visto viene omessa nelle espressioni degli oggetti. I richiami più frequenti riguardano la modifica di solito temporanea di alcune proprietà come DisplayAlerts, ScreenUpdating, Calculation, eccetera (es: Application.DisplayAlerts sospende gli allarmi di excel, mentre Application.ScreenUpdating sospende l’aggiornamento dello schermo).

2.       Workbook rappresenta una cartella di lavoro di Excel, ed è un elemento dell’insieme workbooks. Questo oggetto contiene quasi tutti gli oggetti di Excel (pagine, range, celle, colonne, tabelle, pivot, controlli, grafici, eccetera) e viene usato poco, soprattutto nel caso di attività che coinvolgono più fogli di lavoro.

3.       Worksheet rappresenta la singola pagina excel che a sua volta contiene celle, colonne, righe, tabelle, grafici, eccetera. In realtà worksheets descrive solo uno dei 4 tipi di pagine supportate da Excel: pagina standard (worksheet), grafici (chart), pagina macro (macro Xlm) e pagine di dialogo (finestra di dialogo Excel 5.0). Di fatto gli ultimi due tipi sono caduti in disuso lasciando il campo ai soli Worksheet e Chart. Worksheet fa parte dell’insieme Worksheets e Sheets e contiene tutti gli oggetti della pagina, per esempio range, cells, colonne, righe, tabelle, eccetera.

4.       Range rappresenta un intervallo di celle, quindi dalla singola cella, righe e colonne, fino a una matrice di celle. È uno degli oggetti più richiamati, viene dichiarato come Range(“CELLA”) per la singola cella, oppure “Range(“CELLA:CELLA”)” per l’intervallo di celle. Per esempio: Worksheets(1).Range(“A1”).Value = “Titolo della pagina” inserisce la stringa nella cella A1. È l’oggetto che possiede più proprietà e metodi ed è più complesso di quello che appare a prima vista.

5.       Chart rappresenta un grafico o diagramma ed è parte dell’insieme Charts, ma anche dell’insieme Sheets. Come worksheet è un oggetto “pagina”, ma invece di presentare celle e oggetti incorporati, mostra solo un grafico specifico. È un oggetto poco utilizzato rispetto a worksheet, ma è il modo più efficace per visualizzare un grafico. Per esempio “Charts(1).Name = “Produttività” cambia il nome del grafico 1, mentre “Charts(1).PrintOut” lo stampa.

6.       ChartObject è un oggetto che rappresenta anch’esso un grafico, ma incorporato nella pagina (worksheet) ed è membro dell’insieme ChartObjects. I grafici sono strutture complesse e sono caratterizzati da molte proprietà per gestirne i diversi elementi e le loro caratteristiche. Quando i grafici sono installati nella pagina, per esempio per realizzare dashboard o report, allora è necessario usare questo oggetto.

7.       ListObject rappresenta una tabella Excel presente in una pagina (worksheets) ed è membro dell’insieme ListObjects. È a sua volta caratterizzato da numerose proprietà che identificano tra l’altro le molte parti di una tabella (es: listcolumns, listrows, databodyrange, headerrowrange, totalrowrange, ecc), oltre che permetterne di gestire strumenti e impostazioni (es: autofilter, showautofilter, ecc). 

La gestione di questi oggetti richiede precisione, ma è meno complessa di quello che può apparire. Per esempio, “Worksheets(1).ListObjects(“ResaR1”). DataBodyRange.Select” seleziona il corpo della tabelle senza intestazioni e totali, “Worksheets(1).ListObjects(“ResaR1”). ShowTotals = True” mostra la riga dei totali in fondo alla tabella.

8.       PivotTable rappresenta una tabella pivot installata in una pagina (worksheets) ed è membro dell’insieme PivotTables. È l’oggetto da cui partire per scrivere codice che gestisca le tabelle pivot in automatico. Considerato la facilità e la velocità con cui si realizzano e si gestiscono, considerato che sono dinamiche e interattive, ha senso gestire le pivot con il codice? Sì, per esempio per preimpostarle in automatico, per copiarle, modificarle e molto altro. D’altra parte il codice per gestire le pivot può essere complesso e spesso si preferisce non automatizzarle, o usare il registratore di macro per scrivere la parte operativa.

9.       Selection è un oggetto speciale che memorizza o meglio indirizza all’oggetto selezionato con il metodo Select. Di solito associato è a un intervallo di celle, viene ampiamente usato per semplificare la scrittura del codice quando si deve intervenire ripetutamente sulle proprietà della selezione, per esempio per impostare la formattazione, valori e formule, ma può essere utilizzato per tutto. È importante gestire con attenzione il focus.

Quali sono gli insiemi fondamentali?

Gli insiemi fondamentali sono quelli composti dagli oggetti più comuni e basilari, in particolare:

1.       Workbooks che contiene tutti gli oggetti Workbook aperti in Excel. Ricordo che si può dichiarare il singolo oggetto dell’insieme indicando tra parantesi il nome (es: Workbooks(“Dashboard”) ). Si può agire sull’intero insieme di fogli con i metodi relativi, per esempio “Workbooks.Close” chiuderà tutti i fogli Excel aperti.

2.       Worksheets è l’insieme di tutti gli oggetti Worksheet nella cartella di lavoro specificata o attiva. Usando i metodi relativi si può agire sull’intero insieme (“Worksheets.Add” aggiunge una nuova pagina), o su un elemento specifico dell’insieme indicandone il nome o il riferimento/indice, per esempio: Worksheets(“NOME”).Select, oppure Worksheets(INDICE).Select porterà la pagina relativa in primo piano.

3.       Sheets è l’insieme di tutte le pagine del foglio di lavoro attivo o specificato e contiene sia oggetti Worksheet, pagine standard, che Chart, cioè pagine che contengono grafici e grafici incorporati. Vale quanto detto sopra per l’indicazione del singolo oggetto e per l’applicazione dei metodi relativi (es: Sheets.PrintOut stampa tutte le pagine, Sheets(“Report”).PrintOut stampa solo la pagina “Report”).

4.       Charts è l’insieme delle pagine dedicate ai grafici (Chart), è parte dell’insieme Sheets appena descritto ed è il cugino di Worksheets.

5.       ChartObjects è l’insieme dei grafici incorporati nella pagina e quindi parte di Worksheets, anche per lui vale quanto visto finora su metodi e singoli oggetti. Per esempio “Worksheets(1).ChartObjects.Delete” cancellerà tutti i grafici della pagina 1.

6.       ListObjects è l’insieme delle tabelle presenti nelle pagine Excel, quindi parte di worksheets, ed è molto utile nella gestione automatica delle tabelle. Vale quanto detto finora.

7.       PivotTables è l’insieme delle tabelle pivot presenti nella pagina ed è l’analogo di ListObjects. Vale quanto ripetuto finora. Per esempio “Worksheets(2).PivotTables.Count” restituisce il numero di tabelle pivot presenti nella pagina.

Ora passiamo alle proprietà.

Quali sono le proprietà più comuni?

Le proprietà sono centinaia, alcune proprietà di certi oggetti hanno lo stesso nome di altri oggetti, alcune esistono solo per quell’oggetto, mentre altre sono presenti nella lista delle proprietà di decine di oggetti (es: select). Questa breve lista è un estratto e vuole essere solo un esempio:

1.       Name è forse la proprietà più comune, tutti gli oggetti hanno un nome che può essere modificato o assegnato. Alcuni oggetti ne hanno perfino due, per esempio le pagine (worksheet) hanno un nome “indicizzato” dell’oggetto che è quello che appare quando ne creiamo di nuove (Foglio1, Foglio2, ecc) e corrisponde al nome “etichetta” finché non lo modifichiamo. Per esempio, ActiveWorkbook.Worksheets (“Foglio1”). Name = “Dati”, modifica il nome della pagina da Foglio1 a Dati.

2.       Value è una delle proprietà più usate e ripetute nel codice, associata di solito a Range e Cells, e usata per l’inserimento di valori nelle celle. Per esempio, Worksheets(1).Range(“B2”).Value = 32, assegna il valore 32 alla cella B2.

3.       Visible è una proprietà con cui si può nascondere l’oggetto, di solito una pagina. Con Worksheets(1).Visible = False si nasconde la pagina 1, con Worksheets(1).Visible = True la si rende nuovamente visibile.

4.       Formula è una proprietà simile a Value, ma che invece di inserire un valore inserisce una formula con notazione standard Excel. Per esempio, Worksheets(1).Range(“D1”).Formula = “=SE(B2=“”; “”; B2)”, imposta la formula tra virgolette nella cella D1.

5.       FormulaR1C1 è analoga alla precedente, ma inserisce le formule con notazione R1C1. Per esempio, Worksheets(1).Range(“D1”).FormulaR1C1 = “=SE(R2C2=“”; “”; R2C2)”.

6.       Font è una proprietà di Range che definisce il font impiegato dall’intervallo di celle relativo. Font è anche un oggetto che contiene gli attributi dei font. Per esempio, Range(“A1”).Font.Name restituisce il nome del font.

7.       Count è una proprietà di tutti gli insiemi di oggetti e restituisce il numero di oggetti relativi presenti. Per esempio Workbooks.Count restituisce il numero di fogli excel aperti, Worksheets.Count il numero di pagine presenti nel foglio attivo.

8.       End è una proprietà di Range e rappresenta la fine della regione di celle in cui è presente la cella selezionata, in una delle 4 direzioni possibili. Equivale alla pressione di Ctrl+Direzione. Per esempio, “Range(“A1″).End(xlDown).Select” seleziona l’ultima cella della regione che contiene A1 (es: una tabella). Le costanti sono naturalmente xlUp, xlDown, xlRight e xlLeft.

9.       Offset è una proprietà di Range che restituisce una posizione vicina alla cella selezionata, indicando direzione e coordinate relative (riga e colonna). Per esempio, Range(“A1”).Offset(1, 0).Select seleziona la cella A2.

10.   Cells è una proprietà di Worksheets, Range, Sheet e altri che non fa altro che restituire una cella. La sintassi è Cells(riga, colonna), per esempio Cells(3, 5) corrisponde alla cella E3. Può sostituire l’oggetto range nell’indicare le singole celle.

11.    UsedRange è una proprietà di Worksheet che identifica l’area di lavoro della pagina, cioè l’intervallo in cui abbiamo agito, inserito, manipolato, modificato, eccetera. Per esempio, ActiveSheet.UsedRange.Select seleziona l’area di lavoro.

12.    DisplayAlerts una proprietà di Application che ci permette di sospendere gli allarmi automatici del sistema. Utile per evitare conferme inutili che bloccherebbero l’esecuzione delle macro facendoci perdere tempo. È importante fare attenzione a come la usiamo e dobbiamo ricordarci sempre di ripristinare gli allarmi di Excel prima di chiudere la macro (Application.DisplayAlerts = False). Gli allarmi non sono lì per caso.

Chiudiamo qui questo assaggio delle proprietà.

So cosa stai pensando: ma devo impararmi a memoria tutta sta roba? Ma sono centinaia di termini associati con decine di oggetti e dove capire come funzionano uno alla volta?

La risposta è: sì. Punto. Il vba è un linguaggio e come una lingua devi studiare e impegnarti finché non la impari.

Passiamo ai metodi.

Quali sono i metodi più usati?

I metodi sono le azioni, le attività che possiamo eseguire con e sugli oggetti, sono molti, ma non come le proprietà, alcuni sono specifici di certi oggetti, altri sono generali e condivisi da molti oggetti (es: select, copy, ecc). Questi sono alcuni tra i metodi più usati:

1.       Select è uno dei metodi più diffusi tra gli oggetti (es: worksheet, cells, rows, columns, range, ecc). Il metodo porta in primo piano la pagina, sposta il cursore sulla selezione, evidenzia l’oggetto e lo rende richiamabile con Selection (es: Selection. ClearContents cancellerà il contenuto delle celle selezionate). L’oggetto più associato a questo metodo è Range, l’intervallo di celle (e i suoi “parenti”, cells, rows, columns). Per esempio, Range(“A:A”).Select seleziona l’intera colonna A.

2.       Activate è il metodo che porta in primo piano gli oggetti, usato di solito per Workbook e Worksheet, la sintassi è semplicemente “Oggetto.Activate”. Per esempio, Worksheets(“Report”).Activate porta in primo piano la pagina Report. È opportuno sottolineare che Select e Activate sono due azioni diverse. Select serve a selezionare uno o più oggetti per poter poi agire su di essi, mentre Activate porta in primo piano l’oggetto indicato.

3.       Add è il metodo usato per creare nuovi oggetti come fogli di lavoro e pagine, es: Worksheets.Add. Il metodo ha come sintassi: Add(Before, After, Count, Type), tutti gli argomenti sono opzionali, cioè non necessari. Before specifica la pagina prima della quale si desidera aggiungere il nuovo foglio, After dopo quale pagina si desidera aggiungere il nuovo foglio, Count specifica il numero di fogli che si vuole aggiungere, uno di default, Type specifica il tipo di foglio da aggiungere (XlSheetType, xlWorksheet, xlChart, xlExcel4MacroSheet, xlExcel4IntlMacroSheet o xlDialogSheet). Per esempio, ActiveSheets.Add Count:=3 aggiunge 3 nuove pagine.

4.       Copy è un metodo che copia un oggetto per crearne uno nuovo, o copia le sue proprietà per trasferirle a un altro oggetto analogo. Per esempio, con Worksheets(“Dati”).Copy After:=Worksheets(“Report”) crei una copia della pagina Dati posizionata a destra di Report. Ma è un metodo usato spesso per copiare le celle, valori e formati. Per esempio, “Worksheets(1).Range(“C1:D3”).Copy Destination:= Worksheets(“MedieStoriche”).Range(A11)Range(A11)” copia le celle indicate in una pagina diversa. Ti faccio notare che con un solo comando si può copiare e incollare. Se non si indica la destinazione, allora l’oggetto verrà copiano nella clipboard e avrà bisogno di un comando Paste per essere portato a destinazione.

5.       Paste quindi serve per incollare quello che abbiamo salvato nella clipboard. Per esempio Worksheets(“MedieStoriche”).Paste Destination:= Activesheet.Range(A11) incolla quello che è stato copiato nell’esempio precedente.

6.       Delete è il metodo per eliminare o rimuovere gli oggetti, per esempio Worksheets(1).Delete rimuoverà la pagina 1. Prima di farlo excel chiederà conferma con un’apposita finestra di dialogo. Questa può essere evitata agendo sulla proprietà DisplayAlerts di Application, per bloccare la funzionalità di Excel (Application.DisplayAlerts = False).

7.       Close è un metodo di Workbook, preve alcuni parametri tra cui il più usato è Savechanges per gestire il salvataggio. Per esempio, ActiveWorkbook.Close SaveChanges:=True chiude il foglio salvando in automatico senza chiederlo.

8.       Save è uno dei metodi classici dei fogli di lavoro e dell’insieme relativo con cui si salva il foglio di lavoro. Per esempio: Workbooks.Save salva tutti i fogli di lavoro aperti. Simile ma più complesso è il metodo SaveAs che salva il foglio come un file diverso e quindi richiede una serie di parametri, es: il nome del nuovo file.

9.       PrintOut metodo di tutti gli oggetti stampabili, stampa l’oggetto specificato con le impostazioni di default o predefinite. Per esempio, ActiveSheet.PrintOut stampa la pagina attiva, oppure, Charts.PrintOut stampa tutti i grafici.

10.   ClearContents è un metodo che cancella il contenuto delle celle selezionate, qualunque sia, formule e valori. Per esempio Worksheets(1).Range(“A:A”).ClearContents, cancella la colonna A intera.

11.    Autofill è un metodo dell’oggetto Range che riempie in automatico le celle dell’intervallo dichiarato a partire dal contenuto di alcune celle. È l’analogo dello strumento di excel che possiamo richiamare con un doppio clic sul quadratino in basso a destra della selezione. Si può usare anche con il vba, per esempio: Worksheets(1).Range(“C1:C2”).AutoFill Destination:= Worksheets(1).Range(“C1:C10”), riempie le 10 celle della destinazione a partire dai valori delle prime 2 celle.

Anche i metodi devono essere studiati con attenzione, anche maggiore delle proprietà in quanto i metodi manipolano gli oggetti o le loro caratteristiche. Quindi devi metterci impegno e all’inizio pazienza per studiare un poco alla volta i metodi che ti servono. Usa l’help del vbe, usa i motori di ricerca, le guide, gli strumenti del Vbe e un poco alla volta ti accorgerai di scrivere più velocemente, di non doverti impegnare a ricordare o cercare le parole. È solo questione di tempo.

Ora parliamo brevemente delle funzioni del vba.

Le funzioni del vba

Il linguaggio vba non è composto solo da oggetti, proprietà e metodi, ma anche da funzioni con cui si può controllare l’esecuzione del codice (es: cicli per ripetere il codice), con cui si possono richiamare strumenti appositi (es: le finestre di dialogo con cui interagire con l’operatore) e altre funzioni specifiche.

Le funzioni hanno un nome e regole precise che devono essere rispettate, per esempio una funzione che ripete il codice, come il ciclo For/Next, richiedere una variabile come contatore e l’indicazione di un inizio e una fine. Una funzione di salto, come Goto, richiede un punto di arrivo con un nome univoco e una dichiarazione di salto che la punta. Una funzione di test come If/Then, che verifica condizioni precise per decidere se eseguire una parte del codice piuttosto che un’altra, richiede sempre il controllo di una condizione e operatori per testarla. E via dicendo.

Queste funzioni sono strumenti fondamentali per scrivere il codice, devono essere imparate a memoria e bene. Le funzioni più importanti sono decine, eccone alcuni esempi:

1.       For/Next

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. 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. La maggior parte delle funzioni di ciclo (es: Do/Loop) invece determina l’uscita dal ciclo attraverso la verifica di una o più condizioni logiche, generando quindi un numero di ripetizioni varabile e non definito.

La sintassi è questa:

For VARIABILE_CONTATORE = inizio To fine [Step passo]

…Istruzioni da eseguire

Next VARIABILE_CONTATORE

La variabile contatore

La variabile contatore è numerica e di solito di tipo Integer o Long, di cui viene definito un valore iniziale e un valore finale. Questo può essere fatto con un numero, una formula, una variabile, eccetera. Di solito le variabili usate come contatori sono nominate con una singola lettera (es: I, J, K, ecc) e di default VBA incrementa il 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 a fine, la funzione esegue nuovamente il ciclo. Se invece contatore è maggiore di fine, il ciclo finisce e continua l’esecuzione della macro dopo Next.

È possibile specificare un valore diverso per l’incremento del contatore aggiungendo Step e il passo, l’incremento del contatore. Per esempio:

For I = 10 To 1 Step -1

… istruzioni

Next I

esegui il ciclo, ma con un conteggio inverso. Utile per esempio quando è necessario rimuovere righe da una tabella ed è più opportuno farlo in senso inverso.

I cicli sono strumenti potenti che permettono la ripetizione delle istruzioni ed è necessario conoscerli bene per averne il massimo controllo ed evitare errori anche gravi sull’esecuzione del codice.

2.       If/Then

Questa funzione serve a controllare l’esecuzione del 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 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ò estendersi alla classica sintassi multiriga con una singola condizione che 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

Strutture condizionali complesse

Questa funzione può arrivare fino a una complessa sequenza di condizioni da verificare, come nella seguente sintassi:

If CONDIZIONE=Vera Then

… Istruzioni

ElseIf CONDIZIONE2=Vera Then

… Istruzioni

ElseIf CONDIZIONE3=Vera Then

… Istruzioni

Else

… istruzioni alternative

End If

Strutture di questo genere sono usate per gestire decisioni relativamente complesse senza l’intervento dell’operatore.

Bisogna sottolineare che la funzione ha la possibilità di verificare non una singola condizione, ma più condizioni contemporaneamente usando gli operatori booleani (And, Or, Not). Per esempio, “If Età>13 And Età <19 Then Adolescente=True”.

Queste funzioni “condizionali” sono una componente fondamentale 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.

3.       Msgbox

La funzione MsgBox() richiama una finestra di dialogo con cui è possibile comunicare informazioni all’operatore e ricevere conferme, o raccogliere le sue decisioni su quesiti a risposta chiusa.

Viene usata in moltissime situazioni nelle diverse fasi di esecuzione della macro:

1.       all’inizio per chiederli conferma dell’esecuzione,

2.       dopo i controlli preliminari per comunicargli imprevisti o la mancanza delle condizioni per l’esecuzione del codice,

3.       nella gestione degli errori per comunicare il problema, ma anche per chiedergli se e come proseguire il codice,

4.       prima dell’esecuzione di operazioni irreversibili (rimozioni, cancellazioni, ecc),

5.       alla fine della macro per comunicargli l’avvenuta esecuzione,

6.       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.

Se vogliamo una finestra di dialogo completamente configurabile e controllabile dobbiamo creare una maschera apposita, attività che richiede molte competenze e tempo, mentre Msgbox è uno strumento pronto e facilmente configurabile.

I parametri della finestra dei messaggi

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.

Le finestre di dialogo, sia quella dei messaggi (Msgbox) che degli input (Inputbox), sono strumenti potenti e flessibili con cui possiamo ottenere risultati notevoli e che quindi vale la pena conoscere alla perfezione per sfruttarne gli innumerevoli vantaggi. L’argomento è ampio e complesso, quindi approfondiremo l’argomento in altri post dedicati.

 

Prima della scrittura il progetto

Vuoi realizzare una macro per Excel? Bene, qual è la cosa che devi fare prima di iniziare a scrivere il codice? Bravo. Definire l’obiettivo da realizzare. E poi? Il secondo passo? Valutare costi–benefici e la fattibilità. Certo, è consigliabile. E una volta confermata la bontà della scelta di fare la macro? Progettare la macro. Perfetto.

Realizzare una macro Excel, non due righe di codice, intendo una macro vera, richiede una fase di progetto in cui definiamo: cosa vogliamo realizzare e come lo vogliamo realizzare.

Un progetto ha molteplici vantaggi: riduce i costi e gli sprechi (es: tempo), dà una traccia da seguire, rende più efficiente l’esecuzione, ci prepara agli imprevisti, spinge a ottimizzare e molto altro. Se sai prima cosa fare e dove andare, allora l’esecuzione sarà più efficace ed efficiente.

 

Come si progetta una macro?

Prendi qualcosa su cui scrivere (es: carta, word o excel), dieci minuti di tempo e rispondi a poche domande.

1.       Definisci l’obiettivo

Qual è l’obiettivo della macro che vuoi realizzare? Scrivilo. Scrivi l’obiettivo in modo preciso. Per esempio: “Voglio una macro che imposti nella pagina attiva una testata di 3 righe bloccate con un titolo, una descrizione, istruzioni per l’uso e una formattazione precisa”.

2.       Valuti costi e benefici

Prima di iniziare a lavorare sul progetto, fai una valutazione dei costi e dei benefici della macro. Confronta il vantaggio che ottiene dalla macro, con il costo per realizzarla. Serve a evitare di sprecare tempo in macro poco utili o troppo costose, ma soprattutto serve per confermare la tua decisione di realizzare la macro per Excel. Per farlo rispondi a queste domande:

·         Quale utilizzo penso di farne?

·         Quanto tempo la userò?

·         Quante volte la userò?

·         Quanto tempo mi permette di risparmiare?

·         Quanto tempo devo spendere per realizzarla?

·         Raggiunge il mio obiettivo? Soddisfa pienamente lo scopo?

·         Ci sono altri modi o strumenti per ottenere lo stesso risultato?

·         Sono più o meno efficaci, efficienti, onerosi?

Lo scopo è comprendere se la macro è effettivamente la migliore soluzione al problema, il miglior strumento per raggiungere il tuo obiettivo, quindi se vale la pena spenderci tempo e risorse.

Per esempio, forse è poco utile impostare una “testata” nelle pagine Excel, forse è più efficace copia–incollarla da una pagina predisposta, o usare un foglio di lavoro pronto, eccetera.

La decisione è tua. Se confermi la scelta di realizzare la macro è tempo di progettarla.

3.       Definisci con precisione il risultato della macro

Prendi l’obiettivo e riscrivilo descrivendo ogni minimo particolare del risultato. Per esempio: “Voglio una macro che imposti nella pagina attiva: colore fondo bianco, una testata di 3 righe bloccate, con nella cella A1 “Titolo pagina”, grandezza font 14, font Arial, eccetera”.

4.       Definisci cosa deve fare la macro per ottenere il risultato descritto

Ora definisci cosa deve essere fatto per ottenere ogni aspetto del risultato atteso, quindi ogni singola attività che deve essere realizzata. Fai un elenco delle attività precise, definendo le loro relazioni e descrivendo: decisioni necessarie, input, interazioni, risultati intermedi.

Torniamo all’esempio iniziale: “Voglio una macro che imposti nella pagina attiva: colore fondo bianco su tutte le celle, blocchi lo scorrimento alla quarta riga, scriva nella cella A1 “Titolo pagina”, imposti nella cella A1 grandezza font 14, font Arial, eccetera”.

5.       Definisci come realizzare ogni singola attività

A fianco di ogni attività elementare, scrivi come realizzarla. Scrivi qualche parola chiave, o anche qualche riga di spiegazione per indicare come realizzerai il codice per quella attività. Per esempio, “cells.interior.tintandshade=0” o in modo più discorsivo “imposta il colore di fondo bianco a tutte le celle”. Oppure “seleziona riga 4 + blocca riquadri” per bloccare le prime 3 righe in alto, e così via.

Nella maggior parte dei casi il “come” è implicito nel “cosa”, ma via via che le attività diventeranno complesse, la risposta non sarà così immediata. In tal caso ti invito a indicare per ogni attività:

·         oggetti e strumenti coinvolti (fogli, tabelle, grafici, ecc),

·         input necessari (dati, valori, kpi, ecc),

·         controlli da eseguire (sulle condizioni, sui dati, sui risultati, ecc),

·         decisioni da gestire e condizioni relative,

·         attività alternative,

·         output attesi e inattesi/prevedibili.

Questo per ottenere una mappa che descrive con precisione il tuo progetto. Naturalmente tutto questo ha poco significato per obiettivi minimi e semplici, per esempio stampare una pagina, salvare il foglio e uscire, ma in caso di macro Excel complesse, diventa uno strumento utile che si ripaga tutto in fase di realizzazione.

Questa fase serve ai principianti che ancora non hanno il controllo degli strumenti e non sanno ancora con chiarezza come realizzare le singole attività. Li costringe a cercare il modo per realizzarlo, a studiare il linguaggio. Per gli esperti questa fase è di solito opzionale, a meno di progetti complessi o innovativi.

Hai scritto tutto?

Bravo, hai il tuo progetto. Ora devi realizzarlo.

Cosa dici? Come so cosa fare e come farlo?

Come ho appena detto, se sei agli inizi questo metodo ti costringe a confrontarti con quello che non sai, quindi se non conosci abbastanza Excel, il vba o gli strumenti relativi, dovrai studiare per completare un pezzo alla volta la mappa.

Quando realizzerai un progetto senza chiederti “Come so” allora non sarai più un principiante.

 

La struttura delle macro Excel

All’inizio abbiamo accennato alla modalità della scrittura del codice e alla struttura delle macro. Abbiamo visto che le macro hanno un inizio, un corpo e una fine, un preciso obiettivo, partono da condizioni iniziali 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 livello di codice sono definibili molteplici attività di diversa natura, la maggior parte delle quali possono essere classificabili in:

1.       dichiarazioni delle variabili, per gestire informazioni che devono essere utilizzate più volte, elaborate, ecc;

2.       controlli di varia natura e scopo;

3.       interazioni con l’operatore per comunicare informazioni o ricevere input (conferme, informazioni, o decisioni),

4.       attività operative dirette o condizionate,

5.       decisioni basate sulle condizioni di lavoro che gestiscono il codice e le attività,

6.       gestione degli eventuali errori imprevisti,

Questa semplificazione delle attività realizzate dal codice è fatta per chi si avvicina alla scrittura del codice e cerca indicazioni da seguire.

Approfondiamo ruolo e scopo di ogni macroattività:

1.Dichiarazioni

Una dichiarazione consiste nella creazione di un contenitore teorico che riserva una porzione della memoria per conservare e quindi poter gestire un’informazione che il codice della macro deve ricordare, poterla usare più volte, elaborare, comunicare, eccetera.

Nella pratica consiste nella dichiarazione del nome preciso di variabili e costanti e del loro tipo, cioè della porzione di memoria da assegnare loro e del modo con cui devono essere gestite.

È necessario dichiarare?

No, Excel si occupa in automatico di qualsiasi variabile che inseriamo nel codice, ma lo farà in modo dispendioso, con un impegno notevole di memoria, poco efficiente, generando variabili di vita breve.

In quale posizione della macro devo fare le dichiarazioni?

Di solito vengono poste all’inizio della macro, ma in realtà le dichiarazioni puoi posizionarle:

1.       nel codice della macro, prima delle righe che la utilizzano naturalmente,

2.       all’inizio della macro prima del codice, posizione naturale per le dichiarazioni di variabili locali,

3.       all’inizio del modulo, fuori da qualunque macro, per essere disponibili a tutte le macro del modulo, quindi con dichiarazione privata (private),

4.       sempre all’inizio del modulo, fuori da qualunque macro, ma dichiarate pubbliche (public), cioè visibili a tutte le macro di tutti i moduli.

2.Controlli

Sono tutte le attività di verifica relative a valori, oggetti, input, proprietà, condizioni, risultati, eccetera.

Per esempio un controllo è la verifica dell’esistenza degli oggetti richiamati dal codice, o il controllo del valore delle variabili principali durante l’esecuzione, o la verifica delig input forniti dall’operatore, ecc.

A livello di codice un controllo richiede blocchi di istruzioni caratterizzati dall’impiego di una o più funzioni di test delle condizioni (es: If/Then) e, dov’è necessario fare controlli su gruppi di valori, oggetti, eccetera, richiede l’uso di cicli (loop). Può essere accompagnato da interazioni con l’operatore e da cambi del flusso di esecuzione della macro Excel (uscite e salti).

È necessario fare controlli?

In teoria non è necessario, soprattutto se si lavora in un ambiente chiuso e se si ha la certezza delle condizioni. Se usiamo la macro per un’attività specifica di un singolo foglio di lavoro possiamo fare a meno di controlli.

Se invece realizziamo macro che vogliamo installare in molti fogli diversi, che vogliamo funzionino in situazioni diverse e sappiano gestire gli imprevisti, allora i controlli sono uno degli strumenti fondamentali per realizzarle.

I controlli sono uno degli ingredienti che rendono le macro per Excel intelligenti e flessibili. Per questo i controlli sono consigliati per macro significative e necessari per gestire situazioni, macro e fogli di lavoro complessi.

In quale posizione della macro vengono posti i controlli?

Dipende dal ruolo dei controlli, per esempio:

1.       i controlli delle condizioni iniziali sono posti all’inizio, di solito dopo le dichiarazioni,

2.       il controllo degli input dell’operatore è inserito di seguito all’interazione con l’operatore, per gestire il flusso del codice a seconda delle decisioni comunicate dall’operatore, o per controllare il valore inserito dall’operatore (es: se coerente, già presente, entro i valori previsti, ecc);

3.       quelli delle condizioni di lavoro sono inseriti prima di porzioni specifiche di codice operative, per verificare lo stato di variabili, la presenza di imprevisti, o altro;

4.       mentre il controllo dei risultati è posto dopo le attività operative, per controllare se il risultato è quello atteso, o eseguire codice aggiuntivo per comunicare all’operatore anomalie e imprevisti.

3.Interazione con l’operatore

L’interazione con l’operatore serve di solito per:

1.       richiedere informazioni precise, per esempio un valore da elaborare, l’intervallo di tempo che si vuole visualizzare, ecc; se numerose o ripetute queste interazioni possono essere gestite in mo diverso, per esempio delegate a un sistema di “configurazione” che conserva le informazioni su una pagina del foglio di lavoro, o su un foglio o file esterno;

2.       gestire imprevisti, di solito il cambiamento delle condizioni di lavoro attese, per esempio l’assenza della tabella su cui la macro deve lavorare, o la presenza di un valore imprevisto; sono di solito interazioni attivate da controlli che hanno dato risultati inattesi;

3.       gestire decisioni, complesse, con troppi parametri, che richiederebbero troppo codice, o parametri non oggettivi e misurabili, come per esempio la valutazione della significatività di un grafico, o la definizione dei livelli di sicurezza degli stock a magazzino;

4.       richiedere conferma su determinate attività, di solito di tipo “irreversibile”, per esempio se si sta per sostituire l’intera base dati, o se si vuole cancellare un’analisi precedente; servono a prevenire difficoltà causate da un’esecuzione accidentale o inconsapevole.

L’interazione con l’utente richiede principalmente l’utilizzo di finestre di dialogo (Msgbox e Inputbox) che permettono di scegliere tra risposte predefinite o di inserire valori, di solito accompagnate da test (If/Then else) per controllare le risposte e gestire le scelte comunicate e quindi il flusso dell’esecuzione del codice.

È necessario interagire con l’operatore?

Un tempo pensavo che gli automatismi dovessero fare il più possibile da soli, meglio tutto, senza far spendere tempo all’operatore. In realtà la necessità di interagire con l’operatore dipende dalla macro, dall’obiettivo, dagli strumenti impiegati e altro ancora. Il consiglio è interagire quando serve.

Per esempio è necessario per gestire errori e imprevisti, ma non è detto sia necessario per avere input, per esempio la macro potrebbe ottenerle tramite pagine o file di configurazione. Oggi ne consiglio l’uso per comunicare all’utente l’avvenuta esecuzione della macro, dove non ci sono segni evidenti del risultato, o lo svolgimento dell’esecuzione dove il tempo di lavoro della macro è lungo.

In quale posizione della macro devo fare interazioni?

Dipende dallo scopo dell’interazione, per esempio:

1.       all’inizio per chiedere input o conferme,

2.       prima dell’esecuzione di attività operative critiche per avere conferma,

3.       durante le attività operative per gestire gli imprevisti,

4.       alla fine per comunicare l’avvenuta esecuzione,

5.       alla fine per gestire gli errori,

e in molte altri occasioni.

4.Attività operative

È il codice che agisce, elabora, chiama altre macro/funzioni, esegue metodi e modifica proprietà. Tutto il resto gira attorno a questo codice, è il cuore della macro, sono le istruzioni che servono a realizzare le attività che la macro deve svolgere: dalla creazione di oggetti (es: tabelle, pagine, ecc), alla modifica di proprietà, attributi, valori e caratteristiche degli oggetti, dall’elaborazione diretta di dati, alla revisione delle pagine, alla gestione dei file, e così via.

Ho raggruppato forzatamente attività tanto diverse e disparate, che a loro volta hanno una classificazione ampia e articolata, solo per semplificare il discorso della struttura del codice a beneficio di chi è agli inizi. Approfondiremo questo argomento in altri post.

Il codice relativo varia in modo notevole a seconda del tipo di attività, è comunque basato su modifiche delle proprietà degli oggetti e soprattutto metodi applicati agli oggetti, accompagnati dalle funzioni necessarie. Per esempio se l’attività riguarda molti oggetti o dati, saranno presenti cicli (loop), mentre se richiede valutazioni interne allora presenterà test per il controllo per gestire il flusso.

Consiglio di dividere il codice per la realizzazione delle attività operative in moduli definiti da commenti e spazi, che si occupano delle singole attività. Per esempio, un blocco di codice per la creazione di una nuova pagina, uno per importare i dati, uno per la creazione della tabella excel, eccetera.

Nel caso il codice diventi lungo e complesso è opportuno considerare di dividere la macro in parti per semplificarne la stesura, gestione e comprensione.

In quale posizione metto il codice operativo?

Al centro della macro, dopo dichiarazioni e controlli iniziali, prima della chiusura e della gestione degli errori.

5. Valutazioni e decisioni

Inseriamo codice per gestire decisioni quando decidiamo di affidare alla macro una scelta basata su condizioni definite e misurabili. Come il valore del fatturato medio del mese precente, o il numero di resi per l’anno in corso. Per esempio possiamo modificare il report, aggiungendo una tabella e un grafico, a seconda del valore dei resi.

Le decisioni non sono i controlli, anche se i controlli possono richiedere decisioni su come gestire i risultati, e le decisioni spesso richiedono controlli per verificare le condizioni.

Il codice per le decisioni si basa principalmente su test delle condizioni (If/Then o Select Case), spesso accompagnati da cicli (es: For Each/Next, For/Next, ecc).

È necessario che la macro prenda decisioni?

È necessario se lo richiede l’obiettivo e il progetto della macro.

Se vuoi che la macro di Excel si assuma una parte delle decisioni che di solito gestisci tu, determinate dalla variabilità o dell’incertezza di condizioni, risultati e dati, allora sì.

In quale posizione della macro sono inserite le decisioni?

Talvolta prima del codice operativo per decidere sulla sua esecuzione, o su quale delle alternative predisposte eseguire, di solito all’interno del codice operativo per decidere su singole attività o sull’esecuzione del resto della macro.

6. Gestione degli errori

È quella porzione di codice che si occupa di gestire gli errori provocati dagli imprevisti, intercettandoli e permettendo l’esecuzione di codice aggiuntivo predisposto dallo sviluppatore. La gestione di solito si limita alla comunicazione dell’errore all’operatore con le indicazioni sul comportamento da tenere e a chi rivolgersi per ricevere assistenza. In realtà offre molte possibilità, per esempio quella di analizzare la causa dell’errore, eseguire codice dedicato per ottenere almeno una parte del risultato atteso, oppure riprendere l’esecuzione del codice da una posizione precisa.

Il codice per le decisioni si basa principalmente sulle funzioni predisposte per la gestione degli errori (es: On Error, Resume, Err.Number, Err.Description, ecc) e su finestre di dialogo (Msgbox). In caso di gestione più evoluta, il codice sarà caratterizzato in modo significativo da test delle condizioni (If/Then o Select Case).

È necessario che la macro gestisca gli errori?

No, anzi di solito è considerata una perdita di tempo, in quanto è codice che verrà eseguito solo in caso di imprevisti. Perché spendere tempo per qualcosa che serve raramente? Eppure senza questo codice la macro non sarà in grado di affrontare nessun errore, limitandosi a mostrare la classica finestra di sistema.

Di conseguenza la gestione degli errori è assente nelle macro piccole e operative. Invece nelle macro Excel di media grandezza e complessità, spesso ci si limita a ignorare l’errore e a far proseguire l’esecuzione, rischiando però di presentare risultati errati o provocare danni. I più esperti preparano un blocco di codice dedicato, sempre uguale che aggiungono in automatico nelle macro che possono averne bisogno.

In quale posizione della macro devo inserire la gestione degli errori?

La gestione degli errori è composta da:

1.       una dichiarazione all’inizio della macro che indica il modo di gestione e il punto del codice a cui riferirsi

2.       un punto a cui salta il codice alla comparsa dell’errore, seguito dal codice per la gestione, di solito posto alla fine della macro, dopo il codice vero e proprio.

Quindi quale struttura deve avere la tua macro?

Nel post precedente abbiamo visto che una macro ha una sua struttura determinata dalla logica, dalla natura del vba e di Excel. Scriviamo le dichiarazioni all’inizio della macro perché servono al codice che segue, scriviamo i blocchi di codice che chiedono input all’operatore prima di elaborarli, scriviamo il codice per controllare questi input, dopo l’interazione con l’operatore e prima dell’elaborazione. E via dicendo.

Essere consapevoli di queste relazioni tra gli elementi del codice, quindi della struttura che questo assume, ci dà una falsariga da seguire nella scrittura del codice, molto utile per chi è agli inizi, comoda per gli altri.

Questo significa che devi imporre una struttura al codice?

No, se il codice viene scritto secondo le regole e nel modo migliore, assumerà spontaneamente una struttura propria a seconda delle attività che svolge e del vba che abbiamo usato. Se è una macro piccola, avrà una struttura minima, tipo: dichiarazioni + attività operative, oppure dichiarazioni + controllo condizioni iniziali + attività operative.

D’altra parte se la macro excel è grande e complessa avrà una struttura articolata e distribuita, scomposta su macro diverse richiamate da quella principale. Per esempio: dichiarazioni a inizio modulo + dichiarazioni in macro + controlli iniziali + interazione + attività operative + controlli + attività operative + decisioni e via dicendo.

 

Le strutture di riferimento

La maggior parte delle macro presenta strutture simili a questa:

1.       Dichiarazioni

2.       Controlli (Opzionale)

3.       Interazione con l’operatore (Opzionale)

4.       Attività operative

5.       precedute o accompagnate da Decisioni (Opzionale)

6.       Chiusura e ripristino (Opzionale)

7.       Gestione degli errori (Opzionale)

Di solito le macro non troppo piccole o troppo grandi hanno strutture che sono semplificazioni, riduzioni, o elaborazioni di questa, che ripeto vuol essere solo una traccia.

In realtà l’esperienza ti mostrerà come le strutture possono essere molto più definite e articolate e che le sole attività operative possono assumere strutture molteplici anche complesse e che sono loro a determinare la struttura complessiva delle macro.

Finché non diventerai un esperto, quello che ti invito a fare è usare le strutture che il codice assume come falsariga da seguire nello sviluppo, perché ti semplificherà la vita e renderà più efficiente il tuo lavoro. Una falsariga che naturalmente va adattata alle situazioni e ai diversi progetti. E in realtà è una cosa che faresti comunque, che tutti facciamo, anche senza rendersene conto.

Prima che me lo chiedi parliamo della chiusura.

 

Chiusura della macro

È un’attività accessoria spesso inesistente che consiste in qualche riga di codice dedicata soprattutto alla gestione e al ripristino delle impostazioni di excel e di alcuni aspetti del codice. Per esempio:

1.       è necessario inserire un comando per chiudere la macro (es: “Exit sub”) in presenza di codice per la gestione degli errori posizionato in fondo alla macro, altrimenti verrebbe eseguito comunque;

2.       nel caso tu abbia assegnato una variabile a un oggetto (Set), è buona pratica rilasciare le risorse con una dichiarazione “Set VARIABILE = Nothing”; non è necessario, è consigliato, ma se sei un principiante passa oltre, ritornaci più avanti;

3.       questo è preceduto dall’eventuale messaggio all’utente di conferma dell’avvenuta esecuzione, nel caso di macro di lunga durata che non offrono risultati percepibili immediatamente a schermo (comparsa di oggetti, cambio di pagina, eccetera),

4.       questo può essere preceduto dall’eventuale ripristino dei messaggi di errore, dell’aggiornamento schermo ed eventi (Application.ScreenUpdating = True, Application.DisplayAlerts = True, Application.EnableEvents = True), se sono stati disabilitati all’inizio della macro con gli analoghi comandi (= False)

5.       preceduto eventualmente, per fogli di lavoro stabili e strutturati, dall’aggiornamento di parametri e indicatori sulle pagine (es: data di esecuzione), o di pagine o file di configurazione.

La lista potrebbe continuare, ma volevo mostrarti che ci sono attività secondarie legate alla gestione del codice, o del foglio, o della macro che possono richiedere attenzione e codice.

 

Come iniziare una macro vba

Invece come si inizia una macro?

Potrei proporti una lista di “attività secondarie” utili e migliorative da porre all’inizio della macro, diciamo prima del codice operativo, ma non credo sia importante adesso.

Se sei un principiante per te la macro inizia con le dichiarazioni delle variabili.

Solo due consigli:

1.       dopo il nome della macro, inserisci sempre un commento per spiegare lo scopo della macro e le note importanti, al massimo due righe;

2.       se devi lavorare sugli oggetti del foglio, passare da una pagina all’altra, spostare il cursore molte volte, questo può generare effetti visivi fastidiosi (flickering) che può essere opportuno nascondere, bloccando l’aggiornamento dello schermo con il comando “Application.ScreenUpdating = False”; lo metti prima del codice operativo, ma devi ricordardi di ripristinare l’aggiornamento prima della chiusura della macro con Application.ScreenUpdating = True.

Adesso vediamo una sintetica introduzione alle variabili.

 

Dichiarare le variabili

Come abbiamo già detto, le informazioni che servono alle procedure, per essere elaborate, per la gestione (es: contatori) e più in generale le informazioni che devono essere utilizzate più volte dalla macro, richiedono di essere memorizzate. Come? Inserendole in “contenitori” chiamati “variabili” che impegnano porzioni di memoria dedicata. Questo avviene con la dichiarazione delle variabili.

Il linguaggio Vba prevede che la dichiarazione avvenga prima del primo utilizzo del dato, ma può essere fatto in modi e momenti diversi a seconda dell’utilità della variabile.

Le variabili possono essere dichiarate all’interno del codice, perfino all’interno di strutture effimere come i cicli, ma più tradizionalmente vengono dichiarate all’inizio della macro e avranno validità limitata alla macro e durata di vita limitata all’esecuzione della stessa.

Oppure possono essere dichiarate all’inizio del modulo, fuori dalle macro, e avere validità per tutte le macro del modulo (Private NOME_VARIABILE As tipo_dati). Possono anche essere dichiarate pubbliche (Public NOME_VARIABILE As tipo_dati) e avere validità su tutte le procedure di tutti i moduli.

La dichiarazione delle variabili ha questa sintassi:

Dim NOME_VARIABILE As tipo_dati

Per esempio, “Dim I as Integer” è la tipica dichiarazione di un contatore, mentre “Dim DataFattura as Data” è la dichiarazione di una data, “Dim NomePagina as String” è la variabile che servirà a gestire il nome di una nuova pagina. La dichiarazione può essere multipla, per esempio “Dim I as Integer, DataFattura as Data, NomePagina as String”, cioè si possono dichiarare più variabili nella stessa riga di codice.

Per poter meglio gestire le variabili e la memoria che occupano, per migliorare l’efficienza della procedura e per minimizzare problemi di varia natura (es: sicurezza) è consigliato che le variabili vengano dichiarate indicando il tipo di dato che dovranno contenere.

Ci sono molti tipi di dati, i più usati sono:

1.       Integer, numero interi con segno compreso fra -32768 a 32767;

2.       Boolean, valore di tipo logico che può assumere il valore vero o falso (True o False);

3.       String, sequenza di caratteri (stringa);

4.       Date, variabile usata per memorizzare date e ore;

5.       Long, numeri interi compresi fra -2.147.483.648 e 2.147.483.647;

6.       Single: numeri reali a singola precisione (es: 1,47 o 376,45);

7.       Variant, tipo di variabile universale che può contenere dei dati di qualsiasi formato.

8.       Non sono gli unici tipi di variabili (es: variabili double, byte, oggetti, variabili matrice, ecc), ma rimandiamo il complesso argomento della dichiarazione e gestione delle variabili ad approfondimenti specifici.

È necessario dichiarare le variabili?

No, il motore di Excel riconoscerà le variabili e assegnerà la memoria necessaria per la loro gestione. Ogni volta che non viene indicato il tipo della variabile, viene assegnato in automatico un tipo Variant alla variabile. Ti sto dicendo che puoi scrivere il codice senza dichiarazioni e funzionerà lo stesso, sarà più lento, ma funzionerà.

Naturalmente è consigliato e opportuno dichiarare correttamente le variabili per i motivi espressi poco sopra (efficienza, meno problemi). È una cosa che dovrai imparare e bene se vuoi realizzare macro evolute.

 

Come scrivi la tua macro?

La macro che devi scrivere è composta da righe di codice, raggruppate in blocchi di codice che svolgono attività diverse e sono posizionati in una struttura definita con un ordine e una logica precisi.

Ma come scrivi le righe di codice?

Prendi il progetto che hai realizzato con la mappa del cosa e del come, con l’obiettivo e la lista delle attività e di come realizzarle, con l’indicazione di oggetti e strumenti, di input, controlli, decisioni, alternative e output.

Rileggilo tutto dall’inizio alla fine. Poi prendi la struttura generica di riferimento che ho suggerito qui sopra e adattala alle attività della mappa. Togli il di più o aggiungi quello che manca, poi scrivila nella macro usando i commenti.

Ora scrivi le righe di codice che eseguono la prima attività della mappa, aggiungi un’attività alla volta, dall’alto verso il basso. Devi solo tradurre la mappa nel codice. All’inizio ignora il controllo degli errori, le dichiarazioni e le attività secondarie, concentrati sulle attività operative. Lavora sugli oggetti che conosci, studiali bene, impara a manipolare le proprietà e a lanciare i metodi. Usa solo le funzioni che servono finché non le conosci come le tue tasche.

Cerca di ottenere macro funzionanti così. Poi aggiungi il resto un poco alla volta. Quando hai bisogno di un input aggiungi un’interazione con l’operatore, quando hai bisogno di un controllo, aggiungi un test e definisci le condizioni, quando hai bisogno di una variabile la all’inizio della routine, e così via.

Un poco alla volta ti ricorderai il vocabolario che serve e la sintassi che conta. Un poco alla volta saprai in anticipo le dichiarazioni che devi scrivere, un poco alla volta scoprirai che ti diverti a farlo. Quasi come quando hai imparato la tua prima lingua abbastanza per parlare con i madrelingua. Un’emozione che lascia il segno.

Poi le cose diventeranno sempre più semplici e veloci, e i tuoi fogli excel faranno corse sorprendenti.

Naturalmente non devi farti prendere la mano, non esagerare e non perdere il controllo del flusso.

 

La gestione del flusso

Avrai notato che fino ad ora ho ripetutamente parlato di esecuzione del codice, di flusso, di controllo dell’esecuzione del codice, di comandi per modificare l’esecuzione del codice, eccetera, per esempio parlando della funzione If/Then che permette di decidere se eseguire una porzione di codice o quale codice eseguire tra più alternative.

Penso sia opportuno approfondire l’argomento.

Come viene eseguito il codice?

Il codice viene eseguito dal motore di Excel riga per riga dall’inizio alla fine. Punto. Però per la natura stessa del linguaggio e per dare la possibilità di realizzare procedure efficaci, flessibili e intelligenti, il vba prevede e offre diversi strumenti che agiscono sull’esecuzione del codice. Questo per motivi naturali, dal bisogno di interrompere il codice, a quello di poter ripetere porzioni di codice, dalla possibilità di saltare in un’altra posizione al bisogno di eseguire il codice solo a certe condizioni.

 

I comandi per la gestione del flusso

Vediamo gli strumenti più importanti e come intervengono sul codice:

1.       Funzioni di uscita/fine, sono strumenti che fermano l’esecuzione del codice in modo parziale (es: Exit For blocca il ciclo For) o definitivo (Es: End). Sono necessarie per evitare che il codice venga eseguito comunque, inutilmente o peggio, con conseguenze non volute. Le funzioni più note sono:

·         Exit, che appunto fa uscire dalla routine (Exit Sub) o dalle strutture di codice come cicli (es: Exit For, Exit Do, ecc);

·         End, termina l’esecuzione

2.       Funzioni di loop, cioè che ripetono il codice, sono i cicli che abbiamo già visto e servono per evitare di scrivere ripetutamente lo stesso codice uguale. Le funzioni di loop più note sono For/Next e Do/Loop, già descritte in precedenza

3.       Funzioni decisionali, sono quelle funzioni che a seconda di una o più condizioni limitano l’esecuzione di porzioni di codice, o eseguono una porzione di codice piuttosto che una alternativa. Le funzioni più note sono:

·         If/Then che abbiamo già visto in precedenza,

·         Select Case, una funzione che esegue una tra diversi gruppi di codice preimpostati a seconda del valore di un’espressione.

4.       Funzioni di salto, che spostano l’esecuzione a un punto preciso della procedura, per il Vba:

·         Goto che salta in modo diretto e incondizionato a un marcatore preimpostato,

·         Resume, che permette di riprendere l’esecuzione dopo il codice di gestione degli errori.

È importante conoscere queste funzioni e usarle quando servono, dove servono nel modo migliore.

Questo significa anche non generare flussi di esecuzione inutilmente complessi e contorti, per esempio usando inutilmente i salti (Goto), o annidando inutilmente troppo il codice.

L’annidamento del codice è l’inserimento di codice in cicli e strutture condizionali.

 

Scrivere codice leggibile

È utile spendere due parole sull’aspetto della scrittura del codice Vba perché la qualità del risultato e il tempo speso per realizzarlo dipende anche dalla leggibilità del codice. Quando scrivi il codice, come quando scrivi qualunque altra cosa, dalla poesia alla lista della spesa, devi rendere la scrittura leggibile, anzi il più leggibile possibile a chi dovrà leggerlo.

Per lo sviluppo del codice questo significa, per esempio:

1.       evidenziare i comandi rilevanti del linguaggio (se ne occupa il vbe),

2.       rendere identificabili i blocchi di codice, regolando gli spazi tra i “moduli” di codice e i rapporti tra pieno e vuoto nel testo,

3.       rendere distinguibili i “livelli” del codice, attraverso indentazione delle righe di codice,

4.       distinguere la parte eseguibile da quella non, evidenziandola con colore diverso,

5.       mantenere il testo nella schermata, senza costringere il lettore a muoverla destra–sinistra,

e altro ancora.

Affinché il codice delle macro sia leggibile e facilmente gestibile è quindi utile e consigliato:

1.       Tornare a capo con le righe troppo lunghe

Durante la scrittura, la riga del codice potrebbe diventare molto lunga e uscire dalla schermata, costringendo a scorrere verso destra per leggerlo. In questi casi è consuetudine che il codice venga riportato su più righe spezzandolo e aggiungendo a fondo riga il marcatore di interruzione di riga: uno spazio seguito da “_” (underscore). In questo modo la riga di codice verrà considerata dal vbe come un’unica riga.

2.       Indentare il codice

Le macro possono essere composte anche da centinaia di righe di codice, ma è sempre buona pratica formattare il codice per rendere più facile la lettura e la comprensione del codice. Come?

Semplicemente applicando alle righe di codice rientri tali da evidenziarne la struttura, porre su “livelli” diversi il codice inserito dentro strutture di controllo, come cicli (es: For Next) e strutture condizionali (es: If Then Else) allo scopo di esplicitare l’annidamento e le relazioni. Per indentare il codice è sufficiente premere Tab a inizio riga per spostare il cursore e il codice di quattro spazi. Premendo Maiusc+Tab si rimuoverà l’effetto del Tab.

3.       Commentare il codice

Durante la scrittura di codice è consigliabile sempre inserire note e commenti per spiegare lo scopo del codice o altro. Per farlo è sufficiente aggiungere a inizio riga il marcatore dei commenti, un apostrofo (’). Le righe cosi marcate cambieranno colore diventando verdi e verranno ignorate durante l’esecuzione del codice. Per questo motivo abitualmente l’apostrofo è usato anche per sospendere quelle righe di codice che non si vuole eseguire, o cancellare.

Ma l’uso principale dei commenti è quello di conservare e comunicare informazioni sul codice stesso, soprattutto sul suo scopo e so cosa fa. Se sei alle prime armi ti invito a prendere l’abitudine a di scrivere con cura le tue macro per poter ricordare il tuo lavoro quando le riprenderai in mano.

Adesso è tempo di parlare di errori.

 

Eliminare gli errori con il debugging

Siamo esseri umani e facciamo errori, nulla di nuovo, ma quando sviluppiamo è importante che il codice sia esente da errori per evitare danni e perdite ai dati o ai nostri fogli di lavoro che possono portare a sprechi di tempo anche significativi.

Per eliminare gli errori dalle nostre macro, il vbe ci mette a disposizione una serie di strumenti che ci aiutano durante la scrittura, come lo strumento di controllo della sintassi che di fatto elimina gli errori di sintassi. D’altra parte ci sono molti tipi di errore (logici, runtime, ecc) e soprattutto agli inizi è piuttosto facile inserire errori nel codice.

Come facciamo a rimuovere gli errori?

Con il debugging, cioè eseguendo il codice e controllandone i risultati. Una volta terminata la scrittura dobbiamo testare la macro usando gli strumenti specifici che l’ambiente di sviluppo ci mette a disposizione. I principali sono la possibilità di mettere in pausa l’esecuzione e quindi la possibilità di eseguire il codice riga per riga.

Poi ci sono gli strumenti con cui possiamo controllare gli effetti del codice sul codice stesso (es: il valore che assumono le variabili) senza dimenticare che dobbiamo sempre controllare gli effetti del codice sul foglio di lavoro.

Il debugging è un mondo, un lavoro e per alcuni un’arte, ma parleremo del debug del codice in post dedicati. Penso sia importante sottolineare che spesso il debug non elimina tutti gli errori, in particolare non rimuove quelli generati da situazioni impreviste.

Cioè il codice può essere esente da errori formali, ma può contenerne dei potenziali se si presentano situazioni impreviste che influenzano l’esecuzione del codice. Per esempio, se i dati elaborati contengono errori, o se assumono valori anomali (es: troppo grandi o nulli), oppure se cambiamo la struttura del foglio o degli oggetti con cui il codice deve interagire, se cambiano le caratteristiche di Excel, se sono attivi automatismi che possono interferire con l’esecuzione della macro (es: macro associate ad eventi), eccetera.

Le possibilità sono innumerevoli e per questo è difficile che un codice sia del tutto esente da errori potenziali generati da imprevisti. Possiamo minimizzarli progettando il codice con attenzione, quindi lavorando nella fase preliminare di progetto, ma non possiamo eliminare del tutto il rischio di errori per imprevisti. Però possiamo gestire questi errori se e quando si presenteranno.

 

Gestire gli errori nel codice

Una delle attività più trascurate del codice è la gestione degli imprevisti e degli errori derivati. Il Vba permette di intercettare gli errori e di gestirli. Invece di sospendere il codice, il vba mette a disposizione fuzioni che:

1.       ignorano l’errore e continuano l’esecuzione,

2.       spostano l’esecuzione in una porzione specifica del codice che permette di gestire l’errore.

Ignorare l’errore evita il blocco dell’esecuzione, ma può moltiplicare gli errori e generare danni ai dati o al foglio di lavoro. Quindi è consigliato ignorare l’errore in situazioni controllate e specifiche.

Tra l’altro ignorare l’errore non aiuta a scoprirne le cause. La sintassi per ignorare gli errori è: “On Error Resume Next” e viene posto all’inizio della macro primo o dopo le dichiarazioni, sempre prima del codice operativo.

La gestione degli errori invece consiste in una dichiarazione iniziale che comanda al sistema che in caso di errori il focus si sposti a un punto preciso del codice, seguito dal codice dedicato alla gestione.

La sintassi è: “On Error Goto NOME_PUNTO_DI_SALTO” che indica appunto di saltare (Goto) al punto indicato.

Il marcatore e il codice di gestione vengono di solito posti in fondo alla routine e la sintassi del punto è: “NOME_PUNTO_DI_SALTO:”, per esempio “Errore:”. Il codice che segue invece serve a gestire l’errore e può andare dalla semplice comunicazione dell’errore all’operatore con una finestra di dialogo, accompagnata dalle istruzioni per gestirlo manualmente, fino all’inserimento di codice (o al richiamo di una macro dedicata) che può analizzare il problema, svolgere l’attività in modo alternativo, tentare di risolvere la causa dell’errore o rispristinare le condizioni, perfino presentare un piccolo report.

Ha senso spendere tempo per gestire errori che non so se ci sono, se e quando si presenteranno?

Dipende.

No, se la macro è piccola, semplice o poco importante, o se per sua natura è molto improbabile che compariranno errori imprevisti.

Sì se la macro è grande e complessa, o se il foglio su cui lavora la macro viene usato da altri, non sviluppatori.

Il mio consiglio è di predisporre un blocco di codice da usare dove necessario, che comunica all’operatore indicazioni anche generiche su cosa fare, come e a chi rivolgersi. Per esempio:

‘ Marcatore di salto

ERROR:

‘ Comunicazione dell’errore

MsgBox “ATTENZIONE, SI E’ VERIFICATO UN ERRORE IMPREVISTO” _

& vbCr & vbCr & _

Err.Number & ” – ” & Err.Description _

& vbCr & vbCr & _

“QUESTO PUO’ ESSERE CAUSATO DA UNA VERSIONE PRECEDENTE DI EXCEL” _

& vbCr & vbCr & _

“O DA MODIFICHE IMPREVISTE AL CODICE O AL FOGLIO DI LAVORO ” _

& vbCr & vbCr & _

“PROVA A CHIUDERE, RIAPRIRE E RILANCIARE IL COMANDO” _

& vbCr & vbCr & _

“SE IL PROBLEMA PERSISTE INTERPELLA IL TUO CONSULENTE IT”, _

vbOKOnly, “ERRORE!”

 

Nel caso invece di automatismi complessi in fogli di lavoro di lunga durata che richiedono alta affidabilità, allora è importante prestare cura e attenzione al codice per la gestione degli errori, per evitare o minimizzare danni o sprechi di tempo rilevanti e mantenere l’operatività anche parziale dello strumento di lavoro. Sulla gestione degli errori dedicheremo un post specifico.

 

Lanciare le macro dal Vbe e dal codice

Come abbiamo già visto nella guida alle macro e nella guida al registratore di macro, le macro possono essere lanciate in molti modi diversi.

 

Da Excel:

1.       attraverso la finestra “Macro”, strumento di Excel per la gestione delle macro, utilizzato nella fase di sviluppo e test degli automatismi, che offre tra l’altro la possibilità di associare e lanciare le macro attraverso una sequenza di tasti;

2.       collegando la macro a un oggetto grafico (una forma, un’immagine, ecc) che in questo modo diventa cliccabile come un pulsante;

3.       usando uno dei controlli appositi, per esempio pulsante posizionato in una pagina del foglio di lavoro;

4.       con un pulsante nella barra di accesso veloce, o in un gruppo personalizzato della barra multifunzione, sistema utile in particolare se si gestiscono gli automatismi con la cartella macro personale;

5.       usando un controllo, come un pulsante, posizionato in una maschera preparata tramite l’editor vba.

 

Gli altri strumenti

Oltre a questi metodi di lancio delle macro che richiedono l’interazione dell’operatore con un comando o un controllo predisposto, ce ne sono altri strettamente legati al vba e al Vbe, il visual basic editor:

1.       puoi lanciare le macro all’interno dell’editor Vba, l’ambiente di sviluppo, con il comando “Esegui” o più semplicemente premendo F5 dopo aver selezionato la macro che desideriamo eseguire. La macro verrà eseguita dall’inizio alla fine, a meno della presenza di punti di interruzioni, finestre di dialogo che attenderanno il tuo input, o comandi di pausa o interruzione, inseriti nel codice o direttamente da tastiera (Ctrl+Inter per interrompere l’esecuzione);

2.       puoi lanciare l’esecuzione passo passo della macro con il comando “Esegui istruzione” o più semplicemente premendo F8, lo strumento fondamentale per il debugging del codice;

3.       come già spiegato è possibile eseguire la macro in modo automatico associandola a oggetti (pagine, foglio excel, controlli e maschere) e a precisi eventi provocati dall’attività dell’operatore; per esempio è possibile lanciare una macro all’apertura del foglio di lavoro che ripristini le condizioni del foglio o aggiorni in automatico i dati, o lanciare una macro che a qualunque cambiamento di una pagina aggiorni le tabelle e i grafici collegati;

4.       infine c’è un altro modo per eseguire una macro, ed è quello di farla lanciare o richiamare da un’altra macro attraverso lo stesso codice vba; è sufficiente inserire il nome della macro, con o senza il comando Call, per lanciare l’esecuzione della macro, per poi al termine tornare alla riga di codice successiva. Per esempio, puoi lanciare la macro scrivendo “Call NOME_MACRO” o semplicemente “NOME_MACRO”, sarà il sistema a riconoscere la macro.

Questo permette di eseguire sequenze di routine, o da un altro punto di vista offre la possibilità di smontare le procedure complesse con molte attività in parti più semplici da realizzare e gestire.

 

Per esempio, per creare una macro che realizzi su una base dati un’ampia gamma di controlli, di interventi di pulizia e revisione dei dati e alla fine produca un report particolareggiato delle rilevazioni e degli interventi, è opportuno dividerla in più routine che vengono via via richiamate da una macro principale ed eseguite a seconda dei risultati delle altri e delle condizioni.

Non una macro, ma 6 macro diverse di cui una di controllo, due di analisi dei dati, due per la revisione, una che realizzi il report del lavoro svolto.

 

Come si impara il vba

Come si impara il vba?

Come una lingua. Ma più facilmente. Per imparare il linguaggio vba è necessario imparare a memoria il vocabolario, cioè i nomi degli oggetti, delle proprietà e dei metodi, delle funzioni del vba, poi la sintassi, cioè il loro funzionamento e le regole d’utilizzo.

Ma non è sufficiente, come per lo studio di una nuova lingua, è necessario fare esercizio e pratica e farne molto, per fissare quello che hai studiato, per iniziare a parlare la lingua prima in modo goffo e facendo errori, poi con il tempo in modo sempre più fluente. Nel vba significa iniziare a scrivere codice ed esercitarsi, a lungo, finché non diventi bravo a sufficienza per i tuoi scopi.

Qualche semplice consiglio:

1.       impara il vocabolario minimo del linguaggio, oggetti, proprietà, metodi e le funzioni fondamentali di Excel, parti da questa guida vba, cerca altre fonti, tieni sempre aperto il browser sulle pagine del sito microsoft dedicate al visual basic;

2.       contemporaneamente studia la sintassi, cioè le regole con cui si usano le parole, per imparare a creare le frasi del linguaggio, cioè il codice vero e proprio; questo significa studiare come si scrivono le funzioni per controllare l’esecuzione del codice o quelle per richiamare le finestre di dialogo, come si scrivono i parametri dei metodi e delle proprietà più utili e complesse e via dicendo;

3.       il passo successivo è iniziare a usare quanto si è appreso, cosa che consiglio di fare appena possibile, per fare esperienza e apprendere ancora di più vocabolario e sintassi;

·         inizia a “leggere” macro esistenti scritte da altri, quelle che trovi in rete o ti danno gli amici, importale nell’editor Vba e ogni volta che trovi una parola che non conosci premi F1 per aprire la guida online e studiala; puoi iniziare dalle macro di Excel professionale, scaricale, studiale e lavoraci sopra;

·         inizia a modificare queste macro, lavoraci, sperimenta, prova a ottenere strumenti nuovi, solo tuoi;

·         registra macro con il registratore per ottenere il codice di comandi che non conosci, ma non usarlo e basta, studialo,

·         modifica le macro registrate, adattale ai tuoi bisogni, rendile più intelligenti e utili;

4.       Infine è importante imparare il metodo e le strategie migliori con cui progettare e realizzare macro evolute: passare dal realizzare elenchi di attività, a macro strutturate con controlli, interazioni, gestione degli errori, fino a realizzare e risolvere algoritmi progettati con attenzione sulla carta.

Il vba si impara studiando, ma soprattutto si impara dall’uso e dagli errori. Ricorda sempre che noi esseri umani impariamo facendo.

 

Concludiamo questa guida vba

In questa lunga guida vba abbiamo presentato il linguaggio con cui possiamo automatizzare i nostri fogli Excel, abbiamo parlato del linguaggio, delle sue caratteristiche, della programmazione, della progettazione delle macro, della struttura del codice, del modo di scrivere il codice, di come si impara il vba e molto altro.

Nonostante le molte pagine, questa nostra carrellata ha toccato solo una parte degli argomenti e in modo superficiale. Ma dopotutto questa è solo una guida introduttiva, non un manuale o un corso.

• Blog di ExcelProfessionale
• Canale Youtube di ExcelProfessionale
• Profilo Facebook di ExcelProfessionale
• Profilo Instagram di ExcelProfessionale