Creating Add-ins in Excel
After much analysis I created my own steps to Create Excel Addin
Follow the below basic steps to create Addin -
- Always use Workbook or Activeworkbook – don’t use Thisworkbook in coding
- The Final Addin Should not have any sheets like – Raw file or data sheet
- Have a blank Sheet and all the coding should refer to Workbook Object or Activeworkbook method
- The Main Subroutine should be Public Sub Sub_name
- All Functions should be Public Function FnName
- All the modules should have specific names- select the module in Project Explorer View & Press F4 (properties) now enter a specific name for the module
- Now select the VBA Project in Project Explorer view & Press F4 (Properties) and enter a specific name for the project. This step is required because if you leave the default name for the project it would be named as "VBAProject" and MS Excel would not allow you to enter duplicate addins with same name - "VBAProject"
- You can use Public variables in Add-ins
- Now save the file as .XLA (2003 version) or .xlam (2007 version) [ Note you can save the file in any location - just remember where you have saved the addin file)
- Now your Addin has been created
So now the addin has been created, how to include the same in your project -
Now goto the main Excel file where you have to include the addin and goto the Visual Basic Editor (press Alt-F11)
Go to Tools > References > Click on Browse and select the required .xla or .xlam file
At this stage if you are getting any errors the below are the possible reasons -
- The Function Name or Module name or Project name or Sub routine name or Variable name would have already been there in the current module as well. Check for duplicated Sub or Function or Variable name and correct it
- If the current Excel file already has an Addin and you get an error while including a new Addin the check for the same issues - Function Name, Sub routine Name, Project Name, Variable names etc
Now that you have included the Addin, how to make it working -
In the current Excel sheet - goto the Visual Basic Window and double click on "ThisWorkbook" and enter the below code -
'''' Below Code Courtesy http://www.brainbell.com/tutorials/ms-office/excel/Distribute_Macros.htm
Option Explicit
Dim cControl As CommandBarButton
Private Sub Workbook_AddinInstall()
On Error Resume Next
Call AddMenus
On Error GoTo 0
End Sub
Private Sub Workbook_AddinUninstall()
On Error Resume Next 'In case it has already gone.
Call DeleteMenu
On Error GoTo 0
End Sub
Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl
'========= use the below code to create Addin Drop down button
'(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("&Drop down Name").Delete
'(2)Set a CommandBar variable to the 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 it.
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 = "&Drop down Name"
'(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 = "DD List 1"
.OnAction = "DD list1_macro" ''' this macro name should be the same in the addin
End With
'(6a)Add another sub control and give it a caption _
'and tell it which macro to run (OnAction)
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "DD List 2"
.OnAction = "DD List2_macro" ''' this macro name should be the same in the Addin
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
'======== use the below code just to create just one addin button
' Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)
' Give the control a caption
' cbcCutomMenu.Caption = "Ne&xt 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("&Drop down Name").Delete
Application.CommandBars("Worksheet Menu Bar").Controls("DD List 1").Delete
Application.CommandBars("Worksheet Menu Bar").Controls("DD List 2").Delete
On Error GoTo 0
End Sub
Now that the code is ready how to execute it !!
Create a Console Sheet and Add Two Command Buttons-
1st Button - Name "Delete Addin" - Call the macro "DeleteMenu()"
2nd Button - Name "Include Addin" - Call the macro "AddMenus()"
Procedure to use the Addin
When you first click on Include Addin Button, Excel would create a Drop down / buttons in Addin Tab
When you click on Delete Addin Button, Excel would remove the Drop down / buttons in Addin Tab
So that you have created Addin, Include the Addin, and created buttons in Addin - how to execute the Addin
Its just simple now, you just got to click on the buttons created in the Addin Tab to run the appropriate macro
Voila… it works !!!
