Micro Mass, Body Mass and Macros.....

Well, the 4th of July 2012 was significant. Not only did the United States' celebrate their 235th day of Independance, but the good scientists in CERN announced that they are pretty much certain they have discovered the Higgs Boson. The "what?", you may splutter into your coffee. I am certainly no expert in particle physics, so here is an easy explanation. In short, it is the tiny little particle that gives everything mass.

This led me to think about my own body mass (not something I like contemplating for very long!), and the fact that I hate having to go online to use a BMI calculator, when it would be really easy to create my own, and track my progress. Which then led me to thinking about Macros. And how they can make life easy. And how they can also make life very very frustrating.

So I am going to show you how to record a VERY simple macro. This one is really very simple. But it will give you an idea of whether there is anything you could speed up if you just recorded a simple little macro to save you from doing repetitive and boring tasks.

However, it comes with a warning. It is my experience that if you want to play around with Macros, and experiment with improving your work life, you want to have someone close by who knows quite a bit about Macros. Because they are finicky little blighters, and they can come up with errors that are as elusive as the Higgs Boson itself. That said, don't let me put you off, because they can save time.

Another thing to note is that you can of course write Macros in VBA from scratch. You don't need to record them at all. Its just easier, when you are starting out, to record them.

So, the first step in recording a good Macro is planning. Make sure you have everything you need set up before you press record. So I have created a (very fictitious) set of numbers for our test subject, Fred. Fred is 1.85 metres tall and got a lecture in December from his doctor for having a BMI of 36.52, which is classified as clinically obese. Fred's New Year's resolution was to get his BMI down to 28 by the end of that year. Looking at our data, you can see he is doing well so far:

So it is now June, and Fred has dropped down to a BMI of 33.02. The Macro we are going to record is simply going to copy the BMI each month when he recalculates it, and add it to the graph data, to update the graph. Like I said, really simple. You may want to try replicate this to see if you can get the Macro to work. If you want to, the formula for the BMI is: =C6/(C5*C5)

I have the graph data set up on the same sheet, like this:

 So we are nearly ready. We just need to insert Fred's weight for July into cell C6. Lets say he is now weighing 110kgs, which gives him a BMI of 32.14.

Now, lets record. I'll show you how to start recording, and then talk you through each step. So go to the Developer tab on your menu ribbon, and click on Record Macro. You will get a box like this:

Choose a name for your Macro, but note that it won't accept spaces in names. You can also assign a shortcut key of your choice. I also use the letter in capitals as it is less likely to already be assigned a shortcut role.

If your Macro is specific to a particular workbook, then choose the option shown above. However, if it is something you would like to use in all your spreadsheets, store it in Personal Macro Workbooks, and it will be available whenever you open Excel.

Also note that Macro needs to be saved in a Macro Enabled Workbook, so make sure that is what your workbook is saved as.

Click Ok and remember that everything you do now is recorded. You will get a little square in the bottom left hand corner of your workbook which you need to press to stop recording the macro.

Then you just do the following:

Click on Cell E6, and press Ctrl C to copy
Click on Cell N3, and press Ctrl + down arrow (this should take you to June's BMI figure)
Click on the down arrow once more (to take you to July's space) and Paste Special - Values
Press Esc
Press Ctrl S to save.
Click on the little square at the bottom left hand side of your spreadsheet, or go to Developer, Stop recording, and you're done. Your table data is updated, your graph is updated. Life is good.

This looks easy, no? Well, hate to break it to you, but there is already an issue with this macro. If you go to your developer tab, click on Macro, select your Macro and click the Edit button, this screen will appear:

This is your VBA code. Cool, isn't it? See that line I have highlighted? That's the problem. Because when you select Cell N3, and then press Ctrl + Down Arrow, it takes you to the last cell with data in it. You then press down once more, and the Macro recording selects the absolute cell reference of N10. Which is fine for July, but in August, when you run the Macro, you will find it saves the BMI into the July cell. How do we fix this?

We change the code to say "Go to N3, then go to the last cell with data in it, then go one row down." How? Easy - instead of having the code as highlighted above, you replace that one line with :

ActiveCell.Offset(1,0).Select

This will ensure you always paste in the next empty cell in column N. The offset function is great for Relative referencing, as you can move around by choosing how many rows and columns to move by, instead of selecting a specific cell.

Whenever you make changes to your code, always remember to press the 'Reset' button. Its the little square highlighted below:

Then you can close out your VBA screen and you should be ready to run your Macro. How do you do this? Either by pressing Ctrl + whichever shortcut key you selected, or go to Developer-Macro-Run Macro. Your Macro will now add in the new data every month, and the graph will update automatically.

Another way to do this is to assign a button to a Macro. My newsletter this week will describe how to do this. Not subscribed yet? Do so now, here.

If you want to play around with Macros in your own time, I can recommend purchasing a textbook to help you through the minefield.My favourite author on all Excel related matters is John Walkenbach, but there are loads out there.

Enjoy, and hope I haven't scared you off Macros for life. They are great little timesavers, once you get over the set up!

Happy Spreadsheeting!