• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

How to invoke Subroutines/Macros from an xla file in an Excel sheet

I I created an add in file (xla), which is always opened when I run Excel.
Here I have got different functions which are available for all worksheets open.

Now I recorded a marco and i put the result also in the xla file.
I declared the subroutine public

When I now go to
/Tools/ Macro / Macro
I do not find this Macro Name in the list, even i choose look in all open workbook.
What do I have to do to acces a macro in a xla file???
  • 3
  • 2
2 Solutions
Rory ArchibaldCommented:
You can run it by typing the macro name into the dialog, but usually routines in an add-in need a menu (button on a toolbar) to run them. They do not show up in the macros list.
themrocAuthor Commented:
How do i create a menu button or perhaps better a list entry/
Patrick MatthewsCommented:
To create custom menu structures, I often use a modified version of the general approach Excel MVP John Walkenbach describes here:


It works in Excel 2007, but in a slightly funny way :)
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Rory ArchibaldCommented:
What did you mean by "perhaps better a list entry"?
themrocAuthor Commented:
I have seen add in files when ticked they create an entry with a specific name under tools in the excel menue.
Rory ArchibaldCommented:
You can certainly do that - it's just a variation of the technique J_Walk demonstrates in Patrick's link. As a rough example of how to create a new menu item under tools:

' In a normal module:
Option Explicit
' this is the name of the toolbar - you can leave this as is or change it
Const mcstrTOOLBAR_NAME As String = "Tools"
' this is the button caption - change as required
Const mcstrBUTTON_CAPTION As String = "Click me"
' this is the name of the macro that the button will run - change as needed
Const mcstrBUTTON_MACRO As String = "ClickHyperlink"

Sub AddMenu()
    Dim cbr As CommandBar, ctl As CommandBarControl, ctlParent As CommandBarControl
    ' worksheet menu bar
    Set cbr = Application.CommandBars(1)
    Set ctlParent = cbr.Controls(mcstrTOOLBAR_NAME)
    On Error Resume Next
    On Error GoTo err_handle
    ' add button to it
    Set ctl = ctlParent.Controls.Add(msoControlButton, , , , True)
    With ctl
        .Caption = mcstrBUTTON_CAPTION
        .OnAction = mcstrBUTTON_MACRO
        .Style = msoButtonCaption
    End With
    Exit Sub
    MsgBox Err.Description
End Sub
Sub RemoveMenu()
    On Error Resume Next
End Sub

'In the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub

Private Sub Workbook_Open()
End Sub

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now