VBA Visual Basic Per Excel Ita

Viaggio attraverso la conoscenza del più diffuso linguaggio di creazione macro

Enrico Cannoni - Manuali.Net © Tutti i diritti riservati


Excel Visual Basic per applicazioni Autore: Enrico Cannoni Prima edizione – Corso on.line - Novembre 2001 Seconda edizione- Versione CD-ROM - Dicembre 2001 Copyright © 2001 Manuali Net U.R.L. www.manuali.it/ Copertina e progetto grafico di Enrico Cannoni Ogni cura è stata posta nella raccolta e nella verifica della documentazione contenuta in questo corso. Tuttavia né l’autore, né Manuali Net possono assumersi alcuna responsabilità derivanti dall’utilizzo della stesa. Tutti i diritti sono riservati a norma di legge e a norma delle convenzioni internazionali. Nessuna parte di questo corso può essere riprodotta con sistemi elettronici, meccanici o altri, senza l’autorizzazione scritta dell’Editore. Nomi e marchi citati nel testo sono generalmente depositati o registrati dalle rispettive case produttrici.

3


Sommario

Sommario _________________________________________________________ 3

Presentazione ______________________________________________________ 8

Informazioni preliminari_____________________________________________ 9 Cosa bisogna sapere per affrontare questo corso? _________________________ 9 Versioni di Excel __________________________________________________ 9 Convenzioni utilizzate ______________________________________________ 9

Contenuto del corso ________________________________________________ 10

PARTE 1 Introduzione alle macro _______________________________________ 14

Lezione I - Il registratore di macro ___________________________________ 15 Prepariamoci a registrare una macro __________________________________ 15 Iniziamo a registrare_______________________________________________ 16 Il registratore è partito _____________________________________________ 17 Provare la macro _________________________________________________ 17

Lezione II – Riferimenti relativi ______________________________________ 19 Registrare una macro con i riferimenti relativi __________________________ 19 Verificare una macro con i riferimenti relativi __________________________ 20 Macro o Procedura ________________________________________________ 21 Allarme virus ____________________________________________________ 21

Lezione III - Macro di navigazione ___________________________________ 22 Navigare tra fogli _________________________________________________ 22 Scorciatoie da tastiera _____________________________________________ 23 I pulsanti________________________________________________________ 23 Raccolta di istruzioni per la gestione dei pulsanti ________________________ 24 Ping Pong _______________________________________________________ 25

Lezione IV - Macro universali _______________________________________ 26 Cartella macro personale ___________________________________________ 26 Personal.xls _____________________________________________________ 27

Lezione V – Barre degli strumenti ____________________________________ 29 Macro per visualizzare le barre… ____________________________________ 29 Una nuova barra degli strumenti _____________________________________ 31 Rifinire i pulsanti _________________________________________________ 32

Lezione VI – Moduli, la terra di nessuno_______________________________ 34 Intervallo dati denominato __________________________________________ 34 Casella combinata ________________________________________________ 35 Casella di controllo _______________________________________________ 37 Casella di selezione _______________________________________________ 38

4


Lezione VII – Un modello valutario ___________________________________ 40 Euroconvert _____________________________________________________ 40 Predisporre la casella combinata _____________________________________ 41 La funzione Indice ________________________________________________ 42 Calcolare l’Euro __________________________________________________ 43 Vincolare la quantità ______________________________________________ 43 Gestire gli eccessi_________________________________________________ 44 Considerazioni ___________________________________________________ 45

PARTE 2 Primi passi nel VBA __________________________________________ 46

Lezione VIII – L’Editor di VBA______________________________________ 47 Aprire l’Editor di VB ______________________________________________ 47 Le finestre dell’Editor di VB ________________________________________ 48 La finestra Progetto _______________________________________________ 48 La finestra delle Proprietà __________________________________________ 49 La finestra del Codice _____________________________________________ 49

Lezione IX – Il codice delle macro ____________________________________ 51 Regole generali___________________________________________________ 51 Analizzare il codice di Macro1 ______________________________________ 52 Analizzare il codice della macro RiferimentiRelativi _____________________ 53 Modificare il contenuto di una macro _________________________________ 54 I nomi delle procedure _____________________________________________ 55 Identare il codice _________________________________________________ 55 Le maiuscole ____________________________________________________ 55

Lezione X – Operare delle scelte con IF…______________________________ 56 Il file Comuni ____________________________________________________ 56 Scomporre i problemi______________________________________________ 56 Registrare la colorazione di una cella _________________________________ 57 Esaminare il codice della macro Colorare ______________________________ 58 L’enunciato Whit _________________________________________________ 59 Eliminare codice superfluo _________________________________________ 59 Colorare e scendere _______________________________________________ 60 Se…Allora ______________________________________________________ 60

Lezione XI – Ripetere le azioni _______________________________________ 62 Pulsanti alternativi ________________________________________________ 62 Rinominare un modulo_____________________________________________ 62 I cicli___________________________________________________________ 63 Do While…Loop _________________________________________________ 63 Do Until… Loop _________________________________________________ 64 Almeno una volta… _______________________________________________ 65

5


Lezione XII - Le variabili ___________________________________________ 66 Definizione di variabile ____________________________________________ 66 Non dichiarare le variabili __________________________________________ 66 Dichiarare le variabili______________________________________________ 66 Denominazione di variabili _________________________________________ 67 Tipo di dati delle variabili __________________________________________ 67 La notazione scientifica ____________________________________________ 68 Dichiarazione delle variabili e del tipo ________________________________ 68 Le costanti ______________________________________________________ 69 Portata delle variabili ______________________________________________ 69 Dichiarare le costanti ______________________________________________ 69

Lezione XIII – Comunicazioni all’utente_______________________________ 70 Una semplice finestra di messaggio___________________________________ 70 Gli argomenti di MsgBox___________________________________________ 70

Lezione XIV – Interazione con l’utente ________________________________ 74 Finestra input ____________________________________________________ 74 L’output verso una cella____________________________________________ 75 Ancora sugli argomenti ____________________________________________ 76 Metodo InputBox _________________________________________________ 76 A capo _________________________________________________________ 77

Lezione XV – Procedura decisionale __________________________________ 78 Variabili a portata di modulo ________________________________________ 78 Inserire una procedura dentro una procedura____________________________ 79 Programmazione strutturata _________________________________________ 79 Decolorare ______________________________________________________ 80

PARTE 3 Addentrarsi nel VBA _________________________________________ 81

Lezione XVI – Trova _______________________________________________ 82 Pulire l’Editor di VB ______________________________________________ 82 Una macro per trovare _____________________________________________ 83 La guida in linea__________________________________________________ 84

Lezione XVII – InputBox e Trova ____________________________________ 86 Una variabile contro la rigidità ______________________________________ 86 Adattare una macro preesistente _____________________________________ 86 Una immagine come pulsante _______________________________________ 87

6


Lezione XVIII – Gestione degli errori _________________________________ 88 Errori di linguaggio o di sintassi _____________________________________ 88 Errori di runtime__________________________________________________ 88 Errori logici _____________________________________________________ 88 Il primo errore ___________________________________________________ 89 Ancora logica ____________________________________________________ 89 Un errore da runtime ______________________________________________ 90 Un caso ambiguo _________________________________________________ 90 Un errore “ortografico” ____________________________________________ 91 Un errore “spaziale”_______________________________________________ 91 Non finiscono mai ________________________________________________ 91

Lezione XIX – Debug _______________________________________________ 92 Punti di interruzione_______________________________________________ 92 Effettuare il debug ________________________________________________ 92 Intercettare gli errori di runtime______________________________________ 94

Lezione XX – I forms _______________________________________________ 95 Il primo form ____________________________________________________ 95 Forms e pulsanti __________________________________________________ 96 Personalizzare il pulsante tramite la finestra delle Proprietà. _______________ 96 Evento clic ______________________________________________________ 97 Pubbliche e Private________________________________________________ 98

Lezione XXI – Forms: atto II ________________________________________ 99 Dare “vita” ad un Form ____________________________________________ 99 Chiudere un Form _______________________________________________ 100 Aggiungere un altro pulsante _______________________________________ 100 Duplicare i pulsanti ______________________________________________ 101

Lezione XXII – Allora Se___________________________________________ 102 Chiedere conferma _______________________________________________ 102 Scegliere un colore_______________________________________________ 103 I Pulsanti di opzione______________________________________________ 103 If…Then…Else If…Else…End If ___________________________________ 104 Case __________________________________________________________ 106

Lezione XXIII – Ciclo For … Next___________________________________ 107 Ricavare dati da una cella _________________________________________ 108 Errore di Overflow _______________________________________________ 109 Proteggere e sproteggere __________________________________________ 109

Lezione XXIV – Nidificare For … Next_______________________________ 111 Il codice di ordinamento___________________________________________ 111

7


Parte 4 Controllare Excel _____________________________________________ 113

Lezione XXV – Le Function ________________________________________ 114 Tipi di routine___________________________________________________ 114 Creare una nuova funzione per Excel ________________________________ 114 Analisi del problema _____________________________________________ 115 Come trovare l’importo netto_______________________________________ 115 CompensoNetto _________________________________________________ 116

Lezione XXVI – Manipolare i dati ___________________________________ 118 Costruire l’esempio ______________________________________________ 118 Funzioni Len e Left ______________________________________________ 119 Ottimizzare_____________________________________________________ 120 Passare i dati ad un nuovo foglio ____________________________________ 121 Pulire solo se… _________________________________________________ 123

Lezione XXVII – Auto_Apri_Chiudi _________________________________ 124 Auto_Apri _____________________________________________________ 124 Auto_Chiudi____________________________________________________ 125 Un po di storia __________________________________________________ 125 Istruzioni varie __________________________________________________ 125 Esempi di Auto_Apri e Auto_Chiudi_________________________________ 126 Proteggere _____________________________________________________ 127

Lezione XXVIII – I macro virus _____________________________________ 128 Come difendersi _________________________________________________ 128 Difesa artigianale ________________________________________________ 129

Appendici __________________________________________________________ 130

Un’animazione con Excel __________________________________________ 131 Il codice _______________________________________________________ 132

Controllare la risoluzione dello schermo ______________________________ 134 Impostazioni____________________________________________________ 134 Listato_________________________________________________________ 135 Il commento del codice ___________________________________________ 136

File allegato ______________________________________________________ 138

Conclusioni ______________________________________________________ 139

Indice rapido_____________________________________________________ 140

8


Presentazione

A partire dalla lontana versione 5.0 (1993), Excel primo fra i programmi della Microsoft, introdusse un nuovo strumento di automatizzazione, il Visual Basic per Applicazioni. Il VBA (acronimo di Visual Basic for Application) è un vero e proprio linguaggio di programmazione, che appartiene alla famiglia del Visual Basic e ne possiede identica sintassi e struttura, ma a differenza di quest’ultimo contiene degli oggetti peculiari di un foglio elettronico. Grazie al connubio fra Excel e il VBA è stata data la possibilità, ad un gran numero di utenti, di creare potenti sistemi informativi completamente automatizzati, prerogativa sino a non molto tempo fa riservata ai soli programmatori professionisti. La strategia della Microsoft, a partire da Office 97, ha introdotto il VBA in tutte le sue applicazioni, in modo tale da diventare un unico linguaggio di programmazione, nella specificità dei rispettivi ambienti di lavoro. Ciò ha favorito al massimo grado quella sinergia fra programmi, quali Excel ed Access per esempio, da tanti invocata. Nonostante tutte queste premesse favorevoli, in Italia il VBA non ha incontrato il successo che ci si poteva attendere, come ad esempio ha avuto negli Stati Uniti. Soprassedendo sulle varie ragioni culturali ed economiche, la scarsa diffusione del VBA è probabilmente da ricercare nelle difficoltà che gli utenti incontrano nell’apprendere la sintassi di un linguaggio di programmazione. Il primo passo che occorre intraprendere per rendere la curva di apprendimento del VBA meno erta è proprio quella di avvicinarvisi dal “versante” di Excel, che grazie al suo Registratore di Macro rende tutto molto più semplice. Il secondo passo lo compie questo corso, durante il quale si cercherà di accompagnare gli utenti con esempi pratici e concreti, senza avventurarsi in dissertazioni e finezze teoriche, sicuramente fondamentali per un programmatore professionista, ma non indispensabili per un utente evoluto che vuole essere subito produttivo. Insomma quello che vi trovate innanzi è un corso di programmazione per non programmatori grazie al quale sarete in grado di costruire applicazioni molto potenti. Novembre 2001

9


Informazioni preliminari

Cosa bisogna sapere per affrontare questo corso?

Per affrontare proficuamente l’apprendimento del VBA non è necessario avere avuto precedenti esperienze di programmazione, ma è sicuramente indispensabile conoscere bene Excel.

Versioni di Excel

Pur essendo le immagini di corredo alle lezioni tratte da Excel 10 (Office XP), si possono utilizzare tranquillamente anche le relase 9 (Office 2000) e 8 (Office 97). Più problematica si presenta la situazione per chi ancora dispone di Excel 7 (Office 95), o addirittura la mitica versione 5 per Windows 3.1. Naturalmente non è assolutamente possibile utilizzare degli autentici fossili tipo Excel 4.

Convenzioni utilizzate

Esempio di convenzione Descrizione

Debug Le parole in corsivo evidenziano elementi di Excel o del VBA.

SaltaErrore Il grassetto indica i nomi attribuiti alle procedure, alle variabili, ai fogli di lavoro e alle celle.

Do While ActiveCell <> “” Il font Verdana con rientro sporgente evidenzia codice di esempio.

Il testo bordato indica tasti, combinazioni di tasti o pulsanti.

Testo su sfondo grigio, indica note o approfondimenti.

CTRL+C

Nota: Non fate …

10


Contenuto del corso

INTRODUZIONE ALLE MACRO

LEZIONE I – Il registratore di macro Prendere confidenza con il registratore di macro e verificarne il funzionamento.

LEZIONE II – Riferimenti relativi Registrare ed eseguire una macro. Apprendere la differenza fra macro con riferimenti assoluti e macro con riferimenti relativi.

LEZIONE III –Macro di navigazione Navigare all’interno di un foglio e fra fogli. Associare le macro registrate a dei pulsanti.

LEZIONE IV – Macro universali Rendere le macro sempre disponibili. I segreti di Personal.xls.

LEZIONE V –Barre degli strumenti Registrare una serie di azioni e associarle ai pulsanti di una nuova Barra degli strumenti.

LEZIONE VI – Moduli, la terra di nessuno I controlli della Barra degli strumenti Moduli. Esaminare il funzionamento della Casella combinata, della Casella di controllo e della Casella di selezione: controlli direttamente disponibili nel foglio di lavoro.

LEZIONE VII –Un modello valutario Utilizzando gli strumenti presentati nella precedente lezione e una buona conoscenza del foglio elettronico costruiremo un programma di conversione di alcune valute Europee in Euro.

11


PRIMI PASSI NEL VBA

LEZIONE VIII – L’Editor di VBA Descrizione dell’ambiente di programmazione.

LEZIONE IX –Il codice delle macro Leggere, analizzare e modificare il codice della macro precedentemente registrate.

LEZIONE X – Operare delle scelte con If…Then Scrivere routine che verificando determinate condizioni eseguono operazioni diverse a seconda del risultato.

LEZIONE XI – Eseguire il ciclo Do While…Loop Le strutture a ciclo consentano di eseguire ripetutamente una serie di istruzioni.

LEZIONE XII – Le variabili Cosa sono le variabili. Tipi di variabili. Dichiarare le variabili.

LEZIONE XIII – Comunicazioni all’utente Usare MsgBox (Finestra messaggio).

LEZIONE XIV – Interazione con l’utente Usare InputBox (Finestra di input)

LEZIONE XV –Procedura decisionale Usando InputBox e MsgBox creare una procedura che agisce secondo le decisioni prese dall’utente.

12


ADDENTRARSI NEL VBA

LEZIONE XVI – Trova Implementare con il VBA una procedura di ricerca.

LEZIONE XVII – InputBox e Trova Rendere più flessibile la ricerca di un campo sfruttando le potenzialità di InputBox.

LEZIONE XVIII – Gestire gli errori Imparare ad intercettare gli errori per rendere le procedure più professionali.

LEZIONE XIX – Debug Esaminare una procedura durante l’esecuzione passo passo per verificarne il funzionamento.

LEZIONE XX – I Forms Costruire una Finestra di dialogo.

LEZIONE XXI –Forms. Atto II Approfondire le modalità di gestione di un form.

LEZIONE XXII – Allora Se Potenziare le procedure precedentemente create con la struttura di controllo If…Then...ElseIf…Else…End If e i Pulsanti di opzione. Ottimizzare con la struttura di controllo Case.

LEZIONE XXIII – Ciclo For…Next For, routine da utilizzare quando si è a conoscenza del numero di volte che le istruzioni di un ciclo vanno ripetute. Il suo impiego facilita la creazione di piccole utilità. Passare ad una procedura il valore contenuto in una cella.

LEZIONE XXIV – Nidificare For…Next Inserire un ciclo For…Next all’interno di un altro ciclo For…Next, aumenta enormemente le nostre possibilità in Excel.

13


CONTROLLARE EXCEL

LEZIONE XXV – Le Function Differenze fra Sub e Function. Con lo strumento Function è possibile creare nuove funzioni per il foglio di lavoro Excel.

LEZIONE XXVI – Manipolare i dati Costruzione di una procedura che ripulisce il contenuto delle singole celle di una colonna da caratteri superflui, che poi copia in un altro foglio di lavoro. Sarà introdotto l’uso delle funzioni interne Len e Left..

LEZIONE XXVII – Auto_Apri_Chiudi Con l’ausilio di particolari macro è possibile eseguire delle routine in fase di avvio e di chiusura di Excel. Piccola carrellata di routine adibite a potenziare gli automatismi delle nostre procedure.

LEZIONE XXVIII – I macro virus Come difendersi dai macro virus

Appendice Un’animazione per misurare le prestazioni del PC.

14


PARTE 1 Introduzione alle macro

In questa prima fase del corso impareremo ad usare il Registratore di macro. Si tratta di uno strumento piuttosto limitato, ma il suo uso rappresenta un buon trampolino di lancio per imparare a programmare con il VBA. Affrontando lo studio della cartella Personal.xls impareremo a rendere globali le nostre macro. Infine, grazie al sapiente utilizzo dei controlli offerti dalla barra degli strumenti Moduli, impareremo a creare applicazioni sorprendenti senza usare una sola parola di codice. Al corso è allegato un file di Excel, di nome Comuni, contenente, oltre ai soliti tre fogli vuoti, un foglio denominato Comuni, contenente l’elenco di tutti i comuni della provincia di Torino. A partire dalla nona lezione dovrete utilizzare i dati presenti nel foglio di lavoro citato nel precedente paragrafo. E’ comunque consigliato usare la cartella di lavoro Comuni sin dalla prima lezione.

15


Lezione I - Il registratore di macro

Il Registratore di Macro è un mezzo per memorizzare e riutilizzare le azioni compiute con la tastiera e/o con il mouse, permettendo così di creare delle procedure automatizzate chiamate Macro. Le Macro eseguono sequenze di azioni molto più rapidamente di noi.

Prepariamoci a registrare una macro

Prima di iniziare la registrazione, dobbiamo pianificare le azioni che vorremo successivamente far eseguire alla macro, poiché eventuali ripensamenti che dovessero sopraggiungere durante la registrazione saranno impietosamente memorizzati e quindi riprodotti dalla macro. Noi, ad esempio, vogliamo grassettare le intestazioni della tabella dati riportata nell’immagine sottostante.

Per effettuate l’operazione voluta dovremo spostarci dalla cella A1 alla cella A3, selezionare l’intervallo A3:F3 per poi cliccare sul comando grassetta. Certo in questo caso le operazioni da effettuare sono molto semplici, ma se la registrazione da fare ci richiedesse un buon numero di azioni, sarebbe consigliabile effettuare una o più prove senza avviare il registratore. La possibilità di compiere degli errori è molto alta.

NOTA: è importante porre molta attenzione nelle azioni che registreremo, poiché anche la più piccola variazione può creare ripercussioni assolutamente imprevedibili.

16


Iniziamo a registrare

Dal menu Strumenti scegliere Macro per espandere una ulteriore tendina e cliccare sulla voce Registra nuova macro… .

Sarà visualizzata una finestra di dialogo denominata Registra Macro.

In questa prima registrazione non ci soffermeremo sulle opzioni che ci vengono offerte, lasceremo invariato anche il poco fantasioso nome di default Macro1. Cliccare su OK per confermare l’inizio della registrazione.

17


Il registratore è partito

In apparenza non sembra essere successo niente se escludiamo la presenza di un nuovo pulsante nell’area di lavoro. Ad essere precisi il pulsante è una barra degli strumenti che prende il nome di Interrompi registrazione e serve appunto a bloccare la registrazione della macro. Iniziamo ad eseguire le operazioni precedentemente provate: spostarsi dalla cella A1 alla cella A3, selezionare l’intervallo A3:F3 per poi cliccare sul comando grassetta. Infine deselezioniamo attivando la cella A2. Finito il nostro compito “stoppiamo” la registrazione cliccando sull’apposito pulsante.

NOTA: Quando volete fermare la registrazione fate attenzione a non posizionarvi sulla X, poiché in tal caso chiudereste la barra degli strumenti Interrompi registrazione, lasciando il registratore di macro “beatamente acceso”.

Provare la macro

Adesso verifichiamo il nostro operato. Propedeuticamente selezioniamo nuovamente l’intervallo A3:F3 e togliamo la grassettatura, altrimenti risulterà difficile valutare l’effetto della nostra prima macro Quindi dal menu Strumenti\Macro scegliamo la voce Macro… .

18


Sarà visualizzata la finestra di dialogo Macro, al momento ospitante la sola Macro1. Cliccando sul pulsante Esegui la macro sarà avviata…

… e come d’incanto le intestazioni della tabella saranno grassettate. Proviamo a ripetere l’esecuzione della macro, spostandoci però, prima di avviarla, nella cella C5 e togliendo la grassettatura. Anche in questo caso la macro viene eseguita correttamente. Ma allora perché abbiamo fatto questa prova? Portate pazienza, con la prossima registrazione scopriremo l’arcano.

19


Lezione II – Riferimenti relativi

Registrare una macro con i riferimenti relativi

Iniziamo a registrare una seconda macro, avendo avuto cura di eliminare l’eventuale grassettatura dalle intestazioni della tabella e di posizionarsi sulla cella A1.

• Dal menu Strumenti scegliere Macro per espandere una ulteriore tendina e cliccare sulla voce Registra nuova macro… .

• Questa volta sostituiamo il nome predefinito Macro2 con il nuovo nome RiferimentiRelativi.

• Quindi clicchiamo su OK per dare inizio alla nuova registrazione.

• Individuiamo nell’area di lavoro la barra degli strumenti Interrompi registrazione e prima di effettuare una qualsiasi azione cliccare sul pulsante Riferimento relativo.

• Spostarsi dalla cella A1 alla cella A3, selezionare l’intervallo A3:F3 per poi cliccare sul comando grassetta

• Infine deselezionare attivando la cella A2.

• Interrompiamo la registrazione cliccando sull’apposito pulsante.

20


Verificare una macro con i riferimenti relativi

• Posizioniamoci sulla cella A1 e dal menu Strumenti\Macro clicchiamo sulla voce Macro per visualizzare la finestra di dialogo Macro.

