VBA per Excel dalla teoria alla pratica: un esempio di una procedura per una piccola gestione di preventivazione e fatturazione di prodotti e servizi che fornisce anche vari spunti di studio di questo linguaggio

La Microsoft ha fornito l’Excel dalla versione 5 in poi di un linguaggio di programmazione che affianca il vecchio linguaggio macro: il Visual Basic for Application o più semplice mente VBA. Essendo assai simile al linguaggio Basic può venire utilizzato per creare veri e propri programmi. Partendo da questa idea, vedremo nella pratica come utilizzarlo per realizzare una piccola procedura per la gestione della preventivazione e relativa fatturazione. Questo programma è stato concepito per due scopi: il primo è di fornire al lettore un qualcosa di preconfezionato utilizzabile così com’è oppure impiegabile come base per sviluppare un applicativo più complesso; molto probabilmente saranno scarse le situazioni in cui non siano necessarie delle personalizzazioni, per cui i dati e le funzioni implementate sono ristrette a quelle fondamentali, lasciando al programmatore la massima libertà di intervenire secondo le proprie necessità.
Il secondo scopo consiste nel poter fornire una macro studiabile per analizzare come si possano realizzare determinate soluzioni in VBA.
Ora passiamo ad una sintetica analisi del problema che questa macro affronta.

Preventivi e fatture

Quasi tutte le aziende si trovano di fronte al problema di dover realizzare preventivi per i propri clienti e recuperarli in tempi successivi per gestirli come ordine e conseguente fattura. Introduciamo una analisi alla struttura tipica di un’applicazione di questo genere.
Sono previsti i seguenti archivi:

• anagrafica articoli o prodotti;
• anagrafica clienti;
• file dei preventivi e fatture.

Il primo file contiene vari campi: il codice articolo, la sua descrizione, il prezzo di vendita, l’aliquota IVA. In relazione all’ambito o settore di applicazione, si trovano altri campi quali: l’unità di misura, la taglia o colore, il costo di acquisto, il fornitore, eccetera.
Nell’anagrafica clienti i campi sono quasi sempre gli stessi: il codice cliente, la ragione sociale o denominazione dell'azienda, indirizzo completo, il codice fiscale e partita IVA, il numero di telefono e di telefax, modalità di pagamento abituale, banca di appoggio; se ne aggiungono di solito altri quali il fido, l’ammontare del fatturato, gli indirizzi di consegna del materiale se diverso da quello fiscale o legale, l’agente o rappresentante incaricato a curarlo, codici indicanti varie caratteristiche del cliente quali l’importanza, la solvibilità, eccetera.
L’archivio dei preventivi è costituito da due file separati.
Il primo contiene campi quali il codice cliente, la data di emissione e la sua scadenza, la forma di pagamento prevista.
Il secondo contiene i vari articoli offerti riportando il codice, il prezzo di vendita, la quantità. La relazione fra i due file viene realizzata utilizzando il codice assegnato al preventivo come chiave: nel primo file essa sarà univoca, mentre sul secondo file saranno presenti tanti record quanti sono i prodotti facenti parte del preventivo. In relazione all’ambiente ed al linguaggio di programmazione, potrebbe rendersi necessario utilizzare un numero sequenziale per ogni record di ogni preventivo al fine di creare chiavi univoche anche sul secondo archivio, ma questa situazione non ricorre sfruttando il VBA di Excel.
Durante le trattative commerciali, spesso il preventivo subisce modifiche e correzioni di prezzo e quantità, richiedendo la riemissione del preventivo secondo le nuove richieste del cliente.
L’ultima versione del preventivo, se accettata dal cliente e non sono sorte problematiche quali l’indisponibilità del prodotto od altre, contiene di fatto tutti i dati per la fatturazione. Di conseguenza, inserendo sul primo file dei preventivi alcuni campi aggiuntivi per la gestione della fatturazione quali il numero di fattura e la sua data di emissione, viene a cadere la necessità di un altro file separato relativo alle fatture.
Il programma si basa su questi concetti.

la macro: l’esecuzione

Quando viene aperto il worksheet della macro appare il primo foglio di nome Avvio. Esso serve a fornire una semplice interfaccia all’utente contenente tre tasti che attivano altrettanti servizi. Essi sono:

• gestione preventivi e fatture;
• gestione archivio Clienti;
• gestione archivio Articoli.

