In deze zelfstudie leert u hoe u celformules maakt met VBA.
Formules in VBA
Met VBA kunt u formules rechtstreeks naar bereiken of cellen in Excel schrijven. Het ziet er zo uit:
| 123456789 | Subformule_voorbeeld()'Wijs een hardgecodeerde formule toe aan een enkele cel'Bereik ("b3").Formule = "=b1+b2"'Wijs een flexibele formule toe aan een celbereik'Bereik ("d1:d100").FormuleR1C1 = "=RC2+RC3"Einde sub |
Er zijn twee Range-eigenschappen die u moet kennen:
- .Formule - Creëert een exacte formule (hardgecodeerde celverwijzingen). Goed voor het toevoegen van een formule aan een enkele cel.
- .FormuleR1C1 - Creëert een flexibele formule. Goed voor het toevoegen van formules aan een celbereik waar celverwijzingen zouden moeten veranderen.
Voor eenvoudige formules is het prima om de .Formula Property te gebruiken. Voor al het andere raden we echter aan om de Macrorecorder…
Macrorecorder en celformules
De Macro Recorder is onze tool voor het schrijven van celformules met VBA. U kunt eenvoudig:
- Beginnen met opnemen
- Typ de formule (met relatieve / absolute verwijzingen indien nodig) in de cel en druk op enter
- Stop met opnemen
- Open VBA en bekijk de formule, pas waar nodig aan en kopieer en plak de code waar nodig.
ik vind het veel makkelijker om een formule in een cel in te voeren dan om de corresponderende formule in VBA te typen.
Let op een paar dingen:
- De Macro Recorder gebruikt altijd de eigenschap .FormulaR1C1
- De macrorecorder herkent absolute versus relatieve celreferenties
VBA-formuleR1C1-eigenschap
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:
| 12345678910111213141516171819 | SubformuleR1C1_Examples()'Referentie D5 (absoluut)'=$D$5Bereik ("a1").FormuleR1C1 = "=R5C4"'Referentie D5 (Relatief) uit cel A1''=D5Bereik ("a1").FormuleR1C1 = "=R[4]C[3]"'Referentie D5 (Absolute rij, relatieve kolom) uit cel A1''=D$5Bereik ("a1").FormuleR1C1 = "=R5C[3]"'Referentie D5 (relatieve rij, absolute kolom) uit cel A1''=$D5Bereik ("a1").FormuleR1C1 = "=R[4]C4"Einde sub |
Merk op dat u met celverwijzingen in R1C1-stijl absolute of relatieve verwijzingen kunt instellen.
Absolute referenties
In standaard A1-notatie ziet een absolute referentie er als volgt uit: “=$C$2”. In R1C1 notatie ziet het er zo uit: “=R2C3”.
Om een Absolute celverwijzing te maken met het R1C1-type type:
- R + Rijnummer
- C + Kolomnummer
Voorbeeld: R2C3 zou cel $C$2 vertegenwoordigen (C is de 3e kolom).
| 123 | 'Referentie D5 (absoluut)'=$D$5Bereik ("a1").FormuleR1C1 = "=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 de R1C1-notatie gebruikt u haakjes [] om de celverwijzing van de huidige cel te verschuiven.
Voorbeeld: het invoeren van formule "=R[1]C[1]" in cel B3 zou verwijzen naar cel D4 (de cel 1 rij eronder en 1 kolom rechts van de formulecel).
Gebruik negatieve getallen om naar cellen boven of links van de huidige cel te verwijzen.
| 123 | 'Referentie D5 (Relatief) uit cel A1''=D5Bereik ("a1").FormuleR1C1 = "=R[4]C[3]" |
Gemengde referenties
Celverwijzingen kunnen gedeeltelijk relatief en gedeeltelijk absoluut zijn. Voorbeeld:
| 123 | 'Referentie D5 (relatieve rij, absolute kolom) uit cel A1''=$D5Bereik ("a1").FormuleR1C1 = "=R[4]C4" |
VBA-formule-eigenschap
Bij het instellen van formules met de .Formule-eigenschap u gebruikt altijd de notatie in A1-stijl. U voert de formule in zoals u zou doen in een Excel-cel, behalve tussen aanhalingstekens:
| 12 | 'Wijs een hardgecodeerde formule toe aan een enkele cel'Bereik ("b3").Formule = "=b1+b2" |
VBA-formuletips
Formule met variabele
Wanneer u met formules in VBA werkt, is het heel gebruikelijk om variabelen binnen de celformules te willen gebruiken. Om variabelen te gebruiken, gebruikt u & om de variabelen te combineren met de rest van de formuletekenreeks. Voorbeeld:
| 1234567 | Subformule_Variabele()Dim colNum As LongcolNum = 4Bereik ("a1").FormuleR1C1 = "=R1C" & colNum & "+R2C" & colNumEinde sub |
Formule Citaten
Als u een offerte (") binnen een formule moet toevoegen, voert u de offerte twee keer in (""):
| 123 | Submacro2()Bereik ("B3").FormuleR1C1 = "=TEKST(RC[-1],""mm/dd/jjjj"")"Einde sub |
Een enkel aanhalingsteken (") betekent voor VBA het einde van een tekstreeks. Terwijl een dubbel aanhalingsteken ("") wordt behandeld als een aanhalingsteken in de tekstreeks.
Gebruik op dezelfde manier 3 aanhalingstekens (“””) om een string te omringen met een aanhalingsteken (“)
| 12 | MsgBox """Gebruik 3 om een string met aanhalingstekens te omringen"""' Dit zal onmiddellijk een venster afdrukken |
Celformule toewijzen aan tekenreeksvariabele
We kunnen de formule in een gegeven cel of bereik lezen en toewijzen aan een stringvariabele:
| 123 | 'Wijs celformule toe aan variabele'Dim strFormula as StringstrFormula = Bereik ("B1").Formule |
Verschillende manieren om formules aan een cel toe te voegen
Hier volgen nog enkele voorbeelden voor het toewijzen van een formule aan een cel:
- Formule direct toewijzen
- Definieer een tekenreeksvariabele die de formule bevat
- Variabelen gebruiken om formule te maken
| 12345678910111213141516171819202122232425 | Sub MeerFormuleVoorbeelden ()' Alternatieve manieren om de SUM-formule toe te voegen' naar cel B1'Dim strFormula as StringDim cel als bereikdim van rij als bereik, naar rij als bereikCel instellen = bereik ("B1")' Direct een string toewijzencel.Formule = "=SOM(A1:A10)"' String opslaan in een variabele' en toewijzen aan de eigenschap "Formule"strFormula = "=SOM(A1:A10)"cel.Formule = strFormule' Variabelen gebruiken om een string te bouwen' en toewijzen aan de eigenschap "Formule"vanRij = 1toRij = 10strFormula = "=SOM(A" & vanWaarde & ":A" & totWaarde & ")cel.Formule = strFormuleEinde sub |
Formules vernieuwen
Ter herinnering: om formules te vernieuwen, kunt u de opdracht Berekenen gebruiken:
| 1 | Berekenen |
Gebruik in plaats daarvan .Calculate om een enkele formule, bereik of volledig werkblad te vernieuwen:
| 1 | Spreadsheets ("Blad1"). Bereik ("a1:a10"). Berekenen |
