Excel中VBA编程使用数组：通过他们或不通过呢？ [英] Excel VBA Programming with Arrays: To Pass them or Not To Pass them?

问题描述

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

背景：我在Excel 2003中宏超过5000线。我已经把它在过去两年增加新的功能，新的程序，这有助于段code和调试，更改或添加到该功能。缺点是，我使用的大部分在多个程序相同的基本信息，这要求我将其加载到带有细微的差别多次阵列。我现在正在运行与运行时间长短的问题，所以我现在能够做一个全面改写。结果
此文件用于抓取制造流的多个项目（最多4个不同的设置窗口，总多达10个不同的流，每个可达1000步骤）与所述信息流具体而言，子流特异于分组/排序目的，和数据（如运动，库存，CT，...）搜索
然后，它会坚持将数据放到用来管理使用数据表的过程中多张纸被细读，图表和单元格格式来表示流程能力/历史。结果
流量是在Excel文件，而制造业数据与读取7种不同OO4O的Oracle SQL拉，有的多次重复使用

的数组是：结果
arrrFlow（1至1000，为1〜4），为记录类型与4串搜索
arrrSubFlow（1至1000，为1〜10），为记录类型与4串，2的整数，和1单结果
arrrData（1至1000，为1〜10），为记录类型1串，4个整数，12 long和1单结果
arriSort（1到1000，1〜​​4）为Integer（用作指针数组排序的流程，子流程和数据在一组，子集团，并同时保留步骤顺序原阵列步骤顺序）

的可能性：结果
1）重写宏成加载数据到过程中的尺寸，一旦结果主阵列的一大步骤
临：尺寸设计成的过程，而不是作为模块公用变量，而不是通过结果
缺点：哈德一个大型的程序，而不是多个较小的调试。

2）保持与多个程序的宏不过将数组结果
优点：更容易调试code。与多个小程序结果。
缺点：传递数组（？贵）

3）保持宏观与多个程序，但与数组是在模块结果公开Dim'ed变量
优点：更容易调试code。与多个小程序结果。
缺点：公共阵列（？贵）

那么，什么是社会的评价呢？有谁知道使用公共数组VS传递数组的代价？是不是这些价值失去了其易用性正在专注于一个功能我手续的费用？

更新：结果
我在离散水平（每步倍数）加载库存数据，在总体水平（每步一）运行数据，并转移库存的在总体水平的开端。我一步汇总清单数据放置在工作状态类别（运行，等待，......）我在床单上已经创建了目标数据。

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

我上放置多张纸这个数据，某些步骤顺序，一些组/子组的顺序。我还需要通过组和产品，组/子组和产品，线和产品，并且产品的一部分相加的数据。

我使用的记录类型，所以我其实有可读的三维阵列，arrSubFlow（1,1）.strStep（步骤1号装置的第1步的名称），arrData（10,5）.lngYest（昨日的运动第五设备的第十步）。

我的优化要点将是在我从头开始创建10页每一次的部分。但合并单元格，边框，接头，...这是一个非常耗时的过程。我将添加一个节将比较我的数据的页面，看是否需要改变，如果是这样，只有然后重新创建它，否则，我会清除数据的每一个部分，只写数据更改为表吧。这将是巨大的，根据我的时间记录数据。但是，每当我更新code，我总是试图提高code的其它方面。我看到的数据装载到一个结构（阵列，记录集，集合）的一次的优化都一点点，但更多的数据的完整性，所以我没有机会加载它不同的不同的表。

我看到从阵列越来越远的主要问题，现在是：结果
*已经投入巨资在其中，但是这不是一个好足够的理由不改变结果
*不知道有多大的成本传递它们，因为它会通过为ByRef结果
*我用一个排序函数创建一个排序的指针数组，让我留在台阶流动顺序阵列，而容易被组/分组顺序引用它。

因为我总是试图让我的code为现在和将来，我不反对更新阵列要么记录集或集合，而不仅仅是改变他们学习一些很酷的东西的缘故。我的阵列一起从我的研究，他们补充秒的运行时间，对于这2个一分钟报告没有大量。因此，如果另一个结构更容易在未来比记录类型的二维数组进行更新，那么请让我知道，但没有人知道将数组传递给一个过程的成本，假设你是不是做一个BYVAL通<？ / p>

解决方案

您已经提供了详细的好一点，但它仍然是相当困难的明白到底发生了什么，没有看到一些code。在你的问题，我可以找出至少4，你在整个交织大主题：制造业，数据访问，VBA，和编码最佳实践。这是我很难告诉你要问什么，因为你的问题的范围是巨大的。无论哪种方式，我AP preciate你想写出更好的code在VBA。

这是我很难准确地了解你打算用数组做什么。你说：

  

缺点是，我使用的大部分在多个程序相同的基本信息，这就要求我把它与细微的差别多次加载到数组。

我不知道你的意思在这里。您是否使用数组来重新present你从数据库中检索数据行？如果是的话，你可以考虑使用的类模块而不是通常的宏观的模块。这将让你与成熟的对象，而不是值数组工作（或参考，视情况而定）。班采取更多的工作来建立和使用，但它们让你的code更容易了很多工作，将极大地帮助你细分code。

由于用户Emtucifor已经指出的那样，有可能是对象，如 ADO记录集对象（可能需要访问安​​装...不知道），可以极大地帮助。或者你可以创建自己的。

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

在VBA编辑器，进入插入＆gt;类模块。在属性窗口（左下默认屏幕），更改模块为 WorkLogItem 的名称。以下code添加到类：

 显式的选项私人pTaskID只要
私人pPersonName作为字符串
私人pHoursWorked为双公共财产取得的TaskID（）只要
    的TaskID = pTaskID
