Passing array from 2nd sub back to first sub

[ExcelMonkey]

I have a sub which calls a second sub. The second sub
loads an array. I want to pass the entire array back up
to the first sub when the second sub is completed. How do
I this assuming that I do not want to make the array a
public array?

 

[Bernie Deitrick]

ExcelMonkey,

1) Dimension the Array as Private within the module containing the two subs

or

2) Pas the Array ByRef to the second sub

Sub FirstSub()
Dim i As Integer
Dim myArray(1 To 10) As String

'Pass the array to the second sub
SecondSub myArray

For i = 1 To 10
MsgBox myArray(i)
Next i
End Sub

Sub SecondSub(ByRef myArray2 As Variant)
Dim i As Integer
'Modify the array
For i = 1 To 10
myArray2(i) = "Test " & i
Next i
End Sub

HTH,
Bernie
MS Excel MVP

 

[ExcelMonkey]

Thanks Bernie

-----Original Message-----
ExcelMonkey,

1) Dimension the Array as Private within the module containing the two subs

or

2) Pas the Array ByRef to the second sub

Sub FirstSub()
Dim i As Integer
Dim myArray(1 To 10) As String

'Pass the array to the second sub
SecondSub myArray

For i = 1 To 10
MsgBox myArray(i)
Next i
End Sub

Sub SecondSub(ByRef myArray2 As Variant)
Dim i As Integer
'Modify the array
For i = 1 To 10
myArray2(i) = "Test " & i
Next i
End Sub

HTH,
Bernie
MS Excel MVP




.