Excel VBA编程与数组：传递或不传递它们？ [英] Excel VBA Programming with Arrays: To Pass them or Not To Pass them?

问题描述

问题：我想知道哪些是在Excel 2003 VBA中处理数组的最佳解决方案

背景： strong>我在Excel 2003中有一个超过5000行的宏。在过去的2年中，我已经构建了新功能作为新的过程，这有助于分割代码，调试，更改或添加到该功能。缺点是我在多个程序中使用了大量相同的基本信息，这需要我将它多次加载到数组中。我现在遇到了运行时间长短的问题，所以我现在可以完全重写。

此文件用于抓取多个制造流程项目（最多4个不同的设置总共可达10个不同的流程，每个流程最多可达1000个步骤），具体信息为流量专用，分组/排序目的特定的子流程和数据（如运动，库存，CT，...） >
然后，将数据粘贴到多个用于管理流程的工作表中，利用要使用的数据表，图表和单元格格式来表示流程能力/历史。

流程在Excel文件，而使用7种不同的OO4O Oracle SQL引用读取制造数据时，有些重复使用多次

数组是： br>
arrrFlow（1到1000，1到4）作为具有4个字符串的记录类型

arrrSubFlow（1到1000,1到10）作为具有4个字符串的记录类型，2个整数，和1个单一的

arrrData（1到1000，1 t o 10）作为记录类型，具有1个字符串，4个整数，12个长整数和1个单个

arriSort（1到1000,1到4）作为整数（用作指针数组来排序流，子流程和子集，以及步骤顺序排列的数据）

可能性：

1）将宏重写为一个大的过程，将数据加载到在程序中定义的主阵列中一次

Pro：在过程中维度而不是模块中的公共变量通过了。

Con：更难调试一个巨大的程序，而不是多个较小的程序。 2）使用多个过程保持宏，但传递数组

Pro：使用多个较小的过程更容易调试代码。

Con：传递数组（昂贵？）

3）保持宏具有多个过程，但数组是模块中的公共调用变量

Pro：更容易使用多个较小的程序调试代码。

Con：公共数组（昂贵的）

那么社区的判决是什么？有没有人知道使用公共数组与传递数组的费用？这些成本是否值得让我的程序集中在一个功能上？

更新：

我将库存数据加载到离散级别（每步多个），以汇总级别（每步一个）移动数据，并在汇总级别移动库存开始。我通过将工作状态类别（运行，等待，...）逐步将库存数据汇总到我已经在工作表上的数据创建目标。

我有一个流程表，显示按类型的工作流程，目前有3个产品具有相似但不完全相同的流程，2个产品是不同的流程，是相似的，但是彼此不一样。我分配了不同流程中的每个步骤组和子组。

我将这些数据放在多个工作表上，有些则是按顺序排列，有些是按组/子组顺序排列的。我还需要按组和产品，组/子组和产品，生产线和产品的部分以及产品总结的数据。

我使用记录类型，所以我实际上有一个可读的三维数组，arrSubFlow（1,1）.strStep（第一个设备的第一步的步骤名称） arrData（10,5）.lngYest（昨天的第五个装置的第10步的动作）。

我的优化要点在于我每一次从头开始创建10个页面的部分。通过合并单元格，边框，标题，...这是一个非常耗时的过程。我将添加一个部分，将我的数据与页面进行比较，看是否需要更改，如果是，那么只有在重新创建它时，否则我将清除每个部分的数据，并且只写入更改为表的数据。这将是巨大的，基于我的时间记录数据。但是，每当我更新代码时，我总是尝试改进代码的其他方面。我看到将数据加载到Structure（Array，RecordSet，Collection）一次作为一个优化，但是对于数据完整性更是如此，所以我没有机会加载它不同的表格不同。

现在我看到远离数组的主要问题是：

*已经大量投资，但这是不是一个很好的理由不改变

*不知道传递它们是否有很多成本，因为它将由ByRef

