Dynamically addressing global arrays

[JSSWAT]

Hello everyone and good morning! Or whatever it is where you are

So I have created a few macros which gather data from tabs into globally defined arrays. I would like to simplify by creating a single macro for this action. To do this I was planning on passing the name of the array to a sub/function which would handle the rest.

My problem is this...

I cannot get the macro to use the string as the name of the array...

I assumed there was an array object so I could do the following treating the array as an object... however, that does not seem to be a possibility.

Sub SimplyArray(strArrayName as String)
    Dim ary as Array
    Set ary = strArrayName
    ....

I am sure I am already wayyyy off base. Any help would be greatly appreciated.

 

[JSSWAT]

Perhaps I should have titled this just addressing arrays... to simplify the question how can you address an array by using another variable (i.e. a string)?

Thanks!

 

[MickG]

Not reall;y sure what your trying to do ,but this might help.

Private Sub CommandButton2_Click()
Dim Myarray As Variant
'Range + "A1:A10" contains numbers 10 to 20
Myarray = Range("A1:A10")
Call MySub(Myarray)
End Sub
Sub MySub(mRay)
'Transfer array to sub
MsgBox mRay(3, 1) ' Result should be "12"
End Sub

Mick

 

[ukmikeb]

Hi

Perhaps this thread -
http://www.mrexcel.com/forum/showthread.php?t=524665&highlight=array

will provide some food for thought.

 

[JSSWAT]

Thanks for your help MickG!

So I see now that I can pass it without declaring it as anything... awesome!

And it all sticks to my global!!!! You Rock.

Thanks :D

Not reall;y sure what your trying to do ,but this might help.

Private Sub CommandButton2_Click()
Dim Myarray As Variant
'Range + "A1:A10" contains numbers 10 to 20
Myarray = Range("A1:A10")
Call MySub(Myarray)
End Sub
Sub MySub(mRay)
'Transfer array to sub
MsgBox mRay(3, 1) ' Result should be "12"
End Sub

Mick

 

[JSSWAT]

Very interesting and PUNNY :P

I could actually try layering like that if the above doesn't work. However, I may come back to that for another problem.

Will keep you updated.

Hi

Perhaps this thread -
http://www.mrexcel.com/forum/showthread.php?t=524665&highlight=array

will provide some food for thought.