Get Formulas from Cells in Excel with VBA

Add to Favorites
Author:

Get a formula from a cell using VBA in Excel.

This method returns the actual formula instead of the output value.

Get the Formula of a Cell into a Macro

We use the Formula property for this.

To get the formula in cell A1, type this in a macro:

Range("A1").Formula

5f4c85a8b3d434417958fa3c838b85a7.jpg

I will put MsgBox in front of this line of code so we can see the output in Excel:

MsgBox Range("A1").Formula

Go back to Excel and run it on a cell that has a date and this is the result:

9fd379c46222835b5be956c846f42cc6.jpg

In cell A1, you can see the actual visible output, which is the date, and in the message box pop-up you can see the formula that was used to create the output.

Using Formula in VBA means that the macro will get the formula that we see in the message box.

Notes

This is pretty straightforward, if you want to get the value of a cell that is easy and you can read about it in the link.

Download the attached workbook to see this example in Excel.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Get Text from Comments in Excel Including the Author of the Comment - UDF
Macro: Output all text from a cell comment, including comment author, with this UDF in Excel. Thi...
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
Macro: UDF to count the number of words in a cell or range with a user-specified delimiter. ...
Loop through a Range of Cells in Excel VBA/Macros
Tutorial: How to use VBA/Macros to iterate through each cell in a range, either a row, a column, or ...
Select Ranges of Cells in Excel using Macros and VBA
Tutorial: This Excel VBA tutorial focuses specifically on selecting ranges of cells in Excel.  This...
Me Keyword in Excel Macros & VBA
Tutorial: The Me keyword in Excel VBA allows you to refer to either the current worksheet, workbook,...
Naming Cells in Excel to Make Using Formulas/Functions Easier
Tutorial: In this tutorial I am going to introduce the idea of Named Cells. A Named Cell is a cell w...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course