*我使用一个排序函数创建一个排序指针数组，让我以步骤流顺序离开数组，同时通过组/子组顺序轻松引用它。

由于我总是试图使我的代码现在和将来，我不反对将数组更新为RecordSets或Collections，而不仅仅是为了改变他们来学习一些很酷。我的阵列工作，从我的研究，他们添加到运行时间的秒数，而不是这个2分钟的报告的大量。所以如果另外一个结构在将来比二维数组记录类型更容易更新，那么请让我知道，但是有人知道将数组传递给一个过程的代价，假设你没有做一个ByVal pass？ / p>

解决方案

你提供了一个很好的细节，但是在看到一些代码之前，还是很难准确地了解发生了什么。在您的问题中，我可以确定至少有四个主题：您的交互过程包括：制造，数据访问，VBA和编码最佳实践。由于您的问题范围很大，我很难准确地告诉您您的问题。无论哪种方式，我感谢您尝试在VBA中编写更好的代码。

我很难准确了解您计划对数组做什么。你说：

缺点是我在多个程序中使用了大量相同的基本信息，这需要我将其加载到数组多少次有微小的差异。

我不知道你在这里的意思。您是否使用数组来表示从数据库检索的一行数据？如果是这样，您可以考虑使用类模块而不是通常的宏模块。这些将允许您使用完整的对象而不是值的数组（或引用，视情况而定）。课程需要更多的工作来设置和使用，但是它们使您的代码更容易使用，并将极大地帮助您分割代码。

随着用户Emtucifor已经指出，可能会有很大的帮助，例如 ADO Recordset 对象（可能需要安装Access ...不确定）。或者你可以创建自己的。

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

在VBA编辑器中，转到插入>课程模块。在属性窗口（默认情况下，屏幕左下方），将模块的名称更改为 WorkLogItem 。将以下代码添加到课程中：

  Option Explicit 
 
私人pTaskID As Long 
私人pPersonName As String 
私人pHours Worked As Double 
 
公共属性Get TaskID（）As Long 
 TaskID = pTaskID 
结束属性
 
公开属性让TaskID（lTask​​ID As Long）
 pTaskID = lTask​​ID 
结束属性
 
公共属性Get PersonName（）As String 
 PersonName = pPersonName 
结束属性
 
公共属性让PersonName（lPersonName As String）
 pPersonName = lPersonName 
结束属性
 
公共属性获取HoursWorked（）As Double 
 HoursWorked = pHoursWorked 
结束属性
 
公共财产让工作时间（一次工作为双倍）
 pHoursWorked = 1年工作
结束财产
  

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

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

接下来，添加一个新类模块，并调用它 ProcessWorkLog 。将以下代码放在那里：

  Option Explicit 
 
私有pWorkLogItems作为集合
 
公共属性获取WorkLogItems（）作为集合
设置WorkLogItems = pWorkLogItems 
结束属性
 
公共属性集WorkLogItems（lWorkLogItem As Collection）
设置pWorkLogItems = lWorkLogItem 
结束属性
 
函数GetHoursWorked（strPersonName As String）As Double 
 On Error GoTo Handle_Errors 
 Dim wli As WorkLogItem 
 Dim doubleTotal As Double 
 doubleTotal = 0 
对于每个wli在WorkLogItems 
如果strPersonName = wli.PersonName然后
 doubleTotal = doubleTotal + wli.HoursWorked 
结束如果
下一个wli 
 
 Exit_Here：
 GetHoursWorked = doubleTotal 
退出函数
 
 Handle_Errors：
'你可能想要捕获'
 '如果WorkLogItems尚未被发现et'
简历Exit_Here 
 
 
结束功能
  

上面的类将被用于做某事，并且使用 WorkLogItem 的集合。最初，我们只是设置它来计算工作的总时数。我们来测试我们写的代码。创建一个新的模块（这次不是一个类模块，只是一个常规模块）。将以下代码粘贴到模块中：

  Option Explicit 
 
