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]

Don't think so, but to Norie's point, I can't get my brain around a function that needs more than 29 discrete arguments.

 

[psoukup]

hardly to imagine, I agree.
My function queries MSSQL tables from the financial system. To make it flexible user can specify name for the fields he wants to use in the condition, each condition uses 3 arguments FieldName, LowLimit, TopLimit
so 30 arguments quickly shrinks to 10 real parameters and then it it is not so difficult to run out of the range
Example:
currency from to, period from To, actuality from to , account from to, CC from to, customer from to, etc.
and you can imagine users are very creative

anyway thank you to all of you for quick responses
rgds Pavel

 

[Jonmo1]

Instead of having the users hard code those strings directly into your function.
Have them put them into a range of cells.
Then reference that range in the function.
And you can loop that range using something like

Public Function x(MyRange As Range)
For Each c In MyRange

 

[Kyle123]

Why not just get them to put that into a table with three headers, Field, LowLimit, TopLimit - they can then have as many rows as they want

Jonmo beat me to it!

 

[shg]

Or if they get collected hither, thither, and yon from the worksheet, at least use arrays of three items each.