Anno nuovo, bilanci nuovi

L'insistenza di amici & fan affinché si cimentasse con un modello Excel per la contabilità di famiglia ha visto chi scrive a lungo recalcitrante. Se è permessa una certa immodestia, l'autore crede di aver creato applicazioni di ben maggiore complessità, ma il punto è che si trova nella condizione dei migliori barbitonsori: come costoro vanno in giro spettinati e mal rasati, così anch'egli confessa di non utilizzare il foglio elettronico per tener in qualche ordine la propria (caotica) contabilità.

D'altro canto, nel momento dello studio di fattibilità, le cose, a voler essere esigenti, si sono rivelate non così semplici come potrebbe sembrare. Che fare? Dopo matura riflessione si è optato per una soluzione semplificata, ma tale da permettere ai perfezionisti di perfezionarla. D'altronde, se i perfezionisti non perfezionassero che perfezionisti sarebbero mai? Ma sicuramente pure i mortali comuni e non comuni si arrangeranno adattando alle proprie esigenze o preferenze l'applicazione seguente:

Conti personali.xls

Un nome che è tutto un... programma, ma la valenza di questo programmino è soprattutto didattica. L'occasione si presta per illustrare tecniche di Excel particolari o misconosciute, fra cui:

a)      utilizzo abbondante di nomi di zona, fra cui i nomi relativi o "ballerini", come celiando li si potrebbe chiamare;

b)      routine di evento

I primi vengono richiamati nel riquadro "I nomi ballerini di Excel". Quanto agli eventi se ne parlerà in modo più diffuso e sistematico più avanti: "PARTE SECONDA: LE MACRO D'EVENTO".

Ma fin d'ora tutti si renderanno conto dell'astuzia di certe tecniche escogitate nel modello, come quella delle "formule al volo", utilizzabile altrove.

Giro esplorativo panoramico del modello

Descriviamo i passaggi necessari per compiere un tour guidato nel modellino.

. Al lancio di "Conti personali.xls" dovrebbe comparire la copertina del modello, realizzata applicando al foglio denominato Menu, una WordArt e la ClipArt (efficace, non è vero?) reperibile nel repertorio MS Office. Inoltre, se l'altoparlante è acceso, si sentirà subito una musica d'apertura. Corrisponde all'oggetto sonoro in alto a sinistra, e volendo lo si può doppio-cliccare per riascoltarlo. Qui si può subito svelato un semplice altarino:

Private Sub Workbook_Open

Foglio1.Activate: Range("A1").Select

Foglio1.OLEObjects(1).Verb

End Sub

Si tratta di una macro d'evento e la si scopre recandosi nell'Editor VBA, con Alt+F11, e attivando ThisWorkbook. In due parole, l'evento Open della nostra cartella di lavoro (oggetto Workbook) scatena la routine Workbook_Open ogni volta che la carichiamo, assicurando in primo luogo l'attivazione di Foglio1 (il foglio "Menu" del modellino), anche se in precedenza abbiamo salvato. E Verb? È il metodo che, applicato all'oggetto OLE di indice 1 (il primo, e unico, incorporato), lo fa risuonare.

A tutti è palese il significato navigatorio dei tre pulsanti sulla sinistra. Sono un'alternativa alle linguette (schede, nel gergo Excel), che volendo si possono nascondere con Strumenti / Opzioni / scheda Generale, togliendo poi la spunta alla casellina Schede. Ma prima di attivare il primo, si fa notare che il terzo è diverso dagli altri. Chi sa dire perché? Riflettere bene, prima di rispondere, poi andare a sbirciare la risposta al quesito in fondo alla prima parte dell'articolo.

Passiamo ora, di riffe o di raffe, al secondo foglio. Notiamo subito lo pseudo pulsante Menu: presente pure negli altri fogli ha lo scopo di far tornare al foglio Menu, come è immediato verificare.

Prima di proseguire, va fatta un'osservazione, valida pure per gli altri fogli del modello (escluso Tabelle). È stato adottato uno sviluppo di tipo elenco, con valori di input sulla sinistra e formule, applicate a quei dati, sulla destra.

Il foglio delle Entrate e delle Uscite

Tutto regolare, ma il punto, lievemente inusuale per chi si attende totali e altre funzioni riepilogative in fondo, è che esse si trovano in cima al foglio. Inoltre si è applicato il comando Finestra / Blocca riquadri, sotto le intestazioni, sicché esse sono visibili su righe remote, assieme ai totali.

Diamo ora uno sguardo al foglio del Conto corrente. Anch'esso è concettualmente analogo al brogliaccio delle Entrate e uscite, ma ha qualche pretesa in più. Ci si è infatti sforzati di valutare gli interessi maturati progressivamente, nel corso dell'anno, sulla base dell'interesse applicato (cella C3), mediante una funzione personale VBA. Non essendo degli esperti bancari si è agito in base alla logica, inoltre va detto che il modello - didattico, si ripete: didattico! - non tiene conto delle spese, fisse e sulle operazioni. Ciò nondimeno, riteniamo che possa aiutare a controllare la disponibilità sul conto, perlomeno per gente oculata che non va mai in rosso. Un'altra funzione personale è usata nelle celle del "cruscotto" in alto. Presenta un'imperfezione che si spera di risolvere in seguito, magari sulla scorta di suggerimenti che dovessero pervenire a questo sito.

Il foglio del Conto corrente

Passiamo ora al foglio Scadenzario. Come viene anche (e meglio) precisato nella risposta al quesito (in fondo all'articolo), appena si attiva questo foglio si scatena la macro dell'evento Activate. Basta riportarne il nucleo centrale, per comprenderne il mestiere, persino da parte di chi mastica poco il VBA (Visual Basic per applicazioni):

Private Sub Worksheet_Activate

' o m i s s i s

Msg = "Vi sono " & NScad & " scadenze!"

Tit = "Data odierna: " & Range("DataOdierna").Value

MsgBox Msg, vbExclamation, Tit

' o m i s s i s

End Sub

La figura stessa ce lo dice: se il numero di scadenze, calcolato nella cella I4, è diverso da zero ne fuoriesce la segnalazione.

Il foglio delle scadenze finanziarie

Il nostro giretto esplorativo potrebbe terminare qui. A dire il vero ci sarebbe il foglio Tabelle, contenente, di fatto, soltanto la tabella dei Flussi di cassa (nome forse non felice), in cui i vari tipi di spesa o introito sono classificati secondo un codice. Ne riparleremo, per il momento, già che siamo sullo Scadenzario, sperimentiamone l'impiego.

Questo foglio contiene solo una tabella, che classifica i tipi di voci

Si riveda la figura precedente, immaginando di digitare l'ultima voce, a riga 12. Giunti in colonna F si digiti solo "a" o "d": automaticamente verrà scritto "RICEVERE" o "PAGARE" nella cella. Spostandoci nella cella accanto, dovremo digitarvi "Si" o "No" e, in questo caso, le formule delle colonne H e I vengono aggiunte. Automaticamente! Si tratta di una tecnica che l'autore ha escogitato, battezzandola FORMULE AL VOLO e spiegandola nel suo ultimo libro "Excel 2000 VBA", 620 pagine, per i tipi di Futura - Jackson Libri). Tale artificio, basato sull'evento Change, verrà descritto prossimamente, ma fin d'ora i più esperti e audaci possono esaminare le Sub Worksheet_Change presenti nei vari moduli Foglio1, Foglio2 ecc. del modello.

La tecnica delle formule al volo viene sfruttata anche nel brogliaccio Entrate-Uscite. Basta portarsi in esso per rendersi conto, aggiungendo una nuova riga, che:

se si digita il codice dell'operazione in colonna C, il Tipo operazione viene inserito automaticamente nella colonna accanto;

Digitando un'entrata o un'uscita, la formula della differenza è inserita al volo in colonna H.

È poi immediato constatare che il codice operativo si può attingere dalla casella a discesa in alto, con lo stesso effetto di aggiunta automatica del Tipo operazione (e a beneficio degli smemorati). Anche questo è frutto di una macro, che sarà illustrata prossimamente, comunque offerta, a mo' di saggio, all'analisi dei più esperti nel Listato 1 (e unico), unitamente alla routine d'evento Change.

L'input su questo, come su altri fogli è facilitato da tecniche anche astute

Il progetto statico (e le formule in gioco)

La tecnica delle formule al volo risolve, almeno nei casi più semplici, un annoso problema degli spreadsheet, che si presenta regolarmente quando si aggiungono dei dati. Come estendere le formule ai nuovi venuti? Il modo più ovvio, ma tedioso, sta nel copiarsele a manina, mentre è criticabile la soluzione adottata da molti, consistente nel predisporre un sacco di formule del tipo:

=SE(A2="";"";. . .)

Così non si fa che appesantire la cartella di lavoro!

Ma è tempo di esaminare più da vicino i vari fogli, indicando come è stato sviluppato il progetto "statico", ossia al netto delle macro. Il lavoro si è articolato in tre punti:

layout e formattazione;

assegnazione di nomi opportuni, a celle e intervalli;

inserimento di formule.

