Denna handledning lär dig hur du skapar cellformler med VBA.
Formler i VBA
Med VBA kan du skriva formler direkt till intervall eller celler i Excel. Det ser ut så här:
| 123456789 | Sub Formula_Example ()'Tilldela en hårdkodad formel till en enda cellOmråde ("b3"). Formel = "= b1+b2"'Tilldela en flexibel formel till ett cellintervallOmråde ("d1: d100"). FormulaR1C1 = "= RC2+RC3"Avsluta Sub |
Det finns två Range -egenskaper du behöver känna till:
- .Formel - Skapar en exakt formel (hårdkodade cellreferenser). Bra för att lägga till en formel till en enda cell.
- .FormulaR1C1 - Skapar en flexibel formel. Bra för att lägga till formler till ett cellområde där cellreferenser bör ändras.
För enkla formler är det bra att använda egenskapen .Formula. För allt annat rekommenderar vi att du använder Makroinspelare…
Makroinspelare och cellformler
Makroinspelaren är vårt verktyg för att skriva cellformler med VBA. Du kan helt enkelt:
- Börja filma
- Skriv in formeln (med relativa / absoluta referenser efter behov) i cellen och tryck på enter
- Sluta spela in
- Öppna VBA och granska formeln, anpassa efter behov och kopiera+klistra in koden där det behövs.
Jag finner det mycket enklare för att ange en formel i en cell än att skriva motsvarande formel i VBA.
Lägg märke till ett par saker:
- Makroinspelaren använder alltid egenskapen .FormulaR1C1
- Makroinspelaren känner igen absoluta vs relativa cellreferenser
VBA FormulaR1C1 egendom
Egenskapen FormulaR1C1 använder cellreferenser i R1C1-stil (i motsats till den vanliga A1-stilen du är van vid att se i Excel).
Här är några exempel:
| 12345678910111213141516171819 | Sub FormulaR1C1_Examples ()'Referens D5 (Absolut)'= $ D $ 5Område ("a1"). FormulaR1C1 = "= R5C4"'Referens D5 (relativ) från cell A1'= D5Område ("a1"). FormulaR1C1 = "= R [4] C [3]"'Referens D5 (Absolut rad, relativ kolumn) från cell A1'= D $ 5Område ("a1"). FormulaR1C1 = "= R5C [3]"'Referens D5 (relativ rad, absolut kolumn) från cell A1'= $ D5Område ("a1"). FormulaR1C1 = "= R [4] C4"Avsluta Sub |
Lägg märke till att cellreferensen i R1C1-stil låter dig ställa in absoluta eller relativa referenser.
Absoluta referenser
I standard A1 -notering ser en absolut referens ut så här: "= $ C $ 2". I R1C1 -notering ser det ut så här: “= R2C3”.
Så här skapar du en absolut cellreferens med R1C1-typ:
- R + radnummer
- C + Kolumnummer
Exempel: R2C3 skulle representera cell $ C $ 2 (C är den tredje kolumnen).
| 123 | 'Referens D5 (Absolut)'= $ D $ 5Område ("a1"). FormulaR1C1 = "= R5C4" |
Relativa referenser
Relativa cellreferenser är cellreferenser som "rör sig" när formeln flyttas.
I standard A1 -notering ser de ut så här: “= C2”. I R1C1 -notering använder du parenteser [] för att kompensera cellreferensen från den aktuella cellen.
Exempel: Ange formel "= R [1] C [1]" i cell B3 skulle referera till cell D4 (cell 1 rad nedan och 1 kolumn till höger om formelcellen).
Använd negativa tal för att referera till celler ovanför eller till vänster om den aktuella cellen.
| 123 | 'Referens D5 (relativ) från cell A1'= D5Område ("a1"). FormulaR1C1 = "= R [4] C [3]" |
Blandade referenser
Cellreferenser kan vara delvis relativa och delvis absoluta. Exempel:
| 123 | 'Referens D5 (relativ rad, absolut kolumn) från cell A1'= $ D5Område ("a1"). FormulaR1C1 = "= R [4] C4" |
VBA Formula Property
När du ställer in formler med.Formula Property du kommer alltid att använda A1-stil notation. Du anger formeln precis som i en Excel -cell, förutom omgiven av citat:
| 12 | 'Tilldela en hårdkodad formel till en enda cellOmråde ("b3"). Formel = "= b1+b2" |
VBA Formel Tips
Formel med variabel
När du arbetar med formler i VBA är det väldigt vanligt att du vill använda variabler i cellformlerna. För att använda variabler använder du & för att kombinera variablerna med resten av formelsträngen. Exempel:
| 1234567 | Sub Formula_Variable ()Dim colNum As LongkolNum = 4Område ("a1"). FormulaR1C1 = "= R1C" & colNum & "+R2C" & colNumAvsluta Sub |
Formelcitat
Om du behöver lägga till en offert (“) inom en formel, ange offerten två gånger (“ ”):
| 123 | Undermakro2 ()Område ("B3"). FormulaR1C1 = "= TEXT (RC [-1]," "mm/dd/åååå" ")"Avsluta Sub |
En enda citat (“) betyder för VBA slutet på en textsträng. Medan en dubbelcitat (””) behandlas som ett citat inom textsträngen.
Använd på samma sätt 3 citattecken (“” ”) för att omge en sträng med ett citattecken (“)
| 12 | MsgBox "" "Använd 3 för att omge en sträng med citat" ""'Detta kommer att skriva ut omedelbart fönster |
Tilldela cellformel till strängvariabel
Vi kan läsa formeln i en given cell eller intervall och tilldela den till en strängvariabel:
| 123 | 'Tilldela cellformeln till variabelDim strFormula som strängstrFormula = Range ("B1"). Formel |
Olika sätt att lägga till formler i en cell
Här är några fler exempel på hur du tilldelar en formel till en cell:
- Tilldela formeln direkt
- Definiera en strängvariabel som innehåller formeln
- Använd variabler för att skapa formel
| 12345678910111213141516171819202122232425 | Sub MoreFormulaExamples ()'Alternativa sätt att lägga till SUM -formel'till cell B1'Dim strFormula som strängDim cell som Rangedimma frånRad som räckvidd, tillRad som räckviddAnge cell = intervall ("B1")'Direkt tilldela en strängcell.Formula = "= SUMMA (A1: A10)"'Lagring av sträng till en variabel'och tilldelar egenskapen "Formula"strFormula = "= SUMMA (A1: A10)"cell.Formel = strFormel'Använda variabler för att bygga en sträng'och tilldela den till egenskapen "Formula"frånRad = 1toRow = 10strFormula = "= SUMMA (A" & fromValue & ": A" & toValue & ")cell.Formel = strFormelAvsluta Sub |
Uppdatera formler
Som en påminnelse, för att uppdatera formler, kan du använda kommandot Beräkna:
| 1 | Beräkna |
För att uppdatera enstaka formler, intervall eller hela kalkylblad använder du. Beräkna istället:
| 1 | Ark ("Ark1"). Område ("a1: a10"). Beräkna |
