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

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *