Macro

A “Macro” is a piece of computer “Code“. In Excel a macro stores a sequence of repetitive actions in Excel as computer code so you can automate them by playing them back/ running the computer code. Macros help you automate Excel. A macro is a piece of computer code – it’s a mini computer program that you create and run within Excel. This material is all about helping you get started with macros. Also see under “Excel VBA macro“.

Macro recorder

M stands for “Macro recorder”. Excel’s macro recorder takes note of all the steps you take in Excel storing them and making them available for later play back. Here we’ve set out to help you learn how to record a macro.

Message box

M is for “Message box”. A message box pops up displaying text or a result for the user. We first looked at simple message boxes on the course when we looked at dialog boxes.

    Here is an example of a simple message box:
    Sub MessageBox()
    MsgBox ("Macros are cool")
    End Sub
    Note for this very simple message box you could leave out the brackets and it would still work:
    Sub EvenSimplerMessageBox()
    MsgBox "Macros are cool"
    End Sub
    Here is an example that establishes “Variables” and combines an “Input box” with a message box to display a result:
    Sub InputAndMessageBoxCombined()
    'Establish variable
    Dim YourName As String
    'Use input box to collect the variable YourName
    YourName = InputBox("What's your name?")
    'Use message box to display the variable YourName
    MsgBox ("Your name is " & YourName)
    End Sub
    Message boxes can get more complicated than the examples you see above. Here is a more complex example that adds yes and no buttons, establishes “Variables” and uses an “If then else” statement.
    Here is the code for the yes/ no message box:
    Sub YesNoMessageBox()
    'Establish variable
    Dim Answer As Double
    'Create message box with yes/ no answer
    Answer = MsgBox("Do you think Macros are cool? ", vbYesNo)
    'Start If statement
    If Answer = vbYes Then
    'If condition is met, display message box
    MsgBox ("We agree: they're great!")
    Else
    'If condition is not met, display this message box
    MsgBox ("Honestly, they're really great")
    'End if statement
    End If
    End Sub

Method

M stands for “Method”. Macros do their work on “Objects” (have a look at our definition there). You can set the “Properties” of objects (see our definition there). We’re trying to make sure you understand Excel’s macro object-property-method structure. Once you’ve selected an object (say you’ve selected a cell in your spreadsheet) you can do all sorts of things to that object – that’s what a method is.

    It might be easier to illustrate what we’re talking about with an example. With the VBA editor open, type in “ActiveCell” followed by a full stop “.”. You’re creating code that’s starting by selecting the active cell as its object. Typing in the full stop is a really neat trick because it brings up a scrollable list of all the things you might like to do to the active cell object. You can set properties for the active cell – the properties are on the list with a little hand pointing to them.
    You can also make something happen to your active cell object. That’s what a method is. The methods get a little yellow-ish icon that’s whizzing through the air. If you scroll through the list you’ll see some things there that sound familiar if you’re a regular Excel user (like cut, copy) plus lots of other interesting sounding things that might be new. Don’t worry. It may be a new world we’re opening up for you but VBA help is a great tool for discovering and understanding new methods in the future. Top of the list you’d have to place the “Select” method so see our definition there. But what we’re going to do right now is copy the active cell.
    Type in the following code:
    ActiveCell.Copy
    That’s pretty much it on this one. If you’d wanted to copy the active cell the code we’ve just created does that. What you might want to do next is see what VBA help has to say about the copy method. It gives us a fuller example which defines a location in the first line. Then in line 2 the code copies the range of cells A1:D4 from that location. In line 3 the code pastes those cells to a new location. Reassuringly it’s a pretty handy example!
    As well as seeing what VBA help has to say about a method, the other thing you might want to do is use some code similar to Microsoft’s (or some of your own) to properly test the new line of code you’ve just created:
    Sub CopyMacro()
    'This macro places the value "X" in cell A1 and then copies it into cell A2
    'The code is designed to test that we have the copy method working
    'The critical line here is the one containing the code: ActiveCell.Copy
    'This first line selects cell A1
    Range("A1").Select
    'Assign the text value Y to the active cell
    ActiveCell.Value = "Y"
    'It's this next key line of code we're trying to test here: copy the active cell
    ActiveCell.Copy
    'Select cell A2
    Range("A2").Select
    'Paste the copied cells as values only
    ActiveCell.PasteSpecial (xlPasteValues)
    'Turn off paste special
    Application.CutCopyMode = False
    End Sub
    If you follow our instructions on how to copy a macro you should be able to satisfy yourself that you can get the code above and the copy method working. Hooray! Note, just like in Excel itself, with VBA macros there are always lots of different ways of solving the same problem. VBA help gives you one way (in fact at just three lines it gets close to winning the prize for the shortest neatest most elegant solution to the problem) but this code also gets you there:
    Sub CopyMacro()
    Range("A1").Copy Range("A2")
    End Sub
    If you’re interested, you could also solve the problem another way using a “Loop” and “Variables” – see our code examples under “Declare a variable” if you’d like to know how.
    We’ve tried to fully explore methods with you by working through an example of the copy method. Remember a method is something you can do to an object. Typing in the full stop after your object is the big trick here. Then you can see what methods are available to you and use VBA help to go from there.

Module

M also stands for “Module”. Excel stores macros in modules – that’s all we’re talking about with this one. You can see your modules when you open the project explorer in the VBA editor – we showed you how to do all that here: view macro code. A module contains macro code but it’s perfectly possible to have a macro inside a macro (you could call that one a sub-macro or a sub-routine or a sub-module – we wouldn’t mind).

Macros coverage from the financial modelling course

You’re looking at the macros glossary from our course material covering the use of macros in Excel financial modelling.

...join the conversation...

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