Excel - Visual Basic for Applications (VBA)

Uno strumento semplice e per chiunque

Inconsciamente quando creiamo una macro è come se stessimo programmando in VBA (Visual Basic for Application).

Cos'è VBA?

Visual Basic for Applications (VBA) è un'implementazione di Visual Basic inserita all'interno di applicazioni Microsoft quali la suite Microsoft Office o Visio, altri programmi contengono un'implementazione parziale di VBA (ad esempio AutoCAD e WordPerfect).
Nonostante il suo stretto legame con Visual Basic, VBA non può essere usato per eseguire applicazioni stand-alone, ma è comunque possibile una certa interoperatività fra applicazioni (ad esempio è possibile creare un report in Word a partire da dati di Excel) grazie all'automazione (tecnologia COM, Component Object Model).
[Fonte: wikipedia]

Modificare una macro con VBA

Per introdurre VBA guardiamo la macro fatta nella sezione precedente per togliere i riferimenti da una colonna e cambiargli formato:

Una volte eseguite queste operazioni cosi come descritte avremo una macro collegata con lo shortcut che gli abbiamo dato.

Per modificare la macro con VBA andiamo su Sviluppo > Visualizza macro




Selezioniamo la macro che vogliamo modificare e clicchiamo su modifica




Una volta cliccato su modifica si aprirà visual basic con inserito nel modulo1 la macro da noi creata.




Codice VBA della macro:




Sub Macro1()
'
' Macro1 Macro
' Macro di prova
'
' Scelta rapida da tastiera: CTRL+g
'
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
End Sub

Come si può notare dal codice sopra, ogni movimento fatto corrisponde a delle procedure specifiche; infatti con Range(Selection, Selection.End(xlDown)).Select selezioniamo tutta la colonna; con Selection.Copy copiamo la selezione; con Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=False incolliamo per valori; con Application.CutCopyMode = False rimuoviamo l'elemento copiato; ed infine con Selection.NumberFormat = "m/d/yyyy" gli cambiamo il formato in formato data.



Programmare in VBA

Da qui in poi si fa sul serio!!! Premetto che non affronteremo a fondo tutti i comandi presenti in VBA perchè credo che, una volta compresi i meccanismi e la logica che sta alla base del linguaggio, il più fa la pratica e affrontare problemi differenti.

Iniziamo elencando le variabili utilizzabili in questo linguaggio:

Tipologia di dati Dimensione Intervallo memorizzazione
Byte 1 byte Da 0 a 255
Boolean 2 byte True o False
Integer 2 byte Da -32.768 a 32.767
Long (intero lungo) 4 byte Da -2.147.483.648 a 2.147.483.647
Single (virgola mobile semplice) 4 byte Da -3.402823E38 a -1.401298E45 e da 1.401298E45 a 3.402823E38
Double (virgola mobile a doppia precisione) 8 byte -4.94065645841247E-324 per i valori negativi e 4.94065645841247E-324 per i valori positivi
Currency 8 byte Da -922.337.203.685.477,5808 a 922.337.203.685.477,5808
Date 8 byte Dal 1 Gennaio 100 al 31 Dicembre 9999
Object 4 byte Qualsiasi riferimento a oggetto
String (Testo) 10 byte + lunghezza stringa Da 0 a, circa, 2 miliardi
Variant 22 byte + lunghezza stringa Da 0 a circa 2 miliardi

Prima di poter vedere come dichiarare una variabile occorre definire il concetto di routine. In Visual Basic esistono due tipi di routine:

La visibilità di una variabile, o di una routine, definisce la sua disponibilità all'interno del progetto. Si possono dichiarare variabili e funzioni a tre livelli:

Prima di poter osservare nel concreto la differenta tra queste due routine dobbiamo aprire un nuovo foglio di Excel e andare su Sviluppo > Visual Basic oppure cliccare ALT + F11. Una volta aperto Visual basic ci troveremo in questa situazione:



