I am having difficulties passing variables to the Excel VBA/Automation Subtotal method from VB. All works well until I try to pass the TotalList argument value. If I hard code the values in, it works fine however when I try to pass the value as a variable, it fails. My example:
Dim vCols as Variant
vCols = rs!ColsEffected
With oXLApp.Worksheets(“Sheet1”)
‘–following works but is hardcoded – not an option:
.Range(“A1”).CurrentRegion.Subtotal _ GroupBy:=21, Function:=xlSum, _
TotalList:=Array(7,8,9,10,11,12,13,14,15,
16, 17,18,19), Replace:=True, _ PageBreaks:=False, _ SummaryBelowData:=xlSummaryBelow
‘–following fails:
.Range(“A1”).CurrentRegion.Subtotal _ GroupBy:=21, Function:=xlSum, _
TotalList:=Array(vCols), _
Replace:=True, PageBreaks:=False, _ SummaryBelowData:=xlSummaryBelow
End With
I have tried everything I can think of with this and would greatly appreciate a fresh set of eyes looking at this.
I tried assigning Array(vCols) to a variable and passing it without success as well.