Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PASSING an array to a sub in VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
PASSING an array to a sub in VBA
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 "Wombat" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
PASSING an array to a sub in VBA
On Sun, 30 Nov 2003 05:22:33 -0600, Chip Pearson wrote:
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() 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. 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 -- auric "underscore" "underscore" "at" hotmail "dot" com ***** The Toe that can be stubbed is not the true Toe. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing string as array variable (Pivot VBA) | Excel Discussion (Misc queries) | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Passing a row to a function | Excel Worksheet Functions | |||
Passing array to a function | Excel Programming | |||
Passing an Array of User-Defined Type to an Argument of a Function | Excel Programming |