Dynamic reference to array

Copper Contributor

Hi, it's been a long time since I've done any VBA code within Access.  I have a situation where I globally defined 2 arrays.
Dim a1() as string

Dim a2() as string

Within a Sub routine, I need to call another sub routine passing the an argument for either one of the two array names defined in the global area.

I'm struggling to figure out how I can add values to the correct array dynamically.

sub addRecs(Byval arrayName as string)

  arrayName(1) = "new value"

end sub

When I run it this way, I get an error stating it's expecting an array.

5 Replies


That is a bridge to far, but this would work:

select case arrayName

  case "a1"

    'do your thing with global array a1()

  case "a2"
    'do your thing with global array a2()    

end select

Also note that if you want to grow an array, Redim Preserve is your friend.


Thanks Tom,

I actually did that as a work around, but was hoping to figure out a way to reference the array name through a variable value.

Maybe you can pass the array to work on as an argument?


Thanks, that was what I needed.  Here's what worked for me.


'Global Setting

Dim a1() as string

Sub call_testArray()

testArray a1

for x =1 to ubound(a1)

  debug.print a1(x)   'should print "Add array Value"

next x


Sub testArray(ByRef arrayName)

redim preserve arrayName(1)

arrayName(1) = "Add array Value"


Note that unless you change the default with OPTION BASE, array indexes start at 0.