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" <(E-Mail Removed)> wrote in message
news:02F293F8-D4DB-4CD7-91A4-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:02F293F8-D4DB-4CD7-91A4-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:02F293F8-D4DB-4CD7-91A4-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> news:02F293F8-D4DB-4CD7-91A4-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:19C0D3CA-7DDB-413A-A24A-(E-Mail Removed)...
> 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
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:19C0D3CA-7DDB-413A-A24A-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
> > news:02F293F8-D4DB-4CD7-91A4-(E-Mail Removed)...
> > > 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
> >
> >