Iniziamo dagli ultimi due in quanto necessitano di spiegazioni minori rispetto al primo. Essi attivano delle procedure che consentono rispettivamente la gestione dei data base dei Clienti e degli Articoli. Le funzioni fornite all’utente sono quelle che ShowDataForm supporta native. Per chi non avesse ancora familiarità con questo metodo, ne riportiamo la sintassi:

WorkSheets( identificativo )
ShowDataForm

nonché una sintetica spiegazione. Esso opera su un Worksheet e ne gestisce il database in cui il cursore è posizionato.
In altre parole ShowDataForm riconosce automaticamente il Database partendo dal presupposto che, al mo mento della sua invocazione, il cursore deve essere posizionato all’interno del la zona dell’elenco dei dati; la necessità di posizionare i! cursore all’interno dell’area permette di avere più data base sullo stesso foglio.
Più in particolare, ShowDataForm scorre verticalmente l’elenco dei dati sino a raggiungere la prima linea che considera come intestazione dei campi e quindi assegna ad essi i nomi prendendoli dalla suddetta riga. Lo scorrimento orizzontale di tutte le righe permette di conoscere quale sia il numero massimo di campi per record, dato indispensabile per la corretta costruzione della Dialog Box che apparirà a video; essa contiene i tasti necessari per muoversi fra i record, modificarli, cancellarli ed aggiungerne dei nuovi in fondo a quelli preesistenti.
Decisamente interessante è il tasto Criteria che permette di creare rapidamente un filtro sui valori dei campi: premendo questo tasto ed inserendo dei valori nelle Edit Box relative ai vari campi, i tasti Find Prev. e Find Next presentano i record che corrispondono ai criteri di selezione specificati.
Un altro dettaglio interessante da notare è i che l’estensione delle caselle che con tengono i valori dei campi nella Dialog Box é calcolata sul la base della larghezza del la cella più grande fra quelle comprese nel DataBase; conseguentemente se si dimensiona una Colonna a 200 mediante il menu Formati | Column | Width, la Dialog Box avrà un’ estensione orizzontale che supera abbondantemente i confini del video.
Il primo tasto del foglio Avvio, Gestione preventivi e fatture, è quello che avvia la macro più importante di nome GestioneOrdini. Il nome di questa macro è dato dal fatto che i preventivi possono essere anche visti e gestiti come ordini. Essa è basata su un loop che propone tre Dialog Box in sequenza, ognuna delle quali assolve un de terminato compito:

• richiesta del codice del preventivo; immissione dei dati gcnerali quali
• il cliente e la data dell’ordine;
• gestione dei singoli articoli ordinabili.

La selezione del preventivo

In figura 2 è rappresentata la prima dialog box della macro; in essa viene richiesto il codice del preventivo che sì desidera inserire oppure di selezionare quello che si desidera modificare. Il codice del preventivo è di tipo stringa, quindi si possono inserire anche i codici parlanti, cioè quei codici che sono autodescrittivi. Sono presenti vari tasti:

• Inserimento/Correzione: permette di inserire un nuovo preventivo op pure di modificarne uno già memorizzato;
• Stampa preventivo/Fatt: mette in stampa il preventivo se’ezionato. Nel caso il preventivo sia stato fat turato, questo tasto permette di rieseguire la stampa della fattura; Fatturazione Preventivo: il pre ventivo selezionato viene fattura to; l’utente deve immettere il nu mero di fattura da attribuire aldo cumento e la macro provvede ad inserire nell ‘archivio dei preven tivi gli estremi; la stampa del do cumento avviene mediante il ta sto precedente,
• Cancel; chiude la Dialog Box e ter mina la Inacro riproponendo il fo glio Avvio.

Un aspetto estremamente interessante di questa Dialog Box risiede nella sua Lisi Box: il contenuto di quest’ultima viene stabilito dinamicamente ricomprendendo tutti i preventivi e fatture presenti nell’archivio. Questo è reso possibile sfruttando la proprietà ListFillRange e la funzione checkFineDbQ: descriviamone i particolari.
La funzione checkFineDB() opera su un foglio Excel che riceve come para metro e ricerca l’ultima linea contenente dei valori e ne restituisce il numero di riga.
Per velocizzare tale ricerca, è stato implementato un semplice ma valido algoritmo: anziché verificare le righe sequenzialmente, ovvero una dopo l’altra, la ricerca avviene eseguendo dei salti di y righe fino a quando la riga in esame non appare vuota. Viene quindi ricalcolato il numero di riga da verificare procedendo a ritroso. Ponendo per esempio y uguale a 100, lafun zione verifica le righe 100, 200, 300, fino a quando all’n-esimo loop la riga non appare vuota.
A questo punto la riga ricercata è compresa fra 100*(n-1) e 100*n ovvero fra la riga analizzata nel ciclo precedente e la riga precedente all’attuale. Per approssimazioni successive si trova la riga cercata. Il valore iniziale di y viene passato alla funzione come secondo parametro. Certamente i puristi del VBA for Excel avranno pensato che il problema si poteva semplicemente e rapidamente risolvere selezionando il foglio ed applicando lo statement

