Worksheet function with variable number of parameters

Hi,

I'm trying to write a sub to create a worksheetfunction that would be called with an array.
It could be called with ["B","D","P"] or ["F","H","Q",T"] etc. The goal would be to have a formula like so =SUM(B3,D3,P3) in R3 or =SUM(F3,H3,Q3,T3) in V3. If easier I could also call the sub with [2,4,16] and use the R1C1 notation style. I've seen some potential solutions with ParamArray.

Please Login or Register  to view this content.

Any ideas ?

Thanks,
amphi

Thanks. Calling the function but I'm getting a type mismatch error on the MySum line but if I set e to string then I can't loop the array...

Please Login or Register  to view this content.

Your Totals is an Error value, hense the type mismatch

One technique for using a ParmaArray is to test the first argument of that array. If that element is an array, use just that element, if not then use the whole ParamArray

Please Login or Register  to view this content.

You would call it in one of these manners.

Please Login or Register  to view this content.

Note that the array Totals does not need to be 0 based.

Thanks both for the reply. The final goal of this function would be to have a formula showing in the worksheet using application.worksheetfunction so when the code runs the formula is still "dynamic".

So something like :

Please Login or Register  to view this content.

Would this be possible ?