Tento tutoriál vás naučí, jak vytvářet vzorce buněk 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:
| 123456789 | Dílčí vzorec_Příklad ()`` Přiřaďte pevně kódovaný vzorec jedné buňceRozsah ("b3"). Vzorec = "= b1+b2"`` Přiřaďte flexibilní buňku řadě buněkRozsah ("d1: d100"). FormulaR1C1 = "= RC2+RC3"End Sub |
Budete potřebovat dvě vlastnosti Range:
- .Vzorec - Vytvoří přesný vzorec (pevně kódované odkazy na buňky). Dobré pro přidání vzorce do jedné buňky.
- .FormulaR1C1 - Vytváří flexibilní vzorec. Dobré pro přidání vzorců do řady buněk, kde by se měly změnit odkazy na buňky.
U jednoduchých vzorců je dobré použít vlastnost .Formula. Pro vše ostatní však doporučujeme použít Záznamník maker…
Makro rekordér a vzorce buněk
Makro rekordér je náš nástroj pro psaní vzorců buněk pomocí VBA. Můžete jednoduše:
- Začít nahrávat
- Do buňky napište vzorec (s relativní / absolutní referencí podle potřeby) a stiskněte Enter
- Zastavit nahrávání
- Otevřete VBA a zkontrolujte vzorec, podle potřeby se přizpůsobte a v případě potřeby zkopírujte+vložte kód.
Zjišťuji, že je mnohem jednodušší zadejte vzorec do buňky, než zadejte odpovídající vzorec ve VBA.
Všimněte si několika věcí:
- Záznamník maker bude vždy používat vlastnost .FormulaR1C1
- Záznamník maker rozpoznává absolutní a relativní odkazy na buňky
Vlastnost VBA FormulaR1C1
Vlastnost FormulaR1C1 používá odkazování na buňky ve stylu R1C1 (na rozdíl od standardního stylu A1, na který jste zvyklí v Excelu).
Zde jsou nějaké příklady:
| 12345678910111213141516171819 | Sub FormulaR1C1_Examples ()'Odkaz D5 (absolutní)'= $ D $ 5Rozsah ("a1"). FormulaR1C1 = "= R5C4"„Odkaz D5 (relativní) z buňky A1'= D5Rozsah ("a1"). FormulaR1C1 = "= R [4] C [3]"'Odkaz D5 (absolutní řádek, relativní sloupec) z buňky A1'= D $ 5Rozsah ("a1"). FormulaR1C1 = "= R5C [3]"'Odkaz D5 (relativní řádek, absolutní sloupec) z buňky A1'= $ D5Rozsah ("a1"). FormulaR1C1 = "= R [4] C4"End Sub |
Všimněte si, že odkazování na buňky ve stylu R1C1 vám umožňuje nastavit absolutní nebo relativní odkazy.
Absolutní reference
Ve standardním zápisu A1 vypadá absolutní reference takto: „= $ C $ 2“. V zápisu R1C1 to vypadá takto: „= R2C3“.
Chcete-li vytvořit absolutní odkaz na buňku pomocí typu stylu R1C1:
- R + Číslo řádku
- C + Číslo sloupce
Příklad: R2C3 bude představovat buňku $ C $ 2 (C je 3. sloupec).
| 123 | 'Odkaz D5 (absolutní)'= $ D $ 5Rozsah ("a1"). FormulaR1C1 = "= R5C4" |
Relativní reference
Relativní odkazy na buňky jsou odkazy na buňky, které se při přesunu vzorce „přesunou“.
Ve standardním zápisu A1 vypadají takto: „= C2“. V zápisu R1C1 použijete závorky [] k odsazení odkazu na buňku od aktuální buňky.
Příklad: Zadání vzorce „= R [1] C [1]“ do buňky B3 by odkazovalo na buňku D4 (buňka 1 řádek níže a 1 sloupec napravo od buňky vzorce).
Pomocí záporných čísel můžete odkazovat na buňky nad nebo vlevo od aktuální buňky.
| 123 | „Odkaz D5 (relativní) z buňky A1'= D5Rozsah ("a1"). FormulaR1C1 = "= R [4] C [3]" |
Smíšené reference
Odkaz na buňku může být částečně relativní a částečně absolutní. Příklad:
| 123 | 'Odkaz D5 (relativní řádek, absolutní sloupec) z buňky A1'= $ D5Rozsah ("a1"). FormulaR1C1 = "= R [4] C4" |
Vlastnost vzorce VBA
Při nastavování vzorců pomocí.Vlastnost vzorce vždy budete používat notaci ve stylu A1. Vzorec zadáváte stejně jako v buňce aplikace Excel, kromě uvozovek:
| 12 | „Přiřaďte pevně kódovaný vzorec jedné buňceRozsah ("b3"). Vzorec = "= b1+b2" |
Tipy pro vzorec VBA
Vzorec s proměnnou
Při práci se vzorci ve VBA je velmi běžné chtít používat proměnné ve vzorcích buněk. Chcete -li použít proměnné, použijte & ke zkombinování proměnných se zbytkem řetězce vzorců. Příklad:
| 1234567 | Sub Formula_Variable ()Dim colNum As LongcolNum = 4Rozsah ("a1"). FormulaR1C1 = "= R1C" & colNum & "+R2C" & colNumEnd Sub |
Citace vzorců
Pokud potřebujete do vzorce přidat nabídku (“), zadejte nabídku dvakrát („ “):
| 123 | Sub Macro2 ()Rozsah ("B3"). FormulaR1C1 = "= TEXT (RC [-1]," "mm/dd/rrrr" ")"End Sub |
Jedna citace (“) znamená VBA konec řetězce textu. Zatímco dvojitá uvozovka („“) je v řetězci textu považována za citát.
Podobně použijte 3 uvozovky (“” ”) k obklopení řetězce uvozovkami (“)
| 12 | MsgBox "" "Pomocí 3 obklopte řetězec uvozovkami" ""„Tím se vytiskne okamžité okno |
Přiřaďte vzorec buňky proměnné řetězce
Můžeme přečíst vzorec v dané buňce nebo rozsahu a přiřadit jej řetězcové proměnné:
| 123 | `` Přiřaďte vzorec buňky proměnnéDim strFormula jako řetězecstrFormula = Rozsah ("B1"). Vzorec |
Různé způsoby přidání vzorců do buňky
Zde je několik dalších příkladů, jak přiřadit buňce vzorec:
- Přímo přiřadit vzorec
- Definujte proměnnou řetězce obsahující vzorec
- K vytvoření vzorce použijte proměnné
| 12345678910111213141516171819202122232425 | Sub MoreFormulaExamples ()„Alternativní způsoby přidání vzorce SUM“do buňky B1'Dim strFormula jako řetězecDim buňky jako rozsahztlumit zRow as Range, toRow as RangeNastavit buňku = rozsah ("B1")"Přímé přiřazení řetězce."cell.Formula = "= SUM (A1: A10)"'Uložení řetězce do proměnné.''a přiřazení k vlastnosti "Formula"strFormula = "= SUM (A1: A10)"cell.Formula = strFormula`` Použití proměnných k vytvoření řetězce“a přiřazením vlastnosti„ Formula “fromRow = 1toRow = 10strFormula = "= SUMA (A" & fromValue & ": A" & toValue & ")cell.Formula = strFormulaEnd Sub |
Obnovte vzorce
Připomínáme, že k aktualizaci vzorců můžete použít příkaz Vypočítat:
| 1 | Vypočítat |
Chcete -li aktualizovat jeden vzorec, rozsah nebo celý list, použijte místo toho .Calculate:
| 1 | Listy („List1“). Rozsah („a1: a10“). Vypočítejte |
