How to make VB macros available to all Excel files ?

  • Thread starter Thread starter tmb
  • Start date Start date
T

tmb

I've found a macro to allow exporting of Quote/Comma delimited files.

And I have it working in my workbook.

How can I make this Macro appear as available in all Excel files and
workbooks without having to import it into each one?

thanks for any help.
 
Maybe Personal.xls which is a workbook that holds your global macros so's they
are available for all open workbooks?

Personal.xls is created the first time you record a Macro using Macro
Recorder.

Tools>Macro>Record New Macro. A dialog box will come up asking you name the
macro and where to place it. Pick Personal Macro Workbook from the dropdown.
Copy and paste a couple of cells then Stop Recording.

You now have a Personal.xls in your Office\XLSTART folder. You can go to
Visual Basic Editor(Alt+F11) to view the macro you just recorded in a Module.

You can add more macros by recording or by typing/copying them into the
Module.

You can do a File>Save from there or better yet hit ALT + Q to return to the
Excel window.

Then with Personal.xls active, hit Window>Hide.

When you close Excel you will be asked if you want to save Personal.xls. Yes!

It will open hidden next time you start Excel.

NOTE: when assigning macros to buttons or menu items you will have to precede
the macro name with Personal.xls.

i.e. Personal.xls!macroname

An alternative to Personal.xls is to create an add-in(*.xla) with your macros
in it and load it through Tools>Add-ins.

The benefit of this is that you don't have to precede the macro name with the
filename.

A disadvantage is that you will not see the macros in the Tools>Macro>Macros
dialog.


Gord Dibben Excel MVP
 
You need to move it to your PERSONAL.XLS workbook. If you have one
already, you can see it by doing a "Window, Unhide". It'll show up in
the box, click OK.
If you don't see one there, just record a macro. It will create your
PERSONAL.XLS workbook and you can unhide it as above.
Then you can copy your macro from the workbook it's in to PERSONAL.XLS,
which is always open and hidden, unless you unhide and/or close it.
 
Back
Top