3 Tips for Writing Formulas with VBA Macros in Excel
Bottom line: Learn 3 tips for writing and creating formulas in your VBA macros with this article and video.
Skill level: Intermediate
Download the File
Download the Excel file to follow along with the video.
Automate Formula Writing
Writing formulas can be one of the most time consuming parts of your weekly or monthly Excel task. If you're working on automating that process with a macro, then you can have VBA write the formula and input it into the cells for you.
Writing formulas in VBA can be a bit tricky at first, so here are 3 tips to help save time and make the process easier.
Tip #1: The Formula Property
The Formula property is a member of the Range object in VBA. We can use it to set/create a formula for a single cell or range of cells.
There are a few requirements for the value of the formula that we set with the Formula property:
- The formula is 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.
Here is a simple example of a formula in a macro.
Sub Formula_Property() 'Formula is a string of text wrapped in quotation marks 'Starts with an = sign Range("B10").Formula = "=SUM(B4:B9)" End Sub
The Formula property can also be used to read an existing formula in a cell.
Tip #2: Use the Macro Recorder
When your formulas are more complex or contain special characters, they can be more challenging to write in VBA. Fortunately we can use the macro recorder to create the code for us.
Here are the steps to creating the formula property code with the macro recorder.
- Turn on the macro recorder (Developer tab > Record Macro)
- Type your formula or edit an existing formula.
- Press Enter to enter the formula.
- The code is created in the macro.
If your formula contains quotation marks or ampersand symbols, the macro recorder will account for this. It creates all the sub-strings and wraps everything in quotes properly. Here is an example.
Sub Macro10() 'Use the macro recorder to create code for complex formulas with 'special characters and relative references ActiveCell.FormulaR1C1 = "=""Total Sales: "" & TEXT(R[-5]C,""$#,###"")" End Sub
Tip #3: R1C1 Style Formula Notation
If you use the macro recorder for formulas, you will notices 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.
For relative references we specify the number of rows and columns we want to offset from the cell that the formula is in. The number of rows and columns are referenced in square brackets.
The following would create a reference to a cell that is 3 rows above and 2 rows to the right of the cell that contains the formula.
Negative numbers go up rows and columns to the left.
Positive numbers go down rows and columns to the right.
We can also use R1C1 notation for absolute references. This would typically look like $A$2.
For absolute references we do NOT use the square brackets. The following would create a direct reference to cell $A$2, row 2 column 1
with mixed references we add the square brackets for either the row or column reference, and no brackets for the other reference. The following formula in cell B2 would create this reference to A$2, where the row is absolute and the column is relative.
When creating mixed references, the relative row or column number will depend on what cell the formula is in.
It's easiest to just use the macro recorder to figure these out.
FormulaR1C1 Property versus Formula Property
The FormulaR1C1 property reads the R1C1 notation and creates the proper references in the cells. If you use the regular Formula property with R1C1 notation, then VBA will attempt to put those letters in the formula, and it will likely result in a formula error.
Therefore, use the Formula property when your code contains cell references ($A$1), the FormulaR1C1 property when you need relative references that are applied to multiple cells or dependent on where the formula is entered.
If your spreadsheet changes based on conditions outside your control, like new columns or rows of data are imported from the data source, then relative references and R1C1 style notation will probably be best.
I hope those tips help. Please leave a comment below with questions or suggestions.