Tento tutoriál vás naučí, jak vytvořit buněčné vzorce pomocí VBA.
vzorce ve VBA
pomocí VBA můžete psát vzorce přímo do rozsahů nebo buněk v aplikaci Excel. Vypadá to takto:
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
potřebujete vědět dvě vlastnosti rozsahu:
- .Formula-vytvoří přesný vzorec (pevně kódované odkazy na buňky). Dobré pro přidání vzorce do jedné buňky.
- .FormulaR1C1-vytvoří flexibilní vzorec. Dobré pro přidání vzorců do řady buněk, kde by se odkazy na buňky měly změnit.
pro jednoduché vzorce je v pořádku použít .Vzorec Vlastnost. Pro všechno ostatní však doporučujeme použít makro rekordér…
makro rekordér a buněčné vzorce
makro rekordér je náš go-to nástroj pro psaní buněčných vzorců s VBA. Můžete jednoduše:
- Spuštění nahrávání
- Napište vzorec (s relativní / absolutní odkazy podle potřeby) do buňky & stiskněte klávesu enter
- Zastavit nahrávání
- Otevřít VBA a recenze vzorec, přizpůsobuje se podle potřeby a kopírování+vkládání kódu, kde je potřeba.
zjistil jsem, že je mnohem snazší zadat vzorec do buňky, než zadat odpovídající vzorec do VBA.
Všimněte si pár věcí:
- Makro Rekordér bude vždy používat .FormulaR1C1 majetku
- Makro Rekordér rozezná Absolutní a Relativní Odkazy na Buňky
VBA FormulaR1C1 Ubytování
vlastnost FormulaR1C1 používá R1C1-styl odkazování na buňky (jako protiklad k standardní A1-styl, které jste zvyklí vídat v aplikaci Excel).
zde je několik příkladů:
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
Všimněte si, že odkazování na buňku ve stylu R1C1 umožňuje nastavit absolutní nebo relativní odkazy.
absolutní reference
ve standardním A1 zápisu absolutní odkaz vypadá takto: „=$C $ 2“. V zápisu R1C1 to vypadá takto: „=R2C3“.
vytvořit Absolutní odkaz na buňku pomocí R1C1-typ stylu:
- R + číslo Řádku
- C + číslo Sloupce
Příklad: R2C3 by představovalo buňka $C$2 (C je 3. sloupec).
'Reference D5 (Absolute) '=$D$5 Range("a1").FormulaR1C1 = "=R5C4"
relativní odkazy
relativní odkazy na buňky jsou odkazy na buňky, které se při přesunutí vzorce“ pohybují“.
ve standardním zápisu A1 vypadají takto: „=C2“. V notaci R1C1 používáte závorky k vyrovnání odkazu na buňku z aktuální buňky.
příklad: Zadání vzorce „= RC “ v buňce B3 by odkazovalo na buňku D4 (buňka 1 řádek níže a 1 sloupec napravo od buňky vzorce).
použijte záporná čísla pro referenční buňky nad nebo vlevo od aktuální buňky.
'Reference D5 (Relative) from cell A1 '=D5 Range("a1").FormulaR1C1 = "=RC"
Smíšené Odkazy
odkazy na Buňky mohou být částečně relativní a částečně absolutní. Příklad:
'Reference D5 (Relative Row, Absolute Column) from cell A1 '=$D5 Range("a1").FormulaR1C1 = "=RC4"
vlastnost vzorce VBA
při nastavování vzorců pomocí .Vlastnost vzorce vždy budete používat notaci ve stylu A1. Můžete zadat vzorec jako byste v aplikace Excel, buňka, s výjimkou obklopen citace:
'Assign a hard-coded formula to a single cell Range("b3").Formula = "=b1+b2"
VBA Vzorce Tipy
Vzorec S Proměnnou
Při práci s Formulí v jazyce VBA, to je velmi běžné, že chcete používat proměnné v buňce vzorce. Chcete-li použít proměnné, použijte & pro kombinaci proměnných se zbytkem řetězce vzorců. Příklad:
Sub Formula_Variable() Dim colNum As Long colNum = 4 Range("a1").FormulaR1C1 = "=R1C" & colNum & "+R2C" & colNumEnd Sub
citace vzorců
Pokud potřebujete přidat nabídku („) do vzorce, zadejte nabídku dvakrát („“):
Sub Macro2() Range("B3").FormulaR1C1 = "=TEXT(RC,""mm/dd/yyyy"")"End Sub
jednoduchá uvozovka („) znamená, VBA konec řetězce textu. Zatímco s dvojitou citací („“) se zachází jako s citací v řetězci textu.
Podobně, použití 3 uvozovek („““) k surround řetězec s uvozovky („)
MsgBox """Use 3 to surround a string with quotes"""' This will print <"Use 3 to surround a string with quotes"> immediate window
Přiřadit Buňce Vzorec, do Řetězcové Proměnné
můžeme číst vzorec v dané buňce nebo rozsahu a přiřadit do proměnné:
'Assign Cell Formula to VariableDim strFormula as StringstrFormula = Range("B1").Formula
Různých Způsobů, jak Přidat Vzorce do Buňky
Zde je několik dalších příkladů, jak přiřadit vzorec do buňky:
- Přímo Přiřadit Vzorec,
- Definovat Proměnné Řetězec Obsahující Vzorec,
- Použití Proměnné Vytvořit Vzorec,
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
Obnovit Vzorce
Jako připomínka, osvěžit vzorců, můžete použít Výpočet příkaz:
Calculate
obnovit jeden vzorec, rozsah, nebo celý list použití .Vypočítejte místo toho:
Sheets("Sheet1").Range("a1:a10").Calculate