Customizing Subtotal

[Paleo]

When we are working with advanced filters in excel, we can have subtotal which use some pre-defined functions.

Now, what if I want to have subtotal to use an UDF? Is it possible? How could I define a number for my UDF?

I mean something like:



.Subtotal GroupBy:=5, Function:=myFunction, TotalList:=Array(5) _

, Replace:=True,
PageBreaks:=False, SummaryBelowData:=False

[Paleo]

Anybody to at least confirm, it's not possible?

[Ken Puls]

Hiya Carlos,

From what I can see in the object browser, in Excel 2010 it supports xlAverage, xlCount, xlCountNums, xlMax, xlMin, xlProduct, xlStDev, xlStDevP, xlSum, xlVar and xlVarP. What's really interesting though is that is also supports xlUnknown...

The only way I can see to get your function in there would be to make yours part of the XlConsolidationFunction Enum. I'm not quite sure how you'd go about doing that though... I know we can create our own Enums, but can we add to an exisiting one?

[Bob Phillips]

My guess would be that you have to roll your own subtotalling routine, including identifying start and end points and the line insert. I doubt you would be able to tap your UDF into the builtin subtotal.

[Paleo]

Well guys, as it was supposed to be Excel 2007 I believe I better follow another path, than to try to use my own UDF there... Thanks for the replies.