Questo tutorial ti insegnerà come creare formule di cella usando VBA.
Formule in VBA
Usando VBA, puoi scrivere formule direttamente su Intervalli o Celle in Excel. Sembra così:
| 123456789 | Sottoformula_Esempio()'Assegna una formula hardcoded a una singola cellaIntervallo ("b3"). Formula = "= b1+b2"'Assegna una formula flessibile a un intervallo di celleIntervallo("d1:d100").FormulaR1C1 = "=RC2+RC3"Fine sottotitolo |
Ci sono due proprietà Range che devi conoscere:
- .Formula - Crea una formula esatta (riferimenti di cella hardcoded). Ottimo per aggiungere una formula a una singola cella.
- .FormulaR1C1 - Crea una formula flessibile. Utile per aggiungere formule a un intervallo di celle in cui i riferimenti di cella dovrebbero cambiare.
Per le formule semplici, va bene utilizzare la proprietà .Formula. Tuttavia, per tutto il resto, ti consigliamo di utilizzare il Registratore di macro…
Registratore di macro e formule di cella
Il Macro Recorder è il nostro strumento di riferimento per scrivere formule di celle con VBA. Puoi semplicemente:
- Inizia a registrare
- Digita la formula (con riferimenti relativi / assoluti secondo necessità) nella cella e premi invio
- Interrompi registrazione
- Apri VBA e rivedi la formula, adattandola secondo necessità e copiando e 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.
Nota un paio di cose:
- Il registratore di macro utilizzerà sempre la proprietà .FormulaR1C1
- Il registratore di macro riconosce i riferimenti di cella assoluti e relativi
Proprietà VBA FormulaR1C1
La proprietà FormulaR1C1 utilizza il riferimento di cella in stile R1C1 (a differenza dello stile A1 standard che sei abituato a vedere in Excel).
Ecco alcuni esempi:
| 12345678910111213141516171819 | Sotto FormulaR1C1_Esempi()'Riferimento D5 (assoluto)'=$D$5Intervallo("a1").FormulaR1C1 = "=R5C4"'Riferimento D5 (relativo) dalla cella A1''=D5Intervallo("a1").FormulaR1C1 = "=R[4]C[3]""Riferimento D5 (riga assoluta, colonna relativa) dalla cella A1"'= D$ 5Intervallo("a1").FormulaR1C1 = "=R5C[3]""Riferimento D5 (riga relativa, colonna assoluta) dalla cella A1"'=$D5Intervallo("a1").FormulaR1C1 = "=R[4]C4"Fine sottotitolo |
Si noti che il riferimento di cella in stile R1C1 consente di impostare riferimenti assoluti o relativi.
Riferimenti Assoluti
Nella notazione A1 standard un riferimento assoluto ha il seguente aspetto: "=$C$2". Nella notazione R1C1 appare così: “=R2C3”.
Per creare un riferimento di cella assoluto utilizzando il tipo in stile R1C1:
- R + Numero riga
- C + Numero colonna
Esempio: R2C3 rappresenterebbe la cella $C$2 (C è la terza colonna).
| 123 | 'Riferimento D5 (assoluto)'=$D$5Intervallo("a1").FormulaR1C1 = "=R5C4" |
Riferimenti relativi
I riferimenti di cella relativi sono riferimenti di cella che si "spostano" quando viene spostata la formula.
Nella notazione standard A1 hanno questo aspetto: "= C2". Nella notazione R1C1, si utilizzano le parentesi [] per spostare il riferimento di cella dalla cella corrente.
Esempio: l'immissione della formula "=R[1]C[1]" nella cella B3 farebbe riferimento alla cella D4 (la cella 1 riga sotto e 1 colonna a destra della cella formula).
Usa numeri negativi per fare riferimento alle celle sopra oa sinistra della cella corrente.
| 123 | 'Riferimento D5 (relativo) dalla cella A1''=D5Intervallo("a1").FormulaR1C1 = "=R[4]C[3]" |
Riferimenti misti
I riferimenti di cella possono essere in parte relativi e in parte assoluti. Esempio:
| 123 | "Riferimento D5 (riga relativa, colonna assoluta) dalla cella A1"'=$D5Intervallo("a1").FormulaR1C1 = "=R[4]C4" |
Proprietà formula VBA
Quando si impostano formule con .Proprietà della formula utilizzerai sempre la notazione in stile A1. Inserisci la formula proprio come faresti in una cella di Excel, ma circondata da virgolette:
| 12 | 'Assegna una formula hardcoded a una singola cellaIntervallo("b3").Formula = "=b1+b2" |
Suggerimenti sulla formula VBA
Formula con variabile
Quando si lavora con le formule in VBA, è molto comune voler utilizzare le variabili all'interno delle formule delle celle. Per utilizzare le variabili, usi & per combinare le variabili con il resto della stringa della formula. Esempio:
| 1234567 | Sotto Formula_Variabile()Dim colNum As LongcolNum = 4Intervallo("a1").FormulaR1C1 = "=R1C" & colNum & "+R2C" & colNumFine sottotitolo |
Citazioni di formule
Se è necessario aggiungere una citazione (“) all'interno di una formula, inserire la citazione due volte (“”):
| 123 | Sottomacro2()Intervallo("B3").FormulaR1C1 = "=TESTO(RC[-1],""mm/gg/aaaa"")"Fine sottotitolo |
Una singola citazione (') significa per VBA la fine di una stringa di testo. Considerando che una doppia citazione ("") viene trattata come una citazione all'interno della stringa di testo.
Allo stesso modo, usa 3 virgolette (""") per racchiudere una stringa tra virgolette (")
| 12 | MsgBox """Usa 3 per racchiudere una stringa tra virgolette"""'Questo stamperà la finestra immediata |
Assegna la formula della cella alla variabile stringa
Possiamo leggere la formula in una determinata cella o intervallo e assegnarla a una variabile stringa:
| 123 | "Assegna formula cella a variabile"Dim strFormula come StringstrFormula = Range("B1").Formula |
Diversi modi per aggiungere formule a una cella
Ecco alcuni altri esempi su come assegnare una formula a una cella:
- Assegna direttamente la formula
- Definire una variabile stringa contenente la formula
- Usa variabili per creare formule
| 12345678910111213141516171819202122232425 | Sub MoreFormulaEsempi ()'Modi alternativi per aggiungere la formula SUM' alla cella B1'Dim strFormula as StringCella debole come intervallodim fromRow as Range, to Row as RangeImposta cella = Intervallo ("B1")' Assegnazione diretta di una stringacell.Formula = "=SOMMA(A1:A10)"' Memorizzazione di una stringa in una variabile' e assegnando alla proprietà "Formula"strFormula = "=SOMMA(A1:A10)"cell.Formula = strFormula'Uso delle variabili per costruire una stringa' e assegnandolo alla proprietà "Formula"da riga = 1toRow = 10strFormula = "=SOMMA(A" & daValore & ":A" & aValore & ")cell.Formula = strFormulaFine sottotitolo |
Aggiorna formule
Come promemoria, per aggiornare le formule, puoi utilizzare il comando Calcola:
| 1 | Calcolare |
Per aggiornare una singola formula, un intervallo o un intero foglio di lavoro, utilizzare invece .Calculate:
| 1 | Fogli("Foglio1").Intervallo("a1:a10").Calcola |