函数PopulateArray（）作为集合
 Dim clnWlis As Collection 
 Dim wli As WorkLogItem 
'将一些数据放在集合'
中Set clnWlis = New Collection 
 
设置wli = New WorkLogItem 
 wli.TaskID = 1 
 wli.PersonName =Fred
 wli.HoursWorked = 4.5 
 clnWlis.Add wli 
 
设置wli =新的WorkLogItem 
 wli.TaskID = 2 
 wli.PersonName =Sally
 wli.HoursWorked = 3 
 clnWlis.Add wli 
 
设置wli =新的WorkLogItem 
 wli.TaskID = 3 
 wli.PersonName =Fred
 wli.HoursWorked = 2.5 
 clnWlis.Add wli 
 
设置PopulateArray = clnWlis 
结束函数
 
 Sub TestGetHoursWorked（）
 Dim pwl As ProcessWorkLog 
 Dim arrWli（）As WorkLogItem 
设置pwl = New ProcessWorkLog 
设置pwl.WorkLogItems = PopulateArray（） 
 Debug.Print pwl.GetHoursWorked（Fred）
 
 End Sub 
  

在上面的代码中， PopulateArray（）只需创建一个 WorkLogItem 的集合。在您的真实代码中，您可以创建类来解析Excel表或数据对象以填充集合或数组。

TestGetHoursWorked ）代码简单地演示了如何使用类。您注意到 ProcessWorkLog 被实例化为对象。在实例化之后， WorkLogItem 的集合成为 pwl 对象的一部分。你注意到这一点在中设置pwl.WorkLogItems = PopulateArray（）。接下来，我们简单地调用我们编写的函数收集 WorkLogItems 。

为什么这对你有帮助？ / p>

我们假设您的数据更改，并且您要添加新的方法。假设您的 WorkLogItem 现在包含一个 HoursOnBreak 的字段，并且您想添加一个新的方法来计算。

所有你需要做的是添加一个属性到 WorkLogItem 像这样：

 私人pHoursOnBreak As Double 
 
公共财产Get HoursOnBreak（）As Double 
 HoursOnBreak = pHoursOnBreak 
终止财产
 
公共财产让HoursOnBreak（1HoursOnBreak As Double）
 pHoursOnBreak = 1HoursOnBreak 
终止财产
  

当然，您需要更改填充集合的方法（我使用的示例方法是 PopulateArray（），但是您可能应该有一个单独的类只为此）。然后，您只需将新方法添加到您的 ProcessWorkLog 类中：

 函数GetHoursOnBreak（strPersonName As String）As Double 
'代码获取休息时间
结束函数
  

现在，如果我们要更新我们的 TestGetHoursWorked（）方法来返回 GetHoursOnBreak 的结果，我们所有必须添加以下行：

  Debug.Print pwl.GetHoursOnBreak（Fred）
  

如果您传递了一系列表示数据的值，则必须在代码中找到所有用户的位置数组，然后相应更新。如果您使用类（及其实例化对象），您可以更轻松地更新代码以使用更改。此外，当您允许以多种方式使用类时（可能一个函数只需要4个对象属性，另一个函数需要6个），它们仍然可以引用同一个对象。为了进一步阅读，我将高度评价要获得 =http://rads.stackoverflow.com/amzn/click/0782129781 =noreferrer> VBA开发者手册，第2版。这本书充满了很好的例子和最佳实践以及大量的示例代码。如果你投入大量的时间进入VBA进行一个严肃的项目，那么很值得你花时间研究这本书。

Question: I am wondering which is the optimal solution for dealing with Arrays in Excel 2003 VBA

