Formuły Excel VBA – najlepszy przewodnik-automatyzuj ExcelAutomate Excel

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 do zakresów lub komórek w programie Excel. Wygląda to tak:

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

istnieją dwie właściwości zakresu, które musisz znać:

• .Formuła-tworzy dokładną formułę (twarde odniesienia do komórek). Dobre do dodawania formuły do pojedynczej komórki.
• .Formula1c1-tworzy elastyczną formułę. Dobre do dodawania formuł do zakresu komórek, w których odniesienia do komórek powinny się zmienić.

dla prostych formuł, dobrze jest użyć .Właściwość Formuły. Jednak we wszystkim innym zalecamy korzystanie z Makro Recorder …

Makro Recorder i formuły komórek

Makro Recorder to nasze narzędzie do pisania formuł komórek za pomocą VBA. Możesz po prostu:

• Rozpocznij nagrywanie
• wpisz formułę (z względnymi/bezwzględnymi referencjami w razie potrzeby) do komórki& naciśnij enter
• Zatrzymaj nagrywanie
• Otwórz VBA i przejrzyj formułę, dostosowując w razie potrzeby i kopiując+wklejając kod w razie potrzeby.

uważam, że znacznie łatwiej jest wprowadzić formułę do komórki niż wpisać odpowiednią formułę w VBA.

zwróć uwagę na kilka rzeczy:

• rejestrator makr zawsze będzie używał .Właściwość FormulaR1C1
• rejestrator makr rozpoznaje bezwzględne kontra względne odwołania do komórek

VBA właściwość FormulaR1C1

właściwość FormulaR1C1 wykorzystuje odwołania do komórek w stylu R1C1 (w przeciwieństwie do standardowego stylu A1, do którego jesteś przyzwyczajony w programie Excel).

oto kilka przykładów:

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

zauważ, że odwoływanie się do komórek w stylu R1C1 pozwala ustawić bezwzględne lub względne odwołania.

odniesienia bezwzględne

w standardowej notacji A1 odniesienie bezwzględne wygląda następująco: „=$C$2”. W notacji R1C1 wygląda to tak: „=R2C3”.

aby utworzyć bezwzględne odniesienie do komórki za pomocą typu R1C1:

• r + numer wiersza
• c + numer kolumny

przykład: r2c3 reprezentowałby komórkę $C$2 (C jest trzecią kolumną).

 'Reference D5 (Absolute) '=$D$5 Range("a1").FormulaR1C1 = "=R5C4"

względne odniesienia

względne odniesienia do komórek To odniesienia do komórek, które „poruszają się”, gdy formuła jest przesuwana.

w standardowej notacji A1 wyglądają tak: „=C2”. W notacji R1C1 nawiasy służą do przesunięcia odniesienia do komórki od bieżącej komórki.

przykład: Wpisanie formuły „= RC ” w komórce B3 odwoływałoby się do komórki D4 (komórki 1 wiersz poniżej i 1 kolumna po prawej stronie komórki formuły).

użyj liczb ujemnych, aby odwołać się do komórek powyżej lub na lewo od bieżącej komórki.

 'Reference D5 (Relative) from cell A1 '=D5 Range("a1").FormulaR1C1 = "=RC"

odniesienia mieszane

odniesienia do komórek mogą być częściowo względne i częściowo bezwzględne. Przykład:

 'Reference D5 (Relative Row, Absolute Column) from cell A1 '=$D5 Range("a1").FormulaR1C1 = "=RC4"

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. Wprowadzasz formułę tak, jak w komórce Excela, z wyjątkiem otoczonych cytatami:

 'Assign a hard-coded formula to a single cell Range("b3").Formula = "=b1+b2"

Wskazówki dotyczące formuły VBA

formuła ze zmienną

podczas pracy z formułami w VBA bardzo często chce się 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:

Sub Formula_Variable() Dim colNum As Long colNum = 4 Range("a1").FormulaR1C1 = "=R1C" & colNum & "+R2C" & colNumEnd Sub

Cytaty Formuły

Jeśli chcesz dodać cytat ( ” ) w ramach formuły, wprowadź cytat dwukrotnie („”):

Sub Macro2() Range("B3").FormulaR1C1 = "=TEXT(RC,""mm/dd/yyyy"")"End Sub

pojedynczy cytat („) oznacza dla VBA koniec łańcucha tekstu. Natomiast cudzysłów podwójny („”) jest traktowany jak cudzysłów w ciągu tekstu.

podobnie, użyj 3 cudzysłowów ( „”” ), aby otoczyć łańcuch cudzysłowem („)

MsgBox """Use 3 to surround a string with quotes"""' This will print <"Use 3 to surround a string with quotes"> immediate window

przypisanie Formuły komórki do zmiennej łańcuchowej

możemy odczytać formułę w danej komórce lub zakresie i przypisać ją do zmiennej:

'Assign Cell Formula to VariableDim strFormula as StringstrFormula = Range("B1").Formula

różne sposoby dodawania formuł do komórki

Oto kilka przykładów, jak przypisać formułę do komórki:

1. bezpośrednio Przypisz formułę
2. Zdefiniuj zmienną łańcuchową zawierającą formułę
3. użyj zmiennych aby utworzyć formułę

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

odśwież formuły

jako przypomnienie, aby odświeżyć formuły, możesz użyć polecenia Oblicz:

Calculate

aby odświeżyć pojedynczą formułę, zakres lub cały arkusz roboczy .Oblicz zamiast:

Sheets("Sheet1").Range("a1:a10").Calculate