CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,675

    Question Excel 2003 VBA Range object: writing to properties .Value and .Text

    Just the other day I saw in Excel VBA's help files that the .Value and .Text propertiers of the Range object are writable. On first sight I was asking myself what this would be good for, is writing to the .Formula property not enough?

    Then I did some quick experiments (with a Range containing a single cell if that matters): Data I wrote to the .Value property was immideately propagated back to .Formula and in turn forward to .Text. An attempt to write to the .Text property raised my favorite error 400.

    So again: what is it good for to write to these two properties?

    Maybe they behave differently when used in the Calculate event handler of a worksheet, allowing to apply custom calculations (writing to .Value) or custom formatting (writing to .Text)? I didn't actually try that yet because it looked a bit more complicated; I have not yet used any event handlers in VBA (except for those in a UserForm class). Maybe somebody has already done things like that an can tell me.

    I am not about to need that right now, but it made me curious and I still keep learning about VBA. So, any information would be appreciated.

  2. #2
    Join Date
    Jul 2010
    Posts
    5

    Re: Excel 2003 VBA Range object: writing to properties .Value and .Text

    The reason for the error is because the Range object's Text property is read-only, regardless of what the helpfile says.

    When you're writing, you can use the Formula property for both formulas and values/text, and it will work the same for values/text as the Value property.

    So why have both? Because they're both needed for reading cells with formulas. Formula returns the formula itself, while Value returns the return value of the formula. And, Text returns the return value of the formula, as formatted by the cell's number formatting.

    So if you enter "=5" in a cell and format it as currency:

    Formula returns "=5"
    Value returns 5
    Text returns "$5.00"


    Greg

  3. #3
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,675

    Re: Excel 2003 VBA Range object: writing to properties .Value and .Text

    Greg,

    thank you for clarifying that. The distinction between these three properties when it comes to reading are described pretty clear in the help, including some examples. I was just unsure about writing.

    When help's claim that .Text is writable was wrong, I won't worry though. I really can live just fine without writing to that one.

    When writing to both .Formula and .Value have exactly the same effect, this doesn't bother me either. Maybe having both of them writable could become useful when it comes to virtual functions, but VBA dos not have something like that anyway...

    BTW, some months ago I had another strange encounter with error 400: I started writing some VBA code for a mid-size Excel file containing production data. When I finishd that day, I saved the file under another name as I didn't want to make further experiments with the live data. When I opened it to continue the next day, writing to the .Formula property of the Series object (that holds a data series for a chart) had suddenly stopped working, giving me that well-known error. It took me an entire day not to figure out why it did that. Then, when I woke up next morning after a night in which I was happy to get some sleep at all (this was big-time bugging me!), I had a spontaneous Idea what it might be while still lying in bed before getting up. And this actually was it: The object dosn't like spaces in the Excel file name! And that although there is no obvious relation between that object and the file name at all on first sight.

    BTW again: The object doesn't like hyphens in the file name either, but this gives you a different error message (and number). But once bitten already, I figuerd that out quickly that time.

  4. #4
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Excel 2003 VBA Range object: writing to properties .Value and .Text

    It allows quotes in a file name. "d:\My Documents\Is Here"
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  5. #5
    Join Date
    Jul 2010
    Posts
    5

    Re: Excel 2003 VBA Range object: writing to properties .Value and .Text

    Just to add -- probably the reason the Range object's Text property is not writable is because it could be ambiguous.

    The Range object's Text property is derived from the Range's Value property, as modified by the Range's NumberFormat property. In my example above, if the cell's value is 5, and currency number formatting is applied, then the Text property returns "$5.00".

    But if the Range object's Text property was writable, what exactly would this mean?

    MyCell.Text = "6"

    Changing the value from 5 to 6 is easy enough, but would it also mean that Excel should remove the currency number formatting?

    And that's just an easy, non-ambiguous example. Due to the wide range of things that can be done with number formatting, even to the point of making the value appear to be something different than it is, we could come up with examples where it was not clear what Excel should set the value and number formatting to. Restricting the programmer to Formula, Value, and NumberFormat removes the ambiguity.

    Greg

  6. #6
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,675

    Re: Excel 2003 VBA Range object: writing to properties .Value and .Text

    David,

    thanks for your advice. Yes, the object could get into direct (and obvios) interaction with the file name when accessing data in a workbook file different from the one the VBA code (or the Chart object) is in. But when I encountered the problem this was not the case: Everything resides happyly together in a single Excel file. How could I use quotes in Excel GUI's Save or Save As commands? The VBA code didn't get to see the file name at all.

    Greg,

    my thought was that Excel's chain from the formula to the final output representing the result might be broken intentionally, in order to apply some formatting that could not be done with Excel's formatting capabilities alone. For e.g. hex numbers: There is a worksheet funktion to express hex numbers in the form of a string, but no formatting option. You need two cells to output a hex value, at least of a non-constant, while a formatting option (custom or not) could do this in a single cell.

    And programming isn't that much fun anymore when you don't even have the opportunity to create some chaos...
    Last edited by Eri523; July 2nd, 2010 at 03:02 PM. Reason: Saw the typo one second _after_ hitting the post button...

  7. #7
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Excel 2003 VBA Range object: writing to properties .Value and .Text

    Well, a script, or even a batch file.
    Code:
    d:
    cd "\temp"
    foo
    c:
    cd "\docs\"
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  8. #8
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,675

    Re: Excel 2003 VBA Range object: writing to properties .Value and .Text

    Quote Originally Posted by dglienna View Post
    Well, a script, or even a batch file. [...]
    David, sorry, but I don't see how this is related to my most recent post or even anything else in this thread. Are you sure you posted that in the right thread? (Although I can't imagine any way for a post to go into the wrong thread...)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured