Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conceptual question on .Value and .Formula properties
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conceptual question on .Value and .Formula properties
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conceptual question on .Value and .Formula properties
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conceptual question on .Value and .Formula properties
..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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conceptual question on .Value and .Formula properties
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conceptual question on .Value and .Formula properties
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conceptual question on .Value and .Formula properties
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conceptual question on .Value and .Formula properties
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conceptual question on .Value and .Formula properties
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conceptual question on .Value and .Formula properties
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conceptual question on .Value and .Formula properties
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conceptual question | Excel Programming | |||
Conceptual Question about ReDim | Excel Programming | |||
Conceptual Question about ReDim | Excel Programming | |||
new guy with a conceptual question | Excel Programming | |||
Conceptual Problem with DAO/ADO Recordset | Excel Programming |