Putting UDF in custom or predefined function category

[ExcelMonkey]

I am trying to create a new category for the function list in Excel and then
add a new UDF from one of my XLA addins to this list. I am in Excel 2003.

I have created the new function category by inserting an MS Excel 4.0 macro
sheet and going to INSERT/NAME/DEFINE. I then created created a name Test1.
I then clicked on Function under Macro, typed in the new function name
("MyFunctions")under the Category heading.

So now the category exists. I am having trouble adding the UDF to this or
any other list. The code I have for adding it to say the Text List is below.
I am getting an error message saying "Cannot edit a maro on a hidden
workbook. Unhide the workbook useing the Unhide Command." It stops on the
line of code:

Application.MacroOptions Macro:="VowelCount", Category:=7

Questions"
1) How do I unhide the xla to add the function?
2) What is the index number of my new category?
3) Is this number constant over time or is there risk that it could change?

Lastly, I have seen alot of posts in this newsgroyp that refer to this
issues by directing people to:
http://longre.free.fr/english/func_cats.htm

But this address does not seem to direct me to the info I am looking for. It
seems to go to the right site but most of it is in French and that which is
in english does not seem to answer my questions. I am not sure if the
orignal link has changed. Please let me know if I am wrong on this.

Thanks in advance.

"************************************************
'in Thisworkbook module
Private Sub Workbook_Open()
Application.MacroOptions Macro:="VowelCount", Category:=7
End Sub


'in regular module
Function VowelCount(r As String)
Count = 0
For i = 1 To Len(r)
Ch = UCase(Mid(r, i, 1))
If Ch Like "[AEIOU]" Then
Count = Count + 1
Debug.Print Ch, i
End If
Next i
VowelCount = Count
End Function
"******************************************************

 

[ExcelMonkey]

I looked closer at: http://longre.free.fr/english/func_cats.htm and realised
that there is an xla and dll you can download that shows an example of how to
what I am trying to do. I will give it a look.

EM

 

[Bob Phillips]

Application.MacroOptions Macro:="MyFunc", Category:=1

The table below lists the valid function category numbers.

Number Category

0 No category (appears only in All)
1 Financial
2 Date & Time
3 Math & Trig
4 Statistical
5 Lookup & Reference
6 Database
7 Text
8 Logical
9 Information
10 Commands (this category is normally hidden)
11 Customizing (this category is normally hidden)
12 Macro Control (this category is normally hidden)
13 DDE/External (this category is normally hidden)
14 User Defined (default)
15 Engineering (this category is valid only if the Analysis Toolpak add-in
is installed)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[ExcelMonkey]

As you can see Bob, i am doing exactly what you have told me to do. Yets is
faililng when the xla opens up. I went to the website (
http://longre.free.fr/english/func_cats.htm) and downloaded the xla and DLL
but this as raised even more questions for me"

1) What exactly is happening when the DLL is registered?
2) It appears from the code in the xla that there is not a line like
Application.MacroOptions Macro:="MyFunc", Category:=1

Is this happenin within the DLL? I do not have any experience with DLLs so
I find the example from the website a tad complicated.

Thanks

EM

Application.MacroOptions Macro:="MyFunc", Category:=1

The table below lists the valid function category numbers.

Number Category

0 No category (appears only in All)
1 Financial
2 Date & Time
3 Math & Trig
4 Statistical
5 Lookup & Reference
6 Database
7 Text
8 Logical
9 Information
10 Commands (this category is normally hidden)
11 Customizing (this category is normally hidden)
12 Macro Control (this category is normally hidden)
13 DDE/External (this category is normally hidden)
14 User Defined (default)
15 Engineering (this category is valid only if the Analysis Toolpak add-in
is installed)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

I am trying to create a new category for the function list in Excel and then
add a new UDF from one of my XLA addins to this list. I am in Excel 2003.

I have created the new function category by inserting an MS Excel 4.0 macro
sheet and going to INSERT/NAME/DEFINE. I then created created a name Test1.
I then clicked on Function under Macro, typed in the new function name
("MyFunctions")under the Category heading.

