Showing argument descriptions as an XLL worksheet function is type

McDowell@discussions.microsoft.com John McDowell

unread,

Mar 26, 2010, 9:40:06 PM3/26/10

to

We're developing a set of XLL worksheet functions that have 5-8 difficult to
remember arguments.

While the argument descriptions passed to the registration function display
properly in the function wizard, they do not appear in the common case of
typing the function in the formula bar.

In addition, I have these related (but lesser) concerns:
- My worksheet function description does not appear when my function
name is being typed in the Formula Bar.
- My add-in description text is not displayed in the Add-in Wizard.

Unfortunately, the sample "GENERIC" provided in the SDK seems to have the
same issues.

I do not believe these are a general limitation of the XLL SDK, as the
Analysis Toolpak XLL and its functions do not have these issues. For
example, add the Toolpak Add-In, observe the description and type "MROUND" in
the formula bar.

Can someone take a look at how to get argument descriptions to work in the
formula bar? I haven't been able to locate this knowledge in Steve Dalton's
book or on the existing internet resources.

Thanks very much,
John McDowell
[Former Windows Program Manager]

Govert van Drimmelen

unread,

Mar 27, 2010, 12:36:34 PM3/27/10

to

Hi John,

The limitations you see are indeed general limitations of add-ins -
user-defined functions are treated differently to the built-in
functions and it is not possible to hook into the function
intellisense or have your .xll add-in register a Description for the
Add-In Wizard. Since Excel 2007, the Analysis Toolpak is no longer an
add-in as before - these functions are now built-in functions, which
is why you see MROUND working so nicely.

Regards,
Govert van Drimmelen
http://exceldna.codeplex.com
Free and easy .NET for Excel.

On Mar 27, 3:40 am, John McDowell <John

John McDowell

unread,

Apr 11, 2010, 2:24:01 AM4/11/10

to

Thanks very much, Govert.

Given the significant value of inline argument help, I hope the Excel
programmability team strongly considers adding this functionality for Office
15.

It's a notable barrier to professional XLL / add-in development.

Cheers,
John

"Govert van Drimmelen" wrote:

> .
>

Govert van Drimmelen

unread,

Apr 12, 2010, 4:42:02 AM4/12/10

to

Hi John,

This seems to be unchanged in the Excel 2010 Beta, so I doubt it would
be considered for the upcoming version.

But you might like to add it as a Feature Request for Excel 15 on
Microsoft Connect: http://connect.microsoft.com/.

There is quite a number of longstanding, well-known limitations and
bugs in the XLL interface. But Excel 2007 and Excel 2010 have finally
introduced significant advances in the XLL interface, so maybe there
is hope for further attention and improvement in future.

Govert van Drimmelen

unread,

May 2, 2010, 4:26:13 AM5/2/10

to

Hi,

I must correct some of what I said - in Excel 2010 there is still an
Analysis ToolPak .xll (ANALYS32.XLL). The description that is
displayed for the add-in in the Add-ins dialog seems to come from a
registry entry under \HKCR\Installer\Components....

I guess the description is set in the Excel installer package.

Regards,
Govert

http://exceldna.codeplex.com
Free and easy .NET for Excel.

> Govert van Drimmelenhttp://exceldna.codeplex.com

lab27

unread,

May 3, 2010, 8:00:40 AM5/3/10

to

On May 2, 9:26 am, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi,
>
> I must correct some of what I said - in Excel 2010 there is still an
> Analysis ToolPak .xll (ANALYS32.XLL). The description that is
> displayed for the add-in in the Add-ins dialog seems to come from a
> registry entry under \HKCR\Installer\Components....
>
> I guess the description is set in the Excel installer package.
>

There is still an xll, but as you said originally, the /function mode/
functions have been moved into the space of excel intrinsics (eg
mround) - they no longer come from analsy32.xll.

You can see this in a couple of ways

* there's no spurious name registered for worksheet functions - i.e.
'=mround' is #name. (So you can be pretty confident it's not being
registered via the C api in some underhanded way).
* analys32.xll isn't loaded into memory when just working with the
worksheet functions.

If you want to go the whole hog, you can write something to intercept
all calls to Excel4/Excel4V/Excel12/Excel12V, to see what's being done
via the C api - I knocked something up to do this out of interest -
the output is at http://www.benf.org/excel/addinwatch/analsy32_register.log
(it's about 70k, so I'll not inline it ;) )

What you see is that all the macro mode stuff is still getting
registered this way - (eg 1) and some dialog stuff.

It's an interesting aside that mround (as a random example) is still
an exported symbol from the version of analsy32.xll that comes with
office 2007 - guess it's for backwards compatibility.....

Rgds,

Lee.

eg 1)

00000402 0.10672654 [3628] -------
00000403 0.10677650 [3628] Someone called Excel12/Excel12v!
00000404 0.10680916 [3628] func of type : xlfRegister
00000405 0.10680916 [3628]
00000406 0.10685104 [3628] xlOper of type : xltypeStr
00000407 0.10685104 [3628] C:\Program Files (x86)\Microsoft Office
\Office12\Library\Analysis\ANALYS32.XLL
00000408 0.10688639 [3628] xlOper of type : xltypeStr
00000409 0.10688639 [3628] fnMCovar
00000410 0.10692137 [3628] xlOper of type : xltypeStr
00000411 0.10692137 [3628] AUUQQ#
00000412 0.10695557 [3628] xlOper of type : xltypeStr
00000413 0.10695557 [3628] MCOVAR
00000414 0.10699245 [3628] xlOper of type : xltypeStr
00000415 0.10699245 [3628] inprng,outrng,grouped,labels
00000416 0.10705817 [3628] xlOper of type : xltypeNum
00000417 0.10705817 [3628] 2
00000418 0.10709851 [3628] xlOper of type : xltypeStr
00000419 0.10709851 [3628] Statistical
00000420 0.10712849 [3628] xlOper of type : xltypeNil
00000421 0.10716423 [3628] xlOper of type : xltypeStr
00000422 0.10716423 [3628] XLMACRO.CHM!3475
00000423 0.10720534 [3628] xlOper of type : xltypeStr
00000424 0.10720534 [3628] Returns a covariance matrix that measures
the covariance between two or more data sets
00000425 0.10723531 [3628] xlOper of type : xltypeNil
00000426 0.10726414 [3628] xlOper of type : xltypeNil
00000427 0.10729257 [3628] xlOper of type : xltypeNil
00000428 0.10732139 [3628] xlOper of type : xltypeNil
00000429 0.10737941 [3628] xlOper of type : xltypeNil
00000430 0.10741477 [3628] xlOper of type : xltypeNil
00000431 0.10744359 [3628] xlOper of type : xltypeNil
00000432 0.10747202 [3628] xlOper of type : xltypeNil
00000433 0.10750046 [3628] xlOper of type : xltypeNil
00000434 0.10752121 [3628] -------