• Selezioniamo la nuova macro RiferimentiRelativi e clicchiamo sul pulsante Esegui.

Anche questa volta la macro eseguirà il suo lavoro correttamente. Ma adesso proviamo a rieseguire RiferimentiRelativi posizionando la cella attiva in C5. Ricordiamoci di togliere preventivamente la grassettatura dalle intestazioni. Constaterete che in questo caso la macro non ha funzionato. Vediamo di capire il perché. La prima macro, Macro1, funziona sempre indipendentemente dalla cella attiva al momento della sua esecuzione, poiché è una macro con riferimenti assoluti; “essa sa” che deve iniziare in ogni caso il suo lavoro dalla cella A3. Invece RiferimentiRelativi, come dice il suo nome è una macro con riferimenti relativi. Durante la registrazione ci siamo spostati dalla cella A1 alla cella A3, cioè siamo scesi in basso di due celle. Quindi la nostra macro, essendo relativa, non ha registrato lo spostamento sulla cella A3, bensì ha registrato uno spostamento in basso di due celle. Pertanto essa è strettamente condizionata dalla cella di partenza. Si potrebbe dire che le macro con riferimenti assoluti sono più sicure, ma più rigide potendo essere utilizzate solo in locazioni non modificabili, mentre le macro con riferimenti relativi sono flessibili ma più difficili da gestire. In realtà le applicazioni pratiche di queste macro sono estremamente limitate, il loro vero valore è di tipo didattico e ce ne renderemo bene conto quando passeremo ad esaminare il codice che hanno generato nelle esercitazioni precedenti.

21


Macro o Procedura

I termini Macro e Procedura, secondo alcuni autori, non sono equivalenti. Si attribuisce l’appellativo macro al codice prodotto con il registratore di macro, mentre si definisce procedura ciò che viene scritto completamente a “mano”, senza l’ausilio del registratore. In realtà il codice che viene prodotto con il VBA di Excel è spesso il risultato combinato dell’uso del registratore e di scrittura diretta del codice: pertanto, distinguere i due termini risulterebbe alquanto pleonastico, conseguentemente in questo corso le due definizioni saranno considerate sinonimi.

Allarme virus

Sappiamo tutti che la nostra cartella di lavoro, avendo utilizzato il registratore, in fase di apertura visualizzerà una finestra segnalante la presenza di macro, potenziali veicolatici di virus. Nessun allarmismo in questo caso, clicchiamo pure su Attiva macro, ma attenzione a non abbassare la guardia con file di altra provenienza.

22


Lezione III - Macro di navigazione

In questa terza lezione continueremo ad usare il Registratore generando delle macro che, per quanto semplici, si riveleranno anche molto utili. Diciamo che alla fine di questa lezione , se dotati di un buon spirito d’inventiva potremo costruire un primo rudimentale sistema informativo.

Navigare tra fogli

Spostarsi fra i fogli di lavoro di una cartella, servendosi delle apposite schede, non è sempre agevole, soprattutto per gli utenti non molto avvezzi all’uso del pc. Inoltre in certi casi si preferisce disattivare la visualizzazione delle schede, per poter così creare una navigazione guidata tra i fogli. Ma nel caso testé prospettato, in che modo le macro possono aiutarci? Innanzi tutto registriamo una macro che ci porti al Foglio2.

• Attivare il Foglio1 poiché per poter registrare lo spostamento al Foglio2 è chiaramente indispensabile avviare la registrazione da una locazione diversa da quella di destinazione.

• Portarsi sul menu Strumenti\Macro e avviare il Registratore di macro.

• Denominare la macro Navigator e confermare cliccando su OK.

• Attivare il Foglio2 e interrompere la registrazione.

Verifichiamo il funzionamento di Navigator.

• Portarsi nel Foglio1.

• Dal menu Strumenti\Macro scegliere Macro.

• Eseguire la macro denominata Navigator.

• Verremo “trasportati” nel Foglio2. Però, se nei nostri intendimenti vi era l’obiettivo di rendere più facile la “vita” degli utenti, dovremo operare qualche ulteriore accorgimento.

23


Scorciatoie da tastiera

Mettiamo nuovamente mano al registratore, avviandolo dal menu Strumenti\Macro\Registra nuova macro. Lasciamo invariato il nome della macro proposto da Excel e spostiamo il nostro interesse alla casella di controllo Tasto di scelta rapida che presenta preimpostato il tasto CTRL.

Se noi inserissimo nella casella di controllo la lettera M ad esempio, per avviare la macro associata, basterebbe digitare la combinazione di tasti CTRL+M. Il problema è che sono molti i comandi associati alla combinazione CTRL più lettera dell’alfabeto, quindi è più prudente utilizzare la combinazione di tasti CTRL+SHIFT+M o una qualche altra lettera. Per operare una simile combinazione di tasti occorre tener schiacciato il tasto SHIFT mentre viene digitata la lettera da associare. Non male! Peccato che le scorciatoie da tastiera siano un ottimo strumento per utenti esperti, non certo per neofiti!

I pulsanti

Indubbiamente il sistema più comodo e facile per far eseguire delle operazioni a chiunque, è quello di usare un pulsante.

• Dal menu Visualizza\Barre degli strumenti scegliere Moduli.

• Individuare l’oggetto Pulsante e cliccarci sopra.

• Spostare il puntatore del mouse in una zona libera del foglio ed effettuare un clic.

Sarà automaticamente generato un pulsante ed avviata la finestra di dialogo Assegna macro, dalla quale bisognerà selezionare il nome della macro di nostro interesse, che in questo caso sarà Navigator. Confermare cliccando su OK.

24


A questo punto, senza null’altro fare, digitare il nuovo nome per il pulsante che, per rendere chiara la sua funzione sarà Foglio 2. Deselezionarlo eseguendo un clic a “vuoto”. Posizionando il puntatore del mouse sulla nuova creazione lo vedremo trasformarsi in una “manina” ad indicare la sua associazione ad una macro. Ciccandoci sopra sarete proiettati nel Foglio2.

Raccolta di istruzioni per la gestione dei pulsanti

• Per poter spostare, dimensionare o rinominare un pulsante devono essere presenti le maniglie.

• Nel caso gli sia associata una macro cliccandoci sopra questa sarà eseguita rendendo impossibile far comparire le maniglie. In tal caso prima di cliccare sull’oggetto, tenere premuto il tasto CTRL.

• Non volendo associare una macro al pulsante all’atto della creazione occorrerà cliccare su Annulla nella finestra di dialogo Assegna macro.

• Per associare una macro ad un pulsante cliccarci sopra con il tasto destro del mouse e dal menu contestuale scegliere Assegna macro.

25


Ping Pong

Grazie al pulsante precedentemente creato possiamo attivare il Foglio2: ma adesso vogliamo anche ritornare indietro, sempre utilizzando un pulsante.

• Attivare il Foglio2.

• Dal menu Strumenti\Macro scegliere Registra nuova macro.

• Rinominare la macro Navigator2.

• Portarsi nel Foglio1 ed interrompere la registrazione.

• Finita la fase di registrazione ritornare nel Foglio2.

• Dalla barra degli strumenti Moduli selezionare l’oggetto Pulsante e cliccare in una zona vuota del foglio per generarlo.

• Associargli la macro Navigator2.

• Rinominare il pulsante Indietro e deselezionarlo cliccando a “vuoto”.

A questo punto sarete in grado di passare dal Foglio1 al Foglio2 e viceversa cliccando sui due pulsanti. Per rendere più evidente le potenzialità del lavoro svolto in questa lezione, dal menu Strumenti scegliete la voce Opzioni, attivate la scheda Visualizza e quindi deselezionate l’opzione Schede. Noterete che per muoversi fra i fogli della cartella sarete obbligati a servirvi dei pulsanti, quindi ad una navigazione guidata.

Nota: Per spostarsi da un foglio di lavoro all’altro si possono anche utilizzare le combinazioni di tasti: CTRL+Pag� , per passare al foglio successivo, o CTRL+Pag� per tornare al foglio precedente.

26


Lezione IV - Macro universali

E’ giunto il momento di conoscere più approfonditamente la finestra di dialogo Registra macro.

Cartella macro personale

Tutti gli elementi della finestra di dialogo Registra macro sono di immediata comprensione. Tutti , escludendo la voce Memorizza macro in : , la quale ospita un piccolo elenco di possibilità fra le quali scegliere.

• Questa cartella di lavoro o Scelta di default. o Le macro saranno contenute nella cartella di lavoro nella

quale sono state registrate. o Le suddette macro saranno disponibili per le altre cartelle solo

se la cartella che le contiene è aperta.

• Cartella macro personale o Le macro non saranno contenute nella cartella dalla quale è

stata effettuata la registrazione. o Le macro saranno sempre disponibili, indifferentemente dalla

cartella aperta. o Nell’eventualità la cartella sia esportata in un altro pc, le

macro (quelle registrate con la modalità Cartella macro personale) non saranno disponibili.

• Nuova cartella di lavoro o Opzione di scarsa utilità.

27


Personal.xls

Quando registriamo una macro in modalità Cartella macro personale , questa non viene memorizzata nella cartella in uso, bensì in una cartella nascosta che si chiama Personal.xls.

Nota : La cartella Personal.xls non esiste come file sino a quando non viene effettuata una registrazione in modalità Cartella macro personale.

Ogni volta che Excel viene avviato il file Personal.xls, pur rimanendo nascosto, viene aperto, mettendo le sue macro a disposizione delle altre cartelle aperte. E’ possibile visualizzare la cartella Personal.xls scegliendo dal menu Finestra la voce Scopri, che visualizza l’omonima finestra di dialogo.

E’ comunque consigliabile nascondere Personal.xls cliccando su Nascondi sempre dal menu Finestra.

Nota: I macro virus che affliggano Excel hanno la consuetudine di eleggere a loro quartiere generale Personal.xls.

28


Ma dove è sita la cartella Personal.xls ? Non troppo complicato da esporre con Windows 95/98.

PERCORSO DESCRIZIONE

C:\ O altra unità disco Programmi\

Microsoft Office\ O similare

Office\

XLStart\

Personal.xls

Più complesso con Windows 2000.

PERCORSO DESCRIZIONE

C:\ O altra unità disco

Documents and settings\

Enrico Cannoni\ Il nome dell’utente

Dati applicazioni\

Microsoft\

Excel\

XLStart\

Personal.xls

I files ospitati all’interno della directori XLStart vengono eseguiti automaticamente all’avvio di Excel.

29


Lezione V – Barre degli strumenti

In questa lezione vedremo come sfruttare al meglio gli strumenti di personalizzazione delle barre degli strumenti offerti da Excel. Il nostro compito sarà quello di creare una piccola barra degli strumenti, disponibile da tutte le cartelle del nostro computer, dalla quale richiamare le barre di uso più frequente.

Macro per visualizzare le barre…

• Selezionare il menu Visualizza\Barre degli strumenti e deselezionare le barre Standard e Formattazione.

• Dal menu Strumenti\Macro lanciare Registra nuova macro.

• Denominare la macro Standard e memorizzarla in Cartella macro personale.

• Confermare l’avvio della registrazione cliccando su OK.

• Dal menu Visualizza\Barre degli strumenti attivare la barra Standard.

• Interrompere la registrazione. Siccome è caldamente consigliato verificare sempre il funzionamento della macro registrata, disattivare nuovamente la barra degli strumenti Standard e richiamare la finestra di dialogo Macro dal menu Strumenti\Macro.

Nota: E’ interessante osservare che la macro Standard a differenza delle altre è preceduta da Personal.xls! , il file ove è ospitata.

30


Selezionare Standard e cliccare sul pulsante Esegui.

La barra degli strumenti Standard sarà immediatamente ripristinata.

Nota: Nell’eventualità che la macro Standard non fosse disponibile nell’elenco della finestra di dialogo Macro, prescindendo da un vostro errore, controllate che la casella Macro in: sia impostata su Tutte le cartelle di lavoro aperte.

Seguendo i punti da 1 a 6, precedentemente illustrati, si proceda a registrare le macro Formattazione, Disegno e Moduli, attivanti le omonime barre degli strumenti.

31


Una nuova barra degli strumenti

Creare l’involucro della nuova barra degli strumenti è un’operazione molto semplice.

• Fare clic su Personalizza dal menu Visualizza\Barre degli strumenti.

• Nella finestra di dialogo Personalizza attivare la scheda Barre degli strumenti.

• Cliccare sul pulsante Nuova… .

Apparirà una nuova barra degli strumenti, priva di ogni pulsante che, salvo diversa scelta, si chiamerà Personalizzata1.

• Nella finestra di dialogo Personalizza scegliere la scheda Comandi per aggiungere dei pulsanti alla barra degli strumenti Personalizzata1.

• Nel riquadro di sinistra, Categorie scegliere Macro.

• Dal riquadro di destra, Comandi, trascinate Pulsante personalizzato, la faccina che ride, all’interno della barra degli strumenti in costruzione.

32


• Cliccare con il tasto destro del mouse sul pulsante e dal menu contestuale che apparirà, scegliere Assegna macro.

• Dalla finestra di dialogo Macro scegliere la macro Standard e confermare cliccando su OK.

• Chiudere la finestra di dialogo Personalizza.

• Verificare il funzionamento del tutto.

Rifinire i pulsanti

Il tutto funziona bene, l’unico neo è rappresentato dall’etichetta autoesplicativa che, compare quando si posiziona il puntatore del mouse sul pulsante, poiché la dicitura Pulsante personalizzato è eccessivamente generica.. Dopo aver aperto la finestra di dialogo Personalizza, cliccare con il tasto destro del mouse sul pulsante e nel conseguente menu contestuale portarsi sulla voce Nome e rinominare il predefinito Pulsante personalizzato in Barra Standard. Adesso possiamo inserire nella nostra barra degli strumenti un secondo pulsante. Seguiamo le istruzioni precedentemente illustrate, con l’unica differenza di associare la macro denominata Formattazione, che permette la visualizzazione dell’omonima barra degli strumenti.

33


Per rendere questo secondo pulsante distinguibile dal primo, clicchiamoci sopra con il tasto destro del mouse e dal menu contestuale che apparirà, posizionarsi su Cambia icona pulsante per disporre di una discreta raccolta di icone per pulsanti.

Ripetere le operazioni descritte precedentemente anche per le macro Disegno e Moduli. A questo punto potrete verificare che la nuova barra degli strumenti è disponibile per qualsiasi cartella di lavoro del vostro computer, o profilo per gli utilizzatori di NT.

34


Lezione VI – Moduli, la terra di nessuno

Generalmente si ritiene che la creazione di interfacce grafiche con caselle combinate, caselle di comando e altri controlli visivi siano un’attività esclusiva degli sviluppatori. Con Excel è possibile disporre di controlli grafici senza conoscere una sola parola di programmazione. Tale possibilità ci è data dagli strumenti disponibili sulla barra degli strumenti Moduli, già sperimentata nella seconda lezione, che ci permette di inserire i suoi controlli direttamente su un foglio di lavoro. Possiamo dire di avventurarci in un territorio inesplorato sia dagli utenti di Excel che dai programmatori, insomma in una terra di nessuno.

Intervallo dati denominato

Inizieremo il nostro studio sui controlli della barra degli strumenti Moduli, prendendo in esame la Casella Combinata.

Nota: Nell’eventualità non fosse chiaro di quale oggetto stiamo parlando, pensate ai vari menu a tendina usati per cambiare il tipo di font e le dimensioni dei caratteri.

Per poter vedere una Casella combinata in funzione occorre disporre di un intervallo di dati denominato.

• Partendo dalla cella A2 di un foglio intonso, si digitino i nomi della città visibili nell’immagine sottostante.

• Selezionare l’intervallo di dati A2:A8.

• Scegliere Crea dal menu Inserisci\Nome.

• Apparirà la finestra di dialogo Crea nomi e dopo aver controllato che sia selezionata la sola opzione Riga superiore, confermare cliccando su OK.

35


Verifichiamo l’effetto dell’intervallo denominato testé creato.

• Fare clic sul pulsante della Casella del nome e selezionare il nome Città dall’intervallo dell’elenco a discesa.

• L’effetto che si otterrà sarà la selezione di tut ti i nomi di città sottostanti l’etichetta Città, che sarebbe il nome dell’intervallo dati denominato.

Nota: In realtà la Casella del nome non è altro che una Casella combinata, quindi l’esatta dicitura sarebbe: cliccare sulla casella combinata Casella del nome.

Casella combinata

Passiamo alla vera e propria creazione della Casella combinata.


• Sfruttando le proprietà di autoesplicazione dei pulsanti, cercare il controllo Casella combinata e cliccarci sopra.

• Posizionarsi sulla cella C4 e modellare un riquadro grande all’incirca come due celle.

• Fare clic con il tasto destro sulla Casella combinata or ora creata e dal menu contestuale scegliere la voce Formato controllo.

36


• Apparirà la finestra di dialogo Formato controllo ove dovremo selezionare la scheda Controllo.

• Nella casella Intervallo di input: digitare Città, nella casella Collegamento cella: digitare C1 e cliccare su OK.

• Deselezionare l’oggetto cliccando a “vuoto”. Cliccando sul pulsantino della Casella combinata vedremo comparire la lista delle città e cliccando su una di esse, ad esempio Milano, nella cella C2 comparirà il numero 2. Provando a fare clic su Firenze in C1 sarà inserito il 4.

37


Cerchiamo di capire come siamo riusciti ad ottenere un simile risultato. Digitando Città nella casella Intervallo di input della finestra di dialogo Formato controllo, abbiamo “detto” alla Casella combinata: Guarda “casella” che tu devi contenere l’elenco di nomi corrispondente all’intervallo di dati Città, cioè Torino, Milano, Venezia ecc. .Poi, la città che sceglierò dovrai indicarla nella cella C1, la cella appunto digitata in Collegamento cella. Ma che cosa è che quel numero che compare in C1? Semplice, è il numero di posizione nell’elenco dell’elemento scelto. Milano è il secondo, quindi corrisponde al numero 2.

Casella di controllo

La Casella di controllo, altro oggetto molto diffuso, serve ad indicare se vogliamo o non volgiamo fare una certa cosa, se è vero o non è vero o meglio è VERO o è FALSO.

• Dalla barra degli strumenti Moduli cliccare sull’oggetto Casella di controllo.

• Fare clic in corrispondenza della cella C9, sarà automaticamente generata una Casella di controllo.

• Cliccare con il tasto destro sulla Casella di controllo per far apparire il menu contestuale, dal quale selezionare Formato controllo.


• Nella casella Collegamento cella digitare B9.

• Deselezionare l’oggetto cliccando a “vuoto”.

38


Cliccando sulla Casella di controllo vi comparirà un segno di spunta e nella cella B9, quella indicata in Collegamento cella, VERO. Cliccando nuovamente sulla Casella di controllo il segno di spunta sarà tolto e in B9 comparirà FALSO.

Casella di selezione

• Dalla barra degli strumenti Moduli cliccare sull’oggetto Casella di selezione.

• Fare clic in una zona vuota del foglio per generare automaticamente una Casella di selezione.

• Fare clic con il tasto destro sulla Casella di selezione per far apparire li menu contestuale, dal quale selezionare Formato controllo.

• Selezionare la scheda Controllo e digitare: o Valore corrente 10 o Valore minimo 10 o Valore massimo 100 o Avanzamento 5 o Collegamento cella H1

• Cliccare su OK.

• Deselezionare il controllo.

39


Adesso cliccando sulla sezione superiore della Casella di selezione vedrete il valore in H1 aumentare di 5 in 5 sino ad un massimo di 100. Cliccando sulla parte inferiore diminuirà di 5 in 5 sino ad un minimo di 10. Probabilmente, dopo un primo momento di soddisfazione, vi sorgerà un dubbio. Ma a che servono questi controlli? Portate pazienza, nella prossima lezione realizzeremo una interessante applicazione che ricorrerà agli oggetti testé studiati.

Nota: Nei controlli ove si imposta un valore massimo non è possibile superare 30.000.

40


Lezione VII – Un modello valutario

Questa lezione potrebbe risultare particolarmente impegnativa, soprattutto per chi non fosse dotato di una adeguata conoscenza di Excel. Ma quì cogliamo l’occasione per ribadire che, soltanto chi già conosce bene il foglio elettronico potrà trarre pieno giovamento da questo corso di VBA. Excel è dotato di una funzione, Euroconvert, in grado di convertire il valore delle valute Europee in euro . Ma se per un utilizzatore di Excel di medie capacità, ricorrere ad una funzione non rappresenta certo un problema, ben diversa è la situazione per quegli utenti sprovveduti di conoscenze sul foglio elettronico. Il nostro compito sarà quello di creare un programma di conversione in euro dall’interfaccia amichevole.

Euroconvert

Diamo una rapida occhiata alla sintassi delle funzione Euroconvert. =Euroconvert(Numero ; Origine; Destinazione)

Numero Importo da convertire.

Origine Il codice ISO della moneta di origine.

Destinazione Il codice ISO della moneta di destinazione.

Nell’esempio sottostante vediamo come convertire 1000 lire in euro.

=Euroconvert(1000;”itl”;”eur”)

Il risultato è € 0,52

Nota: Nella guida di Excel troverete tutti i ragguagli relativi alla funzione Euroconvert.

41


Predisporre la casella combinata

L’utilizzo di una Casella combinata prevede un intervallo dati denominato.

• Partendo dalla cella J2 di un foglio vuoto, digitare i dati sottostanti.

Paese Moneta CodiceISO Francia Italia Paesi Bassi Germania Spagna

Franco Lira Fiorino Marco Peseta

frf itl nlg dem esp

• Selezioniamo l’intervallo di dati K2:L7.

• Scegliere Crea dal menu Inserisci\Nome.

• Nella finestra di dialogo Crea nomi lasciare selezionata solo Riga superiore e confermare cliccando su OK.

Nota: In realtà abbiamo creato due intervalli denominati, Moneta e CodiceISO.

Passiamo alla creazione di una Casella combinata.


• Sfruttando le proprietà di autoesplicazione dei pulsanti cercare nella barra Moduli il controllo Casella combinata e cliccarci sopra.

• Posizionarsi sulla cella E5 e modellare un riquadro grande all’incirca come due celle.

• Fare clic con il tasto destro sulla Casella combinata or ora creata e dal menu contestuale scegliere la voce Formato controllo.


• Nella casella Intervallo di input: digitare il nome dell’intervallo denominato Moneta, nella casella Collegamento cella: digitare G5 e cliccare su OK.


42


La funzione Indice

Inseriamo nella colonna B i dati come raffigurati nell’immagine sottostante, la quale ci lascia intuire che la moneta presente nella cella C4, Peseta, è quella scelta con la Casella combinata. Sempre nell’immagine sottostante la Barra della formula, indicante il contenuto della cella C4, ci mostra la funzione Indice.

Esaminiamo brevemente anche la sintassi della funzione INDICE. =INDICE(Intervallo dati; Riga) =INDICE(Moneta;G5)

Intervallo dati L’intervallo denominato contenente i dati ove vogliamo effettuare la ricerca, nel nostro caso Moneta.

Riga

La riga dell’intervallo dati in corrispondenza della quale si trova il valore desiderato. Nel nostro esempio la Peseta è il quinto elemento, cioè si trova alla quinta riga dell’intervallo denominato Moneta.

Nota: La sintassi della funzione Indice è ben più articolata di quella rappresentata nei paragrafi precedenti, ma essendo questo un corso di VBA non ci dilungheremo oltre su di essa.

