Department of Technology

TECH 394 - Tech Computer Applications

Summer 2004

E-mail Me

Syllabus

Schedule

Handouts

Assignments

All My Webs

Dept Home

 

Add-Ins

As a favor to all my co-workers who need to convert from Degrees-Minutes-Seconds to Decimal Degrees and back, within an Excel spreadsheet, I've created a workbook called DD2DMS.xls.  It contains a module with 2 functions - DD2DMS and DMS2DD - which make the conversions.  I'd like to make these available to a number of people, but I'm not sure how to do it.  I can give them the file, but if they want to use my function, they need to have both my workbook and their current file open.  I'd prefer a better method...

Actually, there are a number of ways to share macros and functions.  This discussion will cover 3 - the XLSTART directory, your personal macro workbook, and Add-ins.

The picture below shows the structure of a typical hard drive - Excel and the other Office products are stored in folders beneath the Program Files folder.

When Excel starts, it looks for a sub-folder called Xlstart.  If it finds it, any workbooks in that directory are automatically opened as Excel opens.  Some of those files may be hidden, but the macro's and functions attached will still be available in other open workbooks. I could copy my file into my boss's Xlstart folder, but every time he started Excel, he'd see that file load and might wonder what's going on.  

As you record macro's, one option is to store the macro in your Personal  Macro Workbook.  Until you save a macro there, this file doesn't exist.  Record a macro, and a new file appears in the Xlstart folder - Personal.XLS.  This file is hidden, and no worksheets are visible.  From then on, as you start Excel, this file loads and you have access to the macro's within it.  You must use the Window-Unhide menu item to unhide the file in order to edit it, but you could add any macro's you create to the existing items in this file.  In my example, I might copy my two functions into my boss's Personal.XLS file to make them available to him.

The best alternative is to create an Add-in.  This is really just a worksheet file with several special features:

  1. No worksheet or chart files are visible.  Its really just a source for macros and functions.

  2. It's hidden from the user.  They won't see the file listed in the Window menu drop-down.

  3. It's file extension is *.xla.  The Add-in manager uses this extension to link files.

To create an Add-in:

  1. Develop and test your macro's so you're sure they work properly.

  2. With the worksheet containing your macro's still open, open a new worksheet, and attempt to use the macro's from that worksheet.  This tests whether your macro contains any code that's specific to the file you  created it in.  For example, if you renamed a worksheet to "Input", and the macro asks to switch to a sheet named "Input", the macro will crash on most other workbooks - they don't contain such a sheet.

  3. If you want to keep other from editing your code, lock the file.  You do this from the VB Editing screen. Click on your workbook's name in the projects window.  Then use the Tools, VBA Projects Properties menu to lock the file, and assign a password.  DON'T LOSE THIS PASSWORD!  Its almost impossible to override.

  4. Use the File-Save As menu item to create the add-in.  This is done from the Excel screen, and you much choose a file type that says Excel Add-in.  It's the last choice on the list.  It automatically adds the *.xla extension. You must also remember where you saved it, so you can copy it to another machine.

Having created the Add-in, you need to place it on your user's machine, and link their copy of Excel to it.

  1. Copy the Add-in file to a disk.  Make sure it's the one with the *.xla extension.

  2. Decide where it should reside on your user's machine.  If you put it in their Xlstart directory, it will be linked automatically, but they may not like that - they can't unlink it without moving or deleting the file. The default location for Add-ins is under the Windows folder, the Application Data subfolder, then the Microsoft subfolder, then the AddIns subfolder. You can chose to put it anywhere, as long as you remember the location.

  3. In Excel, use the Tools, Add-ins menu item to link your Add-in to their version of Excel.  You may need to browse to the place you stored the Add-in if you didn't use the default directory.