Excel: Objects in VBA
Object Oriented Programming (OOP for short) is based on the idea that everything in programming can be described as an object. These objects are not physical things we can see in the real life, but virtual ones existing in the computer memory.
In the following lessons, I will write about things associated with objects, such as properties, methods, events, and hierarchy.
Application object and object hierarchy
The Application object represents the entire Excel application. It is located at the top of the object hierarchy and it contains every other object. Follow this lesson to learn more.
The most common objects that derive from the Application object are:
These objects can contain other objects. Let’s take a Workbook for example. Each workbook contains objects, such as:
These objects can also contain objects. For example, a Worksheet can contain the following objects:
Referring to objects
In order to work with VBA objects, first you need to learn how you can refer to them. Below you will find examples of how you can shorten the code, so it will be more readable and easier to write.
- The following code is a reference to the value in cell B5. It can be written in the following form:
- In VBA, the application object is always assumed. That means that our reference can be written as:
- Furthermore, if you want to point to the first worksheet in the workbook you can use:
- If you don’t refer to a particular workbook, Excel assumes that the worksheet is in the currently opened workbook:
- The same is with the worksheet. If you lose the word, Excel will assume that the range is in the active worksheet.
- Because Value is treated as the default property, in this case, we can also make a reference without the Value property. The final reference will be:
Each physical object has some properties. It may be color, size, weight, name and so on. But not only objects in real life have properties, also virtual VBA objects have them.
There are thousands of properties in Excel, some of them can be changed and the other ones are read-only. You don’t have to remember all of them. You will probably use only a small fraction of the most important ones.
Look at the following example. It gets the value from cell B5 and displays it the MsgBox window.
Sub displayCellValue() cellValue = Worksheets("Sheet1").Range("B5").Value MsgBox cellValue End Sub
This is the result generated by the macro.
In the second example change this value to 1234.
Sub changeCellValue() Worksheets("Sheet1").Range("B5").Value = 1234 End Sub
This will give us the following result.
In the previous lesson, I presented object properties. In this lesson, I will describe methods.
The method is an action that is performed by an object.
Let’s take a real-life object: a car. The car has such properties as color, speed, weight, etc. But in addition to that is can perform actions, such as start engine, drive, stop and so on.
In the following examples, I describe a few methods you can use in Excel 2013.
In the following example, we will clear cells from B2 to C5.
To do this, we can use the following code:
Sub clearData() Range("B2:C5").ClearContents End Sub
This will give us the following result:
Collections, as well as other objects in Excel, can use methods. The following macro will create a new workbook.
Sub createWorkbook() Workbooks.Add End Sub
This method will display the number telling you how many workbooks are currently opened.
Sub countWorkbooks() MsgBox Workbooks.Count End Sub
An Event in Excel is an action (performed by a user) which can execute VBA code. Below, you will find a sample list of events that can trigger the code execution:
- Activating or deactivating an object (worksheet, window, etc.).
- Entering data into a cell.
- Opening or closing the workbook.
- Saving the workbook.
- The occurrence of a particular date.
- Use of a particular key combination.
Creating an Event
To create the event, first you have to open the Visual Basic Editor (VBE) by selecting DEVELOPER >> Code >> Visual Basic, or using the Alt + F11 keyboard shortcut.
When a new window appears, perform the following actions:
- Double-click „ThisWorkbook”.
- Select „Workbook” because we want to trigger the code when something with the workbook happens.
- Click the event for which you want to execute the code.
When you start clicking different events in the list, Excel will automatically enter new subs. As long as there is no code in them, they won’t have any effect on the worksheet. You can also delete them if you don’t want empty subs to be visible.
This code will highlight cell A1 with a random color each time you activate the workbook. You can activate your workbook when you open it or when you switch from another opened workbook.
Private Sub Workbook_Activate() r = WorksheetFunction.RandBetween(0, 255) g = WorksheetFunction.RandBetween(0, 255) b = WorksheetFunction.RandBetween(0, 255) Range("A1").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = RGB(r, g, b) End With End Sub Private Sub Workbook_Open() End Sub