Passing an array name to a function

[JonathanS]

Hi-
I am working on a project in VBA in Excel 2000. I have a loop in which I want to append a certain (previously defined) array to another array.

 
example:
 
array1 = array("a","b")  'these are in reality longer than this
array2 = array("c","d")
 
array3 = array("1","2","3","4")
 
for j = 0 to Somevar
If flagarr(j) = 1 Then
 
            For i = 0 To UBound(array1)
                ReDim Preserve array3(UBound(array3) + 1)
                array3(UBound(array3)) = array1(i)
 
            Next i
 
 End If
Next j
 

My question is how to pass the names of array1 and array2 to this loop so I don't have to write out a for i = 0 to UBound(array1) loop for each individual array I want to add

Thanks for any help you can give
(there is probably a more effective way to do this as well, so if you have any input on that also.....)

[WinblowsME]

Sub Test()
   Dim all() As Variant, array1() As Variant, array2() As Variant, array3() As Variant
 
   array1 = Array("a", "b")
   array2 = Array("c", "d")
   array3 = Array("1", "2", "3", "4")
 
   Call Merge_Arrays(all, array1, False)
   Call Print_Array(all)
 
   Call Merge_Arrays(all, array2, False)
   Call Print_Array(all)
 
   Call Merge_Arrays(all, array3, True)
   Call Print_Array(all)
 
   On Error GoTo ERR_HANDLER
   Debug.Print array2(0)
   Debug.Print array3(0)
 
   Exit Sub
ERR_HANDLER:
   Debug.Print Err.Description
   Resume Next
End Sub
 
Private Sub Merge_Arrays(ByRef all As Variant, ByRef source_array As Variant, clear_source As Boolean)
   Dim i As Integer, j As Integer
 
   On Error GoTo SET_INDEX
 
   j = UBound(all) + 1
 
   For i = 0 To UBound(source_array)
      ReDim Preserve all(j)
      all(j) = source_array(i)
      j = j + 1
   Next i
 
   If clear_source Then
      Erase source_array
   End If
 
   Exit Sub
SET_INDEX:
   j = 0
   Resume Next
End Sub
 
Private Sub Print_Array(ByVal arr As Variant)
   Dim i As Integer
 
   For i = 0 To UBound(arr)
      Debug.Print arr(i)
   Next i
 
   Debug.Print "-----------------------"
End Sub

[Killer42]

One general suggestion - use LBound() function rather than assuming all arrays start at zero. Some of us prefer to use numbers that make sense to people rather than computers.

[WinblowsME]

One general suggestion - use LBound() function rather than assuming all arrays start at zero.

That's a great suggestion, and for some reason, I have never even thought of using LBound. Thanks.

[kadghar]

Other general suggestion:
Try to avoid using ReDim inside a FOR, it's not always possible, but you can always try.
e.g.
Instead of:

   j = UBound(all) + 1
 
   For i = 0 To UBound(source_array)
      ReDim Preserve all(j)
      all(j) = source_array(i)
      j = j + 1
   Next i

something like this will avoid using REDIM inside the FOR, and would be more general for any lbound, ubound of the arrays:

j= ubound(all) + 1 - lbound(source_array) 
redim preserve all (j + ubound(source_array))
for i = lbound(source_array) to ubound(source_array)
    all(i + j) = sourcearray(i)
next

note: it's not necessary to use j, but it makes the code easier to read. It'll be the index that source_array(0) would use in "all", a little bit confusing, but believe me, it works for any lbound of the source array, even if its not zero.

[Killer42]

Thanks kadghar.

Yeah, I seem to recall ReDim is a pretty expensive statement. Better to do it once up front.

[JonathanS]

Thanks for all the helpful replies. I do have one little twist on the problem, though, and perhaps it is best approached by the merge arrays function written by WinB: I want to be able to either append an array or not depending on a condition that is set in a dialogbox, so I would be choosing from a number of them, but not necessarily all of them.

For example (in some pcode):


a = {1,2,3}, b = {4,5,6}, c = {a,b,c}

if condition 1 = true and condition 2 =false and condition3 = false Then
array = {1,2,3}
if condition 1 = true and condition2=false and condition3=true Then
array = {1,2,3,a,b,c}

etc. (so my original motivation was to be able to step through a list of the arrays and pass them to the merging function)

Perhaps I'm making life unnecessarily difficult..... lol I might revert back to the old inputbox.

[JonathanS]

bump

---------------->

[JonathanS]

Please forgive the double post, but it seems my ability to edit has disappeared (or been taken away?).

Let me delineate my problem in a different way:

how can I reference elements of an array whose name is defined as a string elsewhere in the program?

for example, str = "x"

then later str = Array(1,2,3) ( I know that this won't work in reality)

and be able to reference say x(1) after that

[Killer42]

I've been thinking about this for a while. The only thing I can come up with is to try checking out the Microsoft Scripting Runtime (or script control). I believe it allows you to execute code dynamically from a string. Whether it will provide the ability to dynamically identify your arrays in the way you describe, I have no idea.

[JonathanS]

I had played around with the Application.Eval() function, but it seems to have different implementations in different dialects and changes with every other new release lol.... I thought about building a 2d array with Array(old,new) and then "flattening" it out to a 1D in the end.

My final solution was less than elegant, but it gets the job done. I have a large constant array in the code and the portions of the array that are desired are "searched" for and placed in a new array.

Anyway, thanks much for thinking about it.

[Killer42]

Oh well, as long as you've got it working.

Oh, and by the way. You can only edit messages for a limited time after posting them. The limit used to be 5 minutes, but I think it's one hour now.