Selection.End(xlDown).Select

infatti ciò è possibile e risulta anche più veloce di checkFineDB(), ma sotto il profilo esplorativo mi è sembrata più interessante questa funzione.
ListFillRange è la proprietà che permette di definire quale range di celle la List Box deve far apparire. Generalmente essa viene definita mediante il menu Format Object che appare premendo il tasto destro del mouse e compilando il box Input Range, ma, così facendo, il range è statico. Quindi questo sistema può essere efficacemente utilizzato in tutti quei casi in cui il numero di informazioni non cambiano come nel caso della selezione di un mese o del giorno della settimana.
Un esempio di applicazione della proprietà ListFillRange può essere il seguente:

DialogSheets("DlgOrdine") _
  .DropDowns("DD_CodiceProdotto"). _
  ListFillRange = "Articoli|$A$2:$A$" _
  & LTrim(str$(DEArticoli_ultimaRiga))

Il carattere underscore presente alla fine di ogni linea e preceduto da uno spazio é il mezzo per comunicare al l’interprete VBA che lo statement prosegue sulla linea successiva. L’istruzione sopra riportata fa sì che il DropDown di nome DD_CodiceProdotto definito nel foglio DlgOrdine contenga i valori presenti nel foglio Articoli limitatamente al range di celle comprese fra $A$2 e la riga il cui valore viene definito mediante la variabile DBArticoliUltimaRiga.
Incidentalmente si può segnalare che quest’ultima variabile viene avvalorata dalla macro mediante la funzione checkFineDB() prima descritta, tramite la seguente chiamata:

DBArticoli UltimaRiga = _   checkFineDB(Sheets("Articoli"), 100)

Vediamo adesso cosa accade premendo i vari tasti, incominciando da Inserimento/Correzione.

Inserimento o modifica dl un preventivo

La pressione del tasto Inserimento/Correzione dopo aver selezionato un preventivo preesistente o aver digitato un nuovo codice, causa l’attivazione di una seconda Dialog Box che è contenuta nel foglio OrdHdrDlg e che è mostrata in figura 3.
Essa consente di digitare la prima parte dei dati del preventivo.
In essa sono presenti i seguenti elementi:

• una zona in alto a sinistra in cui appare il codice del preventivo;
• un Drop Down che consente di selezionare il codice del Cliente;
• una zona a destra del precedente Drop Down che visualizza la ragione sociale del cliente e sua città;
• una Edit Box per la digitazione della data del preventivo.

In una applicazione commerciale troviamo che questa Dialog Box è arricchita di numerosi altri campi di cui abbiamo già parlato.
Siccome il Drop Down del codice cliente è gestito dinamicamente come descritto per la precedente Dialog Box, esiste un solo aspetto interessante da evidenziare che è inerente l’Edit Box della data del preventivo: non è possibile avvalorarla mediante uno statement che prende il dato direttamente dal foglio in cui è contenuta l’informazione, perché inevitabilmente la data verrà visualizzata nel formato anglosassone, ovvero mese/giorno/anno.
Di conseguenza lo statement seguente non lavora correttamente:

DialogSheets("OrdHdrDlg") _
  .EditBoxes("OrdData_EB").Text = _
  Worksheets("OrdHdrDlgInp"). _
  Cells(11, 1) .Value

Una possibile soluzione è rappresentata dallo statement:

DialogSheets ("OrdHdrDlg")
  .Editboxes("OrdData_EB") .Text =
  Day(Worksheets("OrdHdrDlgInp") _
  .Cells(11, 1).Value) & "/" & _
  Month(Worksheets ("OrdHdrDlglnp") _
  .Cells(11, 1).Value) & "/" & _
  Year (Worksheets ("OrdDlgInp") _
  .Cells(11, 1) .Value)

il quale estrae dalla cella del foglio la data sezionandola nei suoi componenti mediante le funzioni Day(), Month() e Year() che restituiscono il giorno, il mese e l’anno; concatenando opportunamente i dati così ottenuti si ottiene la visualizzazione corretta della data nel formato desiderato.

Gestione degli articoli

