Constant Array in VBA

    You can define constants in VBA as


    Public Const a=10


    But how can you define a constant array? I need:


    a(1)=1
    a(2)=10
    a(3)=5


    or something like that.


    Many thanks


    Peter

    Re: Constant Array in VBA



    Public Const a = (1, 10, 5)

    Re: Constant Array in VBA


    That was indeed my first guess, but this highlights in red, hence it contains a syntactic error... I also tried with (1;10;5) { 1;10;5} and {1,10,5} and none of them worked

    Re: Constant Array in VBA


    Hi,


    Const arrays are not supported.
    Alternatives are individual consts with similar names,

    Code
    Const A1=1
Const A2=10
Const A3=5


    Or store a string as a constant and the split it into a global array

    Code
    Const AVALUES = "1:10:5"
Public A(2) as Integer


Public Sub MyMacro()
    A = Split(AVALUES, ":")
    Debug.Print A(0)
    Debug.Print A(1)
    Debug.Print A(2)
End Sub


    Another alternative could be to use a class. Here you could use an array and make the values readonly.

    Re: Constant Array in VBA


    Quote from Andy Pope

    Or store a string as a constant and the split it into a global array

    Code
    Const AVALUES = "1:10:5"
Public A(2) as Integer


Public Sub MyMacro()
    A = Split(AVALUES, ":")
    Debug.Print A(0)
    Debug.Print A(1)
    Debug.Print A(2)
End Sub


    This example seems just the thing. However if I try it, I get the following error:
    Compile Error: Can't assign to array


    What am I doing wrong?

    Re: Constant Array in VBA


    You do nothing wrong, my bad :(


    The result of the split function needs to be a variant and not a specified integer array.
    Try this mod.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login