Tento tutoriál vás naučí vytvárať vzorce buniek pomocou jazyka VBA.
Vzorce vo VBA
Pomocou VBA môžete písať vzorce priamo do rozsahov alebo buniek v programe Excel. Vyzerá to takto:
| 123456789 | Sub Formula_Example ()„Priraďte pevne kódovaný vzorec k jednej bunkeRozsah ("b3"). Vzorec = "= b1+b2"'Priraďte flexibilný vzorec k radu buniekRozsah ("d1: d100"). FormulaR1C1 = "= RC2+RC3"Koniec pod |
Potrebujete vedieť dve vlastnosti rozsahu:
- .Formula - Vytvorí presný vzorec (pevne zakódované odkazy na bunky). Dobré na pridanie vzorca do jednej bunky.
- .FormulaR1C1 - Vytvára flexibilný vzorec. Dobré na pridanie vzorcov do radu buniek, kde by sa mali zmeniť odkazy na bunky.
V prípade jednoduchých vzorcov je dobré použiť vlastnosť .Formula. Na všetko ostatné však odporúčame použiť Záznamník makra…
Makro rekordér a vzorce buniek
Makro rekordér je náš nástroj na písanie vzorcov buniek pomocou jazyka VBA. Môžete jednoducho:
- Spustite nahrávanie
- Zadajte vzorec (podľa potreby s relatívnymi / absolútnymi odkazmi) do bunky a stlačte kláves Enter
- Zastaviť nahrávanie
- Otvorte VBA a skontrolujte vzorec, podľa potreby sa prispôsobte a podľa potreby skopírujte+prilepte kód.
Zisťujem, že je oveľa lahšie zadať vzorec do bunky, ako zadať zodpovedajúci vzorec vo VBA.
Všimnite si pár vecí:
- Makro rekordér bude vždy používať vlastnosť .FormulaR1C1
- Makro rekordér rozpoznáva absolútne a relatívne bunkové referencie
Vlastníctvo VBA FormulaR1C1
Vlastnosť FormulaR1C1 používa odkazovanie na bunky v štýle R1C1 (na rozdiel od štandardného štýlu A1, na ktorý ste zvyknutí v programe Excel).
Tu je niekoľko príkladov:
| 12345678910111213141516171819 | Sub FormulaR1C1_Examples ()„Referencia D5 (absolútna)'= $ D $ 5Rozsah ("a1"). FormulaR1C1 = "= R5C4"„Odkaz D5 (relatívny) z bunky A1'= D5Rozsah ("a1"). FormulaR1C1 = "= R [4] C [3]"„Referencia D5 (absolútny riadok, relatívny stĺpec) z bunky A1'= 5 DRozsah ("a1"). FormulaR1C1 = "= R5C [3]"„Referencia D5 (relatívny riadok, absolútny stĺpec) z bunky A1'= 5 D $Rozsah ("a1"). FormulaR1C1 = "= R [4] C4"Koniec pod |
Všimnite si, že odkazovanie na bunky v štýle R1C1 vám umožňuje nastaviť absolútne alebo relatívne referencie.
Absolútne referencie
V štandardnom zápise A1 vyzerá absolútny odkaz takto: „= $ C $ 2“. V zápise R1C1 to vyzerá takto: „= R2C3“.
Ak chcete vytvoriť absolútny odkaz na bunku pomocou typu štýlu R1C1:
- R + Číslo riadku
- C + Číslo stĺpca
Príklad: R2C3 bude predstavovať bunku $ C $ 2 (C je tretí stĺpec).
| 123 | „Referencia D5 (absolútna)'= $ D $ 5Rozsah ("a1"). FormulaR1C1 = "= R5C4" |
Relatívne referencie
Relatívne odkazy na bunky sú odkazy na bunky, ktoré sa „pohybujú“ pri presúvaní vzorca.
V štandardnom zápise A1 vyzerajú takto: „= C2“. V zápise R1C1 použite zátvorky [] na odsadenie odkazu na bunku od aktuálnej bunky.
Príklad: Zadanie vzorca „= R [1] C [1]“ do bunky B3 by odkazovalo na bunku D4 (bunka 1 riadok nižšie a 1 stĺpec napravo od bunky vzorca).
Na odkaz na bunky nad alebo vľavo od aktuálnej bunky použite záporné čísla.
| 123 | „Odkaz D5 (relatívny) z bunky A1'= D5Rozsah ("a1"). FormulaR1C1 = "= R [4] C [3]" |
Zmiešané referencie
Bunkové odkazy môžu byť čiastočne relatívne a čiastočne absolútne. Príklad:
| 123 | „Referencia D5 (relatívny riadok, absolútny stĺpec) z bunky A1'= 5 D $Rozsah ("a1"). FormulaR1C1 = "= R [4] C4" |
Vlastnosť vzorca VBA
Pri nastavovaní vzorcov pomocou.Vlastnosť vzorca vždy budete používať notáciu v štýle A1. Vzorec zadávate rovnako ako v bunke programu Excel, okrem úvodzoviek:
| 12 | „Priraďte pevne kódovaný vzorec k jednej bunkeRozsah ("b3"). Vzorec = "= b1+b2" |
Tipy na vzorec VBA
Vzorec s premennou
Pri práci so vzorcami vo VBA je veľmi bežné chcieť používať vo vzorcoch buniek premenné. Ak chcete použiť premenné, pomocou & skombinujte premenné so zvyškom reťazca vzorcov. Príklad:
| 1234567 | Sub Formula_Variable ()Dim colNum tak dlhécolNum = 4Rozsah ("a1"). FormulaR1C1 = "= R1C" & colNum & "+R2C" & colNumKoniec pod |
Citáty zo vzorcov
Ak potrebujete do vzorca pridať citát (“), zadajte citát dvakrát („ “):
| 123 | Sub Macro2 ()Rozsah ("B3"). FormulaR1C1 = "= TEXT (RC [-1]," "mm/dd/rrrr" ")"Koniec pod |
Jedna citácia („) znamená pre VBA koniec reťazca textu. Zatiaľ čo dvojitá úvodzovka („“) je v reťazci textu považovaná za citáciu.
Podobne použite 3 úvodzovky („“ “) na obklopenie reťazca úvodzovkami („)
| 12 | MsgBox "" "Použite 3 na obklopenie reťazca úvodzovkami" ""'Toto vytlačí okamžité okno |
Priraďte vzorec bunky k premennej reťazca
Môžeme prečítať vzorec v danej bunke alebo rozsahu a priradiť ho reťazcovej premennej:
| 123 | „Priradiť vzorec bunky k premennejDim strFormula ako reťazecstrFormula = Rozsah („B1“). Vzorec |
Rôzne spôsoby pridania vzorcov do bunky
Tu je niekoľko ďalších príkladov priradenia vzorca k bunke:
- Priamo priradiť vzorec
- Definujte premennú reťazca obsahujúcu vzorec
- Na vytvorenie vzorca použite premenné
| 12345678910111213141516171819202122232425 | Sub MoreFormulaExamples ()„Alternatívne spôsoby pridania vzorca SUM“do bunky B1'Dim strFormula ako reťazecDim bunka ako rozsahstmaviť odRow as Range, toRow as RangeNastaviť bunku = rozsah („B1“)„Priame priradenie reťazcacell.Formula = "= SUM (A1: A10)"'Ukladanie reťazca do premennej“a priradenie k vlastnosti„ Formula “strFormula = "= SUM (A1: A10)"bunka.Formula = strFormula„Použitie premenných na vytvorenie reťazca“a priradením k vlastnosti„ Formula “fromRow = 1toRow = 10strFormula = "= SUM (A" & fromValue & ": A" & toValue & ")bunka.Formula = strFormulaKoniec pod |
Obnovte vzorce
Pripomíname, že na aktualizáciu vzorcov môžete použiť príkaz Vypočítať:
| 1 | Vypočítajte |
Ak chcete obnoviť jeden vzorec, rozsah alebo celý pracovný hárok, použite namiesto toho .Vypočítajte:
| 1 | Tabuľky („List1“). Rozsah („a1: a10“). Vypočítajte |
