Stringhe come Formule - Seconda parte

Abbiamo già visto nell'articolo "Stringhe come Formule" presente nella sezione vba del sito http://ennius.interfree.it come intervenire quando per necessità operative, siamo costretti a specificare passaggi di calcoli, cioè scrivere in una cella la descrizione in formato testo delle operazioni che porteranno ad un risultato, ad esempio : 3.5*3.5, e di cui vorremo comunque in altra cella il risultato della moltiplicazione; abbiamo in quel caso trasformato la stringa testo in formula mediante l'aggiunta del segno di formula ("=") tramite istruzione vba.

Questa procedura di fatto non esegue il calcolo, ma pone nella cella di destinazione un concatenamento di stringhe , ed è Excel che esegue il calcolo, "ingannato" dal segno di formula, non il vba.

Esistono altre strade percorribili, che consentono di estrarre i numeri da una stringa, e fare in modo che sia il vba ad eseguire i calcoli, ponendo nella cella di destinazione il risultato del calcolo, e non una formula. Tutto questo porta a facilitazioni nello sviluppo di calcoli e o passaggi successivi che si baseranno quindi su valori numerici reali.

Nella colonna A abbiamo la descrizione di un calcolo, in formato che Excel considera testo, e vorremo nella colonna B il risultato dell'operazione, in questo caso delle moltiplicazioni.

Per estrarre i valori, seguiremo questi passaggi: useremo delle variabili (a,b,c,d) alle quali assegnare i passaggi. Prendiamo per ora in esame una sola cella, la A1, che contiene 3.5*3.5 :

a questo punto moltiplichiamo c * d e il risultato lo poniamo in una cella a lato. Ma vediamo la routine, dove faremo scorrere (come esempio) le celle nell'intervallo A1:A10 tramite un ciclo For Each ..Next, controllando che la cella in quel momento letta non sia vuota, altrimenti passiamo alla successiva con Next:

Sub Calcola()
Dim CL As Object
For Each CL In Range("A1:A10")
If CL <> "" Then 'se la cella è diversa da vuoto, cioè contiene dati, allora...
a = Len(CL.Value) ' con "a" si memorizza la lunghezza della stringa nella cella, presa con Len
b = InStr(1, CL, "*") ' con "b" si memorizza la posizione dell' * presa tramite InStr
c = Mid(CL, 1, b - 1) ' con "c" si ottiene tramite la funzione Mid, i numeri prima dell'asterisco
d = Mid(CL, b + 1) 'e con "d" si ottiene tramite Mid, i numeri dopo l'asterisco

'a questo punto "c" è uguale a 3.5 e "d" è uguale a 3.5, si possono moltiplicare e inserirli nella cella 'a lato della cella letta, con Offset
CL.Offset(0, 1) = Val(c) * Val(d)
End If
Next
End Sub

Attenzione: vi sarete accorti che i numeri scritti nelle celle della colonna A usano come separatore decimale il punto ( . ) come si usa nel sistema inglese; questo tipo di sintassi non verrebbe accettata da Excel per eseguire un calcolo aritmetico, tuttavia, in molti riferimenti tecnici, è abitudine scrivere usando il punto, ed Excel lo accetta in quanto lo considera "testo"; sappiamo anche che non è possibile eseguire operazioni aritmetiche su valori testo, ma solo su valori numerici. Al contrario, il vba, ragionando in inglese, accetta ed esegue la moltiplicazione tra 3.5 per 3.5, e restituirebbe il giusto risultato (12.25), ma dovendolo poi trasferire ad una cella del foglio, diventa necessario dichiarare il giusto tipo di dati in modo che Excel possa riconoscerli come valori numeri e/o con decimali, e scriverli come giustamente è abituato a fare, usando cioè la virgola come separatore decimale. Per questo usiamo Val(valore) che è una Funzione di conversione del tipo di dato. Ed Excel scriverà nella cella il numero 12,25. (Il ciclo provvederà a completare i calcoli, riga dopo riga.)

Se invece i valori nella colonna A, fossero scritti con la virgola come separatore di decimali, es. 3,5*3,5 , ecc., dovremmo modificare la Funzione di conversione per il tipo di dato per ottenere il giusto risultato, usando CDbl (numero con decimali, di Tipo Double) al posto di Val, per ottenere il risultato giusto, così:

Estendiamo l'esempio, aggiungendo ulteriori esigenze. Supponiamo che la tabella vista sopra sia composta da colonne alternate di valori stringa (quindi la colonna A, la C, la D ecc) e che ogni colonna in più venga aggiunta con cadenze periodiche, per esempio ogni mese. Vorremo per ogni colonna poter usare la colonna a lato dove svolgere i calcoli visti con la routine sopra, ma con la complicazione che i valori calcolati siano il frutto del conteggio relativo alla colonna aggiunta più i valori calcolati della colonna precedente, in modo che l'ultima colonna sia il totale di tutte le righe di tutte le colonne dove sono presenti valori. Cerco di spiegarmi : le colonne gialle sono quelle in cui inseriamo i valori come valori stringa, con progressione mensile: nella colonna a lato di ogni colonna gialla, vogliamo il calcolo dei valori per quanto riguarda colonne A e B, ma a partire dalla C, quindi nella colonna D, vogliamo il conteggio dei valori numerici della colonna C più la somma con i totali ottenuti nella colonna bianca precedente: in D1 vogliamo il risultato della moltiplicazione di 3.2 x 3.7 (C1) più il valore in B1 (12,25); nella colonna F vorremo (quando inseriti) il calcolo della colonna E più i risultati della colonna D. In questo modo otterremo che gli ultimi valori registrati saranno il totale del mese più i mesi precedenti. Un pò più chiaro detto così? Lo spero. Quindi avremo:

Per questa seconda necessità, abbiamo bisogno di un ciclo esterno, che scorra le colonne, scalando di una colonna ad ogni ciclo e per 24 colonne (12 per i mesi + 12 per i conteggi). Gli faremo controllare se la prima cella della colonna in quel momento spazzolata contiene dati : se conterrà dati eseguiremo in ciclo For Each Next che leggerà le celle della colonna, estrarrà i numeri, eseguirà le moltiplicazioni, e SE saremo oltre la prima colonna (colonna 1), provveda a sommare anche il valore contenuto nella cella antecedente (quindi del mese precedente). Condizione necessaria sarà che le colonne che conterrano i dati mensili, abbiano dati nella prima cella di colonna, altrimenti il ciclo esterno passa alla colonna successiva.

Potremo comunque predisporre delle intestazioni di campo, in modo che il ciclo trovi comunque la cella di inizio colonna occupata, tanto se non ci saranno dati, non avverrà nessun conteggio.

Sub Calcolo2()
Dim CL As Object
For col = 1 To 24 Step 2 'inizia il ciclo che scorrerà le colonne, dalla 1 alla 24, con passo 2
If Cells(1, col) <> "" Then 'se la cella, prima riga, colonna numero "col", contiene dati, inizia il 'ciclo interno che scorrerà le righe fino alla riga 10; ognuno varierà la lunghezza secondo necessità
For Each CL In Range(Cells(1, col), Cells(10, col))
If CL <> "" Then 'queste istruzioni sono le stesse già viste sopra
a = Len(CL.Value)
b = InStr(1, CL, "*")
c = Mid(CL, 1, b - 1)
d = Mid(CL, b + 1)
If col = 1 Then 'ora si controlla in quale colonna ci troviamo; se siamo alla A (la uno) eseguiamo
CL.Offset(0, 1) = Val(c) * Val(d) 'svolgiamo la moltiplicazione nella cella a lato (la B)
Else 'altrimenti svolgiamo la moltiplicazione nella cella a destra e al totale sommiamo il valore della 'cella a sinistra della cella letta
CL.Offset(0, 1) = (Val(c) * Val(d)) + CL.Offset(0, -1).Value
End If
End If
Next
End If
Next
End Sub

La procedura scorrerà tutte le colonne e solo se ci saranno valori stringa nelle colonne previste, si avrà l'esecuzione delle moltiplicazioni e relativa somma in progressione. Per illustrare ancora un esempio con anche la colonna E contenente dati, e relativi risultati nella colonna F:

Ovviamente potremo prevedere cicli di 3 colonne: una dove inserire i valori stringa, una dove scrivere il risultato della moltiplicazione, e la terza dove inserire la somma in progressione. Basterà modificare di poco le istruzioni.