By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,720 Members | 2,185 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,720 IT Pros & Developers. It's quick & easy.

Passing an array name to a function

P: 37
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.

Expand|Select|Wrap|Line Numbers
  2. example:
  4. array1 = array("a","b")  'these are in reality longer than this
  5. array2 = array("c","d")
  7. array3 = array("1","2","3","4")
  9. for j = 0 to Somevar
  10. If flagarr(j) = 1 Then
  12.             For i = 0 To UBound(array1)
  13.                 ReDim Preserve array3(UBound(array3) + 1)
  14.                 array3(UBound(array3)) = array1(i)
  16.             Next i
  18.  End If
  19. 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.....)
Jan 30 '08 #1
Share this Question
Share on Google+
11 Replies

P: 58
Expand|Select|Wrap|Line Numbers
  1. Sub Test()
  2.    Dim all() As Variant, array1() As Variant, array2() As Variant, array3() As Variant
  4.    array1 = Array("a", "b")
  5.    array2 = Array("c", "d")
  6.    array3 = Array("1", "2", "3", "4")
  8.    Call Merge_Arrays(all, array1, False)
  9.    Call Print_Array(all)
  11.    Call Merge_Arrays(all, array2, False)
  12.    Call Print_Array(all)
  14.    Call Merge_Arrays(all, array3, True)
  15.    Call Print_Array(all)
  17.    On Error GoTo ERR_HANDLER
  18.    Debug.Print array2(0)
  19.    Debug.Print array3(0)
  21.    Exit Sub
  23.    Debug.Print Err.Description
  24.    Resume Next
  25. End Sub
  27. Private Sub Merge_Arrays(ByRef all As Variant, ByRef source_array As Variant, clear_source As Boolean)
  28.    Dim i As Integer, j As Integer
  30.    On Error GoTo SET_INDEX
  32.    j = UBound(all) + 1
  34.    For i = 0 To UBound(source_array)
  35.       ReDim Preserve all(j)
  36.       all(j) = source_array(i)
  37.       j = j + 1
  38.    Next i
  40.    If clear_source Then
  41.       Erase source_array
  42.    End If
  44.    Exit Sub
  45. SET_INDEX:
  46.    j = 0
  47.    Resume Next
  48. End Sub
  50. Private Sub Print_Array(ByVal arr As Variant)
  51.    Dim i As Integer
  53.    For i = 0 To UBound(arr)
  54.       Debug.Print arr(i)
  55.    Next i
  57.    Debug.Print "-----------------------"
  58. End Sub
Jan 31 '08 #2

Expert 5K+
P: 8,434
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.
Jan 31 '08 #3

P: 58
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.
Jan 31 '08 #4

Expert 100+
P: 1,295
Other general suggestion:
Try to avoid using ReDim inside a FOR, it's not always possible, but you can always try.
Instead of:

Expand|Select|Wrap|Line Numbers
  1.    j = UBound(all) + 1
  3.    For i = 0 To UBound(source_array)
  4.       ReDim Preserve all(j)
  5.       all(j) = source_array(i)
  6.       j = j + 1
  7.    Next i
something like this will avoid using REDIM inside the FOR, and would be more general for any lbound, ubound of the arrays:

Expand|Select|Wrap|Line Numbers
  1. j= ubound(all) + 1 - lbound(source_array) 
  2. redim preserve all (j + ubound(source_array))
  3. for i = lbound(source_array) to ubound(source_array)
  4.     all(i + j) = sourcearray(i)
  5. 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.
Jan 31 '08 #5

Expert 5K+
P: 8,434
Thanks kadghar.

Yeah, I seem to recall ReDim is a pretty expensive statement. Better to do it once up front.
Jan 31 '08 #6

P: 37
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.
Feb 1 '08 #7

P: 37

Feb 5 '08 #8

P: 37
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
Feb 5 '08 #9

Expert 5K+
P: 8,434
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.
Feb 6 '08 #10

P: 37
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.
Feb 6 '08 #11

Expert 5K+
P: 8,434
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.
Feb 6 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.