Background: I have a Macro in Excel 2003 that is over 5000 lines. I have built it over the last 2 years adding new features as new Procedures, which helps to segment the code and debug, change, or add to that feature. 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. I am now running into issues with the length of run time, so I am now able to do a full rewrite.
This file is used to grab multiple items of manufacturing flows (up to 4 different set ups with a total of up to 10 distinct flows , of up to 1000 steps each) with the information being Flow specific, Sub-Flow specific for grouping / sorting purposes, and Data (such as movements, inventory, CT, ...)
It then will stick the data onto multiple sheets used to manage the process utilizing data sheets to be perused, charts, and Cell Formatting to denote process flow capability / history.
The Flow is in the Excel File, while the Manufacturing data is read in with 7 different OO4O Oracle SQL pulls, some reused multiple times

The Arrays are:
arrrFlow(1 to 1000, 1 to 4) as a Record Type with 4 strings
arrrSubFlow(1 to 1000, 1 to 10) as a Record Type with 4 strings, 2 integers, and 1 single
arrrData(1 to 1000, 1 to 10) as a Record Type with 1 string, 4 integers, 12 longs, and 1 single
arriSort(1 to 1000, 1 to 4) as Integer (Used as a pointer Array to sort the Flow, Sub Flow, and Data in a Group, Sub Group, and Step order while leaving the original arrays in Step order)

Possibilities:
1) Rewrite the macro into one big procedure that loads the data into master arrays dimensioned within the Procedure once
Pro: Dimensioned in the Procedure rather than as a Public Variable in the Module and not passed.
Con: Harder to debug with one mega procedure instead of multiple smaller ones.

2) Keep macro with multiple procedures but passing the Arrays
Pro: Easier to debug code with multiple smaller procedures.
Con: Passing Arrays (Expensive?)

3) Keep macro with multiple procedures but with the Arrays being Public Dim'ed variables in the Module
Pro: Easier to debug code with multiple smaller procedures.
Con: Public Arrays (Expensive?)

So, what's the community's verdict? Does anyone know the expense of using Public Arrays vs Passing Arrays? Is the Cost of either of these worth losing the ease of having my procedures being focused on one feature?

UPDATE:
I load Inventory Data at a discrete level (multiple per Step), Moves Data at a aggregate level (one per step), and the Beginning of Shift Inventory at an aggregate level. I aggregate the Inventory data by step placing it in Work State categories (Run, Wait,...) I create targets off data already on the sheets.

I have a Flow sheet that shows the Work Flows by Type, currently 3 products have a similar but not exactly the same flow, and 2 products are a different flow, that are similar but again not the same as each other. I have assigned each set of steps in the different flows a group and sub-group.

I place this data on multiple sheets, some in Step Order, some in group / sub-group order. I also need the data summed up by group and product, group / sub-group and product, portion of the line and product, and product.

