Security settings

We’ve tried to give you a few warnings about macros, including a warning about how you might want to copy a macro you’ve found somewhere else. If you’re going to play around with this stuff you’ll need to tailor Excel’s macro security settings to suit you and anyone else that might want to make use of what you’ve created.

Select

S stands for “Select”. You’ll find yourself busy selecting things within/ parts of Excel (“Objects“) that your macro will do work on. Examples include a cell, the cell you’re currently in (the active cell) or a range of cells. Here are some code examples:

    Select the active cell (= the cell where your cursor is placed in Excel – your macro will do its work on this cell):
    ActiveCell.Select
    You could create this code by setting the macro recorder to run, selecting “Relative references”, and clicking on any cell in the spreadsheet.
    Select cell A1:
    Range("A1").Select
    You can create this code with the macro recorder, NOT depressing the “Use Relative References” button and clicking on cell A1.
    Select a range of cells e.g. A1:A5:
    Range("A1:A5").Select
    Create this code by using the macro recorder, not hitting the relative references button and selecting cells A1 through A5 in your Excel spreadsheet.
    Select to the bottom of a column of data in Excel:
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select

    To create this run the macro recorder with relative references off, select cell A1 and make use of one of the regular Excel Excel shortcuts for selecting to the bottom of a column of cells in a spreadsheet (“Ctrl” “Shift” followed by the down arrow on your keyboard).
    This simplified code would also do the same job for you:
    Range("A1", Range("A1").End(xlDown)).Select

Sub

S stands for “Sub”. Like a ship but one that sails under the sea. Or a sandwich you might like to eat. See under M for “Module“.

Syntax

S stands for “Syntax”. English (or any other language) becomes pretty impossible to understand unless you’ve got the grammar right. We’re talking about the same thing here but for the VBA computer language. Unless you get all the components right, and in the right order, Excel is unlikely to be able to understand your computer code. That’s what syntax is all about – making the right statements in the right order so Excel can understand and run your code. Understanding the word syntax is probably not the most important here as long as you understand that any computer code you end up typing yourself has to be exactly right to work. The “Macro recorder” will do the hard work for you to start. To get it all right when you move from recording to typing directly in the “VBA editor“, “VBA help” is going to become your BFF (best friend forever). VBA help tells you how to get the syntax right, including the correct “Arguments” to use.

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 *