Addin functions in user defined

[athomas236]

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.

 

[Denial]

And I have read somewhere that it is NOT possible, but again I cannot remember where. (Probably during some random scan of Google Groups, so maybe not totally authoritative.)

 

[msmith]

The following shows how to accomplish this. Its source is the Excel Experts E-Letter by David Hager (now defunct). Call the RegisterUDF procedure when your Add-In is loaded and UnRegisterUDF whenever the Add-in is unloaded.

Notes:
[li]The registered functions are only registerd for the current Excel session (i.e., The RegisterUDF sub would need to be called again on next Excel launch), which shouldn't be an issue if called from an Add-In that is loaded whenever Excel starts.[/li]
[li]Laurent calls the register/unregister functions from Autpen and Auto_Close procedures. I have renamed these to RegisterUDFs/UnRegisterUDFs which you can call from within Workbook_Open, Workbook_AddinInstall and Workbook_AddinUninstall event procedures.[/li]
[li]Pay particular attention to the use of multiple quotation marks.[/li]
[li]You will need to change the path of user32.dll if you are running Win2000, WinXP, etc.[/li]

Created by Laurent Longre

This example shows how to register functions into user-defined catagories and provide descriptions for their arguments. The Autpen [RegisterUDFs] procedure registers the two functions, Multiply and Divide in two categories Multiplication and Division and provides descriptions of the input parameters.

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

 

[athomas236]

tanks msmith will try and get back

 

[Mutt]

There is a way I found can be used. Using users32.dll as above gave me some headaches!

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

 

[Mutt]

Well, learn something everyday!
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.