12. Macros: assigning a variable

Being able to record a macro means that you don’t have to really be a computer programmer to program Excel (brilliant). We are great fans of Excel’s macro recorder. But the code the recorder produces is not the simplest neatest neatest code known to man. Previously we looked at some ways to simplify your macro code by:

  • Going through the macro code line by line deleting the surplus lines produced by the recorder
  • Ramping up the power of your macros by adding loops. For example, when we looked at how to loop a macro we discovered that adding a bit of key looping code meant that we could set a macro working until it reaches the bottom of a list of data.

Assigning a macro variable

Assigning macro variables

We’re not knocking the macro recorder. We think it’s brilliant. You can use the recorder while you’re doing all the things you’re used to doing in Excel and then play them back later without having to know a programming language. It’s a brilliant concept. But it does result in some pretty ugly and potentially confusing computer code sometimes. Along with looping, assigning variables within your macro is another way to make your macro neater, simpler, shorter and more powerful. It takes a little bit of explanation though.

The looping example

Have another look at the example we created when we looked at macro loops. Do you remember what that example was all about? We wanted to perform the simplest manipulation we could imagine: adding +1 to all data points in column A. We got that one looping fine but the solution to adding 1 ended up a bit cumbersome. To perform the +1 we set the macro recorder running. And then we started working in Excel:

  • Creating a formula in cell B1 (=A1+1)
  • Copying cell B1
  • Pasting the contents of B1 into A1 (values only: “Home” tab, “Clipboard”, “Paste”, “Paste Special”, “Values”)
  • Deleting the contents of B1.

That’s the part the macro recorder helped us with. The clever part was adding the macro loop that would work to the bottom of any list. Maybe we managed to impress ourselves by doing that and the solution, as a whole, definitely worked. But if you think about what we went through when we were recording, in retrospect it seems like we came up with a pretty cumbersome solution to what we thought was the simplest operation imaginable: adding 1 to a number. You can see the resultant code where we were looking at macro loops. Assigning a macro variable can help us with simplifying all that.

What’s a macro variable?

Think of a variable as a temporary store area – a bit like a little bucket – a place to put or hold something while you do a bit of work on it. In the first example above, column B acted as our ‘bucket’. In column B we created a formula and then pasted it back into column A.

Making macro variables do the work: shortening the code

Shortening the code by assigning a variable

If you look at the code below you’ll see that the whole process has been shortened. We’re working with much simpler code now. In the example below we create a “bucket” or a variable called “x”. It’s just a place to do a bit of work. What’s the work we do in this case? In this case we just add one to the bucket. Here’s the code. It just adds one to the active cell – that’s all.


    Sub AddOneMacro()
    'This macro increases the value of the current/ active cell by 1
    'Select the active cell = the cell we're currently in
    ActiveCell.Select
    'Use VBA code to declare that x is a variable and that x must be an integer. "x" can be replaced by anything you like in this case - all the rest of the statement is VBA code. You could delete this line and the code would still work - Excel is clever enough to work out what's going on without it.
    Dim x As Integer
    'Make x equal to the ActiveCell
    x = ActiveCell
    'Add 1 to x
    x = x + 1
    'Output the result into the active cell
    ActiveCell = x
    End Sub

Reading the notes above, you will notice that our ‘bucket’ or variable “x” can be given any name you like. It’s all the rest that’s the VBA code. This code would work just as well as the code above (and might appeal most to Excel modellers without low self esteem problems):


    Sub AddOneMacro()
    'This macro increases the value of the active cell by 1
    'Select the active cell = the cell we're currently in
    ActiveCell.Select
    'Create a variable equal to the value of the ActiveCell
    IAbsolutelyRockIDoIReallyReallyDo = ActiveCell
    'Add 1 to the variable
    IAbsolutelyRockIDoIReallyReallyDo = IAbsolutelyRockIDoIReallyReallyDo + 1
    'Output the result into the active cell
    ActiveCell = IAbsolutelyRockIDoIReallyReallyDo
    End Sub

The above is designed just to illustrate that it’s up to us to create a name for the variable! Both versions do the same job.

Combining a variables with a macro loop

Combining the code with a loop

Here’s a version of the code where we’ve included a loop with the code. It’s one of the same macro loops we showed you previously. The loop is working down column A looking for any values and adding 1 to them all.


    Sub AddOneMacroWithLoop()
    'This macro increases the values in column A by 1
    'Select cell A1
    Range("A1").Select
    'Start loop, which later stops when an empty cell is reached.
    Do Until IsEmpty(ActiveCell)
    'Create a variable x, where x equals the current value of the active cell
    x = ActiveCell
    'Add 1 to x
    x = x + 1
    'Output the result into the active cell
    ActiveCell = x
    'Step down 1 row in column 1
    ActiveCell.Offset(1, 0).Select
    'End loop
    Loop
    ' Select cell A1, taking you back to the top left of the spreadsheet
    Range("A1").Select
    'Message box - just for fun - it can be deleted
    MsgBox ("I rock - I really really do (and I don't have many self esteem issues)")
    End Sub

It’s all an awful lot simpler and neater than some of the things we’ve seen working before (although those ones got the job done just fine too).

Getting the code to work

If you would like to get the code above running for yourself, please see our instructions on how to copy a macro.

Or, if you’re feeling brave or in a hurry, you can download a spreadsheet by clicking on the picture above (“Alt” “F11” will allow you to view the code once you’ve got the Excel macro variables spreadsheet open). This one links the macro to a button – remember we showed you how to run a macro automatically from a button?

Where are we at with macros?

We haven’t changed the world (yet) with our macros but we’ve made a good step forward in our understanding of the basics of VBA macro code. We’ve now learnt about:

  • The power of being able to record a macro and replay back anything we can do in Excel
  • How to loop a macro and work to the bottom of a list of data really quickly
  • Simplify macro code by deleting out extra lines created by the macro recorder.

Just now we’ve been looking really closely at how assigning variables (little storage buckets that provide us with space to do some work) can help us simplify our code further and potentially help us create more powerful macros. We’re definitely getting there! Next we look at:

  • How to perform operations on variables
  • Some of the different kinds of macro variable.

About this online financial modelling course material

You’re looking at a free online financial modelling course extract covering macros.

3 comments so far:

...join the conversation...

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

  1. Thanks very much to the Financial Training Associated, so I know a lot of macros, may also be useful to others who want to learn macros. Once again, thank you

    Reply
  2. I agree that the macro recorder is very useful, but leads to very inefficient code. Now that we have introduced loops, and are moving into variables, we are doing more of what I believe I need.

    Before we move away entirely from recorded macros, is it worth mentioning the merits of replacing cell references with range names to reduce the risk that a macro stops working because someone adds a row or column?

    Reply
  3. Good lesson Thanks Narayan

    Reply