Excel VBA Range.Formula: How to Read and Set Formulas

In Excel VBA, use a range’s Formula property to read or write a worksheet formula in A1 notation. For example, with a worksheet named Sales and the target cell D2, Range("D2").Formula = "=B2*C2" puts a multiplication formula in D2; the result appears in the cell just as if you had entered the formula there.

What Range.Formula means

A Range is one cell or a group of cells on a worksheet. Its Formula property gets or sets the formula associated with that range. The property is useful when a macro needs to write calculations, inspect existing formulas, or update formulas across many rows without entering them one at a time.

The text you assign is normally an Excel formula beginning with an equals sign. VBA uses a string to pass that text to Excel, so the formula’s quotation marks, if any, must also be represented as part of the string. For a basic arithmetic formula, the assignment is straightforward. With values 3 in B2 and 12 in C2, the formula =B2*C2 in D2 returns 36.

Range.Formula is not the same as a cell’s displayed result. If D2 contains =B2*C2, its Formula property returns the formula text, while its Value property returns the calculated result—in this example, 36. That distinction matters when a macro needs to preserve or inspect formulas rather than just copy their current outputs.

Basic syntax and a single-cell example

The general pattern is to identify the worksheet and range, then assign a formula string to the range’s Formula property. Qualifying the range with a worksheet variable avoids accidentally writing to whichever sheet happens to be active.

Sub WriteOneFormula()
    Dim ws As Worksheet
    Dim target As Range

    ' Identify the sheet and destination cell.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set target = ws.Range("D2")

    ' Write a formula using A1-style references.
    target.Formula = "=B2*C2"
End Sub

Before running any macro that changes a workbook, save a copy. Excel macros cannot be undone with Ctrl+Z, so a backup is the reliable way to recover the prior contents if the macro writes to the wrong cells. On Windows, open the Visual Basic Editor with Alt+F11; on Mac, the equivalent shortcut is Option+F11, though the keyboard shortcut can depend on the keyboard and macOS settings.

Writing formulas to a range of cells

You can assign a formula to a multi-cell range as well as to a single cell. Excel fills the target cells with formulas and adjusts relative A1 references for each position. This is handy when each row should calculate its own total or line amount.

For example, with quantity values in B2:B100 and unit prices in C2:C100, assigning =B2*C2 to D2:D100 fills each row with the corresponding multiplication. D2 refers to B2 and C2, D3 refers to B3 and C3, and so on. The first row’s illustrative values of 3 and 12 produce 36 in D2.

Sub FillLineAmounts()
    Dim ws As Worksheet
    Dim outputRange As Range

    ' Set the sheet and the rows that need formulas.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set outputRange = ws.Range("D2:D100")

    ' Relative references adjust for each destination row.
    outputRange.Formula = "=B2*C2"
End Sub

Use a bounded range when the intended row count is known. For a changing data set, a macro can find the last used row and build a range accordingly, but it should account for blank rows and for which column defines the data’s extent. A range that is too large may overwrite notes or existing formulas below the data.

Reading formulas and values

To inspect a formula, read the Formula property from a cell. To read the calculated result instead, use Value or Value2. Value2 returns the underlying value without the Currency and Date data types that Value may use; it is often a practical choice when a macro is working with raw numeric results.

With the Sales sheet’s D2 formula set to =B2*C2, reading D2.Formula returns the formula string, while reading D2.Value2 returns the calculated number 36 if B2 is 3 and C2 is 12. A cell containing a typed number rather than a formula has no formula to retrieve: its Formula property returns the cell’s contents as a value. Code that needs to distinguish formulas from constants can check the cell’s HasFormula property.

For a one-cell range, the result is easy to interpret. For a multi-cell range, Formula may return a two-dimensional Variant array containing the cells’ formulas or values. Code that reads an entire block should be prepared to handle an array, rather than assuming the result is a single string. If the macro only needs one formula, select a specific cell.

Formula versus FormulaR1C1

Formula uses A1-style references such as B2 or $B$2. FormulaR1C1 uses row-and-column notation such as R2C2 for B2, or RC[-2] for the cell two columns to the left on the current row. Both properties can write formulas; the right choice depends on how you want to describe the references.

FormulaR1C1 is particularly convenient when applying the same relative calculation to a range, because the formula can explicitly say “two columns left times one column left.” With a target range of D2:D100, the following assignment calculates each row’s B-column value multiplied by its C-column value:

Sub FillWithR1C1()
    Dim ws As Worksheet
    Dim outputRange As Range

    ' The output is in column D on each row.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set outputRange = ws.Range("D2:D100")

    ' RC[-2] is column B; RC[-1] is column C.
    outputRange.FormulaR1C1 = "=RC[-2]*RC[-1]"
End Sub

Use Formula when a formula is easier to understand in ordinary worksheet notation or when the formula text is already written in A1 style. Use FormulaR1C1 when constructing formulas programmatically, especially when relative offsets make a reusable pattern clearer. Absolute and mixed references are possible in either notation, but their notation differs.

Formula2 and dynamic-array formulas

In modern Excel versions that support dynamic arrays, Formula2 is available alongside Formula. Formula2 uses Excel’s array-evaluation behavior, while Formula retains legacy implicit-intersection behavior in relevant cases. This distinction can affect formulas that return multiple results or expressions that refer to ranges where Excel might otherwise reduce a result to one value.

For example, SORT can return a list of sorted values that spills into cells below the formula. With source values in A2:A10 and the intended output beginning in C2, the worksheet formula is =SORT(A2:A10). In VBA, put that formula in the top-left spill cell using Formula2 when the Excel version supports it:

