Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Over 29 args in a UDF with double parens
I noticed that Excel will accept any number of arguments for a worksheet
function if the parens are doubled, and that all of the arguments work with Excel's SUM and AVERAGE functions (I haven't tried any other functions). For example, this doesn't work because it exceeds the maximum number of arguments: =AVERAGE(A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A1 3,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25, A26,A27,A28,A29,A30,A31) But this works fine, and the average is correct for all 50 arguments. Note the doubled parens: =AVERAGE((A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A 13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25 ,A26,A27,A28,A29,A30,A31,A32,A33,A34,A35,A36,A37,A 38,A39,A40,A41,A42,A43,A44,A45,A46,A47,A48,A49,A50 )) And this works too: =SUM((A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A 14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25,A26 ,A27,A28,A29,A30,A31,A32,A33,A34,A35,A36,A37,A38,A 39,A40,A41,A42,A43,A44,A45,A46,A47,A48,A49,A50)) (BTW, I'm using Excel 2003 and haven't tried this with other versions.) I tried to get that to work in a UDF, and found a way to get access to all the values. But, then I realized I seemed to have access to ALL of the values in the worksheet -- even those which weren't passed to the function in an argument -- which seems very strange. Here's what I did: First, the UDF uses ParamArray: Public Function MyFunction(ParamArray arglist() As Variant) Here's how I called it from the worksheet: =MyFunction((A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A1 2,A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24, A25,A26,A27,A28,A29,A30,A31,A32,A33,A34,A35,A36,A3 7,A38,A39,A40,A41,A42,A43,A44,A45,A46,A47,A48,A49, A50)) This gives me access to the values in A1:A50: arglist(0)(1) ..... arglist(0)(50) So far, so good. BUT, this gives me access to the value in A51, which wasn't passed to the function: arglist(0)(51) And, this gives me access to the value in cell J10, which also was not passed to the function: arglist(0)(10,10) So, I seem to have access to all the values in the worksheet, just by passing A1. But as weird as that seems, it wouldn't be a problem if I could somehow determine what cell references were passed. But I haven't been able to find a way. I've tried setting the paramarray to an array declared in the UDF, but that didn't help. Any suggestions? Many Thanks, Greg P.S. -- Is it well known that you can pass any number of arguments to Excel's SUM and AVERAGE functions (and others for all I know), and still get a correct result, just by doubling the parens? I'd never heard of that before I stumbled into it looking for a way to get more than 29 arguments into a UDF. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Over 29 args in a UDF with double parens
using the extra parenthesis will pass all references as an array to the first argument of the function. in your example you are passing an array of ranges thus arglist(0) = Range("a1") - a1.cells(10,10) will get you J10 a1(51) will probably get you A51 else you could test against passing multiareas (double parens) Function HowMany(ParamArray pa()) If TypeName(pa(0)) = "Range" Then If pa(0).Areas.Count 1 Then HowMany = "NO MULTIAREAS" End If End If End Function If you write a UDF 'to the max': declare 28 (variant) arguments + 1 paramarray at the end.. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Greg Lovern wrote : I tried to get that to work in a UDF, and found a way to get access to all the values. But, then I realized I seemed to have access to ALL of the values in the worksheet -- even those which weren't passed to the function in an argument -- which seems very strange. Here's what I did: First, the UDF uses ParamArray: Public Function MyFunction(ParamArray arglist() As Variant) Here's how I called it from the worksheet: =MyFunction((A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A1 2,A13,A14,A15,A16,A1 7,A18,A19,A20,A21,A22,A23,A24,A25,A26,A27,A28,A29, A30,A31,A32,A33,A34, A35,A36,A37,A38,A39,A40,A41,A42,A43,A44,A45,A46,A4 7,A48,A49,A50)) This gives me access to the values in A1:A50: arglist(0)(1) .... arglist(0)(50) So far, so good. BUT, this gives me access to the value in A51, which wasn't passed to the function: arglist(0)(51) And, this gives me access to the value in cell J10, which also was not passed to the function: arglist(0)(10,10) So, I seem to have access to all the values in the worksheet, just by passing A1. But as weird as that seems, it wouldn't be a problem if I could somehow determine what cell references were passed. But I haven't been able to find a way. I've tried setting the paramarray to an array declared in the UDF, but that didn't help. Any suggestions? Many Thanks, Greg P.S. -- Is it well known that you can pass any number of arguments to Excel's SUM and AVERAGE functions (and others for all I know), and still get a correct result, just by doubling the parens? I'd never heard of that before I stumbled into it looking for a way to get more than 29 arguments into a UDF. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace parens | Excel Discussion (Misc queries) | |||
double axis, double problem (i hope only to me) | Charts and Charting in Excel | |||
How to ignore parens in a sort? | Excel Discussion (Misc queries) | |||
End Double Sub | Excel Programming | |||
End Double Sub | Excel Programming |