JKP Application Development Services.

                    Microsoft Office Application Development

Preparations

Up • Preparations • Book or Addin • Menu's • Toolbars • Limit access • Protect code • Initialise • Save Settings • UI Languages • Setup • Conclusion
•  •

Search Zoeken

WWW
This site

Go to Ga Naar
Home
Up

Donate Donaties
Did you find something helpful on my site? Consider a donation!
Heeft u iets gevonden waar u wat aan had? Overweeg dan een donatie!

 

Creating an addin from an Excel macro.

Introduction 1 2 3 4 5 6 7 8 9 10 Conclusion

Preparing A Macro For Distribution

When one creates an Excel macro for personal use (which one wishes to be available to all open workbooks), the best place to store that macro is in a special workbook named personal.xls (or Personal.xlsb in Excel 2007). This workbook is generated automatically as soon as one records a macro and indicates that Excel should store it in the "Personal Macro Workbook", as shown in this figure:

Record macro dialog.

This workbook is a hidden workbook, saved in the XLSTART folder of Excel, from which Excel loads all files when it starts.

In Windows XP this folder may be located here:

C:\Documents and Settings\UserName\Application Data\Microsoft\Excel\XLSTART

But the folder can also be located in the path to Office (which will then be available to all users on the system). For Office XP:

C:\Program Files\Microsoft Office\Office10\XLSTART

If needed (and with the proper permissions), one can create this folder manually.

When one wants to distribute a macro to others, it is not a good idea to send them your personal.xls/personal.xlsb workbook. If your recipient has his own personal.xls/personal.xlsb and tries to open yours, Excel will generate an error message stating it cannot open two files with the same name. Alternatively, if the recipient copies your file to his own XLSTART folder, his own personal.xls/personal.xlsb gets overwritten, wiping out his own macros permanently.

The way to do this is to copy your macro to a fresh workbook. If the macro is contained in one ore more modules, this is a simple process:

bulletOpen a blank workbook (File, New).
bulletOpen the Visual Basic Editor (alt-F11).
bulletCopy the module(s), Class Module(s) and Userform(s) that contain your code by dragging their entry in the project explorer to the project of the new empty workbook (see below).

 

Copying a module in the VBE

As soon as all relating modules, forms and class modules have been copied the new workbook should be saved.

If you are using Excel 2007, make sure you select the proper file type in the save-as window. You need to use the xlsm filetype, otherwise if you use the default xlsx type, you're macros will NOT be saved with the workbook!

<<Previous        Next>>

 

    Subscribe in a reader

powered by longhead.com

Use the contact page to issue questions or comments about this website.
Copyright © 2003-2007 JKP Application Development Services.