J-Walk & Associates, Inc.

New Tip: Understanding Three Properties

I posted a new tip, The Value, Formula, and Text Properties.

It was in response to a question I got from a teacher. I thought others might benefit from the answer.

- Reader Comments -

Following are comments in response to this item.
The most recent comment is at the bottom.

1. By chip. Comment posted 05 February, 2009 5:22pm

   I'll add to the confusion by pointing out that can also use this statement to enter a formula:
   
   Range("C1").Value = "=SUM(A1:B1)"
   
   I think you meant to put Range("C1").Text
2. By John Walkenbach. Comment posted 05 February, 2009 6:46pm

   Thanks, chip. Typo fixed. That's why we famous authors have technical editors.
   
   But seriously... those types of mistakes seem to be happening with increasing frequency. I used to be pretty good at proofreading, but old age is taking its toll.
3. By sam. Comment posted 05 February, 2009 8:21pm

   And then we have
   Range("C1").FormulaR1C1 = "=SUM(AMT)"
   Range("C1").FormulaLocal = "=SUM(AMT)"
   Amt - Range Name
   
   Or
   
   Range("C1").FormulaR1C1 ="Hello"
   Range("C1").FormulaLocal = "Hello"
4. By Dick Kusleika. Comment posted 06 February, 2009 6:38am

   Also, to get truly unformatted values, use Value2. Try it with a date.
5. By John Walkenbach. Comment posted 06 February, 2009 10:54am

   I always forget about Value2. I've updated that tip (and also corrected an error). The Text property is read-only.
6. By Raymond Allan. Comment posted 10 July, 2009 1:16pm

   Interesting findings, I have a bad habit of doing:
   
   Range("H5") = "=SUM(A2:G2)"
   
   Should I change this to be Range("H5").Formula = ...