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

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

Is there a real difference?

The real difference is in reading a cell.

Jim
"Stefano Gatto" wrote in message
...
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

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.

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

--
Regards,
Tom Ogilvy


"Stefano Gatto" wrote in message
...
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

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.
--
Stefano Gatto


"Jim Rech" wrote:

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

Is there a real difference?

The real difference is in reading a cell.

Jim
"Stefano Gatto" wrote in message
...
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

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.
--
Stefano Gatto


"IanKR" wrote:

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.

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


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Stefano Gatto" wrote in message
...
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

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

"Stefano Gatto" wrote in message
...
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.
--
Stefano Gatto


"IanKR" wrote:

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.

Activecell.Formula = 100

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

--
Regards,
Tom Ogilvy


"Peter T" <peter_t@discussions wrote in message
...
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

"Stefano Gatto" wrote in message
...
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.
--
Stefano Gatto


"IanKR" wrote:

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.

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


"Stefano Gatto" wrote:

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.
--
Stefano Gatto


"Jim Rech" wrote:

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

Is there a real difference?

The real difference is in reading a cell.

Jim
"Stefano Gatto" wrote in message
...
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

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


"Tom Ogilvy" wrote in message
...
Activecell.Formula = 100

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

--
Regards,
Tom Ogilvy


"Peter T" <peter_t@discussions wrote in message
...
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

"Stefano Gatto" wrote in
message
...
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.
--
Stefano Gatto


"IanKR" wrote:

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.