Where to save macros

[Guest]

I have two questions really. First, I was told there was a specific place to
save a macro if I wanted it to be avalable whenever I opened excel. Where
would that be? Also, how do I create a macro that is always available when I
open excel instead of spreadsheet specific. Thanks for your help.

 

[Don Guillett]

If you do not already have a personal.xls >record a macro to personal macro
workbook. When you close excel after that creation you will be asked if you
want to save personal.xls >say yes. Now it will be available to you.
However this is a hidden workbook.

 

[Gord Dibben]

Rae

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

 

[Guest]

Don, could we take this one step further please. New computer and it's been
a long while since I've had to start from scratch.
When I try to record a macro, I get the message "Personal Macro Workbook in
the startup folder must stay open for recording". All I have in my XLSTART
folder is Personal.xls, that automatically opens when I enter excel, so it is
open. I click OK, hoping to record a macro to "create" the folder. I get is
"Unable to record". There is no file in Window - Unhide. Am I looking in the
wrong place? Elli