Excel development e sql 1.3

Management Analytics VBA per Excel Giovanni Della Lunga giovanni.dellalunga@gmail.com MASTER BIG DATA, ANALYTICS AND TECHNOLOGIES FOR MANAGEMENT

Agenda » Excel come Piattaforma di Sviluppo Excel può essere molto più di un semplice foglio di calcolo » Le Macro di Excel Come utilizzare il registratore di Macro per imparare il VBA » La Sintassi del Visual Basic Gli elementi base del linguaggio di programmazione » Gestire l’interazione con Excel Il modello ad oggetti di Excel ed il suo utilizzo con VBA

Excel come Piattaforma di Sviluppo Excel può essere molto più di un semplice foglio di calcolo

Excel come piattaforma di sviluppo » Microsoft Excel è molto più di un semplice spreadsheet. » A partire dall’introduzione dell’editor VBA con Excel 2007 fino ai recenti miglioramenti di stabilità e performance di Excel 2016, possiamo dire che tale applicativo è diventato un’interessante piattaforma di sviluppo per reporting e presentation oltre che per analisi preliminari di dati e test di procedure di calcolo.

Excel come piattaforma di sviluppo » Se pensiamo ad Excel non come ad un semplice spreadsheet ma come una piattaforma di sviluppo, possiamo individuare almeno 4 idee fondamentali che possiamo utilizzare con successo nelle nostre applicazioni. Cartelle di lavoro come interfacce utente e presentation layer per data entry e reporting Cartelle di lavoro viste come linguaggio di programmazione «dichiarativo» Modello ad oggetti di Excel Ambiente di Sviluppo VBA

Excel come piattaforma di sviluppo • Possiamo pensare i fogli di Excel come un sistema efficiente di interfaccia utente per data entry; • L’utilizzo delle proprietà di formattazione e validazione dei dati, tipici delle celle Excel, permette la realizzazione di interfacce di livello professionale con pochissimo sforzo; • Gli elementi grafici di Excel permettono altresì la realizzazione di eccellenti output per qualunque tipo di reporting; Cartelle di lavoro come interfacce utente e presentation layer per data entry e reporting

Excel come piattaforma di sviluppo » Venduti = 1000 » Prezzo = 10.99 » Ricavato = Venduti * Prezzo » D1 = 1000 » D2 = 10.99 » D3 = D1 * D2 • Un foglio Excel è di fatto un linguaggio di programmazione di per se; • Invece di definire un insieme di operazioni che vengono eseguite linea per linea, noi «programmiamo» in questo linguaggio definendo una serie di dichiarazioni (scrivendo formule e inserendo valori nelle celle); D1 ha il valore 1000 D2 ha il valore 10.99 D3 è il prodotto di D1 per D2 Cartelle di lavoro viste come linguaggio di programmazione «dichiarativo»

Excel come piattaforma di sviluppo • Quasi tutto quello che può essere fatto direttamente sui fogli Excel può essere riprodotto in VBA attraverso il ricco modello ad oggetti messo a disposizione dall’ambiente di programmazione Modello ad oggetti di Excel

Excel come piattaforma di sviluppo • Il linguaggio di programmazione di Excel: VBA (Visual Basic for Application); • Condiviso da tutta la piattaforma MS Office; Le Macro di Excel L’Ambiente di Sviluppo La Sintassi del VBA Interagire con Excel

Le Macro di Excel Come utilizzare il registratore di Macro per imparare il VBA

Le Macro di Excel • La costruzione e l'organizzazione di strutture dati non banali tramite Excel può comportare l'esecuzione da parte dell'utente di una discreta mole di operazioni, spesso ripetitive. • In questi casi può essere estremamente utile ricorrere al registratore di macro che permette di memorizzare una sequenza di operazioni al fine di poterle successivamente ripetere a richiesta. • Il registratore macro di Excel memorizza le operazioni eseguite dall’utente registrando gli equivalenti comandi sotto forma di istruzioni in linguaggio Visual Basic. • per questo motivo il registratore di macro oltre ad essere un utilissimo aiuto nella normale operatività con Excel costituisce anche un formidabile strumento didattico per cominciare a muoversi con la sintassi VBA. 11

Gestire i file che contengono codice VBA » Office 2013 (come già Office 2010 e 2007), dunque anche Excel 2013, distingue i file che contengono codice VBA dagli altri file con un’estensione e un’icona diverse; » I normali file di Excel hanno estensione .xlsx, mentre quelli che contengono codice VBA hanno estensione .xlsm. » Anche il formato di salvataggio è diverso. Quando salvate un file che contiene codice VBA (le porzioni di codice vengono definite Macro) occorre indicare a Excel che deve salvarlo usando il formato Cartella di lavoro con attivazione di macro di Excel;

Le Macro di Excel • Oltre a vedere la macro in esecuzione è possibile esaminare il codice prodotto e farlo girare istruzione per istruzione. • Quest’attività oltre al chiaro valore didattico ha un enorme utilità ai fini del test dei programmi prodotti (debug). 15

L’Ambiente di Sviluppo del VBA • Il Visual Basic dispone di un ambiente di sviluppo integrato (IDE) attraverso il quale è possibile non solo modificare le macro ma scrivere interi programmi; • L’editor è composto da una serie di finestre tramite le quali controllare le varie fasi di sviluppo e test dell’applicazione; 16  Da Excel l’editor VBA può essere richiamato utilizzando la finestra VBA o premendo F11.

Università dell'Insubria - C.d.L. in Banca & Finanza - A.A. 2004- 2005 17 GESTIONE PROGETTI. Questa sottofinestra contiene un diagramma ad albero delle cartelle di lavoro aperte e degli oggetti Excel in esse contenuti (oggetti, moduli, riferimenti, form e così via). Si userà la finestra gestione progetti per navigare fra i vari moduli ed altri oggetti di un progetto VBA FINESTRA PROPRIETA’. In questa finestra compare un elenco di tutte le proprietà dell’oggetto attualmente selezionato. FINESTRA DEL CODICE. La finestra del codice è quella in cui si può esaminare, modificare o creare ex-novo il codice sorgente VBA. Questa finestra viene usata per scrivere nuove macro o per editare macro esistenti. FINESTRA IMMEDIATA. In questa finestra è possibile inserire comandi diretti per la visualizzazione del valore assunto dalle variabili (usata in fase di debug) FINESTRA CONTROLLO. Consente il monitoraggio del contenuto di una variabile o dei valori generati da un’espressione (usata in fase di debug). Identifica un progetto VBA ovvero un insieme di oggetti e il nome fra parentesi è il nome della cartella in cui esso è memorizzato (in questo caso Cartel1); Identifica un oggetto di tipo foglio di lavoro contenuto nella cartella il cui nome appare fra parentesi; tutti i fogli di lavoro appaiono identificati dalla parola Foglio seguita da un numero, ma è importante non confonderlo con il nome che appare sul cavalierino nella finestra di Excel e che nella finestra Progetto appare fra parentesi; per ogni foglio di lavoro presente nella cartella sono presenti altrettante voci. Indica la cartella

Università dell'Insubria - C.d.L. in Banca & Finanza - A.A. 2004- 2005 18

Università dell'Insubria - C.d.L. in Banca & Finanza - A.A. 2004- 2005 19

Università dell'Insubria - C.d.L. in Banca & Finanza - A.A. 2004- 2005 20  Un modulo è concettualmente simile ad un documento al cui interno si inseriscono i programmi o una loro parte.  La suddivisione di un programma su più moduli spesso è dovuta alla volontà del programmatore di suddividere su più parti il programma dividendolo con concetti di omogeneità;  per esempio si può avere un modulo con tutte le funzioni create che eseguono i calcoli finanziari…  …un altro con le routine che gestiscono l’accesso al database…  …un altro con le funzioni create ad uso generale come quelle che eseguono particolari elaborazioni sul testo mentre…  …un altro modulo contiene le routine principali del programma che richiamano quelle contenute negli altri moduli.

La Sintassi del Visual Basic Gli elementi base del linguaggio di programmazione

La Sintassi del VBA • Il Visual Basic deriva la sua sintassi dal vecchio linguaggio basic; • Nel corso del tempo molte nuove strutture sintattiche sono state aggiunte al linguaggio trasformandolo in qualcosa di molto diverso rispetto all’originale; • In questa parte vedremo la componente convenzionale del linguaggio cioè le istruzioni base per la gestione del flusso del programma, i principali tipi di dato gestiti e la costruzione di procedure; • Nella prossima sezione vedremo la parte più innovativa di questo linguaggio e cioè la componente di interazione con Excel realizzata tramite componenti di programmazione orientata agli oggetti. 22

Variabili eTipi di Dato Operatori Istruzioni di Controllo StruttureDati Procedure: Funzioni e Subroutine La Sintassi del VBA

La Sintassi del VBA: Tipi di Dato e Operatori • Anche Visual Basic, come tutti i linguaggi di programmazione, prevede l'uso delle variabili, mediante le quali è possibile memorizzare dei valori testuali o numerici in strutture a cui il programma può accedere grazie a un nome assegnato loro in fase di creazione. • Visibilità. Una variabile è detta locale quando è definita all'interno di una procedura; • Tipo. La sua creazione avviene quando si fa riferimento ad essa per la prima volta, oppure quando è eseguita l'istruzione Dim, che presenta la seguente sintassi: • Dim <nome> [As <tipo>] 24

La Sintassi del VBA: Tipi di Dato e Operatori • Naming Convention • Una buona convenzione nella definizione del nome delle variabili dovrebbe permettere di dedurre dal nome della variabile: • Il livello di visibilita • Il tipo del dato • Se si tratta di un vettore o di uno scalare • Es. Scope Specifier  g – Public  m – Module Level 25

