Excel VBA Formula Property of Range

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

 

 

Leave a Reply

Your email address will not be published.