Arrays in macros

[Guest]

Within a sub procedure two other sub procedures are called consecutively.
The first sub procedure feeds values into an array.
I need this array with these values to then be used in the second sub
procedure.

As soon as the first sub procedure is complete the array becomes empty and
passes an empty array to the second sub procedure.
Thus givign me zero for all my calculations in the second array.

Please assist.

 

[Bob Phillips]

Either declare the array variable in the module declaration, that is before
any macros, or pass it as a parameter to the second macro.

Air-coded

Sub Macro1()
Dim myArray

myArray = Array(1,2,3)
Macro2 myArray

End Sub

Sub Macro2(ary As Variant)

msgbox ary(1)

End Sub

 

[Guest]

The arrays need to be public and static. Just DIM them outside the subs, not
inside the subs and the values will "live" from sub call to sub call

Have a good day

 

[Bob Phillips]

Gary,

They only need to be public if used across modules. If used in separate
macros in the same module, private is fine (preferable?).

 

[Guest]

Unfortunately the arrays vary any size for different times the subs are run.
So I have them as PUBLIC open array initially and then REDIM them for the
correct size just before populating them in the first sub.
I have tried to make this sub a function instead to pass the array with its
"new" info back to the initial sub and then pass this to the second sub where
the next series of calculations take place.
Unfortunately, it still reverts back to an empty array as it returns to the
initial sub before even being passed to the second sub.

Is there a way of stating the array in the sub name so that it passes the
array back with the new data?

 

[Guest]

I did try and declare the array as a public variable prior to any subs but
this requires them to have either a fixed size or to be totally variable, ie
myArray(5) or myArray().
So I set them initially as follows:
PUBLIC myArray() as integer
and then in the main sub redefined them as follows:
REDIM myArray(Var_Size) as integer

This array then populates fine in the first sub, within the main sub, but as
soon as the first sub is completed and you return to the main sub the array
empties.
Which means an empty array is carried into the second sub, within the main
sub.

Any other suggestions on how to pass the populated array back to the main sub?

Thanks for the initial suggestion.

 

[Guest]

I think that solves my problem.
The subs I was calling were in seperate modules.
I should just group them in the same module.

Will let you know.
Thanks Bob and Gary

 

[Guest]

Although your problem seems solved now, you might be interested to read
Knowledgebase article ID 843144 for background info


Jan Bart

 

[Myrna Larson]

I don't understand why you are having problems. If you pass the array as an
argument, any changes that are made in the called Sub are passed back to the
calling procedure, including size and content of the array. The following code
displays the expected results, regardless of where Sub1 and Sub2 are located.
Than can be in different module(s) from Main.

'~~~~~~~~~~~~~~~~~~~~~~~~
'1st set of routines uses an array stored in a variant and Array statement
Option Explicit

Sub Main()
Dim Ary As Variant

Ary = Array(1, 2, 3)
ShowContents Ary

Sub1 Ary
ShowContents Ary

Sub2 Ary
ShowContents Ary
End Sub

Sub Sub1(A As Variant)
A = Array("A", "B", "C", "D")
End Sub

Sub Sub2(A As Variant)
A = Array(10, "A", 20, "B", 30, "C")
End Sub

Sub ShowContents(A As Variant)
Dim i As Long
For i = LBound(A) To UBound(A)
Debug.Print A(i),
Next i
Debug.Print
End Sub

Option Explicit

'~~~~~~~~~~~~~~~~~~~~~~~~
'2nd set of routines uses a "normal" array

Sub Main()
Dim i As Long
Dim Ary() As Variant

ReDim Ary(1 To 3)
For i = 1 To 3
Ary(i) = i
Next i

ShowContents Ary()
Sub1 Ary()
ShowContents Ary()
Sub2 Ary()
ShowContents Ary()
End Sub

Sub Sub1(A() As Variant)
Dim i As Long
ReDim A(1 To 4)
For i = 1 To 4
A(i) = Chr$(64 + i)
Next i
End Sub

Sub Sub2(A() As Variant)
Dim i As Long
Dim j As Long
ReDim A(1 To 6)
j = 1
For i = 1 To 6
If i Mod 2 = 1 Then
A(i) = j * 10
Else
A(i) = Chr$(64 + j)
j = j + 1
End If
Next i
End Sub

Sub ShowContents(A() As Variant)
Dim i As Long
For i = LBound(A) To UBound(A)
Debug.Print A(i),
Next i
Debug.Print
End Sub