La Sintassi del VBA: Tipi di Dato e Operatori • Tipi Numerici  Il Visual Basic fornisce per la gestione dei valori numerici varie possibilità che vanno di volta in volta scelte in accordo con il genere di informazione che deve essere memorizzata.  A prima vista potrebbe apparire più comodo impiegare tipi generici che vadano bene per qualunque occasione come il Double ma in realtà è sconsigliabile per vari motivi, soprattutto perché i tipi che supportano la parte decimale vengono elaborati ad una velocità inferiore a quella dei tipi che non la gestiscono quali i tipi Integer o Long. 26 Dim liThisNumber as Integer Dim lsngThisNumber as Single liThisNumber = 5 lsngThisNumber = 4.5

La Sintassi del VBA: Tipi di Dato e Operatori » Operatori Matematici » Gli operatori matematici sono quelli che indicano le principali operazioni matematiche e sono:  + : addizione  - : sottrazione  * : moltiplicazione  / : divisione  : divisione con restituzione della sola parte intera  ^ : elevazione a potenza  Sqr : radice quadrata  mod : resto di una divisione 27

La Sintassi del VBA: Tipi di Dato e Operatori • Tipo di dati in Excel:  Dati Numerici  Dalle Impostazioni internazionali nel pannello di controllo modificare  Per visualizzare i numeri in notazione scientifica ovvero 2.3 e non 2,3  Inoltre direttamente in excel (menu Formato, Celle) è possibile scegliere il tipo di notazione numerica. 28

La Sintassi del VBA: Tipi di Dato e Operatori • Stringhe  Per memorizzare dati testuali quali nomi, indirizzi, annotazioni o altro si ricorre al tipo String.  Facendo seguire la parola chiave String da un asterisco e un numero è possibile predeterminare una lunghezza per le stringhe in modo che il valore in esse inserito  se eccede la dimensione prevista venga troncato  se è inferiore vengano automaticamente inseriti degli spazi fino a raggiungere la lunghezza definita. 29 • Dim lsMyName as String • lsMyName = “Giovanni”

Esercitazione • Esempi di elaborazione di stringhe  Elementi noti :  dichiarazione variabili  Apprendimento:  Funzioni elaborazione stringhe  Anticipazione:  Interazione con oggetti di Excel  Procedure in vba  Array dinamici in VBA e funzioni connesse  Cicli For 30

Esercitazione 31 Public Sub esempio_1_stringhe() ' dichiarazione variabili Dim lsNomeFileCompleto As String Dim lsNomeFileSenzaExt As String Dim lsEstensioneFile As String Dim n As Integer ' recuperiamo il nome file completo utilizzando ' l'oggetto Workbook di Excel lsNomeFileCompleto = ActiveWorkbook.Name Debug.Print lsNomeFileCompleto ' ricerchiamo la posizione del punto nel nome completo ' utilizzando la funzione InStr(). Si noti che il carattere ' . (punto) deve essere compreso fra doppi apici... n = InStr(lsNomeFileCompleto, ".") Debug.Print n (continua) Si noti l’intestazione della procedura Sezione dichiarativa NomeOggetto.Proprietà Funzione InStr()

