Svolgere calcoli col VBA. - dal 04/09/04 pagina vista: volte

Questo articolo è destinato ai "pellegrini" ai primi passi nel vba, e se pure tutte le informazioni che qui useremo sono contenute in articoli sui due siti ennius, le ripetiamo, riunendo i concetti, cercando di esemplificare al massimo. Invito comunque gli interessati a leggersi almeno questi due articoli:

• "Operatori Numerici" - dove sono spiegati i simboli da usare per le operazioni tra i numeri, sul sito interfree, sezione "Primi passi".

• "Variabili e Tipi di dati"  - in questa stessa sezione, dove vengono spiegati in modo semplice "Variabili" e "Tipi di Dati".

Inoltre ricordo che nello sviluppo di calcoli, esiste una priorità nello svolgimento di più operazioni aritmetiche concatenate, e la sequenza è:

• prima vengono svolte le divisioni.

• poi le moltiplicazioni

• poi le somme/sottrazioni

• e comunque prima di tutto vengono svolte le operazioni inserite tra parentesi (rispettando comunque la sequenza sopra per le operazioni svolte al loro interno).

Bene, iniziamo quindi a vedere intanto come reperire, in vba, dei dati presenti in celle del foglio di lavoro; supponiamo di avere una situazione simile a questa:

In A1 abbiamo il valore 10000 e nella cella B1  30  : vogliamo calcolare lo sconto del 30% su 10000.  Rimane però da decidere cosa fare con lo sconto che otterremo; abbiamo due possibilità: usare il valore ottenuto per successivi calcoli sempre in vba, oppure inserire lo sconto ottenuto in una cella. Vediamo entrambe le soluzioni, in due varianti.

• per reperire i valori (in vba) è sufficiente usare i riferimenti alle celle che contengono i valori, quindi potremo usare la sintassi Range("A1") e Range("B1"), oppure la sintassi abbreviata in cui il riferimento è compreso tra parentesi quadre, così: [A1] e [B1]; abbiamo risparmiato di scrivere Range e non usiamo i doppi apici, ed Excel identifica comunque le due celle.

• Creeremo una macro che poi lanceremo dallo stesso foglio sul quale sono i numeri; in questo caso non è necessario, nelle istruzioni, definire il nome del foglio, in quanto Excel assume i riferimenti alle celle come riferimenti al foglio attivo. Se invece la macro la lanciamo da un'altro foglio, (come pure se vogliamo riferirci a celle che sono su un'altro foglio) dovremo indicare ad Excel su quale foglio sono le celle, usando la parola Sheets (con la "s", al plurale) seguita dal nome del foglio messo tra parentesi e doppi apici, seguito dal riferimento alla cella, così : Sheets("Foglio3").[A1] . Questa sintassi ci consente di mirare a celle che sono su fogli diversi dal foglio in quel momento attivo. Non sarebbe male abituarsi comunque ad usare sempre la sintassi che prevede il "nome" del foglio, eviteremo possibili errori futuri che qui non tratteremo.

Se noi facessimo l'operazione per lo sconto manualmente, noi eseguiremmo queste operazioni aritmetiche: 10000 moltiplicato 30, dividendo il prodotto per 100; lo stesso faremo in vba, ma usando al posto dei valori i riferimenti alle celle che li contengono, così:

• [A1] * [B1] / 100  - usiamo gli operatori asterisco * (moltiplica) e barra  / (dividi) posti tra i riferimenti alle celle, e il risultato sarà quindi 3000, cioè 10000 x 30 = 30000, diviso 100 = 3000, o meglio, visto che per prima viene svolta la divisione, avremo 30 (in B1) diviso 100 = 0,3 che moltiplicato per 10000 (in A1) restituisce 3000; in effetti, a conferma di quanto detto sulla priorità di svolgimento delle operazioni, l'istruzione giusta sarebbe:

• ( [A1] * [B1] ) / 100 - prima facciamo svolgere la moltiplicazione, inserendo i fattori tra parentesi tonde, e poi verrà eseguita la divisione per 100 del prodotto così ottenuto, che è la sequenza giusta da seguire.

Ora però bisogna dire al vba cosa fare del risultato dell'operazione, che al momento è tenuto in memoria, e qui decideremo se inviare direttamente in risultato ad una cella, usando il riferimento della cella a cui inviarlo, così:

