1.4 Understanding the code

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").Select

Understanding 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

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").Select

Could be shortened to:

Range("A1").Select

Application
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").Select

If 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").Select

Or if we wanted to select Sheet1 of the workbook in which the VBA code is we could use:

ThisWorkbook.Sheets("Sheet1").Range("A1").Select

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 = 65535

In 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").Select

Deleting 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:

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

Save