Occorre creare un nuovo modulo andando, all'interno di Visual Basic, su Inserisci > Modulo


Apriamo il modulo, cliccandoci sopra due volte, e nell'editor di Visual Basic creiamo la nostra prima routine scrivendo il seguente codice: 

' La seguente variabile è visibile nell'intero progetto
Public risultato As String

' La seguente variabile è visibile a livello di modulo
Private testo As String

Sub inviamessaggio(messaggio As String)
    'Questa routine non restituisce un valore ma esegue un'azione
    MsgBox messaggio, vbInformation, "Un messaggio per te:"
End Sub
Function moltiplicazione(numero1 As Integer, numero2 As Integer) As Integer
    ' Questa routine restituisce un valore ed esegue una azione
    moltiplicazione = numero1 * numero2
End Function

Sub la_prima_macro_con_vba()
    testo = "Il risultato della moltiplicazione è: "
    
    ' La seguente variabile è visibile solo all'interno della funzione
    Dim ris As Integer
    
    ris = moltiplicazione(5, 5)
    risultato = testo & CStr(ris)
    inviamessaggio (risultato)
End Sub


Si può notare come la routine inviamessaggio() esegua un azione (creare un message box) ma non restituisce un valore, mentre al contrario la funzione moltiplicazione() esegue un operazione e restituisce come valore il risultato.

La terza routine esegue la moltiplicazione tra 5 e 5 attraverso la seconda funzione e restituisce il risultato come messaggio utilizzando la prima routine. Per far partire lo svolgimento di questo primo piccolo programma clicchiamo su Esegui > Esegui Sub/UserForm o clicchiamo F5.



Selezioniamo poi la macro che vogliamo far partire ossia la_prima_macro_con_vba e clicchiamo su Esegui



Il risultato finale che ci compare è:

Questa macro che abbiamo creato non è particolarmente utile ma spero aiuti a capire il funzionamento di come programmare in VBA.


Richiamare un valore dal foglio di calcolo

Per poter inserire o poter operare con un valore inserito nel foglio di calcolo ci sono diversi i metodi, i principali metodi sono 2:



Come ultimo argomento di questa prima sezione analizziamo gli Array ossia dei vettori contenenti degli elementi.
Array: Un array è un insieme organizzato di elementi omogenei, identificati da uno stesso nome e da uno o più indici. 

'Metodo 1 : Usare Dim
Dim arr1()	'arrey di dimensione 0

'Metodo 2 : Specificare la dimensione
Dim arr2(5)  'Dimensione di 5 elementi

'Metodo 3 : Utilizzare 'Array' per specificare i parametri
Dim arr3
arr3 = Array("elemento1","elemento2","elemento3")

Specificare le differenti tipologie di elementi:

Dim arr(5)
arr(0) = "1"           'Numero come String
arr(1) = "VBScript"    'String
arr(2) = 100 		       'Numero intero
arr(3) = 2.45 		     'Numero decimale
arr(4) = #16/08/2020#  'Data
arr(5) = #14.15 PM#    'Ora
Solitamente a senso inserire all'interno di un array valori con lo stesso formato

Aumentiamo la difficoltà: If...Then...

Quando programmiamo spesso capita di dover instruire il computer a fare una certa operazione se è presente una determinata caratteristica e a farne un'altra quando questa non fosse presente.
In VBA per gestire questo tipo di eccezioni si utilizza il SE (ossia IF): 

If condizione Then
    'operazioni da eseguire se la condizione è rispettata
    '...
    '...
    '...
    Else
    'operazioni da eseguire se la condizione no è rispettata
    '...
    '...
    '...
End If


Per definire una condizione si utilizzano tre tipi di operatori:


Per comprendere l'utilizzo di questa procedura facciamo un esempio:

Si vuole creare un input box nel quale dobbiamo inserire un valore e restituiamo come messaggio un testo differente in base a se il numero è maggiore, uguale o minore di 0:

Sub esempioIF()

    Dim num As Integer
    num = InputBox("Inserisci un valore ", "Esempio utilizzo If")
    If num > 0 Then
        MsgBox "Il numero è positivo", vbInformation, "Risultato:"
    ElseIf num < 0 Then
        MsgBox "Il numero è negativo", vbInformation, "Risultato:"
    Else
        MsgBox "Il numero è uguale a zero", vbInformation, "Risultato:"
    End If
End Sub


L'input box:




I risultati in base al numero inserito:



Ripetere azioni per più volte:


Ci sono diversi metodi per ripetere azioni pù volte in base a quello che dobbiamo fare. Il primo metodo che guardiamo è il ciclo FOR (TO e EACH) e poi approfondiremo la reiterazione con il Do (Until e While).

For To



For Counter = Start To End [Step Value]
[Azioni da reiterare]
Next [counter]
Guardiamo un esempio:
Vogliamo aggiungere in diagonale un valore in una matrice 7x7

Dim i As Integer

For i = 1 To 7
    Foglio1.Cells(i, i).Value = 100
Next i


Adesso proviamo a modificare lo step: 

Dim i As Integer

For i = 1 To 6 Step 2
    Foglio1.Cells(i, i).Value = 100
Next i


Per mettere delle eccezioni per le quali il ciclo for si deve interrompere si consiglia di utilizzare un IF per defiire l'eccezione e al suo interno Exit For

For Each



For Each element In collection
[Azioni da reiterare]
Next [element]
Guardiamo un esempio:
Vogliamo aggiungere un dei fogli di calcolo con dei nomi contenuti in un array. 


Dim arr
arr = Array("Contabilità", "Finanza", "Assicurazione")


For Each b In arr
  ActiveWorkbook.Sheets.Add(after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)).Name = b
Next b


Guardiamo un altro esempio:
Vogliamo sottolineare di rosso i valori contenuti nella prima colonna inferiori a 10. (occorre inserire dei valori nella prima colonna del foglio di calcolo) 

Dim valori As Range
Dim colonna As Range
Set colonna = Range("A1", Range("A1").End(xlDown))
For Each valori In colonna
    If valori.Value < 10 Then
        valori.Interior.Color = vbRed
    End If
Next valori


Do Until e Do While


Differenze
Ci sono due modalità per dichiarare questa reiterazione, dichiarando la condizione di interruzione all'inizio: 

Do { While | Until } [condizione]
[Azioni da reiterare]
Loop
e dichiarando la condizione di interruzione alla fine: 
Do
[Azioni da reiterare]
Loop { While | Until } [condizione]

Per una mia comprensione logica io preferisco sempre mettere la condizione all'inizio e mi trovo più spesso ad utilizzare Until piuttosto che While ma "De gustibus non disputandum".

Guardiamo un esempio:
Vogliamo sommare i numeri da 1 a 10 (questo stesso problema può essere affrontato da due punti di vista differenti):


Sub prova1()
Dim i As Integer
i = 1
Do Until i > 10
    'continuo a sommare fino a quando trovo
    'un valore maggiore di 10
    Result = Result + i
    i = i + 1
Loop
MsgBox Result
End Sub


Sub prova2()
Dim i As Integer
i = 1
Do While i <= 10
    'continuo a sommare fino a quando non trovo
    'un valore minore uguale di 10
    Result = Result + i
    i = i + 1
Loop
MsgBox Result
End Sub




Facciamo un po' di chiarezza!

Una sintesi un po' più strutturata

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:

Gli insiemi di oggetti ci permettono di identificare univocamente i singoli oggetti, ad esempio Workbook può essere inteso come un oggetto nel senso che posso modificare le proprietà del foglio di calcolo ma può anche identificare un insieme di oggetti in quanto all'interno del foglio ci saranno presenti tabelle o altro. In questi casi si utilizza il nome al plurale degli oggetti per identicarne la categoria:

