Registering parameter/function help for Excel User Defined Functio

[Jack Hoxley [MVP]]

Morning all,

Just a quick post to request some best-practice advice as to what my options
might be. I've been digging around MSDN for a while and haven't found any
promising leads yet.

In a nutshell I want some VBA (or C#) code for Excel 2003 where I can store
help text that appears in the function helper window - e.g. function
description, category and parameter name/description.

I found the following two articles:
http://www.dailydoseofexcel.com/archives/2006/02/17/registering-a-user-defined-function-with-excel/
http://www.bettersolutions.com/excel/EIK284/YN723710331.htm

But they cause strange errors whereby our UDF's aren't always called. I did
some digging, and found http://msdn.microsoft.com/en-us/library/bb687900.aspx
which left me confused as to how the aforementioned code is ever supposed to
work. It strikes me that the oft repeated fragments online are nothing but
fugly hacks. Who's smart idea was it to try and pull out User32.dll calls and
re-alias them as other VBA functions?! A true "WTF".

Does anyone know of any other approaches to exposing intellisense-style
information to the end-users of our Excel Addin? Can VSTO somehow do it
directly from within the .NET/C# code maybe?

Any suggestions or advice are appreciated,
Jack

[Peter T]

The accolade for your "WTF" method, as you describe, belongs to Laurent
Longre, see here
http://xcell05.free.fr/english/index.html
The method is limited to 255 characters of help text in total for the
description and all the arguments.

KeepItCool discovered a way to overcome the 255 limit. It is based on
Laurent's method but with clever use of names, see demo here
http://www.jkp-ads.com/Articles/RegisterUDF00.htm
Unless or until you fully understand the demo use it as-is, but find
alternative API function names to those used in the demo. Later you may find
the code can be usefully adapted to your needs, but If you get something
slightly wrong you may crash Excel. If you are using automation addin
functions, as became available for use in XL2002, the intrinsic method can
also be used with the code in your own app, though function names will
appear twice in the function wizard.

If you have your own .Net/C# "ordinary" dll you might just as well include
some dummy functions for use with the above methods (instead of say calling
User32.dll).

You can to link to a chm help file without either of the above. Also you can
include 2-3 lines (depending on XL version) of help text to appear in the
function wizard (but not text for each argument). The help link and text can
be added by selecting the function in Object browser, rt-click, properties.
With simple functions the "description" text alone might be enough without
resorting to the methods above. Have a go with that first.

AFAIK there are no other approaches to include help text in the function
wizard.

Regards,
Peter T

"Jack Hoxley [MVP]" <JackHo...@discussions.microsoft.com> wrote in
message news:979A0501-2CAB-4D13...@microsoft.com...

[Jack Hoxley [MVP]]

Thanks for the quick reply Peter.

> The accolade for your "WTF" method, as you describe, belongs to Laurent
> Longre, see here
> http://xcell05.free.fr/english/index.html
> The method is limited to 255 characters of help text in total for the
> description and all the arguments.

Yes, I'm familiar with that in my travels (just didn't have the URL to hand
when I posted my original message). Don't get me wrong, I don't wish to knock
Laurent personally for this code - but the technique just seems like a
horrific hack which sets off my alarm bells for integrating this into a
solution used by 1000's of people. I like to have confidence that arbitrary
code from the internet has some sane technical grounding :-)

Firstly, the xlfRegister function (aka REGISTER) seems geared towards
aliasing a native DLL's public functions for use in Excel. That is, a rather
neat way of exposing advanced functionality in a clean Excel manner.
Therefore it seems strange to me that it's being used to expose existing VBA
Macros that don't come from an external and otherwise unusable DLL.

Secondly, the articles online talk about picking system DLL's (for which the
DLL may exist in different places, so you need platform specific "where is
the Windows folder" code) and overriding system functions. You also mention
"you may crash Excel" which is one distinct outcome I can see for
redefining/overriding otherwise standard API functions. Ok, so maybe it
doesn't actually redefine the "host" API, but based on the MSDN documentation
it definitely seems like "undefined behaviour" which, as before, doesn't give
me confidence in this for an enterprise solution.

> If you have your own .Net/C# "ordinary" dll you might just as well include
> some dummy functions for use with the above methods (instead of say calling
> User32.dll).

I will concede that this avoids the second point, so may well be acceptable.
Our code is currently a COM DLL written in C#, so I'll have to investigate
further.

> If you are using automation addin
> functions, as became available for use in XL2002, the intrinsic method can
> also be used with the code in your own app, though function names will
> appear twice in the function wizard.

I'm only bothered about Excel 2003 and later for now, so this sounds
interesting. I've seen a couple of articles about exposing C# COM methods
directly to Excel, but they didn't do anything about adding descriptions or
parameter information - would you know of an example of doing this, or what
keywords/APIs are involved?

> You can to link to a chm help file without either of the above. Also you can
> include 2-3 lines (depending on XL version) of help text to appear in the
> function wizard (but not text for each argument). The help link and text can
> be added by selecting the function in Object browser, rt-click, properties.
> With simple functions the "description" text alone might be enough without
> resorting to the methods above. Have a go with that first.

I'll have a look into this, thanks for the suggestion.

> AFAIK there are no other approaches to include help text in the function
> wizard.

That's "good" to hear - obviously I'd like a nice clean solution, but it's
also good to know I haven't missed anything crucial when reading around.

Thanks again for your help,
Jack

[Peter T]

> Firstly, the xlfRegister function (aka REGISTER) seems geared towards
> aliasing a native DLL's public functions for use in Excel.

not sure I quite follow but I assume the old Register function was
originally geared towards XLL's

> You also mention
> "you may crash Excel" which is one distinct outcome I can see for
> redefining/overriding otherwise standard API functions.

No, redefining/overriding otherwise standard API functions is not the
problem, just need to get the code right. In the testing I've done
KeepItCool's method appears stable and reliable (the first published beta
wasn't!). AFAIK Laurant's method is completely reliable, albeit with the
255 restriction.

> Secondly, the articles online talk about picking system DLL's (for which
> the
> DLL may exist in different places, so you need platform specific "where is
> the Windows folder" code) and overriding system functions.

System dll's will be found in the system folder without having to specify
the path. If you make your own dll, it would need to be in a system folder,
otherwise would need to fully qualify the path (normally could put in your
app path or temporarily ChDir to your path but I don't think that would work
for this purpose)

> Our code is currently a COM DLL written in C#, so I'll have to
> investigate further.

I'm not sure about a C# COM dll. If you can "Declare" your functions in
VB/VBA then I guess it might work (I mean for same purpose as those User32
functions). However I assume all the code to register the functions with the
help strings could all be contained in your COM DLL.

Regards,
Peter T

"Jack Hoxley [MVP]" <JackHo...@discussions.microsoft.com> wrote in

message news:4A93243E-8291-4334...@microsoft.com...

[PBezucha]

Hi all,

In his great comment, Peter mentioned but shortly the method of chm help. As
I take it, this had been intended as a Help standard, by Microsoft. I wonder
then, why users avoid using this way, for at least no discussion in this
direction appeared on NGs for a long time. All people use to return to the
genial, but strained methods of LL, JKP, and KIC (restricted number of
serviced functions in an open file!).
I mean, we could surely do without the arguments description to a UDF,
provided there would be a simple accessible, detailed help for the function
as a whole. The thought is of a simple document without excessive hyperlinks
and other programmers’ finesses, so that the cooperation with professional
Help creators would be not necessary.

I myself have tried sometimes to find out helps to Help on web, including
official Microsoft pages, but so far fallen behind, because of not imminent
necessity and of some beginner failures when following scanty instructions.
So far I have failed also to get an older book of Jeannine Klein on HTML
building, where one can hope to be tutored in a more instructive way. It is
my problem.

But generally taken: can anybody give here a learned opinion to the
question, which is, as we see, returning frequently? Does it pay to tackle
the problem, for not too engrossed a man, from this side?

Thanks

--
Petr Bezucha

[Peter T]

How to create a chm help file is probably beyond the scope of this ng. There
are various chm compilers, including MS's free HTML Help Workshop available
here
http://msdn.microsoft.com/en-us/library/ms669985.aspx

To create a chm and how deploy it in VBA with the HTML API is all quite a
learning curve, and perhaps explains why not many go to the effort of
including a chm with an xla/s. However plenty of on-line resources and
tutorials can be found with a quick search.

Regards,
Peter T

"PBezucha" <PBez...@discussions.microsoft.com> wrote in message
news:03D37DE1-59E0-4874...@microsoft.com...