Excel: convert Postcode in x,y grid co-ordinates

This is a cracking blast from the past, I was digging through my old personal macro excel backups when I came across this beauty.

It dates back to my recycling plant development days, I had to devise an algorithm to work out the most economical site placements for food waste recycling facilities for a given highly dispersion of waste arisings.

The data I had to work with was in the form of a list of postcode locations along with annual waste tonnages. I had originally intended to lookup the addresses against my longitude/latitude list however turns out the maths to convert long/lat into something usable in excel was rather complex.

I've since worked this long/lat conversion out and have been meaning to write it up into a blog post but it is frankly pretty boring even by my standards.

Screen scraper

So back then my next angle of attack was to use a screen scraper - a bit of code I could fire the postcode off into the interweb and get back the result I needed. I already knew the site I was going to leverage, Streetmap.co.uk, as I'd been using it for a while for all things MTB as it was it was the only service at the time where you could view Ordnance Survey maps.

The code

Paste this code into your VBA Project:

Then all you need to do is place you cursor in the cell next to the postcode you want to look up and run the macro.

If you have a list of postcodes the code will happily rattle down the list for you.

Uses

But what use is it to me I hear you ask? Plenty, I wasn't expecting you to need a recycling plant location algorythm, it has lots of uses:

• with a bit of Pythagoras you can determine the distances between two locations
• You can use it to plot locations (on a graph)
• (well two, I'm sue there must be more!)