vba 数组可以声明为常量吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41046174/
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
Can an array be declared as a constant?
提问by ChrisB
Is it possible to either:
是否可以：
Declare an array as a constant
OR
Use a workaround to declare an array that is protected from adding, deleting or changing elements, and therefore functionally constant during the life of a macro?
将数组声明为常量
或者
使用一种变通方法来声明一个数组，该数组不会被添加、删除或更改元素，因此在宏的生命周期中功能不变？
Of course I could do this:
我当然可以这样做：
Const myConstant1 As Integer = 2
Const myConstant2 As Integer = 13
Const myConstant3 As Integer = 17
Const myConstant4 ...and so on
...but it loses the elegance of working with arrays. I could also load the constants into an array, and reload them each time I use them, but any failure to reload the array with those constant values before use could expose the code to a "constant" value that has changed.
...但它失去了使用数组的优雅。我也可以将常量加载到一个数组中，并在每次使用它们时重新加载它们，但是在使用之前使用这些常量值重新加载数组的任何失败都可能使代码暴露于已更改的“常量”值。
Any workable answer is welcome but the ideal answer is one that can be setup once and not require any changes/maintenance when other code is modified.
欢迎任何可行的答案，但理想的答案是可以设置一次并且在修改其他代码时不需要任何更改/维护。
回答by
You could use a function to return the array and use the function as an array.
您可以使用函数返回数组并将该函数用作数组。
Function ContantArray()
ContantArray = Array(2, 13, 17)
End Function
回答by A.S.H
How about making it a function? Such as:
把它变成一个函数怎么样？如：
Public Function myConstant(ByVal idx As Integer) As Integer
myConstant = Array(2, 13, 17, 23)(idx - 1)
End Function
Sub Test()
Debug.Print myConstant(1)
Debug.Print myConstant(2)
Debug.Print myConstant(3)
Debug.Print myConstant(4)
End Sub
Nobody can change it, resize it, or edit its content... Moreover, you can define your constants on just one line!
没有人可以改变它、调整它的大小或编辑它的内容......此外，您可以在一行中定义您的常量！
回答by Soulfire
I declared a Stringconstant of "1,2,3,4,5"and then used Splitto create a new array, like so:
我声明了一个String常量，"1,2,3,4,5"然后用于Split创建一个新数组，如下所示：
Public Const myArray = "1,2,3,4,5"
Public Sub createArray()
Dim i As Integer
A = Split(myArray, ",")
For i = LBound(A) To UBound(A)
Debug.Print A(i)
Next i
End Sub
When I tried to use ReDimor ReDim Preserveon Ait did not let me. The downfall of this method is that you can still edit the values of the array, even if you can't change the size.
当我试图使用ReDim或ReDim Preserve在A它没有让我。这种方法的缺点是你仍然可以编辑数组的值，即使你不能改变大小。
回答by S Meaden
If the specific VBA environment is Excel-VBA then a nice syntax is available from the Excel Application's Evaluate method which can be shortened to just square brackets.
如果特定的 VBA 环境是 Excel-VBA，那么可以从 Excel 应用程序的 Evaluate 方法中获得一个很好的语法，该方法可以缩短为方括号。
Look at this
看这个
Sub XlSerialization1()
Dim v
v = [{1,2;"foo",4.5}]
Debug.Assert v(1, 1) = 1
Debug.Assert v(1, 2) = 2
Debug.Assert v(2, 1) = "foo"
Debug.Assert v(2, 2) = 4.5
'* write all cells in one line
Sheet1.Cells(1, 1).Resize(2, 2).Value2 = v
End Sub
回答by Tragamor
No - arrays can't be declared as constant but you can use a workaround.
否 - 数组不能声明为常量，但您可以使用一种解决方法。
You can create a function that returns the array you want
您可以创建一个返回所需数组的函数
http://www.vbaexpress.com/forum/showthread.php?1233-Solved-Declare-a-Constant-Array
http://www.vbaexpress.com/forum/showthread.php?1233-Solved-Declare-a-Constant-Array
回答by Pankaj Jaju
Can an array be declared as a constant? No.
数组可以声明为常量吗？不。
Workarounds - Simplest one I can think of is to define a constant with delim and then use Splitfunction to create an array.
解决方法 - 我能想到的最简单的方法是使用 delim 定义一个常量，然后使用Split函数创建一个数组。
Const myConstant = "2,13,17"
Sub Test()
i = Split(myConstant, ",")
For j = LBound(i) To UBound(i)
Debug.Print i(j)
Next
End Sub
回答by Pragmateek
If you don't need a new instance each time you can use a Staticlocal variable to avoid multiple objects creation and initialization:
如果您不需要每次都创建一个新实例，您可以使用Static局部变量来避免多个对象的创建和初始化：
Private Function MyConstants()
Static constants As Variant
If IsEmpty(constants) Then
constants = Array(2, 13, 17)
End If
MyConstants = constants
End Function
回答by Webrohm
Is this too simplistic?
这是不是太简单了？
PUBLIC CONST MyArray = "1,2,3,4"
then later in a module:
然后在一个模块中：
Dim Arr as Variant
SET Arr = split(MyArray,",")
回答by BillR
Don't know when this changed, but in Excel 365, this works (or, at least, does not generate a compiler error): Const table1Defs As Variant = Array("value 1", 42, Range("A1:D20"))
不知道这什么时候改变了，但在 Excel 365 中，这有效（或者，至少不会产生编译器错误）：Const table1Defs As Variant = Array("value 1", 42, Range("A1:D20" ))
