arraysexcelvbaconstants

Can an array be declared as a constant?


Is it possible to either:

  1. Declare an array as a constant

    OR

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


Solution

  • You could use a function to return the array and use the function as an array.

    Function ConstantArray()
        ConstantArray = Array(2, 13, 17)
    End Function
    

    enter image description here

    enter image description here