A Case for Value as a Default Property

Charles Williams had an interesting post1 the other day about the Text, Value, and Value2 properties of the Range object. In it, he wrote:

So .Value2 really should be the default, and is definitely the one to use 99% of the time.

I disagree. I never use Value2.

The Value2 property returns a Double data type (64-bit floating point). The Value property returns the same thing except when the cell is formatted as Date or Currency. When it’s currency, Value returns a Currency data type (64-bit scaled integer), and when it’s a date, Value returns a Date date type (another 64-bit floating point). Who cares (besides Charles and me) and what does it mean?

Let’s imagine that we’re writing our own spreadsheet program to get a better understanding of what Excel is doing. I don’t actually know what Excel does under the hood, so this is all conjecture. In our spreadsheet application that we’re writing from scratch, we’ll store all numbers in memory as IEEE double precision floating point number, or Doubles. There is a case when the user may want more calculation accuracy than floating point allows and is willing to sacrifice a little precision (I’m looking at you fellow accountants). To offer that feature, we’ll allow the user to identify certain numbers as scaled integers with 15 digits to the left of the decimal and four digits to the right. Let’s call that data type Currency. We’re still going to store the value as a Double, but we’ll do the conversion prior to any calculations to ensure the accuracy.

As spreadsheet writers, we have a decision to make. I said we’d allow the user to identify certain numbers as Currency, but how exactly do they identify it. We could add a property called DataType to the Range class. OK, but how does the user set that property? Most Range properties are set in the Format Cells dialog box. We have a few tabs on that dialog already, so let’s add another one called Data Type. The user can set the data type and we’ll do the floating point to scaled integer conversion when the DataType property is set to Currency. That’s when the usability people come in.

The usability people begin by hurling epithets at us regarding our lack of sex lives and penchant for role playing games. They say that normal people (i.e. non-programmers) don’t have any idea what a “data type” is and if we make them learn, our sales will go down by 14.1% (a totally made up number because marketing people can’t do math). Surely, they go on, there is a better way to identify Currency values. Then they get out the corporate programming guidelines and remind us that no dialog box can have more than six tabs. We already have six tabs on the Format Cells dialog, so we can’t add another (Tools – Options hasn’t been invented yet).

Time to compromise. One of our bright, young interns suggests that we make Currency a format. As programmers, we are incensed. We already have an Accounting format and a Currency format will be confusing. And besides, Currency is a data type, not a format. Due to our lack of persuasiveness or the caffeine withdrawals, we agree to add it is a format. Because of the Currency debacle we go from two levels to three levels for data display. The bottom level is the raw Double that’s stored in memory, just like before. The top level is the text representation of the number with all the commas and periods and such, just like before. We need to add a pre-processor level. At this level, we’ll check to see if the Range object has it’s NumberFormat property set to Currency and, if so, we’ll convert from floating point to scaled integer. Once converted, we’ll send to the formatting layer to add the text goodies.

The deed is done and we all feel a little dirty. We code the changes and send them over to the VBA folks. The guy building the object model already has a Value property and a Text property. Now that we’ve added another layer, he doesn’t know which value to return for the Value property. There’s really not much debate – it has to be the value that comes from the pre-processor. If people “format” a value as Currency, they’re going to expect a scaled integer from the Value property even if they don’t know what the hell a scaled integer is. Object model guy then asks if he should expose the raw value. Of course. We’re all about exposing ourselves. We call in the dullest intern on staff to come up with the name and, true to form, he produces Value2. Positively inspired.

And scene.

You may disagree with some of the decision made in the above dramatic reenactment, but you have to realize they’re all connected. I personally disagree with calling Currency a format. But whether we call it format or a data type, doesn’t change the fact that we have to convert it. You can disagree with the decision to store all numbers as Doubles, and just store Currency as a scaled integer in memory. That doesn’t really change how the user identifies Currency values and you can’t simply store everything as currency because then everything would be slow.

Back to my assertion that Value is the appropriate default (or, put another way, that Value points to the right layer). I contend that if you “format” something as Currency or Date, that you do so knowingly and for a specific purpose. One of the knocks on Value is that you lose precision for Currency because Currency only goes four digits out to the right. I say you don’t lose precision. The precision that’s there is a myth. It’s only there because we had to convert the number to a float. When we chose Currency, we consciously forfeited all rights to precision beyond the 10,000th place (or should have). The fact that we can convert that number to a Double and see what looks like precision, doesn’t make it there.

