Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Constant Array in VBA

1. Member
Join Date
18th February 2005
Posts
58

## 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

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Constant Array in VBA

Originally Posted by 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
Public Const a = (1, 10, 5)

3. Member
Join Date
18th February 2005
Posts
58

## 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

Excel Video Tutorials / Excel Dashboards Reports

4. ## Re: Constant Array in VBA

Hi,

Const arrays are not supported.
Alternatives are individual consts with similar names,
VB:
```Const A1=1
Const A2=10
Const A3=5

```
Or store a string as a constant and the split it into a global array
VB:
```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.

5. Member
Join Date
18th February 2005
Posts
58

## Re: Constant Array in VBA

Originally Posted by Andy Pope
Or store a string as a constant and the split it into a global array
VB:
```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?

Excel Video Tutorials / Excel Dashboards Reports

6. ## 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.
VB:
```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

```

7. Member
Join Date
18th February 2005
Posts
58

## Re: Constant Array in VBA

Thanks,

this works perfectly!!!

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno