Objects and collections, Properties, Methods, and Variables in Excel VBA

This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide.

Objects and collections

VBA is an object-oriented programming language. As name shows, object-oriented language works with objects. Some excel objects are Ranges, Charts, Shapes, and so on. The objects are arranged in a hierarchy. The Application object (that is Excel) holds other objects. For example, the Application object contains a number of other objects, including the following:

  • Windows (two or more Window objects make a Windows collection.)
  • AddIns (two or more AddIn object make a AddIns collection.)
  • Workbooks (two or more Workbook objects make a Workbooks collection.)

Most of the objects contain other objects. Open the VB Editor (press ALT+F11) and open any existing module under Project window. We have opened ShowSumOf macro. Start writing this code Workbooks(“vba-macro.xlsm”). in the Code window like the following figure.

Objects and collections, Properties, Methods, and Variables in Excel VBA

Workbook objects list.

Each of these objects can contain other objects. For example, a Worksheet object can contain objects like the following list:

  • ChartObject: A chart related object.
  • PivotTable: A PivotTable related object.
  • PageSetup: This object sets printing information.

Some same type of objects make a collection. For example, the collection of all Workbook objects is known as the Workbooks collection. We refer an individual object in a collection by using a name or an index number. For example, say a workbook has three worksheets (named Sheet1, Sheet2, and Sheet3). We can refer to the first object of the Worksheets collection in either of the following two ways:

Worksheets(1)
Worksheets(“Sheet1”)

Properties

Most of the objects we work with have properties. Properties can be thought of settings of objects. For example, a Range object has properties. Some of them are Column, Formula, Row, Width, and Value. A Chart object has properties, such as Legend, ChartArea, ChartStyle, and so on. ChartTitle is also an object, with properties such as Font, Format, and Border. Excel has many objects. Each has its own set of properties. We can write VBA code to do the following:

  • You can examine an object’s current property settings and do something based on these settings.
  • You can change the object’s property settings by setting new values.

Look at this VBA statement: Range("E10").Value. In this statement, Range is an object, Value is its one of the properties. In the VBA statement, objects and properties are placed side by side separating them by a period (a dot, .). Objects are placed first, then its properties.

For example, the following VBA statement sets the Value property of Range E10 to 100.
Range(“E10”).Value = 100
That statement will cause number 100 to display in the cell E10.

Some properties are read-only. Read-only properties mean that you can examine the property, but you can’t change the property. Say you have selected a single-cell Range object (say it is Range(“E10”)), you can only find out what is the column text and row number using Column and Row properties of the Range object. You can’t change the values of column and row of selected object Range(“E10”).

A Range object also has a Formula property. This property is not read-only. We can insert a formula into a cell by changing its Formula property. The following statement inserts a formula into cell E10 by using the cell’s Formula property:

Range("E10").Formula = "=SUM(E1:E9)"

Note: It is interesting that Excel doesn’t have a Cell object. When you want to manipulate a single cell, you use the Range object (Range(“E10”)).

Properties can return objects. For example, Application.ActiveCell returns a Range object. Application.ActiveCell.Font returns again a Font object. You can now change the font of the active cell using its Size properties like the following statement: Application.ActiveCell.Font.Size = 12

Tip: Application properties are commonly used. For this reason, you can omit this object from the VBA statement. For example, to get the row of the active cell, you can use the following statement: ActiveCell.Row

In most cases, we can refer to the same object in different ways. Assume that we have a workbook named vba-macro.xlsm and it’s the only workbook open. Furthermore, assume that this workbook has one worksheet, named Expenses. Our VBA code can refer to the Expenses sheet in any of the following ways:

Workbooks(“vba-macro.xlsm”).Worksheets("Expenses")
Workbooks(1).Worksheets(1)
Workbooks(1).Sheets(1)
Application.ActiveWorkbook.ActiveSheet
ActiveWorkbook.ActiveSheet
ActiveSheet

If more than one workbook is open, the second or third method isn’t reliable. You have to use a specific name for the workbooks. When you are working with an active sheet, any of the last three methods would work. The first method is the best choice as this statement refers to the sheet specifically.

Methods

Objects also have methods. A method is an action implemented on an object. For example, Range objects have a Clear method. The following VBA statement clears a Range. This statement is equivalent to selecting the Range and then choosing Home ➪ Editing ➪ Clear ➪ Clear All:
Range(“A10:C20”).Clear

In VBA code, methods look like properties. Methods are connected to the objects with a separating operator (.). However, methods and properties are different concepts in VBA.

Variables

Like all programming languages, VBA also works with variables. In VBA you don’t need to declare variables explicitly before you use them in your code. But it is a good practice to declare variables before using them in the code.

Note: Sometimes you may find that your VBA module contains an Option Explicit statement at the top of the module. This instructs you to declare all variables in the module. Undeclared variables, in this case, will result in a compile error, and your macro procedures will not run.

In the following example, the value in cell A1 on Sheet1 is assigned to a variable named InterestRate:

InterestRate = Worksheets("Sheet1").Range("A1").Value

After the statement is executed, you can work with the variable InterestRate in other parts of your VBA code.

Happy Excelling 🙂


Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

We will be happy to hear your thoughts

      Leave a reply