3. Viewing macro code

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.

19 comments so far:

...join the conversation...

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

  1. Looking for more new lessons

    Reply
  2. Instructions remain admirably clear. Might help if at the end of stage 2 there was a recommendation to save the file, so that it will be available for stage 3.

    Reply
  3. Very Gud Lesson

    Reply
  4. CAN WAIT TO BECOME AN EXPECT

    Reply
  5. smart lesson

    Reply
  6. Good so far but got stuck checking for the macro project explorer!

    Reply
    • Lee one of our trainers will email you to see whether he can sort this out for you

      Reply
  7. Thanks you so much.
    I got all of the 20 materials.

    Reply
  8. Yes,
    Its really help me out.

    Reply
  9. Thank u.

    Reply
  10. Thanks so much for the lesson

    Reply
  11. thanks for this. Need the next course.

    Reply
    • Hazem if you’re in a hurry for more macro lessons you should be able to find a link at the bottom of the last email you received. There’s a link there. If you follow it you can get all the Excel macro lessons at once. You don’t have to wait.

      Reply
  12. Good learning!

    Reply
  13. thanks for this. Need the next course.

    Reply
    • Watch out for the next lesson arriving in your inbox soon!

      Reply
  14. thanks a lot I got all of the materials .

    Reply
  15. Thanks you so much. I have learned.

    Reply
    • Thanks for the feedback! Please keep it coming!

      Reply