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
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