PASSING an array to a sub in VBA

  • Thread starter Thread starter Wombat
  • Start date Start date
W

Wombat

I want to pass an array to a subroutine: I tried some
variants such as
Call Output(K()) or
Call Output(K(*)) (used in some forms of HP BASIC),
but could not find one that worked. Any ideas anyone?

This is what I want to do
For t = 1 To NT 'put data in array
For w = 1 To NW
K(t, w) = t * w
Next w
Next t
Call Output_dat(Sheet1, K()) 'pass sheetname & array to sub
'this syntax is not accepted by VBA
End Sub
'
Sub Output_data(Sheet_name, K_temp())
'this sub would allow various versions of K to be output
to various sheets
For t = 1 To NT
For w = 1 To NW
Sheet_name.Cells(1 + w, 1 + t) = K_temp(t, w)
Next w
Next t
End Sub
 
Try using specific variable types in your array declaration and
the called function definition. For example, the following code
works in Excel 97 and Excel 2003.

Sub AAA()
Dim Arr(1 To 10) As Long
Dim Ndx As Long
For Ndx = 1 To 10
Arr(Ndx) = Ndx * 10
Next Ndx
BBB Arr()
End Sub

Sub BBB(X() As Long)
Dim Ndx As Long
For Ndx = LBound(X) To UBound(X)
Debug.Print X(Ndx)
Next Ndx
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Try using specific variable types in your array declaration and
the called function definition. For example, the following code
works in Excel 97 and Excel 2003.

Sub AAA()
Dim Arr(1 To 10) As Long
Dim Ndx As Long
For Ndx = 1 To 10
Arr(Ndx) = Ndx * 10
Next Ndx
BBB Arr()
Click to expand...

I don't know about 97 or 2003, but in Excel 2000 the parentheses aren't
necessary - you can do it like this:
BBB Arr
and it works.
 
Back
Top