Deze tutorial leert u hoe u celformules kunt maken met behulp van VBA.
formules in VBA
met VBA kunt u formules rechtstreeks naar bereiken of cellen in Excel schrijven. Het ziet er zo uit:
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
Er zijn twee Range-eigenschappen die u moet weten:
- .Formule-creëert een exacte formule (harde-gecodeerde celverwijzingen). Goed voor het toevoegen van een formule aan een enkele cel.
- .FormulaR1C1-creëert een flexibele formule. Goed voor het toevoegen van formules aan een celbereik waar celverwijzingen moeten veranderen.
voor eenvoudige formules is het prima om de .Formule Eigenschap. Echter, voor al het andere, raden we aan om de Macro Recorder te gebruiken…
Macro Recorder en Celformules
De Macro Recorder is onze go-to tool voor het schrijven van celformules met VBA. U kunt eenvoudig:
- start recording
- Typ de formule (met relatieve / absolute referenties indien nodig) in de cel & druk op enter
- Stop recording
- Open VBA en bekijk de formule, aanpassen indien nodig en kopiëren+plakken van de code indien nodig.
Ik vind het veel gemakkelijker om een formule in een cel in te voeren dan om de bijbehorende formule in VBA te typen.
merk een paar dingen op:
- De Macro Recorder zal altijd de .Eigenschap FormulaR1C1
- de Macro-Recorder herkent Absolute vs.relatieve celverwijzingen
VBA eigenschap FormulaR1C1
De eigenschap FormulaR1C1 gebruikt celverwijzingen in R1C1-stijl (in tegenstelling tot de standaard A1-stijl die u gewend bent in Excel te zien).
Hier zijn enkele voorbeelden:
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
merk op dat de R1C1-stijl celverwijzing u toestaat om absolute of relatieve verwijzingen in te stellen.
Absolute referenties
in standaard A1 notatie ziet een absolute referentie er als volgt uit:”=$C$2″. In R1C1 notatie ziet het er als volgt uit: “=R2C3”.
om een absolute celverwijzing te maken met behulp van R1C1-stijl type:
- R + rijnummer
- C + kolomnummer
voorbeeld: R2C3 zou cel $C$2 vertegenwoordigen (C is de derde kolom).
'Reference D5 (Absolute) '=$D$5 Range("a1").FormulaR1C1 = "=R5C4"
relatieve referenties
relatieve celverwijzingen zijn celverwijzingen die” verplaatsen ” wanneer de formule wordt verplaatst.
in standaard A1 notatie zien ze er als volgt uit: “=C2”. In R1C1-notatie gebruikt u haakjes om de celverwijzing van de huidige cel te verschuiven.
voorbeeld: Het invoeren van de formule “= RC ” in cel B3 zou verwijzen naar cel D4 (de cel 1 rij hieronder en 1 kolom rechts van de formulecel).
gebruik negatieve getallen om te verwijzen naar cellen boven of links van de huidige cel.
'Reference D5 (Relative) from cell A1 '=D5 Range("a1").FormulaR1C1 = "=RC"
gemengde referenties
celverwijzingen kunnen gedeeltelijk relatief en gedeeltelijk absoluut zijn. Voorbeeld:
'Reference D5 (Relative Row, Absolute Column) from cell A1 '=$D5 Range("a1").FormulaR1C1 = "=RC4"
VBA-formule-eigenschap
bij het instellen van formules met de .Formule eigenschap u zult altijd gebruik maken van A1-stijl notatie. U voert de formule in zoals u in een Excel-cel zou doen, behalve omgeven door aanhalingstekens:
'Assign a hard-coded formula to a single cell Range("b3").Formula = "=b1+b2"
VBA Formuletips
formule met variabele
wanneer u met formules in VBA werkt, is het heel gebruikelijk om variabelen te gebruiken binnen de celformules. Om variabelen te gebruiken, gebruikt u & om de variabelen te combineren met de rest van de formulestring. Voorbeeld:
Sub Formula_Variable() Dim colNum As Long colNum = 4 Range("a1").FormulaR1C1 = "=R1C" & colNum & "+R2C" & colNumEnd Sub
formule aanhalingstekens
Als u een aanhalingsteken (“) binnen een formule wilt toevoegen, voert u de aanhalingsteken tweemaal in (“”):
Sub Macro2() Range("B3").FormulaR1C1 = "=TEXT(RC,""mm/dd/yyyy"")"End Sub
een enkele aanhalingsteken (“) betekent voor VBA het einde van een tekenreeks. Terwijl een dubbel citaat ( “” ) wordt behandeld als een citaat binnen de string van de tekst.
gebruik ook 3 aanhalingstekens (“””) om een tekenreeks te omringen met een aanhalingsteken (“)
MsgBox """Use 3 to surround a string with quotes"""' This will print <"Use 3 to surround a string with quotes"> immediate window
Celformule toewijzen aan Stringvariabele
We kunnen de formule in een bepaalde cel of Bereik lezen en toewijzen aan een variabele:
'Assign Cell Formula to VariableDim strFormula as StringstrFormula = Range("B1").Formula
Verschillende Manieren om Formules Toevoegen aan een Cel
Hier zijn een paar voorbeelden voor het toewijzen van een formule naar een cel:
- Direct Toewijzen Formule
- het Definiëren van een String-Variabele Met de Formule
- Variabelen Gebruiken voor het Maken van Formule
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
Vernieuwen Formules
Als een herinnering, voor het vernieuwen van formules, kunt u gebruik maken van het Berekenen van de opdracht:
Calculate
vernieuwen formule, assortiment, of het hele werkblad gebruiken .In plaats daarvan berekenen:
Sheets("Sheet1").Range("a1:a10").Calculate