Constant Array in VBA

[neverland]

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

[Brandtrock]

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

Public Const a = (1, 10, 5)

[neverland]

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

[Andy Pope]

Hi,

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

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

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

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.

[neverland]

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

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?

[Andy Pope]

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.

Const AVALUES = "1:10:5" 
Public A(2) As Integer 
 
Public Sub MyMacro() 
    Dim vntTemp As Variant 
    Dim intIndex As Integer 
    vntTemp = Split(AVALUES, ":") 
     
    For intIndex = 0 To 2 
        A(intIndex) = vntTemp(intIndex) 
    Next 
     
    Debug.Print A(0) 
    Debug.Print A(1) 
    Debug.Print A(2) 
End Sub 

[neverland]

Thanks,

this works perfectly!!!