Excel Objects in VBA

What are Excel Objects?

The Excel objects belong to the entities that make up an Excel Workbook, Worksheets, Columns, Rows, Cell Ranges, etc. Each object in Excel has loads of Properties that are stored as a part of that object. For example, an Excel Worksheet's properties include the Worksheet's Name, Protection, Visible Property, Scroll Area, etc., The primary benefit of an object is that it hides the implementation details. When an item is added, it must allocate memory, add the item, update the item count, and soon.

Although VBA is not a truly object-oriented programming language, it does deal with a project. VBA object has specific functions, properties, and can contain data or child objects. Therefore, if, during the execution of a macro, we wanted to hide an Excel worksheet, we could do this by accessing the Worksheet object and altering the 'Visible' property.

VBA has a particular type of object, called a Collection. The Collection, as the name suggests, refers to a group (or collection) of Excel objects. For example, the 'Rows' collection is an object containing all the rows of a Worksheet.

Object Components

The following are essential components of an object.

1. Property–Allows us to read a value from the object or write a value to the project.

2.Method–They are used to some action to do with the object data.

3.Event–Events occur when a code is executed.

A Real World Analogy

Take an example of House. House is an Object, And Windows and Doors are its’ child objects. Colors, Height, Floors are properties of House, and they also have some Events, such as Door Open, Door Close, etc. Likewise, An Excel worksheet is an object, and a Range of Cells in the worksheet are child objects of the worksheet. A Worksheet contains several Properties, Methods, and Events.

Accessing Excel Objects

The significant Excel Objects can all be accessed (directly or indirectly) from the 'Workbooks' object, which is a collection of all the currently open Workbooks. Each 'Workbook' object contains the 'Sheets' object which consists of all the Worksheets and Chart sheets in the Workbook, and in turn, each 'Worksheet' object contains a 'Rows' object which further consists of all Rows in the Worksheet and a 'Columns' object including of all Columns in the Worksheet.

The following are the lists of some of the more commonly used Excel objects.

1. Properties: All Objects are accessed via Properties. When using Workbook, Workbooks are the property of the Application (Object). For example, the Workbook object has the properties 'Name', 'Revision Number', 'Sheets', and many more. In the above example, a Range Object is having properties like Value, Wrap Text, etc … If, during the execution of a VBA macro, the user wanted to hide an Excel worksheet, he could do this by accessing the Worksheet object, and adjusting the 'Visible' property.

Objective: Assign the current active Workbook name to the variable vbSheetName, and displaying the value in A1 cell with wraptext property, and we could use the following code:

Program

Sub Example()
Dim wbSheetName As String
wbSheetName  = ActiveWorkbook.Name
Range(“A1”).Value= wbSheetName 
Range(“A1”).WrapText= True
End Sub 

• Methods: The action (functions or subs) that can be performed on an Object. VBA methods perform specific actions. Object methods are procedures that are connected to a certain object type. For example, the Workbook object has the methods 'Activate', 'Save', 'Close', and a lot more.

Objective: Write “Hello World! ” in cell A1 and select and copy the selection and paste it in “A1: A10”, cells, respectively.

Program

Sub Method_Macro()
Range("A1").Select
Selection.Copy
Range("A1:A10").Select
ActiveSheet.Paste
End Sub 

Output

• Application: Excel VBA Application Object is one of the frequently used objects which helps in automating any task with VBA. It is used to refer to different Excel applications and perform various operations on Excel Workbooks. It represents the current excel application.

Objective: Run a code where the font of the active cell of the application is bold.

Program

Sub Application_Macro()
Range("A1").Value = "Hello World"
Application.ActiveCell.Font.Bold = True
End Sub 

Output

• Workbooks: Workbooks are one of the most common Excel objects. Whatever you do in Excel takes place in a workbook, which is stored in a file that, by default, has an XLSX extension. An Excel workbook can hold any number of sheets (limited only by memory). A Workbook object can be accessed from the Workbooks Collection by using a Workbook index number or a Workbook name.