Di ogni oggetto posso definire le proprietà o utilizzarne dei metodi. In VBA per definire metodo e proprietà davanti all'oggetto si pone il .. Un esempio di definizione di una proprietà è: Worksheets("Foglio1").Range("A1:B12").Font.Bold imposta il grassetto; Un esempio di definizione di un metodo: Worksheets("Foglio1").Range("A1:B12").Copy copia le celle.


Le principali proprietà:
NomeDescrizione
1 NameTutti gli oggetti hanno un nome che può essere modificato o assegnato. Alcuni oggetti ne hanno due, per esempio le pagine (worksheet) hanno un nome formale (che possiamo modificare rinominando il foglio di calcolo) e un nome che corrisponde all'etichetta (che possiamo rinominare ActiveWorkbook.Worksheets ("Foglio1"). Name = 'Nuovonome').
2 ValueViene utilizzata per l'associazione di valori nelle celle è utilizzata spesso con gli oggetti range e cells (es. Worksheets(1).Range("A1").Value = 100).
3 VisibleQuesta proprietà identifica la visibilità di un oggetto e può assumente un valore booleano: True o False (es. Worksheets(1).Visible = False / True).
4 FormulaViene utilizzata per l'associazione di formule nelle celle è utilizzata spesso con gli oggetti range e cells (es. Worksheets(1).Range("A1").Formula = "=SE(B1=""; "NA"; B1)")
5 FormulaR1C1Viene utilizzata per l'associazione di formule con notazione R1C1 nelle celle è utilizzata spesso con gli oggetti range e cells (es. Worksheets(1).Range("R1C1").Formula = "=SE(R1C2=""; "NA"; R1C2)") [notazione R1C1= "R+numero_riga+C+numero_colonna"]
6 FontQuesta proprietà definisce il font impiegato.
7 CountQuesta proprietà si utilizza con gli insiemi di oggetti e restituisce il numero di oggetti relativi presenti.
8 EndQuesta proprietà di Range e rappresenta la fine della regione di celle in cui è presente la cella selezionata, in una delle quattro direzioni possibili: xlDown / xlUp / xlRight / xlLeft (es. Range("A1").End(xlDown).Select)
9 OffsetQuesta proprietà di Range che restituisce una posizione vicina alla cella selezionata, bisogna indicare direzione e coordinate di spostamento (es. Range("A1").Offset(1, 0)).
10 CellsQuesta proprietà restituire una cella inserendo riga e colonna.
11 UsedRangeQuesta proprietà di Worksheet che identifica l'area di lavoro della pagina (es. ActiveSheet.UsedRange.Copy).
12 DisplayAlertsQuesta proprietà di Application permette di sospendere gli allarmi automatici del sistema (es. Application.DisplayAlerts = False) attenzione però ad aprire file di cui non si conosce l'origine e sconsiglio di farlo ma in alcuni processi può essere utile.

I principali metodi:
NomeDescrizione
1 SelectQuesto funzione selezione un oggetto.
2 ActivateQuesta funziona porta in primo piano un oggetto senza selezionarlo.
3 AddQuesto metodo è utilizzato per creare nuovi oggetti.
4 CopyQuesta funzione copia un certo elemento selezionato in precedenza.
5 PasteQuesta funzione incolla un certo elemento copiato in precedenza.
6 DeleteQuesta funzione serve per eliminare un determinato oggetto.
7 CloseQuesto metodo è utilizzato per chiudere un oggetto (workbook, userform, ecc.).
8 SaveQuesta funzione permette di salvare il file.
9 PrintOutQuesta funzione consente di stampare l'oggetto specificato.
10 ClearContentsQuesto metodo cancella il contenuto delle celle selezionate (formule e valori).
11 AutofillQuesta funzione dell'oggetto Range riempie in automatico le celle dell'intervallo dichiarato a partire dal contenuto di alcune celle (uguale allo strumento che possiamo richiamare con un doppio clic sul quadrato in basso a destra della selezione o trascinando la selezione in una direzione).