This Excel VBA tutorial explains how to use Formula Property in Excel VBA.
Formula Property in Excel VBA
There are two functions for Formula Property:
1. To set formula in a Range, like the way you type a formula in worksheet
2. To retrieve formula from a Range
Syntax of Formula Property
To set a formula:
Range.Formula = "=your formula"
To retrieve a formula:
variableName = Range.Formula
Remarks
You can also set a formula using Value Property
Range.Value = “=your formula”
But the difference is that you cannot retrieve formula using Value Property
You can also set a formula using R1C1 Reference Style, see the below article
Excel VBA FormulaR1C1 Property of Range
Example of using Formula Property
Line | VBA code | Result | Explanation |
1 | Range(“C2”).Formula= “=1+2” | A value “3” is shown in Range C2 | To set a formula for C2 as =1+2 |
2 | Msgbox(Range(“C2”).Formula) | =1+2 | To retrieve formula from C2 |
3 | Msgbox(Range(“C2”).value) | 3 | To retrieve value from C2 |
4 | Range(C3″).value = “=1+2” | A value “3” is shown in Range C3 | To set a formula for C3 as =1+2 |
Outbound References
http://msdn.microsoft.com/en-us/library/office/ff838835%28v=office.15%29.aspx