VBA Range Object
Job-ready Online Courses: Click, Learn, Succeed, Start Now!
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:
Range For selecting Cell | Range Declaration |
Single Row | Range(“1:1”) |
Single Column | Range(“A:A”) |
Contiguous cells | Range(“A1:D2”) |
Non – Contiguous cells (Note: There is a comma operator in between the two ranges) | Range(“A1:C2, D2:D5”) |
Intersection of two ranges (Note: There is no comma operator in between the two ranges) | Range(“A1:C2 D2:D5”) |
Merge Cells (While merging the cells, use merge command also) | Range(“A1:D5”) |
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.
Did you know we work 24x7 to provide you best tutorials
Please encourage us - write a review on Google