Quindi ogni qualvolta che scegliamo una valuta con la Casella combinata, grazie alla funzione Indice, questa viene visualizzata anche nella cella C4.

43


Calcolare l’Euro

La cella C4 individua la valuta che vogliamo convertire, mentre la cella C6 è adibita all’inserimento della quantità della valuta scelta. Nel nostro esempio, illustrato nell’immagine a seguire, vogliamo convertire 1.000 Lire: il risultato dovrà apparire nella cella C9. Per ottenere tale risultato dobbiamo inserire in C9 una funzione piuttosto complessa.

=EUROCONVERT(C6;INDICE(CodiceIso;G5);”eur”) Il secondo argomento della funzione EUROCONVERT, valuta di origine, invece di essere digitato direttamente viene passato dalla funzione INDICE, che a sua volta si avvale dell’intervallo denominato CodiceIso.

A questo punto, variando la quantità (C6) o la moneta con la Casella combinata, automaticamente sarà calcolato l’importo corrispondente in Euro.

Vincolare la quantità

Volendo impostare una quantità (C6) che non possa eccedere un determinato valore, o più semplicemente togliere all’utente ogni onere di digitazione, possiamo ricorrere ad una Casella di selezione.

• Dalla barra degli strumenti Moduli cliccare sull’oggetto Casella di selezione.

• Fare clic in una zona vuota del foglio per generare automaticamente una Casella di selezione.

44


• Fare clic con il tasto destro sulla Casella di selezione per far apparire li menu contestuale, dal quale selezionare Formato controllo.

• Selezionare la scheda Controllo e digitare: o Valore corrente 10 o Valore minimo 1 o Valore massimo 20000 o Avanzamento 1 o Collegamento cella G8

• Cliccare su OK.

Quindi nella cella C6, in corrispondenza della quantità, digitare la seguente formula: =1000*G8 ove G8 è la cella collegata alla Casella di selezione. Noterete che ad ogni singolo clic sulla Casella di selezione si aggiornerà automaticamente la quantità di valuta da convertire e a cascata gli euro corrispondenti.

Gestire gli eccessi

Il nostro modello valutario ha un problema! Il valore minimo 1000, impostabile dall’azione combinata della Casella di selezione e della formula in C6, è sicuramente adeguato alle lire, ma è troppo elevato per i franchi. Si potrebbe rimediare al problema abbastanza agevolmente con una funzione SE nidificata, ma noi che vogliamo vedere la Casella di controllo in azione seguiremo un’altra via.

• Dalla barra degli strumenti Moduli cliccare sull’oggetto Casella di controllo.

• Fare clic in corrispondenza della cella E11.

• Sarà automaticamente generata una Casella di controllo.

• Fare clic con il tasto destro sulla Casella di controllo per far apparire li menu contestuale, dal quale selezionare Formato controllo.

• Apparirà la fine stra di dialogo Formato controllo ove dovremo selezionare la scheda Controllo.

• Nella casella Collegamento cella digitare G11.

• Selezionare il nome di default per sovrascriverlo digitando “Dividi per 100”.


45


Il nostro intendimento è quello di poter dividere per 100 la quantità (C6) di valuta da convertire quando selezioniamo la Casella di controllo e conseguentemente nella cella G11 comparirà VERO. Ad esempio, se la quantità in C6 di 92000 risultasse eccessiva metteremo il segno di spunta alla Casella di controllo ed automaticamente il valore in C6 si trasformerebbe in 920. Per ottenere il risultato di cui sopra, occorre modificare la formula in C6, integrandola così : =SE(G11=FALSO;1000*G8;10*G8) . Traducibile in: se la Casella di controllo non è selezionato e quindi restituisce il valore FALSO in G11, venga eseguita la moltiplicazione normale per 1000, altrimenti moltiplicare solo per 10 (1000/100=10).

Considerazioni

L’uso dei Pulsanti di opzione ci avrebbe permesso di creare un modello valutario più elegante e preciso, ma in questa fase del nostro corso avremmo introdotto delle difficoltà, forse, eccessive. Poi, per completezza di informa-zione, vi invito a fare una “visitina” al menu Strumenti/Conversione euro… per prendere visione di uno strumento di conversione niente male, che Excel ci mette a disposi-zione. Ma allora vi domanderete, perché tutto sto esercizio? Semplice! Per pure finalità didattiche!


46

PARTE 2 Primi passi nel VBA

Inizieremo adesso ad addentrarci nel VBA vero e proprio. Prenderemo confidenza con l’ambiente di programmazione e vedremo il codice scritto con le macro che abbiamo registrato. Scriveremo le prime semplici procedure e piano piano conosceremo le principali strutture di controllo. Insomma vedremo che la programmazione, se accompagnata dalla logica e un po’ di inventiva, non è poi così difficile, anzi ci troveremo ben presto in grado di raggiungere risultati di tutto rispetto.


47

Lezione VIII – L’Editor di VBA

I novizi della programmazione troveranno non poche difficoltà nel primo impatto con l’Editor di Visual Basic; tenete conto che anche i “vecchi” utilizzatori del VBA prima maniera, cioè quello risalente alle versioni 5 e 7 di Excel, trovarono ostico raccapezzarsi nel nuovo ambiente di programmazione. Per questa ragione illustreremo gli elementi essenziali dell’Editor da questa prima lezione dedicata ai rudimenti della programmazione.

Aprire l’Editor di VB

Nell’immagine sottostante si può “ammirare” l’Editor di VB…


48

… richiamabile dal menu Strumenti di Excel, posizionandosi sulla voce Macro per poi cliccare Visual Basic Editor., oppure con la combinazione di tasti ALT+F11.

Nota: L’Editor illustrato nell’immagine della pagina precedente corrisponde ad una nuova cartella di lavoro appena aperta. Gli elementi di un Editor associato ad una cartella contenente delle macro si presenterebbero un po’ diversamente.

Le finestre dell’Editor di VB

L’Editor di VB è composto da una finestra principale, detta genitrice, che contiene tre sottofinestre definite figlie. Le finestre figlie sono: Finestra Progetto, Finestra delle Proprietà e Finestra del Codice. Tutte e tre le sottofinestre possono essere chiuse, spostate e ridimensionate.

La finestra Progetto

La Finestra Progetto è per default posizionata in alto a sinistra e visualizza tutti i fogli di lavoro, i moduli e se presenti i forms della cartella o cartelle di lavoro aperte. I suoi contenuti sono rappresentati da un diagramma ad albero con tutte le cartelle aperte e collassabili con un clic sul segno meno (-). Nell’esempio raffigurato a lato possiamo vedere che stiamo trattando del VBAProject(Cartel1), cioè di una cartella appena aperta che ha il consueto nome Cartel1.xls. Scendendo nella scala gerarchica troviamo la cartella Microsoft Excel Oggetti contenente i tre fogli di lavoro, più un elemento particolare denominato ThisWorkbook, del quale parleremo nelle ultime lezioni.


49

Nell’eventualità la finestra Progetto non fosse presente, la si può richiamare dal menu Visualizza dell’Editor VB, cliccando sulla voce Gestione progetti.

La finestra delle Proprietà

La finestra delle Proprietà mostra le proprietà dell’oggetto selezionato. Nell’immagine d’esempio stiamo visualizzando le proprietà del Foglio1. Provando a cliccare sul Foglio2 nella finestra del Progetto vedremo la finestra delle Proprietà aggiornarsi al nuovo elemento selezionato. Nell’eventualità la finestra delle Proprietà non fosse presente, la si può richiamare dal menu Visualizza dell’Editor VB, cliccando sulla voce Finestra Proprietà.

La finestra del Codice

La terza sottofinestra, quella del Codice, nell’immagine raffigurante l’Editor non è presente, al suo posto vediamo uno spazio vuoto grigio. Ciò è dovuto al fatto che in una nuova cartella di lavoro non è ancora presente il codice, quindi a meno di richiamarla esplicitamente, la finestra del Codice non è visibile. Per richiamare la finestra del Codice, vi sono vari sistemi, tra i quali fare clic sul menu Inserisci/Modulo. Ma se andiamo ad aprire la cartella di lavoro Comuni, ove abbiamo eseguito le prime registrazioni, scopriremo una situazione sensibilmente diversa.


50

La finestra del Progetto presenta degli elementi aggiuntivi, tipo la cartella Moduli, all’interno della quale troviamo Modulo1 e Modulo2. Ancora più evidente è la presenza della finestra del Codice con del codice all’interno.

Nota: L’Editor di Visual Basic è sicuramente ben più complesso di quello che può apparire dalla nostra breve “chiacchierata”, ma senza appesantimenti teorici che, rischierebbero di scoraggiarci, gli elementi appresi in questa lezione sono sufficienti ad orientarci nel suo interno.


51

Lezione IX – Il codice delle macro

Finalmente è giunto il momento di esplorare il codice prodotto dal nostro registratore di macro.

Regole generali

Apriamo la cartella di lavoro contenente gli esempi delle prime lezioni e dal menu Strumenti\Macro selezioniamo la voce Visual Basic Editor. La finestra Progetto dovrebbe già mostrare selezionato il Modulo1 e la finestra Codice il corrispondente listato. Se così non fosse nella finestra Progetto, eseguire un doppio clic sulla voce Modulo1. Andiamo così ad esaminare la macro registrata nella prima lezione. Le righe che iniziano con un apice (‘), colorate di verde sono testo di commento. E’ possibile aggiungere, modificare, eliminare commenti senza influenzare l’esecuzione della macro. Ricordiamoci però che i commenti rivestono una grande ruolo nella documentazione delle nostre procedure. La macro vera e propria inizia con Sub, Procedura in Italiano. Quindi ogni macro deve iniziare con la parola chiave Sub, al quale segue un nome di fantasia, che nel nostro caso è Macro1. Infine aprire e chiudere una coppia di parentesi tonde senza spazi all’interno. Riepilogando una macro deve sempre iniziare con la seguente sintassi:

Sub NomeFantasia( )

Sub è un elemento fondamentale anche per la chiusura della macro. Ogni macro deve terminare con End Sub.


52

Analizzare il codice di Macro1

La macro registrava le azioni di selezionare l’intervallo di celle A3:F3, di grassettarlo e infine di deselezionare attivando la cella A2.

Range("A3:F3").Select Seleziono le celle da A3 a F3 Selection.Font.Bold = True La selezione deve essere grassettata? Vero. Range("A2").Select Seleziono la cella A2 per deselezionare.

Per rendere più comprensibile la trasposizione del codice, in linguaggio corrente si è un po’ sacrificata la struttura sintattica. In VBA, l’oggetto precede l’azione.

Range("A2") = oggetto Select = azione (verbo)

E’ come se nel parlare di tutti i giorni invece di dire “Bevo il caffè”, dicessimo “Il caffè bevo”. Qual è il vantaggio di una simile bizzarria linguistica? Una volta selezionato un oggetto possiamo compiere su di esso innumerevoli azioni senza dover riselezionare continuamente lo stesso oggetto. Per restare nell’esempio del caffè è come se dicessimo: “Il caffè bevo, acquisto, sorseggio, rovescio”. Il punto (.) funge da separatore fra l’oggetto e l’azione

Nota: Ad essere precisi l’oggetto, oltre a precedere l’azione, può anche precedere la specificazione di una sua Proprietà, ossia, continuando la metafora linguistica, l’aggettivo. Sempre per mettere i puntini sulle i, l’azione in VBA viene chiamata Metodo.


53

Analizzare il codice della macro RiferimentiRelativi

La seconda macro, registrata durante la prima lezione, introduceva la logica dei riferimenti relativi. Anche questa macro, registrava le azioni di selezionare l’intervallo di celle A3:F3, di grassettarlo e infine di deselezionare attivando la cella A2.

Sub Riferimentirelativi() 'Riferimentirelativi Macro ActiveCell.Offset(2, 0).Range("A1:F1").Select Selection.Font.Bold = True ActiveCell.Offset(-1, 0).Range("A1").Select End Sub

Ma in questo caso passava in secondo piano l’esatta ubicazione delle celle, bensì assumevano importanza gli spostamenti, quindi era anche importante sapere qual era la cella attiva nel momento in cui veniva attivata la registrazione. La cella attiva era A1.

ActiveCell.Offset(2, 0). Dalla cella attiva, qualunque essa sia, scende di 2 celle…

("A1:F1").Select

…quindi seleziona verso destra 6 celle. E’ importante notare che A1 e F1 non rappresentano riferimenti assoluti, anche perché in realtà sono state selezionate le celle A3:F3.

Selection.Font.Bold = True La selezione deve essere grassettata? Sì .

ActiveCell.Offset(-1, 0). Dalla cella attiva, qualunque essa sia, sale di 1 cella…

Range("A1").Select

… quindi seleziona la cella per deselezionare. Anche qui è importante notare che non è A1 la cella selezionata.


54

Modificare il contenuto di una macro

Passiamo ad esaminare .la macro Navigator, presente nel Modulo2:

Sub Navigator() Sheets("Foglio2").Select End Sub

Con l’istruzione…

Sheets(“Foglio2”).Select …è come se dicessimo “Il foglio chiamato Foglio2 seleziona”. E’ intuitivo che volendo cambiare il foglio da attivare, basterà riscrivere il nome del foglio posto fra le parentesi tonde e le virgolette: ad esempio reindirizziamo la navigazione di Navigator al Foglio3.

Sheets(“Foglio3”).Select

Facile vero?! Le nostre procedure di navigazione hanno un difetto però! Ci portano nel foglio desiderato, ma non si preoccupano di controllare se il foglio è visibile dall’inizio: è certamente sgradevole transitare in un foglio e visualizzare la fine di una tabella.

• Posizioniamoci dopo Select della macro Navigator e diamo un INVIO per creare una riga vuota.

• Spostiamo il cursore sulla riga vuota testè creata e digitiamo le seguenti istruzioni:

Range("A1").Select L’aggiunta della piccola porzione di codice sopra illustrata, farà sì che la macro dopo essersi spostata sul foglio desiderato, attivi la cella A1, garantendo così la visualizzazione del foglio dall’inizio. Nel paragrafo sottostante possiamo vedere la nuova versione di Navigator:

Sub Navigator() Sheets("Foglio3").Select Range("A1").Select End Sub


55

I nomi delle procedure

Nell’assegnazione di nomi alle macro, occorre seguire alcune regole di denominazione:

• Devono iniziare con una lettera.

• Non possono includere punti, operatori matematici o di confronto.

• Non possono superare 255 caratteri.

• Devono essere univoci, almeno all’interno della procedura (poiché a volte non basta è meglio prudentemente all’interno della cartella), o più correttamente nell’area di validità.

• Non possono coincidere con il nome di parole chiave del VBA.

• Non devono essere composti da più parole.

Identare il codice

Si può notare, nei primi esempi di macro esaminati, che le righe non sono sempre allineate al margine sinistro, ma risultano spostate verso destra in vario modo. Questo stile di scrivere il codice è detto indentazione. Non ci sono regole fisse che stabiliscano di quanto bisogna far rientrare una riga, od un gruppo di righe; l’identazione è solo un modo per rendere visivamente più chiara la struttura del programma. Generalmente ci si serve dell’identazione per allineare ad esempio sulla stessa colonna blocchi di istruzioni dello stesso livello o comunque correlati fra loro. Nell’uso dell’identazione entra comunque il gusto personale dell’autore del programma, giacché introduce elementi di chiarezza visiva, del tutto estranei al sistema di esecuzione del programma.

Le maiuscole

Tutte le parole chiave del VBA hanno l’iniziale maiuscola: Sub, End, Range, Select ecc. . Però la digitazione di esse deve avvenire tutta in minuscolo, poiché automaticamente le iniziali di trasformeranno in maiuscole, sempre che siano state scritte correttamente. Il digitare le iniziali delle parole chiave in minuscolo è di fatto un sistema di controllo.


56

Lezione X – Operare delle scelte con IF…

In questa lezione impareremo a prendere delle decisioni, a scrivere delle routine che verificano determinate condizioni ed eseguono conseguentemente, istruzioni specifiche, in base al risultato della verifica. Le istruzioni in VBA che controllano il flusso delle decisioni sono definite strutture di controllo. Mettendola un po’ sul mistico, potremmo dire che implementiamo le capacità delle nostre macro a scegliere fra il vero ed il falso: fra il bene ed il male.

Il file Comuni

Apriamo la cartella di lavoro Comuni e scegliamo l’omonimo foglio, contenente l’elenco dei comuni della provincia di Torino. In particolar modo, soffermiamoci sulla colonna F, contenente i valori relativi alla popolazione. Il nostro compito consisterà nel colorare di giallo le celle contenenti un numero di abitanti superiore al valore di 9.999: il tutto naturalmente, con un semplice clic.

Scomporre i problemi

Iniziamo per gradi: il primo compito è quello di capire come si fa a colorare di giallo una cella tramite il codice VBA. La soluzione è molto semplice! Registriamo una macro che formatta di giallo lo sfondo di una cella.


57

Prima di iniziare la registrazione ci accerteremo che l’area di lavoro non presenti degli impedimenti, che potrebbero compromettere la qualità del codice che sarà generato. Notiamo che le barre degli strumenti, disposte su di un’unica riga ci occultano dei pulsanti, fra i quali proprio quello del Colore di riempimento.

• Dal menu Visualizza\Barre degli strumenti scegliere Personalizza.

• Comparirà la finestra di dialogo Personalizza, dalla quale sceglieremo la scheda Opzioni.

• Mettere il segno di spunta su Mostra barre degli strumenti Standard e Formattazione su due righe.

• Cliccare su OK per confermare.

Registrare la colorazione di una cella

• Posizioniamoci su di una cella da colorare.

• Lanciare il registratore di macro dal menu Strumenti\Macro\Registra nuova macro.

• Digitare come nome della macro Colorare e cliccare su OK per avviare la registrazione.

• Dal pulsante Colore riempimento, posto sulla barra degli strumenti Formattazione, scegliere il colore giallo.

• Interrompere la registrazione cliccando sul pulsante Interrompi registrazione.


58

Esaminare il codice della macro Colorare

Nella precedente lezione abbiamo visto che per visualizzare il codice di una macro, occorre richiamare l’Editor di VB: risultato ottenibile anche dalla finestra di dialogo Macro, richiamabile dal menu Strumenti\Macro\Macro. Una volta visualizzata la finestra Macro, selezionare la macro Colorare e cliccare sul pulsante Modifica, per essere immediatamente proiettati nell’ambiente di programmazione…

… ove è possibile “ammirare” la sintassi generata per colorare una cella di giallo. Questa volta il codice si presenta meno intuitivo degli esempi precedenti, complicato dalla presenza dell’enunciato Whit.

Sub Colorare() With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End Sub


59

L’enunciato Whit

L’enunciato Whit, traducibile in con, serve a rendere più veloce l’esecuzione della macro, ma non è indispensabile. Rivediamo la procedura Colorare senza Whit.

Sub Colorare() Selection.Interior.ColorIndex = 6 Selection.Interior.Pattern = xlSolid End Sub

Le due righe di codice così trasformate si possono leggere: Selezione interna colora di giallo Selezione interna trama solida Mentre il codice originale è così traducibile: Con selezione interna

colora di giallo trama solida

Fine con Appare evidente che in presenza di molti attributi il codice racchiuso dall’enunciato Whit diventa più conciso, più veloce.

Eliminare codice superfluo

In molti casi il Registratore di macro registra tutte le possibili impostazioni, anche quando se ne modificano solo una o due. Eliminando le impostazioni non necessarie la macro risulterà semplificata e più veloce. Nella macro Colorare l’unica proprietà che vogliamo modificare è il colore, quindi la tipologia di trama è eliminabile. Il risultato della suddetta colorazione è una macro estremamente leggera.

Sub Colorare() Selection.Interior.ColorIndex = 6 ‘6 significa giallo End Sub

Notate il commento che ricorda che al numero 6 corrisponde il giallo.


60

Colorare e scendere

Dopo essere riusciti a far colorare di giallo una cella, un’altra azione che possiamo far compiere alla nostra macro, senza eccessive difficoltà è quella di scendere alla cella sottostante. Il pezzo di codice necessario a tale necessità è già disponibile nella macro Navigator. Ricordate? Il seguente codice scende di due celle…

ActiveCell.Offset(2, 0).Range("A1").Select … quindi sostituendo il 2 con un 1, scenderemo di una sola cella per volta. Ed ecco il risultato di questa ulteriore modifica alla procedura Colorare, che formatta la cella di giallo e scende di una cella.

Sub Colorare() Selection.Interior.ColorIndex = 6 ‘6 significa giallo ActiveCell.Offset(2, 0).Range("A1").Select ‘Scende di una cella End Sub

Se…Allora

E’ giunto il momento di dare il “lume della ragione” alla nostra macro! Dovrà colorare sol tanto quando il valore della cella supera 9.999 .

Sub Colorare() If ActiveCell > 9999 Then ‘Se il contenuto della cella è superiore a 9999 Selection.Interior.ColorIndex = 6 ‘6 significa giallo End If ActiveCell.Offset(1, 0).Range("A1").Select ‘Scende di una cella End Sub

Il codice della struttura di controllo If è così traducibile. Se la cella attiva contiene un valore maggiore di 9999 allora Colorala di giallo Fine se Esaminiamo anche la sintassi di If, che si presenta molto semplice. Alla parola chiave If segue una condizione, a sua volta seguita dalla parola chiave Then. Nella riga successiva troviamo una o più istruzioni da eseguire se una determinata condizione risulta vera. Scendendo nuovamente, la fine della struttura di controllo è data da End If.

If condizione Then Istruzioni End If


61

Il controllo If deve essere sempre chiuso da End If, ad eccezione di macro con struttura che si sviluppa su di una sola riga.

If condizione Then istruzioni Pur dando per acquisita la conoscenza degli operatori di confronto, se ne propone una rapida carrellata.

OPERATORI di CONFRONTO MAGGIORE > DIVERSO <> MINORE < MAGGIORE O UGUALE >= UGUALE =

MINORE O UGUALE <=


62

Lezione XI – Ripetere le azioni

Posizioniamo la cella attiva in F5 e proviamo la macro Colorare. Sarà testato il valore della cella, eventualmente formattato, in ogni caso la cella attiva scenderà nella cella sottostante. Volendo ripetere più volte l’esecuzione della macro, le operazioni di “rovistare” fra il menu Strumenti\Macro\Macro, individuare la procedura e cliccare su OK, si dimostreranno veramente noiose.

Pulsanti alternativi

Si può rimediare al problema di cui sopra, associando la macro Colorare ad un pulsante. Però i pulsanti, diciamocelo, sono proprio bruttini. Scegliamo una soluzione alternativa.

• Dal menu Visualizza\Barre degli strumenti scegliere Disegno.

• Cliccare su Casella di testo.

• Modellare la Casella di testo in prossimità della cella G2.

• Personalizzarla a piacere.

• Cliccarci sopra con il pulsante destro del mouse e dal menu contestuale che apparirà, scegliere la voce Assegna macro.

• Assegnare la macro di nome Colorare e confermare cliccando su OK.

• Deselezionare l’oggetto cliccando sul foglio. Adesso risulterà molto più comodo provare la macro Colorare.

Rinominare un modulo

Volendo visualizzare la macro Colorare, cliccare sul pulsante Modifica della finestra di dialogo Macro. Tale macro si trova presumibilmente nel Modulo3, in ogni caso accertarsene osservando la barra del titolo del modulo, ove è riportato il nome del Modulo. Per rendere meno generico il nome del modulo, rinominiamolo.


