MS Excel - How to create a default Add-in into Microsoft Excel

Wilder1626
CERTIFIED EXPERT
Published:
Hi all,

This is a brief aid on how to create a simple add-in into all new Workbook Excel files.

It may happen that you use a Module with a specific VBA macro in it that you wish you could include it as a default macro into all new Excel files you create. This article will guide you through the steps you will need to take to make it happen. Then you will be able to apply the same steps to your own modules or macros.

Step 1:
Open a new excel file. Press Alt+F11 to open the Visual Basic Editor, and then click Insert and select Module  as shown below. Then copy and paste this code:
Option Explicit
Function GetFormula(x As Range) As String
GetFormula = x.Formula
End Function

Open in new window

Insert-function-2.jpgStep 2:
You need to save this workbook as: Excel Add-In. Save the file with name as:  GetFormula. Make certain to save the file under your Documents in a place you will remember.
Save-as-excel-add-in.jpgStep 3:
Now it's time to add the new add-in GetFormula to all new Excel files where you will Include it as a default add-in.

Open a new Excel file, and go into File / Option / Add-Ins.  In the Manage field  select Excel Add-ins and then, click Go...
Excel-add-ins-selection.jpgStep 4:
Search for you  GetFormula Excel add ins file you created at Step 2. Follow the steps 1 through 3 shown in the picture below, and then click OK. Make sure to check the GetFormula  before you click OK.
find-add-in-file.jpg
Step 5:
Close Excel without saving the file and then re-open the program. Press Alt+F11 to open the Visual Basic Editor, and see if your module is showing your version of  GetFormula .
Excel-file-with-default-add-in.jpg
You have now made  GetFormula be a default module to all Excel files.

You can always remove it if you don't want your add-in anymore.  Just go back to step 4 and uncheck  GetFormula. This will remove it from the default Add-ins.

You can now make your own Excel add-ins!!!  Enjoy.
 
1
4,077 Views
Wilder1626
CERTIFIED EXPERT

Comments (5)

Commented:
I am trying to make the workbook have default settings: gridlines, column headings, two decimal places; headers etc
automatically.
all of the above can be done in the program but I would like it to boot up showing most of the above.
Will this add-in do that?
CERTIFIED EXPERT

Author

Commented:
Hi mikovitz

Are you saying that you would like to open new excel files with a specific excel template having grid lines, headings etc? what would happen if the person wants to open a Blank workbook with no formats in it?

I'm  a little surprised as i have never seen this where it would always default to a template. Excel is used for so many reasons, and never the same.

But maybe i did not understand you question correctly. Can you give me more information?

Commented:
yes I am saying that I want to set up my template as opposed to what comes from excel for printing purposes. in other words when I go to print, I press a key and the sheet:
1. sets up gridlines and column letters;
2. converts numbers to 2 decimal places;
3 goes to Edit Header so I can insert a header or: automatically set up a header that shows date and time and filename on left header and in center the tab name on the bottom of the sheet.
CERTIFIED EXPERT

Author

Commented:
I have never tested it. But i think that you can build an Add-in that applies multiple settings and then, you would just need to call that function manually if you create a new sheet.

I don't think we can add this to automatically applies to all new excel files without call the add-in macro.
CERTIFIED EXPERT

Author

Commented:
Another option could be to create a master VBA file with all your default setting, and when you open your file, it will automatically prompt you to save the file as a new VBA file with all the macros inside.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community