Where to store macros?

[Markus S]

Hi,

very simple question. When I create a macro that I want to use with a
lot of different files, where do I store it. I can store it in the
excel file I created it in or in another excel file, eg, the 'Personal
Macro Workbook'. But I only have access to it when I have the file in
which I stored it is open. Until now, I have simply always kept the
'Personal Macro Workbook' open, but it rather annoying that I have to
always minimize this workbook first whenever I open Excel or switch to
Excel from another app.

Is there a better solution?

Markus

 

[Bob Greenblatt]

Hi,

very simple question. When I create a macro that I want to use with a
lot of different files, where do I store it. I can store it in the
excel file I created it in or in another excel file, eg, the 'Personal
Macro Workbook'. But I only have access to it when I have the file in
which I stored it is open. Until now, I have simply always kept the
'Personal Macro Workbook' open, but it rather annoying that I have to
always minimize this workbook first whenever I open Excel or switch to
Excel from another app.

Is there a better solution?

Markus

The personal macro workbook is probably the best place. Why don't you add a
Workbook_open macro to the PMW to minimize ort hide the workbook?

 

[Markus S]

The personal macro workbook is probably the best place. Why don't you add a

Workbook_open macro to the PMW to minimize ort hide the workbook?

I only know how to record macros, can't really write them on my own
except for small edits. So my next questions would be:
- how do I create a 'minimize' macro? Trying to record it did not work.
- how do I assign a macro to be executed upon opening a file?

And also, if I close the last Excel file (except for the PMW) and
switch to another app and then switch back to Excel, this minimized PMW
will be un-minimized again. Would a Workbook_open macro also take care
of that?

But mabye if Excel does not provide for a central location to store
macros so that they are always accessible, I should not use macros for
my purpose but rather add-ins?

 

[Bob Greenblatt]

I only know how to record macros, can't really write them on my own
except for small edits. So my next questions would be:
- how do I create a 'minimize' macro? Trying to record it did not work.
- how do I assign a macro to be executed upon opening a file?

Open the personal macro workbook. Go to the visual Basic Editor (Option-F11)
or Tools-Macro-Visual Basic Editor. Double click on the ThisWorkbook object
to get a code page. Then paste in the following:
Private Sub Workbook_Open()
ThisWorkbook.Windows(1).Visible = False
End Sub

This will hide the PMW, but there will need to be another workbook window
(usually Workbook1) visible or the macro will fail.

And also, if I close the last Excel file (except for the PMW) and
switch to another app and then switch back to Excel, this minimized PMW
will be un-minimized again. Would a Workbook_open macro also take care
of that?

No, this is probably an artifact (and bug) where the visual basic editor
becomes active if it is open. You can close the VBE before you save the PMW,
then it should be OK. You can paste the same line of code into the window
activate event also. While in the VBE after pasting in the above code, pull
down the left dropdown to select Workbook, then pull down the right drop
down to select window activate, and type in or paste the middle line above.

But mabye if Excel does not provide for a central location to store
macros so that they are always accessible, I should not use macros for
my purpose but rather add-ins?

I'm not sure what your purpose is. The PMW is the best and simplest way to
make macros accessible. You can, also create an add-in, but you'll probably
find that that will be more trouble than it's worth.

 

[Markus S]

Open the personal macro workbook. Go to the visual Basic Editor (Option-F11)

or Tools-Macro-Visual Basic Editor. Double click on the ThisWorkbook object
to get a code page. Then paste in the following:
Private Sub Workbook_Open()
ThisWorkbook.Windows(1).Visible = False
End Sub

This will hide the PMW, but there will need to be another workbook window
(usually Workbook1) visible or the macro will fail.

Works perfectly, thanks a lot. The PMW stays hidden (even better than
minimized) whatever I do. I couldn't ask for more.

Now why is this not the default behaviour of the PMW? What would be the
advantages of constantly having a blank workbook labelled PMW arround?