VBA 公共数组：如何？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1654281/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Public Array : how to?
提问by Julien
So today's problem is getting me mad because that should be easy and i can not find the answer :
所以今天的问题让我很生气，因为这应该很容易，但我找不到答案：
How to declare a public array in VBA ? I'm using an array with the letters A, B, C,... because i'm working with Excel cells, and i don't want to declare it in every function i create, right ? I've tried to look on the web first and i read that you have to declare it in a different module, so that's what i've done :
如何在 VBA 中声明一个公共数组？我正在使用一个带有字母 A、B、C...的数组，因为我正在使用 Excel 单元格，而且我不想在我创建的每个函数中都声明它，对吧？我试着先在网上看，我读到你必须在不同的模块中声明它，所以这就是我所做的：
Public colHeader As String
colHeader = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L")
But Visual Basic doesn't like it...
但是 Visual Basic 不喜欢它...
So what shall i do ?
那我该怎么办？
Thank's a lot :)
非常感谢 ：）
Edit : the problem is more about asigning values to the array than to declare it
编辑：问题更多地是将值分配给数组而不是声明它
采纳答案by Stanislav Stoyanov
Declare array as global across subs in a application:
在应用程序中将数组声明为全局子项：
Public GlobalArray(10) as String
GlobalArray = Array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L')
Sub DisplayArray()
Dim i As Integer
For i = 0 to UBound(GlobalArray, 1)
MsgBox GlobalArray(i)
Next i
End Sub
Method 2: Pass an array to sub. Use ParamArray.
方法2：将数组传递给sub。使用参数数组。
Sub DisplayArray(Name As String, ParamArray Arr() As Variant)
Dim i As Integer
For i = 0 To UBound(Arr())
MsgBox Name & ": " & Arr(i)
Next i
End Sub
ParamArray must be the last parameter.
ParamArray 必须是最后一个参数。
回答by Heinzi
You are using the wrong type. The Array(...)function returns a Variant, not a String.
您使用了错误的类型。该Array(...)函数返回 a Variant，而不是 a String。
Thus, in the Declaration section of your module (it does not need to be a different module!), you define
因此，在你的模块的声明部分（它不需要是一个不同的模块！），你定义
Public colHeader As Variant
and somewhere at the beginning of your program code (for example, in the Workbook_Openevent) you initialize it with
并在程序代码开头的某个地方（例如，在Workbook_Open事件中）使用以下命令对其进行初始化
colHeader = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L")
Another (simple) alternative would be to create a function that returns the array, e.g. something like
另一个（简单的）替代方法是创建一个返回数组的函数，例如
Public Function GetHeaders() As Variant
GetHeaders = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L")
End Function
This has the advantage that you do not need to initialize the global variable and the drawback that the array is created again on every function call.
这样做的优点是不需要初始化全局变量，缺点是每次函数调用都会重新创建数组。
回答by Jaanus
This worked for me, seems to work as global :
这对我有用，似乎适用于全局：
Dim savePos(2 To 8) As Integer
And cancall it from every sub, for example getting first element :
并且可以从每个子节点调用它，例如获取第一个元素：
MsgBox (savePos(2))
回答by user28864
Option Explicit
Public myarray (1 To 10)
Public Count As Integer
myarray(1) = "A"
myarray(2) = "B"
myarray(3) = "C"
myarray(4) = "D"
myarray(5) = "E"
myarray(6) = "F"
myarray(7) = "G"
myarray(8) = "H"
myarray(9) = "I"
myarray(10) = "J"
Private Function unwrapArray()
For Count = 1 to UBound(myarray)
MsgBox "Letters of the Alphabet : " & myarray(Count)
Next
End Function
回答by Juan Joya
Well, basically what I found is that you can declare the array, but when you set it vba shows you an error.
好吧，基本上我发现你可以声明数组，但是当你设置它时，vba 会显示一个错误。
So I put an special sub to declare global variables and arrays, something like:
所以我放了一个特殊的子来声明全局变量和数组，比如：
Global example(10) As Variant
Sub set_values()
example(1) = 1
example(2) = 1
example(3) = 1
example(4) = 1
example(5) = 1
example(6) = 1
example(7) = 1
example(8) = 1
example(9) = 1
example(10) = 1
End Sub
And whenever I want to use the array, I call the sub first, just in case
每当我想使用数组时，我都会先调用 sub，以防万一
call set_values
Msgbox example(5)
Perhaps is not the most correct way, but I hope it works for you
也许不是最正确的方法，但我希望它对你有用
回答by Chris Van Opstal
Try this:
尝试这个：
Dim colHeader(12)
colHeader = ("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L")
Unfortunately the code found online was VB.NET not VBA.
不幸的是，在网上找到的代码是 VB.NET 而不是 VBA。
