We jumped straight in and had you record a macro. Then we’ve looked at how you can simplify and add some really powerful features to your code by looping as well as establishing and performing simple operations on variables. It’s all awesome. Maybe now’s a good time to step back and try and understand a bit more about what’s going on. Maybe it’s time to introduce you to VBA’s object-property-method structure.
The object-property-method model
Overview of the object-property-method model
Here’s a quick overview to start:
- VBA does work on objects – you need to tell Excel what object you want the work done on
- Objects have properties – Excel allows you to set and change the properties of objects
- You can apply methods to objects – a method is a little bit of prescribed work/ a routine that you get Excel to do/ perform on an object.
- Tell Excel what object to work on
- Set the properties of the object you’re interested in and
- Set a method to work on your object then…
…you’ll soon have your Excel VBA macro humming.
Excel is made up of objects
Excel consists of objects
An object is a component or part of Excel that you use your macro code to do some work on. Here are some common examples of Excel objects:
- The active cell (= the cell you find yourself in in the spreadsheet)
- A particular cell within your Excel spreadsheet
- A range of cells
- A worksheet.
In your macro code what you have to do is tell Excel what object you want it to work on.
Sources of information on objects
Guidance on objects
We’ve introduced you to the idea that you have to include in your code some instructions about what object you want Excel to work on. But you’re new to this. How do you know what object you should be targeting? Here are some helpful sources:
- Excel’s macro recorder
- The VBA editor
- Excel’s VBA help function.
1: The macro recorder
We’ve already tried to sell you on the power of Excel’s macro recorder. If you use the recorder to point to the object you’re interested in, then look at the resulting code, you’ll get some massive clues about what object you need your code pointing to. That’s the beauty of the macro recorder (no, we’re not on commission from Microsoft): it does most of the work for you. There are a few other sources that will be helpful to you as well though.
2: The object browser in the VBA editor
If you open the VBA editor (“Alt “F11”) and click on the “object browser” button (“F2”) you’ll be presented with a list of possible Excel objects (it’s a pretty long list).
You can see below that “ActiveCell” is top of the list for us.
3: The VBA editor’s help function
If you open the VBA editor (“Alt” “F11” and get into its help function “F1”) you should be able to access the Excel Object Model Reference. If that’s not working for you, you can access an online version of it on Microsoft’s site. It contains lots of information about the objects that you might want to target with your code.
We bet you’ll find the macro recorder really useful
Although you’ve got a few sources listed above that should help you with Excel objects (not to mention just general web surfing looking for an online example where someone has listed their solution to a problem that’s similar to yours) to begin with the macro recorder should be able to help you target the objects you are interested in. The recorder is there to help you avoid having to wade through acres and acres of programming documentation.
Example: how to target an object
Here’s the example code we were using when we looked at different kinds of macro variable:
Sub AddOneMacro() 'This macro increases the value of the active cell by 1 'Select the active cell ActiveCell.Select 'Declare that x is a variable and that x must be an integer Dim x As Integer 'Set x equal to the ActiveCell x = ActiveCell 'Add 1 to x x = x + 1 'Output the result into the active cell ActiveCell = x End Sub
Ignoring the comments (with the single quotation marks ‘ in front), the first line of code tells Excel to do its work on the ActiveCell object. You could have created that piece of code yourself by:
- 1. Pointing to the active cell whilst trying to record a macro (having depressed the “Use relative references” button on the code section of the developer tab prior to recording) 2. Surfing the object browser and then typing “ActiveCell” directly into your macro module 3. Surfing Excel VBA help (“Alt” “F11”, “F1”) for the same information.
If you’re brave enough to start typing in “ActiveCell” into your macro, things start to get a bit more interesting when you type a full stop after the object you want to target (a list should pop up as per the screen shot/ end of the video below).
It’s object first, properties and methods next
Once we’ve told Excel what object we want to target, we can
About this online financial modelling course material
You’re looking at a free online financial modelling course extract covering macros.