Multi Tasking with Running VBA

A large amount of work I do is in excel, be it simple tables, pivot reports or VBA Macros. One hurdle I’ve been facing recently is how to overcome the problem created when excel is running a macro and you have other work to be done in excel.

Say your macro is used to run through a big loop and whilst you’ve improved it for speed and simplicity (see my Top Tips for VBA Run Speed), it is doing a big job and big jobs take time! This is often a brilliant excuse to go make yourself a cuppa and check up on the water cooler gossip. Regular breaks make you work better and stop you turning into a Square Eyed Zombie. However, I have cracked a way to work with excel whilst it’s working on your number crunching.

Super simple:

1. BEFORE you run your macro, open up a new window of Excel. This is not a new document, but a whole new window. Do this by: Right clicking on the excel icon, (which I’m sure you have pinned to your taskbar if you’re a geek like me) and click the icon marked “Microsoft Excel”. Check this has happened properly by trying to view two different documents in the two windows (you should have two unique sets of menus)

2. Run your macro in the first window

3. Work as normal in the second window!

These two windows aren’t linked so when your macro is finished you’ll have some limits when working with both windows (e.g. coping and pasting will only paste values and not formulas).

Advertisements

3 thoughts on “Multi Tasking with Running VBA

    1. Yes, there are some limitations, but it does at least give you the option for you to work in excel whilst you have code running! Have you found it too much of an issue to work with? Too limited? Any work arounds that you’ve managed?

      Like

      1. I have a fairly large collection of routines in my personal.xls file, so I tend to just avoid it. It’s frustrating to suddenly not have all of the shortcuts and tools you that you’re used to using. As for workarounds, you can always click the “Notify Me” button and they become available when you close the original instance. The REAL workaround is to create a full fledged *.xlam add-in for anything you may have in your personal.xls file. Add-ins are available in any instance.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s