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?
An Object is a command or something that applies in a VBA code to perform some specific tasks.
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.
Excel has many objects. Most of the objects in Excel VBA we work with have properties.
- 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:
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.
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:
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.
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.
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
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.
2. Workbooks Object
Workbooks object is related to the workbook. It denotes the list of presently opened workbooks on an Excel application.
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
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.
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.
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.
This VBA code will activate the 2nd sheet of the workbook.
Sub Activate_Workbook() Worksheets(2).Activate End Sub
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.
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.
We will copy a sheet on our desired location on the workbook.
Sub Copy_A_Worksheet() Worksheets("Disney").Copy Before:=Worksheets("Sheet1") End Sub
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.
The name of the active worksheet will change after applying this VBA code.
Sub Rename_A_Worksheet() ActiveSheet.Name = "Data Set -2" End Sub
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.
This is a sample VBA code, that selects cells of range B5:D5.
Sub Select_A_Range() Range("B5:D5").Select End Sub
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.
This VBA code will select all kinds of shapes from a worksheet.
Sub All_Shapes_of_A_Worksheet() ActiveSheet.Shapes.SelectAll End Sub
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.
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.
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
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.