Using Objects, Methods, and Properties and Events in VBA (The Guide)

HomeTop 15 Excel VBA Tutorials – Best Way to Learn VBA in Excel (Guide)CHAPTER 7: Using Objects, Methods, and Properties and Events in VBA (The Guide)


How to use Objects in VBA

Visual Basic for Applications is an Object Oriented language and to make the best out of it you need to understand Excel Objects.

The workbook you use in Excel is made up of different Objects.

And there are a number of properties which are attached to an object which you can access and there several methods which you can use on/with an object.

Let’s understand this with an example.

Think about a big grocery store, which has different sections for products and then those sections further have racks and in those racks have products.

excel object are like a grocery store

So if you want to buy clothes, you need to go to the clothes section and in the clothes section, you could have the sub-sections for Men and Women.

And when you pick a cloth that cloth would have a specific color, size, and fabric (which are the properties of that cloth).

In the same way, in VBA, there are objects which are structured in a hierarchy.

Object Hierarchy in Excel

In Excel Application, you open a workbook and in that workbook, you have a worksheet (or multiple worksheets) and in that worksheet, you have cells and range of cells.

Excel’s Hierarchy starts with itself Excel Application and below is the structure which defines this hierarchy:

object hierarchy excel

Let’s say if you want to refer to a workbook the code you have write should be like:

Application.Workbooks(“Book.xlsx”)

In the above code, you are referring to the workbook “Book1”.

What actually you have written is, you refer to the Excel application first and you have used “Workbooks” which is further a part of the Application object.

Now let’s say you want to go further and wants to refer to a worksheet in the workbook “Book1” and the code for this would be:

Application.Workbooks(“Book.xlsx”).Worksheets(“Sheet1”)

You know what it means, Right? But let me tell you.

In the above code, after referring to the workbook “Book1” you have referred to the worksheet “Sheet1” which is further part of the workbook.

Let’s go a bit further. Let’s refer to a specific cell in the worksheet “Sheet1” and the code for this would be:

Application.Workbooks(“Book.xlsx”).Worksheets(“Sheet1”). Range(“A1”)

Before you ask, let me tell you this. In the above code, you referred to the cell A1 which is in the worksheet “Sheet1” of the workbook “Book1”.

When you refer to a Range object in this way, it's called a fully qualified reference.

Note
Using the APPLICATION object is optional. Reason? When you do something in VBA you are already in the Excel Application.

Simplify the reference to an Object

In the above code, you have used a fully qualified reference. That means you tell VBA to which workbook, worksheet, and cell to refer.

But what if you are already in the Book1 workbook?

In that case, you can simply refer to the worksheet in which you want to refer to a cell, just like below code:

Worksheets(“Sheet1”). Range(“A1”)

Even further, if the worksheet “Sheet1” is activated then you can simply refer to the cell or range of the cells, just like below code:

Range(“A1”)

Isn’t it better?

VBA Properties

Now you are clear about objects and the next thing to understand is “Properties”.

As I told you again and again that VBA is an object-oriented language BUT just by learn to refer to an object won’t help you to automate your stuff with VBA.

With each object, there are some properties which you can access and make changes into.

Here’s a real-world example:

Let’s say you have a box and the color of that box is RED. The box is the object and color is the property of it.

To make you understand how to access properties in your VBA codes, let’s write a code and use “Value” property of the range object.

The VALUE property can be used to read and write value from a cell and a range of cells. So let’s say you want to enter a number into the cell A1.

  • First of all, you need to specify the range, so the will be Range(“A1”) and the next thing is to enter “.Value”. When you write “.Value” it tells VBA to access this property.
    Range(“A1”).Value
    1-define-rane-to-add-value
  • The next thing is to type the value which you want to enter. Let’s type a text here so “Excel Champs”. If you typing a text you need to wrap it with double quotation marks and when you run this it will simply enter the value in the cell A1.
    Range(“A1”).Value = “ExcelChamps”
    2-add-value-excel-champs-as-value-to-the-cel-a1
  • In the same way, you can add a number. But with a number, you don’t need to use double quotation marks.
    Range(“A1”).Value = 9988
    3-enter-number-in-the-cell-a1
  • And if you want to enter the current date. For this, you need to use the DATE function here which returns the current date and when you run it, you will have the current date into the cell A1.
    Range(“A1”).Value = Date
    4-enter-date-as-in-the-cell-a1
  • Now let’s try a range of cells now.
    Range(“A1:A5”).Value = Date
    5-enter-date-the-entire-range

