The .Text Property Causes Code to Execute Slower and Slower

I can't duplicate the progressive slowdown with this code

Sub textit()
Dim dTime As Double
Dim j As Long
Dim jStart As Long
Dim str As String
dTime = microtimer()
For jStart = 1 To 65000 Step 1000
dTime = microtimer
For j = 1 To 1000
str = UCase(Trim(Range("c1").Offset(j + jStart - 2, 0).Text))
Next j
dTime = microtimer - dTime
Debug.Print dTime
Next jStart
End Sub

What version of Excel are you using?

Post some code - it may just be the way your loop is constructed that's slowing things down.

 

Eric

It seems that you do not get the progressive slowdown if the workbook was created using Excel 2010, but you do get the progressive slowdown if the workbook was created using Excel 2003.

I use Excel 2007, have to design for compatibility with 2003.

I first asked the question on Mr. Excel, you can read my post. Others were able to reproduce, and effectively narrowed it down to the use of the .Text property, as changing just that caused significant differences in runtime, all other things held constant. Here's the basic gist of the loop (sorry for the code's so hard to read; apparently it was too hard to incorporate code blocks so indenting could be used easily like on the Mr. Excel forum).

For rw = startRow To lastRow
    'Display updated progress on status bar
    If rw Mod 100 = 0 Then
        Application.StatusBar = "Checking row: " & rw
        DoEvents
    End If

    'Load new CGID into user-defined type GR
    With Worksheets("MySheet")
        CGID = .Range("CGID")(rw).Text

        'Check if CGID is the same as the one before or after
        If CGID <> "" And (CGID = .Range("CGID")(rw - 1) Or CGID = .Range("CGID")(rw + 1)) Then
            HasDuplicateCGIDs = True
        End If
    End With
Next rw

If you read the post above, I've since switched to using a dictionary item, but regardless, I'm not really looking for code efficiency tips, since I've got that covered. I really just want to know why in the world the use of the .Text property repeatedly over thousands of rows causes the rows to be processed progressively slower.

More research shows that the time taken by .TEXT is a function of the number of rows between the rows visible in the sheet window and the row you are getting .TEXT from.

This effect only seems to happen if the row-height of some of the rows has been changed.

So one bypass is to set Screenupdating to True and select the cell before getting the text value.

(In general using .TEXT is a bad idea because it gets the formatted value and the user may have done something that will make .TEXT return ### etc. Its better to use .VALUE2 instead)

I read your other post.  In case you didn't get an answer, you can actually get microsecond timing in VBA.  Use the code below.

I had a similar problem to yours, only it was in PowerPoint when I was building slides in VBA.  The more slides I built, the slower the code went - dramatically slower.  The theory (not proven) there was that the undo stack in PowerPoint was getting unmanageably huge and every time you added an object to it it would take longer to increase the stack size.  Or something like that.

Eric

 

'=========== CODE START ==============

Option Explicit

#If VBA7 Then
Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#Else
Private Declare Function getFrequency Lib "kernel32" Alias _
"QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" Alias _
"QueryPerformanceCounter" (cyTickCount As Currency) As Long
#End If
'
' This function returns the value of the Windows high resolution
' (microsecond) timer, in seconds.
'
Public Function MicroTimer() As Double
'
' returns seconds
' uses Windows API calls to the high resolution timer
'
Dim cyTicks1 As Currency
Dim cyTicks2 As Currency
Static cyFrequency As Currency
'
MicroTimer = 0
'
' get frequency
'
If cyFrequency = 0 Then getFrequency cyFrequency
'
' get ticks
'
getTickCount cyTicks1
getTickCount cyTicks2
If cyTicks2 < cyTicks1 Then cyTicks2 = cyTicks1
'
' calc seconds
'
If cyFrequency Then MicroTimer = cyTicks2 / cyFrequency
End Function

'===========  CODE END ===============

More research shows that the time taken by .TEXT is a function of the number of rows between the rows visible in the sheet window and the row you are getting .TEXT from.

This effect only seems to happen if the row-height of some of the rows has been changed.

So one bypass is to set Screenupdating to True and select the cell before getting the text value.

(In general using .TEXT is a bad idea because it gets the formatted value and the user may have done something that will make .TEXT return ### etc. Its better to use .VALUE2 instead)

That's interesting, although I confess I don't know quite what to make of it. Are you saying that it takes longer the farther the row you're currently referring to is from what's in the viewing window? If so, I find that completely ludicrous that Microsoft would have designed it that way.

Row heights are all equal.

I definitely don't want to turn screenupdating on and select the cell since that's a bad way to go when doing so many interactions on thousands of rows. I don't want the screen scrolling all over the place for the user.

I need to use .Text precisely because the user may have entered custom formatting and I can't rely on them being sophisticated enough to know that the VALUE is different from what's shown in the cell, so users didn't understand why they shouldn't use formatting instead of actually properly formatting the data themselves, so I have to accommodate that.

I'll look into VALUE2, never heard of it before, does it return what's shown in the cell rather than it's actual value?

More research shows that the time taken by .TEXT is a function of the number of rows between the rows visible in the sheet window and the row you are getting .TEXT from.

Just for kicks I tried turning ScreenUpdating on and selecting the a new cell every thousand rows, but there was essentially no difference. It still got slower and slower as it ran. If the above were true, I would expect it to process at the same speed the whole way through, but it still got progressively slower. So it seems to be a factor of how many times it's called during a run rather than where the sheet is focused.

Try this code with the sheet visible so you can see the selection change

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

the times are
 0.355989698437043
 0.357595686451532
 0.359053989406675
 0.355781469435897
 0.355072090984322
 0.357417554478161
 0.355920055473689
 0.355667380907107

Now comment out the select and rerun. I get an increasing speedup because the previous run ended with row 35002 selected.

the times now are
3.23945670080138
 2.76494515530067
 2.28287052252563
 1.80327678954927
 1.32486593723297
 0.846296900941525
 0.349591643724125
 0.352475352818146

I think if the individual rows have a rowheight then .Text must do some sort of cumulative height calculation starting at the selected row for each row.

I need to use .Text precisely because the user may have entered custom formatting and I can't rely on them being sophisticated enough to know that the VALUE is different from what's shown in the cell, so users didn't understand why they shouldn't use formatting instead of actually properly formatting the data themselves, so I have to accommodate that.

As a matter of curiosity why are you trying to compare the CGID .Text with .Value to detect duplicates? This seems like comparing apples and oranges to me ...
And I don't see how its going to work if the user uses some crazy columnwidth or zoom and the .Text winds up as for example ###?