There are four types of sheets in VBA which are as follows:

• Worksheets
• Excel 4.0 XLM macrosheet. It has become obsolete but is still supported in various industries.
• Chart sheets
• Excel 5.0 dialog sheet (obsolete, but still supported)

The user can use 'ActiveWorkbook' to access the current active Workbook or can also access the Worksheets object, which is a collection of all the Worksheets in the Workbook.

Objective: Print the active workbook name in the A1 cell.

Program

Sub Workbook_Example()
Dim wbSheetName As String
wbSheetName  = ActiveWorkbook.Name
Range(“A1”).Value= wbSheetName 
End Sub 

Output

• Worksheets: When it comes to think of a spreadsheet, the most common type of sheet is a worksheet. Worksheets contain cells, and the cells store data and formulas. A worksheet cell can hold a constant value– a number, text, a date/time, a Boolean value (True or False), or the result of a function/formula. The user can also use 'ActiveSheet' to access the current active Sheet.

Objective: Print the active sheet name in the A1 cell.

Program

Sub Worksheet_Macro()
Dim wsSheetName As String
wsSheetName = ActiveSheet.Name
Range("A1").Value = wsSheetName
Range("A1").WrapText = True
End Sub 

Output

• Range : The range is another most frequently used object when automating tasks with VBA. A Range object can refer to different Ranges in Excel Worksheets and can perform a different task.  

A range can be specified by either a cell range with particular start and end cell (e.g. Range("A1:A10") or Range("A1", "A10") or Range(Cells(1,10), Cells(1,210)).

Objective: Select the value of range A1 and paste it in the cells A1: A10 by using Range object.

Program

Sub Range_Macro()
Range("A1").Select
Selection.Copy
Range("A1:A10").Select
ActiveSheet.Paste
End Sub 

Output

• Variable & Constant: A variable is used to accommodate a wide variety of data types -from simple Boolean values (True or False) to large, double-precision values. Whereas constant refers to a named memory location used to hold a value that CANNOT be changed during the script execution. If a user tries to change a Constant value, the script execution ends up with an error. Constants are declared the same way the variable are declared

Rules for declaring variables or constant variables

• Alphabetic characters, numbers, and some punctuation characters, are used but the first character must be alphabetic.
• Space or periods are not recommended and used. Instead, use underscore character.
• Special characters ($, #, %, & or !) are also not allowed in a variable name.
• Variable names can be as long as 254 characters –but using such a long name isn’t suggested.

• Data Types: It refers to how data is stored in memory —as integers, real numbers, strings, and so on. Although VBA takes care of data typing automatically, it does so at a cost: slower execution and less efficient use of memory and when running large or complex codes can present problems when VBA itself handles data types.

Data Type	Bytes	Range
Byte	1 byte	0 to 255
Boolean	2 bytes	True, False
Integer	2 bytes	-32768 to 32767
Long	4 bytes	-2147483648 to 2147483647
Single	4 bytes	3.402823E38 to –1.401298E-45 (for negative values); 1.401298E-45 to 3.402823E38 (for positive values)
Double	8 bytes	–1.79769313486232E308 to –4.94065645841247E-324 (negative values) and 4.94065645841247E-324 to 1.79769313486232E308 (positive values)
Currency	8 bytes	922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal	12 bytes	+/–79,228,162,514,264,337,593,543,950,335 with no decimal point;+/–7.9228162514264337593543950335 with 28 places to the right of the decimal
Date	8 bytes	January 1, 0100 to December 31, 9999
String (variable length)	10 bytes + String length	0 to approximately 2 billion characters
String (fixed length)	Length of string	1 to approximately 65,400 characters
Variant (with numbers)	16 bytes	Any numeric value up to the range of a double data type. It can also hold special values, such as Empty, Error, Nothing, and Null.
Variant (with characters)	22 bytes + string length	0 to approximately 2 billion
User-defined	Varies	Varies by element