Esercitazione 32 ' recuperiamo il nome del file privo di estensione cercando ' la sub-stringa composta da n - 1 caratteri che si trova ' a sinistra del punto. Notare che per evitare di prendere ' anche il punto di separazione, dobbiamo ' indicare alla funzione Left() di prendere soltanto n - 1 caratteri ' a sinistra del nome completo del file... lsNomeFileSenzaExt = Left(lsNomeFileCompleto, n - 1) Debug.Print lsNomeFileSenzaExt ' possiamo anche ricavare l'estensione del file utilizzando la ' funzione Right() che restituisce un numero specifico di ' caratteri di una stringa partendo da destra (ovvero dalla fine ' della string). In questo caso utilizzeremo anche la funzione ' Len() che restituisce il numero complessivo di caratteri di una ' stringa, infatti il numero di caratteri che compongono ‘ l'estensione sono gli ultimi Len() - n caratteri... lsEstensioneFile = Right(lsNomeFileCompleto, Len(lsNomeFileCompleto) - n) Debug.Print lsEstensioneFile ' ricombiniamo insieme le due informazioni con l'operatore di concatenazione ' di stringhe "&" Debug.Print lsNomeFileSenzaExt & "." & lsEstensioneFile End Sub Funzione Left() Funzione Right() Funzione Len() Op. Concatenazione : &

La Sintassi del VBA: Tipi di Dato e Operatori • Date  Il VBA usa il tipo dati Date per memorizzare data e ora. Il tipo Date utilizza 8 byte di memoria per ogni combinazione di data/ora.  Per assegnare un valore ad una variabile di tipo Date mediante un’istruzione, si deve ricorrere ad una sintassi particolare che prevede il racchiudere la data tra una coppia di caratteri #. 33 Per esempio il 31 dicembre 2016 può essere rappresentato come illustrato Dim gdteSanSilvestro as Date gdteSanSilvestro = #31/12/2016#

Function IsBisestile(Data As Date) As Boolean If (Year(Date) Mod 100 <> 0 And Year(Date) Mod 4 = 0) Or _ (Year(Date) Mod 100 = 0 And Year(Date) Mod 400 = 0) Then IsBisestile = True Else IsBisestile = False End If End Function Funzioni Speciali Gestione delle Date Year().  Questa funzione restituisce in output un numero intero (di tipo Integer) che contiene l’anno estratto da una data che riceve come parametro di input.  Nel riquadro seguente vediamo un esempio di impiego della funzione Year. Supponiamo di voler costruire una funzione che prenda come parametro di input una data e che restituisca un valore logico vero o falso a seconda che l’anno della data sia o meno bisestile. 34

Function GiorniMese(Data As Date) As Integer Select Case Month(Data) Case 1, 3, 5, 7, 8, 10, 12 GiorniMese = 31 Case 4, 6, 9, 11 GiorniMese = 30 Case 2 If IsBisestile(Data) Then GiorniMese = 29 Else GiorniMese = 28 End If End Select End Function Funzioni Speciali Gestione delle Date » Month().  Questa funzione permette di estrarre il numero del mese da una data che riceve come parametro di input e restituisce tale valore in formato Integer.  Nel seguente esempio di codice abbiamo scritto una semplice funzione che restituisce il numero di giorni del mese a cui appartiene la data passata come parametro alla funzione stessa. 35

Funzioni Speciali Gestione delle Date » Day()  La funzione Day() permette di estrarre il numero del giorno da una data che riceve come parametro. Tale valore viene restituito come al solito in formato Integer. » Weekday()  Questa funzione permette di ottenere un numero di tipo Integer che indica il giorno della settimana relativo alla data ricevuta in input. I giorni vengono rappresentati secondo il valore della tabella sottostante dove sono indicate le relative costanti 36 Giorno Costante VBA Domenica vbSunday Lunedì vbMonday Martedì vbTuesday Mercoledì vbWednesday Giovedì vbThursday Venerdì vbFriday Sabato vbSaturday

Funzioni Speciali Gestione delle Date » DateAdd().  Restituisce un valore Variant (Date) contenente una data alla quale è stato aggiunto un intervallo di tempo specificato. La sintassi di questa funzione è la seguente DateAdd(intervallo, numero, data)  dove  intervallo è un’espressione di tipo stringa corrispondente all'intervallo di tempo che si desidera aggiungere,  numero è un’ espressione numerica corrispondente al numero di intervalli da aggiungere (può essere positiva, per ottenere date future, oppure negativa, per ottenere date passate),  data, infine, rappresenta un valore di tipo data o di tipo stringa che rappresenta una data a cui viene aggiunto l'intervallo. 37

Funzioni Speciali Gestione delle Date » DateDiff().  Restituisce un valore Variant(Long) corrispondente al numero di intervalli di tempo tra due date specificate. La sintassi della funzione DateDiff è la seguente DateDiff(intervallo, data1, data2) dove  intervallo è un’espressione di tipo stringa corrispondente all'intervallo di tempo utilizzato per calcolare la differenza tra data1 e data2.  È possibile utilizzare la funzione DateDiff per determinare quanti intervalli di tempo specificati esistono tra due date.  Ad esempio possiamo utilizzare DateDiff per calcolare il numero di giorni che intercorrono tra due date oppure il numero di settimane tra la data odierna e la fine dell'anno. 38

Esercitazione »Esempi di elaborazione di date  Elementi noti :  dichiarazione variabili  Apprendimento:  Funzioni Analisi Date  Anticipazione:  Istruzioni Condizionali  Ciclo For  Funzioni in VBA  Interazione con Foglio Excel 39

La Sintassi del VBA: Tipi di Dato e Operatori » Operatore di Assegnazione  L’operatore di assegnazione coincide graficamente con l’operatore di confronto di uguaglianza. Ciò non causa generalmente nessuna confusione nel programmatore che si abitua con una certa rapidità a riconoscerli e distinguerli nel loro significato.  L’unico aspetto che lascia momentaneamente confusi chi non ha mai avuto esperienze di sviluppo software è proprio il carattere uguale come operatore di assegnazione in quanto si tende a considerare l’uguale un’asserzione di uguaglianza fra il valore posto alla sua destra e quello posto alla sua sinistra  INCREMENTO DI UNA VARIABILE : a = a + 1 40

La Sintassi del VBA: Tipi di Dato e Operatori Operatori di Confronto 41 Operatore Simbolo Esempio Applicabile a Minore < A < B qualunque Minore o uguale <= A <= B qualunque Uguale = A = B qualunque Diverso <> A <> B qualunque Maggiore > A > B qualunque Maggiore o uguale >= A >= B qualunque Identico tipo Is A Is B Object Analisi per criteri Like A Like B String

La Sintassi del VBA: Istruzioni di Controllo » Il flusso di Controllo  Sequenziale  Condizionale  Iterativo  Determinato  Condizionalo 42

La Sintassi del VBA: Istruzioni di Controllo Condizionale » La condizione IF  Il flusso delle istruzioni all'interno della procedura non è necessariamente rigidamente definito, ma può variare in base al verificarsi di alcune condizioni.  Il programma deve pertanto essere in grado di prendere delle decisioni.  Come la quasi totalità dei linguaggi di programmazione, Visual Basic prevede a tal fine la struttura If, caratterizzata dalla seguente sintassi: If <condizione> Then <istruzioni da eseguire se la condizione è vera> [Else <istruzioni da eseguire se la condizione è falsa>] End If 43

La Sintassi del VBA: Istruzioni di Controllo Condizionale » La condizione può essere rappresentata da pressoché qualsiasi espressione booleana.  Si ricorda che un'espressione è così detta se può assumere solo due valori distinti, cioè vero o falso. » Se essa è verificata, l'interprete esegue il blocco di istruzioni indicato fra le parole chiave Then e Else. In caso contrario, è eseguito il secondo gruppo di comandi. Si noti che quest'ultimo può anche essere assente. In tal caso, la parola chiave Else non va utilizzata e non è eseguito alcun codice in caso di mancata verifica della condizione. » La frase End If delimita la struttura. Tutte le istruzioni che la seguono sono eseguite in modo indipendente dal valore dell'espressione booleana. 44

La Sintassi del VBA: Istruzioni di Controllo Condizionale » La struttura Select Case  E’ possibile ricorrere ad una diversa struttura di controllo denominata Select Case, la cui sintassi è la seguente: Select Case <variabile> Case <valore 1>: <blocco istruzioni 1> [Case <valore 2>: <blocco istruzioni 2>] .. [Case <valore n>: <blocco istruzioni n>] [Case Else: <istruzioni da eseguire se tutti i confronti falliscono>] End Select 45

La Sintassi del VBA: Istruzioni di Controllo Condizionale » Gli operatori logici elementari » Si ipotizzi di voler scrivere una struttura If per verificare se il valore di una variabile numerica intera, denominata n, appartiene all'intervallo compreso fra 10 e 100. È necessario valutare contemporaneamente due condizioni: n deve essere maggiore di 10 E minore di 100 » Per far sì che sia prodotta una stringa indicante l'esito del confronto, occorre digitare il seguente codice: If Numero > 10 then If Numero < 100 then Testo = "Il numero è compreso fra 10 e 100" Else Testo = "Il numero non appartiene all'intervallo" End if End if 46

La Sintassi del VBA: Istruzioni di Controllo Condizionale » L'operatore And » Spesso si rivela necessario valutare la contemporanea validità di due o più condizioni. » È il caso dell'esempio precedente, in cui si desidera verificare se la variabile Numero contiene un valore maggiore di 10 e nel contempo minore di 100. » L'operatore And assolve questo compito, restituendo il valore logico True solo se le condizioni a cui è applicato sono contemporaneamente verificate. Il codice può pertanto essere riscritto come segue: If (Numero > 10) AND (Numero < 100) Then Testo = "Il numero è compreso fra 10 e 100" Else Testo = "Il numero non appartiene all'intervallo" End if » Come è possibile notare, la struttura risulta più semplice, in quanto composta da un solo livello. 47

La Sintassi del VBA: Istruzioni di Controllo Condizionale » L'operatore Or » A differenza del precedente, l'operatore Or restituisce il valore logico True se almeno una delle condizioni specificate è vera. Ad esempio, la condizione  (Numero = 5) OR (Numero > 11) è verificata quando la variabile Numero assume un valore maggiore di 11 o uguale a 5. 48

La Sintassi del VBA: Istruzioni di Controllo Condizionale » L'operatore Not » Un altro utile operatore logico è quello di negazione (Not). » Come è facile dedurre, esso restituisce il valore True se la condizione a cui è applicato non è verificata, mentre restituisce False in caso contrario. » A titolo di esempio, si supponga di voler realizzare una struttura If in grado di generare una stringa indicante se il valore della variabile Numero risulta contemporaneamente diverso da 5 e da 10. Il codice da digitare è il seguente: If NOT ((Numero = 5) Or (Numero = 10)) Then Testo = "Il numero è diverso da 5 e da 10" End if » Si noti la connessione con l’operatore And If Numero <> 5 And Numero <> 10 Then Testo = "Il numero è diverso da 5 e da 10" End if 49

La Sintassi del VBA: Istruzioni di Controllo Iterativo Il ciclo For » Si supponga di voler realizzare un programma in grado di calcolare il fattoriale di un numero, ovvero il prodotto di tutti i valori interi positivi minori o uguali ad esso. » Gli strumenti illustrati fino a questo punto non si rivelano sufficienti a tal fine, in quanto è necessaria la capacità di ripetere per un numero variabile di volte l'operazione di moltiplicazione. » Si impone pertanto il ricorso alle strutture di iterazione. » Nel caso dell'esempio, occorre applicare la moltiplicazione a tutti i numeri naturali minori o uguali a quello di cui si desidera calcolare il fattoriale. » Appare così evidente la necessità di disporre di una struttura in grado di permettere la ripetizione di una porzione di codice per un numero finito di volte. 50

La Sintassi del VBA: Istruzioni di Controllo Iterativo » Si tratta della classica struttura For, che nel caso di Visual Basic è caratterizzata dalla seguente sintassi: For <contatore> = <inizio> To <fine> [Step <passo>] <istruzione 1> ... <istruzione n> Next [<contatore>] » Dopo la parola chiave For è necessario far seguire una variabile intera, che funge da contatore. » Il suo valore è incrementato ad ogni ripetizione di un numero di unità pari a quello specificato dopo la parola chiave Step, fino al raggiungimento del valore finale; » questa condizione determina la fine delle iterazioni e il passaggio all'istruzione seguente la parola riservata Next. 51

La Sintassi del VBA: Istruzioni di Controllo Iterativo Il ciclo While » Una diversa esigenza è quella soddisfatta dall'uso del ciclo While, che permette la ripetizione di un segmento di codice per tutto il tempo in cui una condizione risulta vera. La sua sintassi è la seguente: While <condizione> <istruzione 1> <istruzione 2> ... <istruzione n> Wend » Le istruzioni comprese fra le parole chiave While e Wend sono ripetute per un numero di volte non stabilito rigidamente a priori, bensì dipendente dalle stesse istruzioni, che devono essere in grado di fare in modo che la condizione ad un certo punto smetta di verificarsi. In caso contrario, si incappa in un errore molto diffuso fra i programmatori alle prime armi, ovvero si crea ciò che è usualmente detto ciclo infinito. 52

La Sintassi del VBA: Istruzioni di Controllo Iterativo Le parole chiave Do e Loop » Il ciclo While può anche essere descritto in modo più elegante per mezzo delle parole chiave Do e Loop. In questo caso la sintassi diventa: Do While <condizione> <istruzione 1> <istruzione 2> ... <istruzione n> Loop » Il comportamento è analogo a quello visto in precedenza: le istruzioni contenute all'interno della struttura sono ripetute fintanto che la condizione indicata accanto alla parola While si verifica. 53

La Sintassi del VBA: Istruzioni di Controllo Iterativo Il ciclo Do Until » Pressoché analogo è il ciclo Do Until, caratterizzato dalla seguente sintassi: Do Until <condizione> <istruzione 1> <istruzione 2> ... <istruzione n> Loop » In questo caso, le iterazioni avvengono quando la condizione è falsa e terminano quando essa si avvera. 54

Esercitazione »Esempi di Cicli Condizionali e Non  Elementi noti :  Istruzioni Condizionali  Cicli condizionali  Cicli determinati  Apprendimento:  Funzioni per analisi di alberi di cartelle e files  Anticipazione:  interazione con oggetti di Excel  Procedure in vba 55

La Sintassi del VBA: Vettori e Matrici » Gli array contengono dati omogenei, tipicamente si considerano array a una e due dimensioni ai quali ci riferiremo come di comune coi termini “vettore” e “matrice”. La dichiarazione di un vettore o di una matrice deve essere accompagnata dal numero di elementi. » Esempio:  Dim myVector(20) as Double  Dim myMatrix(10,10) as Integer » Nel primo caso abbiamo definito un vettore che contiene numeri in doppia precisione di 20 elementi, nel secondo abbiamo una matrice 10 x 10 di interi. 56

La Sintassi del VBA: Vettori e Matrici » Descriviamo anche la sintassi in forma generica Dim NomeVariabile ([Indici]) [As Tipo] La sezione indici ha la seguente sintassi [minimo to] massimo [, [minimo to] massimo] che cosa vuol dire? E’ semplice, prima di tutto ricordiamo che tutto quello che si trova tra parentesi quadre è opzionale, quindi è possibile dimensionare un vettore sia scrivendo Dim myVector(20) As Double Sia scrivendo Dim myVector(1 to 10) As Double 57

La Sintassi del VBA: Vettori e Matrici » In maniera analoga entrambe le dichiarazioni sono valide Dim myMatrix(10,10) As Integer Dim myMatrix(1 to 10, 1 to 10) As Integer » Notate che è richiesto solo il limite superiore; l’indicazione del valore minimo per l’indice è opzionale. Se si specifica solo il limite superiore il VBA numera gli elementi dell’array coerentemente con l’impostazione Option Base. » Questa è una direttiva al compilatore che specifica se si desidera che gli indici degli array partano da 0 o da 1. La sintassi è la seguente Option Base 0 | 1 » In mancanza di questa istruzione l’interprete VBA fa partire la numerazione degli array da 0. 58

La Sintassi del VBA: Vettori e Matrici » Il riferimento ad un generico elemento all’interno di un vettore o di una matrice avviene specificando l’indice all’interno di parentesi. » Esempio: a = myVector(5) b = myMatrix(7,6) » Nel primo caso alla variabile a viene assegnato il valore del 5° elemento del vettore myVector, nel secondo b è posto uguale all’elemento di matrice posto nella 7a riga, 6a colonna 59 » In maniera analoga, ogni volta che si intende attribuire un valore ad un certo elemento di un array occorre specificare l’indice relativo. Ad esempio MyVector(7) = 8.9

La Sintassi del VBA: Vettori e Matrici » Possono presentarsi delle situazioni in cui non è possibile o semplicemente non è conveniente fissare a priori il numero di elementi di un array cioè la sua dimensione. » L’uso di un array dinamico al posto di uno statico risolve questo problema in quanto un array dinamico può essere esteso o ridotto secondo le necessità. » Un array dinamico viene dichiarato tale facendo seguire la parola chiave ReDim e indicando il numero di elementi del vettore con una variabile (che deve essere comunque definita al momento dell’esecuzione dell’istruzione di dimensionamento) ReDim Scadenze(n) As Date ReDim Flussi(m) As Currency ReDim Matrice(k) As Double 60

La Sintassi del VBA: Vettori e Matrici » E’ possibile anche ridimensionare un array preservandone il contenuto. A tale scopo è necessario usare la clausola Preserve che va posta subito dopo la parola chiave ReDim, ad esempio ReDim Preserve Vettore(n) As Single » Nel caso di un array multidimensionale, tuttavia, quando si usa la parola chiave Preserve si può modificare soltanto l’ultima dimensione. 61

La Sintassi del VBA: Vettori e Matrici » Per riuscire a tenere traccia delle dimenzioni di array siano essi statici o dinamici il VBA prevede due funzioni, LBound e UBound, che restituiscono il valore minimo e massimo per gli indici di un array. La sintassi generica per queste funzioni è LBound(NomeArray [, dimensione]) UBound(NomeArray [, dimensione]) » dimensione è un numero intero che specifica per quale dimensione dell’array si vuole ottenere il limite minimo o massimo. Se non viene specificato VBA restituisce l’estremo relativo alla prima dimensione dell’array. 62

La Sintassi del VBA: Procedure » Una procedura è una frazione di codice che esegue un determinato compito il più possibile elementare; » Con Visual Basic è possibile creare due tipi di procedure:  procedure di tipo Sub (SUBROUTINE)  procedure di tipo Function. (FUNZIONI) » A differenza di altri linguaggi il Visual Basic distingue nettamente queste due diverse tipologie di procedura; 63

La Sintassi del VBA: Procedure » Una procedura Function è un’unità di codice racchiusa fra le istruzioni Function e End Function. » Come una procedura Sub una procedura Function esegue un preciso compito. A differenza della procedura Sub tuttavia essa restituisce anche un valore. » Esempio Function CalcolaQuadrato(x as Double) as Double CalcolaQuadrato = x*x End Function 64

» Ritorno di valori da una procedura Function  Di solito lo scopo di una funzione è quello di eseguire alcuni calcoli o delle operazioni specifiche su un insieme di dati e di restituire il risultato di queste operazioni.  All’interno del corpo della funzione la riga Nome = Espressione rappresenta l’assegnamento della funzione.  Perché una funzione restituisca un valore essa deve sempre includere un’istruzione che assegni un valore al nome della funzione.  Nella sintassi del Visual Basic questo si ottiene assegnando il valore che si vuole restituire al programma chiamante ad una variabile fittizia che ha lo stesso nome della funzione ma che non viene dichiarata in alcun luogo. La Sintassi del VBA: Procedure

La Sintassi del VBA: Procedure » Una procedura Sub (subroutine) è un’unità di codice racchiusa fra le istruzioni Sub … End Sub. » La procedura di tipo subroutine esegue un compito ma non restituisce alcun valore. » Esempio Sub CalcolaQuadrato(x as Double, res as Double) res = x * x End Sub 66 » Le funzioni e le subroutine possono prendere in input dei parametri (detti argomenti della funzione). » Questi parametri sono dei valori che la funzione o la subroutine riceve per la propria elaborazione e sono indicati fra le parentesi tonde nella riga di dichiarazione della funzione.

La Sintassi del VBA: Procedure 67 Public Sub ProceduraSub() MsgBox “Questa procedura è una subroutine” End Sub Public Function ProceduraFun() As String ProceduraFun = “La procedura che ha generato _ questo risultato è una funzione” End Function

Il Modello ad Oggetti di Excel L’interazione con i fogli di calcolo

Gestire l’Interazione con Excel » Le caratteristiche sintattiche del VBA viste fino a questo punto riguardano tecniche di programmazione convenzionale; » Il punto realmente innovativo del VBA consiste nella possibilità di interagire dinamicamente con i dati presenti nei foglio Excel » Un’applicazione VBA per Excel si caratterizza quindi a livello progettuale distinguendo chiaramente tre fasi  INPUT – in questa fase si raccolgono i dati necessari all’elaborazione da uno o più fogli Excel, eventualmente si possono raccogliere altri dati utilizzando la funzione InputBox o appositi form costruiti dall’utente (vedi oltre);  ELABORAZIONE – questa è la parte di calcolo convenzionale all’interno della quale possono essere presenti anche procedure di elaborazione dati realizzate in altri linguaggi (es. C/C++) al fine di ottimizzare i tempi di elaborazione;  OUTPUT – Il risultato del calcolo solitamente viene riportato nelle celle del foglio di lavoro ed eventualmente riportato in grafico. 69

Gestire l’Interazione con Excel » Gli oggetti di Excel (così come quelli delle altre applicazioni Microsoft Office) sono raggruppati in classi organizzate in modo gerarchico. Ogni classe di oggetti a sua volta può contenere una o più sottoclassi. » La gerarchia vede al livello più alto l’oggetto Application che rappresenta l’applicazione stessa. » E’ facile rendersi conto che all’interno dell’applicazione molti oggetti sono dello stesso tipo essendo distinti solo da un nome univoco o da un indice di riferimento; si pensi ad esempio ai vari fogli che compaiono all’interno di una cartella di lavoro. Questi oggetti formano una collezione o insieme di oggetti. 70

Gestire l’Interazione con Excel » Le collezioni sono sempre individuate da nomi al plurale. Ad esempio in Excel l’insieme Workbooks rappresenta tutte le cartelle di lavoro correntemente aperte. All’interno di ogni oggetto Workbook è disponibile un insieme di oggetti Worksheet (i fogli di lavoro), tale insieme è identificato dal nome Worksheets.  Tutti gli insiemi in Microsoft Office consentono di accedere a ciascun elemento nell’insieme oltre che al numero di oggetti dell’insieme.  Per accedere ad un singolo oggetto in un insieme in Visual Basic è sufficiente digitare il nome dell’insieme seguito dal valore di un indice univoco di riferimento (che può essere un nome o un numero) compreso fra parentesi tonde.  Ad esempio se intendiamo riferirci al foglio denominato “Foglio1” di una generica cartella di lavoro, possiamo utilizzare l’insieme Worksheets con la seguente sintassi: Worksheets(“Foglio1”) oppure Worksheets(1). 71

Gestire l’Interazione con Excel » L’istruzione For Each consente di ripetere un gruppo di istruzioni per ciascun elemento di una collezione. 72 Sintassi Public Sub test() Dim w As Worksheet For Each w In Worksheets Debug.Print w.Name Next End Sub

Gestire l’Interazione con Excel » Ogni oggetto Microsoft Office può contenere proprietà, metodi o eventi.  Una proprietà è un attributo caratteristico che definisce o descrive un oggetto, come la dimensione di una cella, la posizione di una finestra di dialogo o la sua forma.  I metodi (come abbiamo già detto) sono azioni che è possibile eseguire sull’oggetto o tramite esso. Ad esempio sono metodi della cartella il salvataggio su disco, l’eliminazione di dati la chiusura della cartella stessa.  Gli eventi sono frammenti di codice che vengono eseguiti in corrispondenza di eventi intercettati dal sistema operativo (es. click sul mouse). 73 » La sintassi per specificare una proprietà o un metodo è la stessa Oggetto.Identificatore dove  Oggetto è un qualsiasi riferimento valido ad un oggetto e  Identificatore è un qualsiasi nome valido di una proprietà o di un metodo. » Notate che un punto (.) separa il riferimento all’oggetto dal nome della proprietà o del metodo.

Gestire l’Interazione con Excel » Durante la scrittura del codice ogni volta che desiderate invocare una proprietà o un metodo il VBA visualizza accanto al punto una finestra in cui compaiono i nomi delle proprietà e dei metodi dell’oggetto corrente. 74  I metodi vengono identificati da un blocchetto verde mentre le proprietà sono indicate da un’icona a forma di lista con un indice che punta ad un elemento.

Gestire l’Interazione con Excel » Pulsanti di Comando  I pulsanti di comando non solo possono attivare un evento quando vi si preme sopra con il mouse ma rispondono anche ad altri eventi come ad esempio il semplice spostamento del mouse sopra il pulsante.  Dato che i pulsanti di comando possono rispondere ad un complesso sistema di eventi (questa come vedremo è la regola piuttosto che l’eccezione per tutti i comandi) è necessario un modo completamente nuovo per collegare una macro ad un pulsante.  Questo nuovo approccio utilizza quelle che vengono chiamate routine di gestione degli eventi.  Queste routine sono speciali macro, collegate ad un oggetto, per esempio ad un pulsante di comando. 75

Gestire l’Interazione con Excel » Finestra Proprietà di un Pulsante di Comando 76

Gestire l’Interazione con Excel » Macro Gestione Evento Associata ad un Pulsante di Comando 77

La Gestione degli Errori Uso della procedura On Error goto

Il Debug Controllo e correzione del codice

Bibliografia » S. Bullen, R. Bovey, J. Green «Professional Excel Development» Addison-Wesley » M. Ferrero «SQL e Excel» Apogeo 80

Excel development e sql 1.3

More Related Content

Similar to Excel development e sql 1.3

More from Giovanni Della Lunga

Excel development e sql 1.3