Addin functions in user defined
Addin functions in user defined
(OP)
When I use functions that I have written myself as addins they always appear under "user defined" in the Insert Function drop down menu.
Is there any way that such functions can be arranged to appear under another label on the menu such as "engineering". I have read some where that this may be possible but cannot remember where I read it.
Is there any way that such functions can be arranged to appear under another label on the menu such as "engineering". I have read some where that this may be possible but cannot remember where I read it.





RE: Addin functions in user defined
RE: Addin functions in user defined
Notes:
Created by Laurent Longre
This example shows how to register functions into user-defined catagories and provide descriptions for their arguments. The Auto_Open [RegisterUDFs] procedure registers the two functions, Multiply and Divide in two categories Multiplication and Division and provides descriptions of the input parameters.
CODE
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 RegisterUDFs()
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 UnRegisterUDFs()
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
Regards,
Mike
RE: Addin functions in user defined
RE: Addin functions in user defined
Try this, works fine for me but can only add one new category. If make another add-in that uses similar coding, will not make a new category but uses the old one for some reason. Haven't bothered to find out why as not a problem for me as only sell one add-in per client so far.
Call this macro below from your auto open macro.
Private Sub catallocate()
Application.ActiveWorkbook.Names.Add Name:="Anything", RefersTo:="A1", MacroType:=2, Category:="Whatever name you want for category"
On Error GoTo errortrapper
'Next few line searches thru all existing categories for a function you have defined until no more categories exist, then gives an error and jumps to error routine. This is to make sure are adding your function to the last one in the list of existing categories ie the one we just added above. May be more elegant way but i couldn't find it!
For i = 1 To 1000
Application.MacroOptions Macro:="gl_abp", Category:=i
Next i
errortrapper:
On Error GoTo 0
mac_cat = i - 1
mac_name = "function_one" 'First defined function name
mac_descrip = "Description of what function one does"
Application.MacroOptions Macro:=mac_name, Category:=mac_cat, Description:=mac_descrip
mac_name = "function_two" 'Second defined function name
mac_descrip = "Description of what function two does"
Application.MacroOptions Macro:=mac_name, Category:=mac_cat, Description:=mac_descrip
etc etc for each defined function
Application.MacroOptions Macro:="Anything", Category:=0
Application.Names("Anything").Delete
End Sub
RE: Addin functions in user defined
Just tried method given by msmith and works like a dream this time! Must have got my quotes muddled up last time I tried this way.....
Thanks msmith.