Introducing variables into Selection.Subtotal

[Cervantes]

Hi All;
I'm setting up code so that users can pick what column(s) they want subtotaled in their reports (Excel 2000). The best way I found to do it was to use the Selection.Subtotal function in VB, and feed their selections into the TotalList:= argument with a variable.

What I have so far in the code takes the columns they specify, converts them to numeric, 1-base offset like the function Selection.Subtotal wants.

[tt]Selection.SubTotal GroupBy:=intGroupBy, Function:=xlSum, TotalList:=Array(varTotalList), replace:=False, PageBreaks:=False, SummaryBelowData:=True[/tt]

Where intGroupBy is an integer, and varTotalList is a Variant

My problem is that when varTotalList is a single number (24, for example), the code runs fine, but when varTotalList = "24,25", for example, I get "Run-time error 1004, Subtotal method of Range class failed". I've tried changing the variable type, changing how it goes in, format, everything, and I am stuck. This wouldn't be so horrid, excep this is one of those "Have it done tomorrow or don't bother coming in Wednesday" kind of things. Arg!

I've confirmed that the only problem is the varTotalList. intGroupby works fine, and the code is set up to build varTotalList with the proper ", " between numbers.

Does anyone know how to get the TotalList argument to accept a variable with more than one number, or have an alternate suggestion to make flexible subtotals?

I hope this all made sense.

 

[ETID]

have you tried

= "24","25

 

[Cervantes]

If you mean have I tried putting the values in, instead of the variable, yes, I have, and it works fine.

If you mean assigning the variable, as in

varTotalList = "24", "25"

that just don't work.

Right now, the value of varTotalList comes out as "22, 23, 24, 25". When I put that value in the TotalList argument, it works fine, but when I put a string variable containing that exact value as the argument, it produces the error I noted above.

If it helps, the argument I'm trying to use a variable in is defined by the unHelp file as:

[tt]
TotalList Required Variant. An array of 1-based field offsets, indicating the fields to which the subtotals are added. For more information, see the example.
[/tt]

The exact code string that I'm using is:

Selection.SubTotal GroupBy:=intGroupBy, Function:=xlSum, TotalList:=Array(varTotalList), replace:=False, PageBreaks:=False, SummaryBelowData:=True

 

[ETID]

are you converting real values to string?...

if so you might need to strip out the leading dead character (reserved for negative numbers)


I.E.

cstr(25) is actually " 25"...so you need


varTotalList = trim(cstr(24)) & "," & trim(cstr(25))

 

[Cervantes]

Good point on that, but unfortunately not applicable (but filed away for future reference)

Here's the code snippet:

Dim SubTotalBy1 as String
Dim varTotalList as Variant

SubTotalBy1 = "WXYZ"

For Looper = 0 To Len(SubTotalBy1) - 1
    If Looper > 0 Then varTotalList = varTotalList & ", "
    ColNum = Asc(Left(Right(SubTotalBy1, Len(SubTotalBy1) - Looper), 1)) - 64
    varTotalList = varTotalList & ColNum
Next Looper

The above takes the string, loops through it one letter at a time, and uses the ASCII code to determine the numeric column offset. The above sets varTotalList to "22, 23, 24, 25" (according to a mouse-float on code break), which are the valyes needed by the TotalList argument of Subtotal. The idea is that [tt]TotalList:=Array(varTotalList)[/tt] should work the same as [tt]TotalList:=Array(22, 23, 24, 25)[/tt]

 

[ETID]

could the space after the comma be the problem changing ", " to "," ?

 

[ETID]

Anoth observation... going into the loop varTotalList = nothing, then the first thing the loop does is add nothing to nothing and then adds a coma.. resulting in the first character varTotalList of "," ...I don'e be;ieve you want to start the list with a comma.

add msgbox(varTotalList) or debug.print varTotalList to confirm...

For Looper = 0 To Len(SubTotalBy1) - 1
If Looper > 0 Then varTotalList = varTotalList & ", "
msgbox(varTotalList)
ColNum = Asc(Left(Right(SubTotalBy1, Len(SubTotalBy1) - Looper), 1)) - 64
varTotalList = varTotalList & ColNum
Next Looper

 

[ETID]

Ooops sorry about fumbling the keys there....

 

[Cervantes]

No prob bout the key fumbling

Avoiding the string starting with ", " is the reason there's the [tt]If Looper > 0 Then varTotalList = varTotalList & ", "[/tt] line in there. That only adds the ", " to the string if it's not the first pass through the loop. This also covers my cheeks for when there is only 1 field to subtotal by.

I did msgbox, just to be sure, and it is indeed behaving. Also, the space had no effect.

 

[ETID]

Or are you missing else and endif statements in your example?

 

[Cervantes]

Well, the example isn't the entire code, but no, I don't believe I am, because I can go to the Selection.Subtotal line, delete the variable name, put in 'real numbers', and it runs just fine.... even though the loop is still processing to make the variable string... so the problem isn't in the loop, or the statement, it's just "What type of variable does it have to be for it to work in the Selection.Subtotal statement?

 

[ETID]

maybe this?


IsArray (varTotalList)

 

[Cervantes]

IsArray is just the boolean check to see if the variable is an array though.

However, it did get me thinking, and I tried making it a variant array instead of a string array. The code now looks like this:

SubTotalBy = UCase(SubTotalBy)
ReDim varTotalList(Len(SubTotalBy))
For Looper = 0 To Len(SubTotalBy) - 1
    ColNum = Trim(CStr(Asc(Left(Right(SubTotalBy, Len(SubTotalBy) - Looper), 1)) - 64))
    varTotalList(Looper + 1) = ColNum
Next Looper

and, with [tt]TotalList:=varTotalList[/tt] as the argument, it works. Shocked the hell outta me. I don't quite know what I did different from the first time I tried arrays, but it works fine now

Thanks for all yer help!

 

[ETID]

I figured that was going down the wrong path, but I was clutching at straws...

Glad to see you got it to work.

 

[PHV]

My guess to reply the original post is to replace this:
TotalList:=Array(varTotalList),
By this:
TotalList:=Eval("Array(" & varTotalList & ")"),
I don't quite know what I did different from the first time I tried arrays
Don't be confused with variable name versus variable value, compile time evaluation versus runtime evaluation, string variable versus literal constant, ... and so on.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Cervantes]

Hey PHV
Good note on "Eval"... didn't consider that one. As for why it didn't work the first time, I probably forgot a ( or something equally stupid... don't know bout ya'll, but I can be a pretty stupid coder when the boss of the boss of my boss is hanging on my every action.

Thanks all for the help!