如何优化此vba循环代码? [英] How can I optimize this vba loop code?

问题描述

我写了这段代码.但是这段代码很慢.如何优化此代码?

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

推荐答案

这可能是您的代码运行缓慢的可能原因:您正在遍历单元格. VBA和Excel工作表数据之间的每次通信都存在大量开销，并且一次引用多个单元格时，这会加起来.

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.

相反，您应该一次将所有数据加载到Variant数组中，然后遍历该数组，如下所示.这明显更快.

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

为进一步阅读，请看这篇古老但仍然相关的文章: http://www .avdf.com/apr98/art_ot003.html

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

这篇关于如何优化此vba循环代码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！