Where to store macros?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Markus S - 27 Jul 2007 16:05 GMT

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

Reply to this Message

Bob Greenblatt - 27 Jul 2007 16:10 GMT

On 7/27/07 11:05 AM, in article 46aa09b6@news1-rz-ap.ethz.ch, "Markus S"
<youcontrol@hispeed.ch> wrote:

> Hi,
>
[quoted text clipped - 10 lines]
>
> 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?

Signature

Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

Reply to this Message

Markus S - 27 Jul 2007 17:39 GMT

> 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?

Reply to this Message

Bob Greenblatt - 27 Jul 2007 19:27 GMT

On 7/27/07 12:39 PM, in article 46aa1fdc@news1-rz-ap.ethz.ch, "Markus S"
<youcontrol@hispeed.ch> wrote:

>> 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?
[quoted text clipped - 3 lines]
> - 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.

Signature

Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

Reply to this Message

Markus S - 27 Jul 2007 20:13 GMT

> 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
[quoted text clipped - 5 lines]
> 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?

Reply to this Message

Mac Forum / Applications / Excel / July 2007

Where to store macros?