TEXT vs VALUE vs VALUE2 – Slow TEXT and how to avoid it

I was intrigued by a recent post pointing out that using .Text to retrieve data from Excel ranges got slower and slower as you iterated through the rows. So I took some time to explore and compare the three main properties (Range.Value, Range.Value2 and Range.Text)  for getting result values from an Excel Range into VBA.

Of course, as you will see, they each have their own peculiarities and advantages.

Range.Text

This gets the formatted value of a cell. Actually it looks like .Text gets the value from the cell and then formats it, because the more complex the formatting the slower it is.
.Text is a read-only property, so you cannot set it directly.

If you try getting .Text from multiple cells into a variant (varr = Range(“A1:A10″).Text) you do NOT get an array of results. Instead if all the cells in the range contain the same value formatted in the same way you get a single formatted value, but if ANY of the cells has different values or formats you get Null ( this could be a useful trick).

When used inside a UDF you can use .Text to get the formatted value of the calling cell as it was before the UDF was called (you can’t do this with .Value or .Value 2 without causing a circular reference). Here is an example that adds an extra . on each calculation.

Function LastValue()
Dim var As Variant
Application.Volatile
var = Application.Caller.Text
Debug.Print var
var = var & "."
LastValue = var
End Function

The major drawback of .Text when used this way is that it gives you the formatted value, so the value you get could be ### if the user has set an inappropriate zoom or column width, or numbers could be retrieved as 1E+18 or …

Range.Value

This mostly gets the underlying value from the cell.

But if the cell is formatted as a date or currency then Range.Value converts the underlying value into a VBA date or currency variable before passing it to the VBA variable used in the assignment statement. Since VBA currency only has 4 decimal places this can cause a loss of accuracy. Suppose cell G4 has a value of 123.456789 formatted as currency. Then Range(“g4″).Value will retrieve a value of 123.4568 rather than the actual value!
If you assign this to a Variant you get a variant with a subtype of currency, but if you assign it to a Double then the value first gets converted to currency datatype and truncated, and then the currency datatype gets converted to a double.

Maybe using .Value to retrieve cells formatted as dates into a variant is useful because then you can use IsDate() to detect that the cell was a date.

Range.Value is an efficient way of transferring a range of cell values to VBA because you can assign the Range to a variant variable and the you get a variant containing a 2-dimensional array of variants. This is much more efficient that looping on the cells one-by-one.

.Value is (unfortunately) the default property of the Range object.

Range.Value2

This works the same way as Range.Value, except that it does not check the cell format and convert to Date or Currency. And thats probably why its faster than .Value when retrieveing numbers.

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

Performance Comparison

So how do these properties compare for speed? Here is my test code:

Sub textit()
Dim dTime As Double
Dim j As Long
Dim jStart As Long
Dim var As Variant
dTime = MicroTimer()
For jStart = 1 To 40000 Step 5000
dTime = MicroTimer
For j = 1 To 5000
var = Range("a1").Offset(jStart + j - 2, 0).Text
Next j
dTime = MicroTimer - dTime
Debug.Print dTime
Next jStart
End Sub

I ran this using a fresh worksheet with test data of numbers in the first 40000 rows.

The first run showed more-or-less constant time for each block. Then I changed the row-height of 10 rows at random intervals. The next run (.Text(2) is much slower, and the times increase from block to block.

So what’s going on: why so much slower with times increasing?

Well it turns out that once enough row-heights have been changed the time for .Text is a function of the number of rows between the selected visible rows on the screen and the row being processed!!!!

(And No I don’t know why, .Text must be doing some sort of cumulative row height calculation).

So if you add Range(“a1″).Offset(jStart).Select immediately after the For jStart = 1 To 40000 Step 5000 you get a faster and more constant set of times. Note you have to have Application.Screenupdating =True or this trick won’t work.

Finally I replaced .Text with .Value and then with .Value2, and then used a variant array instead of the inner loop to get the full set of timings:

Conclusions

  • .Text is seriously slow even if you bypass the row-height problem.
  • .Value can seriously damage your numbers
  • .Value2 is faster than .value with numbers (no significant difference with text)
  • .Value2 using a variant array is much the fastest way to go

So do you ever use .Text? And if so why?

About these ads
This entry was posted in UDF, VBA. Bookmark the permalink.

16 Responses to TEXT vs VALUE vs VALUE2 – Slow TEXT and how to avoid it

  1. Pingback: Daily Dose of Excel » Blog Archive » UDF for Cumulative Sum

  2. Nice work Charles,
    I think I use .Text most of the time, or I guess, .Value, as that’s the default, when pushing the wks range to an Array.

    I had always assumed/thought that .text would just get the text, a bit like paste special, so if some of the text was hidden/not displayed in the cell you would not get that text with .text, but you would with .value.

    So .value2 it is then, ta very much

    Having said that I can say I’ve ever had a problem with speed in this way.

    Rosscoe

  3. Jim Cone says:

    Charles,
    Have you done any tests comparing the use of .Formula vs. .Value/.Value2?

    One use for .Text would be in checking for duplicate entries in a formatted list of Strings. (depending on how “duplicate” is defined)
    ‘—
    Jim Cone

  4. fastexcel says:

    .Formula and .FormulaR1C1 are slower than .Value (factor of 10 using a variant array, and of course produce different results if you actually have a formula!)

    I must admit I find it difficult to envisage a scenario where I would want to compare the formatted values for duplicates rather than the real values.

  5. Michael says:

    Charles -

    I’ve posted macros over at DDoE that turn Excel tables into HTML tables. I use .Text to ensure wysiwyg.

    …mrt (aka Michael)

  6. fastexcel says:

    I found another use for .Value with dates/currency: If you use .Value to write a VBA Date/Time or currency variable into a cell it will format the cell as date or time or currency (but only if you do this from a Sub rather than a UDF).

  7. Great article. I recently got turned on to using .Text when I had the explicit need to traverse a range looking for #NAME? or #DIV/0! to highlight them in a worksheet as part of an audit process. I had thought to use it further for non numeric applications, easily avoiding any evaluation errors that I would otherwise have had to trap. Having heard the argument about speed, etc., I think I’ll go back to .Value (or .Value2) but keep .Text in mind for these special applications like the one I mentioned.

    Dave

    • fastexcel says:

      When looking for #NAME? or #DIV/0! you can use .Value2 to get the cell value into a variant, then use If IsError(var) to detect an error and if var=CVErr(xlErrName) etc to distinguish between different error types. This will be more robust (and probably faster) than using .TEXT.

      • I used IsError on just that occassion, today – and recalled I learned that from you. This scenario, however, led me down the path of using .Text. You see, I was writing an export text, tab delimited sub to avoid having quotes double-quoted, and considered the occassion that the worksheet might have errors in it. As a result, as I processed each cell, I tested for IsError(rng) and if it was, I used the rng.Text value for the output.

        One reason, perhaps, to use the .Text property ;)

        Dave

      • Dan says:

        Hi Fastexcel – your method doesnt work as .Value2 converts errors such as #DIV/0! into a number (-2146826281) and if you test IsError(-2146826281) you get false … so it doesnt flag as an error :(

      • fastexcel says:

        Hi Dan, It works Ok for me: put =1/0 in cell A1 and then run this.
        Sub Dan()
        Dim var As Variant
        Dim var2 As Variant
        var = Range("A1").Value
        var2 = Range("A1").Value2
        MsgBox IsError(var2)
        MsgBox IsError(var)
        End Sub

  8. David says:

    I recently came across .Text whilst modifying a set of subs made by an external company. I didn’t even know that .Text existed as I always use .Value. I was a macro designed to gather data from a set of spreadsheets and store them in an access db…
    Now that I understand that you would transfer out formatting errors etc like that I really wonder why they chose .Text??

    Many thanks for explaining the advantages and disadvantages of each property. I think for most of my programming I can now switch to using .value2 :o)

  9. Peter Bartholomew says:

    I tried the .Text property to move zip codes (especially those with a leading zero) in the knowledge that the code would be validated as text in the receiving workbook. It was ill-conceived though because, on input, the .Value2 property recognised the text format correctly and I still couldn’t write the text property to the output range. I finally settled for assigning an apostrophe as String, concatenated with the Value2 using “&”.

    Any better ideas?

  10. Mr. Poul says:

    I think I use .Text most of the time, or I guess, .Value, as that’s the default, when pushing the wks range to an Array.

  11. Mr. Barry says:

    Value converts the underlying value into a VBA date or currency variable before passing it to the VBA variable used in the assignment statement.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s