arrays - Excel VBA 编程与数组 : To Pass them or Not To Pass them?

Question

问题:我想知道在 Excel 2003 VBA 中处理数组的最佳解决方案是什么

背景:我在 Excel 2003 中有一个超过 5000 行的宏。我在过去 2 年中构建了它，将新功能添加为新过程，这有助于分割代码和调试、更改或添加到该功能。缺点是我在多个过程中使用了许多相同的基本信息，这需要我多次将其加载到具有细微差别的数组中。我现在遇到了运行时间长度的问题，所以我现在可以完全重写。
此文件用于获取制造流程的多个项目(最多 4 个不同的设置，总共最多 10 个不同的流程，每个流程最多 1000 个步骤)，其中的信息是特定于流程的，特定于分组/排序的子流程目的和数据(例如移动、库存、CT、...)
然后，它会将数据粘贴到用于管理流程的多个工作表上，使用要阅读的数据表、图表和单元格格式来表示过程流能力/历史。
流程在 Excel 文件中，而制造数据是用 7 个不同的 OO4O Oracle SQL 拉入读取的，其中一些被多次重用

数组是:
arrrFlow(1 to 1000, 1 to 4) 作为具有 4 个字符串的记录类型
arrrSubFlow(1 to 1000, 1 to 10) 作为具有 4 个字符串、2 个整数和 1 个单个的记录类型
arrrData(1 to 1000, 1 to 10) 作为记录类型，具有 1 个字符串、4 个整数、12 个长整数和 1 个单字符
arriSort(1 to 1000, 1 to 4) as Integer(用作指针数组，按组、子组和步骤顺序对流、子流和数据进行排序，同时保留原始数组按步骤顺序)

可能性:
1) 将宏重写为一个大程序，该程序将数据加载到程序中的主数组中一次
优点:在程序中标注而不是作为模块中的公共(public)变量并且未通过。
缺点:用一个大型程序而不是多个较小的程序更难调试。

2)保持宏与多个过程但传递数组
优点:使用多个较小的程序更容易调试代码。
缺点:传递数组(昂贵？)

3) 使用多个过程保留宏，但数组是模块中的公共(public)变暗变量
优点:使用多个较小的程序更容易调试代码。
缺点:公共(public)数组(昂贵？)

那么，社区的裁决是什么？有谁知道使用公共(public)数组与传递数组的费用吗？失去让我的程序专注于一个功能的便利性，这些成本中的任何一个值得吗？

更新:
我在离散级别(每步多个)加载库存数据，在聚合级别(每步一个)移动数据，并在聚合级别开始轮类库存。我逐步汇总库存数据，将其放置在工作状态类别(运行、等待等)中，并根据工作表上已有的数据创建目标。

我有一个按类型显示工作流程的流程图，目前 3 个产品具有相似但不完全相同的流程，2 个产品是不同的流程，它们相似但又不相同。我已将不同流程中的每组步骤分配为一个组和子组。

我把这些数据放在多张纸上，一些按步骤顺序，一些按组/子组顺序。我还需要按组和产品、组/子组和产品、生产线和产品的部分以及产品汇总的数据。

我使用记录类型，所以我实际上有一个可读的三维数组，arrSubFlow(1,1).strStep(第一个设备的第一个步骤的步骤名称)，arrData(10,5).lngYes(昨天第 10 个步骤的移动)第 5 个设备)。

我的主要优化点将出现在我每次从头开始创建 10 个页面的部分。合并单元格、边框、标题……这是一个非常耗时的过程。我将添加一个部分，将我的数据与页面进行比较，以查看它是否需要更改，如果需要，则仅重新创建它，否则，我将清除数据的每个部分，并且只将更改的数据写入工作表。根据我的时间记录数据，这将是巨大的。然而，每当我更新代码时，我总是尝试改进代码的其他方面。我看到数据加载到结构中(数组、记录集、集合)一次 因为两者都有一点优化，但对于数据完整性更是如此，所以我没有机会为不同的工作表以不同的方式加载它。

我现在看到的远离数组的主要问题是:
* 已经对它们进行了大量投资，但这还不足以成为不改变的理由
* 不知道通过它们是否有很多成本，因为它会通过 ByRef
* 我使用排序函数创建一个排序的“指针”数组，让我可以按步骤流顺序保留数组，同时按组/子组顺序轻松引用它。

由于我一直在努力为现在和 future 编写代码，因此我不反对将数组更新为 RecordSets 或 Collections，而不仅仅是为了更改它们以学习一些很酷的东西。我的阵列可以正常工作，并且根据我的研究，它们为运行时间增加了几秒钟，对于这个 2 分钟的报告来说，数量并不多。因此，如果将来另一种结构比记录类型的二维数组更容易更新，那么请告诉我，但有没有人知道将数组传递给过程的成本，假设您没有执行 ByVal 传递？

Answer 1

您提供了很多细节，但如果不看一些代码，仍然很难准确理解发生了什么。在您的问题中，我可以确定您贯穿始终的至少 4 个大主题:制造、数据访问、VBA 和编码最佳实践。我很难确切地说出你在问什么，因为你的问题范围很大。无论哪种方式，我感谢您尝试在 VBA 中编写更好的代码。

我很难确切地理解你打算用数组做什么。你说:

The downside is that I am using much of the same base information in multiple procedures, which requires me to load it into arrays with minor differences multiple times.

我不确定你在这里的意思。您是否使用数组来表示从数据库中检索到的一行数据？如果是这样，您可以考虑使用 类模块而不是通常的“宏”模块。这些将允许您使用成熟的对象而不是值数组(或引用，视情况而定)。类需要更多的工作来设置和使用，但它们使您的代码更易于使用，并且将极大地帮助您分割代码。

正如用户 Emtucifor 已经指出的那样，可能存在诸如 ADO Recordset 之类的对象。可以提供很大帮助的对象(可能需要安装 Access...不确定)。或者您可以创建自己的。

这是一个很长的示例，说明使用类可能如何帮助您。虽然这个例子很长，但它会向您展示一些面向对象编程的原则如何真正帮助您清理代码。

在 VBA 编辑器中，转到 Insert > Class Module .在“属性”窗口(默认屏幕左下角)中，将模块的名称更改为 WorkLogItem .将以下代码添加到类中:

Option Explicit

Private pTaskID As Long
Private pPersonName As String
Private pHoursWorked As Double

Public Property Get TaskID() As Long
    TaskID = pTaskID
End Property

Public Property Let TaskID(lTaskID As Long)
    pTaskID = lTaskID
End Property

Public Property Get PersonName() As String
    PersonName = pPersonName
End Property

Public Property Let PersonName(lPersonName As String)
    pPersonName = lPersonName
End Property

Public Property Get HoursWorked() As Double
    HoursWorked = pHoursWorked
End Property

Public Property Let HoursWorked(lHoursWorked As Double)
    pHoursWorked = lHoursWorked
End Property

上面的代码将为我们提供一个强类型对象，该对象特定于我们正在处理的数据。当您使用多维数组存储数据时，您的代码类似于: arr(1,1)是 ID， arr(1,2)是 PersonName，而 arr(1,3)是工作时间。使用这种语法，很难知道什么是什么。假设您仍然将对象加载到数组中，而是使用 WorkLogItem我们在上面创建的。这个名字，你就可以做 arr(1).PersonName获取此人的姓名。这使您的代码更易于阅读。

让我们继续这个例子。我们将尝试使用 collection，而不是将对象存储在数组中。 .

接下来，添加一个新的类模块并将其命名为 ProcessWorkLog .将以下代码放入其中:

Option Explicit

Private pWorkLogItems As Collection

Public Property Get WorkLogItems() As Collection
    Set WorkLogItems = pWorkLogItems
End Property

Public Property Set WorkLogItems(lWorkLogItem As Collection)
    Set pWorkLogItems = lWorkLogItem
End Property

Function GetHoursWorked(strPersonName As String) As Double
    On Error GoTo Handle_Errors
    Dim wli As WorkLogItem
    Dim doubleTotal As Double
    doubleTotal = 0
    For Each wli In WorkLogItems
        If strPersonName = wli.PersonName Then
            doubleTotal = doubleTotal + wli.HoursWorked
        End If
    Next wli

Exit_Here:
    GetHoursWorked = doubleTotal
        Exit Function

Handle_Errors:
        'You will probably want to catch the error that will '
        'occur if WorkLogItems has not been set '
        Resume Exit_Here


End Function

上面的类将用于“做某事”，集合为 WorkLogItem .最初，我们只是将其设置为计算工作总小时数。让我们测试一下我们写的代码。创建一个新模块(这次不是类模块；只是一个“常规”模块)。将以下代码粘贴到模块中:

Option Explicit

Function PopulateArray() As Collection
    Dim clnWlis As Collection
    Dim wli As WorkLogItem
    'Put some data in the collection'
    Set clnWlis = New Collection

    Set wli = New WorkLogItem
    wli.TaskID = 1
    wli.PersonName = "Fred"
    wli.HoursWorked = 4.5
    clnWlis.Add wli

    Set wli = New WorkLogItem
    wli.TaskID = 2
    wli.PersonName = "Sally"
    wli.HoursWorked = 3
    clnWlis.Add wli

    Set wli = New WorkLogItem
    wli.TaskID = 3
    wli.PersonName = "Fred"
    wli.HoursWorked = 2.5
    clnWlis.Add wli

    Set PopulateArray = clnWlis
End Function

Sub TestGetHoursWorked()
    Dim pwl As ProcessWorkLog
    Dim arrWli() As WorkLogItem
    Set pwl = New ProcessWorkLog
    Set pwl.WorkLogItems = PopulateArray()
    Debug.Print pwl.GetHoursWorked("Fred")

End Sub

在上面的代码中， PopulateArray()只需创建一个 WorkLogItem 的集合.在您的实际代码中，您可能会创建类来解析 Excel 工作表或数据对象以填充集合或数组。
TestGetHoursWorked()代码只是演示了如何使用这些类。你注意到 ProcessWorkLog被实例化为一个对象。实例化后，集合 WorkLogItem成为 pwl 的一部分目的。您会在 Set pwl.WorkLogItems = PopulateArray() 行中注意到这一点.接下来，我们简单地调用我们编写的作用于集合 WorkLogItems 的函数。 .

为什么这有帮助？

假设您的数据发生变化并且您想要添加一个新方法。假设您的 WorkLogItem现在包括 HoursOnBreak 的字段并且您想添加一种新方法来计算它。

您需要做的就是向 WorkLogItem 添加一个属性像这样:

Private pHoursOnBreak As Double

Public Property Get HoursOnBreak() As Double
    HoursOnBreak = pHoursOnBreak
End Property

Public Property Let HoursOnBreak(lHoursOnBreak As Double)
    pHoursOnBreak = lHoursOnBreak
End Property

当然，您需要更改填充集合的方法(我使用的示例方法是 PopulateArray() ，但您可能应该为此创建一个单独的类)。然后您只需将您的新方法添加到您的 ProcessWorkLog类(class):

Function GetHoursOnBreak(strPersonName As String) As Double
     'Code to get hours on break
End Function

现在，如果我们想更新我们的 TestGetHoursWorked()返回 GetHoursOnBreak 结果的方法，我们要做的就是添加以下行:

    Debug.Print pwl.GetHoursOnBreak("Fred")

如果传入表示数据的值数组，则必须在代码中找到使用数组的每个位置，然后相应地更新它。如果您改用类(及其实例化的对象)，您可以更轻松地更新代码以处理更改。此外，当您允许以多种方式使用类时(也许一个函数只需要 4 个对象属性，而另一个函数需要 6 个)，它们仍然可以引用同一个对象。这可以防止您为不同类型的函数使用多个数组。

为了进一步阅读，我强烈建议您获取 VBA Developer's Handbook, 2nd edition 的副本。 .这本书充满了很好的例子和最佳实践以及大量的示例代码。如果您正在为一个严肃的项目在 VBA 上投入大量时间，那么花时间阅读本书是非常值得的。