Excel VBA objects are code-and-data “entities.” Objects are helpful because they can contain other objects. Workbooks, spreadsheets, cells, ranges, rows, columns, and shapes make up Microsoft Excel. Objects have properties and methods.
Visual Basic isn’t an object-oriented language, but it handles projects. The VBA object has functions, properties, data, and child objects. Excel objects have several characteristics. VBA objects hide implementation details. Memory is allocated when a VBA element is inserted.
Components of Object
The components of an object are helpful in a programme. The following are the three main parts of the object.
- Property: It lets the user read a value from the object or write it in the application window.
- Method: They help to perform some actions with the object data. The procedures are carried out within a method.
- Event: When VBA code runs, events occur.
Types of VBA Objects
There are four main objects that a user will interact with when working with VBA programming.
- Application Objects
- Workbook Objects
- Worksheet Objects
- Range Objects
See More: VBA Workbooks Open
1. Application Objects
When creating macros in Visual Basic for Applications (VBA), the most popular object to work with is the Excel VBA Application Object. Applications objects allow you to conduct a wide range of tasks on Excel files using a variety of applications. It has the following characteristics:
- Application-wide settings and options.
- Various VBA Methods return ActiveCell, ActiveSheet, and other objects.
Example
Write a programme to open a workbook.
Sub Excel_Workbooks_Object_Example1()
Dim oXL_App As Object
Set oXL_App = CreateObject("Excel.Application")
oXL_App.Workbooks.Open Filename:="D:\test.xlsx"
End Sub
2. Workbook Objects
In Excel, the workbook object is also quite common. Workbooks (with the XLSX file extension) are where Excel’s magic happens (by default). Each object associated with the active Workbook in Microsoft Excel is stored in the Workbook object, which is a part of the Workbook collection. A workbook in Excel can have n sheets (the sheets are only restricted by memory). Excel provides users with four distinct kinds of sheets, which are as follows:
- Excel Worksheets
- Excel 4.0 XLM macro sheet
- Chart sheets
- Excel 5.0 dialogue sheet
Example
Use VBA to make a programme to close the workbooks.
Sub Excel_Workbooks_Object_Example2()
Dim oXL_App As Object
Dim wb As Workbook
Set oXL_App = CreateObject("Excel.Application")
Set wb = oXL_App.Workbooks.Open(Filename:="D:\test.xlsx")
wb.Close False
End Sub
3. Worksheet Objects
The worksheet is the most common sort of sheet used in spreadsheets. Each cell on a worksheet serves as a storage location for the worksheet’s associated data, numbers, comments, and formulas. In a worksheet cell, you can enter numeric data, text, dates and times, Boolean values (True or False), or the result of a calculation or function. All worksheets in a given workbook are stored in the Worksheets collection, which is a part of the Worksheet object.
Example
Create a programme to hide your worksheet using the worksheet object, as shown in the example.
Sub Excel_Worksheet_Object_Example()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet7")
ws.Visible = xlSheetVisible
End Sub
4. Range Objects
The range is the most frequently utilised object that aids in automating VBA activities when working with Excel sheets. A Range object can execute various operations on several Ranges in an Excel Worksheet. Each cell or range of cells on a worksheet is represented by a Range object contained within a Worksheet object.
Below are three ways to refer to Range objects in VBA code.
The Range property of a Worksheet or Range class object
The Cells property of a Worksheet object
The Offset property of a Range object
Example
Write a program to put a value in cell A5 with a range object.
Sub Excel_Range_Object_Example()
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("Sheet7").Range("A1:A10")
rng.Value = "Test"
End Sub
