denna handledning lär dig hur du skapar cellformler med VBA.
formler i VBA
med VBA kan du skriva formler direkt till områden eller celler i Excel. Det ser ut så här:
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
det finns två Områdesegenskaper du behöver veta:
- .Formel-skapar en exakt formel (hårdkodade cellreferenser). Bra för att lägga till en formel i en enda cell.
- .FormulaR1C1-skapar en flexibel formel. Bra för att lägga till formler i ett cellområde där cellreferenser bör ändras.
för enkla formler, det är bra att använda .Formel Egendom. Men för allt annat rekommenderar vi att du använder makroinspelaren…
Makroinspelare och cellformler
makroinspelaren är vårt go-to-verktyg för att skriva cellformler med VBA. Du kan helt enkelt:
- börja spela in
- skriv formeln (med relativa/absoluta referenser efter behov) i cellen& tryck på enter
- stoppa inspelningen
- öppna VBA och granska formeln, anpassa efter behov och kopiera+klistra in koden där det behövs.
Jag tycker att det är mycket lättare att ange en formel i en cell än att skriva motsvarande formel i VBA.
Lägg märke till ett par saker:
- makroinspelaren kommer alltid att använda .Egenskapen FormulaR1C1
- makroinspelaren känner igen absoluta kontra relativa cellreferenser
VBA FormulaR1C1-egenskapen
egenskapen FormulaR1C1 använder cellreferenser i R1C1-stil (i motsats till standard A1-stil som du är van vid att se i Excel).
här är några exempel:
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
Lägg märke till att cellreferenser i R1C1-stil låter dig ställa in absoluta eller relativa referenser.
absoluta referenser
i standard A1-notation ser en absolut referens ut så här:”=$C$2″. I R1C1-notationen ser det ut så här:”=R2C3″.
för att skapa en absolut cellreferens med R1C1-stil typ:
- R + radnummer
- C + kolumnnummer
exempel: R2C3 skulle representera cell $C$2 (C är den 3: e kolumnen).
'Reference D5 (Absolute) '=$D$5 Range("a1").FormulaR1C1 = "=R5C4"
relativa referenser
relativa cellreferenser är cellreferenser som ”rör sig” när formeln flyttas.
i standard A1-notation ser de ut så här:” = C2″. I R1C1-notation använder du parenteser för att kompensera cellreferensen från den aktuella cellen.
exempel: Att ange formel” =RC ” i cell B3 skulle referera till cell D4 (cell 1-raden nedan och 1-kolumnen 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.
'Reference D5 (Relative) from cell A1 '=D5 Range("a1").FormulaR1C1 = "=RC"
blandade referenser
cellreferenser kan vara delvis relativa och delvis absoluta. Exempel:
'Reference D5 (Relative Row, Absolute Column) from cell A1 '=$D5 Range("a1").FormulaR1C1 = "=RC4"
VBA Formula Property
När du ställer in formler med .Formel egenskap du kommer alltid att använda A1-stil notation. Du anger formeln precis som i en Excel-cell, utom omgiven av citat:
'Assign a hard-coded formula to a single cell Range("b3").Formula = "=b1+b2"
VBA Formeltips
formel med variabel
När du arbetar med formler i VBA är det mycket vanligt att 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:
Sub Formula_Variable() Dim colNum As Long colNum = 4 Range("a1").FormulaR1C1 = "=R1C" & colNum & "+R2C" & colNumEnd Sub
formel citat
om du behöver lägga till en offert (”) i en formel, ange citatet två gånger (””):
Sub Macro2() Range("B3").FormulaR1C1 = "=TEXT(RC,""mm/dd/yyyy"")"End Sub
ett enda citat ( ” ) betyder för VBA slutet på en textsträng. Medan en dubbel citat ( ”” ) behandlas som ett citat i textsträngen.
På samma sätt använder du 3 citattecken ( ”””) för att omge en sträng med ett citattecken ( ” )
MsgBox """Use 3 to surround a string with quotes"""' This will print <"Use 3 to surround a string with quotes"> immediate window
tilldela Cellformel till Strängvariabel
Vi kan läsa formeln i en given cell eller intervall och tilldela den till en variabel:
'Assign Cell Formula to VariableDim strFormula as StringstrFormula = Range("B1").Formula
olika sätt att lägga till formler i en Cell
här är några fler exempel på hur man tilldelar en formel till en cell:
- direkt tilldela formel
- definiera en strängvariabel som innehåller formeln
- använd variabler för att skapa formel
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
uppdatera formler
som en påminnelse, för att uppdatera formler, kan du använda kommandot beräkna:
Calculate
för att uppdatera enstaka formel, intervall eller hela kalkylbladet .Beräkna istället:
Sheets("Sheet1").Range("a1:a10").Calculate