Any guidance as to how to have a UDF in an addin be declared/registered etc.
so as to be used like all other usual/normal functions? like be autocompleted
upon partial entry, arguments be displayed in the supertip and be capable to
be used in the Conditional Formatting and other features?

--
Thanx in advance & Best Regards,

Faraz!

Read this:

http://www.jkp-ads.com/articles/RegisterUDF00.asp

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Faraz Ahmed Qureshi" <(E-Mail Removed)> wrote in
message news:B5447321-57FD-46EC-B9AA-(E-Mail Removed)...
> Any guidance as to how to have a UDF in an addin be declared/registered
> etc.
> so as to be used like all other usual/normal functions? like be
> autocompleted
> upon partial entry, arguments be displayed in the supertip and be capable
> to
> be used in the Conditional Formatting and other features?
>
> --
> Thanx in advance & Best Regards,
>
> Faraz!

It's not straight forward at all, but it can be done (not tooltips though)

http://www.jkp-ads.com/articles/RegisterUDF01.asp

Regards,
Peter T

"Faraz Ahmed Qureshi" <(E-Mail Removed)> wrote in
message news:B5447321-57FD-46EC-B9AA-(E-Mail Removed)...
> Any guidance as to how to have a UDF in an addin be declared/registered
> etc.
> so as to be used like all other usual/normal functions? like be
> autocompleted
> upon partial entry, arguments be displayed in the supertip and be capable
> to
> be used in the Conditional Formatting and other features?
>
> --
> Thanx in advance & Best Regards,
>
> Faraz!

I've been playing with this recently as well, using the FunCustomize
example on Laurent Longre's website, http://xcell05.free.fr/english/index.html

I would be interested to know the relative merits of the two
approaches (I realise both are a variation on the same idea).
Laurent's method provides a dll and the only vb code there is is to
install the dll and pass a range with the UDF properties to it (and
uninstall when the add-in closes). It looks comparatively simple. I
presume somehow the dll then registers dummy functions using that
data. One thing I like about this approach is that you don't have to
overwrite functionality of any existing dll functions. The help file
mentions the 255 character limit but this must only apply to each
individual description as I'm sure the total string length for each of
my functions exceeds 255 characters. I guess it means an extra file
to distribute which could be a disadvantage but the functions I'm
working with all require additional data files anyway.

Cheers,
Andrew



On 30 Apr, 13:41, "Peter T" <peter_t@discussions> wrote:
> It's not straight forward at all, but it can be done (not tooltips though)
>
> http://www.jkp-ads.com/articles/RegisterUDF01.asp
>
> Regards,
> Peter T
>
> "Faraz Ahmed Qureshi" <FarazAhmedQure...@discussions.microsoft.com> wrote in
> messagenews:B5447321-57FD-46EC-B9AA-(E-Mail Removed)...
>
>
>
> > Any guidance as to how to have a UDF in an addin be declared/registered
> > etc.
> > so as to be used like all other usual/normal functions? like be
> > autocompleted
> > upon partial entry, arguments be displayed in the supertip and be capable
> > to
> > be used in the Conditional Formatting and other features?
>
> > --
> > Thanx in advance & Best Regards,
>
> > Faraz!- Hide quoted text -
>
> - Show quoted text -

The main advantage is overcoming the 255 limit. I'm (almost) sure you will
not have included more than a combined total of 255 for all the descriptions
in a single UDF.

As you say the method is a development of Laurent Longre's, it's an absolute
mystery to me as to how Jurgen Volkerink (KeepItCool) got it to work!

If anyone's interested the approach can also be adapted to work with UDFs in
an Automation Addin though there's no way to avoid duplicate entries in the
function wizard.

FWIW the code example can be considerably simplified, once you've got your
head round it!

Regards,
Peter T



