When getting cell content using Excel VBA, you usually use the .Value property to retrieve the calculation result.
However, if a function or formula is set in the cell, you might want to get the “actual formula itself” rather than the result.
In this article, I will introduce how to use the .Formula property to retrieve cell formulas in VBA.
Basic Syntax: Getting Formulas with the Formula Property
CellObject.Formula
By using this property, you can retrieve the function or calculation string entered in the specified cell exactly as it is.
VBA Code Example to Check Formula and Value Simultaneously
The following sample retrieves both the value and the formula set in the active cell and displays them in a message box.
Sub ShowFormulaAndValue()
With ActiveCell
MsgBox "Current Value: " & .Value & vbCrLf & _
"Set Formula: " & .Formula
End With
End Sub
Explanation:
- .Value: Retrieves the actual calculation result displayed in the cell.
- .Formula: Retrieves the formula entered in the cell as a string.
- vbCrLf: This is a line break code. It is used to display the message in two lines within the message box.
What Happens If There Is No Formula?
Even if no formula is entered in the cell, .Formula still works and returns the normal value (string or number) as a string.
However, since it is not a formula, it will not start with an equal sign (=).
Examples of Use Cases
- Checking function formulas set in cells (e.g.,
=SUM(A1:A10)). - Creating a tool to list all formulas on a sheet.
- Saving the original content before automatically replacing or editing formulas.
- Branching processing based on whether a formula exists (e.g.,
If Left(.Formula, 1) = "=").
Summary | Distinguish Between Formulas and Values for Automation
In Excel VBA, .Value retrieves the calculation result, while .Formula retrieves the formula itself.
It is important to clearly understand this difference and use the appropriate property depending on the situation.
- Use .Value for data output or verification.
- Use .Formula for validating or managing function formulas.
Using them for their specific purposes allows you to create more flexible VBA macros. Retrieving formulas is simple, yet it becomes a very powerful tool in system development and error detection processes.
