Uso avanzato di MS Excel - dista.unibo.it

Uso avanzato di MS Excel

1

LE BASI DEL FOGLIO ELETTRONICO EXCEL ......................................................................................... 5 GLI ELEMENTI FONDAMENTALI ........................................................................................................................... 5 LE FORMULE ........................................................................................................................................................ 5 RIFERIMENTI ....................................................................................................................................................... 6 NOMI ................................................................................................................................................................... 7 TRIDIMENSIONALITÀ ........................................................................................................................................... 7 RIFERIMENTI AD ALTRE CARTELLE ...................................................................................................................... 8 LE FUNZIONI ........................................................................................................................................................ 8 Funzioni di base............................................................................................................................................. 8 Funzioni logiche ............................................................................................................................................ 9 Funzione di ricerca verticale ....................................................................................................................... 10 Funzioni del tempo....................................................................................................................................... 10 VISUAL BASIC PER APPLICAZIONI ........................................................................................................... 11 PREMESSE ......................................................................................................................................................... 11 ESEMPI BREVI .................................................................................................................................................... 11 Esempio 1 – Creare una macro dall’editor VBA ......................................................................................... 11 Esempio 2 – La funzione MsgBox............................................................................................................... 12 Esempio 2 – Personalizzazione della barra del titolo di Excel.................................................................... 13 Esempio 3 – Individuazione di colonne nascoste......................................................................................... 13 Esempio 4 – Eliminazione di un foglio ........................................................................................................ 13 Esempio 5 – Protezione di una macro ......................................................................................................... 14 Esempio 6 – Esecuzione automatica di una macro all’apertura ................................................................. 14 Esempio 7 – Conteggio del numero di righe, colonne e fogli ...................................................................... 14 Esempio 8 – Copia di dati............................................................................................................................ 15 Esempio 9 – Inserimento della data corrente .............................................................................................. 15 Esempio 10 – Controllo del contenuto di una cella ..................................................................................... 15 Esempio 11 – Controllo della posizione di una cella................................................................................... 16 Esempio 12 – Eliminazione delle celle vuote............................................................................................... 16 Esempio 13 – Funzioni................................................................................................................................. 16 LE BASI DI VBA ................................................................................................................................................ 17 L’EDITOR DI VISUAL BASIC ............................................................................................................................... 19 Attivazione di VBE ....................................................................................................................................... 19 Le finestre VBE ............................................................................................................................................ 20 Barra menu..............................................................................................................................................................20 Barre degli strumenti ..............................................................................................................................................20 Finestra di Gestione progetti.................................................................................................................................20 Finestra codice........................................................................................................................................................20 Finestra immediata.................................................................................................................................................20

GESTIONE PROGETTI .......................................................................................................................................... 21 Aggiungere un nuovo modulo VBA.............................................................................................................. 22 Rimuovere un modulo VBA.......................................................................................................................... 22 Esportare e importare oggetti...................................................................................................................... 22 LE FINESTRE DI CODICE ..................................................................................................................................... 22 Minimizzare e massimizzare finestre ........................................................................................................... 23 Mantenimento del codice VBA..................................................................................................................... 24 Entrando nel codice VBA............................................................................................................................. 24 Entrare manualmente nel codice..........................................................................................................................24 Usare il registratore di macro................................................................................................................................27 Copiare il codice VBA ............................................................................................................................................29

PERSONALIZZARE L’AMBIENTE VBE................................................................................................................. 29 La scheda Editor.......................................................................................................................................... 29 Controllo automatico sintassi ................................................................................................................................30 Dichiarazione di variabili obbligatoria ..................................................................................................................30 Elenco membri automatico....................................................................................................................................30 I .................................................................................................................................................................................31 nformazioni rapide automatiche ...........................................................................................................................31 Descrizione dati automatica ..................................................................................................................................31 Rientro automatico .................................................................................................................................................31 Trascinamento della selezione .............................................................................................................................32 Visualizza modulo intero........................................................................................................................................32 Separa routine.........................................................................................................................................................32

La scheda Formato editor............................................................................................................................ 32 Uso avanzato di MS Excel

2

Colori ........................................................................................................................................................................33 codice .......................................................................................................................................................................33 Tipo di carattere......................................................................................................................................................33 Dimensione..............................................................................................................................................................33 Barra indicatori........................................................................................................................................................33

La scheda Generale ..................................................................................................................................... 33 La scheda Ancoraggio ................................................................................................................................. 33 OGGETTI E COLLEZIONI ..................................................................................................................................... 34 L’oggetto gerarchia ..................................................................................................................................... 34 Le collezioni................................................................................................................................................. 35 Riferimento all’oggetto ................................................................................................................................ 35 PROPRIETÀ E METODI ........................................................................................................................................ 36 Proprietà degli oggetti................................................................................................................................. 36 Metodi degli oggetti ..................................................................................................................................... 37 GLI OGGETTI RANGE ......................................................................................................................................... 37 La proprietà Range...................................................................................................................................... 37 La proprietà celle......................................................................................................................................... 39 COSA SAPERE DEGLI OGGETTI............................................................................................................................ 40 Concetti essenziali da ricordare .................................................................................................................. 40 VBA: PROGRAMMAZIONE FONDAMENTALE......................................................................................... 42 ELEMENTI DI LINGUAGGIO VBA: UNA PANORAMICA......................................................................................... 42 COMMENTI ........................................................................................................................................................ 42 Ispezione del codice VBA............................................................................................................................. 43 VARIABILI, TIPI DI DATI E COSTANTI .................................................................................................................. 45 Definizione del tipo di dati........................................................................................................................... 46 Dichiarazione di variabili............................................................................................................................ 48 Determinare il tipo di dati.......................................................................................................................................48 Forzatura a dichiarare tutte le variabili ................................................................................................................50

Scopo delle variabili .................................................................................................................................... 50 Variabili local ...........................................................................................................................................................50 Variabili modulewide ..............................................................................................................................................52 Variabili public .........................................................................................................................................................52 Variabili static ..........................................................................................................................................................53

Lavorare con le costanti .............................................................................................................................. 53 Dichiarazione di costanti........................................................................................................................................53 Costanti predefinite ................................................................................................................................................54

Le stringhe ................................................................................................................................................... 54 Le date ......................................................................................................................................................... 55 DICHIARAZIONI DI ASSEGNAZIONE .................................................................................................................... 56 VETTORI ............................................................................................................................................................ 57 Dichiarare vettori ........................................................................................................................................ 57 Dichiarare vettori multidimensionali........................................................................................................... 58 VARIABILI OGGETTO ......................................................................................................................................... 58 TIPI DI DATI DEFINITI DALL’UTENTE .................................................................................................................. 59 FUNZIONI BUILT-IN ............................................................................................................................................ 60 MANIPOLARE OGGETTI E COLLEZIONI................................................................................................................ 64 Costruzioni With-End With .......................................................................................................................... 64 Istruzioni Each-Next .................................................................................................................................... 65 CONTROLLO DELL’ESECUZIONE ........................................................................................................................ 67 Dichiarazione GoTo .................................................................................................................................... 67 Istruzioni If-Then ......................................................................................................................................... 67 Istruzione Select Case .................................................................................................................................. 71 Looping di blocchi di istruzioni ................................................................................................................... 73 Loops For-Next .......................................................................................................................................................74 Loops Do While.......................................................................................................................................................76 Loops Do Until.........................................................................................................................................................78

USO DI COMPONENTI ESTERNE................................................................................................................. 79 LETTURA DI FILES METEO CON PAR_CSDLL.DLL ............................................................................................ 79 CALCOLO EVAPOTRASPIRAZIONE CON ET_CSDLL.DLL ................................................................................... 82 VALUTAZIONE DELLA PERFORMANCE DEI MODELLI CON IRENE_DLL.DLL ..................................................... 86 1) Caricamento dei dati. .............................................................................................................................. 87 Uso avanzato di MS Excel

3

Es. 1 – Caricamento dati .......................................................................................................................................87

2) Calcolo delle statistiche........................................................................................................................... 87 Es. 2 – Calcolo dei parametri di regressione......................................................................................................89

3) Gestione degli output............................................................................................................................... 90 Es. 3 – Display dei risultati ....................................................................................................................................90

Altro esempio applicativo ............................................................................................................................ 90


4

Le basi del foglio elettronico Excel

Gli elementi fondamentali I file di Excel hanno estensione XLS e sono denominati Cartelle di lavoro, che a loro volta contengono più Fogli. Un foglio di Excel è suddiviso in righe (65536) e colonne (256). L’elemento fondamentale del foglio di lavoro è costituito dalla cella, a sua volta caratterizzata dal riferimento che la individua. Ad esempio, A1 è la prima cella, C3 è la cella posta all’incrocio tra la terza colonna (C) e la terza riga (3). Excel accetta due tipi di dati in una cella: le costanti e le formule. •

•

Le costanti sono suddivise in tre categorie principali o Valori numerici: 57, 9%, £125000 o Testi: azoto lisciviato, N. 20, output o Data e ora: 18/11/2002, 21.00 o Valori logici: FALSO, VERO o Valori di errore: #DIV/0!, #NOME?, #RIF!, #VALORE! Le formule sono una serie di istruzioni, che possono essere composte da costanti, o da riferimenti a costanti oppure da operatori aritmetici, atte ad ottenere dei risultati. L’addizione è un esempio di formula.

Sulle celle si possono fare diverse operazioni: selezione celle, cancellazione contenuto, modifica contenuto, formattazione contenuto, trascinamento contenuto, dimensionamento, inserimento celle, eliminazione celle, stampa contenuto. Anche i fogli di lavoro permettono diverse operazioni: movimento tra fogli, aggiunta fogli, eliminazione fogli, rinomina fogli, spostamento fogli, duplicazione fogli, stampa contenuto, lavoro simultaneo su più fogli. Quest’ultima operazione definisce la proprietà della tridimensionalità (vedere paragrafo Tridimensionalità).

Le formule Le formule sono espressioni aritmetiche del tipo 10+5. Una formula viene digitata in una cella nella forma del tipo: =10+5. Il segno uguale segnala al programma che i caratteri seguenti danno vita a una formula. Dopo la conferma (tasto Invio) la cella visualizza il valore 15. Gli OPERATORI ARITMETICI - noti come PIÙ, MENO, PER, DIVISO – in un PC assumono la seguente forma: +, -, *, /. L’espressione: =10+5*2 dà come risultato 20. L’espressione: =(10+5)*2 dà come risultato 30. Una formula può anche gestire valori di testo. Se la cella A1 contiene il nome Modello, introducendo la formula =A1 in un’altra cella, anch’essa visualizzerà Modello. Questo è un valore stringa. Uso avanzato di MS Excel

5

È possibile concatenare (unire) diversi valori di testo usando l’operatore spaziale & (e commerciale). Avendo in A1 Modello e in B1 colturale, introducendo in un’altra cella la formula =A1&B1 si ottiene Modellocolturale. Per inserire uno spazio tra sostantivo e aggettivo, la formula va modificata come: =A1&” “&B1. Quest’ultima formula usa due operatori di concatenamento (&) e una costante di stringa, cioè lo spazio racchiuso tra le doppie virgolette. L’operatore & può concatenare anche celle contenenti valori numerici. Se la cella A1 contiene 10 e la cella B1 5, inserendo la formula =A1&B1 in un’altra cella si ottiene 105. Questo valore risulta trasformato in un valore di testo, ma può essere usato per qualsiasi operazione matematica. Quando le formule diventano complesse può diventare difficile capire a quali celle facciano riferimento. In aiuto viene l’opzione Verifica formule, richiamabile dal menu Visualizza/Barre degli strumenti. Posizionandosi su di una cella contenente una formula e cliccando sul primo pulsante, Individua precedenti, sono indicate con delle frecce tutte le celle a cui la formula fa riferimento. Posizionandosi su una cella contenente una costante (valore) e cliccando sul terzo pulsante, Individua dipendenti, se alla costante fa riferimento una formula, la freccia indica la cella contenente la fomula.

Riferimenti Nell’uso corretto di Excel le formule non devono contenere quasi mai i valori, ma i riferimenti ai valori. Si introducano nella cella C1 il valore 10, in C2 il valore 5, infine in C3 il valore 2. Digitando in un’altra cella la formula =C1+C2*C3 si ottiene il valore 20. Questa modalità permette di variare il risultato al variare dei valori nelle celle a cui la cella del risultato fa riferimento. La variazione automatica del risultato avviene se in menu Strumenti, voce Opzioni, scheda Calcolo è abilitata l’opzione Ricalcolo automatico (di default). Altrimenti si può disabilitare il ricalcolo automatico e rendere attivo il Ricalcolo manuale. In questo caso, una volta variati gli input della formula, occorre premere F9 per aggiornare il risultato. In base all’operazione che si desidera eseguire, è possibile scegliere tra i riferimenti relativi, misti o assoluti. Se la cella B1 contiene il valore 10 e la cella A2 contiene la formula =B1, in A2 è riportato il valore 10. Questo è un esempio di riferimento relativo. Quando si trascina o copia una formula che utilizza i riferimenti relativi, i riferimenti della formula trascinata o incollata vengono modificati automaticamente per adeguarsi alla nuova posizione della formula. Se A2 contiene la formula =B1, B1 contiene 10, B2 contiene 15, trascinando o copiando la formula di A2 in A3, questa mostra 15. I riferimenti assoluti invece non vengono adeguati durante il trascinamento o la copia di una formula in una cella differente. Se in A3 è contenuto 10, in C3 5, in C4 2, e in B1 la formula =A3*C3, B1 mostra 50. Trascinando la cella B1 in B2 si ottiene 0, ovvero il risultato della formula =A4*C4. Questo trascinamento rappresenta un errore. Per bloccare il riferimento ad A3 occorre trasformare la formula della cella B1 in =$A$3*C4. Il risultato in B2 sarà così 20. Un riferimento assoluto del tipo =$A$3 impedisce l’adeguamento dei riferimenti sia che si operi un trascinamento verticale che orizzontale. I riferimenti misti consentono di bloccare verticalmente, ma non orizzontalmente, o viceversa. Nella formula =A$3*$C5 con =A$3 si permettono gli adeguamenti orizzontali,


6

cioè il cambiamento di colonna e si bloccano gli spostamenti verticali, cioè i cambi di riga. Il riferimento $C5 opera esattamente il contrario.

Nomi È possibile utilizzare un nome per rappresentare una formula o un valore costante tramite la Casella del nome, a sinistra della Barra della formula. Digitare il nome desiderato e premere INVIO. Se si denominano Bob e Dilan le celle C2 e B4, che contengono i valori 10 e 5, si può ottenere il risultato della somma in A1, digitando in questa cella la formula =Bob+Dilan. Fare uso dei nomi equivale all’utilizzo di un riferimento assoluto. Per rimuovere un nome di cella, ad esempio Dilan, si opera dal menu Inserisci/Nome, voce Definisci nome, dalla quale si seleziona Dilan e poi si clicca sul pulsante Elimina. Un nome, oltre che a una cella può essere attribuito anche a una costante. Un esempio è il calcolo in Euro. Dal menu Inserisci/Nome si sceglie la voce Definisci. Nella casella Nomi della cartella di lavoro: si digita euro e si clicca Aggiungi, mentre nella casella Riferito a: si digita =1936.27 e si conferma con OK. Se ora in una cella qualsiasi si digita =euro, viene restituito 1936.27, se si digita =10*euro si ottiene 19362.7, ecc. La tecnica di attribuire i nomi alle celle può anche riguardare intervalli di celle.

Tridimensionalità La tridimensionalità è una operazione tipica di Excel. Word infatti è un programma bidimensionale: i suoi documenti hanno sempre uno sviluppo orizzontale e verticale. Diversamente Excel si può sviluppare anche in profondità, cioè su più fogli contemporaneamente. Tra le implicazioni della tridimensionalità c’è il consolidamento dei dati. Si nominino 12 fogli con i riferimenti ai mesi dell’anno (Gen, Feb, Mar, …) più un foglio di riepilogo. Ad esempio se di un modello di auto sono stati venduti 10 esemplari a gennaio, 15 a febbraio, 5 a dicembre, si vuole ottenere il totale delle vendite nel foglio riepilogativo. Il numero di vetture è riportato nella cella F10 dei tre fogli mensili. Per ottenere il totale nella cella B3 del foglio riepilogativo, si procede come segue: • posizionarsi nella cella B3 del foglio riepilogativo e digitare =; • attivare il foglio Gen e cliccare sulla cella F10; • premere il tasto + , attivare il foglio Feb e poi la cella F10; • premere il tasto + , attivare il foglio Dic e poi la cella F10; • finiti i fogli dei quali si vogliono sommare i valori, si conferma l’immissione della formula con un INVIO. Il risultato ottenuto nella cella B3, 30, corrisponde alla formula: =Gen!F10+Feb!10+Dic!10. Il punto esclamativo permette a Excel di capire che il nome che lo precede appartiene a un foglio di lavoro. Se si modifica il valore in F10 del foglio Gen, da 10 a 20, il risultato in B3 del foglio riepilogativo diventa 40. La possibilità di creare formule che operano su più fogli prende il nome di Consolidamento tridimensionale dei dati.


7

Riferimenti ad altre cartelle Oltre ai fogli della cartella a cui appartengono, le formule possono fare riferimento ai fogli posti in cartelle diverse. Si hanno in questo caso i Riferimenti esterni. Se si lavora con una cartella posizionata sul Desktop per fare riferimento alla cella D3 del foglio Films di una seconda cartella di lavoro dal nome Elenco.xls, sempre collocata sul Desktop, si digita: =[Elenco.xls]Films’!D3. Dopo aver confermato la formula la sintassi viene adeguata con l’intero percorso del file: ='C:\Documents and Settings\Administrator\Desktop\[Nuovo Foglio di lavoro di Microsoft Excel.xls]Films'!$D$3. Ogni volta che si apre una cartella di lavoro

contenente riferimenti ad altre cartelle, viene chiesto di aggiornare o meno i collegamenti all’altra origine dei dati.

Le funzioni Le funzioni sono formule predefinite che rendono più veloci operazioni ripetitive (es., sommare il contenuto di più celle) o più facili operazioni complesse (es., un’analisi statistica). La sintassi generale delle funzioni è del tipo: =NomeFunzione(Argomenti). Anziché digitare direttamente una funzione in una cella si può ricorrere agli automatismi del comando Inserisci funzione, richiamabile dalla barra degli strumenti Standard. Si tratta di una metodologia non molto efficiente ma che costituisce un potente strumento conoscitivo per esplorare e studiare la notevole quantità di funzioni messe a disposizioni da Excel.

Funzioni di base La funzione SOMMA rappresenta uno degli elementi fondamentali di Excel. Volendo sommare nella cella A6 le prime quattro celle della colonna A, si digita in A6: =SOMMA(A1:A4). Per sommare le prime quattro celle della colonna A e della colonna C si digita: =SOMMA(A1:A4;C1:C4). Per sommare tutti i numeri presenti nelle prime quattro celle delle colonne A, B e C si digita: =SOMMA(A1:C4). La funzione Somma serve anche ad addizionare valori contenuti in fogli diversi. Se dodici fogli sono nominati con i riferimenti dei mesi (Gen, …, Dic) e si volessero sommare i valori contenuti nella cella A1 di tutti i fogli la formula diventa: =SOMMA(Gen:Dic!A1). Analogamente si possono sommare anche intervalli di celle di cartelle diverse. La funzione che segue effettua la somma dell’intervallo di celle A1:C3 locato nel Foglio2 dellla cartella Comuni.xls: =SOMMA([Comuni.xls]Foglio2!$A$1:$C$3). Le funzioni MAX, MIN, MEDIA sono le tre funzioni statistiche più semplici. Le funzioni =MAX(A1:C4), =MIN(A1:C4), MEDIA(A1:C4) restituiscono, rispettivamente, il valore massimo, minimo e medio dei valori contenuti nelle prime quattro celle delle colonne A, B e C. Altre due semplici funzioni statistiche sono MODA, che restituisce il valore più ricorrente di un intervallo di dati, e MEDIANA, che restituisce il numero che occupa la posizione centrale di un insieme di numeri.


8

Funzioni logiche La funzione SE rappresenta uno degli elementi fondamentali di Excel e non se ne può prescindere volendo operare con VBA. Serve a mettere a confronto due elementi, ovvero a eseguire un TEST CONDIZIONALE. Il risultato di un test condizionale è un valore logico VERO oppure FALSO. Ogni test condizionale include almeno un Operatore di confronto. OPERATORI DI CONFRONTO > Diverso < Maggiore o uguale = Minore o uguale

Maggiore Minore Uguale

<> >= <=

La sintassi generale della funzione logica SE è: =SE(Condizione,Vero,Falso). Se si digita in una cella =SE(A1>20000,”Procedere”,”Abbandonare”) nella cella compare la dicitura Procedere se il valore di A1 è superiore a 20000, altrimenti Abbandonare. Anziché restituire del testo si possono all’occorrenza usare numeri o espressioni matematiche, ad esempio: =SE(A1>20000,A1+A1*10%;”Abbandonare”). In questo caso se la condizione imposta è vera, ad esempio il valore è 30000, viene restituito il valore maggiorato del 10%, cioè 33000. Altre funzioni logiche sono utili a sviluppare test condizionali complessi, cioè E, O, NON, quasi sempre abbinate alla funzione SE. La funzione logica E ha la sintassi generale =E(Condizione1,Condizione2), che restituisce: =E(Vero,Vero) =E(Vero,Falso) =E(Falso,Falso)

VERO FALSO FALSO

Se si digita in A3 =E(A1>5,A2>10), la cella restituisce VERO se le celle A1 e A2 contengono valori maggiori di 5 e 10. Se uno dei due numeri risulta minore o uguale al valore con cui si confronta, il risultato diventa FALSO. Le condizioni possono essere incrementate oltre le due dell’esempio. La funzione logica O la sintassi generale =O(Condizione1,Condizione2), che restituisce =O(Vero,Vero) =O(Vero,Falso) =O(Falso,Falso)

VERO VERO FALSO

Se si digita in A3 =O(A1>5,A2>10), la cella restituisce VERO se almeno una delle celle A1 e A2 contiene valori maggiori di quelli di confronto. Con O solo se entrambe le condizioni danno esito negativo si ottiene il responso FALSO. La funzione logica NON nega una condizione. Se si digita =SE(NON(A1=6),”Scarso”,”Bravo”) si chiede alla funzione di restituire il valore Scarso se il valore della cella A1 non è 6. Se per essere promossi in una scuola occorre la media del 6 e un numero di assenze inferiore a 5, per ciascuno studente registrato in un foglio elettronico si può impostare un test condizionale del tipo: =SE(E(C2>=6,D2<5), “Promosso”,”Respinto”)


9

In questo caso la funzione SE deve avvalersi della funzione E perché non riesce a testare più condizioni da sola. L’uso di una o più funzioni all’interno di un’altra funzione corrisponde alla tecnica di Nidificazione. La funzione =SE(A1>80,"Ottimo",SE(E(A1>50,A1<=80),"Medio","Scarso")) permette di esprimere tre giudizi alternativi. La tecnica usata prevede la nidificazione della funzione SE inserendo al suo interno un’altra funzione SE con l’ausilio della funzione E. La procedura è ulteriormente espandibile, per esempio: =SE(A1>80,"Ottimo",SE(E(A1>50,A1<=80),"Medio", SE(E(A1>40,A1<=50),"Scarso", "Insufficiente")))

Il principio resta immutato: all’interno di un argomento, vero o falso, anche di un SE già nidificato, si può porre un’altra funzione. Il limite alle nidificazioni è fissato a 7.

Funzione di ricerca verticale La funzione di ricerca CERCA.VERT ha la seguente sintassi generale: =CERCA.VERT(valore,matrice_tabella,indice,intervallo) L’argomento intervallo è facoltativo e può assumere il valore di VERO o FALSO. L’esempio =CERCA.VERT(“Milano”,A3:E7,4) recita “nella prima colonna della matrice A3:E7, posizionarsi nella cella che contiene Milano, quindi rimanendo sulla stessa riga, spostarsi alla quarta colonna e restituire il valore contenuto in quella cella”. Questa funzione effettua la ricerca del primo argomento sempre nella prima colonna della matrice. Se non viene impostato l’argomento opzionale intervallo, viene assunto per default il valore VERO. Con VERO la funzione ricerca nella prima colonna, iniziando dalla prima cella e procedendo verso il basso, fino a localizzare un valore maggiore di quello specificato. Gli elementi della prima colonna devono quindi essere ordinati in modo crescente. Se l’argomento intervallo è impostato su FALSO non è obbligatorio ordinare la prima colonna perché viene ricercata solo la ricorrenza esatta. CERCA.VERT è la funzione di ricerca più utilizzata, ma risultano utili anche le funzioni INDIRETTO e CONFRONTA.

Funzioni del tempo In Excel le date sono immesse nel formato gg/mm/aaaa, ma il programma assegna a ciascuna data un valore seriale, cioè un numero sequenziale tra 1 (1 gennaio 1900) e 2958465 (31 dicembre 9999). Il formato è comunque modificabile dal menu Formato, voce Celle, scheda Numero, categoria Data. Assegnando valori seriali ai giorni (ma anche alle ore e ai minuti), Excel consente di eseguire formule e funzioni anche complesse. Per ottenere la data che ricorre 200 giorni dopo il 03/09/2000, se questa data è locata in A1, è possibile usare la formula =A1+200 per ottenere 22/03/2001. Calcolare quante settimane intercorrono tra il 01/09/2002 e il 20/10/2002 richiede la formula: =(“20/10/2002”-“01/09/2002”)/7, con la quale si ottiene il risultato di 7 settimane. Lo stesso risultato si ottiene digitando le date in due celle e facendo la sottrazione delle celle. Se sono impostati i settaggi anglo-sassoni le date vanno immesse nel formato mm/gg/aaaa. Calcolare quante ore e minuti sono trascorsi dalle 10.10 alle 16.17 richiede la formula =”16.17-10.10” per ottenere 6.07. Uso avanzato di MS Excel

10

Visual Basic per Applicazioni

Premesse Molti programmatori non considerano l’ipotesi di programmare in BASIC. Il nome stesso (acronimo di Beginner’s All-purpose Symbolic Instruction Code) indica che non è un linguaggio professionale. BASIC venne sviluppato per la prima volta agli inizi degli anni ‘60 come metodologia per insegnare tecniche di programmazione a studenti universitari. Successivamente, BASIC ha preso piede velocemente ed ora è disponibile in svariati linguaggi per molti tipi di computers. All’inizio BASIC era un interprete di linguaggio con delle prestazioni particolarmente lente poichè ogni linea di codice doveva essere interpretata prima di poter essere eseguita. I linguaggi più moderni di BASIC permettono la compilazione del codice, risultando così molto più veloci. BASIC si guadagnò piena stima nel 1991 quando la Microsoft realizzò Visual Basic per Applicazioni (VBA), rendendo particolarmente semplice lo sviluppo di applicazioni per Windows. Visual Basic ha veramente molto poco in comune con le prime versioni di BASIC, nonostante BASIC sia il fondamento sul quale VBA è stato costruito e sviluppato. Con Excel 5, Visual Basic per Applicazioni è stato per la prima volta reso disponibile sul mercato. VBA costituisce il miglior esempio di linguaggio disponibile nelle applicazioni Microsoft e viene oggi incluso in tutte le applicazioni di Office 2002, perfino in applicazioni di altri produttori di software. Il VBA appartiene alla famiglia del linguaggio di programmazione Visual Basic (VB), con cui condivide sintassi e struttura, ma da cui si differenzia per contenere gli oggetti peculiari di una applicazione Windows. Per esempio, il connubio Excel e VBA offre la possibilità di creare potenti strumenti informativi completamente automatizzati utilizzando gli oggetti di un foglio elettronico. Infatti, in Excel esistono numerosi oggetti come fogli di lavoro, grafici, tabelle pivot, scenari, funzioni matematiche, finanziarie, ecc. Progressivamente (a partire da Office 97) il VBA è stato esteso a tutte le applicazioni Microsoft, in modo da diventare un unico linguaggio di programmazione nella specificità dei rispettivi ambienti di lavoro e favorire così la sinergia tra diversi programmi (ad esempio tra Access ed Excel).

Esempi brevi In questo capitolo vengono riportati alcuni brevi esempi sull’uso di VBA. I concetti di base sono qui solo accennati e saranno approfonditi nei capitoli successivi, dove esamineremo i rudimenti della programmazione in VBA. Lo scopo è quindi solo quello di un primo approccio che consenta di vedere alcune funzionalità di Excel abitualmente ignorate dagli utenti.

Esempio 1 – Creare una macro dall’editor VBA L’apprendimento della sintassi di VBA è semplificato dal Registratore di Macro di Excel. Per registrare una macro denominata “Comunicare”, selezionare dal menu Strumenti/Macro/ Macro): Uso avanzato di MS Excel

11

Scrivete “Comunicare” nella finestra Nome macro: e quindi Crea. Si entra nell’ambiente VBA, costituito da una finestra in cui è riportato il codice con le istruzioni: Sub Comunicare() . . . End Sub

Queste istruzioni definiscono una procedura, che include altre istruzioni precedute da un apice (di colore verde). Sono commenti che possono essere cancellati. Nello spazio della Sub (prima di End Sub) si possono scrivere le istruzioni che interessano. Si ritorna al foglio di Excel e si inserisce un’immagine (ad esempio una clipart: Inserisci/Immagine/Clipart). Posizionando il puntatore del mouse sopra l’immagine.e cliccando il tasto destro compare il menu contestuale dal quale si può selezionare Assegna macro. Dalla finestra di dialogo che appare si può selezionare “Comunicare” e cliccare OK. Quando con il puntatore del mouse si passa sopra l’immagine compare una mano e cliccando viene eseguita la macro (ovvero l’insieme delle istruzioni in essa inserite). Fino a questo punto abbiamo creato una macro, chiamata Comunicare, e questa macro (per ora “vuota”) è attivata dal un click sull’immagine posta sul foglio di lavoro.

Esempio 2 – La funzione MsgBox La funzione MsgBox è utile a scrivere frasi. Inserire in una procedura MsgBox "Ciao come stai"

Per esempio: Sub Comunicare() MsgBox "Ciao come stai" End Sub

Eseguendo la macro si visualizza una finestra di messaggio riportante “Ciao come stai”. La funzione MsgBox è fastidiosa da usare quando le frasi sono lunghe. Per mandarle a capo nel punto desiderato si ricorre alla costante vbCr, come nell’esempio seguente:


12

Sub Comunicare() MsgBox "Ciao come stai" & vbCr & _ “Io sto bene” End Sub

Esempio 2 – Personalizzazione della barra del titolo di Excel Inserire in una procedura le istruzioni Application.Caption = "Sistemi colturali"

Per esempio: Sub Caption() Application.Caption = "Sistemi colturali" End Sub

Nella barra del titolo del programma “Microsoft Excel” viene sostituito con “Sistemi colturali”.

Esempio 3 – Individuazione di colonne nascoste Inserire in una procedura le istruzioni If Columns("C:D").Hidden Then Columns("C:D").Hidden = False

Per esempio Sub Colonne_Nascoste() If Columns("C:D").Hidden Then Columns("C:D").Hidden = False End If End Sub

Eseguendo la macro se le colonne C e D sono scoperte non accade nulla. Se sono nascoste vengono mostrate.

Esempio 4 – Eliminazione di un foglio Inserire in una procedura le istruzioni Sheets(2).Delete

Per esempio: Sub Elimina_foglio() Sheets(2).Delete End Sub

Eseguendo questa macro si visualizza una finestra di messaggio riportante “Nei fogli potrebbero esistere dei dati. Per eliminarli in modo permanente scegliere Elimina”. Cliccando Elimina il foglio denominato Foglio2 viene cancellato, cliccando Annulla l’eliminazione non viene eseguita. Se si inserisce nella riga precedente alle istruzioni di eliminazione del foglio la seguente istruzione: Application.DisplayAlerts = False

la finestra di messaggio non viene visualizzata e l’esecuzione della macro determina l’eliminazione immediata di Foglio2. È opportuno ripristinare la visualizzazione dei messaggi alla fine della procedura con la seguente istruzione: Application.DisplayAlerts = True

Il codice completo risulta: Sub Elimina_foglio() Application.DisplayAlerts = False Sheets(2).Delete Application.DisplayAlerts = True End Sub


13

Esempio 5 – Protezione di una macro Chi crea macro può precludere ad altri utenti la visualizzazione del loro contenuto. Dal menu Strumenti scegliere la voce Macro/Editor di Visual Basic. Compare l’editor di VBA e dal suo menu Strumenti selezionare la voce Proprietà di VBAProject. Scegliere la scheda Protezione ove è possibile immettere la password per proteggere l’accesso al codice della macro.

Esempio 6 – Esecuzione automatica di una macro all’apertura Per far eseguire una macro all’apertura della cartella di lavoro si possono usare due metodi: Auto Open e Workbook Open. Nel primo caso il codice deve essere allocato in un modulo. Per esempio si può far comparire il messagio “Ciao!”, con il codice Sub Auto_Open() Msgbox "Ciao!" End Sub

Nel secondo caso il codice deve essere allocato nel workbook. Cliccare due volte “This workbook” dall’editor di VBA, finestra a sinistra denominata Progetto – VBAProject (se non è visibile richiamarla da Visualizza/Gestione progetti). Cliccare il menu in alto denominato (generale) e scegliere Workbook. Sull’editor compare il seguente codice: Private Sub Workbook_Open() End Sub

Immettere l’istruzione MsgBox "Ciao!". Il codice compelto risulta: Private Sub Workbook_Open() MsgBox "Ciao!" End Sub

Esempio 7 – Conteggio del numero di righe, colonne e fogli La seguente macro conta il numero di righe in un intervallo di celle: Sub ContaRighe() Dim ContaRighe As Single Range("E1:E3").Select ContaRighe = Selection.Rows.Count MsgBox (ContaRighe) End Sub

In particolare viene contato il numero delle righe dell’intervallo di celle E1:E3 (cioè 3) e visualizzato in una finestra di messaggio. Sostituendo Rows con Columns viene restituito il numero delle colonne (in questo caso 1): Sub ContaColonne() Dim ContaColonne As Single Range("E1:E3").Select ContaColonne = Selection.Rows.Count MsgBox (ContaColonne) End Sub

Il seguente codice serve a contare il numero di fogli in una cartella: Sub ContaFogli() Dim ContaFogli As Single ContaFogli = Application.Sheets.Count MsgBox (ContaFogli) End Sub


14

Esempio 8 – Copia di dati La copia di dati contenuti in un intervallo di celle può essere fatta con la seguente macro: Sub CopiaRange() Range("A1:A3").Copy Destination:=ActiveCell End Sub

In particolare i dati contenuti nell’intervallo di celle A1:A3 vengono copiati in un intervallo corrispondente che ha inizio dalla cella attiva. L’attivazione di una cella avviene posizionando in quella cella il cursore del mouse o raggiungendola con le frecce direzionali. Se si posiziona il mouse in G1, l’esecuzione della macro copia i valori di A1:A3 in G1:G3. Per copiare i dati nella cella attiva di un altro foglio, per esempio Foglio3, il codice diventa: Sub CopiaRange() Foglio3.Range("A1:A3").Copy Destination:=ActiveCell End Sub

Esempio 9 – Inserimento della data corrente Può essere utile inserire la data corrente in una cartella di lavoro prima di salvarla. Il codice deve essere contenuto in “This workbook”. Cliccare il menu in alto denominato (dichiarazioni) e sceglere BeforeSave. Private Sub Workbook_Open() End Sub

Immettere l’istruzione Range("A1") = Now. Il codice completo risulta:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Range("A1") = Now End Sub

L’esecuzione della macro assegna alla cella A1 la data corrente nel formato gg/mm/aaaa hh:mm AM (o PM).

Esempio 10 – Controllo del contenuto di una cella La macro seguente visualizza una finestra di messaggio che informa se la cella attiva contiene testo, date, formule o è vuota: Sub ControlloContenuto() If Application.IsText(ActiveCell) = True Then MsgBox "Testo" Else If ActiveCell = "" Then MsgBox "Cella vuota" Else End If If ActiveCell.HasFormula Then MsgBox "Formula" Else End If If IsDate(ActiveCell.Value) = True Then MsgBox "Data" Else End If End If End Sub


15

Esempio 11 – Controllo della posizione di una cella La macro seguente visualizza una finestra di messaggio che informa sulla posizione relativa della cella attiva. Ad esempio 6,4 indica che la cella attiva è quella posizionata sulla sesta riga e sulla quarta colonna: Sub Posizione() myRow = ActiveCell.Row myCol = ActiveCell.Column MsgBox myRow & "," & myCol End Sub

Esempio 12 – Eliminazione delle celle vuote La macro seguente elimina le celle vuote contenute in un intervallo di celle di una colonna selezionato dall’utente: Sub CancellaRigheVuote() Rng = Selection.Rows.Count ActiveCell.Offset(0, 0).Select Application.ScreenUpdating = False For i = 1 To Rng If ActiveCell.Value = "" Then Selection.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Next i Application.ScreenUpdating = True End Sub Sostituendo "" con 0 vengono cancellate le celle contenenti 0. La dichiarazione Application.ScreenUpdating = False previene lo schermo

dall’aggiornamento e dal tremolio e assicura l’esecuzione rapida della macro. È sempre opportuno ripristinare il valore True alla fine della procedura. Questa macro utilizza il loop For Next. Le dichiarazioni For Next funzionano da contatore in un intervallo selezionato e causano l’interruzione della macro.

Esempio 13 – Funzioni La creazione di funzioni complesse può essere fatta in modo più semplice utilizzando il codice invece che il foglio elettronico. Nell’esempio riportato viene calcolata una imposta sul reddito usando la dichiarazione Select Case in base al seguente scenario: - i primi 5000 euro non sono tassabili; - i successivi 2500 euro sono tassati al 22%; - ogni importo superiore a 7500 euro è tassato al 25%. Digitare in A1 Reddito e in B1 il relativo ammontare, per esempio 20000. In A2 digitare Imposta e in B2 =tax(B1). Il codice è il seguente: Public Function tax(income As Single) Select Case income Case Is <= 5000 tax = 0 Case Is <= 7500 tax = (7500 - 5000) * 0.22 'in questo caso è 550 Case Else tax = (income - 7500) * 0.25 + 550 End Select End Function

Il risultato in B2 è 3675 euro: (7500–5000)*22%+(20000-7500)*25%. Uso avanzato di MS Excel

16

Le basi di VBA L’elenco che segue è un breve sommario di tutto quello che riguarda VBA: Con VBA compi delle azioni eseguendo il suo codice. Con VBA scrivi (o registri) il codice che viene mantenuto in un modulo VBA. I moduli vengono memorizzati in un libro di lavoro Excel, che puoi vedere o aprire usando il Visual Basic Editor (VBE). Un modulo VBA si compone di procedure. Una procedura è essenzialmente una unità di codice di computer che esegue alcune azioni. Qui c’è un esempio di una semplice procedura Sub chiamata Test che calcola una somma e mostra il risultato in un box di messaggio. Sub End

Test( ) Somma = 1 + 1 MsgBox “La risposta è “ & Somma Sub

In questo caso la risposta è 2. Oltre alle procedure Sub, un modulo VBA può anche avere procedure Function. Una procedura Function restituisce un valore singolo (o un vettore). Una funzione può essere chiamata da un’altra procedura VBA, o usata in una formula di foglio di lavoro. Qui c’è un esempio di una funzione chiamata AddTwo: Function AddTwo(arg1 , arg2) AddTwo = arg1 + arg2 End Function

VBA manipola oggetti contenuti all’interno dell’ambiente in cui si lavora (in questo caso Excel è l’ospite dell’applicazione). Excel offre più di 100 classi di oggetti da manipolare. Esempi di oggetti sono il libro di lavoro, il foglio di lavoro, l’intervallo di fogli di lavoro, un grafico, ecc. Sono disponibili anche altri oggetti usando il codice VBA. Le classi oggetto sono disposte gerarchicamente. Gli oggetti possono agire come contenitori per altri oggetti. Per esempio, Excel è un oggetto chiamato Application e contiene altri oggetti, come un Workbook e CommandBar. L’oggetto Workbook può contenere altri oggetti, come Worksheet e Chart. L’oggetto Worksheet può contenere oggetti quali Range, PivotTable, e così via. La disposizione di questi oggetti è riferita al modello di Excel object. Come gli oggetti formano una collezione. Per esempio, la collezione Worksheets consiste di tutti i fogli di lavoro in un particolare libro di lavoro. La collezione CommandBars comprende tutti gli oggetti CommandBar. Le collezioni sono loro stesse degli oggetti. Quando si fa riferimento ad un contenuto o ad un elemento oggetto, bisogna specificare la posizione nella gerarchia degli oggetti usando un punto come separatore del contenitore dall’elemento. Uso avanzato di MS Excel

17

Per esempio, si può fare riferimento ad un libro di lavoro chiamato Book1.xls come Application.Workbooks(“Book1.xls”)

Questo si riferisce al libro di lavoro Book1.xls nella collezione Workbooks. La collezione Workbooks è contenuta nell’oggetto Excel Application. Estendendo questo ad un altro livello, si può far riferimento a Foglio1 in Book1 come: Application.Workbooks(“Book1.xls”).Worksheets(“Foglio”).Range(“A1”)

Se si omette un riferimento specifico ad un oggetto, Excel usa gli oggetti attivi. Se Book1 è il libro di lavoro attivo, il precedente riferimento può essere semplificato come Worksheets(“Foglio1”).Range(“A1”)

Se Foglio1 è il foglio attivo, si può ulteriormente semplificare il riferimento così: Range(“A1”)

Gli oggetti hanno proprietà Una proprietà può essere considerata come la struttura di un oggetto. Per esempio, un oggetto Range ha proprietà come Value e Name. Un oggetto grafico ha proprietà come HasTitle and Type. Si può usare VBA per determinare e/o cambiare le proprietà di un oggetto. Si assegna una proprietà combinando l’oggetto con la proprietà, separandoli con un punto. Per esempio, si può far riferimento al valore nella cella A1 nel foglio 1 come: Worksheets(“Foglio1”).Range(“A1”).Value

Si possono assegnare valori alle variabili VBA. Ad ogni variabile si può attribuire un nome indicativo del valore della variabile stessa. Per assegnare il valore nella cella A1 nel foglio 1 di una variabile chiamata Interesse, si usa la seguente espressione VBA: Interesse = Worksheets(“Foglio1”).Range(“A1”).Value

Gli oggetti hanno metodi. Un metodo è un’azione che compie l’oggetto. Per esempio, uno dei metodi per l’oggetto Range è ClearContents. Questo metodo cancella il contenuto dell’intervallo. I metodi vengono indicati combinando l’oggetto con il metodo, separandoli con un punto. Per esempio, per cancellare il contenuto della cella A1 nel foglio di lavoro attivo, si usa: Range(“A1”).ClearContents

VBA inoltre include tutte i costrutti dei moderni linguaggi di programmazione per esempio vettori, loop, e così via. Fino qui è stata fornita una descrizione a grandi linee di VBA. Ora è il momento di entrare nel dettaglio.


18

L’editor di Visual Basic In Excel 5 e Excel 95, un modulo VBA appariva come un foglio in un libro di lavoro. Da Excel 97 in poi, i moduli VBA non sono più stati visualizzati come fogli di lavoro e si è passati all’uso di Visual Basic Editor (VBE) per visualizzare e per operare con i moduli VBA. I moduli VBA non sono visibili a meno che VBE non venga attivato. VBE è una applicazione separata ma dipendente da Excel. Non è possibile avviare VBE separatamente da Excel.

Attivazione di VBE Quando si lavora in Excel, si può passare a VBE in uno dei seguenti modi: Premendo Alt+F11. Selezionando Strumenti/Macro/Visual Basic Editor. Ciccando il pulsante Visual Basic Editor ( ), posto nella barra degli strumenti di Visual Basic. Non bisogna confondere Visual Basic Editor e Microsoft Script Editor: sono due cose completamente diverse. Script Editor è usato per compilare script HTML scritti in VBScript o JavaScript. Lo Script Editor non verrà descritto in questa sede. La figura seguente mostra VBE. Probabilmente, la vostra finestra VBE non si presenta esattamente come quella mostrata in figura. Le finestre si possono nascondere, ridurre, ingrandire e riorganizzare.


19

