1

I have an xll (a rather complex c++ project) exporting a function which, for historical reasons, takes a large number of arguments, and precisely 20.

This is a magic number: apparently in Excel 2003 there is a maximum number of 30 arguments (" In Microsoft Office Excel 2003, the maximum number of arguments that any function can take is 30, although most take fewer than this. "), but over 20 the function cannot be registered in the function wizard.

Now, as it happens and as you could guess, I have been required to add 3 more arguments. Ok, so the argument count goes up to 23 (at least, this function is not intended for "human consumption" but is always called by a VBA wrapper).

By attaching to the Excel process with VC++ debugger, I get at registration time an error code 4, which stands for xlretInvCount.

I have made sure that the string of comma separated argument names is shorter than 255 characters. Btw, I am using xlw 4 (old version, I know).

So, if the limit is 30 I expect to be able to invoke my function via 

Application.Run("function name", ..... very long list of arguments)

but not to use the wizard. Trouble is that VBA tells me that the function is not registered.

So, how do I properly use a function which takes more than 20 arguments and less than 30?

NB: please refrain from stating the obvious. I know where the real crux of the problem is. A refactoring is not possible at the moment.

Francesco
  • 3,200
  • 1
  • 34
  • 46
  • 1
    I am not sure if you consider this as a refactoring, but often you can reduce the number of arguments, by passing a range (which contains the arguments) as a single argument. Evil, dirty alternative: pass arguments as string and parse the string - especially since you call it via a VBA wrapper. – Christian Fries Oct 11 '13 at 19:11
  • This is a fallback that I was considering, but I wonder why I apparently cannot overcome what should only be a "non binding" limit. – Francesco Oct 11 '13 at 20:36

1 Answers1

1

Since you call your function via VBA you might call it directly, not even registering it, i.e., NOT USING XLW.

  • Declare in C/C++: extern “C” int __declspec(dllexport) _stdcall myfunc(double arg1, double* arg2, ...)
  • Declare in VBA: Function MyFunc& Lib "C:\myL.dll" Alias "_myfunc@1" (ByVal arg1 As Double, ByRef arg2 As Double, ...)

The @1 refers to the position in of the function in the .def file.

Now you can call the function form VBA. See http://aandreasen.wordpress.com/2008/05/05/how-to-create-a-dll-for-ms-excel-vba-with-microsoft-visual-c-2008-command-line-tools/

Christian Fries
  • 16,175
  • 10
  • 56
  • 67
  • Hey that's great. I will try it asap. The function api expects XlfOper and unwraps them to native types. I hope this doesn't force me to only use it via application. Run. Will read your link right now, thanks for the pointer. – Francesco Oct 12 '13 at 08:37
  • I could try this only today, and it works. Unfortunately the xll was writteng accepting arguments of type XlfOper which is an XLW wrapper around LPXLOPER. This poses the interesting question of how to declare this function (your example you are using native data types which is a bit easier). Maybe I will ask another question on that. For the time being, thanks for providing the correct input. – Francesco Oct 23 '13 at 11:14
  • 1
    I would declare the function using native C arguments, then create the XlfOper arguments in it. Alternatively (better): Create the general function using native C arguments and call that function from the XLW function by unwrapping the XlfOper. I hope that the referencing of the XlfOper is not too deep in your old function. – Christian Fries Oct 23 '13 at 12:36
  • yes, that's possible but it requires a kind of work that I was hoping to avoid. Actually, I had in mind to rewrite the wrapper, but using Python and not Excel ;-) I have asked the new question at this link: http://stackoverflow.com/questions/19540594/how-do-i-declare-a-function-in-vba-which-accepts-arguments-of-type-xlfoper-lpxl?lq=1 – Francesco Oct 23 '13 at 13:10