高端物业公共财产令的TaskID（lTask​​ID长）
    pTaskID = lTask​​ID
高端物业公共属性获取PersonName的（）作为字符串
    PersonName的= pPersonName
高端物业公共属性让PersonName的（lPersonName作为字符串）
    pPersonName = lPersonName
高端物业公共属性获取HoursWorked（）为双
    HoursWorked = pHoursWorked
高端物业公共属性让HoursWorked（lHoursWorked为双）
    pHoursWorked = lHoursWorked
高端物业
 

以上code会给我们一个强类型的对象是特定的与我们正在使用的数据。当您使用多维数组存储你的数据，你的code类似于这样： ARR（1,1）是ID， ARR （1,2）是PersonName的，而 ARR（1,3）是HoursWorked。使用该语法，很难知道什么是什么。让我们假设你仍然是你的对象加载到一个数组，而是使用 WorkLogItem ，我们在上面创建的。这个名字，你就可以做到 ARR（1）.PersonName 来得到这个人的名字。这使得你的code更容易阅读。

让我们保持这个例子感动。相反，在存储阵列中的对象，我们将尝试使用集。

接下来，添加一个新的类模块，并调用它 ProcessWorkLog 。将下面的code在那里：

 显式的选项私人pWorkLogItems为集合公共属性获取WorkLogItems（）为集合
    设置WorkLogItems = pWorkLogItems
高端物业公共属性设置WorkLogItems（lWorkLogItem为集合）
    设置pWorkLogItems = lWorkLogItem
高端物业功能GetHoursWorked（strPersonName作为字符串）为双
    对错误转到Handle_Errors
    昏暗的WLI作为WorkLogItem
    昏暗doubleTotal为双
    doubleTotal = 0
    对于每个WLI在WorkLogItems
        如果strPersonName = wli.PersonName然后
            doubleTotal = doubleTotal + wli.HoursWorked
        万一
    接下来WLIExit_Here：
    GetHoursWorked = doubleTotal
        退出功能Handle_Errors：
        你可能会想捕获错误即会
        如果WorkLogItems尚未设置发生
        简历Exit_Here
结束功能
 

上面的类将被用来做什么与 WorkLogItem 的colleciton。最初，我们只是将它设置为计数工作小时总数。让我们试code，我们写了。创建一个新的模块（不是类模块这段时间，只是一个普通模块）。在模块中粘贴以下code：

 显式的选项功能PopulateArray（）为集合
    昏暗clnWlis为集合
    昏暗的WLI作为WorkLogItem
    把一些数据集合中的
    设置clnWlis =新集合    设置WLI =新WorkLogItem
    wli.TaskID = 1
    wli.PersonName =弗雷德
    wli.HoursWorked = 4.5
    clnWlis.Add WLI    设置WLI =新WorkLogItem
    wli.TaskID = 2
    wli.PersonName =莎莉
    wli.HoursWorked = 3
    clnWlis.Add WLI    设置WLI =新WorkLogItem
    wli.TaskID = 3
    wli.PersonName =弗雷德
    wli.HoursWorked = 2.5
    clnWlis.Add WLI    设置PopulateArray = clnWlis
结束功能子TestGetHoursWorked（）
    昏暗的PWL作为ProcessWorkLog
    昏暗arrWli（）作为WorkLogItem
    设置PWL =新ProcessWorkLog
    设置pwl.WorkLogItems = PopulateArray（）
    Debug.Print pwl.GetHoursWorked（弗雷德）结束小组
 

在上面的code， PopulateArray（）只是简单地创建 WorkLogItem 的集合。在你真正的code，可以创建类来分析你的Excel工作表或数据对象来填充集合或数组。

的 TestGetHoursWorked（） code简单地演示了如何在类中使用。您会注意到 ProcessWorkLog 实例化一个对象。它被实例化后， WorkLogItem 的集合成为 PWL 对象的一部分。您可以在该行注意到这个设置pwl.WorkLogItems = PopulateArray（）。接下来，我们简单地调用我们写的功能，作用在集合 WorkLogItems 。

这是为什么有用吗？

让我们假设你的数据变化，您要添加的新方法。假设你的 WorkLogItem 现在包括 HoursOnBreak 字段，你想添加一个新的方法来计算。

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

 私人pHoursOnBreak为双公共属性获取HoursOnBreak（）为双
    HoursOnBreak = pHoursOnBreak
高端物业公共属性让HoursOnBreak（lHoursOnBreak为双）
    pHoursOnBreak = lHoursOnBreak
高端物业
 

当然，你需要改变你的方法用于填充您的收藏（我用的样本方法 PopulateArray（），但你可能应该有一个单独的类只是这一点）。然后你只需要添加新的方法，你的 ProcessWorkLog 类：

 功能GetHoursOnBreak（strPersonName作为字符串）为双
     code拿到破发小时
结束功能
 

现在，如果我们想更新我们的 TestGetHoursWorked（）方法返回 GetHoursOnBreak 的结果，所有我们会需要做的是添加以下行：

  Debug.Print pwl.GetHoursOnBreak（弗雷德）
 

如果您在重新presented数据值数组过去了，你必须找到你code，你所使用的阵列，然后相应地更新它的每一个地方。如果您使用类（和它们的实例化对象），而不是，你可以更轻松地更新你的code。与修改工作。此外，当你让全班同学以多种方式被消耗（也许是一个功能只需要4对象的属性，而另一个功能将需要6个），他们仍然可以引用同一个对象。这样可以使您不必为不同类型的功能多个阵列。

有关进一步的阅读，我会的高的建议得到 VBA开发手册的副本，第2版。这本书充满了极大的实例和最佳做法，并吨样品code的。如果你投入了大量的时间进入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屋！