Excel VBA Macros : Answer To Challenge Question 1

The Challenge

In the last post (click here) we recorded a macro.

This macro took our basic report…..

and automatically formatted the basic report to a layout that we needed…..

However, when we added in an extra row and ran the macro, it didn’t give us what we might have expected…..

Your challenge was not to fix the problem, but just to understand why the macro didn’t give us what we expected?

The Solution

The solution is simple…… the Macro did exactly what we told it to do and not what we expected it to do!!

This is an old post that I use to demonstrate Excel logic, if you haven’t already read it, I would suggest reading it now.

Our macro completed 16 formatting steps that we recorded in the last blog post, including putting a border around our table and a total highlighted in yellow below the table in column D.

When we add more lines to our table and run the macro we might instantly think that the macro didn’t work – but it did!

To us, we want a border around our table and a total below it, our macro tell Excel to put a border around cells A1 to D11 and put a total in cell D12.  That is what Excel did.

Remember – a computer is powerful and intelligent, but it does what it is told to do and not what we meant to tell it to do…. you may  need a crystal ball for that!!

So, does this mean that our macro is really useful as long as our information doesn’t pass row 11 or column D?  Of course not!  To make the macro more flexible we would go into the code and change the code that was recorded and use variables.

Don’t worry about what variables are for now, the next step is to look at the code and see what it is doing.

The next blog post will do this.