Problem calling Excel DNA function from VBA with very long string (> 32767 chars)

May 30, 2013 at 4:00 PM
I have a function that takes an array of strings:

C# code:
    [ExcelFunction("Update", Category = BaseApi.ExcelFunctionCategory)]
    public static object MAUpdate([ExcelArgument(Name = "attributes", Description = "The list of attributes to set")] object[] attributes,
            [ExcelArgument(Name = "values", Description = "The list of values of the attributes")] object[] values)
I am trying to call this from VBA like this:
dim attrs() as string
dim values() as string
result = MAUpdate(attrs, values)

this works fine except when one of the fields in the values array exceeds a certain size - it looks to be 32767.

The string limit in both VBA and C# is 2^31 (> 2 billion) so it looks like there is a problem inside Excel DNA.

I wrote a function in VBA taking the same params and that does not have any problem.
Also, if I put a breakpoint in the C# function on the first line of code, it never hits it.

The error reported is:
1004: Application-defined or object-defined error

any help greatly appreciated.
Coordinator
May 30, 2013 at 4:46 PM
Hi Jonathan,

This is an Excel limit - see http://office.microsoft.com/en-za/excel-help/excel-specifications-and-limits-HP005199291.aspx. It would not apply to function inside VBA calling other VBA code directly, but when you are calling a worksheet function (via Application.Run, I presume) the call is subject to the limitations of Excel worksheet functions.

You could move your VBA code into the Excel-DNA add-in.

Regards,
Govert
May 30, 2013 at 4:49 PM
Many thanks for the quick and informative reply.

Unfortunately, moving the code inside the Add-In isn't viable.

So I will probably have to split the string up into separate elements of the array and concatenate inside the C#.
May 31, 2013 at 5:38 PM
Not sure what your scenario is, but passing such a long argument suggests refactoring the functionality into a component or module that processes a file so the data's length or format is not an issue.

So instead, have the parameter refer to a file path or URI that can be loaded from within the function. It also makes it easier to invoke since you can decouple the function's invocation from the data changing in the file. This can make your VBA references easier to maintain. Just a thought. ~BT
Jun 4, 2013 at 12:01 PM
the VBA code is building a string from form elements (including a list which can be large) and using Excel DNA to talk client sever to a web service (passing the string).
The obvious fix would be to implement the form in C# but that isn't possible for non technical reasons.

So I will either have to split the string or allow some way of passing it in chunks.
Thanks for your help.