Le finestre VBE VBE è costituito da diversi componenti cui, di seguito, ne viene fornita una breve descrizione. Barra menu La barra del menu di VBE funziona come ogni altra barra di menu. Essa contiene comandi che si usano per lavorare con le varie componenti di VBE. Inoltre esistono delle opzioni veloci per molti comandi del menu VBE. Per esempio, il comando Visualizza/Finestra Immediata ha come via breve Ctrl+G. VBE configura i menu abbreviati. Ciccando con il tasto destro su qualunque cosa nella finestra di VBE si ottiene un menu di abbreviazioni dei comandi più comuni. Barre degli strumenti La barra Standard degli strumenti, che si trova direttamente sotto la barra del menu di default, è una delle sei barre disponibili di VBE (la barra menu è anche considerata una barra degli strumenti). Le barre degli strumenti VBE lavorano come in Excel: si possono usare, muovere, ecc. Si usano i comandi Visualizza/Barra degli strumenti/Personalizza per lavorare con le barre strumenti VBE. Finestra di Gestione progetti La finestra Gestione progetti mostra un diagramma ad albero corrispondente ad ogni libro di lavoro aperto in Excel in quel momento (inclusi quelli aggiunti e i libri di lavoro nascosti). Ogni libro di lavoro è conosciuto come project. Si preme Ctrl+R per rendere visibile una finestra. Per nascondere la finestra si preme il pulsante Chiudi nel titolo della sua barra (o premi col destro in una qualsiasi parte della finestra di Gestione progetti e seleziona Nascondi dal menu di scelta rapida). Finestra codice La finestra codice (anche conosciuta come finestra modulo) contiene il codice VBA. Ogni voce nel progetto ha una sua finestra di codice associata. Per vedere la finestra del codice di un oggetto, si preme due volte l’oggetto nella finestra Gestione progetti. Per esempio per vedere la finestra di codice dell’oggetto Foglio 1, si clicca due volte Foglio1 nella finestra Gestione progetti. Un’altra strada per vedere la finestra di codice di un oggetto è quella di selezionare l’oggetto nella finestra Gestione progetti e premere il tasto Visualizza Codice nella barra strumenti in cima alla finestra Gestione progetti. Finestra immediata La finestra immediata è la più utile per eseguire direttamente espressioni VBA, testarle e controllarne gli errori nel codice. Questa finestra può essere visibile o meno. Se non è immediatamente visibile, si preme Ctrl+G. Per chiudere la finestra si Uso avanzato di MS Excel

20

preme il bottone Chiudi nel titolo della sua barra (o clicca col destro in qualsiasi posto nella finestra immediata e seleziona Nascondi dal menu di scelta rapida).

Gestione progetti Quando si lavora in VBE, ogni libro di lavoro Excel, comprese le componenti aggiuntive in quello che è il lavoro corrente è considerato un progetto. Si può immaginare un progetto come una collezione di oggetti disposti in modo ordinato. Si può espandere un progetto cliccando il segno più (+) alla sinistra del nome del progetto nella finestra di Gestione progetti. Si può ridurre il progetto cliccando il segno meno (-) a sinistra del nome del progetto. Si può anche usare il pulsante Espandi/comprimi cartelle nella barra in cima alla finestra Gestione progetti per espandere o ridurre il progetto. Se si prova a espandere un progetto protetto, viene richiesta la password. La figura seguente mostra una finestra di Gestione progetti. Quando si attiva VBE, non è detto che il codice del modulo che viene mostrato corrisponda all’oggetto evidenziato nella finestra Gestione progetti. Per essere sicuri di lavorare nel codice del modulo giusto, cliccare due volte l’oggetto nella finestra di Gestione progetti.

Se sono aperti molti libri di lavoro, la finestra di Gestione progetti può risultare un po’ schiacciata. Sfortunatamente, non è possibile nascondere il progetto nella finestra di Gestione progetti. Ogni progetto espanso mostra i capitoli di ogni libro di lavoro (ogni foglio è considerato un oggetto), e un altro oggetto chiamato ThisWorkbook (il quale rappresenta l’oggetto ActiveWorkbook). Se il progetto ha qualche modulo VBA, il progetto elencato mostra pure l’elenco dei moduli. Un progetto può anche contenere gli oggetti denominati UserForm (anche conosciuti come finestre di dialogo). Se il progetto ha moduli di classe, mostrerà anche un elenco dei moduli di classe. In Excel 2002, aggiungere un riferimento ad un progetto (usando il comando Strumenti/Riferimenti) causa la comparsa di una voce in Gestione progetti. Ogni riferimento è elencato come oggetto separato.


21

Aggiungere un nuovo modulo VBA Per aggiungere un nuovo modulo VBA ad un progetto selezionare il nome del progetto nella finestra di Gestione progetti e scegliere Inserisci/Modulo. Oppure cliccare col tasto destro sul nome del progetto e scegliere Inserisci/Modulo dal menu di scelta rapida. Quando si registra una macro, Excel automaticamente inserisce un modulo VBA per mantenere il codice registrato.

Rimuovere un modulo VBA Per rimuovere un modulo o un modulo classe da un progetto, selezionare il nome del modulo nella finestra Gestione progetti e scegliere File/Rimuovi xxx (dove xxx è il nome del modulo). È possibile anche cliccare col tasto destro del mouse sul nome del modulo e scegliere Rimuovi xxx dal menu di scelta rapida. Non è possibile rimuovere il codice di moduli associati al libro di lavoro (il codice modulo ThisWorkbook) o al foglio (per esempio il codice modulo Foglio1).

Esportare e importare oggetti Esportare ed importare oggetti può essere utile se si vuole usare un oggetto in un progetto diverso (come ad esempio un modulo VBA o una UserForm). Per esportare un oggetto, selezionare la finestra Gestione progetti e scegliere File/Esporta file (o premere Ctrl+E). Apparirà una finestra di dialogo che chiede il nome del file. Da notare che l’oggetto esportato rimane comunque nel progetto di origine (ne viene esportata solo una copia). Se si esporta un oggetto UserForm, nessun codice associato con UserForm viene esportato. Per importare un file in un progetto si seleziona il nome del progetto nella finestra Gestione progetti e si sceglie File/Importa file. Si ottiene una finestra di dialogo che richiede il nome del file. Si può importare solo un file che è stato esportato usando il comando File/Esporta file. Se si vuole copiare un modulo o un oggetto UserForm in un altro progetto, non è necessario esportare e poi importare l’oggetto. Accertarsi che entrambi i progetti siano aperti e semplicemente attivare le finestre Gestione progetti e trascinare l’oggetto da un progetto all’altro.

Le finestre di codice A ogni oggetto è associata una finestra di codice. Tali oggetti possono essere: Il libro di lavoro stesso (ThisWorkbook nella finestra Gestione progetti) Un foglio di lavoro o un foglio grafico nel libro di lavoro (per esempio, Foglio1 di Grafico1 nella finestra Gestione progetti) Uso avanzato di MS Excel

22

Un modulo VBA Un modulo di classe (uno speciale tipo di modulo che ti permette di creare nuove classi di oggetto) Una UserForm

Minimizzare e massimizzare finestre In certi momenti, VBE può avere molte finestre di codice aperte. La figura che segue mostra un esempio di che cosa si intende.

Le finestre di codice sono molto simili alle finestre del foglio di lavoro in Excel. Si possono minimizzare, massimizzare, nascondere, riarrangiare e così via. Molti utenti trovano più efficiente massimizzare la finestra di codice nella stanno lavorando. Per massimizzare la finestra di codice, cliccare il pulsante Ingrandisci nel titolo della barra o fare un doppio clic nel titolo della barra. Per ripristinare la finestra di codice, cliccare sul tasto Ripristina nel titolo della sua barra. Alle volte, può essere utile avere due o più finestre di codice visibili, ad esempio per comparare il codice di due moduli, o copiare codice da un modulo all’altro. Minimizzando una finestra di codice essa si localizza nella parte bassa dello schermo. Premendo il tasto Chiudi nel titolo della barra della finestra di codice la finestra si chiude completamente. Per aprirla nuovamente cliccare due volte l’oggetto appropriato dalla finestra di Gestione progetti. VBE non permette di chiudere un libro di lavoro. Occorre richiamare Excel e fare la chiusura da qui. Comunque si può usare la Finestra Immediata per chiudere un libro di lavoro o aggiungerne uno. Appena attivata la Finestra Immediata, digitare un’espressione VBA simile a quella di seguito e premere Invio. Uso avanzato di MS Excel

23

Workbooks(“myaddin.xla”).Close

Questa espressione esegue il metodo Chiudi dell’oggetto Workbook, il quale chiude un libro di lavoro. In questo caso, viene chiuso un libro di lavoro che era stato aggiunto.

Mantenimento del codice VBA In generale una finestra di codice può sostenere quattro tipologie di codice: Procedure sub. Una procedura è una struttura di istruzioni che esegue alcune azioni. Procedure funzioni. Una funzione è un set di istruzioni che restituisce un singolo valore o un ordine (concettualmente simile alla funzione di foglio di lavoro come =SUM). Procedure proprietà. Queste sono procedure speciali usate in moduli di classe. Dichiarazioni. Una dichiarazione è un’informazione circa una variabile fornita a VBA. Per esempio, l’utente può dichiarare il tipo di dati per le variabili che intende usare. Un singolo modulo VBA può mantenere qualunque passo di procedura Sub, Function e dichiarazione. Come organizzare un modulo VBA è compito dell’utente. Alcuni utenti preferiscono tenere tutto il loro codice VBA per una applicazione in un singolo modulo VBA; ad altri piace suddividere il codice in numerosi differenti moduli. Sebbene si abbiano molte possibilità su dove posizionare il codice, ci sono alcune restrizioni. Procedure di gestione di eventi devono essere poste nella finestra di codice relativa all’oggetto che risponde all’evento. Per esempio, se si scrive una procedura che viene eseguito nel momento in cui il libro di lavoro viene aperto, questa procedura deve essere posta nella finestra di codice dell’oggetto ThisWorkbook.

Entrando nel codice VBA Prima di poter eseguire qualcosa di veramente significativo va scritta una certa quantità di codice VBA nella finestra di codice. Il codice VBA deve essere dentro una procedura. Una procedura è una dichiarazione VBA. Per ora, focalizzeremo l’attenzione su uno dei tipi di finestre codice: il modulo VBA. Si può aggiungere codice ad un modulo VBA in tre maniere: Entrando nel codice nella vecchia maniera e cioè digitandolo sulla tastiera. Usando il registratore di macro di Excel per registrare le azioni e convertirle nel codice VBA. Copiando il codice da un altro modulo e incollandolo nel modulo in cui si sta lavorando. Entrare manualmente nel codice Alle volte, la via più diretta è la migliore, ovvero digitare il codice usando la tastiera. Si può usare il tasto Tab per fare i rientri nelle righe che logicamente stanno insieme Uso avanzato di MS Excel

24

– per esempio, le condizioni di espressioni fra un If e un End If. Questo non è realmente necessario, ma rende il codice più leggibile. Una singola istruzione in VBA può avere una lunghezza indefinita. Per una maggiore leggibilità, comunque, è possibile spezzare un’istruzione lunga in due o più righe. Per fare ciò, al termine di una linea di istruzioni si aggiunge uno spazio seguito dal carattere underscore (_), poi si preme Invio e si continua l’istruzione nella linea seguente. Il seguente codice, per esempio, è una unica dichiarazione suddivisa in 4 righe: MsgBox “Non trovato “ & Ucase(SHORTCUTMENUFILE) _ & vbCrLf & vbCrLf & “Il file deve essere posizionato in _ “ & ThisWorkbook.Path & vbCrLf & vbCrLf & “Può essere _ necessario reinstallare BudgetMan”, vbCritical, APPNAME

Notare il rientro delle tre linee dell’espressione. È una soluzione opzionale, ma aiuta a capire che queste quattro righe in realtà sono una singola dichiarazione. Come Excel, VBE ha livelli multipli di Annulla e Ripeti. Perciò se si incontra un’istruzione cancellata che invece non andava cancellata, si può premere ripetutamente il tasto Annulla (o Ctrl+Z) fino a che l’istruzione non ritorna. Dopo l’annullamento, si può premere il tasto Ripeti per cambiare quello che era stato annullato. Provare quanto segue: inserire un modulo VBA in un progetto, e poi entrare nella seguente procedura nella finestra di codice del modulo: Sub

End

TuoNome( ) Msg = “Il tuo nome è “ & Application.UserName & “?” Ans = MsgBox(Msg, vbYesNo) If Ans = vbNo Then MsgBox “Oh, mi dispiace.” Else MsgBox “Devo essere telepatico!” End If Sub

La figura seguente mostra come questo appare nel modulo VBA. Entra nel codice si nota che VBE fa alcuni aggiustamento sul testo. Esempio, se si omette lo spazio prima e dopo il segno uguale, VBE lo inserisce. Inoltre, il colore di parte dei testi è cambiato. Questo è normale, e sarà apprezzato più avanti. Per eseguire la procedura TuoNome, accertarsi che il cursore sia posizionato in un qualunque posto all’interno del testo digitato. Poi procedere con una delle seguenti opzioni: Premere F5. Selezionare Esegui dal menu e scegliere il comando Esegui Sub/UserForm. Premere il pulsante Esegui Sub/UserForm ( ) sulla barra degli strumenti standard.


25

La procedura viene eseguita e si può rispondere a una semplice finestra di dialogo (vedi figura seguente) che mostra il nome dell’utente. Notare che Excel viene attivato quando la macro è eseguita.


26

Cosa significa scrivere una procedura Sub in VBA (anche detta macro)? Quando il comando per eseguire la macro viene inviato, VBE velocemente compila il codice e lo esegue. In altre parole, ogni istruzione viene valutata ed Excel svolge ciò che è chiamato a fare. Per registrare questa semplice procedura:

Dichiarare la procedura (prima linea) Assegnare un valore alle variabili (Msg e Ans) Concatenare le stringhe (usando l’operatore &) Usare la funzione built-in di VBA (MsgBox) Usare una costruzione If-Then-Else Concludere la procedura (ultima linea)

Usare il registratore di macro Un’altra modalità per ottenere codice nel modulo VBA è di registrare le azioni usando il registratore di macro Excel. Questo prossimo esempio mostra come registrare una macro che semplicemente cambia la disposizione dell’orientamento della pagina da verticale in orizzontale. 1. Attivare un foglio di lavoro attraverso il libro di lavoro. 2. Selezionare Strumenti/Macro/Registra Nuova Macro. Excel mostra la finestra di dialogo della macro da registrare. 3. Premere OK per accettare i defaults. Excel automaticamente inserisce un nuovo modulo VBA nel progetto. Da questo punto in poi, Excel converte le azioni eseguite in codice VBA. Mentre la macro viene registrata, Excel mostra una piccola finestra fluttuante di barra strumenti che contiene due bottoni della barra degli strumenti (Interrompi registrazione e Riferimento relativo). 4. Selezionare il comando File/Imposta pagina. 5. Selezionare l’opzione Orizzontale e premere OK per chiudere la finestra di dialogo. 6. Premere il tasto Interrompi registrazione sulla finestra fluttuante della barra strumenti (o selezionare Strumenti/Macro/Interrompi registrazione). Excel interrompe la registrazione delle azioni. Per dare uno sguardo alla macro, attivare il VBE (Alt+F11) e trovare il progetto relativo nella finestra Gestione progetti. Cliccare il Modulo1 per mostrare la finestra di codice (se il progetto ha già un Modulo1, la nuova macro sarà Modulo2). Il codice generato da un singolo comando è mostrato nella Lista 1. Per coloro che usano una versione di Excel che non è Excel2002, il codice può variare leggermente.


27

Lista 1 - Macro per variare l’orientamento della pagina in orizzontale Sub Macro1() ' ' Macro1 Macro ' Macro registrata il 18/11/2002 da MAESTRINI ' '

With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.787401575) .RightMargin = Application.InchesToPoints(0.787401575) .TopMargin = Application.InchesToPoints(0.984251969) .BottomMargin = Application.InchesToPoints(0.984251969) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 300 .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub

Sebbene cambi solo un semplice settaggio nella finestra di dialogo dell’impostazione della pagina, Excel genera un codice che riproduce tutti i settaggi nella finestra di dialogo. Questo porta ad un importante concetto. Spesso il codice prodotto quando si registra una macro è eccessivo. Si può ottenere la modalità orizzontale semplificando considerevolmente la macro cancellando codice estraneo. La macro diventa più semplice da leggere e viene eseguita più velocemente. La precedente macro può essere semplificata come segue: Sub Macro1() With ActiveSheet.PageSetup .Orientation = xlLandscape End With End Sub

È stato cancellato tutto il codice in eccesso mediante l’istruzione che setta la proprietà Orientation. In realtà, questa macro può essere semplificata ancora di più Uso avanzato di MS Excel

28

perché la costruzione With-EndWith non è necessaria quando si modifica una sola proprietà: Sub Macro1( ) ActiveSheet.PageSetup.Orientation = xlLandscape End Sub

In questo esempio, la macro cambia la proprietà Orientation dell’oggetto Imposta pagina nel foglio attivo. L’istruzione xlLandscape è una costante che provvede a semplificare le cose. La variabile xlLandscape ha valore 2, e xlPortrait ha valore 1. La seguente macro lavora come la precedente Macro1. Sub Macro1( ) ActiveSheet.PageSetup.Orientation = 2 End Sub

Si può usare l’help in linea per imparare le costanti relative ad un particolare comando. Si può entrare in questa procedura direttamente nel modulo VBA, ma occorre sapere quali oggetti, proprietà e metodi usare. Ovviamente, è molto più veloce registrare macro da cui si impara inoltre che l’oggetto PageSetup ha una proprietà Orientation. Registrare le azioni è la modalità migliore per imparare VBA, sebbene il risultato possa non essere esattamente ciò si desidera. Si può usare l’help in linea per controllare gli oggetti, le proprietà e i metodi che appaiono nel codice registrato.

Copiare il codice VBA Fino ad ora abbiamo esaminato come lavorare direttamente sul codice e registrare le azioni per generare codice VBA. L’ultimo metodo è quello di prendere il codice nel modulo VBA e di copiarlo da un altro modulo. Per esempio, una procedura potrebbe essere già scritta per un progetto e risultare utile anche nel progetto corrente. Piuttosto che rientrare nel codice, si puè più semplicemente aprire il libro di lavoro, attivare il modulo e usare la normale prassi copia – incolla per copiare il codice nel corrente modulo. Dopo averlo incollato, si puoi modificare il codice come si preferisce. Come già detto in questo capitolo, si può anche importare un modulo intero che è stato esportato su un file.

Personalizzare l’ambiente VBE VBE offre numerose opzioni per la personalizzazione dell’ambiente. Quando VBE è attivo, scegliere Strumenti/Opzioni. Si apre una finestra di dialogo con 4 schede: Editor, Formato Editor, Generale, Ancoraggio.

La scheda Editor La figura seguente mostra le opzioni a cui si può accedere cliccando il tab Editor dalla finestra di dialogo Opzioni. Uso avanzato di MS Excel

29

Controllo automatico sintassi L’opzione Controllo automatico sintassi controlla automaticamente errori di sintassi mentre si sta lavorando nel codice VBA. Una finestra di dialogo avverte sul tipo di problema. Se non si sceglie questo settaggio, VBE mostra gli errori di sintassi in un colore differente dal resto del codice e non appare nessuna finestra di dialogo sul monitor. Dichiarazione di variabili obbligatoria Se l’opzione Dichiarazione di variabili obbligatoria è settata, VBE inserisce la seguente dichiarazione all’inizio di ogni nuovo modulo VBA: Option Explicit

Se ciò appare, è obbligatorio definire ogni variabile che viene usata. Questa è una eccellente abitudine da prendere, benchè richieda alcuni sforzi in più da parte dell’utente. Se le variabili non vengono dichiarate, tutti i dati sonodi di tipo Variant, e ciò non è efficiente. Cambiare l’opzione Dichiarazione di variabili obbligatoria riguarda solo moduli nuovi, non quelli già esistenti.

Elenco membri automatico Se questa opzione è settata, VBE offre qualche aiuto quando si entra nel codice VBA, mostrando una lista di capitoli per oggetto. La figura seguente mostra un esempio di Elenco membri automatico. VBE mostra una lista di componenti per l’oggetto Application. È possibile selezionare un Uso avanzato di MS Excel

30

elemento dalla lista ed evitare di scriverlo (questo assicura anche che venga correttamente digitato).

Informazioni rapide automatiche Se questa opzione è settata, VBE informa circa gli argomenti disponibili per funzioni, proprietà e metodi appena vengono digitati. La figura che segue mostra questa caratteristica in azione per la proprietà Range.

Descrizione dati automatica Se l’opzione è settata, VBE mostra il valore della variabile sulla quale il cursore è posto quando si sta lavorando nel codice. Rientro automatico Il settaggio Rientro automatico determina se VBE automaticamente deve far rientrare ogni nuova linea di codice alla stessa maniera come la linea precedente. È possibile specificare il numero di caratteri di rientro (di default sono 4). Uso avanzato di MS Excel

31

Usare la chiave Tab per rientrare nel codice e non la barra spazio. Con Shift+Tab si rimuove il rientro delle linee di codice. Queste chiavi lavorano anche su selezioni di più linee.

Trascinamento della selezione L’opzione Trascinamento della selezione permette di copiare e muovere testo trascinando e rilasciando. Visualizza modulo intero Questa opzione specifica come le procedure vengono mostrate. Se settata, la procedura nella finestra di codice appare come una singola finestra “scorribile”. Se l’opzione è disattivata, si può visualizzare solo una procedura alla volta. Separa routine Quando l’opzione Separa routine è attivata, vengono mostrate linee di separazione alla fine di ogni procedura nella finestra di codice.

La scheda Formato editor La figura seguente mostra l’opzione della scheda Formato editor della finestra di dialogo delle Opzioni.


32

Coloricodice L’opzione Colori codice permette di settare il colore del testo (primo piano e sfondo) e indicare il colore mostrato per vari elementi di codice di VBA. Tipo di carattere L’opzione Tipo di carattere permette di impostare il carattere del testo che si usa nei moduli VBA. Dimensione Specifica la grandezza del carattere nei moduli VBA. Barra indicatori Questa opzione controlla la disposizione del margine verticale dell’indicatore della barra nei moduli.

La scheda Generale La figura seguente mostra le opzioni disponibili sotto lo schema Generale nella finestra di dialogo delle opzioni. Quasi in ogni caso i settaggi di default sono adeguati.

La scheda Ancoraggio La figura seguente mostra la scheda Ancoraggio della finsetra di dialogo delle Opzioni. Queste opzioni determinano il comportamento delle diverse finestre in VBE. Quando una finestra è ancorata, viene fissata lungo uno dei margini della finestra di


33

VBE. Questo facilita l’identificazione e il posizionamento di una particolare finestra. Senza la modalità ancoraggio, si genera una disposizione disordinata delle finestre.

Oggetti e Collezioni In questa sezione sono riportato maggiori dettagli rispetto ai precedenti capitoli relativamente agli oggetti e alle collezioni di oggetti. Gli oggetti vanno immaginati in termini di gerarchia. In cima alla gerarchia c’è l’oggetto Application – nel nostro caso Excel stesso. Ma se si programma in VBA usando Microsoft Word, l’oggetto Application è Word.

L’oggetto gerarchia L’oggetto Application (che è Excel) contiene altri oggetti. Qui ci sono solo pochi esempi di oggetti contenuti nell’oggetto Application: Workbooks (una collezione di tutti gli oggetti Workbook) Windows (una collezione di tutti gli oggetti Window) AddIns (una collezione di tutti gli oggetti AddIn)

