formula in vba
 

Range("a10") = "= a2 + a3 + a5"
Range("a12").Formula = "=a2+a3+a5"
both appear to give correct result. Then on what conditions the property
"formula " is to be used.

formula in vba
 

Whe n you don't spesify what property to be used, the object's default will
be used. For cells that's Value. But what happens here is that Excel
recognizes a value starting with = to be a formula, so she makes it a
formula.

So in this case it makes no difference for entry, but for retrieval and
manipulation it's crucial. Note the last part of this little demo:

Sub test()
MsgBox "Will execute: Range(""a10"") = ""= a2 + a3 + a5"""
Range("a10") = "= a2 + a3 + a5"

MsgBox "Range(""a10""): " & Range("a10"), , _
"Not what you said a10 it should be ?"

MsgBox "Range(""a10"").Formula: " & _
Range("a10").Formula & Chr(10) & _
"Range(""a10"").Value: " & _
Range("a10").Value, , _
"Note the difference:"


MsgBox "Now let's do something stupid:" & Chr(10) & _
"Will execute: Range(""a10"") = Range(""a10"")"

Range("a10") = Range("a10")

MsgBox "Range(""a10"").Formula: " & _
Range("a10").Formula & Chr(10) & _
"Range(""a10"").Value: " & _
Range("a10").Value, , "Formula is destroyed:"
End Sub
--
HTH. Best wishes Harald
Excel MVP
Followup to newsgroup only please

"R.Venkataraman" skrev i melding
...

Range("a10") = "= a2 + a3 + a5"
Range("a12").Formula = "=a2+a3+a5"
both appear to give correct result. Then on what conditions the property
"formula " is to be used.

formula in vba
 

thanks.

"Harald Staff" wrote in message
...
Whe n you don't spesify what property to be used, the object's default will
be used. For cells that's Value. But what happens here is that Excel
recognizes a value starting with = to be a formula, so she makes it a
formula.

So in this case it makes no difference for entry, but for retrieval and
manipulation it's crucial. Note the last part of this little demo:

Sub test()
MsgBox "Will execute: Range(""a10"") = ""= a2 + a3 + a5"""
Range("a10") = "= a2 + a3 + a5"

MsgBox "Range(""a10""): " & Range("a10"), , _
"Not what you said a10 it should be ?"

MsgBox "Range(""a10"").Formula: " & _
Range("a10").Formula & Chr(10) & _
"Range(""a10"").Value: " & _
Range("a10").Value, , _
"Note the difference:"


MsgBox "Now let's do something stupid:" & Chr(10) & _
"Will execute: Range(""a10"") = Range(""a10"")"

Range("a10") = Range("a10")

MsgBox "Range(""a10"").Formula: " & _
Range("a10").Formula & Chr(10) & _
"Range(""a10"").Value: " & _
Range("a10").Value, , "Formula is destroyed:"
End Sub
--
HTH. Best wishes Harald
Excel MVP
Followup to newsgroup only please

"R.Venkataraman" skrev i melding
...

Range("a10") = "= a2 + a3 + a5"
Range("a12").Formula = "=a2+a3+a5"
both appear to give correct result. Then on what conditions the property
"formula " is to be used.