As part of my new gig, I’m delivering excel classes to the wider business department. It’s amazing the differing levels of exposure some staff have to excel and data as a whole. Things as simple as Ctrl+C have passed some staff by, whilst other staff members are teaching me things. One of the simplest, yet palm-to-face inducing thing is “text to columns”. I wrote a macro to split out some columns data! (it’s the .split function kids)
The moral of the story – CLICK EVERY BUTTON, even the big red one.
It’s quite intuitive:
- Select your column and how you’d like to split the text – by fixed width that you specify or by delimiter.
- You can select multiple delimiters too.
- Then select a cell you want the data to end up in (careful not to overwrite your existing data!)
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.
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).
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.