63

Nella finestra delle Proprietà, di fianco a (Name), selezionare Modulo3 e digitare il testo alternativo ModColorare.

I cicli

Indubbiamente Colorare è una macro simpatica, ma parliamoci chiaro, non serve a niente. Già nel nostro elenco di circa 300 records testare tutti i valori sarebbe assai uggioso, figuriamoci se ci trovassimo innanzi un database di cinquemila o seimila records! Dobbiamo trovare il sistema, di far scorrere da Excel tutto l’elenco automaticamente e di fermarsi, quando questi finisce. Il VBA, dopo If … Then, ci mette a disposizione un’altra tipologia di struttura di controllo: i cicli. Le strutture a ciclo consentono di eseguire ripetutamente un blocco di istruzioni di una macro.

Do While…Loop

L’istruzione Do While…Loop verifica una condizione all’inizio del ciclo e se risulta vera (True) esegue le istruzioni, ripetendole fino a quando la condizione risulta falsa (False)

Do While condizione istruzioni Loop

Modifichiamo la macro Colorare aggiungendovi la struttura di controllo Do While… Loop.

Nota: Il codice di una macro non prevede dei numeri davanti alle righe: la numerazione delle linee presente nel listato successivo e in altri seguenti, è stata inclusa solo per facilitare il riconoscimento e il commento della sintassi delle procedure d’esempio.


64

1: Sub Colorare() 2: Do While ActiveCell <> "" 'esegue se è vero che è diverso da vuoto 3: If ActiveCell > 9999 Then 4: Selection.Interior.ColorIndex = 6 5: End If 6: ActiveCell.Offset(1, 0).Range("A1").Select 7: Loop 8: End Sub

La procedura alla riga 2 inizia testando la cella attiva ed è come se recitasse: esegui fino a quando è vero che la cella attiva contiene un qualche valore Se abbiamo avviato la macro con la cella attiva in F5, la cella è chiaramente (vero) diversa da vuota e quindi passa ad eseguire il blocco di istruzioni successive.

Nota: Il simbolo virgolette/virgolette (“”) senza spazi vuoti significa vuoto; quindi <> “” significa diverso da vuoto.

Dalla riga 3 alla riga 5 esegue le istruzioni di controllo del numero degli abitanti della città e se superiori a 9999 colora la cella di giallo. Le istruzioni della riga 6 scendono nella cella F6. Arrivati alla riga 7 troviamo la parola chiave Loop e ripetiamo il ciclo partendo dalla verifica del contenuto della cella attiva, che a questo punto abbiamo visto essere F6. Il ciclo sarà ripetuto sino a quando arrivati a F320 Do While non troverà più vera la condizione, poiché tale cella è vuota.

Do Until… Loop

Sub Colorare() Do Until ActiveCell = "" 'esegue se non è vero che la cella ‘è vuota If ActiveCell > 9999 Then Selection.Interior.ColorIndex = 6 End If ActiveCell.Offset(1, 0).Range("A1").Select Loop End Sub


65

Do Until… Loop verifica una condizione all’inizio del ciclo, ed esegue il blocco di istruzioni successive fino a quando la condizione risulta falsa, per poi fermarsi quando la condizione diventa vera. Entrambi i cicli funzionano perfettamente: forse Do While è più diffuso.

Almeno una volta…

Do While … e Do Until … testano sin dall’inizio del ciclo il soddisfacimento della condizione e se tale non risultasse, non eseguirebbero neanche una volta la serie di istruzioni comprese nel loro corpo. Attivare una cella vuota e lanciare la macro Colorare: la procedura non scenderà neanche di una cella! Si dovesse presentare la necessità di eseguire l’intero ciclo di istruzioni, almeno una volta, prima di verificare il soddisfacimento della condizione, occorrerà eseguire i seguenti ritocchi al codice:

Sub Colorare() Do If ActiveCell > 9999 Then Selection.Interior.ColorIndex = 6 End If ActiveCell.Offset(1, 0).Range("A1").Select Loop While ActiveCell <> "" 'esegue se è vero che è diverso ‘da vuoto End Sub

Sub Colorare() Do If ActiveCell > 9999 Then Selection.Interior.ColorIndex = 6 End If ActiveCell.Offset(1, 0).Range("A1").Select Loop Until ActiveCell = "" ' esegue se non è vero che la cella ‘è vuota End Sub

Nota: Le strutture cicliche a volte vanno in errore ed entrano in un loop infinito. Per uscire da una simile situazione di stallo, premere la combinazione di tasti CTRL+INTERR .


66

Lezione XII - Le variabili

Questa lezione è dedicata a uno degli elementi fondamentali nello studio di qualsiasi linguaggio di programmazione: le variabili.

Definizione di variabile

Le variabili sono aree predefinite della memoria del computer, in cui vengono archiviati i dati mentre le macro sono in esecuzione. Le variabili risultano utili per tenere traccia di conteggi che cambiano continuamente, per conservare l’input dell’utente o di altra origine e per l’archiviazione di valori destinati ad essere modificati da macro eseguite successivamente.

Nota: in un certo senso possiamo paragonare le variabili alle celle di Excel. Le celle sono identificabili da un riferimento, tipo A1, o ancora meglio da un nome (cella denominata), Pippo ad esempio: nello stesso modo noi identifichiamo le variabili attribuendogli un nome. Il contenuto della cella\variabile può variare, ma non il suo nome. Quindi ogni volta che si vuole fare riferimento al valore contenuto in una determinata cella\variabile basterà usare il suo nome.

Non dichiarare le variabili

In VBA, quindi in VB, a differenza di linguaggi di programmazione nobili come il C ed il Pascal, si può decidere di non dichiarare le variabili: in realtà le controindicazioni ad una simile ipotesi sono moltissime. In questo corso non prenderemo in considerazione tale opportunità. Anzi adesso ci preoccuperemo di eliminare la possibilità, che una evenienza così scellerata, come quella di non dichiarare le variabili, possa accadere anche solo accidentalmente.

• Dal menu Strumenti dell’Editor di VBA scegliere Opzioni, portarsi sulla scheda Editor e porre il flag (segno di spunta) sulla scelta Dichiarazione di variabili obbligatoria.

Dichiarare le variabili

La dichiarazione delle variabili, detta anche dichiarazione esplicita, si ottiene con la parola chiave Dim (Dimensionare), collocando la dichiarazione all’inizio della procedura o del modulo.

Sub NomeMacro() Dim NomeVariabile


67

Denominazione di variabili

Nell’assegnazione di nomi alle variabili, occorre seguire alcune regole di denominazione. I nomi di variabili:

• Devono iniziare con una lettera.

• Non possono includere punti, operatori matematici o di confronto.

• Non possono superare 255 caratteri.

• Devono essere univoci, almeno all’interno della procedura, o più correttamente nell’area di validità.

• Non possono coincidere con il nome di parole chiave del VBA.

• Non devono essere composti da più parole.

Tipo di dati delle variabili

Quando si dichiara una variabile è possibile assegnarle anche il tipo di dati che dovrà contenere. Se non si specifica nessun tipo di dati, come nell’esempio sopra esposto (Dim NomeVariabile), viene automaticamente assegnato il tipo Variant. Il tipo Variant è una sorta di “minestrone” in grado di gestire qualsiasi tipo di dati. Ma se si desidera creare macro rapide, e si sa che in una determinata variabile saranno sempre archiviate informazioni dello stesso tipo di dati, è consigliabile specificare il giusto tipo di appartenenza.

Nome del Tipo TIPO Dimensioni Intervallo Integer Numerico 2 byte da –32.768 a 32.767

Long Numerico 4 byte da -2.147.483.648 a 2.147.483.6477

Single Numerico 4 byte da -3,402823E38 a -1,401298E-45 per valori negativi; da 1,401298E-45 a 3,402823E38 per valori positivi.

Double Numerico 8 byte

da -1.79769313486231E308 a -4,94065645841247E-324 per valori negativi; da 4,94065645841247E-324 a 1,79769313486232E308 per valori positivi.

Currency Numerico 8 byte da -922.337.203.685.477,5808 a 922.337.203.685.477,5807

String Testo 1 byte per carattere

Boolean Logico 2 byte True o False

Date Data 4 byte dal'1 gen. 100 al 31 dic. 9999

Object Oggetto 4 byte Serve ad accedere ad oggetti riconosciuti dal VBA.

Variant Universale 16 b. + 1 b. x carat. Può contenere qualsiasi tipo di dato.


68

La notazione scientifica

La tabella riepilogante i tipi di dati, mostra alcuni numeri un po’ particolari, la loro sequenza sembrerebbe interrotta dalla vocale E. Tale particolarità è conosciuta come notazione scientifica. La notazione scientifica viene utilizzata in genere per rappresentare numeri molto grandi o molto piccoli in un formato compatto. 1.12E-2 -0.012 2.4E10 24 000 000 000 5E9 5 000 000 000 33E-10 0.00000000033 4.3E1 43

Dichiarazione delle variabili e del tipo

Dim Pippo As String

Dimensioniamo la variabile di nome Pippo come tipo stringa: detta in altre parole, la variabile Pippo potrà contenere del testo.

Dim Pluto As Integer In questo secondo caso abbiamo dichiarato la variabile Pluto di tipo numerico Intero.

Dim Paperino, Archimede, Gastone As Variant In presenza di più variabili dello stesso tipo, possiamo dichiararle anche sulla stessa linea. Vediamo un piccolo esempio pratico sull’uso di una variabile.

Sub Prova() Dim Pippo As Integer ‘dichiaro una variabile di tipo numerico intero Pippo = 9999 ‘assegno alla variabile il valore 9999 If ActiveCell > Pippo Then ‘non uso più il valore, bensì la variabile Selection.Interior.ColorIndex = 6 End If ActiveCell.Offset(1, 0).Range("A1").Select End Sub

Probabilmente avrete notato, dopo aver scritto la parola chiave As, la comparsa di un menu a tendina, ospitante un lungo elenco di parole chiave. Se a quel punto avete digitato la prima vocale, nel nostro esempio la i di


69

integer, l’elenco si sarà posizionato automaticamente in corrispondenza dei termini inizianti con la i. Quindi, utilizzando il pulsante direzionale che punta verso il basso, raggiungete integer e premete sul tasto SHIFT per convalidare la scelta.

Le costanti

Le procedure spesso includono valori che non variano e che ricorrono più volte: in questo caso si utilizzano le costanti. Una costante è un’area predefinita della memoria, alla quale viene attribuito un nome e che sostituisce un numero o una stringa non soggetti a modifiche. Le costanti sembrano simili alle variabili, in realtà non è possibile modificarle o assegnarvi nuovi valori.

Portata delle variabili

Portata è l’ambito entro cui una variabile è accessibile. L’area di azione di una variabile può essere a livello di procedura o a livello di modulo. Una variabile dichiarata all’interno della macro è una variabile a livello di procedura e la sua portata è limitata alla macro che la contiene. Per trasformarsi a livello di modulo, quindi utilizzabile da qualsiasi macro di quel modulo, una variabile deve essere dichiarata all’inizio del modulo.

Dichiarare le costanti

Const Pippo = 9999

Const Paperino = “Il mattino ha l’oro in bocca” Notate che il testo che assegniamo alla costante è delimitato dalle virgolette (“…”).

Const Pluto = #03/09/1957#

