Sergio Scippacercola » 11.Programmazione in VBA

Argomenti

• Il VBA
• Le variabili
• Gli operatori
• Istruzioni di Input/Output
• Istruzioni di controllo sequenza
• Debugging
• Esempi ed esercizi

Modulo sviluppato in collaborazione con il prof. Pierangelo Savarese

Il VBA

VBA è un linguaggio di programmazione ad oggetti, sviluppato con lo scopo di aggiungere funzionalità ai fogli elettronici di Excel. Nel seguito si trova una breve introduzione al VBA ed alcune informazioni utili per acquisire una operatività di base. La trattazione è limitata ad alcuni argomenti ed è volutamente semplificata. Anche se il VBA è un linguaggio ad oggetti vengono presentati solo aspetti di programmazione procedurale.

Le variabili

Ogni linguaggio di programmazione prevede l’uso di variabili, queste si possono considerare come i contenitori dei dati, esse vengono usate nei programmi, per memorizzare i valori, che possono essere relativi ai dati in ingresso, ai risultati finali o a quelli intermedi dell’elaborazione.

Una variabile deve avere:

• un nome univoco;
• un tipo che indica il genere di informazione che contiene;
• un valore fra quelli possibili per il tipo, che può ovviamente essere modificato nel corso delle elaborazioni ed infine;
• una dimensione, in termini di celle di memoria occupate.

Le dichiarazioni di variabili

In un programma occorre specificare  le variabili con il relativo tipo. La specifica avviene mediante la scrittura di istruzioni (dette dichiarazioni) che rispettano la seguente sintassi:

Dim nome_variabile As tipo_variabile

Dove i termini in grassetto sono parole chiave del VBA e quindi sono obbligatori e non modificabili ed i termini in corsivo sono scelti dal programmatore. Occorre tenere presente che i nomi devono essere di lunghezza inferiore a 256 caratteri, iniziare con una lettera e non contenere altro che lettere e cifre (fatto salvo il segno di sottolineatura) ed il tipo deve essere uno di quelli previsti dal VBA.

Ogni variabile deve avere:

• un attributo (cioè un nome) univoco per poterla distinguere da tutte le altre;
• un tipo che indica il genere di informazione;
• un valore  che può ovviamente essere modificato nel corso delle elaborazioni;
• una dimensione se la variabile è del tipo vettore o matrice.

Esempi di dichiarazioni di variabili

• Dim NumeroIntero As Integer ‘dichiara una variabile di tipo intero
• Dim QuantiEuro As Currency ‘dichiara una variabile di tipo valuta
• Dim RadiceQuad As Single ‘dichiara un tipo numero con virgola

Le variabili che servono a contenere informazioni testuali, sono dette di tipo stringa. Perciò per memorizzare un testo occorre assegnarlo ad una variabile di tipo stringa ed inoltre  delimitarlo con doppi apici (che non vengono memorizzati, ma servono a delimitare il testo da memorizzare).
Dim Frase As String  ‘ dichiara un tipo sequenza di caratteri

Principali tipi di variabili in Vba

Vettori e matrici

Per array (o vettore) si intende un dato strutturato, cioè caratterizzato dall’essere una collezione di dati elementari dello stesso tipo ed organizzati logicamente come le componenti di un vettore .

Es. per memorizzare i sette numeri estratti di un concorso del Super Enalotto dovremmo definire separatamente sette variabili intere, ma è più semplice definire un vettore di sette elementi:

Dim Estratti (7) As Integer ‘ dichiara un vettore di sette numeri interi

Se i dati sono ordinati in righe e colonne la struttura corrispondente prende il nome di matrice.

Es. per memorizzare le due colonne di una schedina del totocalcio, si può usare una matrice di 13 righe e 2 colonne, come:

Dim Doppia (13, 2) As String ‘ è una matrice di caratteri

Per riferirsi ad un singolo elemento sono necessari due indici, il primo indica la riga, il secondo la colonna.

Es.

• Doppia(1,0) = “X” ‘ per la seconda partita in schedina si prevede
• Doppia(1,1) = “2″ ‘ pareggio o vittoria esterna.

Un foglio di lavoro Excel è una matrice di celle pertanto il linguaggio VBA consente di riferirsi a ciascuna cella di riga r e colonna c mediante la “variabile matrice” Cells(r,c).

Commenti

Nota: in qualsiasi riga di programma è possibile inserire dei commenti esplicativi, che non influenzano l’esecuzione del programma e servono solo a facilitare la comprensione di chi legge. Un commento si distingue dalle istruzioni poiché è sempre preceduto da un apostrofo; ciò sia se i commenti occupano tutta una riga, sia se seguano, sulla stessa riga, una istruzione VBA.

Tipi di variabili: numerici

Esempi

• RadiceQuadrata = 1.73 ‘ assegna direttamente un valore
• NumeroIntero = 4 * 3 ‘ assegna il valore mediante una espressione
• QuantiEuro = Stipendio – Spese
  • ‘ assegna il valore mediante altre
  • ‘ due variabili ed un’operazione

Tipi di variabili: stringa

Le variabili che servono a contenere informazioni testuali, sono dette di tipo stringa. Perciò per memorizzare un testo occorre assegnarlo ad una variabile di tipo stringa ed inoltre bisogna delimitarlo con doppi apici (che non vengono memorizzati, ma servono a delimitare il testo da memorizzare).

Esempio:

• Dim Frase As String ‘dichiara un tipo sequenza di caratteri
• Frase = “Buongiorno a tutti” ‘memorizza un testo di saluto

Operatori di Excel

• Aritmetici: +, -, *,/,^
• Logici: E, O, SE, …
• Su stringhe: &, …Le stringhe vanno scritte fra doppi apici e si possono concatenare sia con altre stringhe, che con i valori assunti dalle variabili, di cui vanno riportati semplicemente i nomi

Es.: “Roma” & “Firenze” & citta

Operatori di VBA

• Aritmetici: +, -, *,/,^
• Logici: AND, OR, XOR, NOT
• Su stringhe: &, like
• Di confronto: =, <,>,=>, <>

Ambiente di sviluppo VBA

VBA opera su oggetti (cartella di lavoro, foglio, barra, …)

• Gli oggetti hanno organizzazione gerarchica (Il punto separa ogni oggetto nella gerarchia)
  • Es. Application.Workbook(“Cartel1″).Worksheet(“Foglio1″).Range(“A1″)
• Gli oggetti hanno proprietà (value,font, carattere, bold, etc.)
  • Es. Workbook(“Cartel1″).Worksheet(“Foglio1″).Range(“A1″).Font.Bold=True
• Gli oggetti possiedono metodi
  • Es. ClearContents

Input/output

1. Sub InputOutput()
2. Dim numero As Integer
3. numero = InputBox(“inserisci un numero”)
4. numero = numero ^ 2
5. MsgBox (“Il quadrato è: ” & numero)
6. End Sub

I costrutti condizionali

1. Sub Augurio()
2. If Cells(1,1) > 5 Then
3. MsgBox “Buon riposo!”
4. End If
5. End Sub

—-

1. Sub Augurio()
2. If Cells(1,1) > 5 Then
3. MsgBox “Buon riposo!” ‘ messaggio festivo o prefestivo
4. Else ‘ altrimenti
5. MsgBox “Buon lavoro!” ‘ messaggio feriale
6. End If
7. End Sub

I cotrutti ciclici

1. Sub RipetiMessaggio()
2. Dim i As Integer ‘ variabile per contare i cicli
3. For i = 1 To 5 ‘ inizio ciclo
4. MsgBox “Buongiorno a tutti! e sono ” & i
5. Next i ‘ fine ciclo
6. End Sub

Es. scriviamo i quadrati dei primi dieci numeri interi nella colonna E a partire dalla riga 1:

• Sub ScriviQuadrati()
• For i = 1 To 10
• Cells(i, 5) = i ^ 2
• Next i
• End Sub

Il Ciclo Do While / Loop

1. Sub Ciclowhile()
2. Dim somma As Integer
3. Dim numero As Integer
4. Do While somma < 10 ‘ inizio ciclo
5. numero = InputBox(“inserisci un numero”)
6. somma = somma + numero
7. Loop ‘ fine ciclo
8. End Sub

L’esecuzione di un ciclo Do While/Loop può essere interrotta anticipatamente se diviene inutile la prosecuzione dei cicli, mediante l’istruzione:Exit Do ‘interrompe l’esecuzione del ciclo Do ‘While\Loop

che provoca il salto immediato dell’elaborazione, all’istruzione successiva a Loop.

Esempi

Inserire un cognome ed effettuare la ricerca sulle prime 1000 celle della colonna A del foglio elettronico. Esemplifica la possibilità di costruire una funzione simile ad una già presente in Excel (nel caso la funzione CERCA.VERT()), ma più flessibile, in quanto riporta la posizione del cognome richiesto e consente di individuare la presenza di omonimi.

• Sub CercaNome()
• Dim r As Integer ‘variabile per l’indice di riga
• Dim cognome As String ‘variabile di input
• cognome = InputBox(“Cognome da cercare: “)
• For r = 1 To 1000 ‘inizio ciclo di ricerca
• If Cells(r, 1) = cognome Then
• MsgBox (cognome & ” è nella riga ” & r)
• End If
• Next r ‘avanza alla riga successiva
• MsgBox (“ricerca terminata”) ‘avviso di ricerca terminata
• End Sub

Esempi

La seguente macro analizza una area del foglio con vertice in A1, della dimensione di 100 righe per 50 colonne, su ogni cella viene applicato un test di confronto con il valore 100, quando viene incontrata la prima cella contenente un valore superiore, la macro mostra un messaggio che indica la riga e la colonna in cui si trova la cella e quindi termina. La macro esemplifica la possibilità di applicare una condizione del tipo SE() ad un insieme di celle.