Alcuni oggetti contengono altri oggetti. Per esempio, la collezione Workbooks consiste di tutti gli oggetti Workbook aperti, e l’oggetto Workbook contiene altri oggetti, alcuni dei quali sono i seguenti: Worksheets (una collezione di oggetti Worksheet) Charts (una collezione di oggetti Chart) Names (una collezione di oggetti Name) Ognuno di questi oggetti, a sua volta, può contenere altri oggetti. La collezione Worksheets consiste di tutti gli oggetti Worksheet in un Workbook. Un oggetto Worksheet contiene molti altri oggetti, i quali includono i seguenti:


34

ChartObjects (una collezione di oggetto ChartObject) Range PageSetup PivotTables (una collezione di oggetti PivotTable)

Il completo modello oggetto Excel è rappresentato schematicamente nel sistema di help in linea.

Le collezioni Un altro concetto chiave nella programmazione di VBA è rappresentato dalle collezioni. Una collezione è un insieme di oggetti della stessa classe (e una collezione è anch’essa un oggetto). Come scritto sopra, Workbooks è una collezione di tutti gli oggetti Workbook, correntemente aperti. Worksheets è una collezione di tutti gli oggetti Worksheet contenuti in un particolare oggetto Workbook. Si può lavorare con una intera collezione di oggetti o con un oggetto individuale di una collezione. Per fare riferimento ad un singolo oggetto di una collezione, mettere il nome dell’oggetto o il numero di indice fra parentesi dopo il nome della collezione, come segue: Worsheets(“Sheet1”)

Se Sheet1 è il primo foglio di lavoro nella collezione, si può alternativamente usare il seguente riferimento: Worksheets(1)

Si fa riferimento al secondo foglio di lavoro in un Workbook con Worksheet(2), e così via. C’è un’altra collezione chiamata Sheets, che è l’insieme di tutti i fogli in un libro di lavoro, sia fogli di lavoro sia fogli grafici. Se Foglio1 è il primo foglio nel tuo libro di lavoro, si può scrivere come segue: Foglio(1)

Riferimento all’oggetto Quando ci si riferisce ad un oggetto usando VBA, spesso si deve qualificare l’oggetto connettendo i nomi dell’oggetto con un periodo (anche conosciuto come “operatore punto”). Per esempio, si hanno due libri di lavoro aperti ed in entrambi è contenuto un foglio di lavoro chiamato Foglio1. Il singolo foglio si identifica facendo riferimento al contenitore dell’oggetto, come segue: Workbooks(“Book1”).Worksheets(“Foglio1”)

Senza il libro di lavoro qualificatore, VBA vedrebbe Foglio1 nel libro di lavoro attivo. Per far riferimento ad uno specifico intervallo (come può essere la cella A1) su un foglio di lavoro chiamato Foglio1 in un libro di lavoro chiamato Book1, si può usare la seguente espressione: Workbooks(“Book1”).Worksheets(“Foglio1”).Range(“A1”)


35

I riferimenti del precedente esempio includono anche l’oggetto Application, come segue: Application.Workbooks(“Book1”).Worksheets(“Foglio1”).Range(“A1”)

Molte volte, comunque, il riferimento all’oggetto Application può essere omesso (esso è già assunto). Se l’oggetto Book1 è il libro di lavoro attivo, si può anche omettere il riferimento di quell’oggetto e usare: Worksheets(“Foglio1”).Range(“A1”)

Ancora, se Foglio1 è il foglio di lavoro attivo, si può usare una espressione ancora più semplificata: Range(“A1”)

Excel non ha un oggetto che fa riferimento ad una singola cella. Una cella è un oggetto Range. I semplici riferimenti agli oggetti (come in questi esempi) non comportanoo alcuna esecuzione. Per permettere esecuzioni, occorre associare ad un oggetto le sue proprietà, o specificare un metodo che deve essere usato con quell’oggetto.

Proprietà e Metodi In questa sezione descriveremo come accedere alle proprietà e ai metodi degli oggetti.

Proprietà degli oggetti Ogni oggetto ha delle proprietà. Per esempio, un oggetto Range ha una proprietà chiamata Value. Si può scrivere il codice VBA per mostrare la proprietà Value o scrivere un codice VBA per settare la proprietà Value per uno specifico valore. Quella che segue è una procedura che usa la funzione VBA MsgBox per fare apparire una finestra che visualizza il valore nella cella A1 del foglio 1 del libro di lavoro attivo: Sub End

ShowValue( ) MsgBox Worksheets(“Foglio1”).Range(“A1”).Value Sub MsgBox è una funzione utile per mostrare risultati mentre il codice VBA è in esecuzione.

Il codice nel precedente esempio mostra il settaggio corrente della proprietà Value di una cella specifica: cella A1 nel foglio di lavoro chiamato Foglio1 nel libro di lavoro attivo. Nota che se il libro di lavoro attivo non ha un foglio chiamato Foglio1, la macro genererà un errore. La seguente procedura cambia il valore mostrato nella cella A1 cambiando la proprietà Value della cella. Sub

ChangeValue( ) Worksheets(“Foglio1”).Range(“A1”).Value = 123


36

End

Sub

Dopo l’esecuzione di questo processo, la cella A1 sul foglio 1 assume il valore 123. Molti oggetti hanno proprietà di default. Per l’oggetto Range, la proprietà di default è la proprietà Value. Perciò si può anche omette la parte .Value dal codice ed ottenere lo stesso effetto. Comunque, è da considerare buona pratica di programmazione quella di includere la proprietà, anche se è già definita di default.

Metodi degli oggetti Un metodo è una azione che si esegue con un oggetto. Seguono semplici esempi che usano il metodo Clear su un oggetto Range. Dopo aver eseguito la procedura, le celle dell’intervallo A1:C3 sul foglio 1 saranno vuote, e il contenuto delle celle formattate sarà rimosso. Sub ZapRange() Worksheets("Foglio1").Range("A1:C3").Clear End Sub

Se se preferisse cancellare i valori nell’intervallo e mantenere la formattazione, usare il metodo ClearContents dell’oggetto Range. Molti metodi gestiscono argomenti per definire ulteriori azioni. Segue un esempio che copia la cella A1 nella cella B1 usando il metodo Copy dell’oggetto Range. In questo esempio, il metodo Copy ha un argomento (la destinazione della copia): Sub CopyOne() Worksheets("Foglio1").Range("A1").Copy Worksheets("Foglio1").Range("B1") End Sub

Gli oggetti Range Molto del lavoro che viene svolto in VBA coinvolge celle e intervalli in fogli di lavoro. Un oggetto Range è contenuto in un oggetto Worksheet, e consiste di una singola cella o intervallo di celle su un singolo foglio di lavoro. Nella sezione che segue, trattiamo tre modalità per far riferimento agli oggetti Range nel codice VBA: La proprietà Range di un oggetto class Worksheet o Range La proprietà Range di un oggetto Worksheet La proprietà Range di un oggetto Range

La proprietà Range La proprietà Range restituisce l’oggetto Range. Questa proprietà ha due sintassi: object.Range(cell1) object.Range(cell1, cell2)

La proprietà Range si applica per due tipi di oggetti, l’oggetto Worksheet o l’oggetto Range. Qui cell1 e cell2 si riferiscono allo spazio che identifica l’intervallo (una cella nel primo caso, più celle nel secondo caso). Di seguito ci sono alcuni esempi sull’uso del metodo Range. Uso avanzato di MS Excel

37

L’istruzione che segue restituisce un valore in una cella specificata, in questo caso il valore 1 nella cella A1 su Foglio1 del libro di lavoro attivo: Worksheets(“Foglio1”).Range(“A1”).Value = 1

La proprietà Range riconosce anche i nomi definiti in un libro di lavoro. Perciò se una cella è chiamata “Input”, si può usare la seguente espressione per entrare nel valore della cella così denominata: Worksheets(“Foglio1”).Range(“Input”).Value = 1

L’esempio che segue restituisce un medesimo valore in un intervallo di 20 celle sul foglio attivo. Se il foglio attivo non è il foglio di lavoro, questo causa un messaggio di errore: ActiveSheet.Range(“A1:B10”).Value = 2

Il prossimo esempio produce esattamente lo stesso risultato come l’esempio precedente: Range(“A1”, “B10”) = 2

Il foglio di riferimento è tralasciato, così viene preso il foglio attivo. Anche la proprietà valore è tralasciata e lascia il posto alla proprietà di default (nel caso di oggetto Range è Value). Questo esempio usa anche la seconda sintassi per la proprietà Range. Con questa sintassi, il primo argomento è la cella in alto a sinistra dell’ intervallo e il secondo argomento è la cella in basso a destra dell’intervallo. L’esempio seguente usa l’operatore di intersezione di range di Excel (uno spazio) per restituire l’intersezione di due intervalli. In questo caso, l’intersezione è una singola cella, C6. Perciò, l’espressione pone 3 nella cella C6: Range(“C1:C10 A6:E6”) = 3

Ed infine, il prossimo esempio, pone il valore 4 dentro 5 celle, in un intervallo noncontiguo. La virgola serve come operatore di unione. Range (“A1, A3, A5, A7, A9”) = 4

Tutti gli esempi hanno usato la proprietà Range su un oggetto Worksheet. Ti ricordo che puoi anche usare la proprietà Range su un oggetto Range. Quel che segue è un esempio di uso della proprietà Range su oggetto Range (in questo caso, l’oggetto Range è la cella attiva). Questo esempio tratta l’oggetto Range come se fosse la cella superiore a sinistra nel foglio di lavoro, e poi pone il valore 5 nella cella che sarebbe la cella B2. In altre parole, il riferimento restituito è relativo all’angolo superiore sinistro dell’oggetto Range. Perciò, l’espressione che segue pone il valore 5 nella cella direttamente alla destra e una riga sotto la cella attiva: ActiveCell.Range(“B2”) = 5

Ci sono molte modalità per accedere a celle relative a intervalli, chiamate proprietà Offset, di cui si parlerà più avanti.


38

La proprietà celle Un’altra modalità che fa riferimento ad intervalli è quella che fa uso della proprietà Cells. Come la proprietà Range, si può usare la proprietà Cells su oggetti Worksheet e oggetti Range. La proprietà Cells ha tre sintassi: object.Cells(rowIndex, columnIndex) object.Cells(rowIndex) object.Cells

Mostreremo alcuni esempi sull’uso della proprietà Cells. Il primo esempio riceve il valore 9 nella cella 1 sul foglio 1. In questo caso, si usa la prima sintassi, che accetta il numero di indice di riga (da 1 a 65536) e di colonna (da 1 a 256): Worksheets(“Foglio1”).Cells(1, 1) = 9

Segue un esempio che riporta il valore 7 nella cella D3 (che è, riga 3, colonna 4) nel foglio di lavoro attivo: ActiveSheet.Cells(3, 4) = 7

Si può anche usare la proprietà Cells su un oggetto Range. Così facendo, l’oggetto Range restituito dalla proprietà Cells è relativo alla cella in alto a sinistra del riferimento Range. Per esempio, la seguente istruzione registra il valore 5 nella cella attiva. In questo caso la cella attiva è trattata come se fosse la cella A1 nel foglio di lavoro: ActiveCell.Cells(1, 1) = 5

Il reale vantaggio di questo tipo riferimento alle celle sarà apprezzato nella discussione delle variabili e del looping (capitolo successivo). Per registrare il valore 5 nella cella direttamente sotto la cella attiva, si può usare l’istruzione: ActiveCell.Cells(2, 1) = 5

Il precedente esempio recita: “Inizia con la cella attiva e considera questa cella come cella A1. Registra la cella nella seconda riga e nella prima colonna”. La seconda sintassi del metodo Cells usa un singolo argomento con intervallo da 1 a 16777216. Questo numero corrisponde al numero delle celle in un foglio di lavoro (65536 righe per 256 colonne). Le celle sono numerate partendo da A1 e continuando a destra e poi sotto nella riga successiva. La 256ma cella è IV1 e la 257ma è A2. Il prossimo esempio registra il valore 2 nella cella H3 (la quale è la 520ma cella nel foglio di lavoro) del foglio di lavoro attivo: ActiveSheet.Cells(520) = 2

Per mostrare il valore nell’ultima cella del foglio di lavoro (IV65536) usa questa espressione: MsgBox ActiveSheet.Cells(16777216) Uso avanzato di MS Excel

39

La sintassi può essere anche usata con un oggetto Range. In questo caso, la cella restituita è relativa all’oggetto Range restituito. Per esempio, se l’oggetto Range è A1:D10 (40 celle), la proprietà Cells può avere un argomento da 1 a 40 e restituisce una delle celle nell’oggetto Range. Nel seguente esempio, viene registrato il valore 2000 nella cella A2 perché A2 è la quinta cella (contando dall’alto e a destra, poi andando in basso) nell’intervallo di riferimento: Range (“A1:D10”).Cells(5) = 2000

Nel precedente esempio, l’argomento per la proprietà Cells non ha limiti di valori fra 1 e 40. Se l’argomento eccede il numero delle celle nell’intervallo, il calcolo continua come se l’intervallo fosse più largo dell’attuale. Perciò, con una dichiarazione come la precedente si potrebbe cambiare il valore in una cella esterna all’intervallo A1:D10. La terza sintassi per la proprietà Cells restituisce tutte le celle sul foglio di lavoro di riferimento. Al contrario delle altre due sintassi, in questa, i dati non sono restituiti in una singola cella. Questo esempio usa il metodo ClearContents sull’intervallo restituito usando la proprietà Cells su un foglio di lavoro attivo. Il risultato è che i contenuti di ogni cella sul foglio di lavoro vengono cancellati: ActiveSheet.Cells.ClearContents

Cosa sapere degli oggetti Nelle sezioni precedenti sono stati introdotti sommariamente gli oggetti (incluse le collezioni), le proprietà e i metodi. In questa sezione vengono aggiunti altri concetti essenziali:

Concetti essenziali da ricordare Gli oggetti hanno proprietà e metodi unici. Ogni oggetto ha il suo insieme di proprietà e metodi. Alcuni oggetti, come sempre, condividono alcune proprietà (esempio Name) e alcuni metodi (esempio Delete). Si possono manipolare oggetti senza selezionarli. Di fatto è molto più efficiente eseguire azioni su oggetti senza selezionarli prima. Quando si registra una macro, generalmente Excel seleziona il primo oggetto. Questo non è necessario e potrebbe effettivamente far eseguire la macro più lentamente. È importante capire il concetto di collezioni. Molte volte l’utente accede ad un oggetto indirettamente, riferendosi invece alla relativa collezione. Per esempio, per accedere all’oggetto chiamato Myfile di un Workbook, si fa riferimento alla collezione Workbooks come segue: Uso avanzato di MS Excel

40

Workbooks(“Myfile.xls”)

Questa specifica si riferisce ad un oggetto, che è nel libro di lavoro. Proprietà possono restituire un riferimento ad un altro oggetto. Per esempio, nella seguente espressione, la proprietà Font restituisce un oggetto Font contenuto in un oggetto Range: Range(“A1”).Font.Bold = True

Ci possono essere diverse modalità per riferirsi allo stesso oggetto. Si supponga di avere un libro di lavoro chiamato Sales, e non è il solo libro di lavoro aperto. Se questo libro di lavoro ha un foglio di lavoro chiamato Summary, si può fare riferimento al foglio in ciascuna delle seguenti modalità: Workbooks(“Sales.xls”).Worksheets(“Summary”) Workbooks(1).Worksheets(1) Workbooks(1).Foglio(1) Application.ActiveWorkbook.ActiveSheet ActiveWorkbook.ActiveSheet ActiveSheet

Se per esempio ci sono più libri di lavoro aperti, il secondo e il terzo metodo non sono realizzabile. Se si vuole lavorare con il foglio attivo (qualunque esso sia), non sono utilizzabili gli ultimi tre metodi. Per essere assolutamente sicuri di riferirsi ad un determinato foglio in un determinato libro di lavoro, il primo metodo è quello migliore.


41

VBA: Programmazione Fondamentale Questo capitolo riporta alcuni elementi chiave del linguaggio e i concetti della programmazione in VBA.

Elementi di linguaggio VBA: una panoramica VBA ha poche particolarità rispetto ad altri linguaggi. Nel capitolo precedente è stata presentata una panoramica di oggetti, proprietà e metodi. Ma non abbiamo detto molto su come manipolare gli oggetti in maniera che essi facciano qualcosa di specifico. Questo capitolo indirizza in questo senso, esplorando elementi di linguaggio in VBA, parole chiave e strutture di controllo che usati abitualmente per scrivere in VBA. La semplice procedura che segue si trova nel modulo VBA e calcola la somma dei primi 100 interi. Quando eseguita, la procedura mostra una finestra che visualizza il risultato (5050). Sub ‘

End

VBA_Demo ( ) Questo è un semplice esempio VBA Dim Totale As Integer, I As Integer Totale = 0 For i = 1 to 100 Totale = Totale + i Next i MsgBox Totale Sub

Questa procedura utilizza alcuni elementi comuni di linguaggio, includendo un commento (la linea preceduta dall’apostrofo), una variabile (Totale), due espressioni assegnate (Totale = 0 e Totale = Totale + i), una struttura in via di sviluppo (For-Next), e uno stato di VBA (MsgBox). Le procedure VBA non hanno bisogno di manipolare nessun oggetto. La procedura precedente, per esempio, non fa niente con gli oggetti. Lavora solo con i numeri.

Commenti Un commento è un testo descrittivo impresso all’interno del codice. Il testo di un commento è completamente ignorato dal VBA. È buona cosa usare commenti liberamente per descrivere cosa si sta facendo (l’istruzione con cui si intende fare qualcosa non è sempre così ovvia). Si possono utilizzare linee intere per il commento o inserire un commento dopo una istruzione sulla stessa linea. Un commento viene indicato con un apostrofo. VBA non prende in considerazione il testo che segue un apostrofo – eccetto quando l’apostrofo si trova fra virgolette – fino a fine linea. Per esempio, la seguente affermazione non contiene un commento, sebbene contenga un apostrofo: MsgBox = “Non posso continuare”


42

L’esempio seguente mostra una procedura VBA con tre commenti: Sub Commenti ( ) ‘ Questa procedura non restituisce valori x = 0 ‘x rappresenta il nulla ‘ Mostra i risultati MsgBox x End Sub

Sebbene l’apostrofo è da preferire come indicatore, si può anche utilizzare la parola chiave Rem per segnalare una linea di commento. Per esempio: Rem

- -

La dichiarazione seguente richiede all’utente il nome di un file

La parola chiave Rem è essenzialmente un richiamo dalle vecchie versioni di BASIC; essa viene inclusa in VBA grazie alla sua compatibilità. A differenza dell’apostrofo, Rem può essere scritto solo all’inizio della linea, non nella stessa linea di un’altra istruzione. Utilizzare commenti è in definitiva una buona idea, ma non tutti i commenti sono di uguale beneficio. Per essere utili, i commenti devono contenere informazioni che, leggendo il codice, non siano immediatamente ovvi. Altrimenti si sprecano preziosi bytes. La seguente procedura, per esempio, contiene molti commenti, nessuno dei quali realmente aggiunge qualcosa di valore: Sub CattiviCommenti ( ) ‘ Dichiara le variabili Dim x As Integer Dim y As Integer Dim z As Integer ‘ Inizia la routine x = 100 ‘ Assign 100 to x y = 200 ‘ Assign 200 to y ‘ Aggiungi x e y e metti in z z = x + y ‘ Mostra i risultati MsgBox z End Sub

Ispezione del codice VBA Il codice VBA, che si trova in un modulo VBA, contiene istruzioni. È buona pratica usare una istruzione per linea. Questo standard non è comunque richiesto. Si possono usare i due punti per separare istruzioni multiple su una singola linea. L’esempio seguente combina quattro istruzioni su una unica linea: Sub OneLine ( ) x= 1: y= 2: z= 3: MsgBox x + y + z End Sub

Molti programmatori convengono che questo codice sarebbe più semplice da leggere se si utilizzasse una istruzione per linea: Sub OneLine ( ) x= 1 y= 2 z= 3 MsgBox x + y + z End Sub


43

Ogni linea può essere lunga quanto si desidera; la finestra di modulo VBA prosegue a sinistra, fintanto non si raggiunge la parte destra. Per linee lunghe, si puoi usare la sequenza continua di linea di VBA: un trattino in basso (_) preceduto da uno spazio. Per esempio: Sub LineaLunga ( ) ValoreEstivo = Worksheets (“Foglio1”). Range (“A1”). Value + _ Worksheets (“Foglio2”). Range (“A1”). Value End Sub

Quando si registrano delle macro, Excel spesso usa il trattino underscore per spezzare una espressione lunga in linee multiple. Dopo essere entrati nell’istruzione, VBA esegue le seguenti azioni per facilitarne la lettura: Inserisce spazi tra operatori. Se si scrive Ans=1+2 (senza spazi), per esempio, VBA converte in Ans = 1 + 2

VBA ordina il tipo di lettere per parole chiave, proprietà e metodi. Se si registra il seguente testo: Results=activesheet.range (“a1”).value=12

VBA converte in: Results = ActiveSheet.Range (“a1”).Value = 12

Da notare che il testo che si trova tra le virgolette (in questo caso a1) non è cambiato. Perché il nome delle variabili di VBA non sia un elemento suscettibile, l’interprete di default registra i nomi di tutte le variabili con le stesse lettere così che il loro carattere incontra il carattere recentemente digitato. Per esempio se prima si specifica una variabile come myvalue (tutto minuscolo) e poi si accede alla variabile come MyValue (un po’ minuscolo e maiuscolo), VBA cambia tutto in MyValue. Si fa eccezione se si dichiara la variabile con Dim o espressione simile; in questo caso, il nome della variabile apparirà sempre come è stato dichiarato. VBA esamina attentamente l’istruzione per verificare che non ci siano errori di sintassi. Se VBA trova un errore, automaticamente cambia il colore della linea e può visualizzare un messaggio che sta a significare che c’è un problema. Utilizzare il comando VBE Strumenti/Opzioni per visualizzare la casella di dialogo delle opzioni, dove si controlla il colore dell’errore e dove ogni messaggio di errore viene visualizzato (usare l’opzione Controllo automatico sintassi della scheda Editor). Di seguito ci sono pochi generali suggerimenti su come utilizzare al meglio i commenti

Usare i commenti per descrivere brevemente lo scopo di ogni procedura. Usare i commenti per descrivere le modifiche fatte nella procedura. Usare i commenti per indicare l’uso di funzioni non in modalità non standard. Usare i commenti per descrivere lo scopo delle variabili. Usare i commenti per descrivere il lavoro fatto per superare gli errori di Excel. Scrivere subito i commenti sulle specifiche della propria programmazione.


