April 1, 2015

Coding in Excel & PowerPoint VBA for a change

Two bits of work came my way in the last couple of weeks and both require some VBA hackery at some level or other.  And neither require Word VBA, so this was a nice break.

One’s a simple ‘can you add a file export button for a range of cells’ in Excel.  That’s today’s task.  Already have the code (near enough) in another spreadsheet, so that shouldn’t take too long to implement.

The other was a bit trickier to achieve as it was my own suggestion to create this PowerPoint add-in.  Although it ended up as a series of routines wrapped up in an add-in 🙂

The basic premise is that quite a few people in the company do presentations.  The presentations include (quite often) the same set of slides, so normally the slideshow creators will take an existing slideshow, make a copy of it, then edit the slideshow to remove the slides they no longer want.

I went about it the other way around.  I created a simple template that enforces the look and feel. I then added some code (learning on the hoof as it were) to insert blocks of slides from ‘building blocks’ – which are just sets of external PowerPoint files.  These ‘building blocks’ are stored somewhere safe, so that no-one can muck about with them. Saves any embarrassment.

The end user just clicks one of the nine available buttons on the new menu bar and the ‘building blocks’ are inserted at the cursor position.  If they want all nine, they just click the nine buttons.  That’s got to be easier than messing around with copy and pasting slides in and out of other PowerPoint files 🙂

I also added a quick ‘delete any empty slides’ and ‘enforce the company font’ routines. This ensures that each slideshow generated doesn’t fall out of the remit of the company branding guidelines.

The only ‘oddity’ was when I came to implement it as a proper add-in.  Unlike Word, you can’t just drop it into your %APPDIR%\Microsoft\Word\Startup folder and it’s automatically loaded.  You have to save it off as a separate plug-in file, then install it via the Add-Ins options under File > Options.

Hey ho, it’s all part of the learning curve 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: