Menu
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.
Some others say use Range.Formula on stack overflow (Sorry I lost the reference to the exact question...)
266
Range is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns.
The Cells and Range functions let you tell your VBA script exactly where on your worksheet you want to obtain, or place data. The main difference between the two cells is what they reference. The VBA cells function usually references a single cell at a time, while Range references a group of cells at once.
If the Excel VBA Range object you want to refer to is a single cell, the syntax is simply “Range(“Cell”)”. For example, if you want to make reference to a single cell, such as A1, type “Range(“A1″)”.
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. :-)
115
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
