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“.
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.
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:
MsgBox ("Macros are cool")
- Note for this very simple message box you could leave out the brackets and it would still work:
MsgBox "Macros are cool"
- Here is an example that establishes “Variables” and combines an “Input box” with a message box to display a result:
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)
- 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:
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!")
'If condition is not met, display this message box
MsgBox ("Honestly, they're really great")
'End if statement
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:
- 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:
'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
'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
'Select cell A2
'Paste the copied cells as values only
'Turn off paste special
Application.CutCopyMode = False
- 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:
- 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.
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).