What are Objects, Properties and Methods in Excel VBA ?

by

An object has properties and methods. Excel Visual Basic is a semi-object oriented programming language. Learn more about the object hierarchy of Excel Visual Basic.

In Excel VBA, objects, properties and methods are connected with a dot. Properties are something which an object has (they describe the object), while methods do something (they perform an action with an object).

What is Excel VBA Property ?

Let’s take a look at an Excel VBA object and an Excel VBA property. We will use the Range object and the Formula property. The Range object is nothing more than a cell (or cells) on your worksheet.
We already know from Excel that a cell can contain a formula.
1. Place the value 10 into cell A1.
2. Create a command button.
3. Add the line:
Range(“B1”).Formula = Range(“A1”) * 2

4. Execute the macro. This macro places the formula into cell B1 and the result will be calculated (20).

What is Excel VBA Method ?
We will use the Range object again and the ClearContents method.
1. Place the value 10 into cell A1.
2. Create a command button.
3. Add the line:
Range(“A1”).ClearContents

4. Execute the macro. Cell A1 is now empty!

How to see all the properties and methods of an object ?
The Range object has many more properties and methods. Want to see more?
1. Launch the Visual Basic Editor.
2. Type in: Range.
3. A list will appear showing you all the Excel VBA methods and properties of the Range object. The fingers are the properties and the green boxes are the methods of the Range object.

Workbook and Worksheet:

The Worksheet and Workbook object have properties and methods as well, such as the count property which counts the number of active workbooks or worksheets. The Workbook and Worksheet object are commonly used in Excel VBA. They are very useful when your macro code has to be executed on different workbooks or worksheets.

What is Object Hierarchy ?

Now that we have seen the Range object, we can understand the Workbook and Worksheet object better. In Excel Visual Basic each object can contain another object, and that object can contain another object, etc. In other words, Excel VBA programming involves working with an object hierarchy.

 

The main hierarchy of all objects is Excel itself. We call it the Application object. The application object contains other objects. An example of an object of the Application object is the Workbook object (Excel File). This can be any workbook you have created. The Workbook object contains other objects, such as the Worksheet object. The Worksheet object contains other objects, such as the Range object.

We have used the following code line a lot:

Range(“A1”).Value

but what we really meant was cell A1 on the first worksheet of Book1. Thus we should actually add the following line in Excel VBA:

Application.Workbooks(“Book1”).Worksheets(1).Range(“A1”).Value

Fortunately we do not have to add a code line this way. This is because Excel Visual Basic knew we meant Book1 and the first worksheet because we placed our command button there (remember?). Now also remember the automatically created module when we recorded a macro with the Excel Macro Recorder. Code placed into a module is available to all workbooks and worksheets.

Place the Sub test into a module (In the Visual Basic Editor, click on Insert and then Module).

Sub test()
Range(“A1”).Value = “code placed here”
End Sub
  1. Execute the code (Click on Macros and then Run, or click on Run from the Visual Basic Editor). The words “code placed here” will be placed into cell A1.
  2. Now go to the second worksheet. Execute the code again. You will see that the words will be placed on the second worksheet as well!
  3. Now even open a new workbook and execute the macro again. You will see that the words will be placed there as well! That is because we didn’t specify a workbook or worksheet name and Excel VBA automatically takes the active workbook and active worksheet. Be aware that if you want to change different things on different sheets to include the Worksheet object

Below are few Properties and methods of the Workbook and Worksheet object :

You may have noticed that worksheets and workbooks are both plural (see the complete code line mentioned earlier). That’s because they are actually collections. The Workbooks collection contains all the Workbook objects that are currently open. The Worksheets collection contains all the Worksheet objects in a workbook.

You can refer to a member of the collection, that is: a single workbook or single worksheet, in two ways. Using the index number, Worksheets(1) is the first worksheet starting from the left. Using the member’s name: Worksheets(“sheet1”).

Below are few examples.

1. The Count property of the Worksheets collection and Workbooks collection. The following code line counts the number of worksheets of a workbook. Place a command button on your worksheet and add the code line:

MsgBox Worksheets.Count
Result when you click the command button on the sheet:

You can also use the Count property to count the number of active workbooks.

2. The Add method of the Workbooks collection and Worksheets collection. The following code line creates a new worksheet.

Worksheets.Add
You can also use the Add method to add a new workbook.

3. The Worksheet object contains more interesting collections, such as the Rows collection. In Excel VBA you can use the Select method to select a row. The code line below selects row 2

Worksheets(1).Rows(2).Select

In a similar way, you can select a column. The code line below selects column 7.

Worksheets(1).Columns(7).Select

What is Application Object ?

The mother of all objects is Excel itself. We call it the Application object. The application object gives access to a lot of Excel related options.

The mother of all objects is Excel itself. We call it the Application object. The application object gives access to a lot of Excel related options.

Thank you for reading. Happy Learning!

Please leave a reply if you like the article or if you need any help on excel

%d bloggers like this: