As an experiment, I decided to rewrite one of my old routines that I use a lot, but hadn't re-thought in a while — a simple sub that trims worksheet cells. I plan to do that more often in the coming weeks, because a lot of my code is old and written quick and dirty to get something done, sometimes by brute force.
I get a lot of poorly formatted worksheets, and as you know, if a cell contains hidden spaces at the end, then string comparisons (i.e. "=IF(A1=B1)") will fail. This is maddening at times, so originally I wrote this routine:
Sub Trim_Cells()
Dim cell As Excel.Range
If Selection.Cells.Count > 1 Then
Selection.SpecialCells(xlCellTypeConstants).Select
End If
With WorksheetFunction
For Each cell In Selection
cell = Trim(cell)
cell = .Trim(cell)
Next cell
End With
ActiveCell.Select
End Sub
With a shortcut key or toolbar button, it's very convenient. And as you might be able to tell by looking at it, very slow. To speed it up somewhat, it limits the action cells to just constants, but the VBA code still has to hit the worksheet in a 1:2 ratio (!) for each cell in the selection, because for good measure I called the VBA Trim function as well as the Worksheetfunction version. That means each cell has to be hit twice!
I inserted the code above into a generic Timer sub I use to for testing purposes, and ran it on a range of 500 cells. See below for test results.
Sub TimerTest()
'
' place your test code between the starttime and endtime variables
'
StartTime = Timer
'
Dim cell As Excel.Range
If Selection.Cells.Count > 1 Then
Selection.SpecialCells(xlCellTypeConstants).Select
End If
With WorksheetFunction
For Each cell In Selection
cell = Trim(cell)
cell = .Trim(cell)
Next cell
End With
ActiveCell.Select
'
EndTime = Timer
MsgBox ("This routine took " & (EndTime - StartTime) & " seconds.")
'
'
'
End Sub
As you can see, this is barely tolerable. In a large worksheet, unacceptable. So on a lark I rewrote the code to use arrays exclusively. Here it is:
Sub Trim_Cells_Array_Method()
Dim arrData() As Variant
Dim arrReturnData() As Variant
Dim rng As Excel.Range
Dim lRows As Long
Dim lCols As Long
Dim i As Long, j As Long
lRows = Selection.Rows.Count
lCols = Selection.Columns.Count
ReDim arrData(1 To lRows, 1 To lCols)
ReDim arrReturnData(1 To lRows, 1 To lCols)
Set rng = Selection
arrData = rng.Value
For j = 1 To lCols
For i = 1 To lRows
arrReturnData(i, j) = Trim(arrData(i, j))
Next i
Next j
rng.Value = arrReturnData
Set rng = Nothing
End Sub
When this code is plugged into the TimerTest sub, here is the result:
By using arrays and limiting the number of hits on the worksheet (i.e. limiting the interaction between Excel and VBA), we are able to produce a 1,000,000% increase in speed! Seriously, the lesson I've learned here is:
a) Read all your worksheet data into an array up front, try to manipulate it in VBA exclusively (no Worksheetfunction calls or temporary worksheets), and dump it back to the worksheet in one shot. Your watch will thank you.
a) Loops aren't intrinsically bad. It's the loops that require repeated hits on the worksheet (especially 1:1 ratio 'For Each x in Selection' type loops) that are costly. As I've mentioned before, when VBA and Excel have to interact, there is a time penalty.
Enjoy,
JP
About JP
3 Comments:
Between the timer statements and your code, insert these statements:
Application.ScreenUpdating = False ' beginning
Application.ScreenUpdating = True ' end
I'll bet this comes in closer to the array approach than the original worksheet approach.
Well met, Jon!
I added the ScreenUpdating option and here was the result:
Old way: .125 seconds
New way: .01 seconds
I also added 'Calculation = xlCalculationManual' and got even more improvement from the original method:
Old way: .11 seconds
New way: .01 seconds
There was an additional slight improvement when calculation was turned off as well. The array method wasn't really helped by turning calculation off, though; my guess is because the worksheet isn't being touched that much in the first place, so not much recalculation was taking place anyway.
Thx,
JP
The array technique should be touching the worksheet twice, once to read and once to write, and reading is much faster than writing. The looping technique is more interactive, with one read and write per cell. Turning off Screen Updating and Caluclation will have a large effect on the loop, since these make each of many I/O operation faster. In the array, these steps make each of one operation faster.