Constant Array in VBA

neverland · May 25th 2005

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 · May 25th 2005

Re: Constant Array in VBA

Quote from 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

Display More

Public Const a = (1, 10, 5)

neverland · May 25th 2005

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

**Andy Pope** · May 25th 2005

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.

neverland · May 25th 2005

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?

**Andy Pope** · May 25th 2005

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.

Code

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

Display More

neverland · May 25th 2005

Re: Constant Array in VBA

Thanks,

this works perfectly!!!

Constant Array in VBA

Participate now!

Share