Using VBA macros in all workbooks

  • Thread starter Thread starter Ian Griffiths
  • Start date Start date
I

Ian Griffiths

I've created a VBA macro for Excel which does some formatting of a
spreadsheet I receive weekly at work, and saves a lot of time.
However, I want to be able to just use the macro of the spreadsheet
when it is mailed to me. I though of having the macro in a blank
spreadsheet in the background while opening the new spreasheet (this
is my current solution) but, to be honest, this seems like a bit of a
botched solution! Surely there is some way to just load a macro and
run it - I want this to be as simple as possible so I can instruction
other non-macro-savvy workmates to use the macro in my abscense.

Any ideas?

Cheers,
Ian Griffiths.
 
Ian,

Seems best to put the macros in an addin. You could also have the addin
create a commandbar top launch the macros.
 
Excel opens a blank workbook every time you start it, but it is hidden. The
name of the workbook is personal.xls and it can contain all of the "global"
macros that you write and want to use in any workbook that you open. I have
a few formatting macros in my personal macro folder.

Open the file that contains the macro, select Tools>Macro>Macros and select
Edit.
You should see your macro in the right hand window (Code Window).
Highlight the macro from "Sub Macro1()" to "End Sub" and copy. (if you
called it something other than Macro1, highlight that)
In the left window, there should be a Project Explorer window with a caption
"Project-VBAProject" and a tree of filenames with branches of worksheets and
modules. If you don't see that, press ctrl-R to open the Project Explorer
Window.
One project should be named "Personal". If it is not expanded, click the +
to expand the tree and click on Module1 to open it in the Code Window to the
right. If there is no module, right click the Project explorer window and
select Insert>Module.

Paste your Macro into the code window with Personal.Module1 open.

You can attach some bells and whistles, like a menu button or hot key to run
the macro. Give a holler if you need help.

When you close Excel, a warning will advise you that there have been changes
made to the personal macro folder, do you want to save the changes? Click
Yes. The macro will be available whenever you open Excel.

Making it an addin would work, but, like the blank worksheet or template, it
is a bit of overkill.
Any coworkers that need to use the macro will have to repeat the above steps
with their own personal macro folder.
 
Excel doesn't create the personal.xls until the user records a macro and
says to store it there. So it doesn't automatically exist. Also, any
workbook stored in the xlstart (startup) directory will be opened by excel
when it is opened manually with no suppression options. Any workbook could
be stored there as hidden and would provide similar functionality in terms
of the availability of macros.
 
Tom Ogilvy said:
Excel doesn't create the personal.xls until the user records a macro and
says to store it there. So it doesn't automatically exist. Also, any
workbook stored in the xlstart (startup) directory will be opened by excel
when it is opened manually with no suppression options. Any workbook could
be stored there as hidden and would provide similar functionality in terms
of the availability of macros.
Click to expand...

Do you think it would be better to place the workbook with the macro in the
xlstart directory, record a dummy macro like copy a1 paste a2 to create
personal.xls or do the addin as the others suggested?
 
for a single Utility type macro, being distributed to others, I would
probably use a workbook stored in the xlstart directory other than
personal.xls or if the users are VBA literate, send them the source code in
an email and let them paste it where they want. I see personal.xls as being
just that. Sharing personal.xls doesn't really appeal to me. For more
stringent requirements, an addin is probably advisable - but not if you want
to do Tools=>Macros=>Macro to run it.

It is really situationally dependent.
 
This is all very helpful guys, I'm going to try and create the
personal.xls tomorrow if I can, but keep the suggestions coming!

Cheers,
Ian.
 
Back
Top