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 native Excel value (the only native Excel Data Types are double-precision numbers, text, Boolean, errors, empty) from the cell.

But if the cell is formatted as a date or currency then Range.Value converts the underlying double-precision 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.

Update: Even worse – assigning a VBA currency variable to a cell using .Value rounds the value to 2 decimal places regardless of cell formatting. This VBA code starts with 123.45679123, converts it currency datatype (rounds to 4 decimal places) which gives 123.4568, and then inserts into a cell using .Value which gives a value in Excel of 123.46 (rounds to 2 decimal places).

So using code like Range(“A2”).Value=Range(“A1”).Value when A1 is formatted as currency will round the actual resulting value in A2 to 2 decimal places.

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.

Update: There is a problem using .Value to get a date from Excel into a variant or a VBA Date and then passing it back to a Worksheet function like VLOOKUP: this will fail because VLOOKUP does not understand the combination of a number and a date format code.

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 that’s probably why its faster than .Value when retrieving 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?

Advertisement