Pass an array

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

How do I pass an array to a sub routine?

Dim HeaderArray(1 to 6) as long

HeaderArray(1) = 43
HeaderArray(2) = 90
HeaderArray(3) = 138
HeaderArray(4) = 186
HeaderArray(5) = 234
HeaderArray(6) = 282

I tried passing it as HeaderArray but got strange results.

Call NextRoutine(HeaderArray)
 
Karen,

How have you declared your NextRoutine procedure? You need to declare the
input paramater as an array of the exact same data type as the array being
passed, or declare it as a Variant (not as an array of variants). For
example,

Sub CalledProc(Arr() As Long)
Dim N As Long
For N = LBound(Arr) To UBound(Arr)
Debug.Print Arr(N)
Next N
End Sub

You can call this with code like:

Sub AAA()
Dim Arr(1 To 3) As Long
Dim L As Long

Arr(1) = 11
Arr(2) = 22
Arr(3) = 33

CalledProc Arr
End Sub

Since CalledProc expects an array of Longs, you'll get an error if you try
to pass an array of any other data type to it. The alternative is to declare
the input parameter as a Variant. For example,

Sub CalledProc(Arr As Variant)
Dim N As Long
If IsArray(Arr) = True Then
For N = LBound(Arr) To UBound(Arr)
Debug.Print Arr(N)
Next N
Else
Debug.Print "not an array"
End If
End Sub

This can accept an array of any data type and data that is not an array. It
tests the input parameter with IsArray to see if it was passed an array,.

See www.cpearson.com/Excel/PassingAndReturningArrays.htm for more
information and example code about passing arrays to and from procedures.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Can you show us the NextRoutine's declaration statement please?

Rick
 
Thank You, Chip!
--
Thanks for your help.
Karen53


Chip Pearson said:
Karen,

How have you declared your NextRoutine procedure? You need to declare the
input paramater as an array of the exact same data type as the array being
passed, or declare it as a Variant (not as an array of variants). For
example,

Sub CalledProc(Arr() As Long)
Dim N As Long
For N = LBound(Arr) To UBound(Arr)
Debug.Print Arr(N)
Next N
End Sub

You can call this with code like:

Sub AAA()
Dim Arr(1 To 3) As Long
Dim L As Long

Arr(1) = 11
Arr(2) = 22
Arr(3) = 33

CalledProc Arr
End Sub

Since CalledProc expects an array of Longs, you'll get an error if you try
to pass an array of any other data type to it. The alternative is to declare
the input parameter as a Variant. For example,

Sub CalledProc(Arr As Variant)
Dim N As Long
If IsArray(Arr) = True Then
For N = LBound(Arr) To UBound(Arr)
Debug.Print Arr(N)
Next N
Else
Debug.Print "not an array"
End If
End Sub

This can accept an array of any data type and data that is not an array. It
tests the input parameter with IsArray to see if it was passed an array,.

See www.cpearson.com/Excel/PassingAndReturningArrays.htm for more
information and example code about passing arrays to and from procedures.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
Click to expand...
 
Back
Top