The Value, Formula, and Text Properties
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).
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks