How can I optimize this vba loop code?

Question

Hi I wrote this code. but this code is very slow. How can I optimize this code?

Private Sub printItem(r, lastCol, objStream)
    FirstCol = 1

    Dim strFirst As String

    strFirst = CStr(ActiveSheet.Cells(r, 1).Value)

    If strFirst = "" Then
        Exit Sub
    End If

        objStream.WriteText "    <"
        objStream.WriteText "TagName"
        objStream.WriteText " "

        For c = FirstCol To lastCol

            data = CStr(ActiveSheet.Cells(r, c).Value)

            If LenB(Trim$(data)) > 0 Then
                objStream.WriteText g_AttributeName(c)
                objStream.WriteText "="""
                objStream.WriteText data
                objStream.WriteText """ "
            End If

        Next c

        objStream.WriteText "/>"

    objStream.WriteText vbNewLine
End Sub

Nothing stands out, except maybe you'd be better off reading the cell values into a variant array in one operation, instead of one-by-one. How many columns is it typically reading, and is objStream a text file, or some other stream? And what happens in g-AttributeName(). ? — Tim Williams
– Tim Williams, Commented May 24, 2011 at 15:06
VBA is interpreted. It's supposed to be useful, not fast. Also all those WriteText calls are going to cost you, though that depends on what objStream is writing to. A file will be faster than a console. Bottom line - I'm not sure you can do much better. — Mike Dunlavey
– Mike Dunlavey, Commented May 24, 2011 at 15:19
Slow as in how slow? Slower than you expected, or so slow that it is stopping you from doing something? As previously mentioned speed is not necesrrily VBA's forte. And for things that are quick and dirty to write I accept slower performance — Peter M
– Peter M, Commented May 24, 2011 at 20:42
@Peter M: Word... But why go for a slow and dirty solution when a fast and equally dirty solution exists? Even with a certain amount of dirt, VBA can be plenty fast -- as long as you know where not to put the dirt. — Jean-François Corbett
– Jean-François Corbett, Commented May 25, 2011 at 8:29

Jean-François Corbett · Accepted Answer · 2011-05-24 21:02:58Z

4

This is the likely reason why your code is slow: you are looping through cells. There is significant overhead to each communication between VBA and Excel sheet data, and this adds up when you refer to many cells one at a time.

Instead, you should load all your data at once into a Variant array, and then loop through that array, as shown below. This is significantly faster.

    Dim varData As Variant

    varData = ActiveSheet.Cells(r, FirstCol).Resize(1, lastCol - FirstCol + 1)

    For c = LBound(varData, 2) To UBound(varData, 2)

        data = CStr(varData(1, c))

        If LenB(Trim$(data)) > 0 Then
            ' etc.
        EndIf

    Next c

For further reading, have a look at this old but still relevant article: http://www.avdf.com/apr98/art_ot003.html

edited May 24, 2011 at 21:02

answered May 24, 2011 at 20:35

Jean-François Corbett

38.7k30 gold badges144 silver badges192 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I optimize this vba loop code?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related