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).

Search for Tips

All Tips

Browse Tips by Category

Tip Books

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

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

© Copyright 2019, J-Walk & Associates, Inc.
Privacy Policy