PDA

View Full Version : Passing multiple arrays between sub



Djblois
05-19-2007, 07:56 AM
I am starting to get good with passing variables, and now I am passing arrays between sub. However now I am trying to reduce the size of my code by a few hundred lines by passing multiple arrays, if it is possible. Here is the code I am trying to change with comments on where I am having trouble. I am trying to pass multiple arrays to this sub procedure and I can't figure out how:


Sub RedundantCode(ByVal stgProdNoTotal As String, ByVal stgItemNoTotal As String, _
ParamArray aRowNoCode() As Variant)

'I use this same exact if statement about 20 times in my code,
'but the arrays would be different so I want to create this code to accept

If PivotTableOptions.No Then
' I would rather pass this as an array
'ex: PT.AddFields RowFields:=Array(aRowNoCode), AddToTable:=True
PT.AddFields RowFields:=Array("Whse", "Product", "SlsPrsn")

ElseIf PivotTableOptions.SortByProduct Then
'Also as an array but a dirrerent Array
PT.AddFields RowFields:=Array("Whse", "Product", "Item#", "SlsPrsn")

ptNoTotal stgProdNoTotal 'This would be set up as a variable

ElseIf PivotTableOptions.SortByItem Then
'A third Array
PT.AddFields RowFields:=Array("Whse", "Item#", "Product", "SlsPrsn")

ptNoTotal stgItemNoTotal 'This would be set up as a different variable
End If
End Sub


Edited 21-May-07 by geekgirlau. Reason: insert line breaks

Bob Phillips
05-19-2007, 10:52 AM
Sub RedundantCode(ByVal stgProdNoTotal As String, ByVal stgItemNoTotal As String, _
ParamArray aRowNoCode() As Variant)

Call LessRedundantCode(stgProdNoTotal, _
stgItemNoTotal, _
Array("Whse", "Product", "SlsPrsn"), _
Array("Whse", "Product", "Item#", "SlsPrsn"), _
Array("Whse", "Item#", "Product", "SlsPrsn"))
End Sub
Sub LessRedundantCode(ByVal stgProdNoTotal As String, _
ByVal stgItemNoTotal As String, _
ByVal ary1 As Variant, _
ByVal ary2 As Variant, _
ByVal ary2 As Variant)

If PivotTableOptions.No Then
PT.AddFields RowFields:=ary1
ElseIf PivotTableOptions.SortByProduct Then
PT.AddFields RowFields:=ary2
ElseIf PivotTableOptions.SortByItem Then
PT.AddFields RowFields:=ary3
End If
End Sub


Edited 21-May-07 by geekgirlau. Reason: insert line breaks

Paul_Hossler
05-19-2007, 11:25 AM
Q: Why pass ByVal instead of ByRef? My undestanding is that ByVal pushs a copy of the entire variable onto the stack before jumping to the Sub, whereas ByRef only pushs the address of the variable onto the stack? If the structures are large, I'd think that takes (relatively) a lot more time

Paul

Djblois
05-19-2007, 11:42 AM
I am new, I want to learn when I should use byref and when I should use byval?

Bob Phillips
05-19-2007, 11:55 AM
There are probably more opinions on this subject than we have time to cover, but this is my understanding.

Passing a String is more efficient ByRef because ByVal results in a deep copy being made.

For an object it makes little difference because a deep copy is never made,both results in only a 4 byte, Long, pointer being passed.

For numbers, it makes little difference due to the data sizes in question.

But there is another factor, data integrity. A variable passed ByRef, if changed in the called procedure, is reflected back in the caller procedure, ByVal is not. Its a matter of the application you are writing whether you use ByVal or ByRef, but personally I tend to use ByVal, even if it takes longer and use more
memory (probably marginal), but it complies better with object orientated concepts of encapsulation.

Paul_Hossler
05-19-2007, 01:45 PM
xld -- also my understanding. From Excel VBA help

by value -- A way of passing the value of an argument to a procedure instead of passing the address. This allows the procedure to access a copy of the variable. As a result, the variable's actual value can't be changed by the procedure to which it is passed.

by reference -- A way of passing the address of an argument to a procedure instead of passing the value. This allows the procedure to access the actual variable. As a result, the variable's actual value can be changed by the procedure to which it is passed. Unless otherwise specified, arguments are passed by reference.

I was asking because I wan't sure about passing a Variant containing an array, and how much data had to be passed (the usual 22 bytes for a Variant data type) or the the whole thing:dunno

Paul