excel - 用户定义类型与类速度

Question

我有一个包含两张数据的工作簿，我需要对其执行操作。我开始直接使用工作表中的数据，但很快发现它非常慢，所以改变了一些东西，将工作表读入两个数组(在从 Workbook_Open 调用的两个单独的方法中)。

我为每个工作表上的数据创建了一个用户定义的类型，然后我发现我无法将它们添加到集合或脚本字典中，所以我将它们转移到类中。

现在我有一个名为 CDealerData 的类，每个类都有 4 个私有(private)字段和公共(public)属性。问题是将数据读入数组的执行是我使用类型时的两倍。是这样还是我做错了什么。

类:

Option Explicit

Private pBAC As String
Private pAccountNumber As String
Private pYear As Integer
Private pUnits As Variant


Public Property Get BAC() As String
    BAC = pBAC
End Property
Public Property Let BAC(Value As String)
    pBAC = Value
End Property


Public Property Get AccountNumber() As String
    AccountNumber = pAccountNumber
End Property
Public Property Let AccountNumber(Value As String)
    pAccountNumber = Value
End Property


Public Property Get Year() As String
    Year = pYear
End Property
Public Property Let Year(Value As String)
    pYear = Value
End Property


Public Property Get Units() As String
    Units = pUnits
End Property
Public Property Let Units(Value As String)
    pUnits = Value
End Property
Option Explicit

Private pBAC As String
Private pAccountNumber As String
Private pYear As Integer
Private pUnits As Variant


Public Property Get BAC() As String
    BAC = pBAC
End Property
Public Property Let BAC(Value As String)
    pBAC = Value
End Property


Public Property Get AccountNumber() As String
    AccountNumber = pAccountNumber
End Property
Public Property Let AccountNumber(Value As String)
    pAccountNumber = Value
End Property


Public Property Get Year() As String
    Year = pYear
End Property
Public Property Let Year(Value As String)
    pYear = Value
End Property


Public Property Get Units() As String
    Units = pUnits
End Property
Public Property Let Units(Value As String)
    pUnits = Value
End Property

模块:

Option Explicit

Public NumberOfYears As Integer

Public DealersData() As CDealerData

Public Sub ReadDealerData()
        
    '** Reads the contents of RawData into an Array
    '** of custom type DealerData, defined above
    
    Dim MyDealerData As CDealerData
    Dim LastRow As Long
    Dim i As Long
    Dim j As Long
        
    LastRow = SheetRawData.UsedRange.Rows.Count
    
    ReDim DealersData(LastRow * NumberOfYears)
    
    For i = 0 To LastRow
        For j = 0 To NumberOfYears - 1 'Year columns
            Set MyDealerData = New CDealerData
        
            MyDealerData.BAC = SheetRawData.Cells(i + 2, 1).Value
            MyDealerData.AccountNumber = SheetRawData.Cells(i + 2, 3).Value
            MyDealerData.Year = j + 1
            MyDealerData.Units = CDec(SheetRawData.Cells(i + 2, 4 + j).Value) 'Assign column based on j
            
            Set DealersData(i) = MyDealerData
        Next j
    Next i
    
End Sub

Answer 1

出于多种原因，UDT 将比以这种方式使用类快得多。

1. UDT是内存中的结构体，可以直接写入数据
2. 该类将具有 Let 和 Get 属性，它们是执行的函数并有一些开销
3. 类的创建和销毁会增加一点开销，但在您的情况下没有什么值得注意的

为了提高性能，您可以考虑使用公共(public)变量而不是私有(private)属性，但这又可能会破坏您使用类的目的。

• 如果您只想将其用作数据容器，最好使用用户定义的数据类型。
• 如果您希望使用特定于类的函数进一步操作此数据，那么类方法更好

---

此外，加快速度的一般方法是尽可能少地访问电子表格。

例如代码如下

For i = 1 to 10
   Variable = Worksheets("Sheet1").Range("A1").Cell(i,1).Value
Next i

可以替换为

Dim VariantArray as Variant
VariantArray = Workeheets("Sheet1").Range("A1:A10")

' Now VariantArray(0,0) has the first element, (1,0) has the second, etc.

---

关于分析的注意事项:请在下面的评论中注意@BlackHawk 的建议，使用 MicroTimer 工具。它对于隔离部分代码和找到非常精确的性能影响非常有用。

此外，虽然这对任何平台都是如此，但 VBA 性能有时可能会不一致，具体取决于此时 Excel 资源的压力有多大，因此，即使 MicroTimer 是精确的，它也可能无法准确代表并且您可能需要考虑在不同时间运行循环以正确衡量代码不同部分的影响。