使用数组进行 Excel VBA 编程：传递还是不传递？

Excel VBA Programming with Arrays: To Pass them or Not To Pass them?

提问by Craig

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

问题：我想知道在 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

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

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)

数组是：
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（用作指针 Array 对 Flow、Sub Flow 和 Data 进行排序以组、子组和步进顺序排列，同时按步进顺序保留原始数组）

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.

可能性：
1) 将宏重写为一个大程序，该程序将数据加载到程序中的主数组中，一旦
Pro：在程序中进行维度处理，而不是作为模块中的公共变量而不是传递。
缺点：用一个大型程序而不是多个较小的程序更难调试。

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

2) 使用多个过程保留宏但通过 Arrays
Pro：使用多个较小的过程更容易调试代码。
缺点：传递数组（昂贵？）

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?)

3) 使用多个过程保留宏，但数组是 Module
Pro中的公共变暗变量：使用多个较小的过程更容易调试代码。
缺点：公共数组（昂贵？）

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.

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

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).

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

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) onceas 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.

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

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.

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

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?

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

回答by Ben McCormack

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.

您提供了很多细节，但如果不看一些代码，仍然很难准确理解发生了什么。在您的问题中，我可以确定您贯穿始终的至少 4 个大主题：制造、数据访问、VBA 和编码最佳实践。我很难确切地说出你在问什么，因为你的问题范围很大。无论哪种方式，我感谢您尝试在 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 modulesinstead 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 Recordsetobjects (which may require Access to be installed...not sure) that can help greatly. Or you might create your own.

正如用户 Emtucifor 已经指出的那样，可能有一些ADO Recordset对象（可能需要安装 Access ……不确定）之类的对象可以提供很大帮助。或者您可以创建自己的。

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:

在 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

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 WorkLogItemthat we created above. This name, you would be able to do arr(1).PersonNameto get the person's name. That makes your code much easier to read.

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

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

让我们继续这个例子。我们将尝试使用collection.

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

接下来，添加一个新的类模块并调用它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

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:

上面的类将用于通过集合“做某事” 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

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.

在上面的代码中，PopulateArray()简单地创建了一个WorkLogItem. 在您的实际代码中，您可能会创建类来解析 Excel 工作表或数据对象以填充集合或数组。

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

该TestGetHoursWorked()代码仅演示了如何使用这些类。您注意到它ProcessWorkLog被实例化为一个对象。实例化后，集合WorkLogItem成为pwl对象的一部分。你注意到这一行Set pwl.WorkLogItems = PopulateArray()。接下来，我们只需调用我们编写的作用于集合的函数WorkLogItems。

Why is this helpful?

为什么这有帮助？

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

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

All you need to do is add a property to WorkLogItemlike so:

您需要做的就是添加一个属性来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

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 ProcessWorkLogclass:

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

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:

现在，如果我们想更新我们的TestGetHoursWorked()方法以返回 的结果GetHoursOnBreak，我们只需添加以下行：

    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.

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

For further reading, I would highlyrecommend 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.

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

回答by ErikE

It sounds like maybe Excel and arrays are not the best tools for the job you're doing. If you could please explain a little bit about the type of data that you're working with and what you're doing, that will really help provide a better answer. Give as much detail as you can about the types of manipulations you're doing on the data and what the inputs and outputs are.

听起来 Excel 和数组可能不是您正在做的工作的最佳工具。如果您可以解释一下您正在使用的数据类型以及您在做什么，这将真正有助于提供更好的答案。尽可能详细地说明您对数据进行的操作类型以及输入和输出是什么。

I'm going to give some highlights that I think will help you, and then may edit my answer to be more complete as I get responses from you, and so I have more time to flesh things out a bit.

我将给出一些我认为会对您有所帮助的重点，然后在收到您的回复时可能会编辑我的答案以使其更加完整，因此我有更多时间来充实一些东西。

• There is an object that naturally handles the record-type objects you're working with called a Recordset. In the VBA editor, go to Tools -> References and add Microsoft ActiveX Data Objects 2.X Library (the highest one on your machine). You can declare an object of type ADODB.Recordset, then do Recordset.Fields.Append to add fields to it, then .Open it and finally .AddNew, set field values, and .Update. This is a natural object to pass around in programs as an input or output parameter. It has natural traversal and positioning functions (.Eof, .Bof, .AbsolutePosition, .MoveNext, .MoveFirst, .MovePrevious) and supports searching and filtering (.Filter = "Field = 'abc'", .Find and so on).

• I don't recommend using public variables, though without an understanding of what you're doing I can't really advise you well here.

• I also would avoid one big procedure. Code should be broken out into reusable functional units that do only one thing, whose names are essentially self-documenting about what they do.

• If you want to improve the performance of your code, hit ctrl-break at random times while it's running and break into the code. Then press Ctrl-L to view the call stack. Make a note of what is in the list each time. If any item shows up a majority of the time, it is the bottleneck and is where you should spend your time trying to optimize it. However, I don't advise trying to optimize what you have until you make some higher-level decisions (like whether you will switch to a recordset).

• 有一个对象可以自然地处理您正在使用的记录类型对象，称为 Recordset。在 VBA 编辑器中，转到 Tools -> References 并添加 Microsoft ActiveX Data Objects 2.X Library（您机器上最高的一个）。您可以声明一个 ADODB.Recordset 类型的对象，然后执行 Recordset.Fields.Append 向其添加字段，然后 .Open 和最后 .AddNew，设置字段值和 .Update。这是在程序中作为输入或输出参数传递的自然对象。它具有自然的遍历和定位功能（.Eof、.Bof、.AbsolutePosition、.MoveNext、.MoveFirst、.MovePrevious）并支持搜索和过滤（.Filter = "Field = 'abc'"、.Find 等）。

• 我不建议使用公共变量，尽管在不了解您在做什么的情况下，我不能在这里为您提供很好的建议。

• 我也会避免一个大的程序。代码应该被分解为只做一件事的可重用功能单元，它们的名称本质上是关于它们所做的事情的自我记录。

• 如果您想提高代码的性能，请在运行时随机按 ctrl-break 并进入代码。然后按 Ctrl-L 查看调用堆栈。每次记下列表中的内容。如果任何项目大部分时间都出现，那就是瓶颈，您应该花时间尝试优化它。但是，我不建议尝试优化您拥有的内容，直到您做出一些更高级别的决定（例如您是否将切换到记录集）。

I really need more information to help you better.

我真的需要更多信息来更好地帮助你。

If you're interested, I'll work up some demonstration code that will show how useful the Recordset object is. Inserting the data from a Recordset into an Excel range is super easy with Recordset.GetRows or .GetString (though some array transposition may be required, that's not hard, either).

如果您有兴趣，我将编写一些演示代码来展示 Recordset 对象的有用性。使用 Recordset.GetRows 或 .GetString 将 Recordset 中的数据插入 Excel 范围非常容易（尽管可能需要一些数组转置，但这也不难）。

UPDATE: If your goal is to speed up your process, then before doing anything I think it's best to be armed with the knowledge of what is taking the most time. Would you please hit ctrl-break about 10 times and note down the call stack each time, then tell me what the most common items in the call stack are?

更新：如果您的目标是加快流程，那么在做任何事情之前，我认为最好先了解什么最耗时。请您按 ctrl-break 大约 10 次并每次记下调用堆栈，然后告诉我调用堆栈中最常见的项目是什么？

In terms of updating the speed of cell formatting, here's my experience:

在更新单元格格式的速度方面，这是我的经验：

1. Merge is the slowest operation you can possibly do. Try to avoid it if at all possible. Using "center across selection" is one alternative. Another is just not merging, but using some combination of sizing properly, borders, cell background color, and turning off gridlines for the entire workbook.

2. Apply borders or other formatting once to the largest thing possible instead of to many small things such as cell by cell. For example, if most cells have all borders but some don't, then apply all borders to the entire range and during your looping remove the ones you don't want. And even then, try to do entire rows and larger ranges.

3. Save a template file with borders and formatting already applied. Let's say you put one row in it with the formatting for a certain section. In one step duplicate that row into as many rows are needed for that section, say 20 rows, and they will all have the same formatting. Duplicating rows is MUCH faster than applying formatting cell by cell.

1. 合并是您可能执行的最慢的操作。如果可能的话，尽量避免它。使用“跨选择居中”是一种选择。另一个只是不合并，而是使用适当大小、边框、单元格背景颜色和关闭整个工作簿的网格线的某种组合。

2. 将边框或其他格式应用于可能最大的事物，而不是应用于许多小事物，例如逐个单元格。例如，如果大多数单元格都有所有边框，但有些没有，则将所有边框应用于整个范围，并在循环过程中删除不需要的边框。即便如此，尝试做整行和更大的范围。

3. 保存已应用边框和格式的模板文件。假设您在其中放置了一行，其中包含特定部分的格式。在一个步骤中，将该行复制为该部分所需的行数，例如 20 行，并且它们都将具有相同的格式。复制行比逐个单元格应用格式要快得多。

Also, I wouldn't automatically go for using classes. While OO is great and I do it myself (heck, I just built 8 classes for something the other day to model a hierarchical structure so I could easily expose the parts of it when I needed them), in practice it can be slower. A simple set of public variables in a class is faster than using getters and setters. A user defined Type is even faster than a class, but you can run into gotchas trying to pass around UDTs in classes (they have to be declared in a non-class public module and even then they can give problems).

另外，我不会自动去使用类。虽然 OO 很棒而且我自己做（哎呀，我前几天刚刚为某事构建了 8 个类来建模层次结构，这样我就可以在需要时轻松地公开它的部分），但实际上它可能会更慢。类中一组简单的公共变量比使用 getter 和 setter 更快。用户定义的类型甚至比类更快，但是您可能会遇到试图在类中传递 UDT 的问题（它们必须在非类公共模块中声明，即使这样它们也会产生问题）。

Erik

埃里克