VBA = Very Beginner-like Abilities?

Last summer, my team manager asked for a new way to record productivity in the department; a new Time in Motion sheet or some such similar thing. We asked the department Excel-whizz to have a think, and he came up with a sheet containing drop-downs for the different work elements that was linked to another sheet which calculated the productivity. I was fairly impressed by it, but thought there was still room for improvement.

I pondered over it that weekend and thought to myself “wouldn’t it be great if all you had to do was click a button, input some basic information and then when you pressed submit it fed that information into a different sheet which would calculate productivity against a series of metrics”. This was like my pie-in-the-sky almost-best-case-scenario time in motion sheet. But not one to give up too easily, I started trawling the internet to find out if there was any way things like this could be done. Turns out, there’s this whole hidden world of possibilities behind Microsoft Office programmes called Visual Basic that I never knew existed! The more I read about, the more real my ideal scenario became.

I found part of the solution on a forum for Excel experts and started trying to build up this new time in motion sheet. It was quite easy to do some bits – create the button, programme in some actions, and get it to feed the information onto a blank sheet in a particular way. With quite a bit of help from a very kind forumite I got the solution to the rest of the puzzle and before I knew it, my pie-in-the-sky time in motion sheet was a reality. It worked beautifully. The sheet is incredibly user-friendly and takes minimal effort to complete – all the complicated calculation bit is done automatically, and I linked it to a ‘master sheet’ which aggregates all of the individual TIM sheets to report on team-wide productivity against the given metrics.

So the sheet works fantastically and I’m incredibly pleased with it. But the trouble is, even though I sort of know how it works, and I understand which bit affects which output, I don’t fully understand the detail of what I’ve written.

My situation reminds me of Searle’s Chinese Room Argument from my days as a philosophy student. The thought experiment goes like this: A man in a room has a box full of Chinese symbols along with a book of ‘rules’ associated with the symbols. People feed pieces of paper into the room with Chinese symbols written on them, and, using his rule book he works out the appropriate response. To the outside world, being given the appropriate response makes it seems as though the man inside the room understands Chinese, but he doesn’t really; to use an Office analogy, he’s just copying and pasting stuff from the rule book.

So my knowledge of VBA (this magical programming language behind Office) is like that – I can copy and paste from the rule book and I can grasp a couple of the basic phrases, but ultimately I don’t have any real in-depth understanding, and wouldn’t be able to write anything of my own from absolute scratch.

But I want to be able to. Now that I’ve discovered what Excel is really capable of, I want to exploit it. I use Excel quite a bit in my job, and there are a whole host of currently manual processes I’d love to automate. I want to be able to take as much of the grunt-work out of the daily reporting as possible, because let’s face it, who actually WANTS to spend over an hour every day tallying up figures from spreadsheets when it can be done automatically.

I’ve had a quick scoot about online and have found a couple of websites offering online courses in Visual Basic. One in particular only costs $30 but comes with a lot of practice examples for you to work through and direct access to a dedicated tutor via email so that seems reasonable. Having an understanding of VBA will probably help me in more ways than I’m currently imagining, and who knows, maybe it will lead to a greater interest in computer programming in general.

VBA-Success

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s