For the Date type, I have a different argument. It’s not that there’s any problem with the data manipulation when converting from Double to Date, it’s just that it takes longer. If I’m reading in a date and spitting it back out, I agree that Value2 is probably the better choice. However, if that’s all I’m really doing I might want to address what value I’m adding. In most cases, I’m manipulating the date in such a way that I care that it’s a date. What I don’t want to do is read in a double using Value2 (avoiding the conversion overhead), then have to convert that number to a date myself to manipulate it, then convert it back to a double. In almost all cases, I want a Date or Currency typed number when I “format” it that way.

I suspect that most people who prefer Value2, in fact, disagree with the decision to confuse formatting and data types in the user interface. And if so, then we agree on that. Let me see if I can reword part of Charles’ statement so that we both agree with it: When performance matters, Value2 should be used 100% of the time there are no dates or currency, and Value should be used 100% of the time there are. I wonder if that will fly.

Footnotes
1In fact, all of Charles’ posts are interesting, so if you’re not subscribed to his RSS feed2 yet, get on it.
2If you’re still not reading blogs via RSS, be sure to say hello to my mom at the next Rotary meeting.

6 thoughts on “A Case for Value as a Default Property

  1. @fanpages: one error in the PDF to which you linked: that in programming languages TRUE usually means -1. That’s true for VB[A], and maybe for FORTH. But for languages most programmers would consider modern, e.g., C, C++, C#, Java, Perl, PHP, any nonzero numeric value would be considered TRUE, but if k were an integer-type variable, k = (1 == 1) would be 1, not -1. This is an all too common failing of VB[A] programmers: believing VB[A] is similar in the guts to other languages.

    @Dick: ‘Let’s call that data type Currency. We’re still going to store the value as a Double, but we’ll do the conversion prior to any calculations to ensure the accuracy.’ Congrats! That’s the problem: STORING values as IEEE double precision. 4 decimal digits to the right of the decimal point CAN’T be stored accurately as IEEE dp except for .0000, .0625, .1250, .1875, .25, etc., i.e, integer multiples of 1/16. Even .1000 can’t be stored accurately.

    The ONLY way to do fixed decimal reliably in Excel is by setting the Calculation option ‘Precision as Displayed’. Using Currency format doesn’t work the same as that.

    Experiment (Excel 2010): format A1:C10000 as Currency with 4 decimal places (and currency symbol wherever the mood takes you), enter 0.0001 in A1, =A1+A$1 in A2, 1 in B1 and =B1+B$1 in B2, =A1-B1*A$1 in C1, fill C1 down into C2 and A2:C2 down into A3:C10000. With ‘Precision as Displayed’ disabled everything will look OK in A1:C10000, but with D10000 formatted as Scientific, the formula =C10000 will return -9.38E-14 and the formula =C10000=0 will return FALSE. Now activate ‘Precision as Displayed’, and D10000 will evaluate =C10000 as 0.00E+00 and =C10000=0 as TRUE.

    The Currency format alone does nothing more than confuse the ignorant and lull the gullible.

    With respect to Date format, I’d never use VBA for Date (as opposed to Time) arithmetic. The worksheet functions DATEDIF, NETWORKDAYS, EDATE, EOMONTH are much better than anything purely VBA, and these worksheet functions readily accept doubles (.Value2) because that’s all they’re ever fed by Excel. You’d go through an unnecessary layer of type conversions passing Date type values to these worksheet functions. As for Time values, maybe there’s some value there, but myself all I ever use Time values in VBA for are delay values for Application.Wait and similar, and #1/16384# is close enough to 5 seconds that I have no use for TimeSerial(0, 0, 5).

    So I’d go along with Charles: .Value is mostly pointless, .Value2 is a much better choice for the default value of the Range class.

  2. Dick,

    If Excel actually used currency-type precision/calculation when calculating numbers formatted as currency I might partially agree with you.

    But it doesnt: Excel does ALL its calculations using its own variation of IEE floating point arithmetic. (There is no such thing as an integer/long in Excel either).

    In Excel Currency only ever exists as a rendered cell (which by the way I think is created on the fly whenever you make a currency-formatted cell visible). The rendered value is the one you get with .Text.

    So it seems to me to be pointless at best to convert the Excel floating point number to a VBA currency datatype. And in cases where you lose precision it seems to me more like a bug: when you round-trip a cell formatted as currency from Excel to VBA and back you can wind up with something different to what you started with.

    For Dates I don’t care: no damage is done apart from the speed loss but I don’t see the benefit – arithmetic on dates works either way.

  3. Actually, it can hurt dates. I’ve seen posts in the old ng’s that assign a date cell’s value to another date cell.

    But excel can parse 01/02/03 (the .value) multiple ways, it’s best to use:

    with receivingcell
    .numberformat = sendingcell.numbervalue
    .value2 = sendingcell.value2
    ‘or .value = sendingcell.value2 ???I don’t recall
    end with

Leave a Reply

Your email address will not be published. Required fields are marked *