VBA Object

VBA Object

In Microsoft Excel, a VBA Object can contain one or more than one object. Such as a single workbook can have one or more than one worksheet. Workbook, Worksheet, Range, and cells are the objects in Excel. Each object has its own properties. And they all have a different method of application. Let say the workbook is the main object which has 2 worksheets in it. Those 2 worksheets will be its child object. One of the sheets has one range, so that sheet will become the main object, and the range will be its child object.

How to Use Object in VBA

Let’s see the examples of object in Excel VBA.

Example #1 – VBA Object

It can be written in many ways. Suppose we need to print any text in a cell, so this can be done in various ways. We can directly use the range value to that cell. This direct method is not a part of VBA Object but a process of performing a task that could be done by VBA Object as well. For this:

Step 1: Go to VBA and insert a Module from the Insert menu option as shown below.

Step 2: Now write the Sub Category of performed function, like VBA Object, in any other name as per your choice, as shown below.

Code:

Sub VBAObject2()

End Sub

Step 3: Now select the range function considering any cell, let’s say cell B3 with Value as shown below.

Code:

Sub VBAObject2()

Range("B3").Value =

End Sub

Step 4: Now add text or word in that range cell as shown below.

Code:

Sub VBAObject2()

Range("B3").Value = "VBA Object"

End Sub

Step 5: Now, compile the code and run it by pressing the play button located below the menu bar.

Example #2 – VBA Object

This is the normal way of printing text to any cell. How we will see how the same process can be done when we use VBA Object. For this, we will need another fresh module. And in that,

Step 1: Go to VBA and insert a Module from the Insert menu option as shown below.

Step 2: Write the Sub Category of VBA Object as shown below.

Code:

Sub VBAObject1()

End Sub

Step 3: Here, we will see the complete VBA Object from the main to a child category. First, select the currently opened workbook with the help of the command ThisWorkBook with Application, as shown below. This will select the workbook which is actually opened and last selected.

Code:

Sub VBAObject1()

Application.ThisWorkbook

End Sub

Step 4: Now select the sheet which is currently opened in the workbook, or we can write the name of the worksheet as well. Here, we have written the name of sheet Sheet1 in inverted commas, as shown below.

Code:

Sub VBAObject1()

Application.ThisWorkbook.Sheets ("Sheet1")

End Sub

Step 5: Now comes the range. Select the range of the cell where we need to print or insert the text. Here we are selecting cell B4, as shown below.

Code:

Sub VBAObject1()

Application.ThisWorkbook.Sheets("Sheet1").Range("B4").Value

End Sub

Step 6: At last, add the text which we need to see in the select Range cell as shown below.

Code:

Sub VBAObject1()

Application.ThisWorkbook.Sheets("Sheet1").Range("B4").Value = "VBA Object"

End Sub

Step 7: Now, compile and run the code. We will see cell B4 has the text “VBA Object”.

This complete process is called VBA Object. In which we have first selected the main object, i.e. Workbook, which has its child object, i.e. Sheet1, and that has another child object range, i.e. cell B4.

Example #3 – VBA Object

There are many different ways to add text to any cell. One can be with Excel VBA Object, and others can be without it. In these categories, again, there are many ways to do it. Let’s see another way to add VBA Object. For this, we will need a module.

Step 1: In that module, add a subcategory; better make it with sequence number as shown below.

Code:

Sub VBAObject3()

End Sub

Step 2: Select the worksheet by adding the name of the current worksheet, which is Sheet1, as shown below.

Code:

Sub VBAObject3()

Worksheets("Sheet1").

End Sub

Step 3: Now, add the range of the cell where we want to see the output, as shown below.

Code:

Sub VBAObject3()

Worksheets("Sheet1").Range("A3").Value

End Sub

Step 4: At last, give it a value that we can see once we run the code. We are considering the same text as seen in example 1.

Code:

Sub VBAObject3()

Worksheets("Sheet1").Range("A3").Value = "VBA Object"

End Sub

Step 5: Now run the code. We will see; cell A3 got the text which we wanted to add there, as shown below.

In this process, we have directly added the worksheet. So Worksheet will become our Object, and Range will become its child object.

Step 6: There is another way to perform the same task. In the bracket of the worksheet, instead of writing the sheet name, we can write the sequence of the sheet, which is 1, as shown below.

Code:

Sub VBAObject3()

'Worksheets("Sheet1").Range("A3").Value = "VBA Object"
Worksheets(1).Range("B3").Value = "VBA Object"

End Sub

Step 7: Now run the modified code. We will see cell B3 got the same text VBA Object as cell A3, as shown below.

By keeping both the code in line, we can see and compare the changes we made. In another way,

Step 8: Instead of the Worksheet function, if we use the Sheet with sequence and selecting cell C3 as range as shown below.

Code:

Sub VBAObject3()
'Worksheets("Sheet1").Range("A3").Value = "VBA Object"
'Worksheets(1).Range("B3").Value = "VBA Object"
Sheet1.Range("C3").Value = "VBA Object"

End Sub

Step 9: Now run this code. We will see, again the same text will get added in range cell C3.

In all the methods which we have seen in this example, Worksheet is our object, and the range of the cell is child object or Sub-object.

Pros and Cons of  Excel VBA Object

• We can make as many objects and link them together to sink them.
• It makes use of Workbook, Sheet, and Range easy.
• This allows a user to make changes in a specific Workbook, Worksheet or Range.
• The same process can be performed by a much shorter code with having the same result.

Things to Remember

• Worksheet and Sheet both have the same use.
• We can select any worksheet of the same workbook of any number sequence.
• While writing big lines of code, it is important to form an Object in which the Workbook, Worksheets, and Ranges of different cells are linked.
• Must save the file in Macro-Enabled Excel format. It allows us to use the code and change the code whenever required.

Recommended Articles

This is a guide to VBA Object. Here we discuss how to use Object in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA IsNumeric
2. VBA UCASE
3. VBA Object Required
4. VBA ByVal