General discussion

Locked

VB/VBA Subtotal Method solution needed

By jhassani ·
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.

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

VB/VBA Subtotal Method solution needed

by wjlarue In reply to VB/VBA Subtotal Method so ...

Upon initial evaluation of your code, I say you need to use the Split Function to create your array. The Array Function accepts an argument list of comma-separated values. It?s assuming your creating a one-element array. I?ll provide a simple example:

Dim aArrayFunc as Variant
Dim aSplitFunc as Variant
Dim strMyString as String

StrMyString = ?1,2,3,4,5,6,7,8,9?

aArrayFunc = Array(StrMyString)
aSplitFunc = Split(StrMyString,?,?)


Immediate Window Results:
? aArrayFunc(0)
1,2,3,4,5,6,7,8,9

? aSplitFunc(0)
1

? aSplitFunc(1)
2

? aSplitFunc(2)
3


Hope this solves your problem.

Jeff LaRue
Raging Technologies

Collapse -

VB/VBA Subtotal Method solution needed

by jhassani In reply to VB/VBA Subtotal Method so ...

I ended up with the TotalList portion of the code looking like:
TotalList:=Array(Split(rs!EffCols, ";", -1, vbTextCompare))
Thanks so much!

Collapse -

VB/VBA Subtotal Method solution needed

by jhassani In reply to VB/VBA Subtotal Method so ...

This question was closed by the author

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums