Limitations of arguments passed to function

[dan16390]

Thank you, Govert, for such a wonderful tool. I have been using it
now for about six months with no problems.

Unfortunately, I now have a rather complex function that has over 20
arguments to pass which, after compiling, the UDF in excel shows a
subset of the 20+ functions I included. Actually, the cut-off seems
somewhat arbitrary, and I suspect it has to do with the overall string
length of how these arguments are passed to ExcelDNA?

I am using the ExcelFunction to Describe and Name the arguments. Is
there an overall limitation to the length of the string that contains
the function's arguments and ExcelFunction modifiers, or the number of
arguments, that can be passed, or is there something else I am missing
that is limiting the complete set of arguments to be recognized in the
UDF in excel? It appears when I shorten up the Description and Name
modifiers, the UDF in excel contains more arguments.

Unfortunately, I am currently well short of the arguments I need. Is
there a way around this limitation? Or might I be inadvertently
creating this limitation? If anybody has run into a similar
circumstance and can assist me in correcting, or a work around, I
would appreciate.

Dan

[Govert]

Hi Dan,

The limitations in Excel pre-2007 are as follows:
The maximum number of arguments is 255.
The maximum length for the total string of argument names (together
with the two character ', ' separators) is 255 characters.
The maximum length for an argument description is 255 characters, and
only the first 20 arguments can have descriptions.

If your function exceeds these limits, you should still be able to
call it with the extra arguments, they are just invisible in the
function wizard.

For the next version of ExcelDna I have removed the space in the
argument separator, so you'll get a few more characters there.

For Excel 2007 all these strings can be long and Unicode. A future
version of ExcelDna will support the conversion to the extended Excel
2007 data types, and should thus remove this restriction under Excel
2007 without any change to your add-in libraries. However, I have not
tested whether the function wizard in Excel 2007 works right with
longer strings.

Let us know if this agrees with your experience.

Regards,
Govert

[Dan Keidel]

Hi Govert,

My apologies for a late reply. I am using Excel 2007. This info is
very helpful and my experience tracks with your specs. I am not having
trouble entering functions with arguments beyond 20 - without
descriptions. The functions are rather complex and I am using Excel
2007. In the upcoming weeks I will be investigating migrating the
functions to a macro style.

My development pc is running XP, while my laptop is running Vista. I
tried numerous ways to get exceldna working properly on Excel
2007/Vista, and finally built an installer program and that seemed
worked. I need to investigate more why it works. Like other postings,
I am not quite sure why it worked.

Thank you,
Dan

[Govert]

Hi Dan,

To be clear - the argument name limitations are the same under Excel
2007 with the current version of ExcelDna as in earlier versions.
However, a future version of ExcelDna will support the Excel 2007 data
types, and with that the restriction on string lengths will disappear
under Excel 2007.

What problems did you experience under Vista? I have had some reports
of problems, but my own brief testing raised no issues. Have you tried
under Vista with ExcelDna version 0.15? The new loader (from version
0.14) removed the C runtime rependency - maybe this was the problem.
What was the error message you got in Excel?

Regards,
Govert

> >> Dan- Hide quoted text -
>
> - Show quoted text -

[Terry Aney]

Any update on supporting 'Excel 2007 data types' to eliminate the restrictions on the parameter length?

Thanks,

Terry

[Govert van Drimmelen]

Yes - Excel-DNA was updated several years ago to allow full support of the Excel 2007 types. In particular, long Unicode strings are supported in most places.

Note that some limitations do remain in Excel, for example the total length of argument names, the string length of array formulas, and a few other places. Any limitations you see with current Excel-DNA versions in this regard are inherent Excel limitations.

To help get rid of the remaining 255 character limitations in future Excel versions, it might help to add a post to this topic on the MSDN forum: https://social.msdn.microsoft.com/Forums/office/en-US/54d259bd-a2ae-4118-87b0-a59035e6ef75/please-remove-remaining-255-character-api-limitations?forum=exceldev

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Olexandr Baturin]

Hello Govert,

The limitations for UDF still exists ?  I mean, the maximum length for the total string of argument names and what is length limitation for total length of the UDF ? 
I'm asking because in some cases I can pass an udf with length almost 410 characters  and in other program is crashing if length is grater than 355 characters.

With my best regards,

Olexandr.

[Govert van Drimmelen]

Hi Olexandr,

I'm don't understand what you mean by "total length of the UDF".

The total length of the argument names used for the function wizard (including separators) is 255 characters.

Excel-DNA will truncate the names if longer, but that won't affect calling your function.

There might be a similar limitation in the length of the function name.

There are some restrictions in other contexts:

* Formula length if you are using the array resizer (expecially if you have many references, since it is expanded to R1C1 format).

* RTD string lengths if you are implementing your own RTD server and not using the Excel-DNA wrapper.

* and probably some other places ...

How are you "passing a udf"?

Can you explain how to recreate the crash?

(Do you mean Excel is crashing or your program is failing?)

Anyway - these restrictions are not Excel-DNA ones, but in Excel itself.

Please add your comments to this post on the Microsoft forum 

https://social.msdn.microsoft.com/Forums/office/en-US/54d259bd-a2ae-4118-87b0-a59035e6ef75/please-remove-remaining-255-character-api-limitations?forum=exceldev

and the UserVoice suggestion: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10208379-remove-the-255-character-limit-in-excel-object-mod 

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Olexandr Baturin [olexandr...@tradingeconomics.com]
Sent: 07 April 2017 05:48 PM
To: Excel-DNA
Subject: Re: [ExcelDna] Re: Limitations of arguments passed to function

--

You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.

Visit this group at https://groups.google.com/group/exceldna.

[Chris Rusby]

Is this still a limitation in the latest Excel versions eg Excel 2016?

Is there any difference if you don't provide pxTypeText in the first xlfRegister call, and instead call xlfRegister from within the subsequent xlAutoRegister12 callback?

Thanks

Chris 

[AE]

I have a UDF which takes a query string as a parameter (e.g. =GetData(A1), where A1 contains the query string). The queries can be very long and Excel cell has a limit of 32,767 characters. If the query contains 40K characters, it cannot go into a single cell. I tried splitting it in two by 20K each into cells A1 and A2. But if I pass the query as A1&A2 into my UDF (=GetData(A1&A2)), the function will only get the first 32,767 characters. Is there a workaround for this limit? Thanks.

[Kedar Kulkarni]

My suggestion would be to use  =GetData(A1:A2) and collect the individual values to concatenate inside the function than making excel send the values. You could also split the query having where clause, sub queries inside different cell and append as you go. eg GetData("A1:A10")? 

the specifications and limits are by design and wont change for excel https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 

[AE]

This could actually work. Thanks!