User-defined function Category
R.A
Help Needed!
I'm writting XLAs under XL using VBA modules.
I know how to create a new function category and how to assign my functions
to this one under the worksheet function wizard (instead of the default
user defined category).
I also assign options for each of my functions using the MacroOptions method
(description, function category,Help Topic ID, Help file...)
My question is the following:
The new function category I added remains, that's fine!
But (yes but) I have others add-ins that are loaded before this one when
Excel is loading, and from which other categories are created. Because of
this, I have my functions in the wrong category though mine still exist.
here what is happens
Once I created my category and assigned my functions to it
---
User defined
My category---> my functions
---
Next time I load Excel, I have this
---
User defined
other category---> my functions
My category
---
Could anyone help me fixing this please?
Thanks a lot.
--
Roland H
e-mail:rol...@hol.fr
John Walkenbach
I've done a lot of experimenting with custom worksheet function categories.
I've concluded that this feature is flaky at best and I've never been able
to get it to work reliably.
John Walkenbach
http://www.j-walk.com/ss/
R.A wrote in message <6ao8kc$a2i$1...@news2.isdnet.net>...
>The new function category I added remains, that's fine!
>But (yes but) I have others add-ins that are loaded before this one when
>Excel is loading, and from which other categories are created. Because of
>this, I have my functions in the wrong category though mine still exist.
>Roland H
>e-mail:rol...@hol.fr
>
John Green
Hi Roland,
I agree with John Walkenbach's assessment. This is a flakey feature.
Categories are assigned by number, not by name. Unfortunately, if you
load files which introduce new categories, the category index numbers
depend on the order of loading. If you can't predict this, you can't be
sure what category the function will fall into. I know of no good
solution to this,
Regards,
John Green
Sydney
Australia
Microsoft MVP - Excel
Laurent Longre
Bonjour John,
John Walkenbach wrote:
>
> I've done a lot of experimenting with custom worksheet function categories.
> I've concluded that this feature is flaky at best and I've never been able
> to get it to work reliably.
Like you, I've done a lot of experimenting with custom whorksheet
function categories (XL97 SR-1).
I've already built some XLL add-in functions, and I was wondering if it
was possible to apply indirectly the REGISTER command (which provides a
lot of formatting options, like custom categories) on functions written
in VBA. I've recently found a way to do this.
This method is based on a curious behaviour of XL97 : assume that an
open workbook contains a function called "Myfunc". If you try now to
register any DLL function (for instance, one of the Win95 API) with the
same name, here's what happens: when you use MyFunc() in a worksheet, it
works fine and returns the result of the VBA function. But if you call
it with the function wizard, it displays the informations of ... the
registered DLL function!
In other words, you can add a few lines in an Auto_open Sub which
register some API functions with the same names as your VBA functions.
REGISTER enables to assign the functions to any custom categories, and
also to "document" each argument. When you call later one of the VBA
custom function, these parameters passed to REGISTER will appear in the
function wizard, including the custom category.
After further experimenting, I've noticed also that you must declare the
VBA functions "Private" in order to remove them from the default
"user-defined" category (otherwise, they would appear twice in the
wizard). Of course, the VBA add-in must also provide an Auto_close Sub
which unregisters the functions. To remove the added custom categories,
I've found this way : first, unregister the functions, then register
them with the MacroType argument set to 0 (= hidden function), and
finally unregister them one more time.
This method is just a funny work-around. I don't know if it works
without any restriction (I've just achieved a few tests), and it
requires that you add some Auto_open and Auto_close code in the add-in
workbook.
If you are interested, I've copied an example below. This add-in
contains two dummy custom functions (MULTIPLY and ... DIVIDE !!!...)
assigned to two separate function categories ("Multiplication" and
"Division"). These categories are automatically deleted after closing
the workbook.
Cordiales salutations,
Laurent Longre
'==========================================
Const Lib = """c:\windows\system\user32.dll"""
Option Base 1
Private Function Multiply(N1 As Double, N2 As Double) As Double
Multiply = N1 * N2
End Function
'==========================================
Private Function Divide(N1 As Double, N2 As Double) As Double
Divide = N1 / N2
End Function
'==========================================
Sub Auto_open()
Register "DIVIDE", 3, "Numerator,Divisor", 1, "Division", _
"Divides two numbers", """Numerator"",""Divisor """, "CharPrevA"
Register "MULTIPLY", 3, "Number1,Number2", 1, "Multiplication", _
"Multiplies two numbers", """First number"",""Second number """, _
"CharNextA"
End Sub
'==========================================
Sub Register(FunctionName As String, NbArgs As Integer, _
Args As String, MacroType As Integer, Category As String, _
Descr As String, DescrArgs As String, FLib As String)
Application.ExecuteExcel4Macro _
"REGISTER(" & Lib & ",""" & FLib & """,""" & String(NbArgs, "P") _
& """,""" & FunctionName & """,""" & Args & """," & MacroType _
& ",""" & Category & """,,,""" & Descr & """," & DescrArgs & ")"
End Sub
'==========================================
Sub Auto_close()
Dim FName, FLib
Dim I As Integer
FName = Array("DIVIDE", "MULTIPLY")
FLib = Array("CharPrevA", "CharNextA")
For I = 1 To 2
With Application
.ExecuteExcel4Macro "UNREGISTER(" & FName(I) & ")"
.ExecuteExcel4Macro "REGISTER(" & Lib & _
",""CharPrevA"",""P"",""" & FName(I) & """,,0)"
.ExecuteExcel4Macro "UNREGISTER(" & FName(I) & ")"
End With
Next
End Sub
Jim Rech
Tres clever, Laurent!
Jim
John Green
G'Day Laurent,
Ca, c'est formidable.
Your code seems to get around the problems of I was having with
assigning categories by number.
I played around with your code and used the AddIn Manager to activate
and remove the Analysis ToolPak functions. I also ran your Auto_Open and
Auto_Close routines. There was one odd interaction. After unloading the
ToolPak, the Engineering category remained in the Function Manager and
could be added to the worksheet, although help for the functions
disappeared and the functions could not be calculated.
As I doubt most users would be doing this, I think your code provides a
very practical approach to user defined categories,
Congratulations,