Personal macro workbook

If you want to make a macro available to all worksheets in Excel that’s where you’ll need to store your macro.

Project explorer

P also stands for “Project explorer”. See under M for “Module“. If you open the project explorer you’ll be able to see the overall structure of your code, including any modules you’ve created. We first showed you how to open the project explorer when we showed you how to view macro code.

Properties

P also stands for “Properties”. Excel does its work on “Objects” (make sure you have had a look at our definition for that one). You can set the properties of objects. Properties are like settings for objects.

    An example might help. With the VBA editor open type in “ActiveCell” followed by a full stop “.” and then the letters “fo”. That is, in the VBA editor type in “ActiveCell.fo” – something should pop up that looks like this:

    We’re about to set the font property for the active cell (the active cell is our object). Typing in the full stop is a really neat trick because the scrollable list that pops up shows you the full list of things you can do to the active cell. Note anything that’s a property for the active cell has its own icon (the icon looks like a list with a hand pointing to it). Plough on until you’ve typed in “ActiveCell.Font.Bold”. Note here we’ve got a property (bold) within a property (font) here. That’s OK – it’s fine.

    If you try and type in a full stop after the end of “bold” nothing will pop up. That may be kind of depressing. It means we’re at the end of the road. We’ve gone as deep into properties as we can and we have to assign a value to Bold. This is what you need to make sure you’ve got typed in now:

    ActiveCell.Font.Bold = True

    We’ve just set the font property of the active cell to bold. Actually, strictly speaking we’ve assigned a value of true to the bold property within the font property of the active cell object. There’s a properties hierarchy at work here (you’ll find that, if you read our definition for “Objects“, there’s an object hierarchy also).

    How did we know how to do that? The answer: experience. Prior to that, reading “VBA help” (remember it’s your new best friend right?). Here’s an extract from VBA help that would have helped us get the above example working:

    Note the hierarchy at work in Microsoft’s example. Their code is targeting a range object within a worksheet object (an object within an object) and assigning a value to the bold property within the font property (a property within a property).

    Ours is a simple example but it illustrates how you go about building some of your own code: selecting objects, then properties and assigning values to those properties using the guidance you’ve got in VBA help.

    You may not have to do all this if you use the “Macro recorder” because that will do a lot of the hard work for you to start. There will come a time when you decide you want to start typing code directly into the VBA editor though and this is just to help you understand what’s going on.

    Remember “Objects“, “Properties” and “Methods” all fit together so you need to have looked at the definitions we’ve got there to make sure you know how it all works.

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 *