Note essenziali riguardo al primo punto. Del blocco dei riquadri si è già detto. Aggiungo solo che il corpo 9 dei caratteri è stato impostato sull'intero foglio, previo clic sul pulsante Seleziona tutto (all'incrocio delle intestazioni di colonne e righe, come non tutti sanno) e che, in tutte le colonne come la E di figura precedente, è stato scelto il formato Data predefinito "14/3/1998" di Excel 9 (siamo nel Duemila, occorrono anni a 4 cifre). Con Excel 97 si usi un formato personale, avente la maschera "g/m/aaaa".

Formato personalizzato Data per ottenere l'anno di 4 cifre

Quanto ai quattrini, per essi ho scelto un poco ingombrante formato valutario con 0 decimali e negativi in rosso (e l'Euro?, è stato trascurato.).

Venendo ai nomi, premendo F5, se ne scoprirà una caterva. Alcuni di questi sono stati creati per rendere le macro più indipendenti rispetto a future modifiche. Elenchiamo, per ora, quelli richiamati dalle formule, cominciando dai nomi utilizzati nel Brogliaccio:

TabellaVoci corrisponde all'intervallo B4:B17 del foglio Tabelle;

Entrate è il nome relativo di un dato in colonna F;

Uscite è il nome "ballerino" di un dato in colonna.

A questo punto è urgente andarsi a leggere il riquadro sui nomi ballerini, dopodiché saranno chiare a tutti le formule seguenti, entrambe inserite "al volo", come s'è detto:

D8=CERCA(C8;TabellaVoci)

H8=Entrate-Uscite

H9=H8+Entrate-Uscite

Si è adottata, chi non l'avesse capito, la convenzione di indicare la cella di appartenenza davanti all'uguale e il sottinteso che la prima e la terza formula siano replicate in basso, mutatis... referentibus. Non occorre poi insistere nel dire che le formule da H9 in poi vogliono esprimere i progressivi sbilanci di cassa: nulla di rigoroso, ovviamente, mentre un po' meglio vanno le cose col Conto corrente.

Non suscitano poi sorpresa né sconcerto le formule riepilogative in alto, che quindi non saranno commentate:

F5=SOMMA(F8:F65536)

G5=SOMMA(G8:G65536)

H5=F5-G5

A proposito della tabella, essa è utilizzata non solo dalla funzione CERCA() predetta, ma anche dalla casella a discesa o combinata che dir si voglia (combo box), tramite la proprietà ListFillRange, che definisce da dove attingere il minielenco. Qui va precisato che l'utilizzo del nome TabellaVoci è valido a fronte di eventuali modifiche ad essa. Gli utenti di medio calibro lo sanno bene, in particolare assegnano anche al ComboBox un nome di zona e non i suoi riferimenti:

Nell'assegnare un intervallo a un Combobox è opportuno usare nomi di zona

Nel nostro modellino si è fatto di più, sfruttando l'evento Deactivate del foglio Tabelle per ribattezzare automaticamente tale tabellina:

Private Sub Worksheet_Deactivate

With Range("TabellaVoci")

.CurrentRegion.Name = "TabellaVoci"

End With

End Sub

Si ripete che il discorso sugli eventi verrà ripreso in una successive puntata, comunque dovrebbe essere più che intuitivo il fatto che, grazie alla routinetta appena vista il nome TabellaVoci si aggiorna automaticamente non appena si abbandona il foglio Tabelle.

La gestione del conto corrente

I discorsi fatti fin qui dovrebbero consentire di comprendere anche da soli le formule e il resto negli altri fogli, per cui ora verrà spesa qualche parola per il Conto corrente, tralasciando lo Scadenzario, salvo precisare che la cella di nome DataOdierna contiene la formula fasulla =OGGI()+120. Palesemente utilizzata per scopi di test (nel novembre 99), va corretta in =OGGI().

Venendo al Conto corrente, i nomi in gioco nelle formule sono: Interesse (C3, in formato percentuale) e SaldoIniz (C5). Dopodiché cominciamo con queste formule:

C5=G3+G1

F7=SE(E7;-E7;D7)

G7=SaldoIniz+Movimento

G8=Saldo+Movimento

Ovvio che la prima restituisce un SaldoIniz pari al Saldo più interessi maturati nel 99. La seconda, replicata in basso, pone nella colonna Movimento il Versamento o il Prelievo, col giusto segno algebrico. Le ultime due danno, rispettivamente, il saldo progressivo sul primo movimento e quello sui successivi (distinzione che va fatta, in analogia con quanto visto sul Brogliaccio). E pure qui è in gioco il nome "ballerino" Movimento.

Dal momento che le funzioni d'utente InteressiMatur() e UltimoValInColonna(), l'una utilizzata in colonna H, l'altra nelle celle "cruscotto" G1:I1, direi che, per questo mese, è tutto.

Nel frattempo, correzioni di bug, suggerimenti ed eventuali ampliamenti saranno altamente graditi, con possibilità di pubblicazione.

Subtotali dell'ultim'ora.

Un upgrading fatto all'ultimo momento, viene lasciato all'esame dei più esperti. Tutti potranno notare, oltre ai fogli di lavoro fin qui illustrati, la presenza dell'ulteriore foglio seguente:

Riepilogo

Su di esso, grazie a opportuno codice VBA, scatenato dall'evento di attivazione del foglio appena detto, si ha la meccanizzazione delle mosse seguenti:

1. copia (automatica!) dei dati del Brogliaccio sul foglio Riepilogo;
2. ordinamento per Tipo operazione;

Dopo di che l'utente può attivare, a piacere, uno dei due pulsanti di comando incorporati:

Crea subtotali

Rimuovi subtotali

Di ovvio significato. Chi proprio ignorasse cosa sono i Subtotali, ricordiamo solo che corrispondono al comando Dati Subtotali., con quel che segue, più lettura della Guida in linea.

La macro del primo pulsante crea un report che tatalizza le Entrate e le Uscite sulla base del tipo di spesa. Agendo poi sui pulsantini contrassegnati da "-", creati dalla struttura di subtotalizzazione, l'utente deve occultare tutti i dettagli fino a visualizzare i soli totali parziali. A quel punto si scoprirà un grafichetto a barre incollato sul foglio.

Il foglio Riepilogo permette di creare i subtotali per tipo di spesa. Con tanto di grafico.

Il grafichetto è stato predisposto dall'autore, che confessa di essere stato anche lui sorpreso dal fatto seguente (ignoto ai più!):

Un grafico applicato a un intervallo fornisce automaticamente la graficizzazione dei subtotali se la struttura di questi è "ripiegata" (come in figura)!!

Per contro, se si fa clic anche solo su un pulsantino "+" il grafico diventa impresentabile.

Meditate, gente, meditate.

Risposta al quesito

Come la figura qui sotto mette in chiara evidenza, i primi due sono degli pseudo pulsanti, ottenuti incollando la shape Telaio della barra strumenti Disegno, colorata di grigio e con aggiunta di scritta blu e, quel che più conta, di un hyperlink locale: 1. clic destro sulla shape; 2. opzione Legame ipertestuale; 3. nella susseguente finestra definire (con facili manovre) il legame al foglio e cella voluti. Il terzo è invece un pulsante in piena regola, di tipo "classico" (ossia ottenuto con la barra Moduli), al quale è associata questa semplice macro:

Sub Scadenzario_Clic()

Foglio3.Activate:Range("A1").Select

End Sub

Pseudo pulsanti, in realtà legami ipertestuali locali

Il mestiere è lo stesso degli pseudo pulsanti. Non si poteva dunque fare a meno del pulsante verace? Risposta esatta: sì ma, almeno nel terzo caso, associando sempre la macro appena vista. Infatti i pur comodissimi hyperlink - dio sa perché?, chi scrive no - disattivano gli eventi Activate dei fogli a cui ci portano, la qual cosa inibirebbe, nel caso del foglio Scadenzario, lo scatenarsi di questa Sub, avente lo scopo di lanciare un avviso all'utente:

Private Sub Worksheet_Activate

' o m i s s i s

Msg = "Vi sono " & NScad & " scadenze!"

Tit = "Data odierna: " & Range("DataOdierna").Value

MsgBox Msg, vbExclamation, Tit

' o m i s s i s

End Sub

< RIQUADRO>

I nomi ballerini di Excel (nomi relativi di celle)

Di norma, in Excel i nomi di celle e intervalli corrispondono a riferimenti assoluti, ossia con tanto di dollari (esempi: $A$1, $A$1:$C$20). In realtà il programma consente di assegnare nomi anche a riferimenti relativi (o misti). In gergo si parla di nomi relativi. Per ottenerli, si tolgono i dollari nei riferimenti suggeriti nella casella Riferito a della finestra di dialogo che fa seguito a Inserisci / Nome / Definisci..., ossia si cambia, poniamo, $G$10 in G10.

Di passaggio si fa notare che la creazione tramite etichette, con Inserisci / Nome / Crea, produce solamente nomi assoluti.

Ma qual è il significato di un nome relativo, e a cosa serve? Per capirlo supponiamo che in A2 vi sia il valore 100 e che sia attiva la cella C5. Premiamo Ctrl+F3 (scorciatoia per definire nomi), poi forziamo il riferimento A2 (senza dollari) nella casella Riferito a, affibbiando il nome DueSinTreSu. Lo strambo nomignolo enfatizza che A2, rispetto a C5, si trova due colonne a sinistra e tre righe al di sopra. Se ora scriviamo, in C5, la banale formula seguente:

=1,1*DueSinTreSu

otterremo il prodotto di 1,1 per il contenuto di A2, ossia 110. Fin qui niente di nuovo, ma se trascriviamo la banal formula in un'altra cella qualsiasi, troveremo sistematicamente il prodotto di 1,1 per il contenuto di una cella posta esattamente due colonne prima e tre righe al di sopra di quella della formula. Così se la nuova cella è G10 e la cella E8 contiene 50 la formula in G10 punta ora a E8 e il risultato stavolta è 55.

Una curiosità, che giustifica l'aggettivo "ballerino": se si preme F5 (tasto Vai a) poi si digita il predetto nome DueSinTreSu, viene selezionata una cella via via diversa, sempre due colonne a sinistra e tre righe sopra.

Questi nomi ballerini, che si applicano pure a riferimenti misti, tornano utili per l'aggiustamento automatico dei riferimenti, tanto nella copia come nell'inserimento in un intervallo tramite Ctrl+Invio.

Nel caso della seguente formula del Brogliaccio

=Entrate-Uscite

essendo Entrate e Uscite nomi relativi, se viene ricopiata in basso essa non cambia, tuttavia i riferimenti effettivi si adeguano, al contrario di quanto accade con quest'altra:

=CERCA(F8;TabellaVoci)

che, invece, ricopiata in basso (o altrove) aggiusta il riferimento F8 ma lascia immutati quelli di TabellaVoci, che è un nome assoluto, ergo corrisponde a $B$4:$C$17 sul modello.

Sul Conto corrente è in gioco il nome ballerino Movimento, con analogo significato operativo.

< FINE RIQUADRO >

Gli eventi e le macro d'evento sfruttate nel modello

Elenchiamo gli EVENTI utilizzati nel modello, con a fianco la routine di nome predefinito (da non modificare!) e il modulo in cui tale routine è presente.

Si fa notare che di norma tali speciali routine sono definite come Private Sub in quanto richiamabili solo dall'interno del rispettivo modulo.

Il Listato che segue riporta la routine Workbook_Open presente sul modulo ThisWorkbook.

Private Sub Workbook_Open

Foglio1.Activate: Range("A1").Select

Foglio1.OLEObjects(1).Verb

End Sub

Come si fa presto a capire, l'evento Open dell'oggetto ThisWorkbook, che poi in soldini è il nostro modello, si scatena al caricamento dello stesso e la routine Workbook_Open, nel nostro caso, garantisce l'attivazione del Foglio1 e la selezione della cella A1 (anche se il salvataggio era stato fatto diversamente). Inoltre il metodo Verb dell'oggetto OLE incorporato, vale a dire la musichetta d'apertura, la fa risuonare. Ben presto ci si annoia di questa trovatine? Non c'è che da eliminare l'oggetto, ma non basta: occorre anche togliere la seconda istruzione della routine precedente, altrimenti all'apertura del file il Debugger protesta.

Il Listato che segue riporta le varie routine presenti sul modulo Foglio2 (Entrate-Uscite).

Private Sub Worksheet_Change ByVal Target As Range)

On Error GoTo fine 'Non si sa mai...

Nc = Target.Column: Nr = Target.Row

If Nr <= Range("Inicod").Row Then Exit Sub

If Nc = Range("Inicod").Column Then

With Application

inf = .Min(Foglio5.Range("TabellaVoci"))

sup = .Max(Foglio5.Range("TabellaVoci"))

End With

If Target.Value < inf Or Target.Value > sup Then

Target.Value = ""

Else

Target.Offset 0, 1).FormulaR1C1 = "=LOOKUP(RC[-1],TabellaVoci)"

End If

Exit Sub

End If

Dim Condiz1 As Boolean

Condiz1 = Nc = Range("Entrate").Column

If Condiz1 Or Nc = Range("Uscite").Column Then

Scarto = IIf(Condiz1, 2, 1)

Target.Offset(0, Scarto).FormulaR1C1 = "=R[-1]C+Entrate-Uscite"

End If

fine

End Sub

Private Sub ComboBox1_Click()

On Error GoTo fine 'Non si sa mai...

With ActiveCell

If .Worksheet.Name = "Tabelle" Then Exit Sub

'INCREDIBILE MA VERO!(*)

If .Column <> Range("Inicod").Column Or _

.Row < Range("Inicod").Row Then Exit Sub

.Value = ComboBox1.Value

