<

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

Published on
6,163 Points
2,563 Views
1 Endorsement
Last Modified:
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
Comment
Author:Wilder1626
  • 3
  • 2
5 Comments

Expert Comment

by:mikovitz
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?
0
LVL 11

Author Comment

by:Wilder1626
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?
0

Expert Comment

by:mikovitz
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.
0
LVL 11

Author Comment

by:Wilder1626
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.
0
LVL 11

Author Comment

by:Wilder1626
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.
0

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Join & Write a Comment

The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month