VBA Formula in Excel — Write Formulas in Code with .Formula and .FormulaR1C1

TL;DR — Range("D2").Formula = "=B2*C2" puts a live formula in a cell, as opposed to .Value, which puts a static result. The rule that trips everyone: .Formula is always US-English function names and comma separators, whatever the user's language. =SUM(A1,B1) works everywhere and Excel localizes the display; a German =SUMME(A1;B1) raises error 1004. Use .FormulaR1C1 to stamp one relative formula across a whole range, and never forget the leading =.

Range("D2").Formula = "=B2*C2"               ' a live formula - Excel evaluates it
Range("E2").Formula = "=IF(D2>100,""Big"",""OK"")"   ' quotes inside are doubled

' stamp a relative formula down a whole column in one assignment:
Range("D2:D1000").FormulaR1C1 = "=RC[-2]*RC[-1]"     ' each row: two cells to the left

Putting a formula into a cell from code looks like assigning text, and mechanically it is — you hand .Formula a string. But that string lives in a fixed dialect, and that is the idea this guide is built on: .Formula always uses US-English function names and comma separators, regardless of the user's locale, and Excel translates the display for you. Once you hold that, the 1004 errors, the .FormulaLocal question, and the R1C1 form all make sense.

What you'll learn

The mental model — .Formula writes a live formula in one fixed dialect
The rule that matters most — English function names and commas, always
.FormulaLocal — when you actually need the user's language and separators
.FormulaR1C1 — stamping one relative formula across a whole range
The leading = is mandatory, and quotes inside must be doubled
Reading a formula back, and .HasFormula

The mental model: a live formula in a fixed dialect

.Value stores a result; .Formula stores a recipe that Excel recalculates. Assigning .Formula is how you make code produce spreadsheets that keep working after the macro ends:

Range("C2").Value = 42               ' a static number
Range("C2").Formula = "=A2+B2"       ' a formula - updates when A2 or B2 change

The string you assign is written in one canonical dialect: US-English function names (SUM, VLOOKUP, IF) and commas between arguments — even on a machine whose Excel UI shows SUMME and uses semicolons. Excel stores the formula in that neutral form and displays it in the user's language. So the same line of VBA produces a correct, localized formula for every user on Earth, which is exactly why the dialect is fixed. The moment you try to write in the user's language instead, you hit the error below.

The rule that matters most: English names, commas, always

This is the single fact that turns a working macro into a 1004 on a colleague's machine. .Formula accepts only US-English function names and only the comma as the argument separator:

Range("A1").Formula = "=SUM(B1:B10)"     ' correct on EVERY machine
Range("A1").Formula = "=SUMME(B1;B10)"   ' German names + semicolons -> run-time error 1004

Two things are locked, not one. The function names are English (SUM, not SUMME or SOMME), and the list separator is always a comma — even in regions where the Excel UI uses a semicolon and the decimal point is a comma. Authoring in this fixed dialect is a feature: write .Formula = "=SUM(...)" once and it is correct for a German, French, or Spanish user, who each see it in their own language. If a formula string built from English names still errors, the usual culprit is the separator — a stray semicolon that came from copying the formula out of a localized Excel UI.

.FormulaLocal: when you want the user's language

There is a sister property, .FormulaLocal, that reads and writes in the user's language and separators:

' on a German machine:
Range("A1").FormulaLocal = "=SUMME(B1;B10)"   ' German names + semicolons - OK here
Debug.Print Range("A1").Formula               ' reads back as "=SUM(B1:B10)"

.FormulaLocal is the right tool when you are echoing exactly what the user typed, or building a UI that shows formulas in their language. But it makes your code non-portable — the same string that works on a German machine fails on an English one. The practical rule: author with .Formula (English, commas) for anything you ship, and reach for .FormulaLocal only when you specifically need to speak the user's dialect. When you read a cell's formula for display, .FormulaLocal gives the reader their own language; when you read it to compare or store, .Formula gives you the stable, neutral form.

.FormulaR1C1: one relative formula for a whole range

When you generate a formula across many rows, the A1 style forces you to think about each row's references. R1C1 notation describes references as offsets from the current cell, so one string is correct for every cell in the range:

' A1 style - the reference is literal, adjusted as Excel copies it down:
Range("D2:D1000").Formula = "=B2*C2"          ' Excel shifts B2/C2 per row

' R1C1 style - the reference is an offset, identical in every row:
Range("D2:D1000").FormulaR1C1 = "=RC[-2]*RC[-1]"   ' "two cells left times one cell left"