.Offset(0, 1).FormulaR1C1 = "=LOOKUP(RC[-1],TabellaVoci)"

End With

ActiveCell.Select

fine:

End Sub

'(*) Essendo la TabellaVoci associata al Combo BOX, ogni modifica

'ad essa darebbe luogo allo scatto dell'evento Click o Change, con

'conseguenze comiche: arresto per errore della ruotine ComboBox1_Click

'o ComboBox1_Change e dati semifolli nella celle digitate!

Formule al volo: un quasi-brevetto dell'autore

La routine Worksheet_Change è forse la più pregiata dell'intero modello, e chi scrive è piuttosto orgoglioso di averla escogitata. Anche se nulla si inventa a questo mondo, il procedimento risolve un annoso problema degli spreadsheet: al crescere (o diminuire) dei dati in un foglio di lavoro chi si preoccupa di copiare o comunque estendere le formule ai nuovi dati? Nella stragrande maggioranza dei casi la risposta è: l'utente, mediante gli strumenti - Appunti, drag and drop, ricopiatura ai quattro venti, magari col simpatico quadratino di riempimento - che generosamente Excel mette a disposizione. Chi mastica il VBA escogita macro più o meno ingegnose, associate a pulsanti o altro, che però chiamano sempre in gioco il clic dell'utilizzatore. Ebbene l'uovo di Colombo suggerito e qui messo in atto sfrutta l'evento Change dell'oggetto Worksheet, che si scatena ogni volta che l'utente immette un dato in una cella di quel foglio. Naturalmente occorre, in un dato caso applicativo, discriminare la o le celle atte a scatenare la routine Worksheet_Change e, a questo proposito, torna a fagiolo l'argomento Target (nome predefinito). Qui (come con altre routine d'evento) Target è l'oggetto Range assoggettato al cambiamento (Change). Nel caso specifico della sub Worksheet_Change   i numeri Nc e Nr della colonna e della riga del Target (Target.Column e Target.Row) sono opportunamente confrontati con le coordinate della cella battezzata "Inicod" e coi limiti inf (eriore) e sup (eriore) della zona denominata "TabellaVoci". L'analisi di dettaglio di tali comparazioni sono affidate al paziente, e non sprovveduto, lettore, l'importante è sottolineare che esse servono a discriminare le celle modificate che, pur dando luogo all'evento Change, rendono di fatto nullafacente la routine Worksheet_Change, da quelle che invece fanno sì che venga immessa la "formula al volo" del caso, nella cella scartata di una colonna rispetto a quella Target. Mediante l'istruzione qui di seguito ripetuta, per massima enfasi e comodità:

Target.Offset 0, 1).FormulaR1C1 = "=LOOKUP(RC[-1],TabellaVoci)"

Qui vale la pena di sottolineare, a ignari & immemori, che le formule in Excel VBA conviene che il più delle volte siano inserite con la proprietà FormulaR1C1 dell'oggetto Range, che richiede la pur tediosa, ma estremamente flessibile e potente, sintassi R1C1 (nata in casa Microsoft col vecchio e glorioso Multiplan). Per tale sintassi si rimanda alla Guida in linea, qui basti sapere che i riferimenti RC[-1] sono riferimenti relativi, che puntano con R alla medesima riga del Target mentre come colonna, C[-1], si prende quella scartata di 1 alla sinistra sempre del Target.

Fogli di lavoro senza formule? Non illudiamoci troppo...

La routine ComboBox1_Click per brevità è lasciata al commento autogestito (magari con l'ausilio del Debugger, ad esempio eseguendo la macro passo passo, mediante il tasto F8). Ma prima di chiudere questo fondamentale paragrafo, occorre enunciare una regola, alla quale va dato il massimo risalto (e assolutamente da non dimenticare):

Provare per credere: sostituendo il codice .FormulaR1C1 = "= . . ." con un banale .Value = 33 si assisterà a un inarrestabile effetto cascata, che si può bloccare con Esc o Ctrl+Interr.

Su questa faccenda le discussioni sono aperte. Ci si domanderà: perché le formule non creano l'effetto cascata? Chi scrive risponde, serenamente: non lo so, ma per il nostro scopo, creazione di formule al volo, è una benedizione. La precedente Regola, se si riflette, frustra invece le speranze di chi s'illudeva di poter creare fogli di lavoro senza formule, ergo estremamente leggeri. Probabilmente l'idea sarà affiorata alla mente dei più svegli: se faccio calcolare i risultati alla macro... E invece no. Ma non del tutto:

in primo luogo nulla vieta di applicare l'idea a macro normali, ad esempio assocciate a un pulsante, o magari a un altro evento (potrebbe essere Deactivate?, forse, però ha lo svantaggio di non esibire subito i risultati);

chi scrive è riuscito ad arrangiarsi con deviatoi booleani, definiti a livello modulo, anche se è una strada contorta e poco raccomandabile;

un'altra strada potrebbe essere la traduzione di formule in valori, mediante codice del tipo seguente, che traduce in VBA il comando Modifica Incolla speciale... , con successiva opzione Valori:

Set CellaAtt = ActiveCell

With Range("MiaZona")

.Copy

.PasteSpecial Paste xlValues

End With

Application.CutCopyMode = False

CellaAtt.Select

Si fa notare che la memorizzazione della (vecchia) cella attiva in CellaAtt si può rendere necessaria a causa del fatto che Incolla speciale ha l'effetto si selezionare l'intervallo assoggettato all'incollaggio, e la conclusiva CellaAtt.Select ripristina (grossomodo) la situazione quo ante.

L'interrogativo, però è un altro, abbastanza angosciante: questa tecnica non è, a sua volta, foriera dell'evento Change in cascata?

Per rispondere sì o no non c'è che da provare, in prima persona!

Altri eventi e listati

Il Listato che segue riporta le varie routine presenti sul modulo Foglio3 (Scadenzario). Le intercaliamo, e nemmeno sempre, da commenti del tutto stringati, tacitiani.

Private Sub Worksheet_Activate

With Range("DbScadenze")

.CurrentRegion.Name = "DbScadenze"

End With

Dim NScad As Integer, Msg As String, Tit As String

NScad = Range("NumScad").Value

If NScad > 0 Then

Msg = "Vi sono " & NScad & " scadenze!"

Tit = "Data odierna: " & Range("DataOdierna").Value

MsgBox Msg, vbExclamation, Tit

End If

End Sub

Private Sub Worksheet_Deactivate

With Range("DbScadenze")

.CurrentRegion.Name = "DbScadenze"

End With

End Sub

Commenti. La prima Sub sfrutta l'evento Activate dell'oggetto Worksheet per segnalare eventuali scadenze (il cui numero viene calcolato sul foglio di lavoro da opportuna formula nell'intervallo di nome "NumScad". La seconda (riutilizzabile altrove!) aggiorna automaticamente il nome "DbScadenze" ogni volta che si disattiva il foglio di lavoro corrente per passare a un foglio diverso: la macro è scatenata dall'evento Deactivate e si ricorre alla proprietà CurrentRegion di un oggetto Range. Quest'ultima, si rammenta, corrisponde alla manovra dell'uso manuale Ctrl+*, il cui effetto - provare per credere, chi non l'avesse mai fatto - è la selezione di tutte le celle contigue a quella attiva. Perché la macro funzioni si richiede solo che l'utente, nell'aggiungere o togliere record all'elenco "DbScadenze" lo mantenga compatto (senza righe vuote) e, inoltre, lasci almeno una colonna vuota alla sua destra.

Private Sub Worksheet_Change ByVal Target As Range)

On Error GoTo fine 'Non si sa mai...

If Target.Row < Range("TIPO").Column Then Exit Sub

If Target.Column = Range("TIPO").Column Then

Select Case LCase Target.Value)

Case "d"

Target.Formula = "=""PAGARE"""

Case "a"

Target.Formula = "=""RICEVERE"""

'è un po' folle, ma l'inserimento di un VALORE

'nel TARGET scatenerebbe di nuovo l'evento CHANGE

'dando origine a un loop!

End Select

End If

If Target.Column = Range("TIPO").Offset(0, 1).Column Then

Dim ZonaFormule As Range

With Target

Set ZonaFormule = Range(.Offset(-1, 1), .Offset(-1, 2))

End With

ZonaFormule.Copy ZonaFormule.Offset 1, 0)

End If

fine

End Sub

La routine precedente svolge, mutatis mutandis, un lavoro analogo a quello descritto nel paragrafo precedente. Ai bravi & volonterosi lettori ogni commento.

Il Listato che segue riporta la routine presente sul modulo Foglio4 (Conto corrente). È, di nuovo, relativa al pluricommentato evento Change, per cui chi legge può arrangiarsi da solo...

Private Sub Worksheet_Change ByVal Target As Range)

Dim NumCol As Integer, NumRig As Integer

NumRig = Target.Row

If NumRig <= Range("DataIniz").Row Then Exit Sub

NumCol = Target.Column

If NumCol = Range("DataIniz").Column Then

If LCase Target.Value) = "oggi" Then Target.Value = Int(Now)

End If

If NumCol = Range("VersamIniz").Column _

Or NumCol = Range("PrelievoIniz").Column Then

NumCol = Range("MovimIniz").Column

Range("Rigaformule").Copy Destination:=Cells(NumRig, NumCol)

End If

End Sub

Il Listato che segue riporta la routine presente sul modulo Foglio5 (Tabelle):

Private Sub Worksheet_Deactivate

With Range("TabellaVoci")

