vba 用户定义类型与班级速度

User Defined Type vs Class Speed

提问by blawford

I have a workbook with two sheets of data that I need to perform operations on. I started off working with the data directly from the sheets but soon found that to be very slow, so changed things to read the sheets into two arrays (in two separate methods called from Workbook_Open).

我有一个包含两张数据的工作簿，我需要对其执行操作。我开始直接从工作表中处理数据，但很快发现这很慢，因此更改了将工作表读入两个数组（在两个单独的方法中调用 from Workbook_Open）的方法。

I had a user defined type created for the data on each sheet, I then found that I was not able to add these to collections or scripting dictionaries, so I transferred them to classes.

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

So now I have a class called CDealerDatawith 4 private fields and public properties for each. The issue is that the execution of reading the data into the array is double that of when I was using a type. Is that just how it is or am I doing something wrong.

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

Class:

班级：

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

Module:

模块：

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

回答by hnk

The UDT will be much faster than using a class in this manner for a number of reasons.

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

1. The UDT is a structure in memory with the data that can be directly written
2. The Class will have Let and Get properties which are functions that execute and have some overhead
3. Creation and Destruction of the class would add to a tiny bit of overhead, but nothing noticeable in your case

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

To improve performance, you may consider using Public Variables instead of private properties, but then again that may defeat the purpose of you using a class.

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

• If you are looking to simply use this as a data container, you are better off with a User-defined data type.
• If you wish to further manipulate this data with Class specific functions, then the Class approach is better

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

---

---

Also, a general approach to speeding things up is to access the spreadsheet as few times as possible.

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

For e.g. code such as the following

例如代码如下

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

can be replaced by

可以替换为

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

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

---

---

A note on profiling:Do note @BlackHawk's suggestion in the comments below, to use the MicroTimer tool. It is incredibly useful for isolating portions of code and finding the performance impact to a very precise level.

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

Also, while this is true for any platform, VBA performance can be inconsistent at times depending on how much pressure is there on Excel's resources at the moment, and hence, even though the MicroTimer is precise, it might not be accurately representative and you might want to consider running loops at different times to correctly gauge the impact of different sections of your code.

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

回答by ja72

Use this syntax to read entire arrays with one operation Dim x() as Variant : x = Range("A1").Resize(40,20).Value.

使用此语法通过一次操作读取整个数组Dim x() as Variant : x = Range("A1").Resize(40,20).Value。

This will read the cells starting from A1in 40 rows and 20 columns into an 2D array of Variant(,).

这将从A140 行和 20 列开始的单元格读取到Variant(,).

The you can loop through this array to put values into the user type and it will be much faster, like DealersData(i*NumberOfYears+j).BAC = x(2*i-1,j)or however you have things organized.

您可以遍历此数组以将值放入用户类型中，并且速度会快得多，就像DealersData(i*NumberOfYears+j).BAC = x(2*i-1,j)您组织的东西一样。

回答by Andy

As the first I would optimze the CDealerData-Class as follows:

首先，我将优化 CDealerData-Class 如下：

Private pUnits As Decimal 'instead of Variant, the internal mapping uses Time
Private pYear As Long 'instead of integer because outside of the Class you calc with Long

Furthermore I suggest you create a Method to set the Data by one line instead of writeable Properties:

此外，我建议您创建一个方法来通过一行而不是可写属性来设置数据：

Public Sub SetData(BAC As String, AccountNumber as String, Year as Long, Units as Decimal)
    pBAC = BAC 
    pAccountNumber = AccountNumber
    pYear = Year
    pUnits = Units
End Sub

The usage in your Module would look like this:

模块中的用法如下所示：

For i = 0 To LastRow
    For j = 0 To NumberOfYears - 1 'Year columns
        Set MyDealerData = New CDealerData
        MyDealerData.SetData(SheetRawData.Cells(i + 2, 1).Value, SheetRawData.Cells(i + 2, 3).Value,  j + 1, CDec(SheetRawData.Cells(i + 2, 4 + j).Value))
        'Assign  column based on j

        Set DealersData(i) = MyDealerData
    Next j
Next i

Also with a Class you can use a Collection and you woudn't need ReDim for the Array.

同样，对于一个类，您可以使用一个集合，并且不需要为数组使用 ReDim。

Hope it helps.

希望能帮助到你。

Cheers Andy

干杯安迪