La gestione degli articoli intesa come caricamento, cancellazione e modifica viene gestita mediante la Dialog Box DlgOrdine.
Essa può essere considerata composta da due zone: quella superiore dove appaiono le informazioni del singolo articolo e quella in feriore dove è visibile una List Box che contiene i vari articoli inseriti nel preventivo.
Analizziamo la prima parte.
In essa appare un Drop Down per la selezione dei codici degli articoli ed alla sua destra una zona in cui essi vengono decodificati mostrandone la descrizione.
Sotto deve venire digitata la quantità.
L’Edit Box a destra permette di inserire il valore unitario: viene automaticamente proposto il valore di listino preso dall’anagrafica articoli, ma la possibilità di correggere il prezzo è fondamentale per consentire l’applicazione di sconti o ribassi.
Automaticamente viene calcolato il prezzo complessivo e visualizzato a destra del prezzo. Si può notare che que st’ultimo viene aggiornato ad ogni minima modifica della quantità o del prezzo: questo accade perché la macro associata alle due Edit Box viene invocata ogni qualvolta l’utente inserisce o cancella dei caratteri: questo è un comportamento tipico dell’oggetto Edit Box.
Vicino allo spigolo superiore destro viene riportato il numero di riga del preventivo sul quale si sta lavorando. E' un dato informativo non particolarmente significativo.
Il tasto Aggiungi permette di memorizzare quanto digitato, mentre Clear provvede a cancellare i dati inseriti li mitatamente all’ articolo.
Un aspetto interessante del tasto Aggiungi consiste nel fatto che riveste due funzionalità: la prima è quella di aggiungere un nuovo articolo al preventivo, mentre quando si è in una fase di modifica di dati già immessi esso cambia nome e diventa Aggiorna per meglio indicare che il suo utilizzo modificherà i dati precedenti.
Vediamo in pratica cosa accade.
Se l’utente seleziona un articolo già inserito tramite la List Box che appare nella seconda parte della Dialog Box e preme il tasto Modifica che si trova sul lato inferiore della medesima, viene eseguita la macro associata al tasto premuto che provvede a copiare i dati nella prima metà della Dialog Box ed a modificare la scritta del tasto Aggiungi trasformandolo in Aggiorna; questo cambiamento viene memorizzato in una variabile.
E da osservare che la scelta delle due parole che appaiono sul tasto iniziano entrambe con la lettera A: non si tratta di un caso, in quanto ciò rende possibile associare al tasto un Accelleraror Key (ovvero quella lettera sottolineata che indica che l’oggetto può essere attivato mediante la pressione simultanea sulla tastiera di ALT e la lettera stessa) che rimane valido e significativo nonostante il cambiamento della descrizione del tasto.
La variabile che memorizza lo stato del tasto viene sfruttata dalla macro associata al tasto Aggiungi che è la stessa di Aggiorna in quanto si tratta in realtà del medesimo oggetto: se essa ha valore zero significa che il tasto espleta la funzione di inserimento di un nuovo articolo ovvero il tasto è visualizzato come Aggiungi, altrimenti contiene il numero di riga che deve essere modificato mentre nella prima metà della Dialog Box sono stati inseriti i dati presi dalla riga del preventivo sottoposta a modifica.
Al termine dell’aggiornamento il tasto viene ripristinato in Aggiungi e la variabile azzerata.
Per cancellare un articolo è sufficiente selezionarlo nella List Box e premere il tasto Elimina posto in basso nella Dialog Box. Se invece si desidera can cellare tutti gli articoli inseriti, bisogna premere il tasto Elimina Tutto.
I tasti OK e Cancel mantengono i canonici significati tipici delle procedure dell’ambiente Windows.

Stampa preventivi e fatture

Selezionando il tasto Stampa Preventivo/Fatt della Dialog Box in figura 2, viene eseguita la stampa del documento. Essa è realizzata con una tecnica estremamente semplice quanto efficace: esiste un foglio di nome Fattura che contiene il layout per i preventivi e le fatture che viene compilato prelevando i dati dall’archivio dei preventivi.
La differenziazione fra i due documenti avviene verificando se il preventivo è stato fatturato: in caso affermativo il campo relativo al numero di fattura deve contenere un valore. Questa informazione viene comunicata alla procedura di stampa che si comporta di conseguenza facendo opportuna mente apparire la dicitura "Fattura" oppure "Preventivo".
Esiste un aspetto di fondamentale importanza che chi si accinge a programmare in VBA for Excel deve conoscere: non è possibile stampare un foglio se c’è una Dialog Box attiva.
Di conseguenza non è possibile collegare ad un tasto una macro che tenti di eseguire la stampa.
Il problema può essere tranquillamente risolto facendo sì che il tasto che richiede la stampa provochi anche la chiusura della Dialog Box dopo aver eseguito il set di un flag.
Per essere più espliciti, il tasto di stampa deve avere la proprietà Dismiss, la quale può essere attivata con il seguente metodo:

• attivare il Dialog Sheet che contiene il bottone in questione e selezionarlo;
• aprire il menu che compare premen do il tasto destro del mouse e sele zionare la voce Format object...;
• Attivare la casella Dismiss che ap pare in Control;

e la macro associata al tasto non deve far altro che porre a vero un flag dichiarato Public (nella procedura si chiama flagStampaFattura).
Tutto il lavoro viene conseguentemente delegato alla procedura che attiva la Dialog Box: dopo aver visualizzato quest’ultima, la procedura deve verificare il valore del flag gestito dalla macro del bottone: se esso contiene il valore indicante la richiesta di stampa, la procedura chiamerà la subroutine relativa.
Questa tecnica presenta una controindicazione: se, ad esempio, la Dialog Box dispone di più bottoni che producono differenti stampe, la proprietà Dismiss attivata per ogni bottone impedisce all’utente di poter selezionare più di una stampa. L’unica soluzione è includere la visualizzazione della Dialog Box all’interno di un ciclo che ha fine quando viene premuto un determinato tasto come l’OK o il Cancel.
In pratica, ad ogni tasto premuto dall’utente, la procedura chiude la Dialog Box, esegue la stampa associata al tasto premuto che viene riconosciuta verificando i vari flag e torna alla fase di visualizzazione.

La fatturazione

Dopo che sono stati caricati i dati mediante il bottone Inserimento/Correzione, è possibile procedere alla fatturazione ricorrendo al bottone Fatturazione Preventivo.
Esso attiva una macro che richiede il numero di fattura da attribuire al preventivo e memorizza l’informazione nell’archivio preventivi che, come già detto, ha anche funzione di archivio fatture. Come data della fattura viene presa la data del giorno mediante la fun zione Excel Now(); qualora ciò non fosse congeniale alle vostre necessità è possibile sostituirla con una InputBox() affinché venga visualizzata una finestra in cui si possa digitare la data.
Dopo che il preventivo è stato fatturato, il tasto Inserimento/Correzione non permette più di accedere ai dati: rimane solo la possibilità di ristampare la fattura; questo accade in quanto le fatture sono documenti fiscali, ma se per qualunque ragione si rendesse necessario apportare delle modifiche, basta prendere il foglio OrdiniHeader e cancellare gli estremi della fattura, per cui il tasto non risulta più inutilizzabile.

Limiti della procedura

Uno dei limiti più visibili è il numero di articoli che questa macro è in grado di gestire per ogni preventivo/fattura. Infatti la macro non gestisce l’ovdrflow sul foglio Fattura, perciò il numero di articoli deve essere uguale o inferiore alla capacità prevista per Fattura.
Il problema si risolve rapidamente prevedendo che, quando il numero di articoli inseriti nel documento ha riempito la pagina o essi sono terminati, nella cella $D$11 appaia la scritta "Pagina" e nella cella $F$11 appaia il numero di pagina che viene controllato da una variabile contatore. Dopo si procede a stampare il documento, cancellare le righe degli articoli e riempire il documento con gli articoli successivi.
Un aspetto decisamente migliorabile sono le ricerche di una chiave all’interno degli archivi: attualmente avvengono sequenzialmente, ma l’utilizzo di funzioni di LookUp consentirebbero un sensibile miglioramento delle prestazioni allorquando gli archivi fossero abbastanza voluminosi.

Conclusioni

Prima di congedarmi devo ricordarvi, a termini di legge, che il software alle gato alla rivista e qui spiegato è distribuito gratuitamente sulla base del così com'è ovvero senza fornire alcun tipo di assicurazione o garanzia sul corretto funzionamento o idoneità allo scopo: chiunque lo utilizza deve sincerarsi che esso sia confacente alle proprie esigenze e soddisfi pienamente tutti i requisiti per l’uso. Chiusa questa parentesi doverosa in quanto sono stati affrontati argomenti fiscali quali le fatture, vi ricordo che se avete problemi inerenti questa procedura potete mandarmi un E-mail via Internet o sulla BBS Shineline.

Tratto da:
Paolo Guccini
Rivista DEV Computer Programming
Edizioni Infomedia
Aprile 1996