maximum number of parameters to a function?

[mrbud1972]

Hi, is there a maximum number of parameters or character length that can be applied to a function (Specifically within excel 2003).
It would appear its around 255 characters but I cant find any confirmation if this is correct. Surely it must be more than this. Looking at a KB article on Microsoft it suggests this is more like 1024.
Any help greatly appreciated
Thanks
Martin.
(Just to confirm. An example is

 

[xlbo]

Would've been useful to link to your previous question in the MSOffice forum as most of what I would've suggested is in there...could still do with seeing the code tho....as was requested in that thread

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[mrbud1972]

Thanks for your quick response. Sorry I got distracted and posted it before I intended.
Right : the example : Function Testfunction (Param1, Param2, Param 3, etc)

Everything looks fine. When executed though when a particular point is reached (which I think maybe 255 characters) nothing is returned. No error messages either. As soon as the number of characters are reduced, it works as it should again. Sorry the KB article thinking about it wasnt a kb article but from help within excel. Cant find it again at the moment. It was something to do with calculating.

 

[mrbud1972]

Ok, this is the function which DOES work :

Function Test(fourteen1414_1 As String, fourteen1414_2 As String, fourteen1414_3 As String, fourteen1414_4 As String, _
fourteen1414_5 As String, fourteen1414_6 As String, fourteen1414_7 As String, fourteen1414_8 As String, _
fourteen1414_9 As String, fourteen1414_10 As String, fourteen1414_11 As String, fourteen1414_12 As String, _
fourteen1414_13 As String, fourteen1414_14 As String, fourteen1414_15 As String, fourteen1414_16 As String) As String

Test = fourteen1414_1 + fourteen1414_2 + fourteen1414_3 + fourteen1414_4 + fourteen1414_5 + fourteen1414_6 + fourteen1414_7 + fourteen1414_8 + fourteen1414_9 + _
fourteen1414_10 + fourteen1414_11 + fourteen1414_12 + fourteen1414_13 + fourteen1414_14 + fourteen1414_15 + fourteen1414_16

Now, if all the arguments are changed from "fourteen1414_xx" to "fifteen151515_xx" (adding a total of only 16 characters to the existing 423ish) it stops working!

Any futher help and clarification greatly appreciated,
Martin

 

[xlbo]

Copied your test function

Worked

Changed to the fifteen151515 - still worked.....

Changed to fifteen1515151515 - 581 characters - still worked

I am using XP but to be honest, limits generally only go upwards in MS development.

also added another 2 arguments (to take it to 18) - still works

By Sherlock Holmes type logic, this leaves only a code error rather than a limit in arguments as cause...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[mrbud1972]

Ok, big thanks for that. Could you try adding another 18 characters as by changing them to fifteen will reduce the number by 16. It fails for me with that. Thanks. It must be documented somewhere sure.
Thanks again
Martin.

 

[combo]

Martin,
just follow Geoff's advice, do the effort and test the code again. Try Option Explicit, shorten variable names - they are very long and and hard to follow, test the visibility of variables, etc. Of course there are code size limitations, but this is not this case. Within the same workbook, this work:

Function Test(fifteen151515_1 As String, fifteen151515_2 As String, fifteen151515_3 As String, fifteen151515_4 As String, _
              fifteen151515_5 As String, fifteen151515_6 As String, fifteen151515_7 As String, fifteen151515_8 As String, _
              fifteen151515_9 As String, fifteen151515_10 As String, fifteen151515_11 As String, fifteen151515_12 As String, _
              fifteen151515_13 As String, fifteen151515_14 As String, fifteen151515_15 As String, fifteen151515_16 As String, _
              fifteen151515_17 As String, fifteen151515_18 As String, fifteen151515_19 As String, fifteen151515_20 As String, _
              fifteen151515_21 As String, fifteen151515_22 As String, fifteen151515_23 As String, fifteen151515_24 As String, _
              Optional fifteen151515_171 As String, Optional fifteen151515_181 As String, Optional fifteen151515_191 As String, Optional fifteen151515_201 As String, _
              Optional fifteen151515_172 As String, Optional fifteen151515_182 As String, Optional fifteen151515_192 As String, Optional fifteen151515_202 As String, _
              Optional fifteen151515_173 As String, Optional fifteen151515_183 As String, Optional fifteen151515_193 As String, Optional fifteen151515_203 As String, _
              Optional fifteen151515_174 As String, Optional fifteen151515_184 As String, Optional fifteen151515_194 As String, Optional fifteen151515_204 As String, _
              Optional fifteen151515_175 As String, Optional fifteen151515_185 As String, Optional fifteen151515_195 As String, Optional fifteen151515_205 As String, _
              Optional fifteen151515_176 As String, Optional fifteen151515_186 As String, Optional fifteen151515_196 As String, Optional fifteen151515_206 As String, _
              Optional fifteen151515_177 As String, Optional fifteen151515_187 As String, Optional fifteen151515_197 As String, Optional fifteen151515_207 As String, _
              Optional fifteen151515_178 As String, Optional fifteen151515_188 As String, Optional fifteen151515_198 As String, Optional fifteen151515_208 As String) As String
               
 Test = fifteen151515_1 + fifteen151515_2 + fifteen151515_3 + fifteen151515_4 + fifteen151515_5 + fifteen151515_6 + fifteen151515_7 + fifteen151515_8 + fifteen151515_9 + _
           fifteen151515_10 + fifteen151515_11 + fifteen151515_12 + fifteen151515_13 + fifteen151515_14 + fifteen151515_15 + fifteen151515_16 + _
           fifteen151515_17 + fifteen151515_18 + fifteen151515_19 + fifteen151515_20 + fifteen151515_21 + fifteen151515_22 + fifteen151515_23 + fifteen151515_24
End Function

Total 2097 chars in function definition. Using in spreadsheet: =test(A2;A2;A2;A2;A2;A2;A2;A2;A2;A2;A2;A2;A2;A2;A2;A2;A2;A2;A2;A2;A2;A2;A2;A2), where A2 is a string of 50 chars, the output is of 2000 chars. All works.

combo

 

[pbrodsky]

This looks like a difficult way to accomplish whatever you are trying to achieve.

Have you considered passing an array to the function rather than separate arguements, or, possibly, referring to a list of values stored somewhere?

 

[xlbo]

Right - I think there IS a limit but it is in arguments not LENGTH of arguments - I think that the limit in XP is 29 arguments. the following works:

Function test(teststr1 As String, teststr2 As String, teststr3 As String, teststr4 As String, _
teststr5 As String, teststr6 As String, teststr7 As String, teststr8 As String, _
teststr9 As String, teststr10 As String, teststr11 As String, teststr12 As String, _
teststr13 As String, teststr14 As String, teststr15 As String, teststr16 As String, _
teststr17 As String, teststr18 As String, teststr19 As String, teststr20 As String, _
teststr21 As String, teststr22 As String, teststr23 As String, teststr24 As String, _
teststr25 As String, teststr26 As String, teststr27 As String, teststr28 As String, _
teststr29)

test = teststr1 & teststr2 & teststr3 & teststr4 & _
teststr5 & teststr6 & teststr7 & teststr8 & _
teststr9 & teststr10 & teststr11 & teststr12 & _
teststr13 & teststr14 & teststr15 & teststr16 & _
teststr17 & teststr18 & teststr19 & teststr20 & _
teststr21 & teststr22 & teststr23 & teststr24 & _
teststr25 & teststr26 & teststr27 & teststr28 & _
teststr29
End Function

but this doesn't:

Function test(teststr1 As String, teststr2 As String, teststr3 As String, teststr4 As String, _
teststr5 As String, teststr6 As String, teststr7 As String, teststr8 As String, _
teststr9 As String, teststr10 As String, teststr11 As String, teststr12 As String, _
teststr13 As String, teststr14 As String, teststr15 As String, teststr16 As String, _
teststr17 As String, teststr18 As String, teststr19 As String, teststr20 As String, _
teststr21 As String, teststr22 As String, teststr23 As String, teststr24 As String, _
teststr25 As String, teststr26 As String, teststr27 As String, teststr28 As String, _
teststr29, [b]teststr30 As String[/b])

test = teststr1 & teststr2 & teststr3 & teststr4 & _
teststr5 & teststr6 & teststr7 & teststr8 & _
teststr9 & teststr10 & teststr11 & teststr12 & _
teststr13 & teststr14 & teststr15 & teststr16 & _
teststr17 & teststr18 & teststr19 & teststr20 & _
teststr21 & teststr22 & teststr23 & teststr24 & _
teststr25 & teststr26 & teststr27 & teststr28 & _
teststr29 [b]& teststr30[/b]
End Function

Doesn't seem to matter what the length of the string itself is. I substituted the variable names for some token gibberish (loooong token gibberish) and got a 842 character length string which worked no probs.

So - you either have 30+ arguments or there is another issue involved...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question