Call An Array From Multiple Modules

[D03274256]

I was curious if there was a way to call an Array from numerous modules. I am learning VBA, and have actually just learned how to declare an Array which lead me to be curious if there was a way to call an Array from multiple modules?

 

[Jon von der Heyden]

You can use globals, so in say module1

Public varArray as Variant

And then somewhere in say module 2

varArray = Range("A1:B10").Value2

And then perhaps in module 3:

Range("B1:C10").Value = varArray

 

[D03274256]

Thank you for your quick response! However, I am not following what you are saying..can you elaborate a little bit more for me, and thank you.

 

[Jon von der Heyden]

Ummm, ok I'll try.

When you want an array accessible to other modules within the same project, one way is to use a variant data type (and you can dimension this array, or even declare a different data type; but for the purpose of this lesson I'm sticking to a plain ol' variant).

Some people keep a dedicated module just for public variable declaration. So insert a module called 'mPublic'. Drop this code into mPublic

Public gvarArray as Variant

The Public statement is what explicitly tells Excel that this variable should be accessible throughout your VBA project. I'm sticking with a fairly common naming convention: g=global; var = Variant Data Type; Array = just the name I gave it.

Now at some point you are going to load your array. I don't know how you're doing this, or what you're loading into it. So for demonstration purposes, I am going to pretend I have a desperate need to load the values in A1:B10 into my array. All this is happening in a different module. So for examples sake insert a new module and call it mDesperateNeed. And plonk the following code into this new module.

Public Sub GetValues()
    'All sorts of stuff going on here
    gvarArray = Range("A1:B10").Value2
    'More stuff going on here
End Sub

So at this point I have this variant data type that is holding the values from A1:B10 in an array, and it's kind of just hanging about in the ether; or memory if you prefer...

So now lets say you need to call it within another routine somewhere else. Maybe you want to loop through each item and do something with each? Maybe you just want to unload the array somewhere else? Set up a new module and call it mSomewhereElse. And in that module drop in:

Public Sub DropValues()
    'All sorts of stuff going on here
    Range("C1:D10").Value = gvarArray
    'More stuff going on here
End Sub

So to test this, enter some random data in A1:B10. Run the macro called 'GetValues'. At this point the values are loaded into the array.

Next run the macro called 'DropValues' and you'll notice that it plonks thje contents of the array into C1:D10.

 

[D03274256]

That helps clarify things! Thanks for the very detailed explanation!!! I am off to try to implement your code in with my current code, and hopefully will be able to successfully do so.

Thanks again for the very detailed response