Return big strings

[screig]

I suppose this is more of a feature request.

But it would be useful to be able to return large strings to VBA. Now note whilst there is a limit on the number of characters that a cell can contain:

Total number of characters that a cell can contain | 32,767 characters

from here

In VBA you can actually define a string up to 2GB in length here

So it would be nice if you could have a UDF that could return these bigger (>32k) string to VBA. I'm using Excel 2016.

Maybe I need to write each character to an array?

Ps I've said it before, but will keep on saying it, incredibly grateful for this fantastic project.

[govert]

You might try the COM Server approach for integrating your add-in with VBA code - that should not have these UDF limitations. See https://github.com/Excel-DNA/Samples/tree/master/DnaComServer and https://github.com/Excel-DNA/Samples/tree/master/ComServerVB

[screig]

Thanks for the suggestion, but the COM server approach would change the dynamics of the VBA side quite a bit? I guess one would not want to create an object like this, each time you called in to the function?

Set dnaComServer = CreateObject("DnaComServer.ComLibrary")

Would it be expensive? So I THINK! one would need to maintain this in state and share it, I'm not sure?

Anyway the other approach of converting to an array is not the worse thing in the world and it does work. Here I am passing a JSON string of 80k characters from C# to VBA and it can consume it. The VBA call also then join it back in to a single string.

     public static object[,] String_To_Array(string SomeString)
        {
            List<string> SomeResult = SomeString.ToCharArray().Select(c => c.ToString()).ToList();
            int imax = SomeResult.Count;
            int jmax = 1;
            object[,] out_array = new object[imax, jmax];
            for (int i = 0; i < imax; i++)
            {
                out_array[i, 0] = SomeResult[i];
            }
            return out_array;
       }

One just needs to write code on both sides of the interface , VBA and C#. Maybe in the ExcelDNA marshalling of the object from C# to VBA, if it detected that the C# string length is over 32k it should do something like the above automatically or give the user a choice?

It would be a nice feature if I could just write in my C# UDF return <the very long string> and Excel DNA handled it or at least gave me the ability to overwrite the marshalling to handle it?

[govert]

There are definitely different reasonable ways of putting together an add-in and interacting with it. For the COM approach, which to me seems more natural from VBA, you need not create a new object for every call - just one for the whole session. But then your array plans are fine too, and would give you a way to see the chopped-up strings in a workbook too.

To be a bit more precise, while you mention "the Excel-DNA marshalling of the object from C# to VBA", it never happens this way for a UDF. Excel-DNA is marshaling to a structure defined by the Excel API which has the string limitation, and everything from there to VBA is Excel only. So the UDF interface is limited by Excel. Putting in place a convention where long strings are returned as strings arrays would be something you can do in your add-in easily, but does not seem like the right feature generally.

[keithalewis]

Another possibility is xlDefineBinaryName but then you would need to write functions using xlGetBinaryName to extract what you need.