"Andrew" <(E-Mail Removed)> wrote in message
news:5e621ee6-4e36-4190-8e12-(E-Mail Removed)...
> I've been playing with this recently as well, using the FunCustomize
> example on Laurent Longre's website,
> http://xcell05.free.fr/english/index.html
>
> I would be interested to know the relative merits of the two
> approaches (I realise both are a variation on the same idea).
> Laurent's method provides a dll and the only vb code there is is to
> install the dll and pass a range with the UDF properties to it (and
> uninstall when the add-in closes). It looks comparatively simple. I
> presume somehow the dll then registers dummy functions using that
> data. One thing I like about this approach is that you don't have to
> overwrite functionality of any existing dll functions. The help file
> mentions the 255 character limit but this must only apply to each
> individual description as I'm sure the total string length for each of
> my functions exceeds 255 characters. I guess it means an extra file
> to distribute which could be a disadvantage but the functions I'm
> working with all require additional data files anyway.
>
> Cheers,
> Andrew
>
>
>
> On 30 Apr, 13:41, "Peter T" <peter_t@discussions> wrote:
>> It's not straight forward at all, but it can be done (not tooltips
>> though)
>>
>> http://www.jkp-ads.com/articles/RegisterUDF01.asp
>>
>> Regards,
>> Peter T
>>
>> "Faraz Ahmed Qureshi" <FarazAhmedQure...@discussions.microsoft.com> wrote
>> in
>> messagenews:B5447321-57FD-46EC-B9AA-(E-Mail Removed)...
>>
>>
>>
>> > Any guidance as to how to have a UDF in an addin be declared/registered
>> > etc.
>> > so as to be used like all other usual/normal functions? like be
>> > autocompleted
>> > upon partial entry, arguments be displayed in the supertip and be
>> > capable
>> > to
>> > be used in the Conditional Formatting and other features?
>>
>> > --
>> > Thanx in advance & Best Regards,
>>
>> > Faraz!- Hide quoted text -
>>
>> - Show quoted text -
>

PS, forgot to add,

>> One thing I like about this approach is that you don't have to
>> overwrite functionality of any existing dll functions.

The functionality of the 'borrowed' functions is not overwritten, but it's
worth not using the named ones in the example in case any one else uses them
for the same purpose in the same system. That might in theory be an
advantage of Laurent's dll, assuming of course no one else's app is not
using the same dll (hmm not sure if that's a potential issue or not, I
haven't used his in so long).

Peter T

"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> The main advantage is overcoming the 255 limit. I'm (almost) sure you will
> not have included more than a combined total of 255 for all the
> descriptions in a single UDF.
>
> As you say the method is a development of Laurent Longre's, it's an
> absolute mystery to me as to how Jurgen Volkerink (KeepItCool) got it to
> work!
>
> If anyone's interested the approach can also be adapted to work with UDFs
> in an Automation Addin though there's no way to avoid duplicate entries in
> the function wizard.
>
> FWIW the code example can be considerably simplified, once you've got your
> head round it!
>
> Regards,
> Peter T
>
>
>
> "Andrew" <(E-Mail Removed)> wrote in message
> news:5e621ee6-4e36-4190-8e12-(E-Mail Removed)...
>> I've been playing with this recently as well, using the FunCustomize
>> example on Laurent Longre's website,
>> http://xcell05.free.fr/english/index.html
>>
>> I would be interested to know the relative merits of the two
>> approaches (I realise both are a variation on the same idea).
>> Laurent's method provides a dll and the only vb code there is is to
>> install the dll and pass a range with the UDF properties to it (and
>> uninstall when the add-in closes). It looks comparatively simple. I
>> presume somehow the dll then registers dummy functions using that
>> data. One thing I like about this approach is that you don't have to
>> overwrite functionality of any existing dll functions. The help file
>> mentions the 255 character limit but this must only apply to each
>> individual description as I'm sure the total string length for each of
>> my functions exceeds 255 characters. I guess it means an extra file
>> to distribute which could be a disadvantage but the functions I'm
>> working with all require additional data files anyway.
>>
>> Cheers,
>> Andrew
>>
>>
>>
>> On 30 Apr, 13:41, "Peter T" <peter_t@discussions> wrote:
>>> It's not straight forward at all, but it can be done (not tooltips
>>> though)
>>>
>>> http://www.jkp-ads.com/articles/RegisterUDF01.asp
>>>
>>> Regards,
>>> Peter T
>>>
>>> "Faraz Ahmed Qureshi" <FarazAhmedQure...@discussions.microsoft.com>
>>> wrote in
>>> messagenews:B5447321-57FD-46EC-B9AA-(E-Mail Removed)...
>>>
>>>
>>>
>>> > Any guidance as to how to have a UDF in an addin be
>>> > declared/registered
>>> > etc.
>>> > so as to be used like all other usual/normal functions? like be
>>> > autocompleted
>>> > upon partial entry, arguments be displayed in the supertip and be
>>> > capable
>>> > to
>>> > be used in the Conditional Formatting and other features?
>>>
>>> > --
>>> > Thanx in advance & Best Regards,
>>>
>>> > Faraz!- Hide quoted text -
>>>
>>> - Show quoted text -
>>
>
>

According to his help file if you need to register more than 200
functions you can create a second copy of the dll and start over again
so on that basis I assume there wouldn't be a conflict with someone
elses app.

