maandag 13 augustus 2012

An all in one Macro - Excel Macro beginner

If you have to use excel a lot to create graphs and figures chances are that you have thought to yourself: ‘There has to be a way to automate this…’. And if you’re like me and know your way around Microsoft Office, you have the developer tab enabled and you’ve seen that button record Macro.image
“Ha! my tiresome repetitive tasks are over!” you think to yourself, but if you’re not used to programming in VBA, watch out! here is a pitfall I ran into so you don’t have to:

Trying to do everything in one Macro:

Macro’s so powerful, they can do everything you want. The problem is they need to be told exactly what to do and as a beginner you don’t know the language. (Try asking for directions in French for example and see if you understand the answer). So you have all this power under your fingertips, but no way to (quickly) use it. What I learned as a superficial Macro user is not to try everything in 1 Macro.
For example I had 10 sheets of raw data formatted exactly the same but needed to be set up differently and I wanted a graph plotted of each sheet. Trying to do that all at once resulted in a graph I couldn’t use and a Macro that was so specific it couldn’t be used for other sheets unless I did some serious tweaking. Which I did have the time nor experience for. So I went for the next best thing:
  1. Record a Macro of moving the raw data in the correct format
  2. Create a graph manually
  3. Record a Macro of adding all the titles and getting the correct formatting
This took me 5 minutes to set up and saved me 20. While trying to figure out how VBA took me 2 hours and it still doesn’t work.
So hope this helps you Macro beginners out there.