Global array declaration

[geo77]

99% of the questions on this group can be answered by anyone with zero VBA experience but with the willingness to spend a few of minutes googling around and trying out the code. This one is not. So please, I need help from someone who actually knows VBA, not newbies (like me) who need to get activity points.

I found a lot of "recipes" but nothing to actually work for my project.

I have a large project containing animations with many features and color options stored/retrieved in/from arrays.

Let's say for example that one of the arrays is: arrDog=Array("white_dog", black_dog", "grey_dog").

How can I declare and initialize this array in a SINGLE place (let's say in Module3), so that the array could be used anywhere in the workbook (both sheets and modules)?

And while: Public Const ACertainName As Double = 277 declared in a module does that just fine for all my global constants (I use these liberally and reliably),

or

Sub position_x_y(shape, x As Double, y As Double)
With ActiveSheet.Shapes.Range(Array(shape))
.Left = x
.Top = y
End With
End Sub

does the same thing for a sub (can be called from everywhere while being written in only one place - I also use these subs liberally and reliably all over the project), I couldn't figure out how to do the same thing with arrays (multidimensional constants).

Based on search results I tried :

Public arrDog() As Variant
Public Sub initialize_parameters()
arrDog=Array("white_dog", black_dog", "grey_dog")
arrCat=Array("white_cat", black_cat", "grey_cat")
End Sub

or

Public arrDog() As Variant
Public Function initialize_parameters()
arrDog=Array("white_dog", black_dog", "grey_dog")
arrCat=Array("white_cat", black_cat", "grey_cat")
End Function

I wrote the code above in Module3 while trying to call the sub/function from every single procedure where needed. I even tried to declare and initialize the array on the top of each sheet and the result was the same: a compile error, VBA not being able to find the array. The project got very large and it works just fine IF the arrays are declared in each and every procedure (which is inconvenient since you declare and initialize the same thing 20 times, let's say). Thanks.

 

[RoryA]

You can declare an array as public like any other variable. You cannot declare a constant array, so you will always have to run a routine first to populate the array.

As an alternative, you can simply create a public function that returns an array and then call that anywhere you need the array.

 

[Repush]

try this

top of Module1:

Public Const doggies As String = "White_dog,Black_dog,Grey_dog"

in Module2 (other):

Private Function dog(i As Integer) As String
    Dim dogs
    dogs = Split(doggies, ",")
    dog = dogs(i)
End Function

 

[geo77]

You can declare an array as public like any other variable. You cannot declare a constant array, so you will always have to run a routine first to populate the array.

As an alternative, you can simply create a public function that returns an array and then call that anywhere you need the array.

Rory, can you give me an example using my array how to do that? Thanks. If you read my post I've done just that. If you cannot or you are to busy, please delete your reply so the post doesn't appear to have been answered. I really need help with this. Thanks again!

 

[geo77]

Thanks for the reply. I've seen this solution using "split" many times before and it's obviously a working one. However, like scratching your ear using a remote control robot, that defeats the purpose since what do I do if (like in my case) I have 100 arrays, create 100 functions with 100 names? I need a real solution. This is not a reasonable solution. My project is very large and I am already drowning in nomenclature. Something that can be applied easily in one place and the original array to be able to be used all over within the project WITHOUT extra names and WITHOUT extra parentheses. Obviously MS dropped the ball if I have to go to such lengths to solve something so needed and so elementary. Like I said in the original post, let people who know VBA answer this.

 

[Repush]

well . .

good luck with your project

 

[shg]

Declare your arrays as Public in a standard code module. They will be visible project-wide.

Use ONE initialization routine that gets called ONCE when the code starts to initialize all of the arrays. Their values will persist until the VBE is reset (or another procedure changes them).

 

[RoryA]

If you cannot or you are to busy, please delete your reply so the post doesn't appear to have been answered.

This is, as you should know by now, a free forum populated, and run, by volunteers. You do not get to choose who answers your questions (although your current attitude will, I'm sure, limit the number of people willing to try and help you), nor do you get to tell people to delete their answers.

Furthermore, and perhaps more importantly, you don't get to insult people trying to help you. That will not be tolerated.

I suggest you moderate the tone of your posts in future, so that we don't have to.

 

[shg]

'Twere i needing a bunch of static arrays, I'd put the data on a worksheet and load them from (perhaps dynamic) named ranges. Separating data from code is generally good practice.