44

Se si vuole testare una procedura che non include particolari istruzioni o gruppi di istruzioni, invece di cancellare l’istruzione, si può ritornare sull’istruzione inserendo un apostrofo all’inizio. VBA allora ignora l’istruzione/i quando la procedura viene eseguita. Per riconvertire il commento in un’istruzione, eliminare l’apostrofo. La barra di apertura degli strumenti di VBE contiene due utili pulsanti. Selezionare un gruppo di istruzioni e poi utilizza il pulsante Commento per convertire le istruzioni in commenti. Il pulsante Rimuovi commento, al contrario, converte un gruppo di commenti in istruzioni.

Variabili, tipi di dati e costanti Scopo principale di VBA è quello di manipolare dati. Alcuni dati si trovano negli oggetti, come ad esempio le pagine di lavoro in successione. Altri dati vengono depositati nelle variabili che create. Una variabile consiste nel denominare un luogo nella memoria del computer ove allocare dati. Le variabili possono accogliere una ampia varietà di tipi di dati – da semplici valori booleani (True or False) a valori a doppia-precisione (vedere sezione seguente). Utilizzando gli stessi segni operatori si assegna un valore ad una variabile (se ne parlerà più avanti). È opportuno assegnare a ogni variabile un nome il più descrittivo possibile. VBA, comunque, ha poche regole riguardanti i nomi delle variabili. Sipossono utilizzare caratteri alfabetici, numeri e alcuni caratteri di punteggiatura, ma il primo carattere deve essere di tipo alfabetico. VBA non fa distinzione fra maiuscolo e minuscolo. Per rendere maggiormente interpretabile una variabile, il programmatore spesso usa caratteri misti (per esempio TassoInteresse piuttosto che Tassointeresse). Non si possono utilizzare spazi o punti. Per rendere più chiare alla lettura le variabili, il programmatore spesso utilizza il carattere “underscore” (Tasso_Interesse). Caratteri speciali come #, $, %, &, or !, non possono essere inclusi nel nome di una variabile. Il nome di una variabile può comprendere fino a 254 caratteri. La seguente lista contiene alcuni esempi di dichiarazioni che usano diverse tipologie di variabili. I nome delle variabili sono alla sinistra dei segni di uguale. Ogni dichiarazione assegna al valore alla destra del segno di uguale la variabile a sinistra. Es.: X = 1 TassoInteresse = 0.075 RataAffitto = 243089 IngressoDati = False X = x + 1 MioNumero = TuoNumero * 1.25 NomeUtente = “Bob Johnson” DataInizio = #3/14/98#


45

VBA ha molte parole riservate (protette), che non si possono usare per nominare variabili o procedure. Se si provi ad usarne una, appare un messaggio di errore. Per esempio, sebbene la parola riservata Next possa costituire un nome di variabile descrittiva, ciò genera un errore di sintassi: Next = 132

Sfortunatamente, il messaggio di errore di sintassi non sempre è chiaro. La precedente istruzione genera questo messaggio di errore: Errore di compilazione: Previsto: identificatore. Sarebbe interessante se nel messaggio di errore ci fosse scitto qualcos’altro tipo Parola riservata assegnata a una variabile. Così se una istruzione produce un messaggio di errore insolito, si può controllare l’help in linea per conoscere se il nome della variabile non abbia un uso speciale in VBA.

Definizione del tipo di dati VBA semplifica la vita ai programmatori perché può automaticamente manipolare tutti i dettagli coinvolti in rapporto con i dati. Non tutti i linguaggi di programmazione fanno questo così facilmente. Per esempio, alcuni linguaggi prevedono una scrittura rigorosa del codice, il che significa che il programmatore deve esplicitamente definire il tipo di dati per ogni variabile usata. Per tipo di dati si intende come i dati vengono immagazzinati nella memoria – come interi, numeri reali, stringhe e così via. Sebbene VBA faccia attenzione ai dati scritti automaticamente, questo ha un costo: lenta esecuzione e bassa efficienza di utilizzo della memoria. Il risultato è che, permettendo VBA la manipolazione del tipo di dati, può presentare problemi qualora si avvii una applicazione lunga e complessa. Un altro vantaggio nell’esplicitare la dichiarazione delle variabili come un particolare tipo di dati è che VBA può eseguire il controllo degli errori in fase di compilazione. Questi errori potrebbero altrimenti essere difficili da individuare.


46

Varie modalità di incorporamento di tipo di dati in VBA Tipo di dati

Integer Long Single

Bytes utilizzati 1 byte 2 byte Vero o Falso 2 bytes 4 bytes 4 bytes

Double

8 bytes

Currency

8 bytes

Decimal

14 bytes

Date Object String (lunghezza variabile) String (lunghezza fissa) Variant (con numeri) Variant (con caratteri) User-defined

8 bytes 4 bytes 10 bytes string length

Byte Boolean

Length string 16 bytes 22 bytes string length Varies

Tipi di dati in VBA Intervallo dei valori Da 0 a 255 Da -32.768 a 32.767 Da -2.147.483.648 a 2.147.483.647 Da -3,402823E38 a – 1,401298E-45 (per valori negativi); da 1,401298E-45 a 3,402823E38 (per valori positivi) Da -1,79769313486232E308 a 4,94065645841247E-324 (per valori negativi); da 4,94065645841247E-324 a 1,79769313486232E308 (per valori positivi) Da -922.337.203.685.477,5808 a 922.337.203.685.477,5807 +/- 79.228.162.514.264.337.593.543.950.335 con nessun punto decimale; +/- 7,9228162514264337593543950335 con 28 decimali Dal 1 Gennaio 0100 al 31 Dicembre 9999 Nessun oggetto di riferimento + Da 0 ad approssimativamente 2 miliardi of Da 1 ad approssimativamente 65.400 Nessun valore numerico al di fuori del range dei dati Double + Da 0 ad approssimativamente 2 miliardi Vario a seconda dell’elemento

I tipi di dati decimali furono introdotti in Excel 2000, e non possono essere usati in versioni precedenti. Questo è un tipo di dato piuttosto anomalo perché attualmente non può essere dichiarato. Infatti, esso è un “sotto-tipo” di Variant. Occorre usare la funzione VBA CDec per convertire un dato di tipo Variant in dato di tipo decimale. Generalmente è meglio usare tipo di dati che utilizzano il minor numero di bytes perché ancora possa manipolare tutti i dati ad esso affidati. Quando VBA lavora con dati, la velocità di esecuzione è in funzione del numero di bytes che ha a sua disposizione. In altre parole, meno sono i bytes utilizzati per i dati, più veloce è VBA nell’accedere ai dati e e manipolarli.


47

Per i fogli di calcolo, Excel utilizza tipi di dati Double, così che double è una buona scelta per operazioni con numeri in VBA per non perdere in precisione. Per calcoli di interi si può usare il tipo di dati Integer, se si è certi che i valori non siano superiori a 32.767, altrimenti usare il tipo Long. Quando si trattano numeri di righe con fogli Excel, va usato il tipo di dati Long perché il numero di righe per pagina di lavoro è superiore al massimo valore assegnato al tipo di dati interi.

Dichiarazione di variabili Se non si dichiari il tipo di dati per una variabile utilizzata in una routine, VBA usa i tipi di dati di default, Variant. I dati archiviati come Variant cambiano tipologia in funzione di come vengono utilizzati. La seguente procedura dimostra come una variabile possa assumere differenti tipi di dato. Sub

VariantDemo ( ) MyVar = “123” MyVar = MyVar / 2 MyVar = “Answer: “ & MyVar MsgBox MyVar End Sub

Nella procedura VariantDemo, MyVar comincia come stringa a tre caratteri. Quindi la “stringa” è divisa in due ed inizia con dati di tipo numerico. Successivamente, MyVar è apposta ad una stringa, convertendo MyVar si ritorna alla stringa. Il MsgBox mostra sul display la stringa finale: Answer: 61.5 Per dimostrare ulteriormente i possibili problemi dati dal tipo di dati variant, proviamo ad eseguire questa procedura: Sub

VariantDemo2 () MyVar = “123” MyVar = MyVar + MyVar MyVar = “Risposta: “ & MyVar MsgBox MyVar End Sub

Il messaggio che verrà visualizzato sarà: “Risposta: 123123”. Quando si tratta con dati Variant che contengono stringhe di testo, l’operatore + esegue una stringa di concatenamento. Determinare il tipo di dati Puoi usare la funzione VBA TypeName per determinate il tipo di dati o di una variabile. Questa è una versione modificata della procedura precedente. Questa versione mostra il tipo di dati di MyVar ad ogni fase. Inizia come stringa, poi è convertita in Double e finisce ancora come stringa.


48

Benchmarking del tipo di dati Variant Testare in ogni caso il tipo di dati è importante. La seguente procedura esegue calcoli privi di significato in un loop e poi mostra il tempo di esecuzione della procedura: Sub TimeTest() Dim x As Integer, y As Integer Dim A As Integer, B As Integer, C As Integer Dim i As Integer, j As Integer Dim TempoInizio As Date, TempoFine As Date 'Avvio TempoInizio = Timer 'Esegui i calcoli x = 0 y = 0 For i = 1 To 5000 For j = 1 To 1000 A = x + y + i B = y - x - i C = x - y - i Next j Next i 'Fine TempoFine = Timer 'Visualizza il tempo totale in secondi MsgBox Format(TempoFine - TempoInizio, "0.0") End Sub

Le dichiarazioni Dim definiscono il tipo di dati. Se si rinuncia alle dichiarazioni circa il tipo di dati usati (Integer, Date) come risultato VBA usa il tipo di dati di default, cioè Variant. La procedura così impiega circa più il doppio del tempo rispetto al precedente. Sub

End

VariantDemo2 ( ) MyVar = “123” MyVar = TypeName (MyVar) MsgBox TypeName (MyVar) MyVar = “Risposta: “ & MyVar MsgBox TypeName (MyVar) MsgBox MyVar Sub

Grazie a VBA, la conversione del tipo di dati di variabili non dichiarate è automatico. Questo processo può sembrare una strada semplice, ma avviene sacrificando velocità e memoria. È buona abitudine dichiarare ogni variabile in una procedura prima che di usarla. Dichiarando una variabile si danno informazioni a VBA circa il suo nome ed il tipo di dato. Dichiarare le variabili offre due principali vantaggi: Il tuo programma partirà più velocemente e userà memoria con più efficienza. Il tipo di dati di default, Variant, causa ripetizione di esecuzione in VBA, sprecando del tempo a controllare e riserva più memoria del necessario. Se VBA conosce il tipo di dati, non ha necessità di controllare e può così riservare abbastanza memoria per immagazzinare dati. Il programmatore evita di coinvolgersi in digitazioni sbagliare del nome di una variabile. Ciò presuppone che l’uso di Option Explicit per forzare la Uso avanzato di MS Excel

49

dichiarazione di tutte le variabili (vedere la prossima sezione). Se, per esempio, una variabile non dichiarata chiamata CurrentRate. ad un certo punto nella procedura viene inserita in modo sbagliato, (ad esempio, CurrentRate = .075), causa un risultato sbagliato. Forzatura a dichiarare tutte le variabili La forzatura a dichiarare tutte le variabili che usate, include, come prima istruzione nel modulo VBA, la seguente: Option Explicit

Questa opzione causa l’arresto del programma ogni volta VBA incontra il nome di una variabile non dichiarata. VBA lancia un messaggio di errore, e occcrre dichiarare la variabile prima di procedere. Per essere sicuri che lo stato Option Explicit sia inserito automaticamente abilitare l’opzione Dichiarazione di variabili obbligatoria nella scheda Editor della finestra di dialogo delle Opzioni di VBA ogni qualvolta si inserisce un nuovo modulo VBA.

Scopo delle variabili Lo scopo delle variabili determina in quale modulo e procedura la variabile può essere usata. Gli scopi delle variabili possono essere i seguenti: Scopo Procedura Single Modulo Single Tutti i moduli

Come una variabile con questo scopo è dichiarata Include un Dim o Static posto all’interno della procedura Include un Dim o Private posto prima della prima procedura in un modulo Include un Public posto prima della prima procedura in un modulo

Questo agomento sarà discusso nelle seguenti sezioni. Una nota circa gli esempi in questo capitolo Questo capitolo contiene molti esempi di codice VBA, normalmente presentati nella forma di semplici procedure. Questi esempi esprimono vari concetti, il più semplicemente possibile. Molti di questi esempi non eseguono nessun particolare utile compito; infatti, questi compiti possono spesso essere dichiarati in altro modo. In altre parole, questi esempi non vanno usati nel lavoro personale. Variabili local Una variabile local è una variabile dichiarata all’interno della procedura. Variabili local possono essere usate solo nelle procedure nelle quali sono dichiarate. Quando la procedura termina, la variabile non permane a lungo, ed Excel la fa uscire dalla memoria.


50

Se si vuole che una variabile conservi il suo valore, dichiararla come Static ( vedi “Variabili Static” più avanti in questa sezione). La modalità più comune per dichiarare una variabile local è di collocare Dim tra Sub ed End Sub. La dichiarazione Dim è normalmente posta a destra dopo la dichiarazione Sub, prima del codice della procedura. Dim è una forma contratta di Dimension. Nella vecchia versione di BASIC, questa dichiarazione era usata esclusivamente per dichiarare le dimensioni per un vettore. In VBA la parola chiave Dim viene usata per dichiarare variabili, non soltanto vettori. La seguente procedura usa sei variabili local dichiarate usando Dim: Sub

End

MySub ( ) Dim x As Integre Dim Primo As Long Dim TassoInteresse As Single Dim DataOggi As Date Dim NameUtente As String * 20 Dim MioValore - [Il codice della procedura va qui] Sub

Notare che l’ultimo Dim nel precedente esempio non dichiara un tipo di dati; è semplicemente il nome della variabile. Come risultato, quella variabile diventa una Variant. Tuttavia, si possono anche dichiarare numerose variabili con un singolo Dim. Per esempio: Dim x As Integer, y As Integer, z As Integer Dim Primo As Long, Ultimo As Double

Un’altra modalità il tipologia di dati Come molti altri linguaggi di BASIC, VBA permette di aggiungere un carattere al nome della variabile per indicare il tipo di dato. Per esempio, si può dichiarare MyVar come intero, ponendo il simbolo % sopra il nome Dim MyVar%

I caratteri di dichiarazione tipo esistono per molti tipi di dati VBA (tipi di dati non in elenco non hanno i caratteri di dichiarazione tipo). Tipi di dati Integer Long Single Double Currency String

Carattere di dichiarazione tipo % & ! # @ $

Questo metodo di tipologia di dati è essenzialmente un rimando dal BASIC; per cui è meglio dichiarare le variabili usando le tecniche descritte in questo capitolo. Uso avanzato di MS Excel

51

A differenza di altri linguaggi, VBA non permette di dichiarare il tipo di dato per un gruppo di variabili separate con una virgola. Per esempio la seguente dichiarazione, benché valida, non indica tutte le variabili come interi: Dim i, j, k As Integer

In VBA, solo k è indicato come un intero: le altre variabili sono dichiarate Variant. Per dichiarare i, j e k come interi, usare la seguente dichiarazione: Dim i As Integer, j As Integer, k As Integer

Se una variabile è dichiarata a scopo locale, altre procedure nello stesso modulo possono usare lo stesso nome di variabile, ma ogni dichiarazione della variabile fa riferimento alla sua propria procedura. In generale, le variabili local sono molto efficienti perché VBA libera memoria che esse usano quando la procedura è conclusa. Variabili modulewide A si vorrebbe che una variabile fosse disponibile per tutte le procedure in un modulo. In tal caso, dichiarare la variabile prima della procedura dell primo modulo (al di fuori di ogni altra procedura o funzione). Nel seguente esempio, la dichiarazione Dim è la prima istruzione nel modulo. Entrambi MySub e YourSub hanno accesso alla variabile CurrentValue. Dim CurrentValue as Integer Sub MySub ( ) 1 - [Il codice va qui] – End Sub Sub YourSub ( ) - [Il codice va qui] – - End Sub

Questo valore di variabile modulewide non cambia quando la procedura finisce. Variabili public Per rendere una variabile disponibile a tutte le procedure in tutti i moduli VBA in un progetto, dichiarare la variabile a livello del modulo usando la parola chiave Public piuttosto che Dim. Qui c’è un esempio: Public TassoCorrente as Long

La parola chiave Public rende la variabile CurrentRate disponibili per qualche procedura nel progetto, perfino quelle in altri moduli all’interno del progetto. Questa dichiarazione va inserita prima della prima procedura in un modulo. Questo tipo di dichiarazione deve anche apparire in un modulo standard VBA, non nel codice di un foglio o di una UserForm. Uso avanzato di MS Excel

52

Variabili static Le variabili static sono un caso particolare. Esse sono dichiarate a livello della procedura, e conservano il loro valore quando la procedura finisce. Si dichiarano variabili static usando la parola Static: Sub

MySub ( ) Static Counter as Integer - [Il codice va qui] End Sub

Lavorare con le costanti Il valore di una variabile può, e spesso lo fa, cambiare mentre una procedura è in esecuzione (è per questo che si chiama variabile). A volte si ha bisogno di riferirsi ad un valore fissato o ad una stringa che non cambi: costante. Dichiarazione di costanti Si possono dichiarare le costanti utilizzando l’espressione Const. Qui di seguito alcuni esempi: Const NumeroQuartieri as Integer = 4 Const Tasso = .0725, Periodo = 12 Const NomeModulo as String = “Budget Macros” Public Const NomeApplicazione as String = “Budget Application”

Il secondo esempio non dichiara un tipo di dato. Conseguentemente, le due costanti sono Variant. Affinché una costante non cambi il suo valore, normalmente si possono dichiarare le costanti come un tipo di dato specifico. Come le variabili, anche le costanti hanno uno scopo. Se si vuole che una costante sia disponibile soltanto all’interno di una procedura, va dichiarata dopo la dichiarazione Sub o Function per farne una costante locale. Per creare una costante disponibile a tutte le procedure in un modulo, dichiararla prima della prima procedura in un modulo. Per creare una costante disponibile per tutti i moduli nel libro di lavoro, usare Public, dichiarare la costante prima della prima procedura in un modulo. Per esempio: Public Const TassoInteresse As Double = 0.0725

Se si tenta di cambiare il valore ad una costante in una procedura VBA, si ottiene un messaggio errore, con la richiesta di un qualcosa che è atteso. Una costante è una costante, non una variabile. Utilizzare costanti in ogni parte del codice al posto di valori di difficile codificazione o stringhe è una eccellente pratica di programmazione. Per esempio, se una procedura ha bisogno di riferirsi ad uno specifico valore, come può essere un tasso di interesse, molte volte è meglio dichiarare il valore come costante e usare il nome della costante piuttosto che il suo valore nelle espressioni. Questa tecnica non solo rende il codice più leggibile, ma anche più facile la modifica del valore se fosse necessario – si cambierebbe solo una istruzione piuttosto che molte.


53

Costanti predefinite Excel e VBA offrono molte costanti predefinite, utilizzabili senza dichiarazioni. Infatti, non è nemmeno richiesto di conoscere il valore di queste costanti. La registrazione della macro generalmente usa costanti piuttosto che valori reali. La seguente procedura usa una costruzione di costante xl.Landscape per settare l’orientamento in orizzontale della pagina attiva. Sub End

SettaInOrizzontale ( ) ActiveSheet.PageSetup.Orientation = xlLandscape Sub

La costante xl.Landscape si può scoprire registrando una macro o tramite l’help in linea. Con l’opzione Elenco membri automatico evidenziata si ottiene spesso assistenza. In molti casi, VBA elenca tutte le costanti che possono essere assegnate ad una proprietà. L’effettivo valore per xl.Landscape è 2. L’altra costante per la modifica dell’orientamento del foglio è xl.Portrait, che ha come valore 1. Ovviamente, usando le costanti, non si ha bisogno di conoscere il loro valore. L’oggetto Browser, di cui abbiamo parlato nel capitolo precedente, contiene un elenco di tutte le costanti Excel e VBA. In VBE, premere F2 per evidenziare l’oggetto Browser. Nomi convenzionali di variabili Alcuni programmatori denominano le variabili così che i loro tipi di dati possono essere definiti appena si leggono i loro nomi. I nomi convenzionali implicano l’utilizzo di un prefisso standard per il nome della variabile. Per esempio, una variabile Boolean che indicase un libro di lavoro è stato salvato, è chiamata bWasSaved. Con ciò, è chiaro che la variabile è una variabile Boolean. La seguente tabella elenca alcuni prefissi standard di tipi di dati: Tipi di dati Prefisso Boolean B Integer I Long L Single S Double D Currency C Date/Time Dt String Str Object Obj Variant V User-defined U

Le stringhe Come Excel, VBA può manipolare sia numeri sia testo (stringhe). Ci sono due tipi di stringhe in VBA: Fixed-length strings sono dichiarate con un numero di caratteri specificato. La massima lunghezza è 65.535 caratteri. Uso avanzato di MS Excel

54

Variable-length strings teoricamente possono sostenere fino a 2 miliardi di caratteri. Ogni carattere in una stringa richiede 1 byte per l’immagazzinamento, e una piccola aggiunta usata per la testata di ogni stringa. Quando si dichiara una stringa variabile con l’espressione Dim, si può specificare la sua lunghezza se si conosce (questa è una stringa di lunghezza fissa: fixed-length-string), o si può lasciare che VBA la manipoli dinamicamente (stringa di lunghezza variabile: variable-length-string). Lavorando con stringhe di lunghezze fisse viene riduce l’efficienza in termini di memoria utilizzata. Nel seguente esempio, la variabile MyString è dichiarata per essere una stringa con una lunghezza massima di 50 caratteri. YourString è anch’essa una stringa, ma la sua lunghezza non viene definita. Dim MyString As String * 50 Dim YourString As String

