Excel VBA Development. In Shropshire!

Excel in ShropshireVBA (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:
twittergoogle_pluslinkedintwittergoogle_pluslinkedin

Thanks Microsoft, I Had to Seek Help Because of You

I had a little tussle with Excel. Just what you need on a chilly Monday morning when you have stacks to do.

I learned (again) that if you want to know how to use Excel, the “help” inside the program is not always the best place to look.

I think it’s fair to say that I am a power user of Excel. I’ve given Excel training courses up to advanced level, to audiences of several nationalities. No, really!

But sometimes, I find myself completely mystified, frustrated and downhearted.

Take a look at this:

Excel 2016 Help for Match Function Match_Type Argument

It’s the contextual help for the function called “MATCH”, specifically for the third argument of that function, called “Match_type”.

So Excel 2016 tells us that Match_type is a number, and must be set to 1, 0 or -1. Fine, I have no issue with that. But then Microsoft goes on to say that when we set 1, 0 or -1 we are “indicating which value to return”.

Well fan-bloomin-tastic!

So if I put “1” as the Match_type, what happens precisely? Who can say? If I put zero, then what? Microsoft isn’t telling us. And heaven forbid if I have the audacity to put “-1”. Perhaps the laptop will explode!

This is Excel version 2016. Excel has been around a long time. This function has been in Excel for a long time. How can it be, when this is such a prominent product, used by millions around the world, with a development budget that dwarfs the GDPs of several smaller countries, that they can miss basic things like this?

I ended up doing what I usually do, when frustrated by useless contextual help: I searched the internet for the answer, and quickly found it in the very first web page at the top of the results. I do this so often that it makes me wonder if Microsoft shouldn’t just remove all the built-in help and stop wasting people’s time with it.

Here’s what I found – just for your info in case you get stuck on this:

I found this useful Excel web page which has the explanation that Microsoft “forgot”:

http://www.exceltrick.com/formulas_macros/excel-match-function/

I quote:

match_type’ Match type is an important thing. It can have three values 1, 0 or -1.

  • If ‘match_type’ has a value 1, it means that match function will find a value that is less than or equal to ‘lookup_value’. It can only be applied if the array (‘range’) is sorted in an ascending order.
  • If ‘match_type’ has a value 0, it means that match function will find the first value that is equal to the ‘lookup_value’. In this case sorting of array (‘range’) is not important.
  • If ‘match_type’ has a value -1, it means that match function will find the smallest value that is greater than or equal to ‘lookup_value’. It can only be applied if the array (‘range’) is sorted in a descending order.

So now we know.

If by any chance you are puzzled by Excel or maybe you have a small business in Shropshire, Cheshire, Shrewsbury or Telford where spreadsheets are growing like Topsy and you need a data bloke to look at your processes and data and get it all under control, well, I’m here for you. Give me a call, please. It will be a welcome diversion from wrestling with Microsoft “help”!

Share:
twittergoogle_pluslinkedintwittergoogle_pluslinkedin