I'll have to have another look at the number of characters but
definately the Stephen Bullen method (from the CD in the book) kicked
me out because my descriptions were too long and so far no problem
with Laurents dll in that regard.

Thanks,
Andrew

On 30 Apr, 16:30, "Peter T" <peter_t@discussions> wrote:
> PS, forgot to add,
>
> >> One thing I like about this approach is that you don't have to
> >> overwrite functionality of any existing dll functions.
>
> The functionality of the 'borrowed' functions is not overwritten, but it's
> worth not using the named ones in the example in case any one else uses them
> for the same purpose in the same system. That might in theory be an
> advantage of Laurent's dll, assuming of course no one else's app is not
> using the same dll (hmm not sure if that's a potential issue or not, I
> haven't used his in so long).
>
> Peter T
>
> "Peter T" <peter_t@discussions> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > The main advantage is overcoming the 255 limit. I'm (almost) sure you will
> > not have included more than a combined total of 255 for all the
> > descriptions in a single UDF.
>
> > As you say the method is a development of Laurent Longre's, it's an
> > absolute mystery to me as to how Jurgen Volkerink (KeepItCool) got it to
> > work!
>
> > If anyone's interested the approach can also be adapted to work with UDFs
> > in an Automation Addin though there's no way to avoid duplicate entriesin
> > the function wizard.
>
> > FWIW the code example can be considerably simplified, once you've got your
> > head round it!
>
> > Regards,
> > Peter T
>
> > "Andrew" <hooks...@hotmail.com> wrote in message
> >news:5e621ee6-4e36-4190-8e12-(E-Mail Removed)....
> >> I've been playing with this recently as well, using the FunCustomize
> >> example on Laurent Longre's website,
> >>http://xcell05.free.fr/english/index.html
>
> >> I would be interested to know the relative merits of the two
> >> approaches (I realise both are a variation on the same idea).
> >> Laurent's method provides a dll and the only vb code there is is to
> >> install the dll and pass a range with the UDF properties to it (and
> >> uninstall when the add-in closes). *It looks comparatively simple. *I
> >> presume somehow the dll then registers dummy functions using that
> >> data. *One thing I like about this approach is that you don't have to
> >> overwrite functionality of any existing dll functions. *The help file
> >> mentions the 255 character limit but this must only apply to each
> >> individual description as I'm sure the total string length for each of
> >> my functions exceeds 255 characters. *I guess it means an extra file
> >> to distribute which could be a disadvantage but the functions I'm
> >> working with all require additional data files anyway.
>
> >> Cheers,
> >> Andrew
>
> >> On 30 Apr, 13:41, "Peter T" <peter_t@discussions> wrote:
> >>> It's not straight forward at all, but it can be done (not tooltips
> >>> though)
>
> >>>http://www.jkp-ads.com/articles/RegisterUDF01.asp
>
> >>> Regards,
> >>> Peter T
>
> >>> "Faraz Ahmed Qureshi" <FarazAhmedQure...@discussions.microsoft.com>
> >>> wrote in
> >>> messagenews:B5447321-57FD-46EC-B9AA-(E-Mail Removed)...
>
> >>> > Any guidance as to how to have a UDF in an addin be
> >>> > declared/registered
> >>> > etc.
> >>> > so as to be used like all other usual/normal functions? like be
> >>> > autocompleted
> >>> > upon partial entry, arguments be displayed in the supertip and be
> >>> > capable
> >>> > to
> >>> > be used in the Conditional Formatting and other features?
>
> >>> > --
> >>> > Thanx in advance & Best Regards,
>
> >>> > Faraz!- Hide quoted text -
>
> >>> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Ah I suppose you can name the name the dll to whatever you want, and
providing nobody else uses a similarly named dll + function there's never
likely to be a problem.

I'm sure Laurant's was limited to 255 but maybe he has subsequently changed
something I'm not aware of.

Is Stephen Bullen's method not intrinsically the same as Laurant's?

Regards,
Peter T

"Andrew" <(E-Mail Removed)> wrote in message
news:4ad65926-af5c-4dfa-a96b-(E-Mail Removed)...
According to his help file if you need to register more than 200
functions you can create a second copy of the dll and start over again
so on that basis I assume there wouldn't be a conflict with someone
elses app.

I'll have to have another look at the number of characters but
definately the Stephen Bullen method (from the CD in the book) kicked
me out because my descriptions were too long and so far no problem
with Laurents dll in that regard.

Thanks,
Andrew

On 30 Apr, 16:30, "Peter T" <peter_t@discussions> wrote:
> PS, forgot to add,
>
> >> One thing I like about this approach is that you don't have to
> >> overwrite functionality of any existing dll functions.
>
> The functionality of the 'borrowed' functions is not overwritten, but it's
> worth not using the named ones in the example in case any one else uses
> them
> for the same purpose in the same system. That might in theory be an
> advantage of Laurent's dll, assuming of course no one else's app is not
> using the same dll (hmm not sure if that's a potential issue or not, I
> haven't used his in so long).
>
> Peter T
>
> "Peter T" <peter_t@discussions> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > The main advantage is overcoming the 255 limit. I'm (almost) sure you
> > will
> > not have included more than a combined total of 255 for all the
> > descriptions in a single UDF.
>
> > As you say the method is a development of Laurent Longre's, it's an
> > absolute mystery to me as to how Jurgen Volkerink (KeepItCool) got it to
> > work!
>
> > If anyone's interested the approach can also be adapted to work with
> > UDFs
> > in an Automation Addin though there's no way to avoid duplicate entries
> > in
> > the function wizard.
>
> > FWIW the code example can be considerably simplified, once you've got
> > your
> > head round it!
>
> > Regards,
> > Peter T
>
> > "Andrew" <hooks...@hotmail.com> wrote in message
> >news:5e621ee6-4e36-4190-8e12-(E-Mail Removed)...
> >> I've been playing with this recently as well, using the FunCustomize
> >> example on Laurent Longre's website,
> >>http://xcell05.free.fr/english/index.html
>
> >> I would be interested to know the relative merits of the two
> >> approaches (I realise both are a variation on the same idea).
> >> Laurent's method provides a dll and the only vb code there is is to
> >> install the dll and pass a range with the UDF properties to it (and
> >> uninstall when the add-in closes). It looks comparatively simple. I
> >> presume somehow the dll then registers dummy functions using that
> >> data. One thing I like about this approach is that you don't have to
> >> overwrite functionality of any existing dll functions. The help file
> >> mentions the 255 character limit but this must only apply to each
> >> individual description as I'm sure the total string length for each of
> >> my functions exceeds 255 characters. I guess it means an extra file
> >> to distribute which could be a disadvantage but the functions I'm
> >> working with all require additional data files anyway.
>
> >> Cheers,
> >> Andrew
>
> >> On 30 Apr, 13:41, "Peter T" <peter_t@discussions> wrote:
> >>> It's not straight forward at all, but it can be done (not tooltips
> >>> though)
>
> >>>http://www.jkp-ads.com/articles/RegisterUDF01.asp
>
> >>> Regards,
> >>> Peter T
>
> >>> "Faraz Ahmed Qureshi" <FarazAhmedQure...@discussions.microsoft.com>
> >>> wrote in
> >>> messagenews:B5447321-57FD-46EC-B9AA-(E-Mail Removed)...
>
> >>> > Any guidance as to how to have a UDF in an addin be
> >>> > declared/registered
> >>> > etc.
> >>> > so as to be used like all other usual/normal functions? like be
> >>> > autocompleted
> >>> > upon partial entry, arguments be displayed in the supertip and be
> >>> > capable
> >>> > to
> >>> > be used in the Conditional Formatting and other features?
>
> >>> > --
> >>> > Thanx in advance & Best Regards,
>
> >>> > Faraz!- Hide quoted text -
>
> >>> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