Sub WriteSpillFormula()
    Dim ws As Worksheet
    Dim anchor As Range

    ' Put the dynamic-array formula in its top-left cell.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set anchor = ws.Range("C2")

    ' SORT is available in Microsoft 365 and Excel 2021 or later.
    anchor.Formula2 = "=SORT(A2:A10)"
End Sub

Enter a dynamic-array formula in its anchor cell, not in every cell where its results will appear. The surrounding spill area must be clear; existing content or merged cells can prevent the results from spilling and produce a #SPILL! error. Formula2 and dynamic-array function availability depends on the Excel version, so older installations may not expose the same behavior or functions.

Formula strings, quotes, and regional settings

VBA formula strings use normal programming-language quotation rules. A worksheet formula that includes a text string needs doubled quotation marks inside the VBA string. For example, to put a formula that displays the word Ready when A2 is not blank, the worksheet formula is =IF(A2<>"","Ready",""). Its VBA assignment is:

Sub WriteTextFormula()
    Dim ws As Worksheet

    ' Formula quotes are doubled inside the VBA string.
    Set ws = ThisWorkbook.Worksheets("Sales")
    ws.Range("D2").Formula = "=IF(A2<>"""",""Ready"","""")"
End Sub

Formula is intended for standard formula text, whereas FormulaLocal uses the formula language and separators used by the user’s localized Excel installation. If a macro must work across computers with different language settings, Formula is generally the more portable choice. FormulaLocal can be useful for a formula assembled from localized names or separators, but code that depends on it may need adapting for another locale.

When troubleshooting, check that the formula string starts with an equals sign, all VBA string quotes are correctly doubled, and the cell references point to the intended locations. A formula may be syntactically valid but still return an error such as #VALUE! if its referenced data has the wrong type. Range.Formula writes the expression; it does not guarantee that the expression’s inputs are suitable.

Common mistakes and practical safeguards

Using an unqualified Range: Range("D2") refers to the active sheet in common VBA contexts. Qualify it through a worksheet variable when the target sheet must be explicit.
Overwriting cells unintentionally: Confirm the destination address before assigning a formula, particularly when a macro fills a large range. Save a backup because Ctrl+Z cannot reverse macro changes.
Confusing formulas with results: Use Formula to access formula text and Value or Value2 for the calculated result. Copying only a value will not preserve the underlying calculation.
Using the wrong reference style: Formula expects A1 references; FormulaR1C1 expects R1C1 references. A formula written in one style should not be assigned as though it used the other.
Filling a spill area: For a dynamic-array formula, write to the anchor cell and leave the expected output cells clear. Do not fill the whole spill range with copies of the formula.
Assuming formulas recalculate immediately: Excel’s calculation mode can be manual. In that case, a newly written formula may not show an updated result until calculation occurs. Check Formulas > Calculation Options in Excel for Windows if results appear stale.

For large workbooks, avoid repeatedly selecting cells or activating sheets just to write formulas. Assigning a formula directly to a qualified range is simpler and reduces unnecessary interface operations. If the macro changes calculation settings to speed up a bulk update, it should restore the original settings even if an error occurs; leaving calculation manual can confuse later users of the workbook.

When to use Formula, Value, or copy-and-fill

Choose Formula when the destination should contain a live calculation that changes as referenced cells change. Choose Value or Value2 when the macro should store a fixed result rather than a continuing formula. For example, a report-generation macro might calculate a total, then retain only the number in a finalized export; a working sales sheet usually needs the formula so later edits update the total.

Excel’s normal fill operations are another option when users need an interactive way to extend a formula. In VBA, direct range assignment is usually clearer for a known destination. Copying a formula cell and using AutoFill can also carry formatting and other cell attributes, which may be desirable, but it can copy more than just the formula. Consider the side effects before choosing an approach.

For structured Excel tables, formulas may use table column names and can be easier for people to read than fixed cell addresses. Table behavior can automatically extend calculated-column formulas as rows are added. Whether to use a normal range formula or a table formula depends on how the workbook’s data is organized; avoid hard-coding a range that excludes future rows if the list is expected to grow.

Frequently Asked Questions

How do I put a formula in a cell using VBA?
Set the cell’s Formula property to a formula string beginning with an equals sign. For example, with a worksheet named Sales, Worksheets("Sales").Range("D2").Formula = "=B2*C2" writes a multiplication formula in D2. In a larger macro, qualify the worksheet explicitly as shown above.

What is the difference between Range.Formula and Range.Value?
Formula reads or writes formula text, while Value reads or writes the cell’s contents as a value. If D2 contains =B2*C2 and calculates to 36, Formula returns the expression and Value or Value2 returns 36. Assigning a number to Value does not create a live formula.

Can I apply one formula to many cells with VBA?
Yes. Assign a formula to a multi-cell range, such as D2:D100, and Excel adjusts relative A1 references for each cell. Check the destination range carefully so the assignment does not replace existing content outside the intended rows.

Should I use Formula or FormulaR1C1?
Use Formula for familiar A1 references such as B2, and FormulaR1C1 when relative row and column offsets make a repeated formula easier to express. For a formula in column D that multiplies the two cells to its left, the R1C1 expression is =RC[-2]*RC[-1].

Why does my VBA formula show an error or fail to spill?
Check the formula syntax, references, and source data first. Dynamic-array formulas also need a supported Excel version and an unobstructed spill area. If the formula is present but its displayed result seems outdated, Excel may be using manual calculation; inspect Formulas > Calculation Options.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.