Le date Naturalmente, si può usare una stringa variabile per conservare una data, ma non si possono eseguire calcoli con date. Il tipo Date è la modalità migliore per lavorare con le date. Una variabile definita come Date utilizza 8 bytes per l’immagazzinamento e può contenere un intervallo di date compreso dal 1 Gennaio A.D. 100 al 31 Dicembre 9999. Questo è circa un periodo di 10.000 anni. Il dato Date è anche utile per il mantenimento di dati legati al tempo. Nel VBA si specificano date e tempo includendoli fra due segni di cancelletto (#), come mostrato di seguito. L’intervallo di date che VBA può gestire è molto più ampio dell’intervallo di date di Excel, il quale inizia con il 1 Gennaio 1900. Perciò, attenzione a non usare una data in un foglio di lavoro al di fuori di un intervallo di date accettabile per Excel. Qui sono alcuni esempi di variabili e costanti dichiarate come date: Dim Oggi As Date Dim DataInizio As Date Const PrimoGiorno As Date = #1/1/2001# Const Mezzogiorno = #12:00:00#

Date costanti sono sempre definite usando il formato mese/giorno/anno, anche se il sistema è settato per mostrare date in differenti formati (per esempio, giorno/mese/anno). Se si usa una finestra di messaggio per mostrare una data, potrebbe essere configurato il sistema breve di formato delle date. Analogamente, il tempo viene visualizzato secondo il formato settato sul computer (12 o 24 ore). Si può modificare questo settaggio usando l’opzione di Impostazioni internazionali nel Pannello di Controllo di Windows.


55

Il Bug della data in Excel È noto che Excel ha un bug legato alla data: erroneamente assume che l’anno 1900 sia bisestile. Sebbene non ci sia il 29 Febbraio 1900, Excel accetta la seguente formula e mostra il risultato come il 29 febbraio 1900: =Date(1900,2,29)

VBA non ha questo bug. L’equivalente VBA della funzione DATE di Excel è DateSerial. La seguente espressione (corretta) restituisce il 1 marzo 1900: DateSerial(1900,2,29)

Quindi la serie dei numeri relativi delle date in Excel non corrisponde esattamente alla serie di numeri di date di VBA. Queste due serie restituiscono valori diversi per le date comprese fra il 1 Gennaio 1900 e il 1 Marzo 1900.

Dichiarazioni di assegnazione La dichiarazioni di assegnazione è una istruzione VBA che fa un controllo matematico e assegna il risultato ad una variabile o ad un oggetto. L’help in linea di Excel definisce una espressione come: combinazione di parole chiave, operatori, variabili e costanti che producono stringhe, numeri e oggetti. Una espressione può eseguire un calcolo, manipolare caratteri o testare dati. Molto del lavoro in VBA implica lo sviluppo di espressioni (e il controllo degli errori). Chi sa come creare formule in Excel non troverà problemi nel creare espressioni in VBA. Con una formula in un foglio di lavoro, Excel mostra il risultato in una cella. Una dichiarazione VBA può essere assegnata ad una variabile o usata come valore di una proprietà. VBA usa il segno di uguale (=) come il suo operatore di assegnazione. Quelli di seguito sono esempi di espressioni di incarico (le espressioni sono a destra del del segno di uguaglianza): x = 1 x = x + 1 x = (y * 2) / (z * 2) FileOpen = True FileOpen = Not FileOpen Range (“Anno”).Value = 2001

Le espressioni possono essere veramente complesse. Si può aver bisogno di usare sequenze continue (spazi seguiti da un underscore) per costruire espressioni lunghe. Spesso, le espressioni usano funzioni. Queste funzioni possono essere le funzioni di VBA, funzioni di fogli di lavoro di Excel, o funzioni che sviluppate in VBA. Discuteremo le funzioni in VBA più avanti in questo capitolo. Gli operatori giocano il ruolo principale in VBA. Operatori conosciuti descrivono operazioni matematiche, includendo somme (+), moltiplicazioni (*), divisioni (/), sottrazioni (-), esponenziali (^) e stringhe di concatenamento (&). Gli operatori meno comuni sono le barre slash (\), usate in divisioni di interi e l’operatore Mod, usato in un modulo aritmetico. L’operatore Mod restituisce il reto di un numero diviso con un altro. Per esempio, l’espressione seguente restituisce 2: 17 Mod 3


56

VBA sostiene pure gli stessi operatori comparativi usati nelle formule Excel: uguali a (=), maggiore di (>), minore di (<), maggiore o uguale a (>=), minore o uguale a (<=), diverso da (<>). Inoltre, VBA offre una serie completa di operatori logici, rappresentati nella tabella seguente. Per i dettagli completi su questi operatori (inclusi gli esempi), usare l’help di VBA. Operatore Not And Or XoR Eqv Imp

VBA: Operatori logici Che cosa fa Esegue la negazione logica di un’espressione Esegue la congiunzione logica di due espressioni Esegue la disgiunzione logica di due espressioni Esegue l’esclusione logica tra due espressioni Esegue un’equivalenza logica tra sue espressioni Esegue un’implicazione logica tra due espressioni

L’ordine di precedenza per operatori in VBA è lo stesso di Excel. Naturalmente, si possono aggiungere parentesi per modfiicare il normale ordine di precedenza. La seguente istruzione utilizza l’operatore Not per visualizzare o meno la griglia delle celle nella finestra attiva. La proprietà DisplayGridlines assume il valore True o False. Quindi, usando l’operatore Not si cambia False in True e True in False. ActiveWindow.DisplayGridlines = _ Not ActiveWindow.DisplayGridlines

La seguente espressione esegue l’operazione logica Or. L’espressione MsgBox mostra True quando uno dei fogli Foglio1 o Foglio2 è la pagina attiva: MsgBox ActiveSheet.Name = “Foglio1” _ Or ActiveSheet.Name = “Foglio2”

Vettori Un vettore è un gruppo di elementi dello stesso tipo che hanno un nome comune. Ci si può riferire a uno specifico elemento nel vettore usando il nome del vettore e un numero di indice. Per esempio, si può definire un vettore di 12 variabili stringa così che ogni variabile corrisponda al nome di un mese. Se si denomina il vettore MonthNames, ci si può riferire al primo elemento del vettore come MonthNames(0), al secondo elemento come MonthNames (1), e così via, fino a MonthNames(11).

Dichiarare vettori Si dichiara un vettore con Dim o Public, proprio come una variabile. Si può anche specificare il numero di elementi nel vettore specificando il primo numero in indice, la parola chiave To, e l’ultimo numero in indice – tutti all’interno di parentesi. Per esempio, ecco come esprimere un vettore compreso esattamente fra 100 interi: Dim MyArray(1 To 100) As Integer


57

Quando si dichiara un vettore, è obbligatorio specificare solo il valore massimo. In tal caso VBA assume 0 come valore minimo. Le seguenti dichiarazioni hanno lo stesso significato: Dim MyArray (0 to 100) As Integer Dim MyArray (100) As Integer

In entrambi i casi, gli schieramenti constano di 101 elementi. Se si preferisce che VBA assuma 1 come valore minimo per tutti i vettori ove si dichiara il solo valore massimo, includere la seguente istruzione prima di qualsiasi procedura in un modulo: Option Base 1

Dichiarare vettori multidimensionali Gli esempi di vettori riportati nella precedente sezione sono di tipo monodimensionale. Invece i vettori avere fino a 60 dimensioni, sebbene è raro usare più di 3 dimensioni (un vettore 3D). La seguente dichiarazione definisce un vettore di 100 interi in due dimensioni (2D): Dim MyArray (1 To 10, 1 To 10) As Integer

Il vettore occupa una matrice 10x10. In riferimento ad uno specifico elemento in un vettore 2D, occorre specificare due indici. Per esempio, ecco come assegnare un valore ad un elemento nel precedente vettore: MyArray (3, 4) = 125

Un vettore 3D somiglia ad un cubo, un vettore multidimensionale non è più immaginabile geometricamente. Uno schieramento dinamico non ha un numero definito di elementi. Si dichiara vettore dinamico con uno spazio bianco fra parentesi: Dim MyArray ( ) As Integer

Prima di poter usare un vettore dinamico nel codice, va usata la dichiarazione ReDim per informare VBA sul numero di elementi nel vettore (o ReDim Preserve per consercare i valori del vettore). Si può usare la dichiarazione ReDim un numero qualsiasi di volte, modificando in tal modo l’ampiezza del vettore tutte le volte che se ne ha bisogno. I vettori appariranno più tardi in questo capitolo, quando si parlerà di looping.

Variabili oggetto Una variabile oggetto rappresenta un oggetto completo, come un intervallo di celle o un foglio di lavoro. Le variabili oggetto sono importanti per due ragioni: Possono significativamente semplificare il codice Possono far eseguire il codice molto velocemente Uso avanzato di MS Excel

58

Le variabili oggetto, come le normali variabili, sono dichiarate con Dim o Public. Per esempio, la seguente istruzione dichiara InputArea come un oggetto Range: Public InputArea As Range

Per vedere come le variabili oggetto semplificano il codice, esaminare la seguente procedura scritta senza l’uso di variabili oggetto: Sub

End

NoObjVar ( ) Worksheets(“Foglio1”).Range(“A1”).Value = 124 Worksheets(“Foglio1”).Range(“A1”).Font.Bold = True WorkSheets(“Foglio1”).Range(“A1”).Font.Italic = True Sub

Questa normale procedura registra un valore (124) nella cella A1 di Foglio1 del libro di lavoro attivo e poi trasforma il contenuto della cella in grassetto e corsivo. Questo è uno dei tanti tipi. Si può condensare il tutto con una variabile oggetto: Sub

End

ObjVar ( ) Dim MyCell As Range Set MyCell = Worksheets (“Foglio1”).Range(“A1”) MyCell.Value = 124 MyCell.Font.Bold = True MyCell.Font.Italic = True Sub

Dopo che la variabile MyCell è dichiarata come un oggetto Range, l’istruzione Set assegna ad esso un oggetto. Le espressioni seguenti possono quindi usare il riferimento più semplice MyCell al posto del riferimento più lungo Worksheets (“Foglio1”).Range(“A1”). Dopo che a un oggetto è stata attribuita una variabile, VBA può accedere alla varaibile più velocemente rispetto alla lentezza che avrebbe un normale riferimento per essere risolto. Così, quando la velocità è critica, usare variabili oggetto. Un modo per saperne di più è nella parola “dot processing”. Ogni volta che VBA incontra un punto come in Foglio(1).Range(“A1”), perde tempo per risolvere il riferimento. Usando una variabile oggetto si riduce il numero di punti che deve essere esaminato. Meno punti, più velocità. Per aumentare ulteriormente la velocità usare la costruzione With-End With, che riduce il numero di punti. Il valore delle variabili oggetto apparirà più chiaro più avanti in questo capitolo quando discuteremo del looping.

Tipi di dati definiti dall’utente VBA permette di creare tipi di dati definti dall’utente (concetto molto simile al registratore di Pascal o alle strutture C). Un tipo di dati definito dall’utente può facilitare il lavoro con alcuni tipi di dati. Per esempio, se una applicazione tratta le


59

informazioni della clientela, si potrebbe creare un tipo di dati definito dall’utente chiamato CustomerInfo, come segue: Type

End

CustomerInfo Company As String * 25 Contact As String * 15 RegionCode As Integer Sales As Long Type

Definire il tipo di dati all’inizio del modulo, prima di ogni procedura. Dopo aver creato un tipo di dati ad definito dall’utente, usare Dim per dichiarare una variabile di questo tipo. Normalmente si definisci un vettore. Per esempio: Dim Customers(1 To 100) As CustomerInfo

Ciascuno dei 100 elementi del seguente vettore consiste di 4 componenti (come specificato dal tipo di dati definito dal’utente, CustomerInfo). Ci si può riferire ad una particolare componente della registrazione come segue: Customers(1).Company = “Acme Tools” Customers(1).Contact = “Tim Robertson” Customers(1).RegionCode = 3 Customers(1).Sales = 150677

Per copiare informazioni da Customers(1) a Customers(2), usare l’istruzione: Customers(2) = Customers(1)

La precedente istruzione equivale al seguente blocco di istruzioni: Customers(2).Company = Customers(1).Company Customers(2).Contact = Customers(1).Contact Customers(2).RegionCode = Customers(1).RegionCode Customers(2).Sales = Customers(1).Sales

Funzioni built-in Come molti linguaggi di programmazione, VBA offre una varietà di funzioni built-in che semplificano calcoli ed operazioni. Spesso, le funzioni abilitano all’esecuzione di operazioni che sarebbero oltremodo difficoltose, o perfino impossibili. Molte delle funzioni VBA sono simili (o identiche) alle funzioni dei fogli di lavoro Excel. Per esempio, la funzione VBA Ucase, che converte una stringa di argomenti in carattere maiuscolo è equivalente alla funzione del foglio di lavoro Excel UPPER. Per ottenere una lista delle funzioni mentre si sta scrivendo il codice, digitare VBA seguito da un punto (.). VBE mostra una lista di tutti i suoi componenti, includendo le funzioni (figura seguente). Le funzioni sono precedute da una icona verde. Se la tecnica non Uso avanzato di MS Excel

60

funziona, assicurarsi che l’opzione Elenco membri automatico sia attiva. Scegliere Strumenti/Opzioni e cliccare nella scheda Editor.

Per lo più le funzioni si utilizzano nelle espressioni VBA allo stesso modo in cui si utilizzano nelle formule dei fogli di lavoro. Quella che segue è una semplice procedura che calcola la radice quadrata di 25 usando la funzione VBA Sqr, immagazzina il risultato in un’altra variabile, poi mostra il risultato: Sub

End

ShowRoot ( ) MyValue = 25 RadiceQuadrata = Sqr(MyValue) MsgBox RadiceQuadrata Sub

La funzione di VBA Sqr è equivalente alla funzione di foglio di lavoro =SQRT. Si possono usare molte (ma non tutte) le funzioni dei fogli di lavoro Excel nel codice VBA. L’oggetto WorksheetFunction, contenuto nell’oggetto Application, sostiene tutte le funzioni dei fogli di lavoro che si possono chiamare dalla procedura VBA. Per usare una funzione dei fogli di lavoro in una espressione VBA, precedere il nome della funzione con: Application.WorksheetFunction

L’esempio seguente mostra come usare una funzione del foglio di lavoro Excel in una procedura VBA. La funzione ROMAN di Excel, usata raramente, converte un numero arabo in un numero romano:


61

Sub

End

ShowRoman() ValoreDecimale= 2001 ValoreRomano = Application.WorksheetFunction.Roman(DecValue) MsgBox ValoreRomano Sub


62

La funzione MsgBox La funzione MsgBox è una delle funzioni più utili. Molti degli esempi in questo capitolo usano questa funzione per presentare il valore di una variabile. Questa funzione spesso è un buon sostituto di una normale finestra di dialogo. È anche un eccellente strumento di controllo degli errori perché la funzione MsgBox può essere inserita in ogni punto del codice e mostrare il risultato del calcolo o di assegnazione. Molte funzioni restituiscono un valore singolo, il quale può essere assegnato ad una variabile. La funzione MsgBox non solo restituisce un valore, ma mostra anche una finestra di dialogo a cui l’utente può rispondere. Il valore restituito dalla funzione MsgBox rappresenta la risposta dell’utente al dialogo. Si può usare la funzione MsgBox anche quando non si è interessati alle risposte dell’utente ma si vuole trarre vantaggio dal messaggio mostrato. La sintassi ufficiale della funzione MsgBox ha cinque argomenti (quelli nelle parentesi quadre sono opzionali): MsgBox(promtp[, buttons][, title][, helpfile, context])

prompt – (Richiesto) Il messaggio mostrato nella finestra del messaggio. buttons – (Opzionale) Un valore che specifica quali bottoni e icone fare apparire nella finestra del messaggio. Usare costanti built-in – per esempio, vbYesNo. title – (Opzionale) Il testo che appare nella barra del titolo della finestra del messaggio. Di default è Microsoft Excel helpfile – (Opzionale) Il nome del file di help associato alla finestra del messaggio context – (Opzionale) Il contesto ID dell’help topico. Questo rappresenta un particolare help topico da mostrare. Si puuò assegnare il valore restituito ad una variabile, o si può usare la funzione per se stessa senza una dichiarazione di assegnazione. Il prossimo esempio assegna il risultato della variabile Ans. Ans = MsgBox("Continuo?", vbYesNo + vbQuestion, "Dimmelo") If Ans = vbNo Then Exit Sub

Notare che è stata usata la somma di due costanti built-in (vbYesNo + vbQuestion) per l’argomento tasti. Usando vbYesNo mostra due tasti nella finestra di dialogo: uno etichettato Yes e l’altro etichettato No. Aggiungendo vbQuestion all’argomento inoltre si mostra di icona di domanda (vedere la figura accompagnata). Quando la prima espressione è eseguita, Ans contiene uno dei due valori, rappresentato dalle costanti vbYes o vbNo. In questo esempio, se l’utente clicca sul pulsante No, la procedura si conclude.


63

Per maggiori informazioni, consultare l’help in linea, che elenca le costanti utilizzabili. Quando si esegue questa procedura, la funzione MsgBox mostra la stringa MMI. Excel non ha una funzione per convertire un numero romano nel suo equivalente decimale. È importante capire che non si possono usare le funzioni dei fogli di lavoro che hanno una funzione equivalente in VBA. Per esempio, VBA non può accedere alla funzione della pagina di lavoro di Excel SQRT, perché VBA ha una sua versione di questa funzione; Sqr. Perciò, la seguente espressione genera un errore: MsgBox Application.WorksheetFunction.Sqrt(123)

‘error

Manipolare oggetti e collezioni VBA offre due importanti costrutti che possono semplificare il lavoro con oggetti e collezioni: Istruzioni With-End With Istruzioni For Each-Next

Costruzioni With-End With Le istruzioni With-End With agevola l’esecuzione di operazioni multiple su un singolo oggetto. Per iniziare a capire come le istruzioni With-End With lavorino, esaminare la procedura seguente, che modifica cinque proprietà del formato di una selezione (la selezione si assume essere un oggetto Range): Sub

End

ChangeFont1 ( ) Selection.Font.Name = “Time New Roman” Selection.Font.FontStyle = “Bold Italic” Selection.Font.Size = 12 Selection.Font.Underline = xlUnderlineStyleSingle Selection.Font.ColorIndex = 5 Sub

Questa procedura può essere riscritta usando la costruzione With-End With. Il seguente processo funziona esattamente come il precedente: Sub

End

ChangeFont2() With Selection.Font .Name = “Times New Roman” .FontStyle = “Bold Italic” .Size = 12 .Underline = xlUnderlineStyleSingle .ColorIndex = 5 End With Sub


64

Alcuni utenti ritnegono che la seconda esecuzione del processo sia più difficile da leggere. L’esecuzione è comunque più rapida. Sebbene la prima versione possa essere più semplice e più facile da capire, una procedura che usi le istruzioni WithEnd With, quando cambiano molte proprietà di un oggetto può essere significativamente più veloce rispetto all’equivalente procedura che esplicita i riferimenti dell’oggetto in ogni istruzione. Qualora si registri una macro VBA, Excel usa le istruzioni With-End With in ogni opportunità. Per vedere un buon esempio, provare a registrare l’azione di modifica dell’impostazione di pagina, scegliendo i comandi File/Impostazione pagina.

Istruzioni Each-Next Si fa richiamo al precedente capitolo che una collection è un gruppo di oggetti relazionati. Per esempio, la collezione Workbooks è una collezione di tutti gli oggetti Workbook aperti. Ci sono molte altre collezioni con cui si può lavorare. Supponiamo che si vogliano eseguire alcune azioni su tutti gli oggetti di una collezione. O supponiamo che si voglia valutare tutti gli oggetti di una collezione ed eseguire un’azione in certe condizioni. Queste sono occasioni perfette per l’uso delle istruzioni For Each-Next. La sintassi della costruzione For Each-Next è: For

Next

Each element In group [instructions] [Exit For] [instructions] [element]

La procedura seguente usa la costruzione For Each-Next per riferirsi ad ognuno dei singoli membri di un vettore di lunghezza fissa, uno alla volta: Sub

End

Macro1( ) Dim MyArray (5) For i = 0 To 5 MyArray(i) = Rnd Next i For Each n In MyArray Debug.Pring n Next n Sub

La prossima procedura usa la costruzione For Each-Next con la collezione Sheets nel foglio di lavoro attivo. Quando fai partire il processo, la funzione MsgBox mostra ogni proprietà Name del foglio di lavoro (se ci sono cinque fogli di lavoro nel libro attivo, la funzione MsgBox viene chiamata cinque volte). Sub

End

ContaFogli( ) Dim Item as WorkSheet For Each Item In ActiveWorkbook.WorkSheets MsgBox Item.Name Next Item Sub


65

Nel precedente esempio, Item è una variabile oggetto (più specificamente, un oggetto Worksheet). Non c’è niente di speciale nel termine Item; e si può usare invece qualunque altro termine valido. Il prossimo esempio usa For Each-Next per passare attraverso tutti gli oggetti nella collezione di Windows: Sub

End

HiddenWindows() Dim TuttiVisibili As Boolean Dim Item As Window AllVisible = True For Each Item In Windows If Item.Visible = False Then TuttiVisibili = False Exit For End If Next Item MsgBox TuttiVisibili Sub

Se un finestra è nascosta, il valore di TuttiVisibili è modificato da True in False, e la procedura esce dal loop For Each-Next. La finestra di messaggio mostra True se tutte le finestre sono visibili e False se almeno una finestra è nascosta. L’espressione Exit For è opzionale. Essa offre una modalità per uscire velocemente dal loop For Each-Next. Questo è generalmente usato in congiunzione con un’espressione If-Then (descritta più avanti in questo capitolo). Segue un esempio che chiude tutti i libri di lavoro eccetto quello attivo. Questa sequenza usano le istruzioni If-Then per valutare ogni lavoro nella collezione Workbooks. Sub

End

CloseInActive( ) Dim Libro as Workbook For Each Libro in Workbooks If Libro.Name <> ActiveWorkbook.Name then Libro.Close Next Libro Sub

L’esempio finale For Each-Next è designato per essere eseguito dopo che l’utente ha selezionato un intervallo di celle. Qui l’oggetto Selection agisce come una collezione costituita da oggetti Range perché ogni cella nella selezione è un oggetto Range. La procedura controlla ogni cella e usa la funzione UCase di VBA per convertire il suo contenuto in caratteri maiuscoli (non riguarda valori numerici). Sub MakeUpperCase() Dim Cella As Range For Each Cella In Selection Cella.Value = UCase(Cella.Value) Next Cella End Sub


66

Controllo dell’esecuzione Alcune procedure partono dall’alto e progressivamente, linea dopo linea vanno verso il basso. Le macro che registrate, per esempio, lavorano in questo modo. Spesso si ha la necessità di controllare il flusso dei calcoli della routine ignorando alcune dichiarazioni, eseguendo alcune dichiarazioni multiple, testando condizioni per determinare il prosieguo della procedura. La precedente sezione descriveva la costruzione For Each-Next, che altro non è che un tipo di loop. Questa sezione discute un altro modo di controllare l’esecuzione del processo VBA.

GoTo If-Then Select Case For-Next Do While Do Until

dichiarazioni istruzioni istruzioni loop loop loop

Dichiarazione GoTo Il modo più semplice per modificare l’esecuzione del programma è quella di utilizzare l’espressione GoTo. Questa dichiarazione trasferisce l’esecuzione di un programma ad una nuova istruzione, la quale deve essere preceduta da una etichetta (una stringa di testo seguita dai due punti, o un numero senza i due punti). La procedura VBA può contenere un qualunque numero di etichette, e un’espressione GoTo non può ramificarsi al di fuori della procedura. La seguente procedura, usata dalla funzione VBA InputBox, prende il nome da chi la usa. Se il nome non è Howard, la procedura si ramifica all’etichetta WrongName e termina. Altrimenti la procedura esegue del codice addizionali. L’espressione Exit Sub causa la chiusura del processo. Sub GoToDemo() UserName = InputBox("Digita il tuo nome:") If UserName <> "Howard" Then GoTo WrongName MsgBox ("Benvenuto Howard!") ' -[Più codice qui] Exit Sub WrongName: MsgBox "Mi dispiace. Solo Howard può eseguire questa procedura." End Sub

Questa semplice procedura funziona, ma in generale l’istruzione GoTo andrebbe usata solo quando non c’è un’altra modalità disponibile. Infatti, le uniche volte in cui è realmente necessaria questa espressione è sicuramente per la cattura di un errore.

Istruzioni If-Then Forse il più comune gruppo di istruzioni utilizzato in VBA è If-Then. Queste istruzioni dotano l’applicazione della capacità di prendere decisioni. Prendere una buona decisione è la chiave per scrivere programmi di successo. Una applicazione Excel di successo essenzialmente prende decisioni ed agisce su di esse. Uso avanzato di MS Excel

67

La sintassi di base della costruzione If-Then è: If condition Then true_instructions [Else false_instructions]

L’istruzione If-Then è usata per eseguire una o più dichiarazioni in modo condizionale. La clausula Else è opzionale. Se inclusa, permette di eseguire una o più istruzioni quando la condizione che si sta testando non è vera. La seguente procedura mostra una struttura If-Then senza la clausola Else. L’esempio funziona con il tempo. VBA usa una serie di numeri relativi a date similmente a Excel. Il tempo in giorni è espresso come un valore frazionario – per esempio, mezzogiorno è rappresentato da .5. La funzione Time di VBA restituisce un valore che rappresenta il tempo in giorni, come riportato dal sistema dell’orologio. Nell’esempio seguente, viene visualizzato un messaggio se l’orario è prima di mezzogiorno. Se l’orario corrente è maggiore o uguale a .5, il processo si conclude e non succede nulla. Sub End

GreetMe1( ) If Time < 0.5 Then MsgBox “Buon giorno” Sub

Se si vuole mostrare un saluto diverso quando l’ora del giorno è posteriore a mezzogiorno, aggiungere un’altra espressione If-Then, come questa: Sub End

GreetMe2( ) If Time < 0.5 Then MsgBox “Buon giorno” If Time >= 0.5 Then MsgBox “Buon pomeriggio” Sub

L’operatore >= (maggiore o uguale a) per la seconda espressione If-Then. Questo copre la possibilià estremamente remota che l’ora sia precisamente pari alle 12:00. Un altro approccio è utilizzare la clausola Else nelle istruzioni If-Then. Per esempio: Sub

GreetMe3( ) If Time < 0.5 Then MsgBox “Buon giorno” Else _ MsgBox “Buon pomeriggio” End Sub

La sequenza di istruzioni usata; If-Then-Else è in effetti una dichiarazione singola. Se la necessità è di espandere lo sviluppo del codice per gestire tre condizioni (per esempio, mattino, pomeriggio e sera) si può usare per ciascuna tre espressioni IfThen o nidificare la struttura If-Then-Else. Il primo approccio è il più semplice: Sub GreetMe4() If Time < 0.5 Then MsgBox "Buon giorno" If Time >= 0.5 And Time < 0.75 Then MsgBox "Buon pomeriggio" If Time >= 0.75 Then MsgBox "Buona sera" End Sub

Il valore 0.75 rappresenta le 6:00 p.m. – tre quarti di giorno passati e quindi è sera. Nei precedenti esempi, ogni istruzione nella procedura viene eseguita anche al mattino. Una procedura più efficiente includerebbe una struttura che termini la sua esecuzione quando la condizione trovata è vera. Per esempio, può apparire il messaggio Buon giorno al mattino per poi uscire senza calcolare le altre che sono condizioni superflue. Con una piccola procedura come questa la differenza in Uso avanzato di MS Excel

68

velocità è irrilevante. Ma per applicazioni più complesse, è opportuna un’altra sintassi: If

condition Then [true_instructions] [Elself condition-n Then [alternate_instructions]] [Else [default_instructions]] End If

Qui si incontra la sintassi per riscrivere la procedura GreetMe: Sub

End

GreetMe5() If Time < 0.5 Then MsgBox “Buon giorno” ElseIf Time >= 0.5 And Time < 0.75 Then Msg Box “Buon pomeriggip” ElseIf Time >= 0.75 Then MsgBox “Buona sera” End If Sub

Con questa sintassi, quando una condizione è vera, le espressioni condizionali sono eseguite e la costruzione If-Then termina. In altre parole, le condizione estranee non sono valutate. Sebbene questa sintassi risulti enormemente efficiente, alcuni possono trovare il codice più difficile da capire. La seguente procedura mostra tuttavia un'altra modalità per codificare questo esempio. Viene usata la costruzione nidificata If-Then-Else (senza l’uso di ElseIf). Questa procedura è efficiente e di facile apprendimento. Notare che ciascuna istruzione If ha una espressione corrispondente End If.


69

Sub GreetMe6() If Time < 0.5 Then MsgBox "Buon giorno" Else If Time >= 0.5 And Time < 0.75 Then MsgBox "Buon pomeriggio" Else If Time >= 0.75 Then MsgBox "Buona sera" End If End If End If End Sub

Il seguente è un altro esempio che usa le istruzioni If-Then. Questa procedura richiede all’utente un valore per Quantità e poi mostra uno sconto basato su quel valore. Se InputBox è annullato, Quantità contiene una stringa vuota e la procedura si conclude. Notare che questa procedura non esegue nessun altro controllo di errore. Per esempio, non è garantito che la quantita digitata sia un valore numerico non negativo. Sub Sconto1() Quantità = InputBox("Digita una quantità: ") If Quantità = "" Then Exit Sub If Quantità >= 0 Then Sconto = 0.1 If Quantità >= 25 Then Sconto = 0.15 If Quantità >= 50 Then Sconto = 0.2 If Quantità >= 75 Then Sconto = 0.25 MsgBox "Sconto: " & Sconto End Sub

Notare che ogni istruzione If-Then nella procedura è sempre eseguita e il valore per Sconto può cambiare. Questo valore finale, comunque, è il valore desiderato. La seguente procedura è sostitutiva della precedente (cambia la sintassi). In questo caso, la procedura termina dopo l’esecuzione del blocco di istruzioni True. Sub Sconto2() Quantità = InputBox("Digita una quantità: ") If Quantità = "" Then Exit Sub If Quantità >= 0 And Quantità < 25 Then Sconto = 0.1 ElseIf Quantità < 50 Then Sconto = 0.15 ElseIf Sconto < 75 Then Sconto = 0.2 ElseIf Quantità >= 75 Then Sconto = 0.25 End If MsgBox "Sconto: " & Sconto End Sub

Le istruzioni If-Then nidifcate risultano ingombranti ed è opportuno usarle solo per decisioni binarie semplici. Quando c’è bisogno di scegliere fra tre o più alternative, la struttura Select Case è spesso la migliore istruzione da usare.


70

Istruzione Select Case L’istruzione Select Case è utile per scegliere tra tre o più opzioni. Questa istruzione inoltre funziona con due opzioni ed è una buona alternativa a If-Then-Else. La sintassi di Select Case è la seguente: Select Case testexpression [Case expressionlist-n [instructions-n]] [Case Else [default_instructions]] End Select

La funzione IIf di VBA VBA offre una alternativa alla costruzione If-Then: la funzione IIf. Questa funzione richiede tre argomenti, e funziona in modo analogo alla funzione del foglio di lavoro Excel SE. La sintassi è: IIf(expr. truepart, falsepart)

expr (richiesta) – Espressione da testare truepart (richiesta) – Valore o espressione restituita se expr è True. falsepart (richiesta) – Valore o espressione restituita se expr è False. La seguente istruzione mostra l’uso della funzione IIf. La finestra di messaggio mostra Zero se la cella A1 contiene uno zero o è vuota, e mostra Nonzero se la cella A1 contiene qualcos’altro. MsgBox IIf(Range(“A1) = 0, “Zero”, “Nonzero”)

È importante capire che il terzo argomento (falsepart) è sempre testato anche se il secondo argomento (truepart) è vero. Perciò la seguente espressione genererà un errore se il valore di n è zero: MsgBox IIf(n = 0, 0, 1 / n)

L’esempio che segue di una istruzione Select Case mostra un’altra modalità per codificare l’esempio GreetMe presentato nella sezione precedente: Sub GreetMe() Select Case Time Case Is < 0.5 Msg = "Buon giorno" Case 0.5 To 0.75 Msg = "Buon pomeriggio" Case Else Msg = "Buona sera" End Select MsgBox Msg End Sub

E di seguito viene riscritta una versione dell’esempio Sconto, usando l’istruzione Select Case. Questa procedura assume che Quantità sarà sempre un valore intero. Per semplificare, la procedura non esegue controlli di errore. Uso avanzato di MS Excel

71

Sub Sconto3() Quantità = InputBox("Digita quantità: ") Select Case Quantità Case "" Exit Sub Case 0 To 24 Sconto = 0.1 Case 25 To 49 Sconto = 0.15 Case 50 To 764 Sconto = 0.2 Case Is >= 75 Sconto = 0.25 End Select MsgBox "Sconto: " & Sconto End Sub

L’istruzione Case può anche usare l’operatore Or. Il seguente processo usa la funzione di VBA WeekDay per determinare se il giorno corrente è un giorno di weekend; poi mostra un messaggio appropriato. Notare l’uso dell’operatore Or, il quale controlla per un sabato o una domenica: Sub

End

GreetUser() Select Case WeekDay(Now) Case 1 Or 7 MsgBox “Siamo nel weekend” Case Else MsgBox “Non siamo nel weekend” End Select Sub

Alternativamente, si può usare una virgola per separare le istruzioni nella dichiarazione Case. L’esempio che segue mostra un altro modo per definire la procedura precedente. Sub

End

GreetUser( ) Select Case Weekday(Now) Case 2, 3, 4, 5, 6 MsgBox “Non siamo nel weekend” Case Else MsgBox “Siamo nel weekend” End Select Sub

Qualunque numero di istruzioni può essere scritto sotto una istruzione Case, e tutte sono eseguiti se la condizione è vera. Se si usa solo una istruzione per caso, come nel precedente esempio, si può inserire l’istruzione nella stessa linea come la parola chiave Case (senza dimenticare il carattere separatore della dichiarazione di VBA, i due punti). Questa tecnica rende il codice più compatto. Per esempio: Sub

Sconto3( ) Quantità = InputBox(“Digita quantità: “) Select Case Quantità Case “”: Exit Sub Case 0 To 24: Sconto = 0.1 Case 25 To 49: Sconto = 0.15 Case 50 To 74: Sconto = 0.20 Case Is >= 75: Sconto = 0.25


72

End

End Select MsgBox “Sconto: “ & Sconto Sub

VBA esce dalla costruzione Select Case tanto più velocemente quanto prima viene trovata la condizione vera. Quindi per una massima efficienza, controllare prima la condizione più probabile. Le strutture SelectCase possono anche essere nidificate. La seguente procedura, per esempio testa lo stato di finestra di Excel (massima, minimizzata, o normale) e poi mostra un messaggio sullo stato della finestra. Se lo stato della finestra di Excel è normale, la procedura testa lo stato della finestra di quella attiva e mostra un altro messaggio. Sub AppWindow() Select Case Application.WindowState Case xlMaximized: MsgBox "Applicazione massimizzata" Case xlMinimized: MsgBox "Applicazione minimizzata" Case xlNormal: MsgBox "Applicazione normale" Select Case ActiveWindow.WindowState Case xlMaximized: MsgBox "Libro massimizzato" Case xlMinimized: MsgBox "Libro minimizzato" Case xlNormal: MsgBox "Libro normale" End Select End Select End Sub

Si possono nidificare le istruzioni Select Case tanto per quanto necessit, ma accertarsi che ogni dichiarazione Select Case abbia il suo corrispondente End Select. La procedura seguente dimostra l’importanza di usare il rientro nel codice per dare chiarezza alla struttura. Per esempio, guardare l’identica procedura senza rientri: Sub AppWindow() Select Case Application.WindowState Case xlMaximized: MsgBox “Applicazione massimizzata” Case xlMinimized: MsgBox “Applicazione minimizzata” Case xlNormal: MsgBox “Applicazione normale” Select Case ActiveWindow.WindowState Case xlMaximized: MsgBox “Libro massimized” Case xlMinimized: MsgBox “Libro minimizzato” Case xlNormal: MsgBox “Libro normale” End Select End Select End Sub

Looping di blocchi di istruzioni Looping è il processo di ripetizione di un blocco di istruzioni. Si può conoscere il numero di ripetizioni o può essere determinato dal valore delle variabili nel programma. Il seguente codice, nel quale entrano numeri consecutivi all’interno di un intervallo, dimostra che cosa sia intendo un cattivo loop. La procedura usa due variabili per immagazzinare un valore di partenza (StartVal) e il numero totale delle celle da Uso avanzato di MS Excel

73

riempire (NumToFill). Questo loop usa la dichiarazione GoTo per controllare il lancio dell’applicazione. Se la variabile Cnt, che tiene traccia di come molte celle sono riempite, è inferiore al numero richiesto dall’utente, il controllo del programma torna indietro a DoAnother: Sub BadLoop() StartVal = 1 NumToFill = 100 ActiveCell.Value = StartVal Cnt = 1 DoAnother: ActiveCell.Offset(Cnt, 0).Value = StartVal + Cnt Cnt = Cnt + 1 If Cnt < NumToFill Then GoTo DoAnother Else Exit Sub End Sub

Questa procedura lavora come inteso, però è un esempio di cattivo looping. Usare GoTo per eseguire il loop è contrario al concetto di codice strutturato (vedere “Che cosa è una Programmazione Strutturata?”). Infatti, la dichiarazione GoTo rende il codice molto più difficile alla lettura, ed è quasi impossibile rappresentare un loop usando linee rientranti. In più questo tipo di loop astrutturato rende la procedura più suscettibile di errore. Inoltre usa molte etichette e il codice che appare ha una minima o nessuna struttura. Affinché VBA abbia parecchi comandi di looping strutturato, non avere mai (o quasi mai) fiducia della dichiarazione GoTo per prendere decisioni. Loops For-Next Il più buon tipo di loop è For-Next, già usato in precedenti esempi. La sintassi è: For

Next

counter = start To end [Step stepval] [instructions] [Exit For] [instructions] [counter]

Cosa è una Programmazione Strutturata? I programmi strutturati sono considerati superiori rispetto a quelli non strutturati. Così che cosa è una programmazione strutturata? E si può fare con VBA? La premessa base è che una routine o segmento di codice può avere solo un punto di entrata e solo un punto di uscita. In altre parole, il corpo del codice potrebbe mantenersi unito da solo e il controllo del programma non potrebbe saltare in uscita dal mezzo di questa unità. Come risultato, la programmazione strutturata domina la dichiarazione GoTo. Quando si scrivi codice strutturato, il programma progredisce in maniera ordinata ed è facile da seguire. Un programma strutturato è semplice da leggere e capire rispetto ad uno non strutturato. Ancor più importante è che è semplice da modificare. VBA è un linguaggio strutturato. Esso offre istruzioni strutturate standard, come i loop If-Then-Else, Select Case, For-Next, Do Until, e Do While. Inoltre, VBA supporta pienamente la costruzione del codice modulare. Uso avanzato di MS Excel

74

Di seguito vi è un esempio di loop For-Next che non usa il valore opzionale Step o l’espressione opzionale Exit For. Questa routine esegue la espressione Sum = Sum + Sqr(Count) 100 volte e mostra il risultato – che è, la somma delle radici quadrate dei primi 100 numeri. Sub SommaRadiciQuadrate() Dim Somma As Double, Conta As Integer Sum = 0 For Conta = 1 To 100 Somma = Somma + Sqr(Conta) Next Conta MsgBox Somma End Sub

In questo esempio, Conta (lo sviluppo della variabile di conteggio) iniziata come 1 e aumenta di uno alla volta la ripetizione del loop. La variabile Somma semplicemente accumula le radici quadrate per ogni valore di Conta. Quando si utilizza il loop For-Next, è importante capire che il conteggio loop è una variabile normale – niente di speciale. Come risultato, è possibile cambiare il valore del contatore di loop all’interno del blocco di codice eseguito fra le dichiarazioni For e Next. Comunque, questa è una pratica sbagliata e può causare un risultato imprevedibile. Infatti, si possono prendere speciali precauzioni per assicurarsi che il codice non cambi il contatore di loop. Si può anche usare il valore Step per saltare alcuni valori nel loop. Segue la stessa procedura riscritta per sommare le radici quadrate dei numeri dispari tra 1 e 100. Sub

End

SommamDispariRadiciQuadrate() Somma = 0 For Conta = 1 To 100 Step 2 Somma = Somma + Sqr(Conta) Next Conta MsgBox Somma Sub

In questo procedimento, Conta inizia con 1 e prende il valore di 3, 5, 7 e così via. Il valore finale di Conta usato all’interno del loop è 99. Quando il loop termina, il valore di Conta è 101. La seguente procedura esegue lo stesso lavoro come il BadLoop di esempio trovato all’inizio della sezione “Blocco di istruzioni di looping”. Eliminato il GoTo, comunque, convertire un cattivo loop in un buon loop usando la struttura For-Next. Sub

End

GoodLoop( ) StartVal = 1 NumToFill = 100 ActiveCell.Value = StartVal For Cnt = 0 To NumToFill – 1 ActiveCell.Offset(Cnt, 0).Value = StartVal + Cnt Next Cnt Sub


75

Il loop For-Next può anche includere una o più espressioni Exit For all’interno del loop. Quando si incontra questa espressione, il loop termina immediatamente, come dimostra l’esempio che segue. Questa procedura determina quali celle hanno il valore più grande nella colonna A del foglio di lavoro attivo: Sub ExitForDemo() MaxVal = Application.WorksheetFunction.Max(Range("A:A")) For Row = 1 To 65536 Set TheCell = Range("A1").Offset(Row - 1, 0) If TheCell.Value = MaxVal Then MsgBox "Max value is in Row " & Row TheCell.Activate Exit For End If Next Row End Sub

Il massimo valore nella colonna è calcolato usando la funzione di Excel MAX. Questo valore è poi assegnato alla variabile MaxVal. Il loop For-Next controlla ogni cella e ogni colonna. Se la cella creata controllata è uguale a MaxVal, l’espressione Exit For conclude la procedura. Prima del termine del loop, la procedura informa l’utente circa l’ubicazione della cella. La ExitForDemo viene presentata per dimostrare come si esce da un loop For-Next. Comunque, non è la strada più efficiente per attivare il più ampio valore in un intervallo. Infatti, una singola dichiarazione eseguirà: Range(“A:A”).Find(Application.WorksheetFunction.Max _ (Range(“A:A”))).Activate

I precedenti esempi usano loop relativamente semplici. Ma si può avere un certo numero di espressioni nel loop, e si possono anche nidificare i loop For-Next dentro altri loop For-Next. Qui c’è un esempio che usa i loop nidificati For-Next per iniziare a vettore 10x10x10 con il valore -1. Quando la procedura termina, ognuno dei 1000 elementi in MyArray dovrebbe contenere -1. Sub

End

NestedLoops( ) Dim MyArray(1 to 10, 1 to 10, 1 to 10) Dim I As Integer, j As Integer, k As Integer For I = 1 To 10 For j = 1 To 10 For k = 1 To 10 MyArray(I, j, k) = -1 Next k Next j Next i Sub

Loops Do While Il loop Do While è un’altro tipo di looping strutturato disponibile in VBA. Diversamente da For-Next, Do While esegue mentre si incontra una specifica condizione. I loop Do While possono avere due sintassi:


76

Do

Loop

[While condition] [instructions] [Exit Do] [instructions]

oppure Do Loop

[instructions] [Exit Do] [instructions] [While condition]

Come si può vedere VBA permette di mettere la condizione While all’inizio o alla fine del loop. Le differenze tra le due sintassi indica il momento in cui la condizione è controllata. Nella prima sintassi, i contenuti del loop non possono essere mai eseguiti. Nella seconda sintassi, i contenuti del loop sono sempre eseguiti almeno una volta. Il seguente esempio usa Do While con la prima sintassi. Sub

End

DoWhileDemo() Do While Not IsEmpty(ActiveCell) ActiveCell.Value = 0 ActiveCell.Offset(1, 0).Select Loop Sub

Questa procedura usa la cella attiva come punto di inizio e prosegue verso il basso nella colonna, inserendo uno 0 nella cella attiva. Ogni volta che il loop si ripete, la cella successiva nella colonna diventa attiva. Il loop continua fino a quando la funzione VBA IsEmpty rileva che la cella attiva non è vuota. La seguente procedura usa la seconda sintassi di Do While. Il loop vuole sempre essere eseguito almeno una volta, anche se la cella iniziale attiva è vuota. Sub

End

DoWhileDemo2() Do ActiveCell.Value = 0 ActiveCell.Offset(1, 0).Select Loop While Not IsEmpty(ActiveCell) Sub

Il seguente è un altro esempio di Do While. Questa procedura apre un file di testo, legge ogni linea, converte il testo in maiuscolo, quindi lo immagazzina nella pagina attiva, iniziando dalla cella A1 e continnuoando sotto nella colonna. Questa procedura utilizza la funzione VBA EOF, che restituisce True quando la fine del file è stata raggiunta. L’istruzione finale conclude il file di testo. Sub

DoWhileDemo1() Open “c:\data\textfile.txt” For Input As #1 LineCt = 0 Do While Not EOF(1) Line Input #1, LineOfText Range(“A1”).Offset(LineCt, 0) = Ucase(LineOfText) LineCt = LineCt + 1 Loop


77

Ens

Close #1 Sub

While può anche contenere una o più espressioni Exit Do. Quando una espressione Exit Do viene incontrata, il loop termina immediatamente. Do

Loops Do Until La struttura del loop Do Until è molto simile alla struttura di Do While. La differenza è evidente solo quando la condizione è testata. Nel loop Do While, il loop viene eseguito mentre la condizione è vera. Nel loop Do Until, il loop viene eseguito finché la condizione è vera. Do Until ha due sintassi: Do[Until condition] [instructions] [Exit Do] [instructions] Loop

oppure Do

Loop

[instructions] [Exit Do] [instructions] [Until condition]

L’esempio seguente era originariamente presentato per il loop Do While ma è stato riscritto per usare il loop Do Until. La sola differenza è la linea con il Do. L’esempio crea un codice un po’ più pulito perché evita le richieste negative nell’esempio Do While. Sub

End

DoUntilDemo1() Open “c:\data\textfile.txt” For Input As #1 LineCt = 0 Do Until EOF(1) Line Input #1, LineOfText Range (“A1”).Offset(LineCt, 0) = Ucase(LineOfText) LineCt = LineCt + 1 Loop Close #1 Sub


78

Uso di componenti esterne Una delle caratteristiche più utili che derivano dall’implementazione di VBA in Excel è la possibilità di utilizzare componenti esterne, sotto forma di DLL (Dynamic Link Libraries), vale a dire di componenti software che mettono a disposizione di una applicazione (Excel nel nostro caso) delle funzionalità di elaborazione dati, manipolazione testi ecc. . Le DLL possono essere sviluppate in diversi linguaggi di programmazione (C++, Delphi, Visual Basic 6, ecc), ma risultano comunque utilizzabili in Excel. Quando queste DLL sono sviluppate secondo il modello COM della Microsoft, il loro uso all’interno di Excel risulta notevolmente semplificato. In questo capitolo vedremo come utilizzare tre DLL sviluppate specificamente per lo sviluppo di modelli agro-ecologici.

Lettura di files meteo con PAR_CSDLL.dll Per utilizzare questa DLL dobbiamo prima installarla sul nostro PC. Via internet ci colleghiamo a http://www.isci.it/tools e scarichiamo l’installazione di PAR_CSDLL. L’installazione richiede pochi minuti. Fatta l’installazione, apriamo una nuova cartella di lavoro Excel, e ci spostiamo nel VBA editor. Qui clicchiamo su Strumenti/Riferimenti. Cerchiamo e selezioniamo la componente PAR_CSVB6 come da figura:

Adesso vogliamo vedere quali oggetti, e quindi quali metodi e quali proprietà, ci mette a disposizione questa DLL. Sempre dall’ambiente VBA, con il tasto F2, attiviamo l’object browser. Selezioniamo la libreria PAR_CSVB6, e questo ci fa vedere le classi contenute nella libreria. Clicchiamo sulla classe CS_Met, e questo ci mostrerà i membri della classe. Cerchiamo e selezioniamo il metodo ReadMetFile, e possiamo vedere la sintassi del metodo in basso (Function ReadMetFile(….) as long)


79

Nel frattempo avremo dato un’occhiata al file di help contenuto nell’installazione (PAR_CSDLL.chm), e in particolare:

Leggiamo che il metodo ReadMetFile serve per leggere i dati da un file meteo, e che, a lettura avvenuta, i dati ed alcune elaborazioni sono rese disponibili attraverso le proprietà della classe. Sempre nel file di help, leggeremo quale deve essere il formato del file con i dati meteorologici (un file di esempio è fornito con l’applicazione). Bene, non ci resta che scrivere poche linee di codice per utilizzare le capacità della classe CS_Met. Torniamo al nostro file Excel. Ci spostiamo dall’ambiente VBA al foglio di lavoro. Passiamo in Modalità Progettazione, e poniamo un pulsante sul foglio di lavoro. Clicchiamo con il tasto destro sul pulsante e, nella finestra proprietà, cambiamo il valore di Name a cmdReadFile e di Caption a Read File. Chiudiamo la finestra delle Uso avanzato di MS Excel

80

proprietà e facciamo un doppio click sul pulsante. Ci ritroviamo nell’ambiente VBA con il cursore all’interno della procedura Private sub cmdReadFile_click. Scriviamo il codice: Private sub cmdReadFile_Click Dim Dim Dim Dim

iIndx As Integer fname As String dat As CS_Met result As Long

With CommonDialog1 .Filter = "CS met files (*.dat, *.gen)|*.dat;*.gen" .ShowOpen fname= .Filename End With Foglio1.Cells(2, 2).Value = fname

‘scrive nome file met su foglio1

Set dat = New CS_Met result = dat.ReadMetFile(fname)

‘crea oggetto tipo CS_Met ‘legge file met

'write daily With Foglio1 For iIndx = 1 To .Cells(iIndx .Cells(iIndx .Cells(iIndx .Cells(iIndx .Cells(iIndx .Cells(iIndx .Cells(iIndx .Cells(iIndx Next iIndx End With

366 + 5, + 5, + 5, + 5, + 5, + 5, + 5, + 5,

‘scrive valori giornalieri su foglio1 1).Value 2).Value 3).Value 4).Value 5).Value 6).Value 7).Value 8).Value

