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



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



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



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

...



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

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

...



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

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

...
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

...

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

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

On May 2, 5:46*pm, "Peter T" <peter_t@discussions wrote:
"Subodh" 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

Dear Peter,
I tried to modify the code of Laurant's addin in the first had
so that nothing for the UDF Registration should be in the sheet
and everything of the code should be in the code (class modules and
modules)
For that i thought that modificaiton was necessary in the class module
and more specific in the Sub ProcessRange
So, i tried to replace the sheet references by an array representation
and mind code looks like this

Sub ProcessRange(FuncListInclHeader As Range, bRegister As Boolean)

Dim r&, c&
Dim vArgs
vArgs = Array(Empty, _
"dllname*", "dllproc*", "argtype*", "funtext*",
"argtext*", _
"mactype*", "catname*", "keytext*", "hlppath*",
"funhelp*", _
"arghelp*")

With FuncListInclHeader
' With .Rows(1)
For c = 1 To 30
If Not LCase(.Columns(c)) Like
vArgs(Application.Min(c, 11)) Then
MsgBox "Range Headers invalid or missing"
Exit Sub
End If
Next
' End With


For r = 2 To 2 '.Rows.Count
' With .Rows(r)
' If Len(.Columns(4)) 0 Then
'Clear the data
ClearData
'Assign the properties
DllName = "user32.dll"
' .Columns (1)
DllProc = "CharNextA" '.Columns(2)
ArgType = P# '.Columns(3)
FunText = "MY FUN NAME" '.Columns(4)
ArgText = "MY ARG TYPE" '.Columns(5)
MacType = 1 '.Columns(6)
CatName = "UDF Helper DEMO" '.Columns(7)
KeyText = "" '.Columns (8)
HlpPath = "" '.Columns(9)
FunHelp = "This is just a trial" '.Columns(10)
' For c = 1 To 20
' ArgHelp(c) = .Columns(10 + c)
' Next
If bRegister Then
'Register
RegisterFunction
Else
'Unregister
UnregisterFunction
End If
End If
End With
Next
End With

This was a trial for just one function declaration
But it didn't work
any help would be appreciated.
Thanks in advance.

End Sub

"Subodh" wrote in message
...
On May 2, 5:46 pm, "Peter T" <peter_t@discussions wrote:
"Subodh" wrote in message

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

Dear Peter,
I tried to modify the code of Laurant's addin in the first had
so that nothing for the UDF Registration should be in the sheet
and everything of the code should be in the code (class modules and
modules)
For that i thought that modificaiton was necessary in the class module
and more specific in the Sub ProcessRange
So, i tried to replace the sheet references by an array representation
and mind code looks like this

=======================

Referring to Laurant's FunCustomize_Demo.xla

First run Workbook_AddinUninstall to remove the customized function
arguments

add the following in a normal module

Function GetAllArgs(arrArgs())
Dim i As Long, j
Dim arr()
Const cFUNCS As Long = 2 ' << change to suit
Const cMAXARGS As Long = 3 ' << change to suit

ReDim arrArgs(1 To cFUNCS, 1 To 6 + cMAXARGS)

For i = 1 To cFUNCS
Call GetArg(i, arr)
For j = 0 To UBound(arr)
arrArgs(i, j + 1) = arr(j)
Next
Next

End Function

Function GetArg(idx As Long, arr())
Dim varEmpty

'' this is the first 2 of 4 sets for function descriptions,
' add the other two in the as Cas2 3 & 4
' don't forget to change cFUNCS to 4 and maybe cMAXARGS

Select Case idx
Case 1
arr = Array("HYPOTENUSE", "Side 1,Side 2", varEmpty, _
"FunCustomize Demo", varEmpty, _
"Returns the length of an hypotenuse", _
"Length of the first side", _
"Length of the second side")

Case 2
arr = Array("RANDOM", "Min,Max,Volatility", varEmpty, _
"FunCustomize Demo", varEmpty, _
"Returns a random number between two integers", _
"Min. random number. If MINMAX, returns #NUM!", _
"Max. random number. If MAX<MIN, returns #NUM!", _
"Optional. If TRUE or omitted, " & _
"this function is volatile, otherwise it is static")

End Select

End Function

and amend the following
Sub Auto_Open()
Dim arrArgs() ' < new

GetAllArgs arrArgs ' < new

comment
' ' Run [FunCustomize], ThisWorkbook.Name, shFunctions.Range("A2:Z5")
add
Run [FunCustomize], ThisWorkbook.Name, arrArgs ' < new


Private Sub Workbook_AddinUninstall()
Dim arrArgs()

GetAllArgs arrArgs

If Run([FuncDelete], arrArgs) = -1 Then _
ExecuteExcel4Macro "UNREGISTER(""" & Me.Path & "\funcustomize.dll" &
""")"
End Sub


Must admit it took me a while to get it to work, eventually the trick was
the use of varEmpty for missing arg's instead of "" or entirely empty (which
becomes Missing)

Regards,
Peter T

On May 3, 2:42*pm, "Peter T" <peter_t@discussions wrote:
"Subodh" wrote in message

...
On May 2, 5:46 pm, "Peter T" <peter_t@discussions wrote:





"Subodh" wrote in message

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

Dear Peter,
I tried to modify the code of Laurant's addin in the first had
so that nothing for the UDF Registration should be in the sheet
and everything of the code should be in the code (class modules and
modules)
For that i thought that modificaiton was necessary in the class module
and more specific in the Sub ProcessRange
So, i tried to replace the sheet references by an array representation
and mind code looks like this

=======================

Referring to Laurant's FunCustomize_Demo.xla

First run Workbook_AddinUninstall to remove the customized function
arguments

add the following in a normal module

Function GetAllArgs(arrArgs())
Dim i As Long, j
Dim arr()
Const cFUNCS As Long = 2 ' * *<< change to suit
Const cMAXARGS As Long = 3 ' * *<< change to suit

* * ReDim arrArgs(1 To cFUNCS, 1 To 6 + cMAXARGS)

* * For i = 1 To cFUNCS
* * * * Call GetArg(i, arr)
* * * * For j = 0 To UBound(arr)
* * * * * * arrArgs(i, j + 1) = arr(j)
* * * * Next
* * Next

End Function

Function GetArg(idx As Long, arr())
Dim varEmpty

'' this is the first 2 of 4 sets for function descriptions,
' add the other two in the as Cas2 3 & 4
' don't forget to change cFUNCS to 4 and maybe cMAXARGS

* * Select Case idx
* * Case 1
arr = Array("HYPOTENUSE", "Side 1,Side 2", varEmpty, _
* * * * "FunCustomize Demo", varEmpty, _
* * * * "Returns the length of an hypotenuse", _
* * * * "Length of the first side", _
* * * * * * "Length of the second side")

* * Case 2
arr = Array("RANDOM", "Min,Max,Volatility", varEmpty, _
* * * * * * "FunCustomize Demo", varEmpty, _
* * * * * * "Returns a random number between two integers", _
* * * * * * "Min. random number. If MINMAX, returns #NUM!", _
* * * * * * "Max. random number. If MAX<MIN, returns #NUM!", _
* * * * * * "Optional. If TRUE or omitted, " & _
* * * * * * * * *"this function is volatile, otherwise it is static")

* * End Select

End Function

and amend the following
Sub Auto_Open()
Dim arrArgs() ' < new

GetAllArgs arrArgs ' < new

comment
' ' Run [FunCustomize], ThisWorkbook.Name, shFunctions.Range("A2:Z5")
add
* *Run [FunCustomize], ThisWorkbook.Name, arrArgs ' < new

Private Sub Workbook_AddinUninstall()
Dim arrArgs()

GetAllArgs arrArgs

If Run([FuncDelete], arrArgs) = -1 Then _
* *ExecuteExcel4Macro "UNREGISTER(""" & Me.Path & "\funcustomize.dll" &
""")"
End Sub

Must admit it took me a while to get it to work, eventually the trick was
the use of varEmpty for missing arg's instead of "" or entirely empty (which
becomes Missing)

Regards,
Peter T- Hide quoted text -

- Show quoted text -

Thanks peter
It worked fine.
But, actually i had not expected a modification of the Laurant's add
but the other one.
This also works fine but i don't want to have an extra .dll file
attached each time with the addin
Thanks anyway.
Or, is there any way I could attach the .dll file along with the same
addin.
Like inserting a form can we insert a dll file

"Subodh" wrote in message
On May 3, 2:42 pm, "Peter T" <peter_t@discussions wrote:
"Subodh" wrote in message

On May 2, 5:46 pm, "Peter T" <peter_t@discussions wrote:





"Subodh" wrote in message

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

Dear Peter,
I tried to modify the code of Laurant's addin in the first had
so that nothing for the UDF Registration should be in the sheet
and everything of the code should be in the code (class modules and
modules)
For that i thought that modificaiton was necessary in the class module
and more specific in the Sub ProcessRange
So, i tried to replace the sheet references by an array representation
and mind code looks like this

=======================

Referring to Laurant's FunCustomize_Demo.xla

First run Workbook_AddinUninstall to remove the customized function
arguments

add the following in a normal module

Function GetAllArgs(arrArgs())
Dim i As Long, j
Dim arr()
Const cFUNCS As Long = 2 ' << change to suit
Const cMAXARGS As Long = 3 ' << change to suit

ReDim arrArgs(1 To cFUNCS, 1 To 6 + cMAXARGS)

For i = 1 To cFUNCS
Call GetArg(i, arr)
For j = 0 To UBound(arr)
arrArgs(i, j + 1) = arr(j)
Next
Next

End Function

Function GetArg(idx As Long, arr())
Dim varEmpty

'' this is the first 2 of 4 sets for function descriptions,
' add the other two in the as Cas2 3 & 4
' don't forget to change cFUNCS to 4 and maybe cMAXARGS

Select Case idx
Case 1
arr = Array("HYPOTENUSE", "Side 1,Side 2", varEmpty, _
"FunCustomize Demo", varEmpty, _
"Returns the length of an hypotenuse", _
"Length of the first side", _
"Length of the second side")

Case 2
arr = Array("RANDOM", "Min,Max,Volatility", varEmpty, _
"FunCustomize Demo", varEmpty, _
"Returns a random number between two integers", _
"Min. random number. If MINMAX, returns #NUM!", _
"Max. random number. If MAX<MIN, returns #NUM!", _
"Optional. If TRUE or omitted, " & _
"this function is volatile, otherwise it is static")

End Select

End Function

and amend the following
Sub Auto_Open()
Dim arrArgs() ' < new

GetAllArgs arrArgs ' < new

comment
' ' Run [FunCustomize], ThisWorkbook.Name, shFunctions.Range("A2:Z5")
add
Run [FunCustomize], ThisWorkbook.Name, arrArgs ' < new

Private Sub Workbook_AddinUninstall()
Dim arrArgs()

GetAllArgs arrArgs

If Run([FuncDelete], arrArgs) = -1 Then _
ExecuteExcel4Macro "UNREGISTER(""" & Me.Path & "\funcustomize.dll" &
""")"
End Sub

Must admit it took me a while to get it to work, eventually the trick was
the use of varEmpty for missing arg's instead of "" or entirely empty
(which
becomes Missing)

Regards,
Peter T- Hide quoted text -

- Show quoted text -

Thanks peter
It worked fine.
But, actually i had not expected a modification of the Laurant's add
but the other one.
This also works fine but i don't want to have an extra .dll file
attached each time with the addin
Thanks anyway.
Or, is there any way I could attach the .dll file along with the same
addin.
Like inserting a form can we insert a dll file

==========================================

I'm confused, last time you said -

"I tried to modify the code of Laurant's addin in the first had
so that nothing for the UDF Registration should be in the sheet"

I showed you how to modify the addin without any UDF data in the sheet. Now
you say not that one but "the other one". Which addin are you referring to.

Regards,
Peter T

On 30 Apr., 17:05, Andrew wrote:
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

...

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

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 -- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hello Andrew,

I found XLW quite helpful to come up quickly with a reliable Add-in:
http://sulprobil.com/html/excel_addin.html

Regards,
Bernd

On May 4, 2:44*pm, Bernd P wrote:
On 30 Apr., 17:05, Andrew wrote:





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

...

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

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 -- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hello Andrew,

I found XLW quite helpful to come up quickly with a reliable Add-in:http://sulprobil.com/html/excel_addin.html

Regards,
Bernd- Hide quoted text -

- Show quoted text -

Sorry Peter.
I meant the addin in the link http://www.jkp-ads.com/articles/RegisterUDF00..asp
as mentioned above by Niek Otten
The advantage with this is that, it does not need any extra file (the
Dll file required in the case of Laurent's)
Or, as i said, it would also work if the dll file can also be attached
within the workbook in some way.

"Subodh" wrote in message
news:1a132b94-d528-48e7-8efe-

Sorry Peter.
I meant the addin in the link
http://www.jkp-ads.com/articles/RegisterUDF00.asp
as mentioned above by Niek Otten
The advantage with this is that, it does not need any extra file (the
Dll file required in the case of Laurent's)
Or, as i said, it would also work if the dll file can also be attached
within the workbook in some way.

========================

You replied to Bernd's message but I can see the question is addressed to
me. The file in that link is not an addin, it's a demo xls.

It's not important where the data strings exist, cells on a sheet, hard
coded array or even read from file. The tricky part is to ensure you make
the command strings in the same way as shown in the demo code, and pay
particular attention to ensuring string data is embraced in double quotes
where necessary.

For my purposes I found the best approach was to thoroughly understand the
code, then remake it from scratch. You may find it can be simplified,
removing quite a lot of redundancy and speeding things up.

Regards,
Peter T

On May 5, 1:18*am, "Peter T" <peter_t@discussions wrote:
"Subodh" wrote in message

news:1a132b94-d528-48e7-8efe-

Sorry Peter.
I meant the addin in the linkhttp://www.jkp-ads.com/articles/RegisterUDF00.asp
as mentioned above by Niek Otten
The advantage with this is that, it does not need any extra file (the
Dll file required in the case of Laurent's)
Or, as i said, it would also work if the dll file can also be attached
within the workbook in some way.

========================

You replied to Bernd's message but I can see the question is addressed to
me. The file in that link is not an addin, it's a demo xls.

It's not important where the data strings exist, cells on a sheet, hard
coded array or even read from file. The tricky part is to ensure you make
the command strings in the same way as shown in the demo code, and pay
particular attention to ensuring string data is embraced in double quotes
where necessary.

For my purposes I found the best approach was to thoroughly understand the
code, then remake it from scratch. You may find it can be simplified,
removing quite a lot of redundancy and speeding things up.

Regards,
Peter T

Thanks Peter.

On Sep 8, 5:03*am, jibin m wrote:
Is there any way to achieve the same *for excelautomationaddinusing c#.Since all the discussions are based on VBA..

pls let me know if something is found


Thanks
jibin



On Friday, April 30, 2010 8:23 AM Faraz Ahmed Qureshi wrote:
Any guidance as to how to have a UDF in anaddinbe 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!
On Friday, April 30, 2010 8:34 AM Niek Otten wrote:
Read this:

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel
On Friday, April 30, 2010 8:41 AM Peter T wrote:
it is not straight forward at all, but it can be done (not tooltips though)

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

Regards,
Peter T
On Friday, April 30, 2010 10:41 AM Andrew wrote:
I have 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 theadd-incloses). *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 do not 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 am 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 am
working with all require additional data files anyway.

Cheers,
Andrew
On Friday, April 30, 2010 11:16 AM Peter T wrote:
The main advantage is overcoming the 255 limit. I am (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 is 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
anAutomationAddinthough there is no way to avoid duplicate entries in the
function wizard.

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

Regards,
Peter T
On Friday, April 30, 2010 11:30 AM Peter T wrote:
PS, forgot to add,

The functionality of the 'borrowed' functions is not overwritten, but it is
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 is a potential issue or not, I
have not used his in so long).

Peter T
On Friday, April 30, 2010 12:05 PM Andrew wrote:
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 would not be a conflict with someone
elses app.

I will 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

s
hem
ill
o
Fs
in
our
.
=A0I
o
e
On Friday, April 30, 2010 12:36 PM Peter T 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 is never
likely to be a problem.

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

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

Regards,
Peter T

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 would not be a conflict with someone
elses app.

I will 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 Sunday, May 02, 2010 5:35 AM Subodh wrote:
ed
t's
to
es
...
of
On Sunday, May 02, 2010 8:46 AM Peter T wrote:
Subodh,
I assume you are talking about Laurant'saddin. I have just had a quick look
at his latest version.

I am 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 am surprised
and not sure what is going on there. Yet that even contradicts the help file:

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

Regards,
Peter T
On Sunday, May 02, 2010 11:00 PM Subodh wrote:
k
ot
,
le:

Dear Peter,
I tried to modify the code of Laurant'saddinin the first had
so that nothing for the UDF Registration should be in the sheet
and everything of the code should be in the code (class modules and
modules)
For that i thought that modificaiton was necessary in the class module
and more specific in the Sub ProcessRange
So, i tried to replace the sheet references by an array representation
and mind code looks like this

Sub ProcessRange(FuncListInclHeader As Range, bRegister As Boolean)

Dim r&, c&
Dim vArgs
vArgs =3D Array(Empty, _
"dllname*", "dllproc*", "argtype*", "funtext*",
"argtext*", _
"mactype*", "catname*", "keytext*", "hlppath*",
"funhelp*", _
"arghelp*")

With FuncListInclHeader
' * * With .Rows(1)
For c =3D 1 To 30
If Not LCase(.Columns(c)) Like
vArgs(Application.Min(c, 11)) Then
MsgBox "Range Headers invalid or missing"
Exit Sub
End If
Next
' * *End With

For r =3D 2 To 2 '.Rows.Count
' *With .Rows(r)
' * * If Len(.Columns(4)) 0 Then
'Clear the data
ClearData
'Assign the properties
DllName =3D "user32.dll"
' * * * * * * * * * *.Columns (1)
DllProc =3D "CharNextA" '.Columns(2)
ArgType =3D P# '.Columns(3)
FunText =3D "MY FUN NAME" '.Columns(4)
ArgText =3D "MY ARG TYPE" '.Columns(5)
MacType =3D 1 '.Columns(6)
CatName =3D "UDF Helper DEMO" '.Columns(7)
KeyText =3D "" '.Columns (8)
HlpPath =3D "" '.Columns(9)
FunHelp =3D "This is just a trial" '.Columns(10)
' * * * * * * * * * *For c =3D 1 To 20
' * * * * * * * * * * * ArgHelp(c) =3D .Columns(10 + c)
' * * * * * * * * *Next
If bRegister Then
'Register
RegisterFunction
Else
'Unregister
UnregisterFunction
End If
End If
End With
On Monday, May 03, 2010 5:42 AM Peter T wrote:
Dear Peter,
I tried to modify the code of Laurant'saddinin the first had
so that nothing for the UDF Registration should be in the sheet
and everything of the code should be in the code (class modules and
modules)
For that i thought that modificaiton was necessary in the class module
and more specific in the Sub ProcessRange
So, i tried to replace the sheet references by an array representation
and mind code looks like this

=======================

Referring to Laurant's FunCustomize_Demo.xla

First run Workbook_AddinUninstall to remove the customized function
arguments

add the following in a normal

...

read more »

Yes, you can write Excel add-in using C# easily. See
http://book.greenwich2greenwich.com/Examples/

Yes, you can write Excel add-in using C# easily. See
http://book.greenwich2greenwich.com/Examples/

jibin m
View profile
More options Sep 8, 5:03 am
Is there any way to achieve the same for excel automation addin using c#.Since all the discussions are based on VBA..
pls let me know if something is found

Thanks
jibin

I can only assume you didn't read the OP!

Regards,
Peter T


"My interest" wrote in message
...


Yes, you can write Excel add-in using C# easily. See
http://book.greenwich2greenwich.com/Examples/

jibin m
View profile
More options Sep 8, 5:03 am
Is there any way to achieve the same for excel automation addin using
c#.Since all the discussions are based on VBA..
pls let me know if something is found

Thanks
jibin