Utilizzo delle funzioni del foglio di lavoro di Microsoft Excel in Visual Basic.

Utilizzo dell'oggetto : WorksheetFunction

Nei nostri lavori ci troviamo spesso a dover "trasformare" formule e funzioni del foglio di lavoro in codice vba. In genere ci affidiamo al "Registratore di macro". Attiviamo il registratore, compiamo i passi necessari (selezione di una cella e inserimento formule e/o funzioni con indicazione dei riferimenti come argomenti per ciò che vogliamo sia fatto, ecc. ) e poi stoppiamo il registratore. A questo punto la trasformazione in codice di ciò che abbiamo fatto, ce l'ha scritta il registratore in un modulo: andiamo a vedere cosa ha scritto, e diventiamo matti perchè il codice è stato compilato, per quanto riguarda la formula impiegata, in puro stile R1C1. E cominciano i nostri drammi a rinvenirsi, se vogliamo modificare dei riferimenti, in quella babele di parentesi quadre, tonde, R, C, numeri col meno davanti, ecc.ecc. Gli smaliziati, avranno sicuramente imparato a trasformarsi una formula da stile R1C1 in stile A1, e conseguentemente a poter modificare, adattare i riferimenti come più gli aggrada.

Esiste un'altra strada per utilizzare le formule e le funzioni del foglio di lavoro  nel codice vba. Una strada che è più simile come concetto di sintassi, al sistema che usiamo sul foglio di lavoro. Questa strada passa attraverso l'utilizzo dell'oggetto WorksheetFunction.

WorksheetFunction rende disponibili in Visual Basic le funzioni del foglio di lavoro.

Alcune funzioni del foglio di lavoro sono superflue in vba. La funzione Concatena (Concatenate per il vba), ad esempio, non è necessaria poiché in vba è possibile utilizzare l'operatore & per concatenare più valori di testo. NON tutte le funzioni del foglio di lavoro, sono disponibili in vba. (vedi elenco).

(Dalla guida in linea:
Se viene utilizzata una funzione del foglio di lavoro che richiede un riferimento di intervallo quale argomento, è necessario specificare un oggetto Range.  Tuttavia, in una routine Visual Basic è possibile specificare un oggetto Range per ottenere lo stesso risultato.
Nota: Le funzioni Visual Basic non utilizzano il qualificatore WorksheetFunction. Non è detto che una funzione Visual Basic con lo stesso nome di una funzione Microsoft Excel operi in modo identico. Ad esempio, Application.WorksheetFunction.Log e Log restituiranno valori diversi).

Vediamo di fare qualche esempio che illustri meglio quanto detto a inizio pagina. Siamo su un foglio di lavoro e supponiamo di volere il totale di tutti i valori legati ad una condizione. Per esempio in A1:A10 abbiamo dei valori, in B1:B10 abbiamo dei nomi: vogliamo sapere quanto è il totale dei valori relativi ad uno dei nomi (giona per esempio) e useremo C1 per ottenere il risultato; in C1 quindi inseriremo la funzione =SOMMA.SE(B1:B10;"giona";A1:A10) che tradotta in "pellegrinese", vuol dire : per ogni nome "giona" che trovi in B1:B10, somma il valore che trovi nella cella accanto, nel range A1:A10. Se usiamo il registratore di macro per ottenere la corrispondente istruzione Somma.Se tradotta in vba, otterremmo la seguente istruzione:

Range("C1").FormulaR1C1 = "=SUMIF(RC[-1]:R[9]C[-1],""giona"",RC[-2]:R[9]C[-2])"

e qui gli amanti dei geroglifici andrebbero a nozze, ma per noi, poveri mortali, sarebbe un supplizio voler cambiare uno o più riferimenti. Vediamo come è possibile trasformarci l'istruzione sopra in modo che ci si possa capire qualcosa. Intanto modifichiamo la parola FormulaR1C1 in una più semplice Formula, poi, ricordandoci la sintassi dell'istruzione messa sul foglio di lavoro dopo SOMMA.SE, la ripeteremo effettuando una piccola modifica, e la sostituiremo a tutto ciò che nell'istruzione sopra è tra parentesi, così:

Range("C1").Formula = "=SUMIF(B1:B10,""giona"",A1:A10)"

La modifica consiste nell'aver messo altri due doppi apici alla parola, necessari perchè in una formula vba, a differenza di quanto accade sul foglio di lavoro, per indicare due doppi apice se ne devono usare quattro. Oppure usare una variabile, che rende l'istruzione più flessibile, in quanto ci consentirebbe di poter scegliere di il nome di cui vogliamo il totale. Per questo abbiamo bisogno di usare una cella del foglio di lavoro, dove di volta in volta scriveremo il nome. Assegneremo questa cella alla variabile e sostituiremo nell'istruzione il nome con i 4 doppi apici, con la variabile senza apici, così :

X = Range("D1") 'una cella a caso dove scriveremo il nome di cui vogliamo i totali
Range("C1").Formula = "=SUMIF(B1:B10,X,A1:A10)"

E ora arriviamo al "piatto forte": all'impiego di WorksheetFunction, che secondo me rappresenta il modo migliore di non impazzire con la sintassi. Vediamo la stessa istruzione compilata usando WorksheetFunction. Visto quanto detto dalla Guida il linea (più sopra riportata: riferimenti come argomenti), useremo l'istruzione Set per restituire i Range di celle interessate:

Sub summase()
Dim myRange, tuoRange As Range   'questa riga si può anche omettere
X = Range("D1") 'una cella a caso dove scriveremo il nome di cui vogliamo i totali
Set myRange = Worksheets("foglio1").Range("A1:A10")
Set tuorange = Worksheets("foglio1").Range("B1:B10")
Range("C1") = WorksheetFunction.SumIf(tuorange, X, myRange)
End Sub

Decisamente più comprensibile e meglio interpretabile. Praticamente (a parte un pò di codice in più) l'istruzione è la stessa usata sul foglio(come impostazione). Da notare una cosa: mentre con l'istruzione a base formula, ciò che viene riportato in C1 è il risultato e la formula  (visibile nella "barra della formula"), con l'istruzione sopra vedremo SOLO il risultato. Questo tornerà sicuramente utile a tutti coloro che, volendo nascondere le formule, in questa maniera hanno la formula non visibile senza dover scegliere altre strade. (protezione foglio e quantaltro). Vediamo le differenze:

Ancora un esempio per esaltare la semplicità di questo metodo, una istruzione per ottenere la somma di tutto un range di celle, non credo abbia bisogno di commenti:

Sub SommaMia()
'Dim myRange As Range  'questa riga si può eliminare
Set myRange = Worksheets("Foglio1").Range("A1:C10")
Range("D1") = WorksheetFunction.Sum(myRange)
End Sub

 

ELENCO FUNZIONI DEL FOGLIO DI LAVORO DISPONIBILI IN VBA

ritorna

ritorna