ParamArray - limited by 29 arguments

[psoukup]

Hi guys,
I created UDF using paramArray expecting I can use as many params I want (the function will be used under Excel 2003/7/10). However once I exceed 29 params I get an error message
"More arguments have been specified for this function that are allowed in the current file format"

is there any workaround, is number of args in the ParamArray really limited?
here is my funtion (shorten)

Function dwhBAL(ParamArray Params() As Variant) As Variant


Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordse
With cnn
.ConnectionString = conString
.CursorLocation = adUseClient
.Open
End With

For i = LBound(Params) To UBound(Params)

Select Case UCase(Params(i))
Case "ACCOUNT", "ACC": thisPodminka

.......

rst.Open dwhBAL, cnn, adOpenStatic, 3 'adLockReadOnly
varData = rst.GetRows()


ReDim varDataT(1 To UBound(varData, 2) + 1, 1 To UBound(varData, 1) + 1)
dwhBAL = Nz(mySum + varData(0, 0))

End function


thank you

 

[shg]

Welcome to the board.

It's apparently a limitation of Excel 2003; it works fine for a larger number of arguments in later versions.

 

[Norie]

What are you actually passing to the function?

Have you considered actually passing an array?

That would mean you were only passing one argument, and the length of the array would only be restricted by memory.

 

[psoukup]

Welcome to the board.

It's apparently a limitation of Excel 2003; it works fine for a larger number of arguments in later versions.

no, it is not a feature of E2003 the same happens in E2010

 

[shg]

the same happens in E2010

Not for me:

Function x(ParamArray av() As Variant)
  x = WorksheetFunction.Sum(av)
End Function

Row\Col	A​	B​	C​
1​	1​	820​	B1: =x(A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25,A26,A27,A28,A29,A30,A31,A32,A33,A34,A35,A36,A37,A38,A39,A40)
2​	2​
3​	3​
4​	4​
5​	5​
6​	6​
7​	7​
8​	8​
9​	9​
10​	10​
11​	11​
12​	12​
13​	13​
14​	14​
15​	15​
16​	16​
17​	17​
18​	18​
19​	19​
20​	20​
21​	21​
22​	22​
23​	23​
24​	24​
25​	25​
26​	26​
27​	27​
28​	28​
29​	29​
30​	30​
31​	31​
32​	32​
33​	33​
34​	34​
35​	35​
36​	36​
37​	37​
38​	38​
39​	39​
40​	40​

You have it opened in compatibility mode?

 

[psoukup]

Norie, I think you are right I use the function and refer either to a string or sheet address like
dwhBAL("ACC", A1,A2, "CC","OPEX","OPEX"), the same as Excel function SUM() works
this is quite comfortable for the users as they can make their own templates and build in this function in it.
Frankly I do not know how to pass to the function the argument as an array in Excel. It would have to be one long strig?
dwhBAL("'ACC',A1,A2,'CC','OPEX','OPEX'") and split then the arguments using SPLIT function ???

 

[Jonmo1]

Even if your users are using XL2007+, it could still be the limitation of XL2003 to 29 param arguments.
If the book is in compatibility mode.

What does this do?

Sub test()
MsgBox ActiveWorkbook.CheckCompatibility
End Sub

 

[psoukup]

I do not use compatibility mode, and your function works fine on my Excel.
Actually I use the same techniques in my function, but you must do it somehow better
pavel

 

[psoukup]

it says False, so not using the compatibility mode

 

[psoukup]

IT WORKS NOW IN E2010, though I did not make any change, thx for all your replies.

is there any workaround for E2003? Not critical to me, but wonder if it is managable in earlier versions?