= = = = = = = =

iIndx dat.Rain(iIndx) dat.Tmax(iIndx) dat.Tmin(iIndx) dat.Rad(iIndx) dat.Hmax(iIndx) dat.Hmin(iIndx) dat.WSpeed(iIndx)

With Foglio ‘scrive valori annuali, mensili, decadali su foglio2 'write annual .Cells(3, 2).Value = dat.RainA .Cells(3, 3).Value = dat.TmaxA .Cells(3, 4).Value = dat.TminA .Cells(3, 5).Value = dat.RadA .Cells(3, 6).Value = dat.HmaxA .Cells(3, 7).Value = dat.HminA .Cells(3, 8).Value = dat.WSpeedA 'write monthly For iIndx = 1 To 12 .Cells(iIndx + 5, 2).Value = dat.RainM(iIndx) .Cells(iIndx + 5, 3).Value = dat.TmaxM(iIndx) .Cells(iIndx + 5, 4).Value = dat.TminM(iIndx) .Cells(iIndx + 5, 5).Value = dat.RadM(iIndx) .Cells(iIndx + 5, 6).Value = dat.HmaxM(iIndx) .Cells(iIndx + 5, 7).Value = dat.HminM(iIndx) .Cells(iIndx + 5, 8).Value = dat.WSpeedM(iIndx) Next iIndx 'write 10 days For iIndx = 1 To 36 .Cells(iIndx + 19, 2).Value = dat.Rain10(iIndx) .Cells(iIndx + 19, 3).Value = dat.Tmax10(iIndx) .Cells(iIndx + 19, 4).Value = dat.Tmin10(iIndx) .Cells(iIndx + 19, 5).Value = dat.Rad10(iIndx) .Cells(iIndx + 19, 6).Value = dat.Hmax10(iIndx) Uso avanzato di MS Excel

