×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Addin functions in user defined

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.

RE: Addin functions in user defined

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

RE: Addin functions in user defined

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:
  • 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.
  • Laurent calls the register/unregister functions from Auto_Open 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.
  • Pay particular attention to the use of multiple quotation marks.
  • You will need to change the path of user32.dll if you are running Win2000, WinXP, etc.


  • 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

    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

    RE: Addin functions in user defined

    (OP)
    tanks msmith will try and get back

    RE: Addin functions in user defined

    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

    RE: Addin functions in user defined

    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.

    Red Flag This Post

    Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

    Red Flag Submitted

    Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
    The Eng-Tips staff will check this out and take appropriate action.

    Reply To This Thread

    Posting in the Eng-Tips forums is a member-only feature.

    Click Here to join Eng-Tips and talk with other members!


    Resources