Why use Range.Formula in VBA for Excel 2003 instead of Range.Value?

Question

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

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

and

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

do the same for me (when I select a cell and execute each of the above code segments separately in separate cells). Both of them show a yes value in the cell, and the formula is saved when I click to view each cell.

I looked at the Microsoft Dev Center for information:

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 of the Value property applicable to the Range object.)

I also googled “Why use Range.Formula instead of Range.Value VBA Excel” and could not find anything related to my question.

Some people said use Range.Value .

Some say use Range.Formula when stack overflows (Sorry, I lost the link to the exact question ...)

Answer 1

In terms of data values:

Imagine you have integers doubles for certain calculations, and if you use .formula, you will be screwed. Because .FORMULA always returns a string. (Any value set in .formula without is a simple value, not a formula). If you do not have exception handling and additional components ready in your application. Whereas .VALUE returns your data type data as you expect.

From the point of view of obtaining the formula: Value is a one-way way if you set the formula, but do not extract it.

So, you see that these methods are introduced for some reason and help you work with the desired required properties. :-)

Answer 2

This is not so strong when writing real formulas for cells, but instead of .Value .Formula when filling a range with values ​​from an array of options containing several data types (especially non-US dates), it gives much more reliable results.

I do not remember the exact problem that made me discover this as a workaround, since there were many moons ago; and I have not tested to find out what the actual differences are. I just switched as soon as I discovered the advantage and have been doing it since then.

Under the assumption, I would say that recording using the .Formula property .Formula not allow Excel to automatically try to convert number formats for any cells in the target range that do not yet have a dialing number (and possibly even those that do).