Thank you Microsoft

Microsoft yesterday released a whole bunch of updates to various things. For the first time ever, these updates have caused me no end of headaches.

I’m currently working on a database which uses ODBC storage, SQL Backend and Access front end. Aside from retrospectively finding out access is a bit of a pain and missing the wonderful functionality of excel nested formulas, it’s coming together nicely.

Up until now, my own mistakes in Macros and the delicate web of tables has been my biggest problem. But today I struggled for hours with a problem that didn’t exist last night only to find out that it was a data type issue in an odbc table. The default date changed from the British to the American, as a result my data wouldn’t save and my macros went through error handling. Thanks Microsoft, thanks.

In actual fact, I had no idea it was even Microsoft’s fault until I was moaning about the sheer futility of it to a friend who suggested it could be the updates. Yet another reason why having a good team around you and good communication is invaluable in problem solving.

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).

Top Speed Tips for VBA

Five top tips for improving your VBA speed

1. Write good code: Your script is only going to be as fast as it’s weekest link, create good habits by keeping your code neat, consice and your macro will run fast

2.Screen Updating: Turn off screen updating whilst you work – Added benifit that you wont have a flashing screen as documents are open and closed

3.Automatic Calculations: Turn off these and it’ll stop your formulas recalculating every single time you add to your workbook

4.Enable Events: This is a bit obscure, but it has saved more than one macro from the recycling bin. This is a more complex version of automatic calculations, which might cause you problems, but really improves speed

5. Active Workbooks: Stop activating workbooks, it’s not necessary and will slow your code down. Instead define your workbooks or ranges or what have you and reference the locations.