Maybe for the first time, you’ve now tried to record a macro. What’s happened is that all the steps you took in Excel have been recorded as VBA computer code. Let’s take a look at the code.
Viewing macro code
View your macro code
Once you know how, it’s easy to see the code you’ve created. On the developer tab, click on “Macros”. The macro you’ve created should pop up on the list. Click on “Edit”.
A new box should pop up – it should look a little bit similar to the screen shot below. You’ve just opened up the world of VBA programming! From here you can modify your recorded code, add notes for another user, add pieces of code you’ve sourced from elsewhere or (once you get good at this) type in code directly.
Turning on the macro project explorer
We’d always recommend that you turn on the project explorer by clicking this button:
Your macro has been stored as Module 1. Well done: you did that!
Macros: what next?
Next we’re going to:
- Learn how to play a macro manually – so you can check your macro’s working OK
- Run a macro automatically: so that you can play the macro back super fast e.g. from a button and save yourself time in the future.
Help! I'm stuck
Help I’m lost!
If you’ve become a bit lost by all this, here are a couple of tips for you:
- Go back a step to where we showed you how to record a macro and have another try at doing that
- Copy our code below (it’s not the nicest code the world has seen, but it will do for the exercise) following these instructions on how to copy a macro.
Sub Format_macro () ' format_macro Macro ' Application.WindowState = xlMinimized Application.WindowState = xlNormal ActiveCell.Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent5 .TintAndShade = 0.399975585192419 .PatternTintAndShade = 0 End With Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With With Selection.Font .Name = "Arial" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Selection.Font.Italic = True Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone ActiveCell.Select End Sub
About this online financial modelling course material
You’re looking at a free online financial modelling course extract covering macros.