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.


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
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 …


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.


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:


  • .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?

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

39 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.


  3. Jim Cone says:

    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.


    • 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 😉


      • 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.

  12. Hi Charles,

    Very interesting and useful as I’m recently working with very large amounts of data.

    Why am I not surprised this info is on your site?

  13. Winand says:

    I think one can use .text to check if he has #### in a cell instead of a number because of a wrong column width. Won’t work with dates, i think.
    set x = range(“D3”)
    if x.text=cstr(x.value) Then …

  14. snb says:

    The advantage of .Text to .Value or Value2:


    I don’t see too much speed difference when comparing an array containing values or an array containing text.
    Apparently it’s the way the array is created/populated that matters.

    Sub M_snb()
    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    sn = Split(.gettext, vbCrLf)
    End With

    For j = 1 To 40000 Step 5000
    t1 = MicroTimer()
    For jj = 1 To 5000
    var = sn(j + jj - 2)
    Debug.Print MicroTimer - t1
    End Sub

  15. snb says:

    If you want to fill comboboxex / listboxes according to the international settings you need the .Text property; otherwise your dates will be mm/dd/yyyy instead of e.g. dd-mm-yyyy or dd.mm.yyyy.
    Unless you use .Text , currency will be written as 12.35 instead of e.g. ƒ 12,35
    Generally: if you want the content of listboxes, comboboxes, textboxes match the formatting in cells you will have to use .Text (or reformat values in VBA before populating the control).

  16. Pingback: Excel VBA: Value or Value2 | IHACtips

  17. Terry Aney says:

    Hi, I did a range.Value as object[,] on a 1.6M cell range (~ 111 columns and 15,000 rows).

    -Column one was unformatted number
    -Columns 2-11 were date formatted columns
    -Columns 12-73 were currency formatted columns
    -Columns 74-97 were 4 decimal place formatted columns
    -Columns 98-111 were unformatted numbers again

    For ‘number cells’ all numbers were integers except two.

    -In a currency formatted cell, I had 15000.12345678
    -In a general formatted cell, I had 14999.12345678

    When calling range.Value, my dates came across as DateTime objects and all my currency formatted numbers (and decimal/general too) came across as double and preserved all their values.

    Does that surprise you? If I understood right, you seemed to think I’d lose precision on my currency formatted value.

    Thanks in advance.

    • fastexcel says:

      .NET does not have the currency data type that VBA/VB6 has and so the loss of precision does not occur.

      • Terry Aney says:

        You know anything about the C API? I’m trying to find the call that is equivalent to Range.Value…I’ve found call using xlfDeref / DEREF but that seems equivalent to Range.Value2 as dates are simply returned as double and I have know way of knowing that they should be a date.

      • fastexcel says:

        I don’t think there is an equivalent in the C API since neither Excel nor the xlOper structure used in the C API have a date datatype (all numbers are doubles).
        I have never used DEREF: I always use COERCE. Not sure why you would want to determine if the cell is currently formatted as a date (the unformatted value is always a double anyway) but you could try using CellFormat to retrieve the format string and work from there.

      • Terry Aney says:

        We export data from Excel into Xml to be loaded into different systems, so I need cells formatted as dates (by the user) to come across as dates in our Xml. Bummer there is no equivalent. Was hoping to keep Interop code out of the mix on this addin. Thanks for the information.

      • fastexcel says:

        Why not use VBA? Much faster than Interop.

      • Terry Aney says:

        We have a current add-in in VBA…but two many problems regarding coding lifecycle. No (not easily) source control since VBA is one large binary. I’ve had Excel crash too many times in my career with this add-in where I’ve lost hours of code. Easier to code many things in the c# language versus VBA. More of our developers can code in C# than VBA. etc. It is an option, but not one I’m ready to give into yet. One call to Interop.Range.Value is still way faster than current vba version we have (b/c it looped entire range with Range.Offest) so will probably end up there after a bit more poking around.

  18. Pingback: How To Assign A Number Value To Text In Excel | Find Goods

  19. fastexcel says:


    Worth looking at Rubberduck V2 for source control and refactoring with VBA.

  20. Michael L says:

    Thanks for article. Could I please get your opinion on the following problem:

    I want to retrieve what the contents of a cell SHOULD look like to the user. That is, including all number formatting, but not impacted by the width of the column. It seems like the Format function in VBA behaves a little different to number formatting in Excel, so I think the best solution is:


    • fastexcel says:

      Sounds like that might work – suggest you try it and see.

      • Michael L says:

        It seems to work except for blank cells, in which case it returns a zero. So it seems the best way to get the formatted contents of a cell is:


      • fastexcel says:

        I f you want to check for empty cells I would use something like If IsEmpty(Selection.Value) then Selection.Value=Empty

  21. Phuong says:

    Thanks for article. It’s very helpful
    But I have concern about identifying Double and DateTime using Range.Value2. Because my sheet contains data are date and double
    I can format the value to date by using Date.FromOADate() e.g.
    #4/9/1900 12:00:00 AM#
    Do you have any idea on it?
    Thanks in advance

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