[C1] = ( [A1] * [B1] ) / 100 - cosa abbiamo fatto? abbiamo destinato, usando il riferimento alla cella C1, questa cella come contenitore del risultato del calcolo

oppure tenerlo in memoria, ma, assegnandolo ad una variabile; potremo in questo modo usare la variabile sia per successivi calcoli, sia per inviare la variabile stessa ad una cella. Usiamo quindi una parola, "Sconto", come nome con cui chiamare la variabile, e la nostra istruzione diventerà:

	Sconto = ( [A1] * [B1] ) / 100   - cosa otteniamo? che ora la variabile Sconto sarà uguale al risultato del calcolo, cioè 3000. Potremo quindi inviare Sconto ad una cella, così:
	[C1] = Sconto  - ora avremo in C1 il valore 3000.

Tanto per concludere con questo esempio, ora simuliamo di svolgere lo stesso calcolo ma con valori posti su tre celle di tre diversi fogli; usando il nome del foglio per identificare la cella, possiamo eseguire calcoli senza necessariamente selezionare o attivare i fogli interessati; questo vuol dire che potremo anche lanciare la macro da un quarto foglio, esempio:

• sul Foglio 1, nella cella A1 avremo 10000.

• sul Foglio 2, nella cella B1 avremo il valore dello sconto da applicare 30

• sul Foglio 3, nella cella C1 vorremo l'importo dello sconto

e queste le istruzioni:

• Sconto = ( Sheets("Foglio1").[A1] * Sheets("Foglio2").[B1] ) / 100

• Sheets("Foglio3").[C1] = Sconto

se avremo lanciato la macro dal Foglio 4, tornando al Foglio3 vedremo in C1 l'importo 3000. Prima di passare ad un'altro esempio di calcolo concatenato, è opportuno ricordare due cose:

• è sempre consigliabile "Dimensionare" una variabile (tramite l'istruzione Dim )

• è sempre consigliabile definire il "Tipo di Dato" che la variabile rappresenta specialmente quando si lavora con numeri. (interi, con decimali, valuta, ecc) (se leggete l'articolo "Variabili e Tipi di dati"  leggerete cosa sono)

• in alternativa ai due punti sopra è possibile usare una "Funzione di conversione del Tipo di Dati". (leggersi l'articolo "Conversioni tipi di dati" sull'altro sito interfree)

Vediamo comunque in cosa consiste usare i due modi:

• Con dimensionamento e definizione del tipo di dati come numero con decimali (Double)

• Dim Sconto As Double    'dimensionamento della variabile "Sconto" come "contenitore" di numeri con decimali

• Sconto = ( [A1] * [B1] ) / 100

• [C1] = Sconto

• ora con la funzione di conversione (del tipo di dati) per i numeri Double (CDbl è la funzione) :

• Sconto = ( CDbl( [A1] ) * CDbl( [B1] ) ) / 100

• [C1] = CDbl( Sconto )

Questo "definire" il tipo di dato diventa necessario perchè non sempre lavoriamo con numeri a cifra tonda (come 10000 op. 30), provate a considerare cosa succede se al posto di 10000 avessimo 11232, e al posto di uno sconto 30, avessimo 31,5: otterremo per forza di cose un numero con decimali come risultato, cioè 3538,08, dove avremo bisogno che anche i decimali figurino nel valore restituito.

Ora, parecchi problemini li risolviamo a volte con la giusta impostazione del "formato cella" nel foglio di lavoro, ma la decisione di una nostra scelta nel decidere il "formato cella", può influenzare poi la costruzione dei conteggi in vba. Prendiamo ad esempio la tabella vista all'inizio, e supponiamo che per la cella B1, quella che contiene il valore di sconto, anzichè a "formato cella :  standard" la si sia impostata a "formato cella : percentuale", quando noi scriveremo il valore 30, Excel automaticamente lo considera un valore percentuale e gli assegna il simbolo del percento ( % ) e come tale lo considera. Questo formato è già equivalente a 30/100, quindi se useremo il riferimento a B1, il valore passato NON necessita di ulteriore divisione per 100: vedremo sul foglio questo:

e noi nel conteggio in vba dovremo tenere conto di questo fatto ed usare questa istruzione (ripeto solo il primo esempio):

• [C1] = [A1] * [B1]  anzi, meglio :

• [C1] = CDbl( [A1] ) * CDbl( [B1] )  (meglio usare la funzione di conversione CDbl)

abbiamo tolto la divisione per 100 ( / 100 ), ma in C1 ci sarà 3000, perchè in questo caso in B1 è come se ci fosse 0,3 e quindi moltiplichiamo 10000 x 0,3 che restituisce 3000.

Ora vediamo invece esempi di calcoli un pochino più complessi (ma comunque semplici) : anzichè nella cella C1 ottenere lo sconto, vogliamo ottenere la cifra netta di sconto, quindi 10000 - 3000 = 7000; dobbiamo comunque effettuare il calcolo della percentuale di sconto per ottenere l'importo dello sconto da sottrarre all'importo iniziale:

anche in questo caso possiamo seguire istruzioni diverse, primo esempio:

• [C1] = [A1] - ( ( [A1] * [B1] ) / 100 )  - se avremo usato in B1 il solo valore 30 senza percentuale

• [C1] = [A1] - ( [A1] * [B1] )   - se avremo usato in B1 il  valore 30%  (in percentuale)

secondo esempio con uso di una variabile "Sconto" (senza dimensionamento, ma con funzione CDbl)

• Sconto =  ( CDbl( [A1] ) * CDbl( [B1] ) ) / 100  - se avremo usato in B1 il solo valore 30 senza percentuale

• [C1] = [A1] - Sconto

oppure se avremo usato in B1 il  valore 30%  (in percentuale)

• Sconto =  CDbl( [A1] ) * CDbl( [B1] )

• [C1] = [A1] - Sconto

ultimo esempio: supponiamo di voler applicare alla cifra 10000 lo sconto 30% e in più uno sconto extra del 10% (attenzione, sto usando i valori sconto con formato cella a "percentuale"). Useremo quindi una cella  come contenitore del valore di sconto extra, la C1 ed il risultato lo vorremo in D1

In genere questo tipo di extra sconti non si calcola sulla cifra iniziale, ma sulla cifra netta del primo sconto; anche in questo caso possiamo scegliere varie forme di calcolo, io vi presento questa:

• Sconto =  CDbl( [A1] ) * CDbl( [B1] )  - prendiamo con Sconto l'importo dello sconto 30% su 10000 (3000)

• Extra = ( CDbl( [A1] ) - Sconto ) * CDbl( [C1] )  - con Extra otteniamo l'importo delle sconto 10% (ora in C1) calcolato sulla cifra 10000 meno l'importo del primo sconto; cioè calcoliamo il 10% su 7000 = 700: eseguiamo prima il calcolo (sottrazione) tra parentesi per ottenere 7000, e lo moltiplichiamo per 10% = 700.

• [D1] = CDbl( [A1] ) - ( Sconto + Extra ) - ora sommiamo i due importi sconto, inserendo il calcolo tra parentesi perchè deve essere svolto prima della sottrazione, e la somma ottenuta la sottraiamo al valore in A1 (10000); in D1 avremo 6300 che è la cifra netta scontata.

Ricordarsi di porre attenzione alle parentesi (tante aperte ne vogliono altrettante chiuse). E' pure evidente che esistono moltissime necessità d'impostazione calcoli (matematici, non renali o biliari), ed è impensabile esemplificarli tutti, aiutatevi quindi con la guida in linea per cercare soluzioni ai vostri problemi. Comunque due altri esempi li possiamo vedere:

Estrarre la radice quadrata di un numero: si usa la funzione Sqr(valore) - se vogliamo la radice quadrata del valore che è in A1 (10000), possiamo usare una variabile "Ris" che conterrà il risultato, così:

• Ris = Sqr( [A1] )  - la funzione Sqr restituisce sempre un valore Double, quindi non è necessario usare una funzione di conversione o il dimensionamento. e Ris sarà uguale a 100.

Elevare a potenza un numero : si usa l'operatore ^ posto tra il numero da elevare a potenza e il suo esponente; sia numero che esponente li possiamo anche reperire da valori posti nelle celle, ed useremo quindi i riferimenti alle celle così:

• Ris = [A1] ^ [B1]  - e se in A1 abbiamo 5 e in B1 abbiamo 4, Ris sarà uguale a 625 - (il risultato è in genere un valore Double o un valore Variant che include un valore Double)

I visitatori pratici di vba riterranno questo articolo superfluo, ma, credetemi, sono in molti a non aver chiaro come svolgere calcoli. Spero di esser stato loro d'aiuto.

Buon lavoro a tutti.

prelevato sul sito www.ennius.altervista.org