Ten samouczek nauczy Cię, jak tworzyć formuły komórek za pomocą VBA.
Formuły w VBA
Korzystając z VBA, możesz pisać formuły bezpośrednio w zakresach lub komórkach w programie Excel. To wygląda tak:
| 123456789 | Podformuła_Przykład()„Przypisz formułę zakodowaną na stałe do pojedynczej komórki”Zakres("b3").Formuła = "=b1+b2"'Przypisz elastyczną formułę do zakresu komórekZakres("d1:d100").WzórR1C1 = "=RC2+RC3"Napis końcowy |
Istnieją dwie właściwości zakresu, o których musisz wiedzieć:
- .Formuła - Tworzy dokładną formułę (zakodowane na stałe odwołania do komórek). Dobry do dodawania formuły do pojedynczej komórki.
- .FormułaR1C1 - Tworzy elastyczną formułę. Dobry do dodawania formuł do zakresu komórek, w których odwołania do komórek powinny się zmienić.
W przypadku prostych formuł można użyć właściwości .Formula. Jednak do wszystkiego innego zalecamy używanie Rejestrator makr…
Rejestrator makr i formuły komórek
Macro Recorder to nasze podstawowe narzędzie do pisania formuł komórkowych za pomocą VBA. Możesz po prostu:
- Rozpocznij nagrywanie
- Wpisz formułę (w razie potrzeby z odniesieniami względnymi / bezwzględnymi) do komórki i naciśnij enter
- Zatrzymaj nagrywanie
- Otwórz VBA i przejrzyj formułę, dostosowując ją w razie potrzeby i kopiując + wklejając kod w razie potrzeby.
Uważam, że to dużo łatwiej wprowadzić formułę do komórki niż wpisać odpowiednią formułę w VBA.
Zwróć uwagę na kilka rzeczy:
- Rejestrator makr zawsze używa właściwości .FormulaR1C1
- Rejestrator makr rozpoznaje bezwzględne i względne odniesienia do komórek
VBA FormułaR1C1 Właściwość
Właściwość FormulaR1C1 używa odwołań do komórek w stylu R1C1 (w przeciwieństwie do standardowego stylu A1, który jest przyzwyczajony do oglądania w programie Excel).
Oto kilka przykładów:
| 12345678910111213141516171819 | PodformułaR1C1_Przykłady()„Odniesienie D5 (bezwzględne)'=$5$Zakres("a1").FormułaR1C1 = "=R5C4"„Odniesienie D5 (względne) z komórki A1”'=D5'Zakres("a1").FormułaR1C1 = "=R[4]C[3]"'Odwołanie D5 (wiersz bezwzględny, kolumna względna) z komórki A1'=5 złZakres("a1").FormułaR1C1 = "=R5C[3]"'Odwołanie D5 (wiersz względny, kolumna bezwzględna) z komórki A1'=$D5Zakres("a1").FormułaR1C1 = "=R[4]C4"Napis końcowy |
Zauważ, że odwołania do komórek w stylu W1K1 umożliwiają ustawienie odwołań bezwzględnych lub względnych.
Referencje bezwzględne
W standardowej notacji A1 odwołanie bezwzględne wygląda tak: „=$C$2”. W notacji R1C1 wygląda to tak: „=R2C3”.
Aby utworzyć bezwzględne odwołanie do komórki przy użyciu typu W1K1:
- R + numer wiersza
- C + numer kolumny
Przykład: R2C3 reprezentuje komórkę $C$2 (C to trzecia kolumna).
| 123 | „Odniesienie D5 (bezwzględne)'=$5$Zakres("a1").FormułaR1C1 = "=R5C4" |
Referencje względne
Względne odwołania do komórek to odwołania do komórek, które „przenoszą się”, gdy formuła jest przenoszona.
W standardowym zapisie A1 wyglądają tak: „=C2”. W notacji W1K1 używasz nawiasów kwadratowych [], aby odsunąć odwołanie do komórki od bieżącej komórki.
Przykład: Wpisanie formuły „=R[1]C[1]” w komórce B3 odwołuje się do komórki D4 (komórka 1 wiersz poniżej i 1 kolumna po prawej stronie komórki z formułą).
Użyj liczb ujemnych, aby odwołać się do komórek powyżej lub po lewej stronie bieżącej komórki.
| 123 | „Odniesienie D5 (względne) z komórki A1”'=D5'Zakres("a1").FormułaR1C1 = "=R[4]C[3]" |
Mieszane referencje
Odwołania do komórek mogą być częściowo względne, a częściowo bezwzględne. Przykład:
| 123 | 'Odwołanie D5 (wiersz względny, kolumna bezwzględna) z komórki A1'=$D5Zakres("a1").FormułaR1C1 = "=R[4]C4" |
Właściwość formuły VBA
Podczas ustawiania formuł za pomocą .Właściwość formuły zawsze będziesz używać notacji w stylu A1. Formułę wpisujesz tak samo, jak w komórce Excela, z wyjątkiem otoczonej cudzysłowami:
| 12 | „Przypisz formułę zakodowaną na stałe do pojedynczej komórki”Zakres("b3").Formuła = "=b1+b2" |
Wskazówki dotyczące formuły VBA
Formuła ze zmienną
Podczas pracy z formułami w VBA bardzo często chcemy używać zmiennych w formułach komórek. Aby użyć zmiennych, użyj &, aby połączyć zmienne z resztą ciągu formuły. Przykład:
| 1234567 | Sub formuła_zmienna()Dim colNum As LongkolNum = 4Zakres("a1").FormułaR1C1 = "=R1C" & numerkolumny & "+R2C" & numerkolumnyNapis końcowy |
Cytaty Formuły
Jeśli chcesz dodać cytat („) w formule, wprowadź cytat dwukrotnie („”):
| 123 | Submakro2()Zakres("B3").FormułaR1C1 = "=TEKST(RC[-1],""mm/dd/rrrr"")"Napis końcowy |
Pojedynczy cytat („) oznacza dla VBA koniec ciągu tekstu. Natomiast podwójny cytat („”) jest traktowany jak cytat w ciągu tekstu.
Podobnie, użyj 3 cudzysłowów („””), aby otoczyć ciąg znakiem cudzysłowu („)
| 12 | MsgBox """Użyj 3, aby otoczyć ciąg cudzysłowami"""' Spowoduje to wydrukowanie natychmiastowego okna |
Przypisz formułę komórki do zmiennej łańcuchowej
Możemy odczytać formułę w danej komórce lub zakresie i przypisać ją do zmiennej łańcuchowej:
| 123 | 'Przypisz wzór komórki do zmiennejDim strFormula jako StringstrFormula = Zakres("B1").Formula |
Różne sposoby dodawania formuł do komórki
Oto kilka przykładów, jak przypisać formułę do komórki:
- Bezpośrednio przypisz formułę
- Zdefiniuj zmienną łańcuchową zawierającą formułę
- Użyj zmiennych do stworzenia formuły
| 12345678910111213141516171819202122232425 | Pod WięcejPrzykładyFormuły ()' Alternatywne sposoby dodawania formuły SUMA' do komórki B1'Dim strFormula jako StringPrzyciemnij komórkę jako zakresdim fromRow as Range, toRow as RangeUstaw komórkę = Zakres("B1")' Bezpośrednie przypisanie ciągukomórka.Formuła = "=SUMA(A1:A10)"' Przechowywanie łańcucha w zmiennej' i przypisanie do właściwości "Formuła"strFormula = "=SUMA(A1:A10)"komórka.Formuła = strFormula' Używanie zmiennych do budowy łańcucha' i przypisanie go do właściwości „Formuła”z rzędu = 1doRzędu = 10strFormula = "=SUM(A" & odWartości & ":A" & doWartości & ")komórka.Formuła = strFormulaNapis końcowy |
Odśwież formuły
Dla przypomnienia, aby odświeżyć formuły, możesz użyć polecenia Oblicz:
| 1 | Oblicz |
Aby odświeżyć pojedynczą formułę, zakres lub cały arkusz, użyj polecenia .Oblicz:
| 1 | Arkusze("Arkusz1").Range("a1:a10").Oblicz |