The idea is the same, specify the range use “.Value” and type the Value which you want to enter.

Let me recall: In the above example, you have read the value property, Right? But you can also read a property of an object.

Even though there are some properties which are only read-only, yes you can only read them.

Let’s write different code to read the value of a cell.

In the below code, you have defined the message box and used the range. After that specified the value property.

MsgBox Range("A1").Value
read value from a cell

And when you run this code it will show a message box with the value you have in the cell A1.

In the same way, you can also read the value from one cell and write it to another cell. In the below code, you have something like this:

Range("A2").Value = Range("A1").Value
read a value from a cell and write it on another cell

The above code reads the value property from the cell A1 and writes it to the cell A2.

Object Methods

Once you understand how to use Object properties in your VBA codes the next thing to master is Methods, which you can use with objects.

In simple words, methods are actions which you can do on or with objects.

If you want to do something meaning full with an object you need to know which is method is for what.

Remember the Box example which I have used in the Introduction of this guide?

Box.Open

Here “Open” is a method which opens the box which is your object. But let write real code. Let’s use a simple example for basic understanding, Right?

Let’s use “ClearContents” method which you can use to clear value and formula from a cell or a range of cells.

  • First of all, you need to specify the range so enter Range(“A1”).
    Range("A1")
  • After that, the next thing is to enter ClearContents.
    Range("A1").ClearContents
  • Now in the same way if you want to clear contents from a range of cells you can define a range as well.
    Range(“A1:A5”).ClearContents
  • And if you want to use “ClearContents” with the cell which is selected you can use the below code:
    Selection.ClearContents

When you run this codes it will simply clear formulas and value from the cell or a range of cell which you defined.

Arguments with Methods

With some methods, there could be some arguments which you need to specify (not all but some methods can have arguments and those arguments could be optional or mandatory).

Let’s say you want to replace value from a particular range of cell. Let’s say from the range A5:G10.

For this, you need to use “.Replace” method which further has arguments which you need to specify.

Once you type “.Replace” and then enter a space you can see the argument which you need to specify.

Here you have all the argument which you can see in the below snapshot. All the arguments which are in square bracket are optional. 

To use this method, you need to define two require arguments:

  • What: The value you want to replace.
  • Replacement: The value which you want to replace with.

Here I’m using “Yes” to replace with “No” and the code would be like below:

Range("A5:G10").Replace "Yes", "No"
defining arguments in vba replace method

This code will simply replace the value “Yes” with the “No” from the range A5:G10 when you run it.

Let me come again: Methods are actions which you can perform with/on an object.

VBA Events

In plain English, an Event means “Something that happens” and the meaning of VBA Events is exactly the same.

Whenever you do something in Excel that’s an event: enter a value in a cell, insert a new worksheet, or insert a chart.

Why do I need to know about Events?

Let me get straight into an example to make it clear to you. In the below example, you have worksheet event macro which triggers a code.

Private Sub Worksheet_Activate()
MsgBox "Welcome"
End Sub
a simple event in vba to trigger a message box when you activate the worksheet

To test this code follow the below steps:

  • First of all, right click on the worksheet tab and open “View Code”.
    open-view-code-window-to-enter-the-code
  • Now, paste this code into the code module and close your visual basic editor.
    a-simple-event-in-vba-to-trigger-a-message-box-when-you-activate-the-worksheet

Now, every time you activate the worksheet you’ll have a message box which says "Welcome". Before you ask me, let me tell you how this code works…

In this code, you have used the Worksheet_Activate Event which triggers the message box code every time when you activate the worksheet.

The point what I want to make here is: If you want to trigger a macro code when an event happens you need to learn about using EVENTS.

Types of Events

As you know there are several objects in Excel which you can use in VBA and there are multiple events associated with those objects which you can use.

