数组可以声明为常量吗?

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.

任何可行的答案都是受欢迎的，但是理想的答案是可以设置一次，并且在修改其他代码时不需要任何更改/维护。

6 个解决方案

#1

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

#2

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!

没有人可以改变它，调整它的大小，或者编辑它的内容……此外，您可以在一行中定义常量!

#3

I declared a String constant of "1,2,3,4,5" and then used Split to create a new array, like so:

我声明了一个字符串常量为“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 ReDim or ReDim Preserve on A it 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保存时，它没有让我。这种方法的缺点是，即使不能更改数组的大小，也可以编辑数组的值。

#4

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 Split function to create an array.

变通方法——我能想到的最简单的方法是使用delim定义一个常量，然后使用Split function创建一个数组。

Const myConstant = "2,13,17"

Sub Test()
    i = Split(myConstant, ",")

    For j = LBound(i) To UBound(i)
        Debug.Print i(j)
    Next
End Sub

#5

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

#6

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

#1