Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite

I was wondering why in VBA code for Excel 2003 do we even need to use Range.Formula to write a formula to a cell instead of just using Range.Cell? They both write Strings to the cell that become forumlas, and the formula works (from what I've tested).

ActiveCell.Value="=If(True,""yes"",""no"")"

and

ActiveCell.Formula="=If(True,""yes"",""no"")"

do the same thing for me (when I select a cell and execute each of the above code segments separately in separate cells). They both show the "yes" value in the cell and the formula is stored when I click to view each cell.

I looked on Microsoft's Dev Center for info:

Range.Formula Range.Formula Property for Excel 2013 (Excel 2003 did not have a page for this Property)

Range.Value Property for Excel 2003 (Expand the "Value property as it applies to the Range object." heading

I also googled "Why use Range.Formula instead of Range.Value VBA Excel" and couldn't find anything that related to my question.

Some people said, use Range.Value: \http://www.ozgrid.com/forum/showthread.php?t=68585\

Some others say use Range.Formula on stack overflow (Sorry I lost the reference to the exact question...)

share|improve this question
1  
I've been trying to find a counterexample of this, but cannot. You can even enter R1C1 formulas via the Range.Value. The primary difference is that you cannot use .Value to retrieve the formula. You may be able to input formulas via the Value, but you cannot retrieve it that way. (Would have bothered to put that as an answer, but it doesn't answer your question.) – Daniel Cook Dec 3 '12 at 17:12
Thank you for your answer. It clarifies that the main difference between the 2 properties are retrieval. .Formula returns a String, .Value returns the respective data types. With respect to writing, it doesn't seem to make a difference – Mr_Moneybags Dec 4 '12 at 20:11

1 Answer

up vote 1 down vote accepted

In terms of values of data:

Imagine you have integers, doubles for certain calculations and if you use .formula you gonna get screwed. Because .FORMULA always return a String. (Any value set to .formula without is a plain value not a formula) Unless you have exception handling and the extras ready in your code. Whereas .VALUE returns your data's data type as you expect them to be.

In terms of retrieving formula: Value is one way road in case to set formula but not to retrieve.

So you see these methods are introduced for a reason and help you to work with right properties you require. :-)

share|improve this answer
I find this part confusing: "(Any value set to .formula without is a plain value not a formula)" What exactly do you mean by it?. Also, thank you for your answer. It clarifies that the main difference between the 2 properties are retrieval. .Formula returns a String, .Value returns the respective data types – Mr_Moneybags Dec 4 '12 at 20:06
1  
Missed out =. :-P sorry. So any formula string set to .formula is a plain value without = in front. – bonCodigo Dec 5 '12 at 3:05
Ah okay, makes sense now. Thanks! – Mr_Moneybags Dec 5 '12 at 23:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.