Formulas in Excel VBA – A Beginners Guide

In this post, you will be learning about formulas in Excel VBA – an in-depth explanation for beginners to learn Excel VBA.

Formulas in Excel

Formulas are used to perform calculations. In Excel, a formula is an expression that operates on values in a range of cells or a cell. Let’s see about formulas in depth.

How to Write Formulas in Excel VBA?

The Formulas in Excel VBA can be written in three methods:

• Using Macro Recorder
• R1C1 Property
• The Formula Property

First we need to enable the Developer tab to use the VBA and Macro features.

To enable the Developer Tab,

• Under the “FILE” menu tab, select “Options.”
• In the new window, Click on Customize Ribbon
• Check the Developer option
• Click on OK button

Now, you can see the Developer tab in the ribbon.

Recording Cell Formulas with Macro Recorder

We can write formulas using the macro recorder option in Excel. Follow these steps to write formulas using macro reader.

• Turn on the macro recorder in the Developer tab

• Type your formula or edit an existing formula.
• Press Enter to enter the formula.
• Successfully, the code has been recorded in the macro.

You can use this macro to run the formula any number of times in the workbook. Or you can add the macro to the toolbar and use it in any workbook.

Formula R1C1 Property in Excel VBA

When you use the macro recorder for writing formulas, you can see that it creates code with the FormulaR1C1 property.

R1C1 style notation allows us to create both relative (A1), absolute ($A$1), and mixed ($A1, A$1) references in our macro code.

R1C1 stands for Rows and Columns.

Formula Property in Excel VBA

The Formula property is a member of the Range object in VBA. It can be used to create a formula for single cells or a range of cells.

Requirements for the formula property in Excel VBA

• The formulae should be a string of text that is wrapped in quotation marks. 
•  The value of the formula must start and end in quotation marks.
• The formula string must start with an equal sign = after the first quotation mark.

Calculate Command in Excel VBA

The calculate command is used to recalculate the calculations in the workbook. It is used in different situations, like for calculating sheet only, for range, etc.

Here is the list of features available in the calculate command and it code.

Calculate Sheet Only

For recalculating the active sheet:

ActiveSheet.Calculate

For recalculating Sheet1:

Sheets("Sheet1").Calculate

Calculate Range

For calculating a certain range

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

Calculate Individual Formula

For recalculating an individual cell formula:

Range("a1").Calculate

Calculate Workbook

In excel, there is no VBA option to calculate an entire workbook. In order to perform this action we can use the Calculate command:

Calculate

Or you can also try selecting all sheets at once and calculating the activesheets:

ThisWorkbook.Sheets.Select
ActiveSheet.Calculate