Ez az oktatóanyag megtanítja, hogyan hozhat létre cellaképleteket VBA használatával.
Képletek a VBA -ban
A VBA használatával képleteket írhat közvetlenül az Excel tartományokba vagy cellákba. Ez így néz ki:
| 123456789 | Részképlet_Példa ()'Rendeljen hozzá egy kódolt képletet egyetlen celláhozTartomány ("b3"). Képlet = "= b1+b2"'Rendeljen rugalmas képletet egy sor celláhozTartomány ("d1: d100"). KépletR1C1 = "= RC2+RC3"End Sub |
Két tartománytulajdonságot kell tudnia:
- .Képlet - Pontos képletet hoz létre (kemény kódolású cellahivatkozások). Jó, ha képletet ad hozzá egyetlen cellához.
- .FormulaR1C1 - Rugalmas formulát hoz létre. Jó, ha képleteket ad hozzá egy sor cellához, ahol a cellahivatkozásoknak meg kell változniuk.
Egyszerű képletek esetén érdemes használni a .Formula tulajdonságot. Minden máshoz azonban javasoljuk a Makró felvevő…
Makró felvevő és cellaképletek
A Macro Recorder a mi eszközünk a cellás képletek írásához VBA-val. Egyszerűen:
- Felvétel megkezdése
- Írja be a képletet (szükség szerint relatív / abszolút hivatkozásokkal) a cellába, és nyomja meg az enter billentyűt
- A felvétel leállítása
- Nyissa meg a VBA -t, és tekintse át a képletet, szükség szerint kiigazítva, és szükség esetén másolja+illessze be a kódot.
Én úgy találom sokkal könnyebb képletet beírni egy cellába, mint a megfelelő képletet beírni a VBA -ba.
Vegyünk észre egy -két dolgot:
- A makró felvevő mindig a .FormulaR1C1 tulajdonságot fogja használni
- A makró felvevő felismeri az abszolút és a relatív cellahivatkozásokat
VBA FormulaR1C1 tulajdonság
A FormulaR1C1 tulajdonság R1C1 stílusú cellahivatkozást használ (szemben az A1-es szabványos stílussal, amelyet az Excelben szokott látni).
Íme néhány példa:
| 12345678910111213141516171819 | RészképletR1C1_Examples ()„D5 hivatkozás (abszolút)'= $ 5 D $Tartomány ("a1"). KépletR1C1 = "= R5C4"„D5 hivatkozás (relatív) az A1 cellából'= D5Tartomány ("a1"). KépletR1C1 = "= R [4] C [3]"„D5 referencia (abszolút sor, relatív oszlop) az A1 cellából'= 5 dollárTartomány ("a1"). KépletR1C1 = "= R5C [3]"„D5 referencia (relatív sor, abszolút oszlop) az A1 cellából'= D5 USDTartomány ("a1"). KépletR1C1 = "= R [4] C4"End Sub |
Vegye figyelembe, hogy az R1C1 stílusú cellahivatkozás lehetővé teszi abszolút vagy relatív hivatkozások beállítását.
Abszolút referenciák
A szabványos A1 jelölésben az 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 + Sor száma
- C + Oszlopszám
Példa: Az R2C3 a $ C $ 2 cellát jelenti (C a 3. oszlop).
| 123 | „D5 hivatkozás (abszolút)'= $ 5 D $Tartomány ("a1"). KépletR1C1 = "= R5C4" |
Relatív hivatkozások
A relatív cellahivatkozások olyan cellahivatkozások, amelyek a képlet mozgatásakor „mozognak”.
A szabványos A1 jelölésben így néznek ki: „= C2”. Az R1C1 jelölésben a zárójelek [] használatával eltolja a cellahivatkozást az aktuális cellától.
Példa: A (z) „= R [1] C [1]” képlet beírása a B3 cellába a D4 cellára hivatkozik (az 1. cella alatti sor és az oszlop a képletcellától jobbra).
Negatív számokkal hivatkozzon az aktuális cella fölötti vagy bal oldali cellákra.
| 123 | „D5 hivatkozás (relatív) az A1 cellából'= D5Tartomány ("a1"). KépletR1C1 = "= R [4] C [3]" |
Vegyes irodalom
A cellahivatkozások részben relatívak és részben abszolútak lehetnek. Példa:
| 123 | „D5 referencia (relatív sor, abszolút oszlop) az A1 cellából'= D5 USDTartomány ("a1"). KépletR1C1 = "= R [4] C4" |
VBA Formula Property
Amikor képleteket állít be a.Formula Property mindig A1-es stílusú jelölést fog használni. A képletet ugyanúgy írja be, mint egy Excel cellába, kivéve idézőjelekkel:
| 12 | 'Rendeljen hozzá egy kódolt képletet egyetlen celláhozTartomány ("b3"). Képlet = "= b1+b2" |
VBA Formula Tippek
Képlet változóval
Amikor a VBA képleteivel dolgozik, nagyon gyakori, hogy változókat akar használni a cellaképleteken belül. A változók használatához a & billentyűvel kombinálja a változókat a képlet karakterláncának többi részével. Példa:
| 1234567 | Részképlet_változó ()Dim colNum As LongcolNum = 4Tartomány ("a1"). KépletR1C1 = "= R1C" & colNum & "+R2C" & colNumEnd Sub |
Képlet Idézetek
Ha idézetet („) kell hozzáadnia a képlethez, írja be kétszer az idézetet („ ”):
| 123 | Makro2 ()Tartomány ("B3"). KépletR1C1 = "= TEXT (RC [-1]," "mm/dd/yyyy" ")"End Sub |
Egyetlen idézet („) a VBA számára egy szövegsor végét jelenti. Míg a kettős idézőjelet („”) idézőjelként kezeljük a szövegben.
Hasonlóképpen használjon 3 idézőjelet („” ”), hogy a karakterláncot idézőjelekkel („) vegye körül
| 12 | MsgBox "" "Használja a 3 billentyűt a karakterlánc körbeírásához idézőjelekkel" ""'Ez azonnali ablakot nyomtat |
Cellaképlet hozzárendelése a karakterlánc -változóhoz
Kiolvashatjuk a képletet egy adott cellában vagy tartományban, és hozzárendelhetjük egy karakterlánc -változóhoz:
| 123 | 'Cellaképlet hozzárendelése a változóhozA strFormula halványítása karakterlánckéntstrFormula = Tartomány ("B1"). Képlet |
Különböző módszerek képletek hozzáadására a cellához
Íme még néhány példa arra, hogyan lehet képletet rendelni egy cellához:
- Képlet közvetlen hozzárendelése
- Határozza meg a képletet tartalmazó karakterlánc -változót
- Használja a változókat a képlet létrehozásához
| 12345678910111213141516171819202122232425 | Sub MoreFormulaEpamples ()'Alternatív módok a SUM képlet hozzáadására'a B1 cellába'A strFormula halványítása karakterlánckéntA cella halványítása tartománykéntdim fromRow as Range, toRow as RangeCella beállítása = tartomány ("B1")'String közvetlen hozzárendelésecell.Formula = "= SUM (A1: A10)"'Karakterlánc tárolása változóba"és hozzárendelés a" Formula "tulajdonsághozstrFormula = "= SUM (A1: A10)"cell.Formula = strFormula'Változók használata karakterlánc készítéséhez'és hozzárendelése a "Formula" tulajdonsághozfromRow = 1toRow = 10strFormula = "= 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 Calculate parancsot:
| 1 | Kiszámítja |
Egyetlen képlet, tartomány vagy teljes munkalap frissítéséhez használja. Ehelyett számítsa ki:
| 1 | Táblázatok ("Sheet1"). Tartomány ("a1: a10"). Számítsa ki |
