Excel VBA Objects
Excel VBA objects is single “entities” constructed with code and data. The reason why Objects are used in a programming language us because Objects can also contain other objects. The Microsoft Excel application is also an object made up of different objects such as workbooks, spreadsheets, cells, ranges, rows, columns, and shapes. Every object has related properties and methods stored as a part of that object…
Although Visual Basic is not a complete object-oriented programming language, but it deals with a project. The VBA object has associated functions and properties and can further contain data or child objects. Each object in Excel has plenty of properties stored as a part of that object. The advantage of using a VBA object is that it hides the implementation information. When an element is added in a VBA procedure, memory space is allocated to it by default.
This tutorial will discover the most commonly used Objects in VBA.
Object Components
Object has its components that are used in a program. Below given are the three important components of an object.
- Property – This enables the user to read a value from the object or write the value in the application window.
- Method – They are used to perfume some actions with the object data. The tasks are executed inside a method.
- Event – Events occur whenever any VBA code is executed.
A Real World Analogy
Take a real-life example of Building. A building is an object made up of Doors, Rooms, Windows, Walls, etc. All the components of the building are its child objects. Next, each child has some of its properties (for example: Colors, Tiles, Floors), and it also has some Events (for example: Window Open, window Close, etc.). Similarly, the Microsoft Excel worksheet is an object, and a Range of Cells present in that worksheet are its child objects. Further, a Worksheet extends and contains different Properties, such as Methods and Events.
Properties
When using Workbook, Workbooks are property of the Application (Object). All Objects are accessed via Properties. In the below example, a Range Object has properties like Value, WrapText, etc.
Program: Write a VBA code, to add a Value to Range A1 and later use the WrapText property to the similar range.
Methods
Methods are defined as the actions (functions or subs) that can be executed on an Object.
For instance: If you wish to select a range in your Excel worksheet, you will use the Select method, and if you want to copy a range, you will use the Copy method.
Program: Write a method to copy the data from a selection and paste it to another row
Events
Whenever you want to run an event the functions or subs that are triggered.
Program: Write a code to display a message box event.
Types of VBA objects
While working with VBA programming, there are four primary objects that a user would be commonly dealing with.
- Application Objects
- Workbook Objects
- Worksheet Objects
- Range Objects
Application Objects
Excel VBA Application Object is the most commonly used object when you want to automate your job using VBA macros. Applications objects are used to refer to various Excel applications and perform different operations on Excel Workbooks. It contains the below given features:−
- Application-wide settings and options.
- Different VBA Methods return objects, such as ActiveCell, ActiveSheet, etc.
Example 1: Write a program to create an application object to open a workbook.
Example 2: Write a program to create an application object to activate a window.
Example 3: W rite a program to using application object make the font bold of the active cell.
Workbook Objects
The workbook object is another frequently common Excel object. Whatever job you perform in Excel takes place in a workbook, which is held in a file with an XLSX extension (by default). The Workbook object is a component of the Workbooks collection and includes all the objects (related to the Workbook) presently open in Ms Excel.
An Excel workbook can hold n number of sheets (the sheets are only restricted by memory). There are four types of sheets available in Excel that are as follows:
- Excel Worksheets
- Excel 4.0 XLM macro sheet (obsolete, but still supported)
- Chart sheets
- Excel 5.0 dialog sheet (obsolete, but still supported)
Example 1: Write a program in VBA to close the workbooks.
Example 2 : Write a program to add an Empty Work Book using workbooks object.
Example 3: Write a program to Open a Workbook using workbooks object.
Example 4 – Write a program to Activate sheet1 in the Workbook.
Worksheet Objects
When working with a spreadsheet, the most preferred sheet type is a worksheet. Worksheets contain several cells, and a cell is a place that keeps all the data, numbers, comments, and formulas. A worksheet cell can store constant data – a number, string, a date/time, a Boolean value (True or False), or the output of the specified function/formula.
The Worksheet object is a component of the Worksheets collection and holds all the Worksheet objects used in a workbook.
Example 1: Write a program to make your worksheet invisible using worksheet object
Example 2: Write a program to protect a WorkSheet using worksheet object
Range Objects
Range is the most commonly used object that helps to automate VBA tasks when you are working with Excel worksheets. A Range object can refer to different Ranges in Excel Worksheets and can perform a different task.
Range Objects are defined as a cell, a row, a column, or a selection of cells holding one or more contiguous blocks of cells. A Range object is contained in a Worksheet object and contains a single cell or range of cells on a single worksheet.
Ways to refer Range object in VBA
Below given are three different methods to refer 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
Program:
Example 1 : Write a program to Put a value in the cell A5 using range object
Example 2 : Write a program to put a value in range of Cells using range object.
Declaring and Assigning an Object Variable
In VBA the objects are declared and assigned to a variable by using the Dim and Set keywords.
For instance:
Explanation: Here, we have declared the ‘wrksht’ as the worksheet variable using the Dim keyword. Later we have assigned the current worksheet of the active workbook to the ‘wrksht’ object variable by using the Set keyword.
Program: Write the VBA macro code to change the font style, font color, Interior color, font size of a range.