1. Sub CercaCento()
2. Dim r As Integer ‘variabile per l’indice di riga
3. Dim c As Integer ‘variabile per l’indice di colonna
4. For r = 1 To 100 ‘ciclo di scansione delle righe
5. For c = 1 To 50 ‘ciclo di scansione delle colonne
6. If Cells(r, c) > 100 Then
7. MsgBox “trovato valore in riga ” & r & “colonna ” & c
8. Exit Sub ‘termine macro per valore trovato
9. End If ‘fine test
10. Next c ‘avanza alla colonna successiva
11. Next r ‘avanza alla riga successiva
12. MsgBox (“ricerca terminata”) ‘avviso di ricerca terminata
13. End Sub

Esempi

La macro seguente trova le radici reali dell’equazione di 2° grado ax2 + bx + c = 0, prendendo come coefficienti a, b, e c, le prime tre celle del foglio di lavoro, per semplicità non si è considerato il caso delle soluzioni complesse coniugate che viene però segnalato con un messaggio.

Esempi

1. Sub Equazione()
2. Dim a, b, c As Single ‘variabili per i coefficienti a,b e c
3. Dim d, delta As Single ‘variabili per il discriminante
4. Dim x1, x2 As Single ‘variabili per le radici
5. a = Cells(1, 1) ‘assegna ad a la cella A1
6. b = Cells(1, 2) ‘assegna a b la cella B1
7. c = Cells(1, 3) ‘assegna a c la cella C1
8. d = b ^ 2 – 4 * a * c ‘calcola il discriminante
9. If d > 0 Then
10. delta = Sqr(d) ‘calcola la radice del discriminante
11. Else
12. MsgBox “Soluzioni complesse”
13. Exit Sub ‘uscita anticipata dalla macro
14. End If
15. x1 = (-b + delta) / 2 * a ‘calcola la prima radice
16. x2 = (-b – delta) / 2 * a ‘calcola la seconda radice
17. MsgBox “le soluzioni sono: ” & x1 & ” e ” & x2
18. End Sub

Esercizi

• Macro n. 1 – Calcolo della soluzione x di un’equazione di primo grado del tipo: ax+b=0
  • Leggere tramite inputbox i valori dei coefficenti a e b;
  • se a=0 segnalarlo con un msgbox altrimenti calcolare il risultato ed inserirlo in cella A1.
• Macro n. 2 – Nella colonna A da A1 a A10 si trovano dei numeri interi positivi. Sviluppare una macro che:
  • legga un numero N con un inputbox;
  • ricerca in quale cella si trova quel numero ed indichi con un msgbox in quale riga è stato trovato il numero N.
• Macro n. 4 – Sviluppare una macro che:
  • legga un numero N fornito con un inputbox;
  • ricerchi nella matrice di numeri interi 10×10 in quale riga e colonna è il numero;
  • se trova il numero N con un msgbox si deve indicare la riga e la colonna dove si trova il numero.

Esercizi

• Macro n. 5 – Sviluppare una macro che:
  • con un inputbox legge un nome X;
  • cerca se nei venti nomi in colonna A è presente X;
  • inserisce in colonna B, in corrispondenza del nome trovato, la dizione “Presente”.
• Macro n. 6 – Sviluppare una macro che:
  • con un inputbox legge un nome X;
  • cerca se nei venti nomi in colonna A è presente X; se trova il nome con un msgbox indica “nome presente” e il numero di riga dove è presente.
• Macro n. 7 – Sviluppare una macro che:
  • legga un numero N da un inputbox;
  • ricerchi nella matrice 5×5 se è presente il numero N;
  • indichi con un msgbox la riga e la colonna in cui è presente il numero N.
• Macro n. 8 – In colonna A (da 1 a 10) vi sono delle matricole numeriche. In colonna B (da 1 a 10) i cognomi. Creare una macro che:
  • legga con inputbox una matricola M;
  • ricerca in colonna A la matricola e, se esiste, cancella in colonna B il cognome corrispondente alla matricola trovata.

Esercizi

• Macro n. 9 – In colonna A (da 1 a 10) vi sono i cognomi degli studenti; in colonna B gli esami superati. Sviluppare una macro che:
  • legge da inputbox il numero N di esami sostenuti;
  • segnala nella colonna C con un asterisco gli studenti con lo stesso numero N di esami.
• Macro n. 10 – In colonna A da 1 a 13 vi è la colonna vincente. In colonna B da 1 a 13 vi è una colonna “giocata”. Sviluppare una macro che conta i punti realizzati e li inserisce in cella B14.
• Macro n. 11 – In colonna A (da riga 1 a 10) e in colonna B (da riga 1 a 10) vi sono dei valori numerici. Sviluppare una macro che cancella in colonna B i valori diversi da quelli della colonna A.