How to Use Objects, Collections, Properties, Events and Methods in Excel Visual Basic
1. What are Objects and Collections?
Visual Basic defines a number of what it calls Objects.
Examples of Objects are:
- A Collection (groups of system- or user-defined objects)
- A Form
- A Module
- A Control
- The Printer
- The Screen
- Dictionary
- The Debug Object (Debug Window)
- The Err (Error) Object
- The Clipboard Object
- The Application Object, i.e. the Program – in this case Microsoft Excel.
In object-orientated terminology, Objects belong to Classes.
Collections are groups of Objects. Here are some examples of Collections that Visual Basic defines:
- Forms
- Controls
- Printers
- Drives
- Folders
2. The Excel Object Model
Excel, too, has objects of its own. When Visual Basic addresses these objects we are able to control Excel.
The diagram below is an excerpt from the full Excel Object Model. It contains the Objects we are most likely to use.
For example, the Workbook Object is in the Workbooks Collection, Chart in Charts, and so on.
To find out, say, the font in a particular Range, we could use any of the following:
Sub cmdFontDemo_Click() ' Demonstration of addressing Excel Objects:
MsgBox Application.ActiveWorkbook.ActiveSheet.Range("A1").Font.Name, _
, "Cell A1 Font Name"
MsgBox ActiveWorkbook.ActiveSheet.Range("B1").Font.Name, , "Cell B1 Font Name"
MsgBox Worksheets(1).Range("C1").Font.Name, , "Cell C1 Font Name"
MsgBox Worksheets("Sheet1").Range("D1").Font.Name, , "Cell D1 Font Name"
MsgBox Range("E1").Font.Name, , "Cell E1 Font Name"
Note that you can omit the active object since it is the default.
Sheet numbering starts from 1, not zero.
3. Properties, Events and Methods
3.1 Properties
Objects have certain characteristics, called Properties. Some Properties you can set. You can read (establish the value of) all of them. To access the properties in the Visual Basic Editor, use the Properties Window (F4) at design time. At run time, you can set or read properties using the syntax
Object.Property
For example (as above):
Font.Name
Some objects have a default property, which you can omit in the syntax. The default property for a Text Box is Text. So you can write Text1 when you actually want Text1.Text –the value of the Text Property. (Unlike VBA, VB.NET does not allow default properties.)
3.2 Events
Events happen to objects.
When you click a button on a sheet, the click event happens. If there is an associated subprogram, it will execute.
For example, see the picture below.
You can get the file Macros.xlsm free of charge from us. If you have it, on Sheet1, click the button “Font Names demonstration” (named cmdFontDemo). The click event happens and executes the subprogram cmdFontDemo_Click.
To see the code, in Visual Basic, in the Project Explorer (Ctrl+R), expand the VBAProject for Macros.xlsm. In Microsoft Excel Objects, select Sheet1). Sub cmdFontDemo_Click runs the demo we saw earlier.
Each type of control supports a certain set of events. Button events are: Before Drag Over, Before Drop or Paste, Click, Dbl Click (double-click), Error, Got Focus, Key Down, Key Press, Key Up, Lost Focus, Mouse Down, Mouse Move, and Mouse Up.
3.3 Methods
Methods operate on Objects. Methods are like Events triggered by the program, not the user. To make a method take place, you use the same syntax as with properties, i.e.:
Object.Method
For example, to print at run time to the Debug window (Ctrl+G), we use the Print method on the Debug Object:
Debug.Print
Each type of control supports a certain set of Methods. For example, the File object has the Methods: Copy, Delete, Move, and OpenAsTextStream.
4. Using the Object Browser
The Object Browser lets you browse through all objects in your project and see their properties, events and methods. You can see the procedures and constants that are available from object libraries in your project.
To use the Object Browser, activate a module and click View > Object Browser or press F2.
To make it less confusing, in the drop-down list at top left, which by default says “<All Libraries>”, instead choose “Excel”, “VBA”, or “VBAProject”. Note: This relates to the current VBA Project as selected in Project Explorer.
5. Naming Objects
The default naming offered by Visual Basic (e.g. Command1 for a Command Button) is not very useful. In the Microsoft Knowledge Base, the Microsoft Consulting Services Naming Conventions suggest:
| Prefix | Object Type | Example |
| cbo` | Combo box and drop down list box | cboLanguages |
| chk | Checkbox | chkReadOnly |
| cmd | Command button | cmdOK |
| dir | Directory list box | dirSource |
| frm | Form | frmEntry |
| fra | Frame | fraStyle |
| img | Image | imgIcon |
| lbl | Label | lblHelpMessage |
| lin | Line | linVertical |
| lst | List box | lstPolicyCodes |
| opt | Option Button (Radio Button) | optRed |
| pic | Picture | picVGA |
| txt | Text Box | txtLastName |
| vsb | Vertical scroll bar | vsbRate |
| hsb | Horizontal scroll bar | hsbVolume |
We will use these conventions from now on.
6. Name that Range, too!
Which of the following two snippets of code will be more robust?
Range("B3").Select
Range("Max_Reading").Select
Obviously, the latter! It is independent of the cell address, which might change.
To use that, you must first name the range in your Excel worksheet. Select the cell you want to name. Then:
- Type the desired name in the Name Box on the left of the Formula Bar and press Enter. (Bottom left of the illustration below.) Or
- From the Formulas ribbon, in the Defined Names group, click Define Name. (Circled in red in the illustration below.) Or
- If you have adjacent cells containing the names you want to use, use Create from Selection.
Remember that range names must be unique. They can contain no spaces (but may contain underscores). You cannot use names that look like cell references, e.g. AB123.
For more on range naming, see this post.