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/arch...on-with-excel/
http://www.bettersolutions.com/excel...N723710331.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

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]" wrote in
message ...
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/arch...on-with-excel/
http://www.bettersolutions.com/excel...N723710331.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

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

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]" wrote in
message ...
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

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" wrote:

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]" wrote in
message ...
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

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" wrote in message
...
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" wrote: