VBA (the language of the Microsoft Excel macro) may feel like a bit of a blast from the past, but in a current assignment I’ve found a valid, up-to-the-minute and very cost-effective use for it, saving a local charity some thousands of pounds.
I’ve just spent a very interesting couple of weeks developing in VBA here in Shropshire, within a spreadsheet that will be used to perform a very interesting and meaty wages calculation.
For those of you who don’t know, VBA is what Excel macros are written in, and it’s a flavour of Microsoft’s Visual Basic programming language.
The view these days in some quarters is that Excel macros shouldn’t be touched with a barge pole, but I can tell you now, that in this particular case, they were the key to a very sound solution. Data had to be moved from a highly proprietary staff time logging system and into a payroll system (Sage Payroll) to enable wages to be calculated. This has been done manually but was becoming an impracticably large task as the organisation grows.
But initial attempts at automation hit a brick wall: The only way to get data out of the source system in a suitable format was to shell out literally thousands of pounds to the software supplier for a new bespoke report. The alternative was to somehow wring the data out of the unsuitable report that came with the system, a report, incidentally, that surrounds the useful data with a load of unusable print formatting – headings, subtotals, horizontal lines, footers, page numbers – you name it! The charity could not justify the massive expenditure of a bespoke report, so asked me if I could do anything with the report provided, as it stood.
Well, who would have thought it, here in 2015, but the most cost-effective, practical and easy-to-use solution turned out to be an Excel spreadsheet. Well, not your ordinary spreadsheet you understand, but a spreadsheet backed by several thousand lines of rather slick and efficient programming in VBA (even if I do say so myself!)
So now, data comes straight out of the proprietary system in its standard report, slots straight into Excel and at the push of an on-screen button is transformed, in a mighty complex but behind-the-scenes way, into a lovely clean format perfect for Sage Payroll. It all works a treat and just goes to show that sometimes we have to think a little laterally when deciding what tools to use for the job!Share: