Solved: Array as a Public Const

[みどり]

Hello everyone,

I have a simple question that I can't deal with by myself.
I couldn't find if it was solved earlier (>500 search results - it's easier to read all the forum), so don't blame me if it really was.

I want to make a Public Const of an array of a fixed size. Something like
Public Const A(10) = ... and then declare somehow each of 10 entries in A().

Reason: I need a list of strings, avaliable from anywhere in the project. I have many worksheets; of them are special ones ("Final Report", "input data"), others are looped through for varoius actions. I need constants with their names. Now I have only two "special" sheets, but I want to make my code "easy-to-scale", so an array avaliable from anywhere in the project would be terrific...

Thank you!! ^_^

[Bob Phillips]

I don't think so, you need to declare it as a public variant and load it in a procedure



Public A As Variant

Sub Proc()

A= Array ("Sheet1", "Sheet2")

End SUb

[みどり]

I don't think so, you need to declare it as a public variant and load it in a procedure

Thank you. I've solved the issue by means of a special module and a function:

Module T1:


Public SL(), Sn
Function SheetName(i)
Sn = 2
ReDim SL(Sn)
SL(1) = "Sheet1"
SL(2) = "Sheet2"
SheetName = SL(i)
End Function


Now I can access this list, as well as its length, from anywhere in my code:


Sub zz()
Debug.Print VBAProject.T1.SheetName(1)
Debug.Print VBAProject.T1.Sn
End Sub

[Bob Phillips]

In other words, you declared it as a public variant and loaded it in a procedure.

[みどり]

Oh, I cannot make a public array of non-fixed size, so I have to do my T1 module as follows:

Public Sn
Dim SL()

Function SheetName(i)
SheetName = SL(i)
End Function

Sub Activate()
Sn = 2
ReDim SL(Sn)
SL(1) = "Sheet1"
SL(2) = "Sheet2"
End Sub

Now everything is the way I want it to be, thanks for assistance. All I have to do is change the contents "Activate" sub, and no more code changes are needed elsewhere if I have some new sheet to take care of. ^_^

[Bob Phillips]

I didn't say a Public array, I said a public variant, which is what you did.