Previous Part: 1.3 Recording Macros
When you first look at VBA code it can seem very confusing. By the end of this section, I hope you will have the knowledge to break down any piece of code to have a reasonable chance of understanding what it is doing.
Understanding the VBA coding structure
VBA is an object-orientated style programming language. Now don’t freak out about how confusing this sentence seems, let me explain it first. Let’s use the objects in your house as an example. In your house you have many rooms and in those rooms are many things and those things are able to do a variety of activities. If we were to open the fridge in your house using object orientated programming we would need to do the write that activity as follows:
House -> Kitchen -> Fridge -> Open
Equally, if you wanted to get into your bed you could use
House -> Bedroom -> Bed -> GetIn
The fridge is only in the kitchen, it is not in your bedroom. As a result, you could not use the following code:
House -> Bedroom -> Fridge
If you tried to open the fridge by going to the bedroom you would fail. Understanding how to get to each object in your house is a key aspect of object orientated programming. Excel follows the same principles, if you wanted to select Cell A1 on a certain worksheet you would use your mouse or keyboard to find the Cell and select it:
Excel -> ExampleWorkbook.xlsx -> Sheet1 -> Cell A1 -> Select
You could not select that Cell if you started by opening Word, or by using the wrong worksheet. Therefore the order of objects is important, just as you can’t find the fridge in the Bedroom, VBA won’t be able to find worksheets in the wrong workbooks.
If you were using VBA code it would follow a very similar structure, but the various elements are separated by a ” . ”
Application.Workbooks("ExmpleWorkbook.xlsx").Sheets("Sheet1").Range("A1").SelectUnderstanding the order of objects in VBA is probably what will form most of your early learning frustrations and errors. So, take the time early on to really try to understand this, it will save you a lot of time in the long run.
VBA’s assumptions
Advertisement:
If you open the VBE and look at the code of a recorded Macro the individual lines of code don’t look as long as the example above. That’s because VBA makes some assumptions.
Going back to our Fridge example, you already know that your fridge is in the kitchen. So, if you wanted to open the fridge, you could just try:
Fridge -> Open
In most cases that would be sufficient, as it is assumed you would want to open your own fridge. If it turns out we want to open your Neighbor’s fridge then we would need to define the path.
NeighborsHouse -> Kitchen -> Fridge -> Open
In the same way, VBA coding structure makes some assumptions. In a lot of circumstances the following code:
Application.Workbooks("ExmpleWorkbook.xlsx").Sheets("Sheet1").Range("A1").SelectCould be shortened to:
Range("A1").SelectApplication
VBA in Excel assumes that we will be using Excel as our application, not Word, not Powerpoint, but Excel. As you develop, you will discover that you can use Excel VBA to tell Word and Powerpoint what to do. Unless we are doing that we do not need to use Application, as VBA will already assume.
Workbook
VBA in Excel also assumes that the active workbook is the one you want to use, so if you want to reference a cell on the active workbook you do not need to define anything about the workbook. However, if you want to reference a different workbook you will need to use one of the following:
- ThisWorkbook – The workbook in which the VBA code is written
- Workbooks(“WorkbookName.xlsx”) – A specific workbook called “WorkbookName.xlsx”
- Workbooks(3) – The number refers to the order the workbooks were opened. In this example the 3rd workbook.
- ActiveWorkbook – If you want to ensure you refer to specifically the active workbook you can use this code.
Worksheets
VBA in Excel also assumes the active worksheet is the one you want to reference. If you want to reference a different worksheet you can use one of the following:
- Sheets(“SheetName”) – This refers to a specific worksheet whose tab is called SheetName.
- Sheets(4) – This refers to the 4th worksheet from the left
- ActiveSheet – This refers to the active worksheet. This can be left out in a lot of circumstances
Examples
If we wanted to select Cell A1 of the active worksheet we could use
Range("A1").SelectIf we wanted to select Cell A1 of the 4th worksheet of a workbook called “Example.xlsx” we would use
Workbooks("Example.xlsx").Sheets(4).Range("A1").SelectOr if we wanted to select Sheet1 of the workbook in which the VBA code is we could use:
ThisWorkbook.Sheets("Sheet1").Range("A1").SelectAdvertisement:
Understanding object properties
Properties are the characteristics of the object. Your house, kitchen and fridge all have characteristics. For example, they all have a height, and all have a color. In the same way, all the objects in VBA have characteristics or properties as they are more commonly known.
If we wanted to change the height of row 9 we could:
Rows(9).RowHeight = 30
If we wanted to change to color of a cell we could (65535 is the color code for yellow)
Range("A1").Interior.Color = 65535In your house, not all objects have the same properties. For example, the fridge has a temperature property, but the TV does not. The TV has a volume property, but the fridge does not. VBA is just the same, each object has it’s own properties. Whilst there are some very common properties, which lots of objects have, they do not all share the same properties. For example, you can change the color of a cell, but you cannot change the color of a workbook, as the workbook does not have a color property.
Understanding methods
Methods are the actions which an object can perform. Using our example, you can open a fridge, but you cannot open a TV. Any action you can perform on a fridge are its methods, and any action you can perform on a TV are its methods. Just like properties, methods are specific to an object. Objects do not all share the same methods.
Selecting a cell is a method:
Range("A1").SelectDeleting a worksheet would also be a method:
Worksheets(4).Delete
Help! This is too much to remember!
As you use VBA you will start to get used to these properties and methods. That’s why I have the golden rule, so you can start to remember which objects belong where and the properties of each object. But you do not need to remember it all, the VBE has a number of tools built in:
Advertisement:
Object Browser
The VBE includes the Object Browser – click on the icon highlighted below.
The object browser will open. This shows a complete list of all the objects, methods and properties available. This can be a little overwhelming at the start, so the Auto List Members may be more useful as you begin.
Auto List Members
This tool will automatically show the methods, objects properties available as you type code. into the VBE. You can double click on the item in the list box and it will populate the code for you.
I’ve found this to be the most useful tool when learning VBA.
If you do not have this turned on automatically:
Tools -> Options -> Editor -> Auto List Members
Use Google
There is no shame is typing your problem into Google and searching. There will be lots of resources available to help you – tutorial sites, videos and forums. Chances are, if you have a problem somebody else has had it before you.
Previous Part: 1.3 Recording Macros
Next Part: 1.5 Adding comments