I use Record Types so I actually have a readable three dimensional array, arrSubFlow(1,1).strStep (Step Name of the 1st Step of the 1st Device), arrData(10,5).lngYest (Yesterday's movement for the 10th Step of the 5th Device).

My main point of optimization is going to be in the section where I create 10 pages from scratch every single time. With Merging Cells, Borders, Headers, ... This is a very time consuming process. I will add a section that will compare my data with the page to see if it needs to be changed and if so, only then recreate it otherwise, I'll clear each section of data and only write data that changes to the sheet. This will be huge, based on my time logging data. However, whenever I update code, I always try to improve other aspects of the code as well. I see the loading of the data into a Structure (Array, RecordSet, Collection) once as both a little bit of optimization, but more so for data integrity, so I do not have the opportunity to load it differently for different sheets.

The main issues I see getting away from Arrays right now are:
* Already heavily invested in them, but this is not a good enough reason to not change
* Don't know if there is much cost to passing them, since it will by ByRef
* I use a Sort Function to create a Sorted "Pointer" array that lets me leave the Array in Step Flow order, while easily referencing it by Group / Sub-group order.

Since I am always trying to make my code for now and the future, I am not against updating the arrays to either RecordSets or Collections, but not merely for the sake of changing them to learn something cool. My arrays work and from my research, they add seconds to the run time, not substantial amounts for this 2 minute report. So If another structure is easier to update in the future than Two-dimensional Arrays of Record Types, then please let me know, but does anyone know the cost of passing an Array to a procedure, assuming you are not doing a ByVal pass?

解决方案

You've provided a good bit of detail, but it's still quite difficult to understand exactly what's going on without seeing some code. In your question, I can identify at least 4 big topics that you interweave throughout: Manufacturing, Data Access, VBA, and Coding Best-Practices. It's hard for me to tell exactly what you're asking because your question scope is huge. Either way, I appreciate your trying to write better code in VBA.

It's hard for me to understand exactly what you plan to do with the arrays. You say:

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.

I'm not sure what you mean here. Are you using arrays to represent a row of data that you retrieved from a database? If so, you might consider using class modules instead of the usual "macro" modules. These will allow you to work with full-blown objects instead of arrays of values (or references, as the case may be). Classes take more work to set up and consume, but they make your code a lot easier to work with and will greatly help you to segment your code.

As user Emtucifor already pointed out, there may be objects such as ADO Recordset objects (which may require Access to be installed...not sure) that can help greatly. Or you might create your own.

Here's a long example of how using a class might help you. Although this example is lengthy, it will show you how a few principles of object-oriented programming can really help you clean up your code.

In the VBA editor, go to Insert > Class Module. In the Properties window (bottom left of the screen by default), change the name of the module to WorkLogItem. Add the following code to the class:

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

The above code will give us a strongly-typed object that's specific to the data with which we're working. When you use multi-dimension arrays to store your data, your code resembles this: arr(1,1) is the ID, arr(1,2) is the PersonName, and arr(1,3) is the HoursWorked. Using that syntax, it's hard to know what is what. Let's assume you still load your objects into an array, but instead use the WorkLogItem that we created above. This name, you would be able to do arr(1).PersonName to get the person's name. That makes your code much easier to read.

Let's keep moving with this example. Instead of storing the objects in array, we'll try using a collection.

Next, add a new class module and call it ProcessWorkLog. Put the following code in there:

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

The above class is going to be used to "do something" with a colleciton of WorkLogItem. Initially, we just set it up to count the total number of hours worked. Let's test the code we wrote. Create a new Module (not a class module this time; just a "regular" module). Paste the following code in the module:

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

In the above code, PopulateArray() simply creates a collection of WorkLogItem. In your real code, you might create class to parse your Excel sheets or your data objects to fill a collection or an array.

The TestGetHoursWorked() code simply demonstrates how the classes were used. You notice that ProcessWorkLog is instantiated as an object. After it is instantiated, a collection of WorkLogItem becomes part of the pwl object. You notice this in the line Set pwl.WorkLogItems = PopulateArray(). Next, we simply call the function we wrote which acts upon the collection WorkLogItems.

Why is this helpful?

Let's suppose your data changes and you want to add a new method. Suppose your WorkLogItem now includes a field for HoursOnBreak and you want to add a new method to calculate that.

All you need to do is add a property to WorkLogItem like so:

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

Of course, you'll need to change your method for populating your collection (the sample method I used was PopulateArray(), but you probably should have a separate class just for this). Then you just add your new method to your ProcessWorkLog class:

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

Now, if we wanted to update our TestGetHoursWorked() method to return result of GetHoursOnBreak, all we would have to do as add the following line:

    Debug.Print pwl.GetHoursOnBreak("Fred")

If you passed in an array of values that represented your data, you would have to find every place in your code where you used the arrays and then update it accordingly. If you use classes (and their instantiated objects) instead, you can much more easily update your code to work with changes. Also, when you allow the class to be consumed in multiple ways (perhaps one function needs only 4 of the objects properties while another function will need 6), they can still reference the same object. This keeps you from having multiple arrays for different types of functions.

For further reading, I would highly recommend getting a copy of VBA Developer's Handbook, 2nd edition. The book is full of great examples and best practices and tons of sample code. If you're investing a lot of time into VBA for a serious project, it's well worth your time to look into this book.

这篇关于Excel VBA编程与数组：传递或不传递它们？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！