Questo tutorial vi insegnerà come creare formule di cella utilizzando VBA.
Formule in VBA
Utilizzando VBA, è possibile scrivere formule direttamente in Intervalli o celle in Excel. Assomiglia a questo:
Sub Formula_Example() 'Assign a hard-coded formula to a single cell Range("b3").Formula = "=b1+b2" 'Assign a flexible formula to a range of cells Range("d1:d100").FormulaR1C1 = "=RC2+RC3"End Sub
Ci sono due proprietà dell’intervallo che devi sapere:
- .Formula-Crea una formula esatta (riferimenti di cella hard-coded). Buono per aggiungere una formula a una singola cella.
- .FormulaR1C1-Crea una formula flessibile. Ottimo per aggiungere formule a un intervallo di celle in cui i riferimenti di cella dovrebbero cambiare.
Per formule semplici, va bene usare il .Proprietà Formula. Tuttavia, per tutto il resto, si consiglia di utilizzare il registratore Macro
Registratore macro e formule di cella
Il registratore Macro è il nostro go-to strumento per la scrittura di formule di cella con VBA. Puoi semplicemente:
- Avviare la registrazione
- Digitare la formula (con riferimenti relativi/assoluti secondo necessità) nella cella& premere invio
- Interrompere la registrazione
- Aprire VBA e rivedere la formula, adattandola secondo necessità e copiando+incollando il codice dove necessario.
Trovo che sia molto più facile inserire una formula in una cella piuttosto che digitare la formula corrispondente in VBA.
Notare un paio di cose:
- Il registratore Macro utilizzerà sempre il .FormulaR1C1 proprietà
- Il registratore di macro riconosce i riferimenti di cella assoluti rispetto a quelli relativi
VBA FormulaR1C1 Proprietà
La proprietà FormulaR1C1 utilizza il riferimento di cella in stile R1C1 (al contrario dello stile A1 standard che
Ecco alcuni esempi:
Sub FormulaR1C1_Examples() 'Reference D5 (Absolute) '=$D$5 Range("a1").FormulaR1C1 = "=R5C4" 'Reference D5 (Relative) from cell A1 '=D5 Range("a1").FormulaR1C1 = "=RC" 'Reference D5 (Absolute Row, Relative Column) from cell A1 '=D$5 Range("a1").FormulaR1C1 = "=R5C" 'Reference D5 (Relative Row, Absolute Column) from cell A1 '=$D5 Range("a1").FormulaR1C1 = "=RC4"End Sub
Si noti che il riferimento alle celle in stile R1C1 consente di impostare riferimenti assoluti o relativi.
Riferimenti assoluti
Nella notazione standard A1 un riferimento assoluto ha il seguente aspetto: “=C C 2 2”. Nella notazione R1C1 assomiglia a questo: “= R2C3”.
Per creare un riferimento di cella assoluto usando il tipo di stile R1C1:
- R + Numero di riga
- C + Numero di colonna
Esempio: R2C3 rappresenterebbe la cella C C 2 2 (C è la 3a colonna).
'Reference D5 (Absolute) '=$D$5 Range("a1").FormulaR1C1 = "=R5C4"
Riferimenti relativi
I riferimenti relativi alle celle sono riferimenti di celle che “si muovono” quando la formula viene spostata.
Nella notazione standard A1 assomigliano a questo: “= C2”. Nella notazione R1C1, si utilizzano parentesi per compensare il riferimento della cella dalla cella corrente.
Esempio: Inserendo la formula “= RC ” nella cella B3 si farebbe riferimento alla cella D4 (la cella 1 riga sotto e 1 colonna a destra della cella della formula).
Usa numeri negativi per fare riferimento alle celle sopra o a sinistra della cella corrente.
'Reference D5 (Relative) from cell A1 '=D5 Range("a1").FormulaR1C1 = "=RC"
Riferimenti misti
I riferimenti di cella possono essere parzialmente relativi e parzialmente assoluti. Esempio:
'Reference D5 (Relative Row, Absolute Column) from cell A1 '=$D5 Range("a1").FormulaR1C1 = "=RC4"
Proprietà formula VBA
Quando si impostano le formule con il .Proprietà Formula userai sempre la notazione in stile A1. Si immette la formula proprio come si farebbe in una cella Excel, tranne circondato da citazioni:
'Assign a hard-coded formula to a single cell Range("b3").Formula = "=b1+b2"
VBA Formula Tips
Formula con variabile
Quando si lavora con Formule in VBA, è molto comune voler utilizzare variabili all’interno delle formule di cella. Per utilizzare le variabili, si utilizza & per combinare le variabili con il resto della stringa della formula. Esempio:
Sub Formula_Variable() Dim colNum As Long colNum = 4 Range("a1").FormulaR1C1 = "=R1C" & colNum & "+R2C" & colNumEnd Sub
Citazioni formula
Se è necessario aggiungere una citazione (“) all’interno di una formula, inserire la citazione due volte (“”):
Sub Macro2() Range("B3").FormulaR1C1 = "=TEXT(RC,""mm/dd/yyyy"")"End Sub
Una singola citazione (“) indica a VBA la fine di una stringa di testo. Mentre una doppia citazione (“”) viene trattata come una citazione all’interno della stringa di testo.
Allo stesso modo, usa 3 virgolette (“””) per circondare una stringa con una virgoletta (“)
MsgBox """Use 3 to surround a string with quotes"""' This will print <"Use 3 to surround a string with quotes"> immediate window
Assegna la formula della cella alla variabile stringa
Possiamo leggere la formula in una data cella o intervallo e assegnarla a una variabile:
'Assign Cell Formula to VariableDim strFormula as StringstrFormula = Range("B1").Formula
Diversi Modi per Aggiungere Formule in una Cella
Ecco alcuni esempi per l’assegnazione di una formula in una cella:
- Assegnare Direttamente la Formula
- Definire una Variabile di tipo Stringa Contenente la Formula
- Utilizzare le Variabili per Creare la Formula
Sub MoreFormulaExamples ()' Alternate ways to add SUM formula' to cell B1' Dim strFormula as String Dim cell as Range dim fromRow as Range, toRow as Range Set cell = Range("B1") ' Directly assigning a String cell.Formula = "=SUM(A1:A10)" ' Storing string to a variable ' and assigning to "Formula" property strFormula = "=SUM(A1:A10)" cell.Formula = strFormula ' Using variables to build a string ' and assigning it to "Formula" property fromRow = 1 toRow = 10 strFormula = "=SUM(A" & fromValue & ":A" & toValue & ") cell.Formula = strFormulaEnd Sub
Aggiorna Formule
Come un promemoria, per aggiornare le formule, è possibile utilizzare l’Calcolare il comando:
Calculate
Per aggiornare singola formula, l’intervallo o l’intero foglio .Calcola invece:
Sheets("Sheet1").Range("a1:a10").Calculate