List of 10 Mostly Used Excel VBA Objects (Attributes & Examples)

This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide. We will discuss a list of only 10 mostly used Excel VBA objects.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

What Are VBA Objects?

The VBA (Visual Basic Application) is an object-oriented programming language. The object is one of the elements of VBA.

An object has its property and method. The method is the operation performed by that object and the property explains the characteristics of that object.

Attributes of VBA Objects

To apply a VBA object there must be a method or property in the Object. We will discuss those attributes here.

Properties

Excel has many objects. Most of the objects in Excel VBA we work with have properties.

Example:

• 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.

Use of VBA Object 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 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 their properties.

For example, the following VBA statement sets the Value property of  Range E10:100.

Range("E10").Value = 100 

That statement will cause the number 100 to display in Cell E10.

Methods:

Objects also have methods. 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.

Read More: Excel Chart Disappears When Data Is Hidden (3 Solutions)

List of 10 Mostly Used VBA Objects in Excel

There is a hierarchy followed by Excel in the case of objects which is:

Application → Workbook → Worksheet → Range

Here, we will discuss a list of the most commonly used objects of Excel VBA in detail.

1. Application Object

The Application object is one of the most widely used objects of Excel. It is used to represent the total Excel application.

We need to add the required property or method while applying this object in Excel.

Example 1:

Here, we used the Calculate method. This macro is used for the calculation of all open workbooks.

Sub Calculate_All_Opened_Workbooks()
Application.Calculate
End Sub

Example 2:

In the below example, we used the DisplayScrollBars property with the Application object. The purpose of this macro is to hide the scroll bar.

Sub Hide_Status_Bar()
Application.DisplayScrollBars = False
End Sub

Here, we put status False, which means it will not display the scroll bars of the Excel sheet.

Read More: How to Open Workbook from Path Using Excel VBA (4 Examples)

2. Workbooks Object

Workbooks object is related to the workbook. It denotes the list of presently opened workbooks on an Excel application.

Example 1:

Here, we applied a simple VBA code based on the Workbooks object that will close the Excel workbook.

Sub Close_All_Opened_Workbooks()
Workbooks.Close
End Sub

Example 2:

This example will add a new variable page_1 on the Disney.xlsx workbook.

Sub Add_Variable_to_Specific_Workbook()
Set page_1 = Workbooks.Item("Disney.xlsx")
End Sub

3. Workbook Object

The Workbook object represents a single workbook. It is a member of Workbooks that are currently active or open. Rather a workbook is a collection of worksheets.

Example 1:

We want to close the current workbook.

Sub Close_Single_Workbook()
ActiveWorkbook.Close
End Sub

We applied a similar code to the close workbook. The Workbooks object is applied to all the opened workbooks. But the Workbook object is applicable only to the active workbook.

Example 2:

In this example, we will name a cell using the Workbook object.

Sub Name_A_Cell()
ActiveWorkbook.Names.Add Name:="myName", RefersToR1C1:="=Sheet1!R5C5"
End Sub

4. Sheets Object

The Sheets object is related to all kinds of sheets of the specified or active Excel workbook. Sheets may be worksheets, chart sheets micro sheets.

Example 1:

This VBA code will activate the 2nd sheet of the workbook.

Sub Activate_Workbook()
Worksheets(2).Activate
End Sub

Example 2:

In this example, we will add a new sheet after the 1st sheet.

Sub Add_New_Sheet()
Sheets.Add after:=Sheets(1)
End Sub

5. Worksheets Object

This Worksheets object is a part of the Sheets object. It is the collection of only the worksheets. But the Sheets object also includes chart sheets and micro sheets.

Example 1:

It will activate the 2nd worksheet of the following workbook

Sub Activate_Worksheet()
Worksheets(2).Activate
End Sub

We may also use the Sheets object. But if we use the Sheets object, that may activate a chart or micro sheet also depends on the location of the specified workbook.

Example 2:

We will copy a sheet on our desired location on the workbook.

Sub Copy_A_Worksheet()
Worksheets("Disney").Copy Before:=Worksheets("Sheet1")
End Sub

Similar Readings

• 22 Macro Examples in Excel VBA
• 20 Practical Coding Tips to Master Excel VBA
• How to Write VBA Code in Excel (With Easy Steps)
• Types of VBA Macros in Excel (A Quick Guide)
• Introduction to VBA Features and Applications

6. Worksheet Object

The Worksheet object is a part of the Worksheets. It represents a single worksheet only. This section will show a sample VBA code based on the Worksheet object that renames a worksheet.

Example 1:

The name of the active worksheet will change after applying this VBA code.

Sub Rename_A_Worksheet()
ActiveSheet.Name = "Data Set -2"
End Sub

Example 2:

We want to know about the current worksheet. Apply the following VBA code.

Sub Show_Worksheet_Name()
MsgBox ActiveSheet.Name
End Sub

7. Range Object

The Range object is related to cells of the Excel file. It is used to select a single cell, row, column, or a certain number of cells, rows, or columns from an Excel Worksheet. We have to put the cell reference in the argument.

Example 1:

This is a sample VBA code, that selects cells of range B5:D5.

Sub Select_A_Range()
Range("B5:D5").Select
End Sub

Example 2:

This example will copy a certain range from the active sheet.

Sub Copy_A_Range1()
Range("A1:E1").Copy
End Sub

8. Shapes Object

The Shapes object is related to all shapes that exist in a worksheet. We can select and delete or perform other tasks using this object.

Example 1:

This VBA code will select all kinds of shapes from a worksheet.

Sub All_Shapes_of_A_Worksheet()
ActiveSheet.Shapes.SelectAll
End Sub

Example 2:

In this example, we will apply the desired action to the existing shapes of the active worksheet.

Sub Apply_A_Procedure_on_Shapes()
ActiveSheet.Shapes(1).OnAction = "ShapeClick"
End Sub

9. Shape Object

The Shape object is a part of the Shapes. It indicates a single shape in an active worksheet. It is used with the Shapes object.

Example:

This simple VBA code is applied to create a star with 5 edges.

Sub Create_A_Shape()
ActiveSheet.Shapes.AddShape msoShape5pointStar, 300, 100, 60, 60
End Sub

We can draw any kind of shape by changing the msoShape5pointStar command.

10. ListObject Object

ListObject is a part of ListObjects Object. A ListObject indicates a single table of the worksheet.

Example:

This example is to extract data from a table and store it in the array.

Sub Store_Data_From_Table_To_Array()
Dim D_Table As ListObject
Dim D_Array As Variant
Dim N As Long
  Set D_Table = ActiveSheet.ListObjects("My_Data")
  D_Array = D_Table.DataBodyRange
  For N = LBound(D_Array) To UBound(D_Array)
    Debug.Print D_Array(N, 2)
  Next N
End Sub

Read More: Excel VBA to Populate Array with Cell Values (4 Suitable Examples)

Conclusion

In this article, we described the commonly used Excel VBA objects list. I hope this will satisfy your needs. Please have a look at our website ExcelDemy.com and give your suggestions in the comment box.

Related Articles

• What You Can Do with VBA (6 Practical Uses)
• [Fixed!] Border Not Showing in Excel (6 Solutions)
• How to Use Select Case Statement in Excel VBA (2 Examples)
• 6 Best Excel VBA Programming Books (For Beginners & Advanced Users)
• How Different Is VBA from Other Programming Languages
• Learn Excel VBA Programming & Macros (Free Tutorial – Step by Step)