RC[-2] means "same row, two columns to the left"; R[-1]C means "one row up, same column"; a number with no brackets like R1C1 is absolute (equivalent to $A$1). Both lines above produce the same result, but R1C1 is unambiguous for generated formulas: you are not mentally tracking how Excel will adjust B2 on row 837. For anything your code writes across a range, R1C1 is usually the clearer choice, and it is the notation the macro recorder emits. See VBA Range for building the target range.

The leading = and doubled quotes

Two mechanical traps catch every beginner. First, the string must start with =, or Excel stores it as literal text, not a formula, with no error to warn you:

Range("A1").Formula = "SUM(B1:B10)"      ' no "=" -> the cell literally shows the text SUM(B1:B10)
Range("A1").Formula = "=SUM(B1:B10)"     ' correct

Second, because the whole formula is a VBA string in double quotes, any quotes inside the formula must be doubled:

Range("A1").Formula = "=IF(B1="""",""empty"",B1)"   ' each "" is one literal quote in the formula

That renders in the cell as =IF(B1="","empty",B1). Miscounting the quotes is the second most common 1004 after the separator problem. When a formula string gets long and quote-heavy, build it in pieces or use a helper that escapes the quotes for you.

Reading a formula back

.Formula reads as well as writes. On a cell that holds a formula it returns the formula string; on a cell that holds a constant it returns the value as a string:

Debug.Print Range("D2").Formula          ' "=B2*C2" if D2 has a formula, else e.g. "100"
Debug.Print Range("D2").HasFormula       ' True only if it is actually a formula

Use .HasFormula to know which you have before you act on the string — it is True only for a real formula, False for a constant. This is the reliable way to tell a computed cell from a typed one, and it pairs naturally with reading the underlying number through .Value or .Value2, covered in VBA Value vs Value2 vs Text. If instead you want the answer in a VBA variable without ever putting a formula in a cell, call the function directly through VBA WorksheetFunction — that is the "result in a variable" path, while .Formula is the "live formula in the cell" path.

How ExcelMaster helps

The .Formula bugs that waste an afternoon are the quiet ones: a semicolon that works on your machine and 1004s on a teammate's, a missing = that turns a formula into text, a miscounted "" deep in a nested IF, and an A1 formula stamped across 1,000 rows with the references off by one. Each is a string detail, invisible until it runs.

ExcelMaster writes formula strings that are correct on every machine. Describe the formula — "multiply the two columns to the left, down the whole table" — and it emits .Formula in the neutral US-English dialect, or .FormulaR1C1 when the formula is relative and generated, with the leading = and every internal quote doubled correctly. It knows when you actually want .FormulaLocal, and when you really wanted the answer in a variable through WorksheetFunction instead. You describe the calculation; it writes the string that runs everywhere.

Frequently asked questions

How do I set a formula in a cell with VBA?

Assign a string that starts with = to the Formula property: Range("D2").Formula = "=B2*C2". Use US-English function names and commas — =SUM(A1,B1), not a localized name or semicolons — and Excel displays the formula in the user's language automatically. To write the same formula down a whole range, assign it to the range; for relative generated formulas, use .FormulaR1C1.

Why does my VBA formula give run-time error 1004?

The most common cause is writing in a localized dialect: .Formula requires US-English function names and comma separators, so =SUMME(A1;B1) fails while =SUM(A1,B1) works. Other causes are a missing leading =, or unbalanced quotes inside the formula string (each literal quote must be doubled as ""). If you must write in the user's language and separators, use .FormulaLocal instead.

What is the difference between .Formula and .FormulaR1C1?

Both write a live formula; they differ in reference style. .Formula uses A1 notation (=B2*C2), where references are literal cells. .FormulaR1C1 uses R1C1 notation (=RC[-2]*RC[-1]), where references are offsets from the current cell, so one string is correct for every cell in a range. R1C1 is clearer for formulas your code generates across many rows, and it is what the macro recorder produces.

What is the difference between .Formula and .FormulaLocal in VBA?

.Formula always uses US-English function names and comma separators, so it is portable — the same string works on any machine. .FormulaLocal uses the user's language and list separator, so =SUMME(A1;B1) works on a German machine but fails on an English one. Use .Formula for code you ship, and .FormulaLocal only when you specifically need to read or write formulas in the user's own language.

How do I read a cell's formula in VBA?

Read the Formula property: s = Range("D2").Formula returns the formula string (like "=B2*C2") for a formula cell, or the value as text for a constant. Check Range("D2").HasFormula first — it returns True only when the cell actually contains a formula, so you can tell a computed cell from a typed one before acting on the string.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-31.