Below is the classification of events based on the objects:

  1. Application Events: These are events which are associated with Excel application itself.
  2. Workbook Events: These are events which are associated with the actions happens in a workbook. For example, when you open a workbook, save it, close it.
  3. Worksheet Events: These events are associated with the action happens in a worksheet, For example, when you enter a value in a cell or when you activate a worksheet.
  4. Chart Events: These events are associated with the chart sheets (which are different from worksheets). For example, when you add data labels or when you add gridlines to a chart.
  5. Userform Events: These events are associated with the action happen with a user form. For example, when you click a button from a user form.
  6. OnTime and OnKey Events: OnTime events are those which can trigger code on a particular point of time and OnKey events are those which can trigger code when a particular key is pressed.

Now it’s time for action.

Let’s create a macro with which you can enter a check mark symbol in a cell by double-clicking on it. Just like you have in the below example.

Here you need to use “BeforeDoubleClick” event of a worksheet. You can learn more about it from here later but RIGHT NOW follow these simple steps:

  • First of all, right click on the worksheet tab and select the “View Code” to open the Worksheet’s code module.
  • The next thing is to write code to insert a check in the cell by using the double click.
  • For this, first of all, select the “Worksheet” from the first drop down you have above the code window and then “BeforeDoubleClick” from the second drop down.
    1-select-worksheet-before-click-from-the-drop-down
  • At this point, you have a blank macro just like below and the nest thing is to write the code.
  • In “BeforeDoubleClick”, you need to define two arguments. The first is the target (cell nearest to the mouse pointer when the double-click occurs) and the second is cancel.
    2-arguments-in-before-click-to-define
  • As you just need to enter the check mark only in column B you need to create a condition which check is the column of the target is 2.
  • And, if it’s 2 then it should cancel the double click edit and apply “Wingdings” font to the cell.
    3-condition-to-select-column-b-and-change-font
  • After that, it should check which is the cell is blank or not and it is blank is will enter an “ü” which is equivalent to a check mark when you have a “Wingdings” font to the cell applied.
    4-check-is-cell-is-blank-then-only-enter-value-
  • But is the cell is not blank then it would delete the value which you have in the cell? This will help you to delete the checkmark from the cell with a double click where you already have a double click.
    5-define-else-value

Here’s the complete code which you just have written:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 2 Then
Cancel = True
Target.Font.Name = "Wingdings"
If Target.Value = "" Then
Target.Value = "ü"
Else
Target.Value = ""
End If
End If
End Sub

The above code which you have written used the worksheet level events so the added the code into the worksheet code window.

Just like this, all other objects have their own code windows and you need to add code into those windows if you want to use related events.

  • Worksheet Code Window: Apart from the worksheet tab, when you open the VBE, you’ll have a separate window of each worksheet you have in your workbook.
  • Workbook Code Window: In the same way, there is a workbook code window where you can add all the code which you want to trigger on workbook events.
  • Userform Code Window: Normally when you create a user from you need to insert a separate a user form module for that and that’s the same pace where you can use all the events to trigger the codes.
  • Chart Code Window: When you insert a chart sheet in your workbook you will able to add code into it from your VBE and access all the events as well.
  • Class Module Window: This module allows you to use events which are related to the Excel’s Application itself.

Ways to Know Available Methods, Properties, and Events an Object

Below are the ways which you can use to refer to get to know about all the available methods to object:

1. Type a Dot

When you define an object in your VBA code you just need to enter a dot after that and VBA will show you a list of all the methods and properties which you use with/on the particular object.

Let’s say when you define a workbook as an object on pressing a dot you get all the methods which you can use, like Close, RefreshAll, Save, etc.

enter dot to show the list of available methods and properties
Note
The list you get after pressing the DOT will be the mixture of Methods, Properties, and Events. Well, before every method there will be a GREEN icon and before every property, there will a GREY icon.

2. Object Browser

The other simple ways open the “Object Browser” from View ➜ Object Browser and once you open it, the next thing is to select the “Excel Library”.

open object browser

Now here you have an entire list of objects and once you click any of the objects you can further see the list of methods, properties, and events.

Tip
If you want to know about a particular method simply right click on it and select the “Help” from the there. This opens the web page on Microsoft Dev Center where that method is listed.

There's More

Below are some of the external links which could be useful for you to learn more about objects, methods, properties, and events, make sure to check out all of these...

© ExcelChamps 2019