我的数据对象在 VBA 中应具有什么范围
我正在从工作表数据填充一组数组。然后，我在将数据写入另一个工作表之前对其进行操作（请参见下面的示例）。
如果我想再次使用相同的工作表数据，如何避免重写从工作表填充数组的代码？
- 我应该在一个很长的过程中编写代码，这样我就不需要重新填充数组吗？
- 我应该将数组设置为全局数组，以便可以在多个过程中重用它们吗？
- 我应该将数组仅传递给在长参数列表中需要它们的过程吗？
- 还有其他选择吗？
Sub ManipulateData()
Dim people(1 To MAX_DATA_ROW) As String
Dim projects(1 To MAX_DATA_ROW) As String
Dim startDates(1 To MAX_DATA_ROW) As Date
Dim endDates(1 To MAX_DATA_ROW) As Date
...
Loop through worksheet cells to populate arrays
...
Do something with array data
End Sub
I am populating a set of arrays from worksheet data. I then manipulate the data before writing it out to another worksheet (see example below).
If I want to then use the same worksheet data again, how can avoid rewriting the code that populates the arrays from the worksheet?
- Should I write my code in one long procedure so I don't need to repopulate the arrays?
- Should I make the arrays global so that they can be reused in several procedures?
- Should I pass the arrays just to the procedures that need them in a long argument list?
- Any other options?
Sub ManipulateData()
Dim people(1 To MAX_DATA_ROW) As String
Dim projects(1 To MAX_DATA_ROW) As String
Dim startDates(1 To MAX_DATA_ROW) As Date
Dim endDates(1 To MAX_DATA_ROW) As Date
...
Loop through worksheet cells to populate arrays
...
Do something with array data
End Sub
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我将创建一个类，其中包含作为
private变量的数组，然后创建一个名为Initialise或类似的公共子程序，用于加载所有内容，然后根据需要加载其他子程序/函数为您提供所需的功能。这样你就不必传递数组，但它们仍然不是全局的。使它们成为全局的问题在于，其他地方的其他代码会以您的代码不期望的某种方式更改数组，这可能很难找到，因为它可能位于代码库中的任何位置，因此风险更大。
以下是 Excel VBA 中的类简介。
I'd create a class that would contain the arrays as
privatevariables and then create one public sub calledInitialiseor similar that loads everything up and then other subs/functions as needed to give you the functionality you need.That way you don't have to pass the arrays around but they're still not global. The problem with making them global is that it's more risk that some other code somewhere else would change the arrays in some way that your code isn't expecting which can be very hard to find since that might be anywhere in the codebase.
Here's an introduction to classes in Excel VBA.