81

.Cells(iIndx + 19, 7).Value = dat.Hmin10(iIndx) .Cells(iIndx + 19, 8).Value = dat.WSpeed10(iIndx) Next iIndx End With Set dat = Nothing

‘cancella oggetto dalla memoria

End Sub

Mentre scriviamo il codice, notiamo che, una volta istanziato l’oggetto dat come oggetto di tipo CS_Met, ogni volta che scrviamo dat. Ci appaiono metodi e proprietà dell’oggetto, in questo caso tutti i valori delle variabili meteorologiche d’interesse. Il valore di queste variabili è appunto memorizzato nelle proprietà di sola lettura che abbiamo visto nel visualizzatore di oggetti.

Calcolo evapotraspirazione con ET_CSDLL.dll Per utilizzare questa DLL dobbiamo prima installarla sul nostro PC. Via internet ci colleghiamo a http://www.isci.it/tools e scarichiamo l’installazione di ET_CSDLL. L’installazione richiede pochi minuti. Fatta l’installazione, questa volta apriamo la cartella di lavoro Excel fornita come esempio nell’installazione (ETCS_DLL.xls), e ci spostiamo nel VBA editor. Con F2 attiviami il visualizzatore di oggetti e selezioniamo la libreria ETCS_100. In questo caso notiamo che c’è solo una classe, ETCS_dll, e un oggetto, ET_CSinputs. ET_CSinputs è una variabile user defined, o Type.


82

Una variabile user defined contiene una serie di variabili, come ad esempio nella finestra dell’object browser relativamente ad ET_CSinputs. Vedremo nelle righe successive perché un Type è molto utile. Intanto, dal file di help della DLL, vediamo in che collezione di variabili consiste ET_CSinputs:

Notate che la variabile ET_Method è definita come ET_CSmethods, vale a dire come Enum anch’esso disponibile. Gli Enum permettono di associare stringhe a costanti numeriche, aumentando la leggibilità del codice. Per assegnare valori ad un Type bisogna rispettare il dimensionamento delle variabili interne. Per esempio, nel nostro caso:

Dim ET_input As ET_CSinputs With ActiveWorkbook.Worksheets(1) If .Cells(2, 2).Value = "PENMAN_MONTEITH" Then ET_input.ET_method = PENMAN_MONTEITH Else ET_input.ET_method = PRIESTLEY_TAYLOR End If ET_input.DOY = .Cells(3, 2).Value Uso avanzato di MS Excel

83

ET_input.Tmax = .Cells(4, 2).Value ET_input.Tmin = .Cells(5, 2).Value ET_input.Tdew = .Cells(18, 2).Value ET_input.RAD = .Cells(6, 2).Value ET_input.WSpeed = .Cells(7, 2).Value ET_input.Hmax = .Cells(8, 2).Value ET_input.Hmin = .Cells(9, 2).Value ET_input.WMeasHeight = .Cells(10, 2).Value ET_input.LAI = .Cells(11, 2).Value ET_input.kc = .Cells(12, 2).Value ET_input.k = .Cells(13, 2).Value ET_input.PTconstant = .Cells(14, 2).Value ET_input.aridity_factor = .Cells(15, 2).Value ET_input.latitude = .Cells(16, 2).Value ET_input.Elevation = .Cells(17, 2).Value End With

Assumiamo che i valori che sono assegnati dalle celle siano corrispondenti ai valori richiesti delle variabili, altrimenti (per esempio, una stringa al posto di una variabile numerica) avremo un errore durante l’esecuzione. Sempre nel visualizzatore di oggetti nella finestra VBA della nostra cartella, adesso clicchiamo sulla classe ETCS_dll, e poi sul metodo start_PET:

Notate che l’input previsto per start_PET è ET_CSinputs. Quindi, quando faremo una chiamata al metodo start_PET, passeremo un tipo ET_CSinputs che avremo prima


84

definito e a cui avremmo assegnato i valori d’interesse. Questo è ciò che accade nel codice della cartella esempio della DLL: Private Sub cmdCalcola_Click() Dim ob As ETCS_dll Dim FReturnLong As Long Dim ET_input As ET_CSinputs With ActiveWorkbook.Worksheets(1) If .Cells(2, 2).Value = "PENMAN_MONTEITH" Then ET_input.ET_method = PENMAN_MONTEITH Else ET_input.ET_method = PRIESTLEY_TAYLOR End If ET_input.DOY = .Cells(3, 2).Value ET_input.Tmax = .Cells(4, 2).Value ET_input.Tmin = .Cells(5, 2).Value ET_input.Tdew = .Cells(18, 2).Value ET_input.RAD = .Cells(6, 2).Value ET_input.WSpeed = .Cells(7, 2).Value ET_input.Hmax = .Cells(8, 2).Value ET_input.Hmin = .Cells(9, 2).Value ET_input.WMeasHeight = .Cells(10, 2).Value ET_input.LAI = .Cells(11, 2).Value ET_input.kc = .Cells(12, 2).Value ET_input.k = .Cells(13, 2).Value ET_input.PTconstant = .Cells(14, 2).Value ET_input.aridity_factor = .Cells(15, 2).Value ET_input.latitude = .Cells(16, 2).Value ET_input.Elevation = .Cells(17, 2).Value End With Set ob = New ETCS_dll FReturn = ob.start_PET(ET_input) With ActiveWorkbook.Worksheets(1) .Cells(2, 7).Value = Format(ob.get_refPET, "#0.00") .Cells(3, 7).Value = Format(ob.get_cropPET, "#0.00") .Cells(4, 7).Value = Format(ob.get_PTR, "#0.00") .Cells(5, 7).Value = Format(ob.get_PEV, "#0.00") .Cells(6, 7).Value = Format(ob.get_Aereoynamic_Resistance, "#0.00") .Cells(7, 7).Value = Format(ob.get_Isothermal_LWNR, "#0.00") .Cells(8, 7).Value = Format(ob.get_Latent_Heat_Vapour, "#0.00") .Cells(9, 7).Value = Format(ob.get_LightInterception, "#0.00") .Cells(10, 7).Value = Format(ob.get_NetRadiation, "#0.00") .Cells(11, 7).Value = Format(ob.get_PotRad, "#0.00") .Cells(12, 7).Value = Format(ob.get_DayLenght, "#0.00") .Cells(13, 7).Value = Format(ob.get_SVP, "#0.00") .Cells(14, 7).Value = Format(ob.get_slopeSVP, "#0.00") .Cells(15, 7).Value = Format(ob.get_Vol_Heat_Capacity, "0.00000") .Cells(16, 7).Value = Format(ob.get_VPD, "#0.00") .Cells(17, 7).Value = Format(ob.get_VPDmax, "#0.00") .Cells(18, 7).Value = Format(ob.get_AirHumidityCorrFact, "#0.00") .Cells(19, 7).Value = Format(ob.get_ActualVapourPressure, "#0.00") End With Set ob = Nothing End Sub


85

Valutazione della performance dei modelli con IRENE_DLL.dll Analogamente alle DLL precedenti, per installare IRENE_DLL sul nostro PC è necessario scaricare il programma di installazione da http://www.isci.it/tools. Dopo l’installazione, IRENE_DLL è selezionabile dalla finestra Riferimenti/VBA Project (dal VBA editor, cliccare sul menu Strumenti/Riferimenti). Per avere una panoramica delle classi, metodi variabili e costanti di IRENE_DLL, aprire il visualizzatore di oggetti, cliccando F2 dal VBA editor. Si può subito notare che questa DLL è un po’ più complessa delle precedenti. Essa consta di dieci classi (prefissate da IRENEobj_), e presenta un certo numero di variabili User defined, (prefissate da IRENEvar_) e costanti di enumerazione (prefissate da IRENEconst_).

Le classi di IRENE_DLL hanno diverse funzioni: le classi IRENEobj_DataObject e IRENEobj_DataObjects servono praticamente da contenitori dei dati da passare alle funzioni; le classi IRENEobj_Index, IRENEobj_Regression, IRENEobj_Pattern, IRENEobj_Tests, IRENEobj_DistrFunctions contengono routines matematiche per il calcolo di indici e test statistici; le classi IRENEobj_Module e IRENEobj_Indicator Uso avanzato di MS Excel

86

contengono procedure per l’aggregazione di statistici in indici globali di performance; infine IRENEobj_GeneralRoutines contiene semplici routines accessorie (display di informazioni). L’uso delle funzioni di IRENE_DLL in una macro di Excel (o altro ambiente di programmazione) è articolato in tre passaggi fondamentali.

1) Caricamento dei dati. Tutte le funzioni di IRENE_DLL calcolano statistiche su data set composti da serie di dati stimati e misurati (solo il pattern index richiede una serie aggiuntiva di dati indipendenti). Prima di essere analizzati, tutti i dati devono essere caricati su un oggetto IRENEobj_DataObject: Es. 1 – Caricamento dati Nel presente esempio, si assume di avere una serie di stimati e una di misurati incolonnati in due colonne di un foglio di lavoro Excel. ‘ Creare un oggetto IRENEobj_DataObject, e chiamarlo “Dati” Dim Dati As IRENEobj_DataObject Set Dati = New IRENEobj_DataObject ‘ Leggere i dati dal foglio Excel, e caricarli nell’oggetto Dati Dim i As Long ‘ variabile contatore With ActiveWorkbook.Worksheets(1) For i = 1 To 365 Dati.Estimated(i, 1) = .Cells(i, 1) Dati.Measured(i, 1) = .Cells(i, 2) Next i End With

2) Calcolo delle statistiche. Una volta caricato un oggetto IRENEobj_DataObject con almeno una serie di stimati e misurati, può essere passato ad una qualsiasi funzione della DLL. Le funzioni sono raggruppate in classi diverse a seconda della loro tipologia. Per. es., se dobbiamo un calcolare un particolare indice basato sullo scarto (Ei-Mi), bisogna creare un oggetto IRENEobj_Index, e scegliere la funzione che ci interessa. Se invece dobbiamo eseguire un calcolo dei parametri di regressione, troveremo le funzioni adatte nell’oggetto IRENEobj_Regression e così via. Per individuare le funzioni necessarie ad uno specifico scopo è necessario consultare la documentazione del programma. Il visualizzatore di oggetti è comunque di aiuto per avere una rapida visione dell’interfaccia di ogni oggetto. Nella figura sottostante per esempio, è possibile notare che la classe IRENEobj_Regression espone quattro funzioni e una proprietà.


87

Ogni funzione restituisce un “pacchetto” di risultati, ossia il valore cercato, più alcune statistiche descrittive, specifiche per quel particolare tipo di funzione. Sempre dal visualizzatore di oggetti, possiamo consultare la struttura di una particolare variabile, per esempio la variabile IRENEvar_Regression, organizzata in modo da raccogliere e gestire i risultati delle funzioni di regressione.


88

E’ necessario quindi, oltre a creare l’oggetto che ci interessa, dichiarare una specifica variabile UDT richiesta da quel tipo di funzione, alla quale andranno assegnati i risultati. Il seguente esempio dovrebbe essere sufficientemente esplicativo. Es. 2 – Calcolo dei parametri di regressione Nel presente esempio, si assume di aver già pronto l’oggetto “Dati” creato nell’esempio precedente, contenente una serie di stimati e misurati. ‘ La funzione per il calcolo dei parametri di regressione è contenuta ‘ nella classe IRENEobj_Regression. Si deve perciò creare un’istanza ‘ di questa classe. Dim RegrObject As IRENEobj_Regression Set RegrObject = New IRENEobj_Regression ‘ Per il calcolo, è necessaria una variabile specifica per raccogliere ‘ i risultati Dim RegrResults As IRENEvar_Regression Uso avanzato di MS Excel

89

‘ ‘ ‘ ‘ ‘ ‘ ‘

Questa linea di codice esegue tutti i calcoli. Regression_LS indica che viene utilizzato il metodo dei minimi quadrati (Least Squares) Gli altri parametri della regressione hanno il seguente significato: (Paired_Column): indica che il confronto viene eseguito “per colonna” (MeasuredVariable): la serie dei misurati viene considerata come variabile indipendente.

RegrResults = RegrObject.Regression_LS(Dati, Paired_Column, _ MeasuredVariable)

3) Gestione degli output Proseguendo l’esempio precedente, osserviamo che disponiamo ora di una variabile RegrResults contenente tutti i risultati del calcolo di regressione. Supponiamo ora di visualizzare I risultati nello stesso foglio di lavoro, accanto alle colonne dei dati di input Es. 3 – Display dei risultati ‘ ‘ ‘ ‘

riprendi la variabile RegrResults dall’esempio precedente e visualizziamo le varie sottovariabili nel foglio di lavoro. Nota: ogni variabile è un array, di cui visualizziamo solo il valore (1), avendo analizzato solo una coppia di serie numeriche

With ActiveWorkbook.Worksheets(1) .Cells(1, 4).Value = Risultati.Intercept(1) .Cells(2, 4).Value = Risultati.Intercept_StandError(1) .Cells(3, 4).Value = Risultati.Intercept_Prob_Tvs0(1) .Cells(4, 4).Value = Risultati.Intercept_Tvs0(1) .Cells(5, 4).Value = Risultati.Slope(1) .Cells(6, 4).Value = Risultati.Slope_Tvs0(1) .Cells(7, 4).Value = Risultati.Slope_Tvs1(1) .Cells(8, 4).Value = Risultati.Slope_Prob_Tvs0(1) .Cells(9, 4).Value = Risultati.Slope_Prob_Tvs1(1) .Cells(10, 4).Value = Risultati.F(1) .Cells(11, 4).Value = Risultati.Prob_F(1) End With

Altro esempio applicativo Come esempio ulteriore, descriviamo un’intera procedura per il calcolo dell’RMSE (Root Mean Squared Error) da implementare in un foglio di lavoro Excel. Apriamo un nuovo foglio di lavoro, e inseriamo due colonne di dati (in questo caso, due serie di 15 osservazioni ciascuna). Entriamo in modalità progettazione (ciccare sull’icona “squadra e matita”, e Inseriamo un pulsante di azione, prelevato col mouse dalla casella toolbox, (ciccare sull’icona “martello e chiave inglese”). Per scrivere l’intestazione sul pulsante, aprire la finestra “proprietà” cliccando sul bottone col pulsante destro del mouse, e scrivere “Calcola” in corrispondenza della proprietà Captino, e “cmdCalcolaRMSE” in corrispondenza della proprietà Name. Possiamo a questo punto predisporre una cella del foglio per visualizzare il risultato finale, magari dandole un colore un po’ vistoso. Il risultato finale dovrebbe essere simile alla figura seguente:


90

Ora, sempre in modalità progettazione, facciamo un doppio clic sul bottone, che ci porterà automaticamente nel VBA editor e aprirà una nuova procedura, che apparirà così:


91

Tra le espressioni: Private Sub ….. e End Sub digitiamo la seguente procedura: ' ' ' '

-------------------------------------------------------Calcolo del Root Mean Squared Error (RMSE) per due serie di dati, stimati e osservati --------------------------------------------------------

' Crea un oggetto "IRENEobj_DataObject" Dim Dati As IRENEobj_DataObject Set Dati = New IRENEobj_DataObject ' Crea un oggetto IRENEobj_Index Dim ComputingObject As IRENEobj_Index Set ComputingObject = New IRENEobj_Index ' Dichiara una variabile di tipo "indice" per i risultati Dim Risultati As IRENEvar_Index ' Leggi i dati dal worksheet(1), e caricali nell'oggetto Dati Dim i As Long With ActiveWorkbook.Worksheets(1) For i = 1 To 15 Dati.Estimated(i, 1) = .Cells(i + 1, 1) Dati.Measured(i, 1) = .Cells(i + 1, 2) Next i End With ' Calcola il risultato Risultati = ComputingObject.RMSE(Dati, Paired_Columns) ' Mostra i risultati ActiveWorkbook.Worksheets(1).Cells(5, 4).Value = Risultati.Value(1) ' Questi due comandi rimuovono gli oggetti dalla memoria Set Dati = Nothing Set ComputingObject = Nothing

La nostra procedura è finalmente pronta. Uscire dal VBA editor, uscire dalla modalità progettazione, e cliccare sul pulsante “Calcola”. Il risultato dovrebbe apparire nell’apposita casella.


92

Uso avanzato di MS Excel - dista.unibo.it

Recommend Documents