I love writing Macro codes – it’s a guilty pleasure. But it can be a bit daunting, frustrating and soul destroying.
I learnt the skill of writing a macro code quite recently in my final year of Unviersity. I soon realised that Microsoft Excel wasn’t just formula’s and charts, and that macro-coding opened up the door into the Microsoft version of Narnia.
What is it?
According to Wikipedia, a macro is “is a rule or pattern that specifies how a certain input sequence (often a sequence of characters) should be mapped to a replacement output sequence (also often a sequence of characters) according to a defined procedure.” Simply, it is the background coding that allows you to write formulas, sub routines and procedures that can work at the click of a button.
How can it help my business?
You can think of it as a long-term investment for repetitive tasks. You put the hard work in to write this “function” that will save you time in the future. For example, before I started at Snowsport England, whenever there was a payment over the phone – roughly a dozen times a day – we recorded the payment on a spreadsheet in Excel, opened up a template in Microsoft Word, added the information to create a receipt, saved that as a PDF file, opened up Microsoft Outlook, typed out an email to the payee and then attached the newly created PDF receipt and hit send. Whilst it doesn’t sound like a long process – it’s a lot of copying & pasting and probably takes 5 minutes for each one. For a dozen payments – that’s an hour effectively ‘wasted’ each day, 5 hours a week and 10 days a year.
I wrote a code for a macro that took about half a day to put together and test, which stopped all the copying and pasting, and recognised that all the program’s used were Microsoft – meaning they can talk to each other. Now, we enter the details in the first spreadsheet, we enter a “Yes” in an extra column than before, click a button I’ve ‘frozen’ in the first line of the spreadsheet and enter the payee’s email address when prompted. It thinks for a moment whilst it talks to ther other programs, and then notifies me “Email Sent”. If I wanted to confirm it had been sent, I can check my “Sent” box on my Outlook and I’ll find an email to the payee telling her that her payment went through and she could find the receipt attached, which it was – in PDF format. But instead of taking 5 minutes, it took a few seconds (and a lot of frustration).
Is it easy?
Not at first – but before too long it gets a lot easier as you start to recognise patterns and develop your own tips to making it much more readable. Except for 12 lectures in the University module that introduced me to coding – all my Macro skills are self-taught. It’s a nice script software that is very much trial & error – which is something I enjoy, as it reminds me of the Math’s challenges I used to love at school: a technological puzzle to solve.
There’s plenty of forums where people post their problems and more experienced Excel-ers get back to them with a few options (or just explaining their errors in the code) Stack Overflow and The Spreadsheet Guru are two of my favourites, with the former being a community devoted to all sorts of programmers.
What are you limited to?
In my experience – nothing.
Sure – there’s a couple of things which seem tricky, but with some perseverence, research and trying a few angles, you can solve the problem and simplify some of your brands tasks.
Whenever I get stuck – I go into the forums and have a search for similar problems, that I can copy & paste snippets of the code (or just to help spot a different avenue to reach my goal). Nothing I’ve tried yet hasn’t had a successful outcome.
Want to know more?
If you’ve got a spreadsheet that you need tweaking or amplifying – send me an email (firstname.lastname@example.org) with your problem or how you’d like help and I’ll see what I can do to boost your brand!