VBA Excel's VBA
VBA Configuring
VBA VBE Editor
VBA The Macro Recorder
VBA First Macro
VBA Comments
VBA Buttons with Macro

VBA Variables
VBA Operators
VBA If Then...Else
VBA Select Case
VBA For...Next
VBA Do Loop
VBA Sub And Function
VBA Strings
VBA Events
VBA Array
VBA For Each

It is possible to use Excel's ready-to-use formulas through VBA programming. These are properties that can be used with Range or Cells.

Formula adds predefined Excel formulas to the worksheet. These formulas should be written in **English** even if you have a language pack installed.

```
Range("F2").Formula = "=SUM(B2:C7)"
Range("F3").Formula = "=SUM($B$2:$C$7)"
```

Do not worry if the language of your Excel is not English because, as in the example, it will do the translation to the spreadsheet automatically.

You can insert multiple formulas at the same time using the Formula property. To do this, simply define a Range object that is larger than a single cell, and the predefined formula will be "dragged" across the range.

"Dragging" manually:

"Dragging" by VBA:

```
Range("D2:D7").Formula = "=SUM(B2:C2)"
```

Another way to perform the same action would be using FillDown method.

```
Range("D2").Formula = "=SUM(B2:C2)"
Range("D2:D7").FillDown
```

FormulaLocal adds predefined Excel formulas to the worksheet. These formulas, however, should be written in the **local language** of Excel (in the case of Brazil, in Portuguese).

```
Range("F2").FormulaLocal = "=SOMA(B2:C7)"
```

Just as the Formula property, **FormulaLocal** can be used to make **multiple formulas**.

FormulaR1C1, as well as Formula and FormulaLocal, also adds pre-defined Excel formulas to the spreadsheet; however, the use of relative and absolute notations have different rules. The formula used must be written in English.

FormulaR1C1 is the way to use Excel's ready-to-use formulas in VBA by easily integrating them into loops and counting variables.

In the notations:

Rrefers torows, in the case of vertical displacementCrefers tocolumns, in the case of horizontal displacementNsymbolizes anintegerthat indicates how much must beshiftedin number of rows and/or columns

**Relative notation**: Use as reference the Range that called it

The format of the relative formula is: R[

N]C[N]:R[N]C[N].

```
Range("F2").FormulaR1C1 = "=SUM(R[0]C[-4]:R[5]C[-3])" 'Equals the bottom row
Range("F2").FormulaR1C1 = "=SUM(RC[-4]:R[5]C[-3])"
```

When N is omitted, the value 0 is assumed.

In the example, RC[-4]:R[5]C[-3] results in **"B2: C7"**. These cells are obtained by: receding 4 columns to the left **RC[-4]** from Range("F2") to obtain **"B2"**; and 5 lines down and 3 columns to the left **R[5]C[-3]** from Range("F2") to obtain **"C7"**.

**Absolute notation**: Use the start of the spreadsheet as a reference

The format of the relative formula is: R

NCN:RNCN.

```
Range("F2").FormulaR1C1 = "=SUM(R2C2:R7C3)" 'Results in "$B$2:$C$7"
```

N negative can only be used in relative notation.

The two notations (relative and absolute) can be merged.

```
Range("F2").FormulaR1C1 = "=SUM(RC[-4]:R7C3)" 'Results in "B2:$C$7"
```

Excel formulas can also be accessed by object WorksheetFunction methods.

```
Range("F2") = WorksheetFunction.Sum(Range("B2:C7"))
```

Excel formulas can also be accessed similarly to functions created in VBA.

The formulas present in the WorksheetFunction object are all in English.

One of the great advantages of accessing Excel formulas this way is to be able to use them more easily in the VBA environment.

```
MsgBox (WorksheetFunction.Sum(3, 4, 5))
Expense=4
MsgBox (WorksheetFunction.Sum(3, 4, 5,-Expense))
```

To list the available Excel formulas in this format, simply type **WorksheetFunction.** that automatically an option menu with all formulas will appear:

**Suggested Exercise**

VBA Excel's VBA
VBA Configuring
VBA VBE Editor
VBA The Macro Recorder
VBA First Macro
VBA Comments
VBA Buttons with Macro

VBA Variables
VBA Operators
VBA If Then...Else
VBA Select Case
VBA For...Next
VBA Do Loop
VBA Sub And Function
VBA Strings
VBA Events
VBA Array
VBA For Each

SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

© 2019 SuperExcelVBA | ABOUT

Thank you for contributing. A message was sent reporting your comment.