So now the category exists. I am having trouble adding the UDF to this or
any other list. The code I have for adding it to say the Text List is below.
I am getting an error message saying "Cannot edit a maro on a hidden
workbook. Unhide the workbook useing the Unhide Command." It stops on the
line of code:

Application.MacroOptions Macro:="VowelCount", Category:=7

Questions"
1) How do I unhide the xla to add the function?
2) What is the index number of my new category?
3) Is this number constant over time or is there risk that it could change?

Lastly, I have seen alot of posts in this newsgroyp that refer to this
issues by directing people to:
http://longre.free.fr/english/func_cats.htm

But this address does not seem to direct me to the info I am looking for. It
seems to go to the right site but most of it is in French and that which is
in english does not seem to answer my questions. I am not sure if the
orignal link has changed. Please let me know if I am wrong on this.

Thanks in advance.

"************************************************
'in Thisworkbook module
Private Sub Workbook_Open()
Application.MacroOptions Macro:="VowelCount", Category:=7
End Sub


'in regular module
Function VowelCount(r As String)
Count = 0
For i = 1 To Len(r)
Ch = UCase(Mid(r, i, 1))
If Ch Like "[AEIOU]" Then
Count = Count + 1
Debug.Print Ch, i
End If
Next i
VowelCount = Count
End Function
"******************************************************

 

[Niek Otten]

This has been and still remains a difficult subject since years. The article
in the link below seems to be the state-of-the-art presently, but even there
is a warning for possible difficulties.
I myself never managed to get it a 100% right until now, but I'll keep
trying!

http://www.jkp-ads.com/Articles/RegisterUDF00.htm

--
Kind regards,

Niek Otten

As you can see Bob, i am doing exactly what you have told me to do. Yets
is
faililng when the xla opens up. I went to the website (
http://longre.free.fr/english/func_cats.htm) and downloaded the xla and
DLL
but this as raised even more questions for me"

1) What exactly is happening when the DLL is registered?
2) It appears from the code in the xla that there is not a line like
Application.MacroOptions Macro:="MyFunc", Category:=1

Is this happenin within the DLL? I do not have any experience with DLLs
so
I find the example from the website a tad complicated.

Thanks

EM

Application.MacroOptions Macro:="MyFunc", Category:=1

The table below lists the valid function category numbers.

Number Category

0 No category (appears only in All)
1 Financial
2 Date & Time
3 Math & Trig
4 Statistical
5 Lookup & Reference
6 Database
7 Text
8 Logical
9 Information
10 Commands (this category is normally hidden)
11 Customizing (this category is normally hidden)
12 Macro Control (this category is normally hidden)
13 DDE/External (this category is normally hidden)
14 User Defined (default)
15 Engineering (this category is valid only if the Analysis Toolpak
add-in
is installed)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

I am trying to create a new category for the function list in Excel and then
add a new UDF from one of my XLA addins to this list. I am in Excel
2003.

I have created the new function category by inserting an MS Excel 4.0 macro
sheet and going to INSERT/NAME/DEFINE. I then created created a name Test1.
I then clicked on Function under Macro, typed in the new function name
("MyFunctions")under the Category heading.

So now the category exists. I am having trouble adding the UDF to this
or
any other list. The code I have for adding it to say the Text List is below.
I am getting an error message saying "Cannot edit a maro on a hidden
workbook. Unhide the workbook useing the Unhide Command." It stops on the
line of code:

Application.MacroOptions Macro:="VowelCount", Category:=7

Questions"
1) How do I unhide the xla to add the function?
2) What is the index number of my new category?
3) Is this number constant over time or is there risk that it could change?

Lastly, I have seen alot of posts in this newsgroyp that refer to this
issues by directing people to:
http://longre.free.fr/english/func_cats.htm

But this address does not seem to direct me to the info I am looking
for. It
seems to go to the right site but most of it is in French and that
which is
in english does not seem to answer my questions. I am not sure if the
orignal link has changed. Please let me know if I am wrong on this.

Thanks in advance.

"************************************************
'in Thisworkbook module
Private Sub Workbook_Open()
Application.MacroOptions Macro:="VowelCount", Category:=7
End Sub


'in regular module
Function VowelCount(r As String)
Count = 0
For i = 1 To Len(r)
Ch = UCase(Mid(r, i, 1))
If Ch Like "[AEIOU]" Then
Count = Count + 1
Debug.Print Ch, i
End If
Next i
VowelCount = Count
End Function
"******************************************************

 

[ExcelMonkey]

Yup this sounds more complicated than I had originally thought. Will have to
assess if its worth pursuing. Thanks.

EM

This has been and still remains a difficult subject since years. The article
in the link below seems to be the state-of-the-art presently, but even there
is a warning for possible difficulties.
I myself never managed to get it a 100% right until now, but I'll keep
trying!

http://www.jkp-ads.com/Articles/RegisterUDF00.htm

--
Kind regards,

Niek Otten

As you can see Bob, i am doing exactly what you have told me to do. Yets
is
faililng when the xla opens up. I went to the website (
http://longre.free.fr/english/func_cats.htm) and downloaded the xla and
DLL
but this as raised even more questions for me"

1) What exactly is happening when the DLL is registered?
2) It appears from the code in the xla that there is not a line like
Application.MacroOptions Macro:="MyFunc", Category:=1

Is this happenin within the DLL? I do not have any experience with DLLs
so
I find the example from the website a tad complicated.

Thanks

EM

Application.MacroOptions Macro:="MyFunc", Category:=1

The table below lists the valid function category numbers.

Number Category

0 No category (appears only in All)
1 Financial
2 Date & Time
3 Math & Trig
4 Statistical
5 Lookup & Reference
6 Database
7 Text
8 Logical
9 Information
10 Commands (this category is normally hidden)
11 Customizing (this category is normally hidden)
12 Macro Control (this category is normally hidden)
13 DDE/External (this category is normally hidden)
14 User Defined (default)
15 Engineering (this category is valid only if the Analysis Toolpak
add-in
is installed)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

I am trying to create a new category for the function list in Excel and
then
add a new UDF from one of my XLA addins to this list. I am in Excel
2003.

I have created the new function category by inserting an MS Excel 4.0
macro
sheet and going to INSERT/NAME/DEFINE. I then created created a name
Test1.
I then clicked on Function under Macro, typed in the new function name
("MyFunctions")under the Category heading.

So now the category exists. I am having trouble adding the UDF to this
or
any other list. The code I have for adding it to say the Text List is
below.
I am getting an error message saying "Cannot edit a maro on a hidden
workbook. Unhide the workbook useing the Unhide Command." It stops on
the
line of code:

Application.MacroOptions Macro:="VowelCount", Category:=7

Questions"
1) How do I unhide the xla to add the function?
2) What is the index number of my new category?
3) Is this number constant over time or is there risk that it could
change?

Lastly, I have seen alot of posts in this newsgroyp that refer to this
issues by directing people to:
http://longre.free.fr/english/func_cats.htm

But this address does not seem to direct me to the info I am looking
for.
It
seems to go to the right site but most of it is in French and that
which
is
in english does not seem to answer my questions. I am not sure if the
orignal link has changed. Please let me know if I am wrong on this.

Thanks in advance.

"************************************************
'in Thisworkbook module
Private Sub Workbook_Open()
Application.MacroOptions Macro:="VowelCount", Category:=7
End Sub


'in regular module
Function VowelCount(r As String)
Count = 0
For i = 1 To Len(r)
Ch = UCase(Mid(r, i, 1))
If Ch Like "[AEIOU]" Then
Count = Count + 1
Debug.Print Ch, i
End If
Next i
VowelCount = Count
End Function
"******************************************************

 

[keepITcool]

Niek,

I've mailed you a copy of UDF helper.
Inspired on JKP's post & Longre's ideas.
Plus a little trick of my own to use SET.NAME for
the arguments to the REGISTER function to avoid 255 char limit.
(recently published on Dick's blog).

Runs stable, from addin, no xlmsheet, no custom dll.
it's a beta with some hardcoding, but I'll release it soon.