Conceptual question on .Value and .Formula properties

[Guest]

One question I have on the Cell class of Excel...

Which property is better to assign when you need to programmatically write
something in a cell?

Formula or Value?

Should this choice be made on the nature of what we want to store in a cell.
In other words, if we want to store 56 then it's better to assign the .Value
property and when we want to assign =34+69 or =$A$3^3 then it's better to use
..Formula instead?

Ok, but then why not consistently use the .Formula property then? .Formula
works in all cases (e.g. ActiveSheet.Cells(1).Formula = 98 perfectly works),
but I keep on reading in web sites and manuals that .Value is used instead.

Is there a real difference?

My personal preference would be that .Formula is read/write and can be
assigned. Value would be readonly and is calculated by Excel exclusively, as
a result of the cell's Formula.

Thank you.
Stefano Gatto

 

[Jim Rech]

There is no Cell class. There is a Range object that includes one or more
cells.

The real difference is in reading a cell.

Jim

 

[IanKR]

Which property is better to assign when you need to programmatically

write something in a cell?

Formula or Value?

If you're writing to a cell it doesn't matter, but if you're looking at a
cell that contains a formula (say, =A1+A2), the difference is that Formula
returns "=A1+A2" (without quotes); whereas Value returns what that formula
evaluates to.

 

[Tom Ogilvy]

Do what you want. If you find a problem adjust your thinking.

 

[Guest]

Yes, I saw that right after having posted my question... The class is range
and .Value applies to the most top-left cell I imagine.

 

[Guest]

Ok, thank you for confirming my thought. There is no difference in Write
mode, there is just one in Read mode.

Still all of the examples I see (including Microsoft's ones) use .Value to
assign numbers and strings to cells. If it's so irrelevant then I would
expect more of a 50-50% split...

Thank you for your answer and have a nice day.

 

[Guest]

Thanks for the note Tom. In this particular case I applied your advice for 14
years (since Excel 5), but eventually I just was afraid to miss an important
aspect of these 2 properties.

Have a nice day.

Stefano Gatto

 

[Peter T]

There is a slight difference in as much as the formula is always a string,
so a little more coercion may be involved behind the scenes, no doubt
trivial for write. When reading the formula of a non formula cell it returns
the Value2 property. I would stick with Value for read/write unless Value2
is required.

Regards,
Peter T

 

[Tom Ogilvy]

Activecell.Formula = 100

produced a number stored in the cell for me. Are you only talking about
reading - it isn't clear (to me).

 

[Guest]

..Value does not apply just to the top left cell

Sub test()
Range("A1:A10").Value = 10
End Sub

Sub test2()
Dim varTemp As Variant

varTemp = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
Range("A1:A10").Value = Application.Transpose(varTemp)
End Sub

 

[Peter T]

As I see it the Formula property is a string for both for read and write.

With ActiveCell

..ClearFormats
..Value = CStr(123) ' or simply "123"
Debug.Print TypeName(.Value) ' double
Debug.Print TypeName(.Formula) ' string
End With

I would assume if writing a true number to the formula property it is first
coerced to a string (as above) then coerced back to a number in the cell.

I can't prove that and it does contradict help which states Formula is
"Read/write Variant for Range objects". Yet help cannot be correct as
regards Read. Formula always returns a String (of the Value2 property if not
a formula cell and not empty) whatever the cell contents, even if empty.

Regards,
Peter T