VBA Range Object

What is the VBA Range?

A cell or multiple cells in excel are represented by VBA range objects. It is one of the important objects in VBA. A range object can represent either a single cell or multiple cells such as a selection of cells, row or column of columns, 3-d range.

Need of Excel VBA Range:

The VBA range object helps the VBA to identify what data from the worksheet needs to be involved and executed in the recorded macro. This is where the VBA range object comes into action as it says which all data in the worksheet should take part in it.

Introduction to Referencing Objects in VBA

1. Object Qualifier
The user uses object qualifier for referencing the object and this specifies the worksheet referring to.
Properties and methods are used to manipulate the cell values.

2. Property
It stores information about the object

3. Method

It is an action of the object which it performs. Range objects can perform actions such as selected, copied, cleared, sorted etc.

Object Hierarchy Structure

VBA follows a hierarchy pattern to refer to objects in excel. The user has to follow the following structure to refer to objects.

Note: The ‘.’ dot connects the objects at each different level such as Application.Workbook.Worksheets.Range

How to refer to Excel VBA Range Object using Range property?

Range property applies in two different types of objects and they are:

• Worksheet Objects
• Range Objects

Range Property Syntax:

Application.Workbooks("DataFlair.xlsm").Worksheets("Articles").Range("A1")

• Add the keyword “Range”
• The keyword should be followed by parentheses.
• The relevant Cell Range between the quotations(“ ”).

Fully qualified reference

When you refer to a Range object, it refers to fully qualified reference. Here, the user is exactly telling which range they want, what sheet and in what worksheet.

Example: MsgBox Worksheet(“Articles”).Range(“A1”).Value
Using Range property, the user can perform many tasks such as,

• Referring to a Single cell
• Referring to multiple cells such as an entire row or column.
• Using Worksheet.Range Property, the user can refer to merged cells also.

There are many scenarios and few of them are stated above.

Refer to a single cell using range property

Let’s see an example referring to a single cell using the range property.

To refer to a single cell, follow the syntax: Range(“cell”).

Here, the user will use the “.Select” command to select the single cell from the worksheet.

To add this in the VBE, follow the steps:

Step 1: Open an excel workbook.

Note: Save the file as an Excel macro-enabled workbook.

Step 2: Use macros to add the button.

In order to create a macro,

• Select insert from the ribbon under the developer tab.
• Click on the command button.
• Provide a name to the macro and click on the new button

Step 3: Finally, press the ok button.

Step 4: Type the following code in the code window.

Code:

Sub DataFlair_Range()
Range("A1") = "Welcome to DataFlair!"
End Sub

Code Explanation:
Here, Sub DataFlair_Range() is the sub procedure.

Range(“A1”) = “Welcome to DataFlair!” denotes that when the code is executed, the A1 cell should contain the value “Welcome to DataFlair!”

Output:
The result will appear in the A1 cell as “ Welcome to DataFlair! ”

The user writes the code in the VBA editor.

Step 5: Click on the save button or press ctrl+s.

Step 6: Close the VBA code editor window.

Your GUI appears as follows:

Once you click on the button, you will get the output:

The other ranges:

Cell Property

The user uses cell property especially for the purpose of loop programming. Cell property is similar to range and the user can use cell property instead of range. The one main difference is that the cell property has an “item” property which the user uses to reference the cell on the worksheet.

Cell property syntax:

Cells.item(Row, Col). 

Here, the row refers to that particular row and the column refers to that particular column in the worksheet.

Example: Cells.item(1,1) , Cells.item(1,”A”)

Cell Property Code Sample:

Sub DataFlair_Range()
Cells.Item(2, "A") = "Welcome to DataFlair!"
End Sub

Code Explanation:
Cells.Item(2, “A”) = “Welcome to DataFlair!” denotes that when the code is executed, “Welcome to DataFlair!” should appear in the second-row first column i.e., in A2 cell.

When the user runs the code, the following output appears.

VBA Range Offset property

The user uses the range offset property to select the rows or column away from its original position. The cells are selected according to the range declaration.

Sample Code:

Sub DataFlair_Range()
Range("B1").Offset(Rowoffset:=1, Columnoffset:=1) = "Welcome to DataFlair!"
End Sub

Code Explanation:
Range(“B1”).Offset(Rowoffset:=1, Columnoffset:=1) – This code leads to the C2 cell. The offset property will move the B1 cell to one column and one row away. The given input information value appears in the C2 cell.

Note: The user can also use negative values to move the cells backwards and they can also change the row offset and column offset value as per the requirement.

VBA – Excel Objects

A VBA object in excel can have more than one object. A single workbook can have more worksheets. Workbook, worksheet, range and cells are some of the objects in MS – Excel. Every object in the sheet has its own properties and they all are executed for different applications.

VBA Object Code

Sub Onj()
Dim DataFlair As Object
Set  DataFlair = CreateObject("Excel.Sheet")
DataFlair.Application.Visible = True
DataFlair.Application.Cells(1, 1).Value = "Welcome to DataFlair!"
End Sub

There are other important objects that a user may deal with and they are

• Application Objects
• Workbook Objects
• Worksheet Objects
• Range Objects

Application Objects

In an application object, the application-wide settings and options are available. There are also methods that return top-level objects.

Application Object Sample

Set dataflair_app = CreateObject("Excel.Sheet") 
dataflair_app.Application.Workbooks.Open "C:\website.xls"
Application.Windows("website.xls").Activate
Application.ActiveCell.Font.Italics = True

Workbook Objects

The workbook object is opened in Microsoft Excel and it is one of the workbooks collection.

Workbook Object Sample

'To open a workbook
Workbooks.Open FileName:="Website.xlsx", ReadOnly:=True

Worksheet Objects

The worksheet object is opened in Microsoft Excel and it is one of the worksheets collection.

Worksheet Object Sample

'To protect a WorkSheet
Worksheets("Sheet1").Protect password:=DataFlair, scenarios:=True

Range Objects

The Range Objects represent a cell, a row, a column, or a selection of cells containing one or more continuous blocks of cells.

Range Object Sample

'To insert a value in the range of cells A1:A10
Worksheets("Sheet1").Range("A1:A10").Value = "123"

Select

Select method is one of the important methods in the range object. This method simply selects the range.

Code:

Sub DataFlair_Range()
Dim DataFlair As Range
Set DataFlair = Range("A1: A10")
DataFlair.Select
End Sub

Result:

Note: To choose a cell on a different worksheet, the user has to activate the sheet first.
For example, the following code lines select cell A1 on the second worksheet from the left.
Worksheets (2). Activate
Worksheets (2). Range (“A1”). Select

Rows

The Rows property in VBA gives access to the specific row in a sheet.

Code:

Sub DataFlair_Range()
Dim DataFlair as Range
Set DataFlair = Range (“A1: A10”)
DataFlair.Rows(1) .Select 
End Sub

Result:

Columns

The columns property in VBA gives access to the specific column in a sheet.

Code:

Sub DataFlair_Range()
Dim DataFlair as Range
Set DataFlair = Range (“A1: B10”)
DataFlair.Columns(2) .Select 
End Sub

Result:

Copy/Paste

To copy a range, we use the copy method. To paste the copied content somewhere on the sheet, we use the paste method.

Code:

Sub DataFlair_Range()
Range (“A1: A10”).Select
Selection.Copy
Range(“C3”).Select
ActiveSheet.Paste
End Sub

Result:

Clear

The user can use the ClearContents method to clear the contents of a range in the sheet.

Code:

Code: 
Sub DataFlair_Range()
Range (“A1”) .ClearContents
End Sub

Result:

Count

The user can count the number of cells, rows and columns of a range using the count property.

Code:

Sub DataFlair_Range()
Dim DataFlair As Range
Set DataFlair = Range(“A1:A10”)
MsgBox DataFlair.Count
End Sub

Result:

Rows Count

The user can count the number of cells in the row using the count property.

Code:

Sub DataFlair_Range()
Dim DataFlair As Range
Set DataFlair = Range(“A1:A10”)
MsgBox DataFlair.Rows.Count
End Sub

Result:

Columns Count

The user can count the number of cells in the column using the count property.

Code:

Sub DataFlair_Range()
Dim DataFlair As Range
Set DataFlair = Range(“A1:B10”)
MsgBox DataFlair.Columns.Count
End Sub

Result:

Pros and Cons of Excel VBA Object

• The user can make as many objects as required and can link them together to sink it.
• VBA objects make use of the workbooks, sheets and range.
• The VBA object allows a user to make changes in a specific workbook, worksheet or range.

Things to remember in VBA object

• Both the term worksheet as well as sheet has the same use.
• The user can select any worksheet of the same workbook of any number sequence.
• The user must save the file in Macro Enabled Excel format. It allows the user to use the code and change the code whenever required.

Excel VBA Named Range

The named range is a range with specific names and it helps the user to identify which part of the range contains which data.

Let us first know how to name a range in excel

To name a group of cells in excel, the user must select the ranges as follows:

Note: There is a box where it is mentioned as A1. The user can click on that particular box and can provide a name.

The box is called a name box and the user uses it to give a name to the selected range.

Note: The user can also name the ranges using VBA too but it is a bit more lengthy process than above.

How to Use Named Range in Excel VBA?

There are two ways to use the Named Range in Excel VBA:

1. First is that the user can name a range in excel and then use it in VBA.
2. The second method is that the user can make a named range in VBA itself and use its properties.

Named Range in VBA

To name a cell range, follow the steps.

1: Select the range which you want to name.

2: There is a name box above the range, the user can make use of it to provide a name to the range.

Note: Here, I have named the range as DataFlair.

3: Now go to the Developer’s tab from the menu.

4: Click on Visual Basic to get into VBA.

5: Insert a module so that you can write code in it.

6: Write the code as follows.

Code:

Sub DataFlair_Rangename()
Worksheets("Sheet3").Activate
Range("DataFlair").Value = 17
End Sub

Result:
Once you run the above code, you can see that every cell in our named range has the same value

Cell vs Range

The user can refer to the cells using two methods. Cell property and range property are the two different methods and now let’s see the differences between those similar properties.

• The cell property can select only one cell at a time i.e., Cells(3,3). Here, it chooses the third row’s third column, which is the C3 cell. If the user wants to select more than one cell together, then the CELLS property cannot do this task.
• The range property can select more than one cell at a time, i.e., Range(“A1:A10”). Here, it means that the cells from A1 to A10 are selected.

Summary:

The VBA Range Object helps to represent a cell or multiple cells in the worksheet. The range objects are specially used for the selection of cells. To manipulate cell values, the user can use property and methods.