.CurrentRegion.Name = "TabellaVoci"

End With

End Sub

Palesemente, si tratta dell'evento Deactivate, relativo al Foglio5 ed è a tutti chiaro che tale Sub svolge lo stesso mestiere dell'analoga relativo al Foglio3, applicato stavolta alla "TabellaVoci": l'utilizzatore può adattarla, ampliandola o restringendola, alle proprie esigenze, preoccupandosi solo di mantenerla compatta, come già raccomandato.

Una sola, minuscola routine è presente sul modulo Foglio6 (Riepilogo):

Private Sub Worksheet_Activate

CopiaQuiDati

End Sub

A scatenarla è l'attivazione del Foglio6, ma il codice invocato a sua volta corrisponde alla routine CopiaQuiDati. Questa si trova sul Modulo2, in buona compagnia:

Sub CopiaQuiDati()

RimuoviSubtotali

Set UltimaCella = ActiveSheet.UsedRange.SpecialCells(xlLastCell)

Range(Range("Inidati"), UltimaCella).Clear

With Foglio2.Range("inicod")

Range(.Cells(1, 1), .End(xlDown).Offset(0, 4)).Copy Range("Inidati")

End With

With Range("Inidati").Offset(-1)

.Sort Key1:=.Offset(0, 1), Order1:=xlAscending, header:=xlYes

End With

End Sub

Sub ApplicaSubtotali()

Range("Inidati").Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5), _

Replace True, PageBreaks:=False, SummaryBelowData:=True

End Sub

Sub RimuoviSubtotali

Range("Inidati").RemoveSubtotal

End Sub

Le operazioni svolte da tale combriccola sono quelle descritte nel paragrafo "Subtotali dell'ultim'ora". Il codice risulta abbastanza parlante, pertanto ci limitiamo a invitare gl'immemori a rileggere il paragrafo predetto, tramite un clic sul seguente legame ipertestuale:

SubtotaliUltimOra

Per concludere

Per completezza e dovere di cronaca riportiamo infine il codice VBA presente nel Modulo1, relativo a un paio di funzioni personalizzate (o d'utente, come anche si dice):

Function InteressiMatur(Movimenti As Range, Tasso As Single, ScartoData) As Double

'ScartoData: l'offset della colonna DATA rispetto alla colonna MOVIMENTI

Dim NumMov As Integer, FinalPer As Date, Periodi

NumMov = Movimenti.Count

FinalPer = Movimenti(NumMov).Offset(0, ScartoData)

'determina il periodo finale

For Each Mov In Movimenti

'MsgBox Mov.Address & " - " & Mov.Value '& " - " & IntMat

Periodi = FinalPer - Mov.Offset(0, ScartoData)

'MsgBox FinalPer & " - " & Mov.Offset(0, ScartoData) & " = " & Periodi

IntMat = IntMat + Mov.Value * Periodi * Tasso / 365

Next

InteressiMatur = IntMat

'Application.Calculate

End Function

Function UltimoValInColonna(Cella As Range)

Dim n As Long 'il tipo Integer NON basta!

With Columns(Cella.Column)

n = .Cells.Count 'NE CRESCE! Si sa già che n =65536

'Ma va bene per un eventuale futuro Excel dotato di più di 65536 righe

UltimoValInColonna = .Cells(n).End(xlUp)

End With

End Function

Sub TestUltimoValInColonna()

MsgBox UltimoValInColonna(Range("H1"))

End Sub

Function SommaDaFondoACima Cella As Range)

Dim MiaColonna As Range, n As Long

Set MiaColonna = Columns(Cella.Column)

n = MiaColonna.Cells.Count 'NE CRESCE!

Set MiaColonna = Range(Cella.End(xlDown), MiaColonna.Cells(n).End(xlUp))

For Each c In MiaColonna

If IsNumeric c.Value) Then

SommaDaFondoACima = SommaDaFondoACima + c.Value

End If

Next

End Function

Sub Scadenzario_Clic()

Foglio3.Activate: Range("A1").Select

End Sub

Commenti? Nessuno. Non è solo una questione di spazio residuo (tiranneggiante) e di stanchezza finale (non trascurabile). Il fatto è che il procedimento adottato per le funzioni in parola da un lato si rivolge palesemente a gente non troppo digiuna di ars programmandi (gli altri siano paghi dell'ars amandi!). D'altro canto, l'autore in tutta onesta non è del tutto sicuro della rispondenza di tale funzione, sicuramente valida didatticamente, alle specifiche dei conti correnti bancari.

Appello conclusivo

Terminiamo pertanto con un appello finale A CHI LA SA LUNGA:

RILETTURA:

Inizio documento Parte seconda: macro ecc.