Ez a bemutató megtanítja, hogyan kell létrehozni sejt képletek segítségével VBA.
képletek a VBA-ban
a VBA használatával képleteket írhat közvetlenül az Excel tartományaiba vagy celláiba. Ez így néz ki:
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
két tartomány tulajdonságot kell tudnia:
- .Képlet-pontos képletet hoz létre (kódolt cellahivatkozások). Jó képlet hozzáadásához egyetlen cellához.
- .FormulaR1C1-rugalmas képletet hoz létre. Jó képletek hozzáadásához olyan cellatartományhoz, ahol a cellahivatkozásoknak meg kell változniuk.
az egyszerű képletek, ez rendben van, hogy használja a .Képlet Tulajdonság. Minden máshoz azonban javasoljuk a makrófelvevő használatát…
makrófelvevő és Cellaképletek
a makrófelvevő a mi eszközünk a cellaképletek VBA-val történő írásához. Egyszerűen:
- Felvétel indítása
- írja be a képletet (szükség szerint relatív / abszolút hivatkozásokkal) a cellába & nyomja meg az enter billentyűt
- felvétel leállítása
- nyissa meg a VBA-t, és tekintse át a képletet, szükség szerint adaptálva és szükség esetén másolva+beillesztve a kódot.
sokkal könnyebb beírni egy képletet egy cellába, mint beírni a megfelelő képletet a VBA-ba.
vegye figyelembe néhány dolgot:
- a makró felvevő mindig a.FormulaR1C1 tulajdonság
- a makrórögzítő felismeri az abszolút vs. relatív cellahivatkozásokat
VBA FormulaR1C1 tulajdonság
a FormulaR1C1 tulajdonság R1C1-stílusú cellahivatkozást használ (szemben az Excelben megszokott A1-stílussal).
íme néhány példa:
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
figyeljük meg, hogy az R1C1-stílusú cella hivatkozás lehetővé teszi abszolút vagy relatív hivatkozások beállítását.
abszolút hivatkozások
a standard A1 jelölésben egy abszolút hivatkozás így néz ki: “=$c$2”. Az R1C1 jelölésben ez így néz ki: “=R2C3”.
abszolút cellahivatkozás létrehozása R1C1 stílusú típus használatával:
- R + sorszám
- C + oszlopszám
példa: az R2C3 a $C$2 cellát képviseli (C a 3.oszlop).
'Reference D5 (Absolute) '=$D$5 Range("a1").FormulaR1C1 = "=R5C4"
relatív hivatkozások
a relatív cellahivatkozások olyan cellahivatkozások, amelyek a képlet áthelyezésekor “mozognak”.
a szabványos A1 jelölésben így néznek ki: “=C2”. Az R1C1 jelölésben zárójelek segítségével eltolhatja a cellahivatkozást az aktuális cellától.
példa: A “=RC ” képlet beírása a B3 cellába a D4 cellára hivatkozna (az 1.sor alatti cellára és az 1. oszlopra a képletcellától jobbra).
használjon negatív számokat az aktuális cella feletti vagy bal oldali cellákra való hivatkozáshoz.
'Reference D5 (Relative) from cell A1 '=D5 Range("a1").FormulaR1C1 = "=RC"
vegyes referenciák
a cellahivatkozások részben relatívak, részben abszolútak lehetnek. Példa:
'Reference D5 (Relative Row, Absolute Column) from cell A1 '=$D5 Range("a1").FormulaR1C1 = "=RC4"
VBA Formula tulajdonság
a képletek beállításakor a .Képlet tulajdonság mindig A1 stílusú jelölést fog használni. A képletet ugyanúgy írja be, mint egy Excel cellában, kivéve az idézeteket:
'Assign a hard-coded formula to a single cell Range("b3").Formula = "=b1+b2"
VBA képlet tippek
képlet
változóval amikor a VBA képletekkel dolgozik, nagyon gyakori, hogy változókat szeretne használni a cellaképletekben. A változók használatához a & használatával kombinálhatja a változókat a képlet többi karakterláncával. Példa:
Sub Formula_Variable() Dim colNum As Long colNum = 4 Range("a1").FormulaR1C1 = "=R1C" & colNum & "+R2C" & colNumEnd Sub
Képletajánlatok
Ha idézetet ( ” ) kell hozzáadnia egy képlethez, írja be kétszer az idézetet (“”):
Sub Macro2() Range("B3").FormulaR1C1 = "=TEXT(RC,""mm/dd/yyyy"")"End Sub
egyetlen idézet ( ” ) a VBA-nak egy szövegsorozat végét jelenti. Míg a kettős idézetet (“”) úgy kezelik, mint egy idézetet a szövegsoron belül.
hasonlóképpen használjon 3 idézőjelet (“””) egy karakterlánc idézőjellel (“)
MsgBox """Use 3 to surround a string with quotes"""' This will print <"Use 3 to surround a string with quotes"> immediate window
Cellaképlet hozzárendelése a karakterlánc változóhoz
elolvashatjuk a képletet egy adott cellában vagy tartományban, és hozzárendelhetjük egy változóhoz:
'Assign Cell Formula to VariableDim strFormula as StringstrFormula = Range("B1").Formula
a képletek cellához való hozzáadásának különböző módjai
íme néhány további példa arra, hogyan lehet képletet hozzárendelni egy cellához:
- közvetlenül hozzárendelni a képletet
- definiáljon egy Karakterláncváltozót, amely a
- képletet tartalmazza változók a képlet létrehozásához
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
képletek frissítése
emlékeztetőül, a képletek frissítéséhez használhatja a számítási parancsot:
Calculate
egyetlen képlet, tartomány vagy teljes munkalap használatának frissítéséhez .Számolja ki helyette:
Sheets("Sheet1").Range("a1:a10").Calculate