Set range variable using VBA in Excel
Setting a range variable is done by using a procedure that returns a range. Because range are the core of Excel VBA provides many such procedures. The major ones are discussed here.
| Note |
|---|
| Setting a range normally does not change the active range. This only happens with the Select and Activate methods — whose use is generally considered harmful / should be avoided, see. . |
Using the Range methods
Application.Range — general use
The code below returns [Book2]Sheet2!$A$3:$B$5 in the Immediate window. It shows you can specify any range in an open workbook on any worksheet.
If in the above you leave out the worksheet or workbook the Application.Range method will assume the active one:
Application.Range(«Sheet2!A3:B5») — in the active workbook
Application.Range(«A3:B5») — in the active worksheet
The argument Cell1 requires the name of the range. It can be a Range object that contains a single cell, an entire column, or entire row, or it can be a string that names a single cell in the language of the macro.
| Note |
|---|
| If the workbook or worksheet is not present running the code will give runtime Error 1004 “Application-defined or Object-defined error” |
Range — topleft and bottomright
Range has two arguments which are used to indicate the area in a worksheet. In the previous section only Cell1 was used. If the Optional Cell2 argument is also used Cell1 is the cell in the upper-left and Cell2 in the lower-right corner of the range.
Select Range using the range selection dialog
When you need to specify the range, the easiest is to use the Code VBA range selection dialog. On opening this only shows the range part $B$2. You can include the sheetname Data! by explicitly switching to that sheet.
Worksheet.Range
In the previous section the the .Range property was called from the Application object. When writing business code it generally is better to be specific about which workbook and which sheet you are operating on. These will be normally be already be available as variables in your code. Below example uses the named range «Company» which makes it clearer what data is contained in the range.
Worksheet.UsedRange
UsedRange returns a Range object representing the area of a worksheet — read more . — that is being used in a broad sense including data, formatting and other uses.
Set Range from another Range
Considering that a range object is the most specific indication of where to start looking, there are quite a few methods that use it to obtain a new range.
Range.Offset
Set a range with position relative a starting range. Positive values are offset downward, and negative values are offset upward. As an example, the code below prints $D$3 . 2 rows down (RowOffset) and 3 columns to the right (ColumnOffset) of the start position A1.
Range.Resize
Range.Resize resizes the specified range. RowSize specifies the number of rows in the new range, ColumnSize the number of columns. When either argument is omitted, the number of rows or columns in the range remains the same.
Use Resize to remove the top row
The code below removes the top row. This is useful when you need work with a table, without the header row.
The resize which is one row smaller Rows.Count — 1 would return the current range minus the bottom row. That’s why in the above code, first the range is taken 1 row down rng.Offset(1, 0) .
Selection in active worksheet — or window
In general purpose macros — such as copy to clipboard, which are supposed to work on any range, the common way to refer to the active range is Selection:
The returned object type depends on the current selection. For example, if a cell is selected, this property returns a Range object, if however a rectangle-Shape is selected an object of type Rectange is returned. If this is a possible scenario, two approaches are possible, discussed in the next sections.
Handle error 13: Type mismatch
If a shape was selected instead of a range, using the above code with no error handling in place would result in the error dialog, and the user even maybe entering your code. To prevent this, you should add error handling.
Check if selection has correct object type
alternatively, you can use an object variable, and make sure that it is the correct type before continuing.
Intersection of two or more ranges
Application.Intersect returns a Range object that represents the intersection of two or more ranges. In the example below the address of rngIntersect is B2:C3 . If the ranges have no intersection the rngIntersect Is Nothing
| Note |
|---|
| If one or more ranges from a different worksheet are specified, run-time error ‘1004’: Method ‘Intersect’ of object ‘_Application’ failed will be returned. |
Union of two or more ranges.
Application.Union returns the union of two or more ranges. In the example below the address of rngUnion for A1:C3 and C3:D4 is A1:C4 and the count is 12, which suggests the range can be used as expected when iterating over cells — even though the third row was overlapping.
If the area can’t unite nicely into 1 rectangle, the address becomes compound. The union of A1:C3 and B2:D4 has the address A1:C3,B2:D4 and count is 18. This shows that the cells in the overlap are interpreted as occurring twice!
CODE VBA — AGORA Software BV Copyright 1997-2019
Analyst Cave
Excel VBA Range Tutorial
The VBA Range Object
The Excel Range Object is an object in Excel VBA that represents a cell, row, column, a selection of cells or a 3 dimensional range. The Excel Range is also a Worksheet property that returns a subset of its cells.
Worksheet Range
The Range is a Worksheet property which allows you to select any subset of cells, rows, columns etc.
Select a cell or Range of cells using the Select method. It will be visibly marked in Excel: Select a single cell using the Range object
Working with Range variables
The Range is a separate object variable and can be declared as other variables. As the VBA Range is an object you need to use the Set statement:
The Range object defaults to your ActiveWorksheet. So beware as depending on your ActiveWorksheet the Range object will return values local to your worksheet:
You might want to define the Worksheet reference by Range if you want your reference values from a specifc Worksheet:
Range properties
The Range object contains a variety of properties with the main one being it’s Value and an the second one being its Formula.
A Range Value is the evaluated property of a cell or a range of cells. For example a cell with the formula =10+20 has an evaluated value of 20 .
A Range Formula is the formula provided in the cell or range of cells. For example a cell with a formula of =10+20 will have the same Formula property.
Other Range properties include:
Work in progress
Worksheet Cells
A Worksheet Cells property is similar to the Range property but allows you to obtain only a SINGLE CELL, based on its row and column index. Numbering starts at 1: Select a single Cell using the Cells property
The Cells property is in fact a Range object not a separate data type.
Excel facilitates a Cells function that allows you to obtain a cell from within the ActiveSheet, current top-most worksheet.
Cells are Ranges which means they are not a separate data type:
Range Rows and Columns
As we all know an Excel Worksheet is divided into Rows and Columns. The Excel VBA Range object allows you to select single or multiple rows as well as single or multiple columns. There are a couple of ways to obtain Worksheet rows in VBA:
Getting an entire row or column
Range EntireRow property To get and entire row of a specified Range you need to use the EntireRow property. Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex it is enough just to provide the row number. Row indexing starts at 1. Range EntireColumn property To get and entire column of a specified Range you need to use the EntireColumn property. Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex it is enough just to provide the column number. Column indexing starts at 1.
Get a row/column of a specified range
Range Rows function If you want to get a certain row within a Range simply use the Rows property of the Worksheet. Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex it is enough just to provide the row number. Row indexing starts at 1. Range Columns property Similarly you can use the Columns function to obtain any single column within a Range. Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex actually the first argument you provide will be the column index. Column indexing starts at 1.
To get a range of rows/columns you need to use the Range function like so:
Get row/column of specified range
The above approach assumed you want to obtain only rows/columns from the ActiveSheet – the visible and top-most Worksheet. Usually however, you will want to obtain rows or columns of an existing Range. Similarly as with the Worksheet Range property, any Range facilitates the Rows and Columns property.
Getting a Ranges first row/column number
As >Row and Column property which provide you with the number of the Ranges first row and column.
Converting Column number to Excel Column
This is an often question that turns up – how to convert a column number to a string e.g. 100 to “CV”.
Range Cut/Copy/Paste
Cutting and pasting rows is generally a bad practice which I heavily discourage as this is a practice that is moments can be heavily cpu-intensive and often is unaccounted for.
Copy function
Range copy function The Copy function works on a single cell, subset of cell or subset of rows/columns.
The Copy function can also be executed without an argument. It then copies the Range to the Windows Clipboard for later Pasting.
Cut function
Range Cut function The Cut function, similarly as the Copy function, cuts single cells, ranges of cells or rows/columns.
The Cut function can be executed without arguments. It will then cut the contents of the Range and copy it to the Windows Clipboard for pasting.
PasteSpecial function
Range PasteSpecial function The Range PasteSpecial function works only when preceded with either the Copy or Cut Range functions. It pastes the Range (or other data) within the Clipboard to the Range on which it was executed.
Syntax
The PasteSpecial function has the following syntax:
Parameters
Paste
The part of the Range which is to be pasted. This parameter can have the following values:
| Parameter | Constant | Description |
|---|---|---|
| xlPasteSpecialOperationAdd | 2 | Copied data will be added with the value in the destination cell. |
| xlPasteSpecialOperationDivide | 5 | Copied data will be divided with the value in the destination cell. |
| xlPasteSpecialOperationMultiply | 4 | Copied data will be multiplied with the value in the destination cell. |
| xlPasteSpecialOperationNone | -4142 | No calculation will be done in the paste operation. |
| xlPasteSpecialOperationSubtract | 3 | Copied data will be subtracted with the value in the destination cell. |
Operation
The paste operation e.g. paste all, only formatting, only values, etc. This can have one of the following values:
| Name | Constant | Description |
|---|---|---|
| xlPasteAll | -4104 | Everything will be pasted. |
| xlPasteAllExceptBorders | 7 | Everything except borders will be pasted. |
| xlPasteAllMergingConditionalFormats | 14 | Everything will be pasted and conditional formats will be merged. |
| xlPasteAllUsingSourceTheme | 13 | Everything will be pasted using the source theme. |
| xlPasteColumnWidths | 8 | Copied column width is pasted. |
| xlPasteComments | -4144 | Comments are pasted. |
| xlPasteFormats | -4122 | Copied source format is pasted. |
| xlPasteFormulas | -4123 | Formulas are pasted. |
| xlPasteFormulasAndNumberFormats | 11 | Formulas and Number formats are pasted. |
| xlPasteValidation | 6 | Validations are pasted. |
| xlPasteValues | -4163 | Values are pasted. |
| xlPasteValuesAndNumberFormats | 12 | Values and Number formats are pasted. |
SkipBlanks
If True then blanks will not be pasted.
Transpose
Transpose the Range before paste (swap rows with columns).
Set range variable using VBA in Excel
Setting a range variable is done by using a procedure that returns a range. Because range are the core of Excel VBA provides many such procedures. The major ones are discussed here.
| Note |
|---|
| Setting a range normally does not change the active range. This only happens with the Select and Activate methods — whose use is generally considered harmful / should be avoided, see. . |
Using the Range methods
Application.Range — general use
The code below returns [Book2]Sheet2!$A$3:$B$5 in the Immediate window. It shows you can specify any range in an open workbook on any worksheet.
If in the above you leave out the worksheet or workbook the Application.Range method will assume the active one:
Application.Range(«Sheet2!A3:B5») — in the active workbook
Application.Range(«A3:B5») — in the active worksheet
The argument Cell1 requires the name of the range. It can be a Range object that contains a single cell, an entire column, or entire row, or it can be a string that names a single cell in the language of the macro.
| Note |
|---|
| If the workbook or worksheet is not present running the code will give runtime Error 1004 “Application-defined or Object-defined error” |
Range — topleft and bottomright
Range has two arguments which are used to indicate the area in a worksheet. In the previous section only Cell1 was used. If the Optional Cell2 argument is also used Cell1 is the cell in the upper-left and Cell2 in the lower-right corner of the range.
Select Range using the range selection dialog
When you need to specify the range, the easiest is to use the Code VBA range selection dialog. On opening this only shows the range part $B$2. You can include the sheetname Data! by explicitly switching to that sheet.
Worksheet.Range
In the previous section the the .Range property was called from the Application object. When writing business code it generally is better to be specific about which workbook and which sheet you are operating on. These will be normally be already be available as variables in your code. Below example uses the named range «Company» which makes it clearer what data is contained in the range.
Worksheet.UsedRange
UsedRange returns a Range object representing the area of a worksheet — read more . — that is being used in a broad sense including data, formatting and other uses.
Set Range from another Range
Considering that a range object is the most specific indication of where to start looking, there are quite a few methods that use it to obtain a new range.
Range.Offset
Set a range with position relative a starting range. Positive values are offset downward, and negative values are offset upward. As an example, the code below prints $D$3 . 2 rows down (RowOffset) and 3 columns to the right (ColumnOffset) of the start position A1.
Range.Resize
Range.Resize resizes the specified range. RowSize specifies the number of rows in the new range, ColumnSize the number of columns. When either argument is omitted, the number of rows or columns in the range remains the same.
Use Resize to remove the top row
The code below removes the top row. This is useful when you need work with a table, without the header row.
The resize which is one row smaller Rows.Count — 1 would return the current range minus the bottom row. That’s why in the above code, first the range is taken 1 row down rng.Offset(1, 0) .
Selection in active worksheet — or window
In general purpose macros — such as copy to clipboard, which are supposed to work on any range, the common way to refer to the active range is Selection:
The returned object type depends on the current selection. For example, if a cell is selected, this property returns a Range object, if however a rectangle-Shape is selected an object of type Rectange is returned. If this is a possible scenario, two approaches are possible, discussed in the next sections.
Handle error 13: Type mismatch
If a shape was selected instead of a range, using the above code with no error handling in place would result in the error dialog, and the user even maybe entering your code. To prevent this, you should add error handling.
Check if selection has correct object type
alternatively, you can use an object variable, and make sure that it is the correct type before continuing.
Intersection of two or more ranges
Application.Intersect returns a Range object that represents the intersection of two or more ranges. In the example below the address of rngIntersect is B2:C3 . If the ranges have no intersection the rngIntersect Is Nothing
| Note |
|---|
| If one or more ranges from a different worksheet are specified, run-time error ‘1004’: Method ‘Intersect’ of object ‘_Application’ failed will be returned. |
Union of two or more ranges.
Application.Union returns the union of two or more ranges. In the example below the address of rngUnion for A1:C3 and C3:D4 is A1:C4 and the count is 12, which suggests the range can be used as expected when iterating over cells — even though the third row was overlapping.
If the area can’t unite nicely into 1 rectangle, the address becomes compound. The union of A1:C3 and B2:D4 has the address A1:C3,B2:D4 and count is 18. This shows that the cells in the overlap are interpreted as occurring twice!
CODE VBA — AGORA Software BV Copyright 1997-2019
Объекты Range и Selection
В иерархии Excel объект Range (диапазон) идет сразу после объекта worksheet. Объект Range является одним из ключевых объектов VBA. Объект Selection (выбор) возникает в VBA двояко — либо как результат работы метода Select, либо при вызове свойства selection. Тип получаемого объекта зависит от типа выделенного объекта. Чаще всего объект Selection принадлежит классу Range и при работе с ним можно использовать свойства и методы объекта Range. Интересной особенностью объектов Range и selection является то, что они не являются элементами никакого семейства объектов.
Адресация ячеек
При работе с объектом Range необходимо помнить, как в Excel ссылаются на ячейку рабочего листа. Имеются два способа ссылки на ячейки рабочего листа: относительная адресация (т. е. когда начало координат, задающее нумерацию строк и столбцов, связывается с объектом, вызвавшим Range) и абсолютная адресация.
Имя ячейки состоит из имени столбца (их 256 — А, В, . Z, АВ, . HZ, IA, . IV) и номера (1, . 16384).
Адресация задается индексом строки и индексом столбца. Например, R1C1, R2C3
Признаком абсолютной адресации является знак «$», предшествующий имени строки (абсолютной адресации на строку) или столбца (абсолютной адресации на столбец). Например, $А10, А$10 и $А$10 задают абсолютную адресацию на столбец А, строку 10 и ячейку А10 соответственно
Указывается смещение по отношению к активной ячейке. Смещение приводится в квадратных скобках, причем знак указывает на направление смещения. Например, если активной ячейкой является касз, то R[i]C[-1] дает ссылку на ячейку кзс2
Адресация ячейки рабочего листа является лишь частью полного адреса ячейки, который в общем случае включает имя рабочего листа и адрес книги. При задании полного адреса за именем листа следует знак «!», а адрес книги заключается в скобки. Например,
В первой строке данного примера дана относительная ссылка на ячейку AI активного рабочего листа, во второй — на ячейку AI рабочего листа листа активной книги, а в третьей на ячейку AI рабочего листа лист2 книги моякнига-xls текущего рабочего каталога.
Задание групп строк и столбцов с помощью объекта Range
Если в диапазоне указываются только имена столбцов или строк, то объект Range задает диапазон, состоящий из указанных столбцов или строк. Например, Range («А: с») задает диапазон, состоящий из столбцов А, в и с, а Range <«2: 2») — из второй строки. Другим способом работы со строками и столбцами являются методы ROWS (строки) и Columns (столбцы), возвращающие коллекции строк и столбцов. Например, столбцом А является columns (1), а второй строкой — ROWS (2).
Связь объекта Range и свойства Cells
Так как ячейка является частным случаем диапазона, состоящим только из единственной ячейки, объект Range также позволяет работать с ней. Объект cells (ячейки) — это альтернативный способ работы с ячейкой. Например, ячейка А2 как объект описывается Range («A2») или cells (1,2). В свою очередь объект ceils, вкладываясь в Range, также позволяет записывать диапа зон в альтернативном виде, который иногда удобен для работы, а именно,
Range («А2:C3») И Range(Cells(1,2), Cells(3,3))
Определяют один и тот же диапазон.
Свойства и методы объекта Range
Объект Range позволяет сочетать гибкость VBA и мощь рабочего листа Excel. Более 400 встроенных функций рабочего листа существенно упрощают и делают более наглядным программирование на VBA.
Далее приводятся наиболее часто используемые свойства и методы объекта
Перечислим основные свойства объекта Range.
Возвращает значение из ячейки или в ячейки диапазона. В данном примере переменной х присваивается значение из ячейки C1 :
х = Range («C1») .Value В следующем примере в диапазон AI : В2 введена 1 :
Range («A1:B2») .Value = 1
Возвращает имя диапазона. В данном примере диапазону А1:В2 присваивается имя итоги:
Range ( «Al :B2») .Name = «Итоги»
Возвращает число объектов в наборе. В данном примере переменной х присваивается значение, равное числу строк диапазона AI : В2 :
х = Range ( «Al :B2») .Rows . Count
Возвращает число строк текущего диапазона. Текущим является диапазон, ограниченный пустыми строками и столбцами и содержащий данный элемент. В следующем примере переменной у присваивается значение, равное числу строк в текущем диапазоне, содержащем ячейку AI :
у = Range ( «Al» ). CurrentRegion. Rows . Count
Позволяет переносить текст при вводе в диапазон. Допустимые значения True и False. В следующем примере в ячейку В2 вводится текст длинный текст и в этой ; ячейке устанавливается режим ввода текста с переносом: With Range («B2») .Value = «Длинный текст» .WrapText = True End With
Возвращает столбец и строку соответственно. В данном примере очищается содержимое строки и выделяется столбец с активной ячейкой:
ActiveCell . EntireRow. Clear ActiveCell .EntireColumn. Select
Возвращает ширину столбцов и высоту строк диапазона соответственно
Возвращает объект comment (примечание), который связан с левым верхним углом диапазона при отображении на экране. Объект comment является элементом семейства comments. Метод AddComment, примененный к диапазону, создает новое примечание. Среди методов объекта comment отметим только метод Text, который задает текст, выводимый в примечании. Синтаксис:
Text (Text, Start, Overwrite)
.Text Text:= «Чрезвычайно важно!» & Chr(10) & «Про это никак нельзя забыть ! «
.Visible = True End With
Рис. 2.1. Пример отображения примечания на рабочем листе
Возвращает объект Font (шрифт). Объект Font имеет следующие свойства: