How to create an Add-On or Add-In into EXCEL application!

Excel-AddOn

Products or services sold by dealerships that can be added onto the EXCEL application.

Simple standard tool bar


Excel-AddOn (Sample)


What is Excel-AddOn used for?

There are various purposes to install "Excel-AddOn" onto your EXCEL application. It depends on your works or your Excel usage purpose. Such as new functions which have no in the standard function of the Excel application, an automatic procedure which can make your excel work more faster or easier by some automatic functions, anything that you can imagine and create with the EXCEL application!

For more understanding, you can download "FindSheet.xla" for starting to make more understanding of "Excel-AddOn"

Sample:

www.excel-addon.FindSheet.dl

Step 1
Create your command bar with the button(s)
For sample; there are 4 buttons on one command bar.

Excel-AddOn (Sample)

Step1.1

With a new excel workbook, open "Microsoft Visual Basic" by pressing "Alt+F11"

Step1.2

Insert "Module" by right click > Insert > Module (See the below picture)

Step1.3

Double click on the module inserted then copy the below code into the module for generating a command bar with a button.

Sub crtCommandBar()

Dim customBar As CommandBar
Dim newButton1 As CommandBarButton
Dim newButton2 As CommandBarButton
Dim newButton3 As CommandBarButton
Dim newButton4 As CommandBarButton

'Check existing of "Excel-AddOn" command bar
For Each bar In Application.CommandBars
If bar.Name = "Excel-AddOn" Then GoTo lNewB
Next

'Add new command bar "Excel-AddOn"
Set customBar = CommandBars.Add(Name:="Excel-AddOn", Position:=msoBarTop, Temporary:=False)

customBar.Visible = True

lNewB:
'Check existing of "Excel-AddOn" button
For Each btn In Application.CommandBars("Excel-AddOn").Controls
If btn.Caption = "BUTTON NAME"Then Exit Sub
Next

'Add a new button in "Excel-AddOn" command bar
Set newButton1 = CommandBars("Excel-AddOn").Controls.Add(Type:=msoControlButton)
Set newButton2 = CommandBars("Excel-AddOn").Controls.Add(Type:=msoControlButton)
Set newButton3 = CommandBars("Excel-AddOn").Controls.Add(Type:=msoControlButton)
Set newButton4 = CommandBars("Excel-AddOn").Controls.Add(Type:=msoControlButton)

With newButton1
.BeginGroup = True
.Caption = "BUTTON NAME"
.FaceId = 9662
.OnAction = "MyExcelAddOn.xla!myfunction"
End With

With newButton2
.FaceId = 481
.HyperlinkType = msoCommandBarButtonHyperlinkOpen
.TooltipText = "www.squidoo.com/Yodarchy_Excel"
End With

With newButton3
.FaceId = 482
.HyperlinkType = msoCommandBarButtonHyperlinkOpen
.TooltipText = "www.squidoo.com/Excel-AddOn"
End With

With newButton4
.FaceId = 483
.HyperlinkType = msoCommandBarButtonHyperlinkOpen
.TooltipText = "www.squidoo.com/groups/Excel-AddOn"
End With

End Sub

Note: Gray texts are just for the example.

Step1.4

Adapt the code on the red text to match on yours.

"BUTTON NAME" - Put any button names which you want, it's just a name. (Note. 2 places in the code for the same name)

.FaceId = 9662 - Put any FaceId indexes, you can visit Excel Tips & Trick for more information.

"MyExcelAddOn.xla!myfunction" - MyExcelAddOn.xla is the file name for this current file which will be saved (Please see more in Step1.7). "myfunction" is the sub name which will be created by you (Please see for an example in Step1.5)

Step1.5

Create your own function or procedure or tools. This is the part which is depended on your creative thinking. The below code is just an example to guide you to get an idea.
Put the below code into the same module as before (Next from the before code)

Sub myfunction() 'For deleting "Excel-AddOn" command bar

Dim customBar As CommandBar

For Each bar In Application.CommandBars
If bar.Name = "Excel-AddOn" Then bar.Delete
Next

End Sub

Step1.6

Call crtCommandBar in Sub Workbook_Open like the below picture.

Step1.7 (Last step)

Save as . . . your file to be .xla file by choosing "Save as type:" as "Microsoft Office Excel Add-In (*.xla)", in this example, name is "MyExcelAddOn" into your located place on your hard disk.

Congratulation!!! Now you can have your own Excel-AddOn. Just double click on your xla file to see my sample or you can adapt your own function by yourself!

Enjoy your tool!

Step 2
Assign your Excel-AddOn to run every time when the Excel application is started.

Copy your xla file into "%userprofile%\Application Data\Microsoft\Excel\XLSTART" like in the below picture.


After copying, restart the Excel application. That's it!