Microsoft Excel 代码运行缓慢

Question

我有以下代码来将一个工作簿中的对象列出到另一个工作簿中；

For Each ws In mainworkBook.Sheets
    
    For Each includedObjects In ws.Shapes

        outputBook.Sheets("Sheet1").Range("A" & i) = ws.Name
        outputBook.Sheets("Sheet1").Range("B" & i) = TypeName(includedObjects)
        outputBook.Sheets("Sheet1").Range("C" & i) = includedObjects.Name
        i = i + 1
        
    Next
        
Next

但是，如果列出大约 2500 个对象，则运行此程序需要 5 分钟。但是，输出到 CSV 文件的代码只需几秒钟即可获得相同的结果。

objFile.writeline "Sheet, Object Type, Object name"
For Each ws In ActiveWorkbook.Sheets
    For Each includedObjects In ws.Shapes
        objFile.writeline ws.Name & "," & TypeName(includedObjects) & "," & includedObjects.Name
    Next
Next

我怎样才能改进第一个代码以使其运行得更快？

Answer 1

这应该会更快一些：

Dim arr(1 To 9999, 1 To 3) As String
Dim i As Long
i = 1
For Each ws In mainworkBook.Sheets
    For Each includedObjects In ws.Shapes
        arr(i, 1) = ws.Name
        arr(i, 2) = TypeName(includedObjects)
        arr(i, 3) = includedObjects.Name
        i = i + 1
    Next
Next

outputBook.Sheets("Sheet1").Range("A1:C9999") = arr

（如果9999不够，可以调大）

笔记：

1. 因为您对速度感兴趣，所以我们使用“超大”静态数组而不是动态数组。
2. 该代码仅“接触” Sheet 一次。