Passing the name of an array

**cbh35711** · 10-03-2006, 08:39 PM

I want to call a function several times, but pass in the array it's going to use each time.
I'm not sure how to do this. I thought i would just pass in the name of the array as a string, but i can't figure out how to use in something like:

 
Dim NDC11(5000) As String

this = "NDC11"

Function test(this As String)

this(counter) = ActiveCell.Value

End Function

I just need an example of how it would work. If anyone would have a better way to do it i would be grateful.

Thanks guys,

Chris

**raypayette** · 10-04-2006, 05:47 AM

The idea is to make your array Public:
Public Ar() As String

Sub testarray()
ReDim Ar(2)
Ar(1) = "test"
Ar(2) = "Test2:"
retest
End Sub

Sub retest()
MsgBox Ar(2)
End Sub

The declaration can be in the main Module, then all other modules will recognize it.

**cbh35711** · 10-04-2006, 08:46 AM

Ok excellent, it took me a bit to realize i you meant i had to make the declaration outside of the macro.

So, then my second question would be how can i pass the name of the array into the function.
I want to call the same function multiple times, passing it different arrays to store the data.

Thanks Ray,

Chris

**cbh35711** · 10-04-2006, 01:44 PM

Is it possible to use pointers in an excel macro?
If i could use pointers i could figure out the solution, but i don't know how to declare them.

Thanks,

Chris

**starryknight64** · 10-04-2006, 02:10 PM

What ray has done is he has made the array a public or "global" variable. So you do no longer need to pass this array to another function unless it is outside of its scope.