How to add a macro to mutiple excel files using VBA

Question

Is there any way to write a VBA Macro to input another VBA Macro into multiple excel workbooks? If so, how do I start?

Any and all help is greatly appreciated.

Answer 1

you'll need a reference first

Microsoft Visual Basic For Applications Extensibility 5.3

And here you go. Have fun

Public Sub AddNewModule()

  Dim proj As VBIDE.VBProject
  Dim comp As VBIDE.VBComponent

  Set proj = ActiveWorkbook.VBProject
  Set comp = proj.VBComponents.Add(vbext_ct_StdModule)
  comp.Name = "MyNewModule"

  Set codeMod = comp.CodeModule

  With codeMod
    lineNum = .CountOfLines + 1
    .InsertLines lineNum, "Public Sub ANewSub()"
    lineNum = lineNum + 1
    .InsertLines lineNum, "  MsgBox " & """" & "I added a module!" & """"
    lineNum = lineNum + 1
    .InsertLines lineNum, "End Sub"
  End With

End Sub

You can also just use the workbook with the code in it as a reference as well. Then you can call the module remotely.

As @BruceWayne mentioned, there is also sotring it in the personal book.

tl;dr - there's a few options that can get you there.

Answer 2

I recommend storing them in the Personal.xslb file which is accessible across Excel.

See this page or this page for more detail, but generally a quick way to get started is:

1. Press ALT+F11 to open the VBEditor.
2. Right click the "VBAProject (PERSONAL.XLSB)" and Add a new module
3. Add your code in the module.
4. Now, when you go to View --> Macros, you can choose to see those stored in the Personal.xlsb file:

_{(I "whited out" my macros for privacy, but they'll be listed by name)}

Note: If you do not have a "Personal.xlsb", then you must create it. Simply record a new macro, but choose to store it in "Personal Macro Workbook". Then you should see it in the VBEditor.

Answer 3

I would think the easiest way to have the same code in slightly different Excel files is to have one 'template' and save it several times as several slightly different files. Or, if you want to get fancy, you can create an AddIn to make an Excel Macro available to all workbooks.

Option Explicit
Dim cControl As CommandBarButton

Private Sub Workbook_AddinInstall()
On Error Resume Next 'Just in case
    'Delete any existing menu item that may have been left.
    Application.CommandBars("Worksheet Menu Bar").Controls("Super Code").Delete
    'Add the new menu item and Set a CommandBarButton Variable to it
    Set cControl = Application.CommandBars("Worksheet Menu Bar").Controls.Add
    'Work with the Variable
        With cControl
            .Caption = "Super Code"
            .Style = msoButtonCaption
            .OnAction = "MyGreatMacro"
            'Macro stored in a Standard Module
        End With
    On Error GoTo 0
End Sub

Private Sub Workbook_AddinUninstall()
On Error Resume Next 'In case it has already gone.
    Application.CommandBars("Worksheet Menu Bar").Controls("Super Code").Delete
    On Error GoTo 0
End Sub

This code will be all you need to add a single menu item (called Super Code) to the end of the existing Worksheet Menu Bar as soon as the Add-in is installed by the user via Tools>Add-ins. When the Super Code menu item is clicked a macro (that is within a standard module of the add-in) is run. As mentioned earlier, the above code MUST be placed in the Private Module of ThisWorkbook for the Add-in.

If you want the Super Code menu item added, say before the Format menu item, you could use some code like this.

Option Explicit
Dim cControl As CommandBarButton

Private Sub Workbook_AddinInstall()
Dim iContIndex As Integer
    On Error Resume Next 'Just in case
    'Delete any existing menu item that may have been left
    Application.CommandBars("Worksheet Menu Bar").Controls("SuperCode").Delete
    'Pass the Index of the "Format" menu item number to a Variable.
    'Use the FindControl Method to find it's Index number. ID number _
     is used in case of Customization
    iContIndex = Application.CommandBars.FindControl(ID:=30006).Index
    'Add the new menu item and Set a CommandBarButton Variable to it.
    'Use the number passed to our Integer Variable to position it.
    Set cControl = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Before:=iContIndex)
    'Work with the Variable
        With cControl
            .Caption = "Super Code"
            .Style = msoButtonCaption
            .OnAction = "MyGreatMacro" 
            'Macro stored in a Standard Module
        End With
    On Error GoTo 0
End Sub

There would be no need to change the Workbook_AddinUninstall() code in this case.

We have covered ID numbers while working with CommandBars etc in a P rior Newsletter Issue The link to the Microsoft site that has a BIG list of all the ID numbers for working with CommandBars can be Found Here

The above examples actually have the all the menu item code in the Workbook_AddinInstall and Workbook_AddinUnInstall Not a problem when the code is only adding one menu item. If however, you will be adding more then one and perhaps even Sub menus, you should place it in a Procedure (or 2) inside a standard Module. Then use some code as shown below

Private Sub Workbook_AddinInstall()
Run "AddMenus"
End Sub



Private Sub Workbook_AddinUninstall()
Run "DeleteMenu"
End Sub

Then in the standard module put some code perhaps like this

Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl
    '(1)Delete any existing one.We must use On Error Resume next _
        in case it does not exist.
    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Controls("&NewMenu").Delete
    '(2)Set a CommandBar variable to Worksheet menu bar
    Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
    '(3)Return the Index number of the Help menu. We can then use _
        this to place a custom menu before.
    iHelpMenu = cbMainMenuBar.Controls("Help").Index
    '(4)Add a Control to the "Worksheet Menu Bar" before Help
    'Set a CommandBarControl variable to it
    Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpMenu)
    '(5)Give the control a caption
    cbcCutomMenu.Caption = "&New Menu"
    '(6)Working with our new Control, add a sub control and _
        give it a Caption and tell it which macro to run (OnAction).
            With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
                .Caption = "Menu 1"
                .OnAction = "MyMacro1"
            End With
    '(6a)Add another sub control give it a Caption _
        and tell it which macro to run (OnAction)
            With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
                .Caption = "Menu 2"
                .OnAction = "MyMacro2"
            End With
'Repeat step "6a" for each menu item you want to add.
    'Add another menu that will lead off to another menu
    'Set a CommandBarControl variable to it
        Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)
    ' Give the control a caption
        cbcCutomMenu.Caption = "Next Menu"
    'Add a control to the sub menu, just created above
        With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
            .Caption = "&Charts"
            .FaceId = 420
.OnAction = "MyMacro2"
End With
On Error GoTo 0
End Sub


Sub DeleteMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&NewMenu").Delete
    On Error GoTo 0
End Sub

You can find all details here.

http://www.ozgrid.com/VBA/excel-add-in-create.htm