Code For Excel And Outlook Blog

Trimming cells, before and after

September 16, 2008 – 10:16 am by JP

---

    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

 Print This Post  |   Email This Post  |   Subscribe to Posts Feed  |   Subscribe to Comments

Filed Under: Excel, VBA, arrays
Tags: array, trim