Can I define an array constant?

[JenniferMurphy]

Is there a way I can define an array constant?

I tried to use the Array function

Const cols As Variant = Array(1, 4, 7, 10, 13)

but it gets an error that it expected a constant expression. Seems like that is a constant expression to me, but what do I know?

I know I can define a constant list and then use Split to turn into an array, but that is not a constant.

Any other ways?

 

[Gokhan Aycan]

A workaround proposed in StackOverflow: "You could use a function to return the array and use the function as an array."

Can an array be declared as a constant?

 

[JenniferMurphy]

A workaround proposed in StackOverflow: "You could use a function to return the array and use the function as an array."

Can an array be declared as a constant?

Yeah, I saw that. I should have mentioned that I saw it. I guess I was hoping for something a little more "normal".

So I guess the bottom line is that it is not possible. (sigh)

Thanks

 

[Gokhan Aycan]

Well, collections are read-only for basic data types, maybe you can use it instead?

 

[Tetra201]

Maybe

Sub Test()
    Const cols = "{1,4,7,10,13}"
    MsgBox Evaluate(cols)(2)
End Sub

 

[Rick Rothstein]

Is there a way I can define an array constant?

I tried to use the Array function

Const cols As Variant = Array(1, 4, 7, 10, 13)

but it gets an error that it expected a constant expression. Seems like that is a constant expression to me, but what do I know?

It is not a constant expression because it uses the Array function which is not a constant. The reason you cannot use any expression that calls out to an array is because of how constants are processed. Before VBA executes the code, it will first physically substitute the value for any declare constants everywhere in the code where the constant is specified. So if you had a constant declared like this...

Const MyConstant As Long = 123

VBA would physically look for all of the MyConstant keywords with in your code and physically replace it with 123. I used a number in my example but everything I said applies to text as well. Because this takes place prior to execution, there is no way functions to "do their thing", hence, you cannot use any function, cell reference, etc. to define your constant. Also, you must declare your constant in code before any of your code makes use of it... the substitution I talked about takes place from the location of the Const statement downward.

 

[JenniferMurphy]

Well, collections are read-only for basic data types, maybe you can use it instead?

Hmmm... I didn't know about Collections. I just did a little checking. Looks interesting. But I didn't see anything about them being read only. I'll read more.

 

[JenniferMurphy]

Maybe

Sub Test()
    Const cols = "{1,4,7,10,13}"
    MsgBox Evaluate(cols)(2)
End Sub

Now that is an interesting solution. Thanks. I never would have thought about it. I'll do a little testing.

 

[JenniferMurphy]

It is not a constant expression because it uses the Array function which is not a constant. The reason you cannot use any expression that calls out to an array is because of how constants are processed. Before VBA executes the code, it will first physically substitute the value for any declare constants everywhere in the code where the constant is specified. So if you had a constant declared like this...

Const MyConstant As Long = 123

VBA would physically look for all of the MyConstant keywords with in your code and physically replace it with 123. I used a number in my example but everything I said applies to text as well. Because this takes place prior to execution, there is no way functions to "do their thing", hence, you cannot use any function, cell reference, etc. to define your constant. Also, you must declare your constant in code before any of your code makes use of it... the substitution I talked about takes place from the location of the Const statement downward.

That makes sense, Rick. But VBA could have provided a way to define an array constant. Maybe something like,

Const MyArray as Long = (1,2,3; 2,4,6; 3,6,9)

 

[Rick Rothstein]

That structure is not a VBA construct... it is an Excel construct... there is no meaningful way for VBA to make a text swap of that for the constant name MyArray in the textual code... remember, nothing is executing yet so there is no such thing as an array at that point in the process. The idea of a constant is that it is complete unto itself so instead of reserving a memory location to house it (like is done with variables), no memory is needed as VBA simply substitutes the constant's name with what it equal just as if you typed it that way originally. Let's see if I can come at this from a different direction for you. Let's say that within your code you had this calculation...

C = 2 * 3.141592654 * R

Now, obviously 3.141592654 is the value of Pi. Let's say you had many other calculations using it throughout your code. You could keep typing those 10 digits over and over again. And if you had to revisit your code in 6 months you might remember that value is Pi. But why make things hard for you . The designers of VB (the root language underneath VBA) figure they would let you make this easy on yourself. They created constants so you could declare this...

Const Pi As Double = 3.141592654

and then in your code write this...

C = 2 * Pi * R

Less overall typing and easier to recognize in 6 month if you had to review or modify your code. Now, before VBA "compiles" your code into whatever it needs to actually execute your code, it first looks for the name Pi and physically puts back the number it is equal to. So you write this...

C = 2 * Pi * R

and VBA converts it to this...

C = 2 * 3.141592654 * R

and the continues. Both C and R will be given memory locations because they are real variables but PI is not given any memory location because after VBA starts doing its thing, Pi completely disappears. The constant construct is simply a convenience for the programmer that takes no memory to implement. Arrays, on the other hand, require storage in memory so they cannot be constants.