Last week, I delivered an Introductory Excel course at a company in Maldon. I am also booked to go back there in the last week of June to deliver the Advanced course. While I was setting up, one of the delegates for the Advanced course popped in to discuss her requirements. Where possible, I do like to tailor my course material so that it is relevant to the delegates, making it more memorable, and with three weeks’ notice it seemed like a good idea to pinpoint anything they particularly wanted.
Part of the Advanced course looks at programming macros. My current exercise looks at the problems inherent in maintaining a bank balance workbook with a running total balance. However, in this firm, they have a particular need to do data file manipulation. Reports come into their system from an external database, and then need considerable manipulation to convert them into something that Excel can analyse. My delegate takes a whole afternoon to process this report, every week, as it runs to hundreds of rows, sometimes thousands.
I asked for a sample “before and after” set, anonymised with “Client 1”, “Client 2” etc replacing real names. I received the files (via my training agency) the same evening. I have now had a look at the spreadsheet and found that the processing is, as I had hoped, a fairly straightforward job, though very repetitive – an ideal candidate for a macro.
I shall be able to guide the delegates in creating a macro to delete an un-needed column, then locate the first data record, copy data into blank cells, and delete rows containing header information (which is repeated for each record in the source data). We will need to tweak the macro to deal with records containing different numbers of rows, and find out what happens when we reach the bottom, and deal with that. We will need a Do While… or Do Until… loop to handle the repetition, perhaps with a message box to say when the records have been processed. We can finish with a bit of further tidying up at the end, then move on to using pivot tables and charts for analysis, also in the course outline.
Developing the macro took me about an hour. Once created, it takes about 20 seconds to run: it would be a lot quicker if I turned off screen refresh, but I wanted to watch!
Normally, I would charge consultancy rates for macro creation, but this is such a common requirement that, in this case, I feel justified in calling it course development. It will come in very handy as an advanced macro exercise.