Le date devono essere delimitate dai cancelletti (#....#).


70

Lezione XIII – Comunicazioni all’utente

Poter offrire all’utente la possibilità di dialogare con una nostra procedura è sicuramente affascinante e molto utile. In questa lezione studieremo MsgBox, la prima delle due finestre di dialogo incorporate del VBA che incontreremo nel nostro corso.

Una semplice finestra di messaggio

Richiamare l’Editor di programmazione dal menu Strumenti\Macro e nella finestra Progetto eseguire un doppio clic per attivare il Modulo3, che dovrebbe contenere le macro Colorare e Prova. Posizionarsi sulla prima riga vuota e “dare” un paio di invii per creare spazio rispetto la procedura Prova: quindi digitiare Sub Comunica() e premere il tasto INVIO. Oltre alla creazione di una riga vuota, saranno inserite le istruzioni di fine procedura End Sub.

Sub Comunica() End Sub

Posizionarsi sulla riga vuota presente fra l’apertura e la chiusura della macro, premere il tasto TAB e digitare le seguenti istruzioni:

MsgBox "Il mattino ha l'oro in bocca" Eseguire Comunica, scegliendola dall’elenco delle macro presenti nella finestra di dialogo Macro: apparirà l’immagine riprodotta a lato. Per poter eseguire qualsiasi altra operazione in Excel o nell’Editor di VBA è obbligatorio cliccare sul pulsante OK.

MsgBox visualizza il testo precedentemente inserito fra le virgolette (“…”).

Gli argomenti di MsgBox

Ma adesso implementiamo il codice di Comunica. Sub Comunica() Dim Titolo, Messaggio As String Titolo = "Corso on line di VBA per Excel" Messaggio = "Utilizzo di MsgBox" MsgBox Messaggio, , Titolo End Sub


71

MsgBox oltre ad un messaggio, permette anche di impostare la sua barra del titolo, sostituendo il generico Microsoft Excel con un titolo a nostro piacere. Mentre veniva digitato il codice, dopo la scrittura della variabile Messaggio, contenente la comunicazione per l’utente, hanno fatto la loro comparsa due elementi contestuali: un’etichetta descrittiva della sintassi di MsgBox e una casella combinata contenente l’elenco degli argomenti inseribili in quel punto.

Ad onor del vero, l’etichetta (gialla) descrittiva della sintassi non è molto esplicativa per un novizio del VBA: vediamo di semplificare un po’, inserendo solo gli argomenti principali.

MsgBox(Prompt, [Buttons], [Title])

Fra gli argomenti presentati, solo Prompt è obbligatorio. Ciò è facilmente deducibile dal fatto che è l’unico non racchiuso fra parentesi quadre […]. Prompt è l’argomento rappresentante il messaggio da inoltrare, che nel nostro esempio è rappresentato dalla variabile Messaggio. Buttons, sempre nel nostro esempio non è stato contemplato, però non essendo contemplata la possibilità di saltare un argomento, abbiamo lasciato a sua testimonianza la doppia virgola , , . Title è il titolo che compare sulla barra del titolo della finestra messaggio, che nel nostro caso è la variabile Titolo.

Nota: Le parentesi tonde a questo stadio di utilizzo della funzione MsgBox non sono ancora necessarie.

Aggiungiamo anche l’argomento Buttons, dandogli la forma di un bel punto esclamativo.

MsgBox Messaggio, vbExclamation, Titolo Le possibili impostazioni dell’argomento Buttons sono:


72

Costante Valore Descrizione

vbOKOnly 0 Visualizza solo il pulsante OK.

VbOKCancel 1 Visualizza i pulsanti OK e Annulla.

VbAbortRetryIgnore 2 Visualizza i pulsanti Termina, Riprova, e Ignora.

VbYesNoCancel 3 Visualizza i pulsanti Sì , No e Annulla.

VbYesNo 4 Visualizza i pulsanti Sì e No.

VbRetryCancel 5 Visualizza i pulsanti Riprova e Annulla.

VbCritical 16 Visualizza l'icona di messaggio critico.

VbQuestion 32 Visualizza l'icona di richiesta di avviso.

VbExclamation 48 Visualizza l'icona di messaggio di avviso.

VbInformation 64 Visualizza l'icona di messaggio di informazione.

VbDefaultButton1 0 Il primo pulsante è il predefinito.

VbDefaultButton2 256 Il secondo pulsante è il predefinito.

VbDefaultButton3 512 Il terzo pulsante è il predefinito.

VbDefaultButton4 768 Il quarto pulsante è il predefinito

VbApplicationModal 0

Finestra di messaggio a scelta obbligatoria nell'applicazione. L'utente deve rispondere alla finestra di messaggio prima di poter continuare a lavorare nell'applicazione corrente.

VbSystemModal 4096

Finestra di messaggio a scelta obbligatoria nel sistema. Tutte le applicazioni vengono sospese fino a quando l'utente non risponde alla finestra di messaggio.

vbMsgBoxHelpButton 16384 Aggiunge un pulsante della Guida nella finestra di messaggio.

VbMsgBoxSetForeground 65536 Specifica che la finestra di messaggio è in primo piano.

vbMsgBoxRight 524288 Il testo è allineato a destra.

vbMsgBoxRtlReading 1048576 Specifica che il testo viene visualizzato da destra a sinistra per i sistemi ebraico e arabo.

Il primo gruppo di valori (0-5) descrive il numero e il tipo dei pulsanti visualizzati nella finestra di messaggio. Il secondo gruppo (16, 32, 48, 64) descrive lo stile dell'icona. Il terzo gruppo (0, 256, 512, 768) determina il pulsante predefinito e il quarto gruppo (0, 4096) determina se la finestra di messaggio è a scelta obbligatoria nell'applicazione o nel sistema. Quando si sommano dei numeri per la creazione di un valore finale dell'argomento buttons, utilizzare soltanto un numero per ciascun gruppo.

Nota: Il contenuto di questa pagina è tratto dalla guida del VBA di Excel.


73

Infine osserviamo la possibilità di inserire più argomenti Buttons nella stessa finestra messaggio.

MsgBox Messaggio , vbYesNo + vbExclamation, Titolo

Ma se la finestra messaggio serve solo a fare comunicazioni all’utente, perché in questo ultimo esempio è rappresentata la possibilità di scegliere fra Sì e No ? Fra non molto affronteremo anche questo “lato” di MsgBox.


74

Lezione XIV – Interazione con l’utente

La finestra di input, la seconda delle due finestre di dialogo incorporate del VBA, ci consente di fare un bel passo avanti nella possibilità di interagire con i fruitori delle nostre procedure.

Finestra input

La funzione InputBox permette di creare e di visualizzare una semplice finestra di dialogo che consente l’immissione di dati da parte dell’utente.

• Richiamare l’Editor di VB dal menu Strumenti\Macro\Visual Basic Editor.

• Attivare il modulo ModColorare dalla finestra Progetto.

• Portarsi alla fine modulo e creare delle righe vuote con degli invii.

• Digitare il codice seguente.

1: Sub Immissione() 2: Dim Titolo, Messaggio As String 3: Dim Contenitore As Variant 4: Titolo = "Corso on line di VBA per Excel" 5: Messaggio = "Digita il tuo nome" 6: Contenitore = InputBox(Messaggio, Titolo) 7: MsgBox Contenitore 8: End Sub

• Dal menu Strumenti\Macro (Excel o Editor che sia) attivare la finestra di dialogo Macro ed eseguire la macro Immissione.

• Apparirà InputBox ove occorrerà digitare un nome a piacere.

• Confermare cliccando su OK.

• Farà la sua comparsa una finestra messaggio recante il nome appena digitato in InputBox.


75

La sintassi della macro Immissione, sino alla riga 5, è pressoché identica a quella utilizzata per MsgBox. Anche qui dichiariamo delle variabili, che saranno poi utilizzate per il titolo e per il messaggio della nostra finestra. Abbiamo in più la dichiarazione di una terza variabile di tipo Variant. Le differenze con le istruzioni di MsgBox, sorgono a partire dalla sesta riga, come risulta evidente dal codice delle due funzioni messe a confronto.

MsgBox Messaggio, , Titolo Contenitore = InputBox(Messaggio, Titolo)

• La differenza meno rilevante è la mancanza nella riga di InputBox delle due virgole (, ,) che in MsgBox possono essere occupate dall’argomento Buttons, non presente in InputBox.

• La seconda differenza, la più importante, è la presenza di una variabile, Contenitore, ad inizio della riga relativa alla gestione di InputBox. Associamo alla variabile Contenitore l’output di InputBox, cioè la variabile Contenitore conterrà ciò che sarà digitato in InputBox.

A questo punto il contenuto della settima riga della procedura Immissione, dovrebbe essere chiara: MsgBox ci presenterà come messaggio il contenuto della variabile Contenitore, variabile “riempita” da quello che è stato digitato in InputBox.

Nota: La variabile Contenitore è stata dichiarata come tipo Variant, non sapendo a priori che tipo di dati potrebbero essere digitati.

L’output verso una cella

Nell’esempio precedente abbiamo indirizzato il valore immesso in InputBox a MsgBox, ma volendo, possiamo anche dirigerlo verso la cella di un foglio di lavoro.

1: Sub Immissione() 2: Dim Titolo, Messaggio As String 3: Dim Contenitore As Variant 4: Titolo = "Corso on line di VBA per Excel" 5: Messaggio = "Digita il tuo nome" 6: Contenitore = InputBox(Messaggio, Titolo, Default) 7: Range("a1") = Contenitore 8: End Sub

E’ bastato modificare le istruzioni della settima riga: in luogo della finestra messaggio abbiamo inserito la cella A1.


76

Ancora sugli argomenti

A volte l’input dell’utente è abbastanza prevedibile: in tal caso può risultare d’aiuto inserire un testo o un numero predefinito, che potrà essere confermato o all’occorrenza sostituito. Si dimensioni una terza variabile di tipo string, denominata Default, quindi s’inserisca la seguente riga di codice fra la quinta e la sesta riga…

Default= “Matteo, Luca e Davide” … e modificare la sesta riga, che a questo punto sarà diventata la settima in:

Contenitore = InputBox (Messaggio, Titolo, Default)

Metodo InputBox

Il VBA, ogni tanto, presenta degli strani misteri. Uno di questi è la presenza, accanto alla funzione InputBox, di un altro oggetto pressoché identico, definito metodo InputBox.

Nota: Il VBA “pesca” i suoi oggetti da varie librerie: la funzione InputBox appartiene alla libreria di Visual Basic: il metodo InputBox fa parte della libreria di Excel.

Il metodo InputBox presenta un vantaggio rispetto alla omonima funzione: è in grado di filtrare il tipo di dati immesso.

1: Sub SuperImmissione() 2: Dim Titolo, Messaggio As String 3: Dim Default As Integer 4: Dim Contenitore As Variant 5: Titolo = "Corso on line di VBA per Excel" 6: Messaggio = "Digita il tuo nome" 7: Default = 10 8: Contenitore = Application.InputBox _ 8: (Messaggio, Titolo, Default, Type:=1) 9: Range("a1") = Contenitore 10: End Sub

La sintassi del metodo InputBox, come potete vedere nell’esempio è di fatto identica alla funzione InputBox.


77

L’unica differenza è rilevabile alla riga 8. La parola chiave Application precede InputBox ed è presente l’argomento Type al quale è associato il valore 1, indicante l’accettazione di soli valori numerici. Nell’immagine è visibile il tentativo fallito, di immettere del testo.

Nota: Application rappresenta l’applicazione Microsoft Excel.

Qui di seguito sono riportati i valori dell’argomento Type, atti a specificare il tipo di dati restituito.

Valore Significato

0 Una formula

1 Un numero

2 Del testo (una stringa)

4 Un valore logico (True o False)

8 Un riferimento di cella, ad esempio un oggetto Range

16 Un valore di errore, ad esempio #N/D

64 Una matrice di valori

A capo

Il codice della macro SuperImmissione, presenta una stranezza: sono presenti due righe 8. Il VBA non permette di spezzare le righe di codice, però a volte le righe diventano eccessivamente lunghe e quindi illeggibili. Si risolve il problema mandando a capo una porzione di codice, dicendo però al VBA che si tratta di una riga di istruzioni unica. L’ultima parola della riga 8.1, nel nostro esempio, è InputBox dopo la quale si è creato uno spazio vuoto con la pressione della barra spaziatrice. Quindi si è digitato un underscore ( _ ) .

8.1: Contenitore = Application.InputBox _ 8.2: (Messaggio, Titolo, Default, Type:=1)


78

Lezione XV – Procedura decisionale

In questa lezione daremo sostanza alla realizzazione di una procedura di interazione fra uomo e macchina. Decideremo di volta in volta qual è il numero massimo di abitanti oltre il quale vogliamo formattare le celle.

Variabili a portata di modulo

Richiamando alla memoria la lezione sulle variabili, sicuramente non avrete dimenticato il paragrafo dedicato alla portata delle variabili. Nello svolgimento di questa lezione dovremo ricorrere, per la prima volta, ad una variabile a livello di modulo. Attiviamo il modulo ModColorare e posizioniamoci al suo inizio, ove digiteremo la seguente dichiarazione di variabile.

Dim Contenitore As Variant 'Variabile a livello di modulo Quindi creiamo una nuova procedura, atta a raccogliere il numero di abitanti che di volta in volta porremo come limite oltre al quale si dovrà colorare la cella.

1: Sub SceltaPopolazione() 2: Dim Titolo, Messaggio As String 3: Dim Default As Integer 4: Titolo = "Corso on line di VBA per Excel" 5: Messaggio = "Popolazione oltre la quale colorare di giallo" 6: Default = 9999 7: Contenitore = Application.InputBox _ 8: (Messaggio, Titolo, Default, Type:=1) 'Tipo 1, cioè numerico 9: End Sub

La macro SceltaPopolazione, ricalca perfettamente la procedura vista nella precedente lezione, SuperImmissione. L’unica differenza è appunto la dichiarazione della variabile Contenitore, fatta a inizio modulo. La variabile Contenitore conterrà il valore digitato nella finestra di input.


79

Inserire una procedura dentro una procedura

Sempre nel modulo ModColorare cerchiamo la macro Colorare e immediatamente sotto la prima riga, con un invio, creiamo una riga vuota ove digitare SceltaPopolazione. Il risultato non dovrebbe essere dissimile da quello visualizzato nelle righe sottostanti.

1: Sub Colorare() 2: SceltaPopolazione 'Macro in cui digitiamo la popolazione 3: Do While ActiveCell <> "" 'esegue se non è vero che la cella è vuota 4: If ActiveCell > Contenitore Then 'se è superiore a 9999 5: Selection.Interior.ColorIndex = 6 'colora di giallo 6: End If ‘fine condizione 7: ActiveCell.Offset(1, 0).Range("A1").Select 'scende di una cella 8: Loop ‘ripeti 9: End Sub

Nota: Incominciando ad aumentare la complessità della procedura i commenti diventano indispensabili.

Probabilmente avrete già capito che, la prima istruzione eseguita dalla macro Colorare sarà quella di eseguire le istruzioni della procedura SceltaPopolazione, posta nella seconda riga. La procedura SceltaPopolazione, tramite InputBox, richiederà il valore desiderato, che una volta ricevuto memorizzerà nella variabile Contenitore. Terminata SceltaPopolazione, la macro Colorare continuerà a processare le sue istruzioni dalla riga 3 e passata alla riga 4 verificherà se il contenuto della cella attiva è superiore al valore contenuto nella variabile Contenitore. Tutto ciò spiega perché abbiamo dimensionato la variabile a livello di modulo: essa viene “riempita” nella procedura SceltaPopolazione, ma il suo contenuto viene “sfruttato” all’interno della macro Colorare.

Programmazione strutturata

In questa lezione, inserendo una procedura all’interno di un’altra procedura, abbiamo adottato la tecnica della programmazione strutturata. Più le procedure diventano lunghe e complesse, più difficile diventa seguirne il comportamento, correggerle, aggiornarle, sino a diventare pressoché ingestibili. Inoltre, a volte, una piccola sottoprocedura può essere riutilizzata più volte all’interno della stessa procedura ospitante, o addirittura da una un’altra procedura ancora.


80

Decolorare

Per rendere la nostra applicazione un po’ più comoda, registriamo una macro per togliere il colore dalle celle precedentemente formattate.

• Dalla barra degli strumenti Disegno scegliere Casella di testo e modellarne le forme accanto al pulsante “artigianale” Verifica, denominandolo Decolora.

• Posizioniamoci in F5, avviamo il registratore di macro, attribuendogli il nome Decolora e clicchiamo su OK per avviare la registrazione.

• Premere la combinazione di tasti CTRL+SHIFT+� (tasto direzionale giù) per selezionare tutte le celle contigue della colonna contenenti dati.

• Sulla barra degli strumenti di Formattazione agire sul pulsante Colore riempimento per scegliere Nessun riempimento.

• Attivare la cella F5 per deselezionare.

• Interrompere la registrazione.

Sub Decolora() 'Toglie il colore 'Risultato della pressione di CTRL+SHIFT+Tasto direzionale Giù Range(Selection, Selection.End(xlDown)).Select Selection.Interior.ColorIndex = xlNone 'No colore Range("F5").Select End Sub


81

PARTE 3 Addentrarsi nel VBA

Il viaggio nella conoscenza del VBA, ci porterà a perfezionare, approfondire e assemblare gli strumenti sino ad ora appresi. Continueremo il discorso su l’output dell’utente, impareremo a convivere con gli errori, a creare finestre di dialogo, a costruire utilità atte a facilitare il nostro lavoro.


82

Lezione XVI – Trova

In questa lezione implementeremo la nostra procedura di “colorazione”, con una funzione di ricerca dei comuni.

Pulire l’Editor di VB

I moduli collegati al file Comuni incominciano ad essere un po’ disordinati: vediamo di fare un po’ di ordine.

• Avviare l’Editor di VB.

• Dalla finestra Progetto attivare il modulo Modulo3 facendo un doppio clic.

• Con il mouse selezionare le due macro Navigator e Navigator2 e premere la combinazione di tasti CTRL+X per tagliarle.

• Transitare al Modulo2 e posizionarsi al fondo, creando eventualmente delle righe vuote con degli invii.

• Premere la combinazione di tasti CTRL+V per incollare le macro precedentemente tagliate.

• Nella finestra Proprietà posizionarsi in corrispondenza di (Name) per rinominare Modulo2 in Varie.

• Nella finestra Progetto cliccare con il tasto destro su Modulo3 e dal menu contestuale scegliere la voce Rimuovi Modulo3…

Nota: I nomi, dei moduli menzionati potrebbero non corrispondere esattamente a quelli creati sul vostro PC. Tale eventualità non dovrebbe comunque rappresentare un problema.


83

Farà la sua comparsa una finestra messaggio, con la quale sarà richiesto se si desidera esportare il modulo prima di eliminarlo. Per esportare s’intende creare un file con estensione bas, contenente il codice del modulo in fase di eliminazione. Nel nostro caso possiamo tranquillamente ignorare la richiesta di esportazione e quindi cliccare su NO.

La logica conseguenza, della presenza di una opzione di esportazione, è che sia disponibile anche la possibilità di importare del codice da un file esterno al nostro progetto. Già l’osservazione del menu contestuale scaturito dall’operazione di eliminazione del modulo, rileva l’opzione Importa file…, richiamabile anche dal menu File dell’Editor.

Una macro per trovare

• Portandosi nel foglio Comuni, si faccia partire il registratore, attribuendo alla macro il nome di TrovaComune.

• Nella finestra di dialogo Trova e sostituisci, richiamabile dal menu Modifica di Excel, digitare nella casella Trova: il Comune di Almese e cliccare su Trova successivo per avviare la ricerca.

• Fermare la registrazione.

• Attivare l’Editor di VB.


84

La conseguenza dell’utilizzo del registratore, sarà la creazione del Modulo1. Nella finestra Progetto dell’Editor di VB effettuiamo un doppio clic su Modulo1 per visualizzare il codice testè registrato.

Sub TrovaComune() Cells.Find(What:="almese", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate End Sub

Nota: Il codice sopra riportato, probabilmente differisce da quello generato dal vostro registratore nei punti ove va a capo.

La macro generata per trovare il Comune è sicuramente troppo logorroica. D'altronde se osservate la finestra di dialogo Trova e sostituisci, raffigurata nella pagina precedente, si nota che riproduce tutte le opzioni in essa presenti. Troppe! Certamente mettere mano al codice di una macro non è necessariamente semplice, ma è possibile. Si possono eliminare dei pezzi di codice e provare a vedere cosa succede. Metodo un po’ empirico, ma a volte funziona.

La guida in linea

Per rendere l’operare a tentativi un po’ più consapevole, facciamoci aiutare dalla guida in linea. Si evidenzi il metodo Find e si schiacci il tasto F1: partirà la guida contestualmente alla parola selezionata. Probabilmente non sarà ben comprensibile quello che la guida vi illustrerà, ma con un po’ di attenzione potrete estrapolare dal tutto il segmento di codice qui di seguito riportato...

.Find(2, lookin:=xlValues) …dal quale si capisce che Find, tutto sommato, funziona anche con pochi argomenti. La guida, inoltre, offre la possibilità di approfondire lo studio della sintassi del metodo Find, cliccando su Metodo Find applicato all’oggetto Range.


85

Vediamo adesso come adattare il frammento di codice prelevato dalla guida alle nostre esigenze

Find Cells.Find, come il registratore aveva generato dovrebbe essere adatto al nostro caso.

2

con 2 viene cercato un numero e corrisponde al What:="almese" prodotto dal registratore. Da ciò si deduce che what può essere omesso e che volendo cercare del testo lo dovremo inserire fra virgolette, ottenendo come risultato “almese”.

lookin:=xlValues

questa istruzione specifica il tipo d’informazioni da cercare, che qui vediamo essere valori (xlValues), mentre nell’esempio della macro è formule (xlFormulas). Se però andiamo a leggere nel dettaglio la guida in linea, essa ci dice lookin è un argomento facoltativo, quindi, salvo esigenze specifiche, eliminabile.

Nell’esempio tratto dalla guida non è presente, ma Activate è presente nel codice della macro che abbiamo registrato. Indubbiamente serve, poiché una volta localizzato il nome cercato, si vuole anche attivare la cella che lo ospita.

Sub TrovaComune() Cells.Find("almese").Activate End Sub

Niente da dire! Il risultato della rielaborazione della macro iniziale è decisamente più “leggero”, con il vantaggio di guadagnarci in velocità di esecuzione delle istruzioni e di leggibilità. Il problema è che la macro TrovaComune, per quanto stringata è estremamente rigida: cerca solo il Comune di Almese e per variare la ricerca occorre agire direttamente sul codice, possibilità ovviamente improponibile per un utente finale. La prossima lezione sarà dedicata a come rendere più flessibile la ricerca dei dati.

Nota: Il metodo Activate applicato all’oggetto Range (cella), come il metodo Select, attiva una cella. La differenza fra i due metodi è che Select può agire o su di una cella singola o si un intervallo di celle, mentre Activate ha “influenza” su di una sola cella.


86

Lezione XVII – InputBox e Trova

Partendo dal codice creato nella precedente lezione, cercheremo di creare una vera e propria procedura di ricerca dati.

Una variabile contro la rigidità

Abbiamo visto nella precedente lezione, che la rigidità della nostra procedura deriva dal fatto di aver inserito nei suoi argomenti direttamente il valore da ricercare.

Cells.Find("almese").Activate Se noi sostituissimo la stringa, che in questo caso indica Almese, con una variabile, avremmo risolto il problema di poter variare a piacimento l’oggetto della ricerca. Quindi dobbiamo trovare il modo di far assumere alla variabile il valore di volta in volta scelto. Grazie ad InputBox il problema è già praticamente risolto! Useremo la finestra di input per chiedere all’utente quale Comune vuole ricercare.

Adattare una macro preesistente

• Nell’Editor di VB attivare il Modulo1.

• Selezionare e copiare, tramite il menu di scelta rapida che compare alla pressione del tasto destro, negli Appunti la macro TrovaComune per andarla ad incollare alla fine del modulo ModColorare.

• Nella finestra Progetto, cliccare con il tasto destro su Modulo1 ed eliminarlo scegliendo dal menu contestuale la voce Rimuovi Modolo1.

• Visualizzare il contenuto del modulo ModColorare, selezionare la macro SceltaPopolazione e copiarla a sua volta negli Appunti.

• Incollare SceltaPopolazione al di sotto della macro TrovaComune e rinominarla in SceltaComune.

• Portarsi all’inizio del modulo nell’area Dichiarazioni ed aggiungere una seconda variabile a livello di modulo denominata, alla faccia della fantasia, Contenitore2.

Dim Contenitore, Contenitore2 As Variant

• Tornare alla fine del modulo e modificare la macro SceltaComune come di seguito riportato.


87

Sub SceltaComune() Dim Titolo, Messaggio,Default As String Titolo = "Corso on line di VBA per Excel" Messaggio = "Digita il Comune da trovare" Default = "Torino" Contenitore2 = Application.InputBox _ (Messaggio, Titolo, Default, Type:=2) 'Tipo 2, cioè stringa End Sub

Come si può vedere le variazioni apportate sono veramente minimali. Si è ovviamente variato il messaggio da comunicare, abbiamo precisato che la variabile ove appoggiare l’output è Contenitore2 e che, ovviamente, i dati sono di tipo stringa. SceltaComune raccoglie, nella variabile Contenitore2, il Comune da ricercare e deve passarlo alla procedura TrovaComune. Quindi SceltaComune rappresenterà la prima istruzione di TrovaComune, che a sua volta andrà a cercare il contenuto della variabile Contenitore2. L’argomento What del metodo Find diverrà la variabile Contenitore2.

Sub TrovaComune() SceltaComune Cells.Find(Contenitore2).Activate End Sub

Una immagine come pulsante

Abbiamo già usato la Casella di testo come pulsante alternativo, ma nulla ci vieta di fare ricorso ad altri oggetti grafici, tipo una clipart. Dal menu Inserisci\Immagine scegliere clipart. Modellare l’immagine sul foglio e cliccandoci sopra con il tasto destro, dal menu contestuale scegliere la voce Assegna macro, per attribuirgli la macro TrovaComune.


88

Lezione XVIII – Gestione degli errori

Alcuni manuali di programmazione introducono la gestione degli errori come primo argomento da affrontare. Ciò testimonia la grande importanza rivestita da tale argomento. Nel nostro corso abbiamo deciso di non assumere una posizione così estrema, anche perché una trattazione approfondita sulla gestione degli errori, rischierebbe di diventare più impegnativa dello stesso corso nel suo insieme. Iniziamo con una presentazione teorica delle tre diverse famiglie di errori

Errori di linguaggio o di sintassi

Derivano da errori di digitazione, tipo l’omissione di segni di punteggiatura, la mancanza di parentesi, o per dimenticanza di una delle parti dei blocchi di istruzione, quali If senza End If e così via. Questi errori possono essere individuati abbastanza facilmente.

Errori di runtime

Si verificano e vengono individuati quando un’istruzione tenta di eseguire un’operazione che non è possibile completare in fase di esecuzione. Un esempio è la divisione per zero. Vediamo un esempio:

Densit = Popolaz \ Superfic Ove la variabile Superfic assume il valore zero, l’operazione diventa impossibile anche se la sintassi dell’istruzione è corretta. Si dice errore di runtime perché viene individuato solo se il codice è in esecuzione. Questo genere di errori non è sempre facilmente individuabile.

Errori logici

In questo ultimo caso, pur in presenza di codice sintatticamente corretto, che esegue operazioni valide, abbiamo risultati non desiderati. Visual Basic non individua gli errori logici, pertanto occorre effettuare un controllo funzionale del codice. Chiaramente questi tipi di errori risultano essere decisamente infidi.


89

Il primo errore

Iniziamo subito in salita, affrontando un errore logico, in cui siamo caduti nell’ultima lezione con la procedura di ricerca. Se avete provato a cercare un Comune, la macro TrovaComune ha sicuramente dato dei buoni risultati, a meno che la scelta sia caduta su Torino. In tal caso, abbastanza inspiegabilmente, la cella si posiziona sempre in B2. Guardando con più attenzione il contenuto della cella, Comuni Provincia di Torino, scopriamo l’arcano: La nostra procedura per default non Confronta intero contenuto della cella, quindi si ferma in B2 contenente una frase con la parola Torino. Capito l’inghippo, per trovare il giusto argomento, che “imponga” a Find, di cercare una esatta corrispondenza ai caratteri specificati in InputBox, andiamo a consultare la guida in linea, che purtroppo non si rivela molto chiarificatrice. Questo è uno di quei casi, ove s’impara ad apprezzare in pieno il Registratore di macro. Registriamo le azioni di cercare un valore, qualunque esso sia, con Trova del menu Modifica, senza attivare l’opzione Confronta intero contenuto della cella, quindi ripetiamo la registrazione, questa volta spuntando Confronta intero contenuto della cella. Confrontiamo il risultato delle due registrazioni per individuare ove si differenziano ed ecco le due istruzioni di nostro interesse.

LookAt := xlPart LookAt := xlWhole Anche con un inglese deboluccio si comprende che Part è parte e Whole è intero, quindi l’istruzione che ci necessita è LookAt := xlWhole. Pertanto la macro TrovaComune assumerà questa nuova forma.

Sub TrovaComune() SceltaComune Cells.Find(Contenitore2, LookAt:=xlWhole).Activate End Sub

Ancora logica

Rimanendo sempre in tema di errori logici, anche la procedura Colorare presenta un inconveniente piuttosto fastidioso. Se vogliamo che colori le celle desiderate, essa ci obbliga a posizionarsi sulla giusta cella di partenza prima di avviare la macro. Fortunatamente la soluzione è estremamente semplice: nel modulo ModColorare posizionarsi in corrispondenza della macro Colorare, creare una riga vuota fra le righe con le istruzioni SceltaPopolazione e Do While…


90

e digitarvi Range(“F5”).Select. In tal modo, indipendentemente dalla cella di partenza, la macro Colorare si posizionerà sempre nel punto giusto ove iniziare a testare i valori delle celle. Un ultimo ritocco, sempre a Colorare, consiste nel non disorientare l’utente che utilizzerà il nostro prodotto. Se dopo aver formattato le giuste celle, il foglio rimane visualizzato intorno alla trecentesima riga, il che non è un bello spettacolo, oltre ad essere una potenziale fonte di confusione per utenti non esperti, occorre riposizionarlo all’inizio. Dopo l’istruzione Loop e prima, ovviamente, di End Sub, inserire Range(“A1”).Select.

Un errore da runtime

Molto spesso gli errori di runtime sono il frutto di “pasticci” effettuati con la tastiera. Prendiamo sempre la macro Colorare ad esempio e simuliamo l’inavvertita cancellazione dell’istruzione Loop, quindi proviamo ad eseguire suddetta procedura. Vedremo comparire l’avviso di errore a lato raffigurato, che molto chiaramente spiega l’origine del “guasto”.

Dopo aver fatto clic sul pulsante OK sarà visualizzata la macro Colorare, evidenziata in giallo, ad indicare lo stato di errore della procedura. E’ importante ripristinare la macro andando a cliccare sul pulsante Ripristina della barra degli strumenti Standard dell’Editor di VB. Naturalmente occorrerà anche inserire nuovamente l’istruzione Loop.

Un caso ambiguo

Insistiamo con Colorare e questa volta la nostra sbadataggine ci fa eliminare If finale, lasciando vedovo End. Provando ad eseguire la macro sarà nuovamente comunicato un errore: il problema è che il VBA, prendendo farfalle, ci dirà Loop senza Do. Così un piccolo refuso da tastiera rischia di diventare un tormentone, contro il quale l’unica “cura” è leggersi con cura il listato.


91

Un errore “ortografico”

Per ultima affrontiamo la prima tipologia di errori: gli errori di linguaggio o di sintassi. Un po’ sgrammaticati digitiamo While senza H e facciamo clic sulla riga successiva. Immediatamente saremo ammoniti del nostro erroraccio.

Un errore “spaziale”

Induciamo la macro Colorare a commettere un errore grossolano, modificando il secondo valore di Offset da 0 a -10, cioè da nessun movimento in orizzontale a dieci celle a sinistra.

ActiveCell.Offset(1, -10).Range("A1").Select

Ecco una nuova finestra di segnalazione errori, ove dovremo cliccare sul pulsante Debug.

Non finiscono mai

Il problema è che non è così semplice: la casistica degli errori è ben più ampia di quella illustrata in queste poche pagine. Molte volte le varie categorie di errori sembrano mescolarsi fra di loro e più i programmi diventano grandi, più diventa complicato venirne a capo. Fortunatamente il VBA possiede degli strumenti in grado di darci una mano in questi frangenti. La prossima lezione sarà appunto dedicata ad approfondire le tematiche sulla gestione degli errori e il controllo sul flusso delle procedure.


92

Lezione XIX – Debug

In VBA sono disponibili strumenti di debug che consentono di analizzare il funzionamento delle macro e che risultano particolarmente utili per individuare le cause degli errori. Questi strumenti possono inoltre essere utilizzati per provare, capire e modificare le procedure create da altri sviluppatori.

Punti di interruzione

Per riuscire a rintracciare la causa di errori, molte volte si dovrà scorrere le istruzioni di una procedura passo per passo. L’esecuzione a passo singolo di tutte le istruzioni di una procedura può diventare decisamente faticosa, ma solo in alcuni casi è veramente necessario scorrere tutte le righe del programma. Fortunatamente, il VBA consente di eseguire la maggior parte del codice a velocità normale e passare in modalità Interruzione, cioè a processare una riga per volta, solo quando vengono eseguite particolari istruzioni all’interno del codice. Un punto di interruzione è una riga all’interno del codice evidenziata da una striscia di colore marrone, con al suo fianco un pallino dello stesso colore. Quando incontra un punto d’interruzione, il VBA passa dalla normale esecuzione del codice alla modalità Interruzione. Usando i punti di interruzione è possibile seguire la maggior parte di una procedura a velocità piena e passare in modalità Interruzione solo quando il VBA raggiunge l’istruzione che occorre esaminare con attenzione.

Effettuare il debug

Per effettuare il debug di una procedura occorre visualizzare la barra degli strumenti Debug, richiamabile dal menu Visualizza\Barre degli strumenti dell’Editor VB.

Attiviamo il modulo ModColorare e cerchiamo la macro Colorare, quindi in corrispondenza della terza riga della suddetta procedura fare clic all’interno del margine sinistro per impostare il punto di interruzione.


93

Quindi fare clic sul pulsante Esegui Sub, posto sulla barra degli strumenti Debug.

Saranno immediatamente eseguite le istruzioni della sotto procedura SceltaPopolazione, per poi fermarsi in corrispondenza della riga con il punto d’interruzione. A questo punto fare clic sul pulsante Esegui Istruzione, posto sulla barra degli strumenti Debug per eseguire un passo alla volta.

Dopo il clic sul pulsante Esegui Istruzione la riga gialla si sposta nella riga in basso. In questa fase è stata eseguita l’istruzione della riga iniziale (quella marrone) e il VBA è in attesa di eseguire l’istruzione della riga colorata di giallo. Eseguendo nuovamente clic sul pulsante Esegui Istruzione, viene controllato il valore contenuto nella variabile Contenitore che, nel caso illustrato nell’immagine sottostante, è deducibile essere inferiore al valore della cella, perché invece di passare all’istruzione successiva, ove avrebbe colorato la cella, salta immediatamente a End If.

Continuando a cliccare su Esegui Istruzione processeremo riga per riga, sino a quando, terminate le esigenze di indagine del codice, interromperemo il debug cliccando sul pulsante Ripristina. E’ importante ricordarsi di togliere il punto d’interruzione, risultato ottenibile eseguendo le stesse operazioni richieste per definirlo, cioè cliccando sopra il pallino marrone.

Nota: Il pulsante Esegui Sub, utilizzato senza l’intermediazione del punto d’interruzione esegue il flusso della procedura “tutto di un fiato”.


94

E’ possibile eseguire il debug anche senza l’ausilio del punto d’interruzione. In tal caso posizioniamo il cursore in corrispondenza della procedura che si desidera verificare e cliccando sul pulsante Esegui istruzione sarà eseguita una istruzione alla volta, partendo dall’inizio della macro. Per interrompere il debug, anche in questo caso, ricorrere al pulsante Ripristina, oppure, per far scorrere velocemente tutto il codice, fare clic sul pulsante Esegui Sub.

Intercettare gli errori di runtime

Per quanti sforzi si facciano in fase di sviluppo di un programma, non sarà possibile “sterminare” tutti gli errori, specie se l’input dei dati dipende dagli utenti. Citando l’esempio della precedente lezione, può accadere che l’utente, perfido, immetta in InputBox uno 0 come divisore. Il VBA è dotato di un sistema di rilevazione degli errori che permette di evitare la visualizzazione dei messaggi di errore. Tale risultato è ottenibile dalla seguente sintassi:

On Error GoTo SaltaErrore Specificando che SaltaErrore è un nome di fantasia, definito etichetta (label), possiamo tradurre il codice in: In caso di errore salta all’etichetta SaltaErrore.

Sub StudioErrori() Dim Popolaz, Densit, Superfic As Long Popolaz = 2000 Superfic = 0 On Error GoTo SaltaErrore Densit = Popolaz \ Superfic MsgBox "Ogni abitante dispone di " & Densit & " metri quadri" Exit Sub SaltaErrore: MsgBox "Impossibile dividere per zero", vbCritical End Sub

Dall’esempio si può vedere che l’etichetta SaltaErrore: è stata posizionata per entrare in azione in caso di errore, al termine delle normali istruzioni. Si è dovuto, inoltre, aggiungere l’istruzione Exit Sub per terminare in anticipo la macro, in modo che non esegua le istruzioni relative agli errori, se errori non ci sono.


95

Lezione XX – I Forms

Fino a questo momento abbiamo preso “confidenza” con le due finestre di dialogo incorporate del VBA: la funzione MsgBox e la funzione/metodo InputBox. Sebbene questi due oggetti possano garantire ad un programma una buona dose di interattività, il loro utilizzo non permette di raggiungere un elevato grado di flessibilità e di personalizzazione. Fortunatamente il VBA permette di creare delle finestre di dialogo personalizzate, conosciute con il nome di Forms.

Il primo form

Dal menu Inserisci, dell’Editor di VB, scegliere la voce UserForm.

Faranno la loro comparsa due nuovi elementi: un form denominato UserForm1 e la barra degli strumenti Casella degli strumenti.

A seguito dell’inserimento del Form, la finestra Progetto visualizza una ulteriore ramificazione con la cartella Form e al suo interno, il Form appena generato UserForm1. Osservando la finestra delle Proprietà, con il Form selezionato, si nota come siano decisamente aumentati gli elementi, le proprietà appunto, in essa presenti . Modifichiamo la proprietà nome (Name) in Dialogo, quindi modifichiamo anche la proprietà Caption in Form di Studio.


96

Potremo osservare che a seguito della modifica della proprietà Caption è cambiato il nome presente sulla barra del titolo del Form. A questo punto occorre fare un po’ di chiarezza sulla differenza fra le proprietà Name e Caption, che probabilmente, non risulta immediatamente comprensibile. Name è il nome dell’oggetto, tramite il quale è identificabile tra altri oggetti. Caption è una semplice etichetta descrittiva.

Forms e pulsanti

Il foglio di lavoro Comuni, incomincia ad essere un po’ troppo affollato da pulsanti di vario genere. Spostiamo questi controlli sulla finestra di dialogo. Selezionare Pulsante di comando dalla barra degli strumenti Casella degli strumenti e modellarlo nel Form Dialogo. Notate che con il Pulsante di comando selezionato, la finestra delle Proprietà si aggiorna automaticamente all’oggetto selezionato.

Personalizzare il pulsante tramite la finestra delle Proprietà.

In un Form, per modificare l’aspetto di un Pulsante di comando, o di un altro controllo, occorre agire sulle sue proprietà.

• Cambiare la descrizione del pulsante in Verifica agendo sulla proprietà Caption.

• Cambiare il colore della scritta grazie alle tavolozze dei colori della proprietà ForeColor.

• Infine grassettare la scritta agendo sulla proprietà Font.

Agendo sulla proprietà Font è stato visualizzato il pulsante Genera (quello con i tre puntini), sul quale occorre fare clic per visualizzare una finestra di dialogo con le relative proprietà.


97

Evento clic

Ora dovremmo associare al pulsante il listato della procedura Colorare, ma ormai, non siamo più in un foglio di lavoro: quindi se nel menu contestuale, richiamato con il clic del tasto destro sul pulsante, cercheremo la voce Assegna macro, non ne troveremo traccia. Siamo entrati nella logica della programmazione ad eventi. Un evento è un azione riconosciuta da un oggetto, per la quale è possibile definire una risposta. Esempi di eventi possono essere: il clic del mouse o la pressione su di un tasto. Un evento può essere originato dall’intervento di un utente o da una macro.

• Nella finestra Progetto fare doppio clic su ModColorare.

• Selezionare il codice della macro Colorare, escludendo le righe di inizio e fine procedura e copiarlo negli Appunti (Modifica\Copia).

• Attivare il form Dialogo.

• Posizionare il puntatore del mouse sopra il pulsante di comando ed eseguire un doppio clic.

• Apparirà un modulo relativo al Form Dialogo, impostato sull’evento Click del pulsante.

• Posizionarsi nel corpo della procedura CommandButton1_Click( ) e incollare il contenuto degli Appunti, cioè il listato della macro Colorare.

Private Sub CommandButton1_Click() SceltaPopolazione 'Macro in cui digitiamo la popolazione Range("F5").Select Do While ActiveCell <> "" 'esegue se non è vero che la cella è vuota If ActiveCell > Contenitore Then 'se è superiore a xxxx Selection.Interior.ColorIndex = 6 'colora di giallo End If ActiveCell.Offset(1, 0).Range("A1").Select 'scende di una cella Loop Range("a1").Select End Sub


98

Verifichiamo il funzionamento del nostro operato assicurandoci che il form Dialogo sia attivo e clicchiamo sul pulsante Esegui Sub/User Form. La finestra di dialogo Dialogo, per quanto un po’ sgraziata, farà la sua comparsa nel foglio di lavoro. Ma amara è la sorpresa nel constatare che cliccando sul pulsante Verifica, dopo aver diligentemente digitato il valore richiestoci da InputBox, otteniamo un'unica uniforme pennellata di giallo. Perché questo errore? Ricordate il discorso delle variabili con la portata estesa a livello di modulo? Bene, dichiarando la variabile Contenitore nell’area di dichiarazione dei moduli l’abbiamo resa disponibile a livello di tutto il modulo, ma non disponibile fra moduli diversi, come invece necessita in questo caso. Occorre rendere la variabile Contenitore disponibile per tutti i moduli della Cartella, risultato ottenibile sostituendo, nell’area di dichiarazione del modulo ModColorare, la parola chiave Dim con Public.

Public Contenitore, Contenitore2 As Variant

Pubbliche e Private

Presentando la parola chiave Public, balzerà all’occhio per analogia che, la macro CommandButton1 è preceduta da Private. Le procedure in VBA per default sono Public, cioè accessibili per tutte le altre routine in tutti i moduli. Si utilizza la dichiarazione Private per rendere una procedura accessibile alle sole routine incluse nel modulo di appartenza. Si dichiarano le procedure come Private quando si vogliono evitare indesiderate interferenze fra routine di vari moduli e per risparmiare preziosa memoria.


99

Lezione XXI – Forms: atto II

Nella precedente lezione abbiamo lasciato il nostro Form un po’ incompiuto. Per poterlo vedere in azione, dall’Editor abbiamo agito sul pulsante Esegui Sub/User Form, prospettiva improponibile per un utente finale.

Dare “vita” ad un Form

• Attivare il foglio di lavoro Comuni.

• Eliminare i pulsanti che richiamano le procedure TrovaComune (il microscopio) e Decolora, cliccandoci sopra con il tasto destro e scegliendo Taglia.

• Fare clic sul pulsante Verifica tenendo premuto il tasto CTRL e modificare il testo descrittivo in Scegli opzioni.

• Attivare l’Editor, visualizzare il modulo ModColorare e posizionarvisi alla fine digitando la seguente procedura:

Sub LanciaDialogo() Dialogo.Show End Sub

• Ritornare sul foglio Comuni e cliccando con il tasto destro sullo unico pulsante rimasto, associargli la macro testé creata: LanciaDialogo.

La sintassi per eseguire una finestra di dialogo, come si può vedere, è estremamente sintetica: nome oggetto, che nel nostro caso è il Form Dialogo, seguito dal metodo Show.


100

Chiudere un Form

Il Form Dialogo per essere chiuso ci obbliga ad agire sulla X posta sulla barra del titolo. Volendo porre un pulsante espressamente adibito alla chiusura del Form, seguiamo i seguenti passi.

Nell’Editor di VB attivare il Form Dialogo.

• Generarci sopra un nuovo pulsante e denominiamolo Chiudi.

• Fare doppio clic sul pulsante testé creato.

• Digitare l’istruzione END nel corpo della procedura, per ottenere il risultato visualizzato nel sottostante codice di esempio.

Private Sub CommandButton2_Click() End End Sub

Se poi si vuole che la finestra di dialogo venga chiusa immediatamente dopo aver eseguito la procedura Colorare, connessa al pulsante Verifica, aggiungere l’istruzione End prima dell’ultima riga.

End End Sub

Verificate il funzionamento dei due pulsanti.

Aggiungere un altro pulsante

Aggiungiamo un terzo pulsante al Form Dialogo per gestire la decolorazione.

• Nella finestra Progetto dell’Editor VB eseguire un doppio clic sul modulo ModColorare per visualizzarlo.

• Selezionare il codice della routine Decolora escludendo la prima e l’ultima riga e copiarlo negli Appunti.

• Nella finestra Progetto dell’Editor VB eseguire un doppio clic sul Form Dialogo per visualizzarlo.


101

• Nella Casella degli strumenti cliccare sul Pulsante di comando e modellarlo sul Form.

• Variare la proprietà Caption del nuovo pulsante in Decolora.

• Fare doppio clic sul pulsante etichettato Decolora, per visualizzare un modulo relativo al Form Dialogo, impostato sull’evento Click del pulsante.

• Posizionarsi nel corpo della procedura CommandButton3_Click() e incollare il contenuto degli Appunti, cioè il listato della macro Decolora.

• Aggiungere End come ultima istruzione della procedura, cioè prima dell’utima riga, per chiudere il Form.

Duplicare i pulsanti

I pulsanti di un Form, in linea di massima, devono avere tutti le stesse dimensioni, tipo di carattere e così via. L’operazione di generare il pulsante etichettato Decolora è stata piuttosto lenta e imprecisa, costringendoci a reimpostare tutte le proprietà sul modello del precedente pulsante etichettato Verifica. Per evitare una simile trafila, selezionare il pulsante da usare come modello ciccandoci sopra, quindi premere la combinazione di tasti CTRL+C per copiarlo negli Appunti, infine cliccare in un punto vuoto qualsiasi del Form e premere la combinazione di tasti CTRL+V per incollare un clone del primo pulsante. A questo nuovo pulsante si dovrà cambiare la proprietà Caption e a seguito del doppio clic associare il codice di pertinenza. E’ abbastanza intuitivo che con questo sistema riprodurre anche un gran numero di pulsanti tutti uguali, non rappresenterebbe un problema.


102

Lezione XXII – Allora Se

Questa lezione, grazie anche all’approfondimento della struttura decisionale If, è dedicata a rendere più flessibile ed interattiva la procedura di colorazione delle celle.

Chiedere conferma

Prima di eseguire delle modifiche, molte volte, le procedure chiedono conferma all’utente tramite una finestra messaggio. Per implementare la suddetta modifica seguire i seguenti passi.

• Attivare il modulo ModColorare, e portarsi all’inizio nell’area Dichiarazioni ed aggiungere una terza variabile a livello di modulo, denominata Risul.

Public Contenitore, Contenitore2, Risul As Variant

• Successivamente digitare la seguente procedura.

Sub Messaggi1() Dim Mess, Titolo As String Mess = "Sei sicuro di voler procedere." Titolo = "Corso on line di VBA per Excel" Risul = MsgBox(Mess, vbYesNo + vbQuestion, Titolo) End Sub

Abbiamo creato una procedura espressamente dedicata alla gestione di messaggi per l’utente. Posizionando il cursore in corrispondenza di una riga della procedura Messaggi1, cliccate sul pulsante Esegui Sub/ User Form per visualizzare questa “nuova” finestra messaggio. Notate la presenza di due pulsanti in luogo dell’unico esemplare presente negli altri esempi con MsgBox. Chiaramente i due pulsanti sono generati dall’istruzione vbYesNo. Un altro particolare da non trascurare è la variabile, a livello di modulo, Risul che riceverà l’output del pulsante sul quale l’utente avrà fatto clic. Portarsi nel Form Dialogo e cliccare due volte sul pulsante etichettato Verifica.


103

Apportare alla Sub CommandButton1_Click le variazioni evidenziate in grassetto visibili nel codice sottostante.

Private Sub CommandButton1_Click() SceltaPopolazione Range("F5").Select Messaggi1 ‘procedura di visualizzazione messaggio If Risul = vbNo Then Exit Sub ‘se cliccato sul pulsante No esce Do While ActiveCell <> "" If ActiveCell > Contenitore Then Selection.Interior.ColorIndex = 6 End If ActiveCell.Offset(1, 0).Range("A1").Select Loop Range("a1").Select End End Sub

Se in seguito alla visualizzazione della finestra messaggio, generata dalla procedura Messaggi1, si clicca sul pulsante NO, la procedura viene subito terminata. Altrimenti saranno eseguite tutte le istruzioni per colorare le celle che contengono più di tot abitanti.

Scegliere un colore

Nella creazione del messaggio di conferma, abbiamo fatto ricorso alla struttura decisionale If, nella sua forma più semplice: in realtà If può esprimere ben altri livelli di strutturazione. Poniamo il caso di voler dare all’utente della nostra applicazione, la possibilità di attingere il colore con cui formattare le celle da una tavolozza di almeno tre colori: il giallo, il rosso e il verde. Sappiamo che nell’indice della tavolozza dei colori il giallo corrisponde al 6. Per conoscere il valore corrispondente al rosso e al verde, registrare una macro che colora due celle rispettivamente dei due colori desiderati. Il rosso corrisponde al 3 ed il verde al 4.

I Pulsanti di opzione

Ma come fare a dare all’utente la possibilità di comunicare alla procedura il colore da lui preferito? Usando i Pulsanti di opzione!

• Attivare il Form Dialogo.

• Eventualmente ingrandirlo per facilitare l’introduzione di nuovi oggetti.

• Sulla Casella degli strumenti fare clic sul pulsante Cornice e modellare il controllo sul Form, tenendo conto che dovrà contenere tre Pulsanti di opzione.


104

• Dalla Casella degli strumenti scegliere il Pulsante di opzione e modellarlo all’interno dell’oggetto Cornice teste introdotto nel Form.

• Ripetere le operazioni del paragrafo precedente per altre due volte.

• Modificare, nella finestra Proprietà, la proprietà Caption del Pulsante di opzione OptionButton1 in Giallo, ricordando che il controllo in oggetto deve essere selezionato..

• Procedere nel modificare la proprietà Caption degli altri due Pulsanti di opzione, in Rosso e Verde.

• Modificare la proprietà Caption della Cornice in Colore.

• Fare clic sul pulsante Esegui Sub / User Form per verificare il funzionamento dei Pulsanti di opzione.

I Pulsanti di opzione si escludono reciprocamente, quindi il loro funzionamento è corretto quando uno solo di essi rimane selezionato. Sono stati introdotti all’interno di una Cornice poiché in caso contrario non funziona l’esclusione vicendevole. Nell’eventualità si riscontrassero dei problemi nel rendere uniforme l’aspetto dei Pulsanti di opzione agire sulla proprietà Height (altezza) ed uniformarne il valore. Occorre poi definire qual è il colore predefinito, che nel caso dell’immagine a lato è il giallo. Per ottenere suddetto risultato, impostare la relativa proprietà Value su True, controllando che la proprietà Value degli altri due Pulsanti di opzione sia impostata su False.

If…Then…Else If…Else…End If

Nell’Editor di VB visualizzare il Form Dialogo e dal menu Visualizza scegliere la voce Codice, per accedere al modulo del Form. Nell’area di dichiarazione, cioè all’inizio del modulo, dimensionare una variabile denominata Pennello con portata a livello di modulo.

Dim Pennello As Integer


105

Quindi digitare la seguente procedura.

Sub ScegliColore() If OptionButton1.Value = True Then 'se il primo pulsante Pennello = 6 'giallo ElseIf OptionButton2.Value = True Then 'altrimenti se il secondo Pennello = 3 'rosso Else 'altrimenti il terzo Pennello = 4 'verde End If End Sub

Questa volta la struttura decisionale If ha scelto fra tre possibilità. Dopo la prima possibilità If…Then è passato alla seconda ElseIf…Then, infine l’ultima con Else senza il Then. Se invece di avere tre scelte, ne avessimo avute quattro, la terza sarebbe stata ElseIf…Then e la quarta Else. Infine modificare la procedura CommandButton1_Click legata alla formattazione delle celle come di seguito riportato.

Private Sub CommandButton1_Click() SceltaPopolazione 'Macro in cui digitiamo la popolazione ScegliColore ‘Macro per scegliere il colore Range("F5").Select Messaggi1 ‘Macro per scegliere se controllare If Risul = vbNo Then Exit Sub Do While ActiveCell <> "" ' esegue se non è vero che la cella è vuota If ActiveCell > Contenitore Then 'se è superiore a 9999 Selection.Interior.ColorIndex = Pennello 'colora End If ActiveCell.Offset(1, 0).Range("A1").Select 'scende di una cella Loop Range("a1").Select End End Sub

Dopo la procedura SceltaPopolazione, abbiamo introdotto la procedura ScegliColore adibita a raccogliere la scelta del colore dai Pulsanti di opzione. Per l’indicazione del colore da usare con Selection.Interior.ColorIndex = abbiamo introdotto la variabile Pennello contenente il valore del colore scelto.


106

Case

La struttura If ElseIf, consente di nidificare al suo interno altre strutture If o anche If ElseIf, un po’ come in Excel nidifichiamo la funzione Se, con la differenza che in VBA le possibilità sono enormemente più elevate. Però se la scelta dovesse avvenire fra molti elementi, una decina per esempio, la struttura If EsleIf, pur funzionando, non rappresenterebbe l’ideale. Se ciascuna istruzione ElseIf esegue un confronto della stessa espressione con valori diversi, questo approccio risulta complicato. In tal caso, è consigliabile utilizzare l’istruzione Select Case.

Sub StudioCase() Dim Numero As Integer Number = 7 ' Inizializza la variabile Select Case Number Case 1 ' Numero 1 MsgBox "Sì uno", vbExclamation Case 6, 7, 8 ' Numeri tra 6 e 8 MsgBox "Sì da 6 a 8", vbExclamation Case 9 To 12 ' Numeri da 9 a 12 MsgBox "Sì da 9 a 12", vbExclamation Case Else 'Gli altri numeri MsgBox "Altro", vbExclamation End Select End Sub

Per verificare il funzionamento di quest’ultima procedura, sostituire il contenuto della variabile Number.


107

Lezione XXIII – Ciclo For … Next

Il ciclo For … Next (Per …Successivo), consente di eseguire un determinato gruppo di istruzioni, un certo numero di volte. Un ciclo For … è un modo sintetico per scrivere un gran numero di istruzioni che fanno sostanzialmente la stessa operazione. Osserviamo questo semplice esempio.

Sub Saluti() Dim Conta As Integer For Conta = 1 To 5 MsgBox "Ciao" Next End Sub

Eseguite questa macro e la finestra messaggio vi “saluterà” per cinque volte. Se non avessimo usato il ciclo For per ottenere lo stesso risultato avremmo dovuto scrivere la procedura nel seguente modo.

Sub Saluti() MsgBox "Ciao" MsgBox "Ciao" MsgBox "Ciao" MsgBox "Ciao" MsgBox "Ciao" End Sub

Forse l’esempio illustrato non rende ancora giustizia alle potenzialità del ciclo For, ma osservate quest’altro listato.

Sub ProvaFor() Dim Conta, Elabora As Long Elabora = Application.InputBox("Digita un numero", , , Type:=1) For Conta = 1 To 5 Elabora = Elabora * 2 Next MsgBox Elabora End Sub

La variabile Conta è il contatore del ciclo For: ogni volta che il ciclo viene eseguito la variabile Conta aumenta di uno. Nella prima esecuzione del ciclo essa conterrà uno, nell’ultima ripetizione, se il ciclo viene ripetuto cinque volte, conterrà cinque.


108

Il numero di volte che il ciclo viene ripetuto è dato dal valore espresso dopo la parola chiave To, cioè il 5. 1 To 5, che può essere tradotto in : dall’inizio (1) alla fine (5). Quindi la variabile Elabora, che prima dell’inizio del ciclo, ha assunto il valore digitato in InputBox (per facilitare l’esposizione dell’esempio poniamo sia 3), assume il valore di se stessa moltiplicata per 2, cioè 3*2= 6. Il ciclo giunge a Next e da inizio alla seconda ripetizione con la variabile Elabora che avrà un valore di 6 e gl i sarà assegnato il valore di se stessa per 2, cioè 6*2 = 12. E così continuando sino alla quinta esecuzione, ove consegue il risultato di 96, visualizzato nella finestra messaggio.

Ricavare dati da una cella

Il ciclo For può diventare anche più flessibile rendendo dipendente il numero di ripetizioni da una variabile. Invece di avere 1 To 5 , possiamo proporre 1 To NomeVariabile. Preparare questo nuovo esempio, controllando che nella cartella in cui stiamo lavorando sia presente un foglio di lavoro denominato Foglio1, e digitare nella cella A1 il valore 5. Il numero di ripetizioni sarà determinato dal valore presente nella cella A1 del foglio Foglio1. Nel codice sottostante possiamo vedere che nella riga 3 la variabile Raccogli, assume il valore della cella A1. Nella riga 5 il numero di ripetizioni è determinato dalla variabile Raccogli.

1: Sub ProvaFor() 2: Dim Conta, Elabora, Raccogli As Long 3: Raccogli = Sheets("Foglio1").Range("a1") 4: Elabora = Application.InputBox("Digita un numero", , , Type:=1) 5: For Conta = 1 To Raccogli 6: Elabora = Elabora * 2 7: Next 8: MsgBox Elabora 9: End Sub

Verificare il funzionamento di questa procedura posizionando il cursore in un punto qualsiasi di ProvaFor e cliccare sul pulsante Esegui Sub. Noterete che cambiano il valore della cella A1 in Foglio1 cambierà il risultato finale.


109

Errore di Overflow

Ma in agguato c’è un errore. Provate a digitare 1020 in A1 e 20 in InputBox. Farà la sua comparsa una finestra di dialogo comunicante un errore di Overflow. Un Overflow può essere il risultato di un'assegnazione superiore al valore massimo previsto. Inseriamo nella nostra procedura la gestione degli errori, come visto nelle precedenti lezioni.

Sub ProvaFor() Dim Conta, Elabora, Raccogli As Long Raccogli = Sheets("Foglio1").Range("a1") Elabora = Application.InputBox("Digita un numero", , , Type:=1) On Error GoTo Salta For Conta = 1 To Raccogli Elabora = Elabora * 2 Next MsgBox Elabora Exit Sub Salta: MsgBox "Numeri troppo alti" End Sub

Proteggere e sproteggere

Molte volte si deve lavorare con cartelle di lavoro contenenti un gran numero di fogli, che per esigenze procedurali dobbiamo consegnare all’utente finale protetti. Durante la lavorazione e successivi aggiornamenti si dovrà procedere alla protezione e sprotezione dei fogli di lavoro più volte, attività decisamente lunga e noiosa, anche perché non effettuabile in multifoglio. Le due procedure qui di seguito riportate proteggono e sproteggono tutti i fogli di una cartella di lavoro in un battibaleno, inserendo anche una password, che naturalmente può essere variata a piacere.


110

Sub Proteggi() Dim spro, conta, fog As Integer fog = 1 spro = Worksheets.Count For conta = 1 To spro Worksheets(fog).Select fog = fog + 1 ActiveSheet.Protect Password:="lilli" Next Worksheets(1).Select End Sub

Sub Sproteggi() Dim spro, conta, fog As Integer fog = 1 spro = Worksheets.Count For conta = 1 To spro Worksheets(fog).Select fog = fog + 1 ActiveSheet.Unprotect Password:="lilli" Next Worksheets(1).Select End Sub

Rispetto agli esempi precedenti sono presenti I seguenti elementi di innovazione.

spro = Worksheets.Count La proprietà Count restituisce il numero di fogli di lavoro presenti in una cartella di lavoro.

Worksheets(fog).Select Un foglio di lavoro, oltre che dal nome è individuabile dalla posizione che occupa nella cartella di lavoro, quindi Worksheets(1) rappresenta il primo foglio di lavoro. Nel nostro caso Worksheets(fog) , il luogo del valore c’è la variabile fog . Se alla variabile fog viene associato il valore 1, indicherà il primo foglio, associandogli 2, il secondo foglio e così via.

ActiveSheet.Protect Ovviamente protegge il foglio di lavoro attivo, mentre Unprotect lo sprotegge. E’ possibile omettere l’istruzione Password, in tal caso la protezione avverrà senza nessuna parola segreta. Utilizzando InputBox è anche possibile permettere all’utente di personalizzare la password.


111

Lezione XXIV – Nidificare For … Next

Un ciclo For…Next può essere inserito all’interno di un altro ciclo For…Next, operazione comunemente definita nidificazione. Per mettere in pratica questa potente possibilità offertaci dal VBA, creeremo una routine che automaticamente ordinerà i fogli di lavoro di una cartella.

Il codice di ordinamento

Il listato sottostante, piuttosto astruso ad una prima occhiata, ordina alfabeticamente tutti i fogli di una cartella di lavoro.

Sub OrdinaFogli() Dim ordina As Integer, ordfog As Integer

For ordina = 1 To Sheets.Count For ordfog = 1 To Sheets.Count – 1 If Sheets(ordfog).Name > Sheets(ordfog + 1).Name Then

Sheets(ordfog).Move after : =Sheets(ordfog + 1) End If Next ordfog

Next ordina End Sub


112

For ordina = 1 To Sheets.Count

Esegue il ciclo For per il numero di fogli presenti nella Cartella di lavoro. Tale valore è determinato dall’istruzione Sheets.Count che conta il n° di fogli presenti nella cartella. Count è una proprietà che restituisce il numero degli oggetti presenti in un insieme.

For ordfog = 1 To Sheets.Count – 1 Questo secondo ciclo For esegue la medesima operazione del precedente, ma una volta in meno.

If Sheets(ordfog).Name > Sheets(ordfog + 1).Name Then

Nella prima esecuzione del ciclo For il Foglio(ordfog), cioè il primo della cartella viene confrontato con il secondo foglio della Cartella, e se alfabeticamente è successivo (>) vengono eseguite le istruzioni della riga sottostante. Nella seconda esecuzione del ciclo For, il confronto avverrà fra il secondo e il terzo foglio e così via.

Sheets(ordfog).Move after : =Sheets(ordfog + 1)

Queste istruzioni vengono eseguite se la condizione della riga precedente è VERA. I due fogli vengono invertiti di posizione, per ordinarli alfabeticamente.

End If Fine condizione

Next ordfog Fine del secondo ciclo For, con l'istruzione Next (successivo) viene ripetuto.

Next ordina Fine del primo ciclo For, con l'istruzione Next (successivo) viene ripetuto.

La sintassi di questa procedura è abbastanza semplice, quello che probabilmente risulta un po’ ostico da intuire è il perché occorrono due cicli For, cioè la loro nidificazione. Illustriamo il risultato dell'esecuzione della procedura OrdinaFogli dopo aver disabilitato il primo ciclo For.

Nell'immagine a sinistra vediamo i fogli di una cartella da ordinare di una cartella.

Nell'immagine a destra la stessa Cartella dopo l'esecuzione della procedura OrdinaFogli con il primo ciclo For disabilitato.

Un poco di ordine è stato fatto, ma non a sufficienza! Praticamente con l'esecuzione del secondo ciclo For è stato messo a posto solo il primo foglio della cartella. Soltanto riabilitando il primo ciclo For il secondo ciclo sarà eseguito per il numero di volte corrispondente al numero dei fogli, in modo tale che ad ogni esecuzione del primo ciclo For il secondo ciclo For mette in ordine un foglio della Cartella di lavoro.


113

PARTE 4 Controllare Excel

Una delle grandi prerogative del VBA, è la possibilità di creare nuove funzioni da affiancare alle funzioni incorporate di Excel, di manipolare e spostare dati, di determinare le opzioni di apertura di una cartella, di eliminare la comparsa di messaggi di avvertimento; insomma, di assumere il controllo di Excel.


114

Lezione XXV – Le Function

Tipi di routine

In Visual Basic, quindi VBA, è possibile creare due tipi di routine, ovvero la già ampiamente sperimentata Sub e la routine Function. La routine Sub è un’unità di codice racchiusa tra le istruzioni Sub e End Sub che esegue delle operazioni, senza restituire alcun valore. Una routine Function è un’unità di codice racchiusa tra le istruzioni Function e End Function che esegue un’operazione specifica restituendo un valore. Le routine Function possono essere utilizzate in sostituzione di formule complesse, direttamente nei fogli di lavoro, proprio come si utilizzano le funzioni incorporate di Excel.

Creare una nuova funzione per Excel

Grazie alle formule di Excel, riusciamo a risolvere problemi veramente complessi. Purtroppo, a volte, determinati problemi risultano essere superiori al nostro retroterra culturale. Ad esempio, personalmente non capisco assolutamente niente di finanza, quindi non sarei in grado di calcolare l’importo delle singole rate mensili, derivanti dal finanziamento chiesto ad una banca per acquistare un’auto. Fortunatamente Excel ci mette a disposizione la funzione Rata, così il problema è risolto in poche battute sulla tastiera.


115

Come si può vedere nell’immagine, la finestra di dialogo Incolla funzione raccoglie un bel numero di Categorie, le quali a loro volta, contengono una ulteriore ricca scelta di funzioni. Ma, il destino birbone, state tranquilli, farà in modo che la funzione per voi, in quel momento indispensabile, non sia contemplata nella pur ricca collezione di Excel. Ed è proprio qui che entrano in ballo le routine di tipo Function.

Analisi del problema

Attenzione! Si presuppone che, se pensiamo di creare una funzione per aiutare l’utente, o anche noi stessi, il problema da affrontare sia dotato di una discreta complessità. E’ quindi, indispensabile effettuare una accurata analisi delle problematiche da affrontare e del come risolverle. Il pretesto preso per il nostro esempio, attinge la sua ispirazione da una forma di lavoro che sempre più si sta diffondendo, soprattutto fra i più giovani: la collaborazione coordinata e continuativa. In molti si saranno scoraggiati di fronte alla complessità del calcolo della ritenuta d’acconto e delle ritenute previdenziali, così da lasciare una parte dei propri, non già lauti guadagni, nelle tasche di qualche commercialista.

Come trovare l’importo netto

Illustreremo adesso i passi occorrenti, per ricavare dall’importo lordo, la corrispondente cifra netta, utilizzando i criteri di calcolo validi sino al 31/12/2000. A titolo esemplificativo partiamo da un importo lordo di un milione.

• Rispetto la cifra di partenza, un milione, occorre calcolare il 20% a titolo di ritenuta d’acconto.

• Sempre dalla cifra lorda, un milione, calcolare il 95% per ricavare l’imponibile (la cifra) sul quale calcolare i contributi previdenziali.

• L’aliquota previdenziale nel 2000 era del 13% o del 10%, di cui la quota a carico del lavoratore corrispondeva ad un terzo .

Nel foglio elettronico, scegliendo per semplicità la l’aliquota previdenziale del 10%, la formula per ottenere l’importo netto è la seguente.

=1000000*0,8-1000000*0,95*0,1*0,333333333333333

Teoricamente si tratta di una formula piuttosto semplice, ma ne illustriamo ugualmente alcuni passaggi salienti.


116

La prima parte del calcolo consiste nel calcolare il compenso epurato dal 20% della ritenuta di acconto e che i più svolgono in questo modo:

1000000-(1000000*20%)

anche se in realtà è abbreviabile nel seguente a lgoritmo:

1000000*0,80 Un secondo passaggio degno di nota riguarda lo scrivere 1/3. Nella formula lo si vede rappresentato da un 0,333333333333333, ma in realtà è stato digitato proprio come un terzo (1/3). I più attenti avranno sgranato gli occhi,sapendo che se in un foglio di Excel si scrive 1/3, questi viene riconosciuto come 1 marzo. Per far metabolizzare ad Excel un valore frazionario si deve procedere nel seguente modo: Digitare uno zero (0), premere spazio (barra spazio) , infine digitare 1/3. Se qualcuno si è stupito per l’assenza di parentesi nella formula, non dimentichi la regola della precedenza fra operatori aritmetici.

CompensoNetto

Passiamo alla creazione della funzione. Attiviamo un modulo e digitiamo il seguente codice.

Function CompensoNetto(Lordo, Ritenuta, CTR) Dim Netto, LordoXCTR, CTRLavor As Long Netto = Lordo - Lordo * Ritenuta LordoXCTR = Lordo * 0.95 CTRLavor = LordoXCTR * CTR / 3 CompensoNetto = Netto - CTRLavor CompensoNetto = Round(CompensoNetto, 0) End Function


117

Function CompensoNetto(Lordo, Ritenuta, CTR)

CompensoNetto è il nome della funzione. I nomi inseriti fra le parentesi sono gli argomenti della funzione, utilizzabili allinterno della funzione come variabili predefinite.

Lordo = corrisponde al compenso lordo Ritenuta = corrisponde alla ritenuta d'acconto (20%) CTR = corrisponde al CTR del 10% o del 13 %.

Netto = Lordo - Lordo * Ritenuta

Netto è il nome della variabile che assume il valore ottenuto dalla sottrazione della variabile Lordo, meno il valore ottenuto dalla variabile Lordo per la variabile Ritenuta. Es. Se Lordo = 1.000.000 e Ritenuta 20% sarà eseguito il seguente calcolo: 1.000.000 - 1.000.000 * 20% = 800.000

LordoXCTR = Lordo * 0.95

LordoXCTR è la variabile che assume il valore ottenuto dalla variabile Lordo * 95%, cioè l'imponibile sul quale calcolare i CTR Es. Sarà eseguito il seguente calcolo: 1.000.000 * 95% = 950.000

CTRLavor = LordoXCTR * CTR / 3

CTRLavor è la variabile che assume il valore ottenuto dalla variabile LordoXCTR moltiplicato per il valore della variabile CTR diviso per 3, cioè la quota di CTR a carico del consulente. Es. Se CTR = 10% sarà eseguito il seguente calcolo: 950.000 * 10% / 3 = 31.666

CompensoNetto = Netto - CTRLavor

CompensoNetto è la variabile e il nome della funzione che assume il risultato finale, ottenuto sottraendo dalla variabile Netto la variabile CTRLavor . Es. Sarà eseguito il seguente calcolo: 800.000 - 31.666 = 768.334

CompensoNetto = Round(CompensoNetto, 0)

In questa riga viene arrotondato il risultato con l’ausilio della funzione Round.

End Function Fine della funzione.

A questo punto siamo pronti ad utilizzare CompensoNetto in un foglio di lavoro, come una qualsiasi funzione. Ad esempio se nella cella A1 è contenuto il valore £ 1.000.000 corrispondente al compenso lordo, in A2 troviamo 20% equivalente alla ritenuta d'acconto e in A3 10% corrispondente al CTR previdenziale, si potrà in una cella libera qualunque inserire le seguenti istruzioni: =CompensoNetto( A1; A2; A3 ) per ottenere il compenso netto del collaboratore. Ricordatevi, se volete rendere la funzione utilizzabile in tute le cartelle di lavoro, di collocarla nella cartella di lavoro speciale Personal.xls.


118

Lezione XXVI – Manipolare i dati

Molte volte, siamo chiamati ad operare elaborazioni su dati, provenienti da altre applicazioni, che si presentano, come dire, “inquinati”. Diventa fondamentale “bonificarli” togliendo caratteri superflui, spazi e altre eventuali imperfezioni.

Costruire l’esempio

In tutte le celle, relative alla colonna dei Comuni, inseriamo uno spazio vuoto e il CAP. Per realizzare tali condizioni attenersi ai seguenti passi.

• Innanzi tutto, per sicurezza, duplichiamo il foglio Comuni. o Con il puntatore del mouse portarsi sulla scheda del foglio

Comuni, tenere premuto CTRL, tenere premuto il tasto sinistro del mouse e spostarsi un po’ più a destra per generare il foglio Comuni(2), clone perfetto di Comuni.

• Nel foglio Comuni(2) attivare la cella G5, digitarvi la formula =A5&” “&B5 trascinandola sino alla corrispondenza dell’ultima riga con dati, la 319.

• Premere la combinazione di tasti CTRL+C per copiare negli Appunti la colonna, ancora selezionata, di dati testé generata.

• Posizionarsi in A5 e dal menu Modifica scegliere Incolla speciale.

• Dalla finestra di dialogo Incolla speciale scegliere Valori e confermare ciccando su OK.

• Cancellare i dati della colonna G ed eliminare la colonna B, quella del CAP, sino ad ottenere il risultato illustrato nell’immagine.

Naturalmente, anche senza ricorrere al VBA, sarebbe possibile con le funzioni di Excel, ripulire i nomi dei comuni dallo spazio vuoto e dal CAP: ma occorre ricordarsi che un utente avanzato opera per creare agli altri utenti un ambiente software più amichevole, dotato di tutti gli agi portati dall’automatismo.


119

Funzioni Len e Left

Nell’Editor di VBA, agendo sul menu Inserisci, aggiungere un nuovo modulo, quindi digitarvi il codice di seguito illustrato.

1: Sub Pulisci() 2: Dim aa As String 3: Dim bb As Integer 4: Range("a5").Select 5: Do While ActiveCell <> "" 6: bb = Len(ActiveCell) – 6 7: aa = Left(ActiveCell.Cells, bb) 8: ActiveCell.Offset(0, 6).Range("A1").Select 9: ActiveCell = aa 10: ActiveCell.Offset(1, -6).Range("A1").Select 11: Loop 12: Trasporta 13: Range("a1").Select 14: End Sub

Range("a5").Select Si posiziona sulla prima cella con dati

Do While ActiveCell <> "" Ripete le istruzioni successive per fermarsi quando incontra una cella vuota.

bb = Len(ActiveCell) – 6

La variabile bb assume il risultato della funzione Len che conta il numero di caratteri del testo inserito nella cella attiva meno 6. Sei è composto dai 5 caratteri del CAP + lo spazio. Quindi sottraendo alla lunghezza complessiva della stringa 6, veniamo a sapere di quanti caratteri è composto il nome del comune contenuto in quella cella.

aa = Left(ActiveCell.Cells, bb)

La variabile aa assume il valore della funzione Left, che restituisce un numero specificato di caratteri partendo da sinistra, contenuti nella cella attiva. Il numero di caratteri che preleva è dato dalla variabile bb.

ActiveCell.Offset(0, 6).Range("A1").Select Si sposta a destra di 6 celle.

ActiveCell = aa La cella attiva assume il valore della variabile aa.

ActiveCell.Offset(1, -6).Range("A1").Select Scende di una cella e torna a sinistra di sei celle.

Loop Ripetere.

Trasporta Finito il ciclo Do While, esegue il listato della procedura Trasporta.


120

Sub Trasporta() Range("G5").Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Range("A5").Select ActiveSheet.Paste End Sub

Range("G5").Select Si posiziona sulla cella G5, la prima della nuova colonna, appena creata da Pulisci.

Range(Selection, Selection.End(xlDown)).Select Seleziona tutte le celle contenenti dati.

Selection.Cut Taglia.

Range("A5").Select Seleziona la cella A5.

ActiveSheet.Paste Incolla il contenuto della colonna G.

Riepiloghiamo l’azione della procedura Pulisci, coadiuvata dalla procedura Trasporta. La procedura Pulisci, grazie all’ausilio del ciclo Do..While passa in rassegna tutte le celle contenenti i nomi dei comuni, “sporcati” dal CAP. Quindi avvalendosi delle funzioni Len e Left estrae il nome “pulito” del comune che sposta sei celle a destra, cioè nella colonna G. A questo punto entra in azione la procedura Trasporta che seleziona l’intero intervallo di nomi ripuliti, li Taglia via, per andare ad incollarli in sostituzione dei dati originali.

Ottimizzare

Ma vediamo che con un po’ di spirito d’osservazione possiamo ottimizzare il codice della procedura Pulisci.

1: Sub Pulisci2 () 2: Dim aa As String 3: Dim bb As Integer 4: Range("a5").Select 5: Do While ActiveCell <> "" 6: bb = Len(ActiveCell) - 6 7: aa = Left(ActiveCell.Cells, bb) 8: ActiveCell = aa 9: ActiveCell.Offset(1, 0).Range("A1").Select 10: Loop 11: Range("a1").Select 12: End Sub


121

Sino al punto 7 la procedura Pulisci e la procedura Pulisci2, sono identiche, ma nel punto 8 abbiamo la svolta! In realtà basta associare subito il r isultato della variabile aa alla cella attiva. Avendo entrambe le procedure “sotto gli occhi” può sembrare evidente l’errore commesso con la prima routine: in realtà è estremamente facile commettere simili sviste e creare procedure che, è vero, funzionano, ma con “mille” contorcimenti, creando alla lunga dei rallentamenti nell’esecuzione del codice non indifferenti.

Passare i dati ad un nuovo foglio

La procedura PulisciSposta, qui di seguito riportata, è pressoché identica a Pulisci2 sino al punto 12, salvo alla riga 4, ove per essere sicuri che la macro sia eseguita nella giusta locazione, è stato indicato il foglio sul quale eseguire le operazioni di pulizia.

1: Sub PulisciSposta() 2: Dim aa As String 3: Dim bb As Integer 4: Sheets("Comuni").Select 5: Range("a5").Select 6: Do While ActiveCell <> "" 7: bb = Len(ActiveCell) - 6 8: aa = Left(ActiveCell.Cells, bb) 9: aa = LTrim(aa) 10: ActiveCell = aa 11: ActiveCell.Offset(1, 0).Range("A1").Select 12: Loop 13: Range("a4").Select 14: Range(Selection, Selection.End(xlDown)).Select 15: Selection.Copy 16: Sheets.Add 17: On Error GoTo salta 18: ActiveSheet.Name = "Arrivo" 19: Range("a4").Select 20: ActiveSheet.Paste 21: Columns.AutoFit 22: Range("a1").Select 23: Exit Sub 24: salta: 25: MsgBox "Il foglio Arrivo " & vbCr & _ 26: "è già presente", vbCritical 27: End Sub

Commentiamo le successive istruzioni.


122

Range("a4").Select Si posiziona sulla prima cella della colonna dati.

Range(Selection, Selection.End(xlDown)).Select

Seleziona l’intera colonna di dati: corrisponde alla combinazione di tasti CTRL+SHIFT+� .

Selection.Copy Copia la selezione negli Appunti.

Sheets.Add Aggiunge un foglio di lavoro.

On Error GoTo salta In caso di errore si sposta all’etichetta salta.

ActiveSheet.Name = "Arrivo" Rinomina il foglio appena inserito.

Range("a4").Select Seleziona A4 del foglio Arrivo.

ActiveSheet.Paste Incolla dagli Appunti la selezione precedentemente copiata.

Columns.AutoFit Autodimensiona la larghezza della colonna alla cella contenente il nome di comune più lungo.

Range("a1").Select Seleziona la cella A1.

Exit Sub Esce dalla procedura per evitare di eseguire le istruzioni relative all’errore.

salta: Etichetta a cui saltano le istruzioni in caso di errore,

MsgBox "Il foglio Arrivo " & vbCr & _

Messaggio di avvertimento, basato sul più probabile errore che potrebbe capitare, cioè il foglio Arrivo già presente nel momento che la procedura nella riga 18 cerca di rinominare il foglio appena aggiunto in Arrivo.

"è già presente", vbCritical

E’ interessante notare come nella riga precedente è stata usata la costante vbCr per mandare a capo in un punto determinato il testo del messaggio.


123

Pulire solo se…

Ipoteticamente potremmo voler applicare la bonifica dei nomi soltanto se questi rientrano in determinate condizioni: ad esempio, solo quando hanno una popolazione inferiore ai 1000 abitanti o superiore ai 10000.

1: Sub Pulisci3() 2: Dim aa As String 3: Dim bb As Integer 4: Range("a5").Select 5: Do While ActiveCell <> "" 6:: ActiveCell.Offset(0, 4).Range("A1").Select 7: If ActiveCell < 1000 Or ActiveCell > 10000 Then 8: ActiveCell.Offset(0, -4).Range("A1").Select 9: bb = Len(ActiveCell) - 6 e sottrae 10: aa = Left(ActiveCell.Cells, bb) 11: ActiveCell = aa 12: ActiveCell.Offset(1, 0).Range("A1").Select 13: Else 14: ActiveCell.Offset(1, -4).Range("A1").Select 15: End If 16: Loop 17: Range("a1").Select 18: End Sub

Sino al punto 5 la procedura Pulisci3 è identica alla precedente.

ActiveCell.Offset(0, 4).Range("A1").Select Si sposta verso la quarta cella a destra, cioè la popolazione.

If ActiveCell < 1000 Or ActiveCell > 10000 Then

If si avvale dell’operatore OR per definire le due condizioni in base alle quali operare la ripulitura.

ActiveCell.Offset(0, -4).Range("A1").Select Se soddisfatte ritorna alla cella del comune.

Dal punto 9 al punto 12 Esegue la ripulitura e scende di una cella.

Else Altrimenti, se non soddisfatte.

ActiveCell.Offset(1, -4).Range("A1").Select Scende di una cella e torna ai comuni, senza effettuare nessuna ripulitura.

End If Fine della struttura di controllo If


124

Lezione XXVII – Auto_Apri_Chiudi

Anche se in questo corso non è stato previsto affrontare temi relativi all’uso della grafica in Excel, è innegabile che la creazione di un buon programma sia legato alla cura posta nella sua interfaccia grafica. In queste lezioni dedicheremo spazio a quelle istruzioni VBA che permettono di valorizzare l’aspetto dei nostri programmi, di personalizzarli, di renderli più usabili, sicuri e automatizzati.

Auto_Apri

Avrete notato, che se si salva una cartella di lavoro con attivo il Foglio2, posizionato nella cella Z100, alla successiva riapertura questa si presenterà con il Foglio2 in Z100. Peccato! Magari avevate previsto una bella pulsantiera nel Foglio1 e per rendere “guidata” la navigazione all’interno della cartella , avevate anche tolto le Schede indicatrici dei fogli. Fortunatamente il VBA mette a disposizione una procedura speciale che permette di indicare con precisione le azioni che Excel deve compiere in fase di apertura della cartella di lavoro. Il “trucco” consiste nel dare alla procedura il nome di Workbook_Open.

Sub Workbook_open() MsgBox “Ciao” End Sub

E di inserirla nel modulo speciale ThisWorlbook.


125

Auto_Chiudi

E’ molto probabile che anche in fase di chiusura della cartella di lavoro, si vogliano eseguire automaticamente alcune operazioni, fosse solo il ripristinare i settagli modificati in fase di apertura. Tale procedura dovrà assumere il nome di Workbook_BeforeClose(Cancel As Boolean). Anche in questo caso la procedura dovrà dimorare all’interno del modulo ThisWorkbook.

Sub Workbook_BeforeClose(Cancel As Boolean) MsgBox “Arrivederci” End Sub

Un po di storia

Ogni tanto si vedono delle procedure di apertura e di chiusura chiamate rispettivamente Auto_Open e Auto_Close. Si tratta di macro risalenti alle versioni 5 e 7 di Excel, ormai tollerate dal VBA per esigenze di compatibilità, ma non certamente amate: quindi non usatele. A proposito nelle versioni di Excel 5 e 7, era prevista la versione del VBA in italiano e le macro di apertura e di chiusura automatica, erano denominate Auto_Apri e Auto_Chiudi.

Istruzioni varie

Vengono adesso presentate una serie di istruzioni, che possono essere utilizzate sia all’interno delle due procedure di apertura, di chiusura, o di altre macro.

• Personalizzare la barra del titolo della cartella di lavoro. Application.Caption = “Corso di VBA on line”

• In chiusura, salva la cartella attiva senza chiedere conferma. ActiveWorkbook.Save

• Chiude la cartella di lavoro senza salvare le modifiche ActiveWorkbook.Saved = True

Impostando la proprietà Saved su True è un po’ come ingannare Excel, dicendogli che la cartella è già stata salvata, anche se non è vero.


126

• Non visualizza la finestra di dialogo di conferma: per spiegarci meglio, quella che compare quando si vuole eliminare un foglio di lavoro.

Application.DisplayAlerts = False Per ripristinarla sostituire False con True. E’ importante ricordarsi di ripristinare la visualizzazione del messaggio di conferma; può essere molto pericolosa la mancanza di un elemento con il quale gli utenti sono abituati ad interagire.

• Disattiva l’aggiornamento dello schermo durante l’esecuzione di una macro.

Application.ScreenUpdating = False E’ importante inserire sempre l’istruzione di ripristino dell’aggiornamento impostando la proprietà ScreenUpdating siu True.

• Aprire un file di Excel. Workbooks.Open FileName:="C:\Documenti\Comuni.xls"

Esempi di Auto_Apri e Auto_Chiudi

Il listato illustra un esempio di utilizzo della procedura di apertura automatica, discretamente articolato.

Sub Workbook_open() With Application .ScreenUpdating = False ‘Disattiva l’aggiornamento dello schermo .DisplayFormulaBar = False ‘Toglie la barra della formula .DisplayStatusBar = False ‘Toglie la barra di stato End With With ActiveWindow .DisplayHorizontalScrollBar = False ‘Toglie la barra di scorrimento orizzon. .DisplayVerticalScrollBar = False ‘Toglie la barra di scorrimento vertic. .DisplayWorkbookTabs = False ‘Toglie le schede indicanti I fogli End With ActiveWindow.WindowState = xlMaximized ‘Excel a tutto schermo Application.Caption = " Corso di VBA on line" Sheets("Comuni").Select ‘Si apre sul foglio Comuni Range("a1").Select Application.ScreenUpdating = True ‘Ripristina l’aggiornamento dello schermo End Sub


127

E’ importante in chiusura, ripristinare gli elementi di Excel che sono stati modificati o eliminati in fase di apertura.

Sub Workbook_BeforeClose(Cancel As Boolean) With Application .ScreenUpdating = False .DisplayFormulaBar = True .DisplayStatusBar = True End With With ActiveWindow .DisplayHorizontalScrollBar = True .DisplayVerticalScrollBar = True .DisplayWorkbookTabs = True End With Application.Caption = Empty 'Svuota la barra del titolo ActiveWorkbook.Saved = True Application.ScreenUpdating = True End Sub

Proteggere

Se volete essere sicuri che nessuno modifichi il vostro lavoro, o peggio faccia dello sciacallaggio, proteggete il codice delle macro.

• Portarsi nell’Editor di VB.

• Dal menu Strumenti scegliere la voce Proprietà di VBAProject… .

• Attivare la scheda Protezione.

• Mettere il flag su Proteggi progetto dalla visualizzazione.

• Inserire la password e confermare ciccando su OK.

Nota: Non fate troppo affidamento su questo tipo di protezione, nella “rete” sono reperibili dei programmi che decriptano facilmente le password dei progetti VBA.


128

Lezione XXVIII – I macro virus

I virus informatici, "grazie al cielo", non sono mai stati una razza che ha corso il rischio di estinguersi, ma in questi ultimi tempi, con il proliferare incontrollato dei macro virus, incominciamo a sentire il bisogno di effettuare un abbattimento "selettivo", per evitare danni eccessivi all'ambiente dei nostri pc.

Come difendersi

La prima regola in assoluto è quella di essere dotati di un antivirus aggiornato. Si noti che un aggiornamento efficace deve avere una cadenza minima mensile, meglio se settimanale. Scandire sempre i nuovi documenti di Word, Excel, Power Point ed Access, sia che provengano da supporti magnetici tipo floppy o CD-ROM, che da posta elettronica. Può essere brutto a dirsi, ma non fidatevi di nessuno. Word ed Excel sono dotati di una procedura che riconosce e segnala in fase di apertura l'eventuale presenza di una macro nel documento. Alcuni utenti infastiditi da tale segnalazione, che avviene anche per macro non infette, la disabilitano. Non fatelo assolutamente! Per ripristinare la procedura di protezione da macro virus, dal menu Strumenti di Excel, scegliere Macro\Protezione e impostarla su Media.

Nota: La protezione da macro virus è stata introdotta con Office 97 e a seconda della versione e dell’applicativo, può presentare delle piccole differenze nelle impostazioni di disabilitazione e riabilitazione.

Scrivere una macro, abbiamo visto nel nostro corso, non è molto difficile, ma sono ugualmente pochi quelli che sono capaci di farlo e anche quelli in grado di scriverne non necessariamente le creano: quindi se all'apertura di una cartella di Excel, o peggio di un documento di Word, vi viene segnalata la presenza di una macro e non vi è stato comunicato che effettivamente doveva esserci, non attivatela. Se avete attivato una macro, dopo l'avvertimento della sua presenza e al successivo riavvio di quel documento, l'avviso non vi viene più proposto, siete sicuramente "infetti".


129

Difesa artigianale

Dalle osservazioni del paragrafo precedente si ricava una idea semplice, ma abbastanza efficace per trovare i macro virus.

• Reperire un file di Excel contenente una macro, possibilmente non infetta, e copiarlo su un dischetto protetto da scrittura.

• Inserire il floppy nel lettore del PC sospetto e lanciare il file di Excel.

Se la procedura di rilevamento macro entra in funzione, probabilmente la macchina è sana, altrimenti il dado è tratto, siete infestati. Per toccare con "mano" l'entità virale r ichiamate dal menu Strumenti\Macro l'Editor di Visual Basic e qui se siete abbastanza smaliziati, potete anche provare a estirpare a "mani nude" il maligno.


130

Appendici

In questa sezione finale del corso, presentiamo alcune realizzazioni probabilmente ritenute inconsuete per Excel. Probabilmente dopo averle visionate vi chiederete che cosa non è possibile fare con il nostro foglio elettronico.


131

Un’animazione con Excel

Una delle idee più deleterie che può circolare su Excel, è quella di crederlo un programma per rigidi contabili. Nulla di più falso! In piena era internettiana imperversano programmi tipo Flash, che sembrano gli unici a poter offrire grafica ed animazioni. In realtà anche il nostro foglio elettronico è in grado, anche se un po’ timidamente, di dire la sua in fatto di animazioni. Costruiremo una piccola animazione per misurare le prestazioni del nostro PC. Procuriamoci una piccola immagine a piacere, ad esempio un ciclista. L’idea è di far scendere il ciclistino di una ventina di celle e di farlo risalire al punto di partenza, dando l’impressione della pedalata. Per ottenere un simile effetto occorre una seconda immagine speculare del ciclista, facilmente ottenibile con un programma di grafica, tipo Paint Shop Pro. La nostra procedura dovrà fare in modo che mentre le due immagini scendono lungo il foglio, siano alternativamente visibili per dare appunto l’impressione della pedalata. Inseriamo le due immagini all’inizio di un foglio, facendo attenzione a sovrapporle con precisione, quindi, se le immagini sono su sfondo bianco, come quelle del nostro esempio, colorare il foglio di lavoro di bianco. Passare nell’Editor Di VB e digitare il codice della procedura CorsaCiclista.


132

Il codice

1: Sub CorsaCiclista() 2: Dim VarDisegno As Picture 3: Dim ContCorsa, Contatore, risul As Integer 4: Dim VelocitPc As Variant, Mess, tit As String 5: Sheets("Corsa").DrawingObjects("Picture 1").BringToFront 6: Mess = "Confermando verrà eseguita un'animazione la" & Chr(13) & _ 7: "cui esecuzione potrebbe risultare eccessivamente" & Chr(13) & _ 8: "lunga con PC non molto potenti." 9: tit = "Corso di VBA on line" 10: risul = MsgBox(Mess, vbYesNo, tit) 11: If risul = vbNo Then 12: Range("a1").Select 13: Exit Sub 14: End If 15: VelocitPc = Timer 16: For ContCorsa = 1 To 144 17: With Sheets("Corsa").DrawingObjects("Picture 1") 18: .Visible = True 19: .Top = .Top + 2 20: .Visible = False 21: End With 22: With Sheets("Corsa").DrawingObjects("Picture 2") 23: .Top = .Top + 2 24: .Visible = True 25: End With 26: Next 27: For ContCorsa = 1 To 73 28: With Sheets("Corsa").DrawingObjects("Picture 1") 29: .Visible = True 30: .Top = .Top - 2.5 31: .Visible = False 32¨ End With 33: With Sheets("Corsa").DrawingObjects("Picture 2") 34: .Visible = True 35: .Top = .Top - 2.5 36: End With 37: Next 38: With Sheets("Corsa").DrawingObjects("Picture 1") 39: .Visible = True 40: End With 41: MsgBox (Timer - VelocitPc) & " Secondi" 42: End Sub


133

Sheets("Corsa").DrawingObjects("Picture 1").BringToFront

Nel foglio Corsa, la prima immagine del ciclista, Picture 1, deve essere in primo piano.

Dal punto 6 al punto 14 Con una finestra messaggio chiediamo conferma per continuare o meno la procedura.

VelocitPc = Timer Associa alla variabile la misurazione del tempo data dalla funzione Timer.

For ContCorsa = 1 To 144 Ripete le operazioni successive per 144 volte. With Sheets("Corsa").DrawingObjects ("Picture 1")

Con l’oggetto Picture 1

.Visible = True Imposta la proprietà di visibile su vero.

.Top = .Top + 2 Sposta in basso l’oggetto di due punti.

.Visible = False Rende invisibile l’immagine del primo ciclista e così rimane visibile la seconda immagine.

End With Fine enunciato Con. With Sheets("Corsa").DrawingObjects ("Picture 2")

Passa alla seconda immagine.

.Top = .Top + 2 E la sposta in basso di due punti.

.Visible = True Confermandone la visibilità.

End With Fine enunciato Con.

Next

Ripete da capo il ciclo. Quindi, mentre è visibile , fa scendere di due punti il primo ciclista e lo nasconde. A questo punto passa al secondo ciclista, che non più coperto dal primo ciclista, scende a sua volta di due punti. Ricompare il primo ciclista e… per 144 volte.

Dal punto 27 al punto 37

Esegue le operazioni di risalita, che avendo un ciclo ripetuto meno volte e spostamenti più lunghi è più rapido della discesa.

With Sheets("Corsa").DrawingObjects ("Picture 1")

Terminata l’animazione rende visibile la prima immagine.

MsgBox (Timer - VelocitPc) & " Secondi" Visualizza il tempo impiegato a percorrere il “giro” del foglio.

Nota: Ad essere scrupolosi, DrawingObjects è un oggetto nascosto, perché ormai considerato obsoleto.

Certo, i ragionieri continueranno ad usare Excel per la grigia contabilità aziendale, ma magari, con l’angolo della bocca piegato verso l’alto, ad abbozzare un tiepido sorriso.


134

Controllare la risoluzione dello schermo

Quando si realizza un applicazione con Excel, si predispone con cura anche il suo layout grafico: così può succedere, che dopo averlo impostato su una risoluzione di 800x600, scopriamo che la metà dei potenziali utilizzatori del nostro lavoro imposta il suo video a 1024x768 pixel. Quindi, non sarebbe male verificare in fase di avvio della procedura, quale risoluzione è utilizzata dall’utente e regolare conseguentemente la modalità di visualizzazione. Il problema è che, il VBA, come anche il Visual Basic, non sono in grado di effettuare operazioni di così basso livello; per tale ragione si dovrà effettuare delle chiamate direttamente alle API di Windows.

Nota: Occorre tenere conto che un uso maldestro delle API può di bloccare il sistema.

Per utilizzare l’API di Windows all’interno del VBA occorre usare l’istruzione Declare che permette di accedere alle DLL non appartenenti all’insieme di quelle messe a disposizione di Excel. L’istruzione Declare và usata nell’area di dichiarazione del modulo, cioè all’inizio.

Impostazioni

La nostra procedura sarà visualizzata a tutto schermo, impostazione di default, in presenza di una risoluzione video di 800 x 600 pixel. Se la risoluzione fosse di 1024 x 768 la finestra andrà disegnata con proporzioni che rispecchino la risoluzione di 800 x 600, per non perdere il layout grafico. Naturalmente una risoluzione di 640 x 480 pixel dovrà essere ritenuta fuori standard.


135

Listato

1: Public Declare Function GetSystemMetrics Lib "user32" _ 2: (ByVal asIndex As Long) As Long 3: Public Const risx = 0 4: Public Const risy = 1 ‘----------------------------------------------------------- ‘----------------------------------------------------------- 5: Function Risoluzione() 6: Dim aaa As Variant, bbb As Variant 7: Dim parole1 As String 8: Dim opzioni1 As Byte, scelta1 As Byte 9: aaa = GetSystemMetrics(risx) 10: bbb = GetSystemMetrics(risy) ‘----------------------------------------------------------- 11: If aaa = 800 Then 12: Application.WindowState = xlMaximized 13: ElseIf aaa = 1024 Then 14: Application.WindowState = xlNormal 15: Application.Left = 78 16: Application.Top = 63 17: Application.Width = 605 18: Application.Height = 437 19: ElseIf aaa = 640 Then 20: parole1 = "La risoluzione del monitor è impostata a " & aaa & " x " & bbb & _ 21: " - si consiglia di cliccare su SI per uscire dalla procedura" & _ 22: "e così impostarla a 800 x 600 per consentire una corretta visualizzazione " 23: opzioni1 = vbQuestion + vbYesNo 24: scelta1 = MsgBox(parole1, opzioni1) 25: If scelta1 = vbYes Then Application.Quit 26: Else 27: parole1 = "La risoluzione del monitor è impostata ad una risoluzione non" & _ 28: " prevista - si consiglia di cliccare su SI per uscire dalla procedura" & _ 29: "e così impostarla a 800 x 600 per consentire una corretta visualizzazione " 30: opzioni1 = vbQuestion + vbYesNo 31: scelta1 = MsgBox(parole1, opzioni1) 32: If scelta1 = vbYes Then Application.Quit 33: End If 34: End Function


136

Il commento del codice

Public Declare Function GetSystemMetrics Lib "user32" _ (ByVal asIndex As Long) As Long

La già citata istruzione Declare, che fa riferimento alla libreria (file) user32.dll , presente nella cartella Windows\System.

Public Const risx = 0 Public Const risy = 1

Dichiarazione delle costanti necessarie alle API per riconoscere la risoluzione. I valori sono desumibili nella documentazione Microsoft relativa all’interfaccia API di Windows.

aaa = GetSystemMetrics(risx) Si associa alla variabile il valore della risoluzione orizzontale.

bbb = GetSystemMetrics(risy) Si associa alla variabile il valore della risoluzione verticale.

If aaa = 800 Then Se la risoluzione orizzontale è uguale a 800 pixel…

Application.WindowState = xlMaximized … manda la finestra di Excel a tutto schermo

ElseIf aaa = 1024 Then Altrimenti se è 1024 pixel…

Application.Left = 78 … posiziona la distanza dal margine sinistro dello schermo al margine sinistro della finestra principale di Microsoft Excel, espressa in punti.

Application.Top = 63 … posiziona la distanza tra il margine superiore dello schermo e il margine superiore della finestra principale di Microsoft Excel.

Application.Width = 605 … determina la distanza tra il margine sinistro e il margine destro della finestra dell'applicazione. In altre parole la larghezza.

Application.Height = 437 … determina l’altezza della finestra principale dell'applicazione.

ElseIf aaa = 640 Then Altrimenti se è 640 pixel…

Dalla riga 20 alla riga 24 Imposta un MsgBox.

If scelta1 = vbYes Then Application.Quit Se si clicca su OK di MsgBox viene chiuso Excel.

Else Altrimenti

Dalla riga 27 alla riga 31 Imposta un MsgBox

If scelta1 = vbYes Then Application.Quit Se si clicca su OK di MsgBox viene chiuso Excel.

Dalla riga 33 alla riga 34 Fine If, fine funzione.


137

Nota: Una funzione come Risoluzione è chiaramente da eseguire automaticamente all’avvio di Excel e quindi si può essere portati ad individuare la sua collocazione all’interno del modulo speciale ThisWorkbook. In realtà se il codice di Risoluzione, o meglio la dichiarazione di Declare, fosse collocata all’interno di suddetto modulo, la nostra procedura non funzionerebbe. Quindi per fare in modo che la funzione Risoluzione venga eseguita in fase di avvio di Excel, bisogna inserire il solo riferimento ad essa all’interno di Workbook_Open, come nell’esempio sottostante.

Sub Workbook_open() ActiveWindow.WindowState = xlMaximized Application.Caption = " Corso di VBA on line" Risoluzione End Sub

Il “bello” della procedura Risoluzione, o meglio del VBA, è che il suo codice, con poche variazioni, può essere utilizzato anche per le altre applicazioni della suite Microsoft Office. Ad esempio, io ho appreso gli strumenti per il controllo delle impostazioni del video, studiando Access.


138

File allegato

Oltre al file di Excel Comuni.xls, da utilizzare per le esercitazioni previste nel corso, questo CD contiene un secondo file, denominato ComuniCD.xls, contenente il codice di alcuni esempi. Nel dettaglio, sono presenti le seguenti procedure:

• CompensoNetto;

• CorsaCiclista;

• Proteggi;

• Risoluzione;

• Sproteggi. Inoltre sono presenti due semplici macro di navigazione fra i fogli.


139

Conclusioni

Questo corso di programmazione, avrete letto nella presentazione,è stato allestito, non per i programmatori professionisti, ma per utenti avanzati (power user), che volevano estendere le potenzialità, già ampie, offerte da Excel. Con questo non si è inteso dare adito ad una interpretazione riduttiva, del tipo: i corsi per programmatori sono di serie A, gli altri di serie B. In realtà sono due aspetti dell’informatica costellati da molte diversità. Il programmatore, molte volte, appartiene ad un team, sviluppa una porzione specifica dell’intero progetto, in taluni casi non conosce neanche molto bene quali sono le finalità del software su cui sta lavorando e sicuramente non ha esperienza diretta in quello specifico ambito lavorativo. A lui si chiede di costruire una struttura solida, sicura e magari duratura nel tempo. L’utente avanzato è generalmente inserito nel contesto lavorativo ove le sue applicazioni dovranno funzionare e molte volte dovrà realizzarle nei ritagli di tempo. Dovrà allestire piccole e agili procedure in pochi giorni per far fronte a problemi imprevisti, magari in attesa del rilascio della procedura ufficiale, che dovrà uscire da lì a poco. Questo non vuol dire che le due figure siano inavvicinabili, anzi a volte gli utenti avanzati crescono così tanto a livello di conoscenze tecniche da diventare dei veri e propri programmatori. Purtroppo più difficile è il processo inverso. E’ raro che un programmatore puro riesca a stabilire un punto di contatto, o meglio a comunicare con i fruitori del suo software. Se avete fatto vostri i concetti espressi da questo corso, sarete sicuramente in grado di realizzare applicazioni professionali di tutto rispetto. Però, non dimenticatevi che in realtà abbiamo solo scalfito l’immenso universo del VBA. Non è stato neppure preso in considerazione un blocco di costruzioni fondamentali come gli Oggetti, per non parlare dell’assoluta assenza di riferimenti all’integrazione con altre applicazioni Office. Poi, per chiunque lavori in una azienda di medie o grandi dimensioni, non è da trascurare la tematica legata alle intranet e alla interazione fra pagine WEB e applicativi Office.


140

Indice rapido

Voce Pagina Costanti 69 Debug 92 Declare 134 Do Until…Loop 64 Do While…Loop 63 - 119 - 123 Editor di VB 47 Errori 88 For…Next 107 - 111 - 132 Forms 95 - 99 If…Then 60 - 132 If…Then ElseIf…Else…End If 104 - 135 InputBox 74 Left 119 Len 119 Macro virus 128 Moduli 34 – 40 MsgBox 70 Notazione scientifica 68 On Error GoTo 94 Personal.xls 27 Registrare una macro 15 Select Case 106 ThisWorkbook 48 - 125 Variabili 66 Workbook_BeforeClose(Cancel As Boolean) 125 - 127 Workbook_Open 124 – 126

Date post:	11-Aug-2015
Category:	Documents
Upload:	sergio-garofalo
View:	3,462 times
Download:	693 times