On Apr 30, 9:36*pm, "Peter T" <peter_t@discussions> wrote:
> Ah I suppose you can name the name the dll to whatever you want, and
> providing nobody else uses a similarly named dll + function there's never
> likely to be a problem.
>
> I'm sure Laurant's was limited to 255 but maybe he has subsequently changed
> something I'm not aware of.
>
> Is Stephen Bullen's method not intrinsically the same as Laurant's?
>
> Regards,
> Peter T
>
> "Andrew" <hooks...@hotmail.com> wrote in message
>
> news:4ad65926-af5c-4dfa-a96b-(E-Mail Removed)...
> According to his help file if you need to register more than 200
> functions you can create a second copy of the dll and start over again
> so on that basis I assume there wouldn't be a conflict with someone
> elses app.
>
> I'll have to have another look at the number of characters but
> definately the Stephen Bullen method (from the CD in the book) kicked
> me out because my descriptions were too long and so far no problem
> with Laurents dll in that regard.
>
> Thanks,
> Andrew
>
> On 30 Apr, 16:30, "Peter T" <peter_t@discussions> wrote:
>
>
>
> > PS, forgot to add,
>
> > >> One thing I like about this approach is that you don't have to
> > >> overwrite functionality of any existing dll functions.
>
> > The functionality of the 'borrowed' functions is not overwritten, but it's
> > worth not using the named ones in the example in case any one else uses
> > them
> > for the same purpose in the same system. That might in theory be an
> > advantage of Laurent's dll, assuming of course no one else's app is not
> > using the same dll (hmm not sure if that's a potential issue or not, I
> > haven't used his in so long).
>
> > Peter T
>
> > "Peter T" <peter_t@discussions> wrote in message
>
> >news:(E-Mail Removed)...
>
> > > The main advantage is overcoming the 255 limit. I'm (almost) sure you
> > > will
> > > not have included more than a combined total of 255 for all the
> > > descriptions in a single UDF.
>
> > > As you say the method is a development of Laurent Longre's, it's an
> > > absolute mystery to me as to how Jurgen Volkerink (KeepItCool) got itto
> > > work!
>
> > > If anyone's interested the approach can also be adapted to work with
> > > UDFs
> > > in an Automation Addin though there's no way to avoid duplicate entries
> > > in
> > > the function wizard.
>
> > > FWIW the code example can be considerably simplified, once you've got
> > > your
> > > head round it!
>
> > > Regards,
> > > Peter T
>
> > > "Andrew" <hooks...@hotmail.com> wrote in message
> > >news:5e621ee6-4e36-4190-8e12-(E-Mail Removed)....
> > >> I've been playing with this recently as well, using the FunCustomize
> > >> example on Laurent Longre's website,
> > >>http://xcell05.free.fr/english/index.html
>
> > >> I would be interested to know the relative merits of the two
> > >> approaches (I realise both are a variation on the same idea).
> > >> Laurent's method provides a dll and the only vb code there is is to
> > >> install the dll and pass a range with the UDF properties to it (and
> > >> uninstall when the add-in closes). It looks comparatively simple. I
> > >> presume somehow the dll then registers dummy functions using that
> > >> data. One thing I like about this approach is that you don't have to
> > >> overwrite functionality of any existing dll functions. The help file
> > >> mentions the 255 character limit but this must only apply to each
> > >> individual description as I'm sure the total string length for each of
> > >> my functions exceeds 255 characters. I guess it means an extra file
> > >> to distribute which could be a disadvantage but the functions I'm
> > >> working with all require additional data files anyway.
>
> > >> Cheers,
> > >> Andrew
>
> > >> On 30 Apr, 13:41, "Peter T" <peter_t@discussions> wrote:
> > >>> It's not straight forward at all, but it can be done (not tooltips
> > >>> though)
>
> > >>>http://www.jkp-ads.com/articles/RegisterUDF01.asp
>
> > >>> Regards,
> > >>> Peter T
>
> > >>> "Faraz Ahmed Qureshi" <FarazAhmedQure...@discussions.microsoft.com>
> > >>> wrote in
> > >>> messagenews:B5447321-57FD-46EC-B9AA-(E-Mail Removed)...
>
> > >>> > Any guidance as to how to have a UDF in an addin be
> > >>> > declared/registered
> > >>> > etc.
> > >>> > so as to be used like all other usual/normal functions? like be
> > >>> > autocompleted
> > >>> > upon partial entry, arguments be displayed in the supertip and be
> > >>> > capable
> > >>> > to
> > >>> > be used in the Conditional Formatting and other features?
>
> > >>> > --
> > >>> > Thanx in advance & Best Regards,
>
> > >>> > Faraz!- Hide quoted text -
>
> > >>> - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Its great to have that addin.
But, I am thinking some modifications in the addin
So, that if there are less number of functions (not 200 atleast around
5 lets say) then
i need to make all the code within the module
Also, that there should not be anything in the sheets.
The code whould include all that within it.
Thanks in advance.

"Subodh" <(E-Mail Removed)> wrote in message
news:83b70cf6-d74a-4d50-97ad-

> Its great to have that addin.
> But, I am thinking some modifications in the addin
> So, that if there are less number of functions (not 200 atleast around
> 5 lets say) then
> i need to make all the code within the module
> Also, that there should not be anything in the sheets.
> The code whould include all that within it.
> Thanks in advance.

Subodh,
I assume you are talking about Laurant's addin. I've just had a quick look
at his latest version.

I'm not sure if I understand your question correctly. However you do not
need to include any text data in cells. You can simply supply an array
between 7 to 26 columns depending on maximum number of function arguments,
and with rows to cater for the number of functions.

Andrew,
Looks like I can include more than 255 characters in total, I'm surprised
and not sure what's going on there. Yet that even contradicts the help file:

"- The total length of all string fields (Function name, argument names,
descriptions.) can't contain more than 255 characters. If it exceeds this
limit, the last strings will be truncated in the function wizard."

Regards,
Peter T