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


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:


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

54 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

  22. Jon says:

    I’ve got a problem where i am reading cell values to text boxes. This works, try to upload load these to a device they don’t get excepted. I have tried text, value and value2. They appear as numbers but are obviously an “object”.

  23. Sandeep Kothari says:

    Interesting discussions.

  24. There is also a peculiar rounding that occurs when passing a value from one cell to another using .Value instead of .Value2 as described in this Stack Overflow question: https://stackoverflow.com/questions/54625432/excel-vba-transferring-numbers-shouldnt-round-off-decimals/54625513#54625513

  25. Leon Garfield says:

    Thank you for sharing your knowledge and for your time researching this problem. Now that I have learned of this problem I am worried about the code I have written. I have tried to duplicate this problem, but I don’t see the loss of precision. I use VBA to store NOW in a cell and rely on the fraction of a second. I tried retrieving the date from the cell using .VALUE and .VALUE2 into a Date & Variant variables and then put them back into another cell. How do I see the loss of precision? I used the immediate window to print the contents of the variables and the cells and they all return the same value of 44001.3565046296. I am using Office 365.

    Dim dateVar As Variant, dateDate As Date, dateVar2 As Variant, DateDate2 As Date

    ‘Range(“b22”).Value has a date stored by using NOW
    dateVar = Range(“b22”).Value
    dateDate = Range(“b22”).Value
    dateVar2 = Range(“b22”).Value2
    DateDate2 = Range(“b22”).Value2
    Debug.Print CDbl(dateVar)
    Debug.Print CDbl(dateDate)
    Debug.Print CDbl(dateVar2)
    Debug.Print CDbl(DateDate2)
    Range(“a30”).Value = dateVar
    Range(“a31”).Value = dateDate
    Range(“a32”).Value = dateVar2
    Range(“a33”).Value = DateDate2
    Debug.Print Range(“a30”).Value2
    Debug.Print Range(“a31”).Value2
    Debug.Print Range(“a32”).Value2
    Debug.Print Range(“a33”).Value2

    they all return the same value

    • fastexcel says:

      Date/Time does not give you loss of precision with .Value (result is not stored in a currency variable). You will get a problem with date/times and .Value if you use the VBA variable in a worksheet function call ans its slower than .Value2 – otherwise you will not see any difference.

  26. Thank you for the time you spent researching this problem. I store the date and time from NOW to a cell that compare it relying on the milliseconds. I have tried to duplicate your tests and I am not successful in duplicating the problem you found.

    this is the code I have run.

    Dim dateVar As Variant, dateDate As Date, dateVar2 As Variant, DateDate2 As Date

    ‘Range(“b22”).Value has a date stored by using NOW
    dateVar = Range(“b22”).Value
    dateDate = Range(“b22”).Value
    dateVar2 = Range(“b22”).Value2
    DateDate2 = Range(“b22”).Value2
    Debug.Print CDbl(dateVar)
    Debug.Print CDbl(dateDate)
    Debug.Print CDbl(dateVar2)
    Debug.Print CDbl(DateDate2)
    Range(“a30”).Value = dateVar
    Range(“a31”).Value = dateDate
    Range(“a32”).Value = dateVar2
    Range(“a33”).Value = DateDate2
    Debug.Print Range(“a30”).Value2
    Debug.Print Range(“a31”).Value2
    Debug.Print Range(“a32”).Value2
    Debug.Print Range(“a33”).Value2

    the results are all the same 44002.8567824074

  27. Derek says:

    A similar problem occurs with ‘Data/Remove duplicates’. Assume a value is formatted as ‘number’ with ‘thousand’ separators and the same value is formatted as general or without the separator then the ‘Data/Remove duplicates’ will fail.
    This implies the function is comparing the display value and not the underlying value.

  28. Andreas says:

    I use .Text for a particular project, because I need to do a Range-To-Array, where the cell values only contain “-” (minus) and “+” (plus). I think because this is also a Formula-implicator it is not easily possible to load a Range with these Values into an array. Seems a little bit like a bug in VBA…

  29. Curaga says:

    When using Value2 along with VB.Net, it cannot be used (throws an error) with checks like “cell.Value2 Is Nothing” but when used with “cell.Value Is Nothing”, it works.

    • Curaga says:

      Upon trying again, when the code is encapsulated in IIF function:
      Dim cellxy As Object = wSheet.Cells(1, 1)
      Dim cellVal As String = IIF(cellxy.Value2 Is Nothing, “”, cellxy.Value2.ToString).ToString
      It gives a reference error unlike using Value because it still evaluates the False part of IIF.

      Dim cellxy As Object = wSheet.Cells(1, 1)
      Dim cellVal As String = “”
      If cellxy.Value2 IsNot Nothing Then cellVal = cellxy.Value2.ToString

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 )

Facebook photo

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

Connecting to %s