The Value, Formula, and Text Properties

Category: General VBA | [Item URL]

Student asks teacher. Teacher asks me:

I got an interesting question from a student in my VBA class today, one I'd never considered. I taught them that if you want to enter a formula in a cell, you use a statement like this:

Range("C1").Formula = "=SUM(A1:B1)"

Of course, that works. But the following appears to do the same thing:

Range("C1").Value = "=SUM(A1:B1)"

So the question is, Why do we need the Formula property at all?

These tw0 different properties exist because they can return different information when your code reads them.

• The Formula property returns the formula, if the cell has one. If the cell does not contain a formula, it returns the value in the cell. In the example above, the Formula property returns =(B3-B2)/B2 for cell B4.
• The Value property returns the raw, unformatted value in the cell. In the example, the Value property returns 0.213756613756614 for cell B4.

In addition, VBA provides the (read-only) Text property:

• The Text property returns the text that is displayed in the cell. If the cell contains a numeric value, this property includes all of the formatting. In the example, the Text property returns 21% (a text string) for cell B4.

Yet another relevant property:

• The Value2 property is just like the value property, except that it doesn't use the Date and Currency data types. Rather, this property converts Date and Currency data types Variants containing Doubles. If cell A1 contains the date 2/6/2009, the Value property returns it as a Date, while the Value2 property returns it as a double (i.e., 39850).