Как в range вставить переменную vba

VBA Range Object Variables (Dim / Set Range)

We have already gone over what variables and constants are, in our VBA Data Types – Variables and Constants tutorial. Now, we are now going to look at the range object in VBA and how to declare a variable as a range object. The range object is used to denote cells or multiple cells in VBA. So, it’s very useful to use in your code.

Click here for more information about VBA Ranges and Cells.

The VBA Range Object

You can use the range object to refer to a single cell. For example, if you wanted to refer to cell A1 in your VBA code to set the cell value and bold the cell’s text use this code:

When you press run or F5 on your keyboard, to run your code then you get the following result, in your actual worksheet:

You can use the range object to refer to multiple cells or ranges. For example, if you wanted to refer to cell range (A1:C1) in your VBA code then you could use the VBA range object as shown in the code below:

When you press run or F5 on your keyboard, to run your code then you get the following result, in your actual worksheet:

Declaring a Variable as a Range

You will need to use the Dim and Set keywords when declaring a variable as a range. The code below shows you how to declare a variable as a range.

The result is:

Selecting Specific Rows In Your Range Object

You can select specific rows within your Range Object. The code below shows you how to do this:

Selecting Specific Columns In Your Range Object

You can select specific columns within your Range Object. The code below shows you how to do this:

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

How to use a Range or a Cell as a Variable in VBA

To use a range or a single cell as a variable, first, you need to declare that variable with the range data type. Once you do that you need to specify a range of a cell to that variable using the range object. This also gives you access to all the properties and methods that you can use with a range.

1. Use the “Dim” keyword and enter the name of the variable.
2. Declare the keyword as Range.
3. Type the name of the variable.
4. Specify the range that you want to assign to the variable.

Set Selection to Range Variable

You can also use the selected range to a variable and then that variable to access properties and methods. Consider the following code.

But there is one thing that you need to take care that when you are selecting an object other than a range in that case VBA will show you an error.

Copy a Range using Variable

Once you declare a variable as a range you can copy that range using that variable name as well. Let say you have declared the range A1:A10 as a variable rng (above example), you can copy it using the following code.

Sub vba_range_variable() Dim rng As Range Set rng = Range(«A1:A10») rng.Copy End Sub

Related:

Using Range Variable in a Function

You can also use a range variable to refer to a range in a function while writing a macro. Consider the following code.

Count Number of Rows and Columns

As I have already said, once specify a variable as a range you will also get all the properties and methods to access. Imagine if you want to count the rows and columns. Following is the code that could be used.

When you run this code, it shows a message with the count of rows and columns from the range that you have specified in the variable.

Defining Worksheet Along with the Range

In all the examples above, we have used range without mentioning the worksheet, so when you run that code, VBA will always refer to the range in the active sheet. But if you want to refer to a specific worksheet then you need to use the worksheet object along with the range.

VBA: Selecting range by variables

I want to select the formatted range of an Excel sheet. To define the last and first row I use the following functions:

In the next step I want to select this area: Formula should look like this:

However, this is not working. Maybe somebody has an idea what is wrong with it. Thanks a lot!

6 Answers 6

I recorded a macro with ‘Relative References’ and this is what I got :

Heres what I thought : If the range selection is in quotes, VBA really wants a STRING and interprets the cells out of it so tried the following:

And it worked 🙂 ie.. just create a string using your variables, make sure to dimension it as a STRING variables and Excel will read right off of it 😉

Following tested and found working :

I ran into something similar — I wanted to create a range based on some variables. Using the Worksheet.Cells did not work directly since I think the cell’s values were passed to Range.

This did work though:

That took care of converting the cell’s numerical location to what Range expects, which is the A1 format.

If you just want to select the used range, use

If you want to select from A1 to the end of the used range, you can use the SpecialCells method like this

Sometimes Excel gets confused on what is the last cell. It’s never a smaller range than the actual used range, but it can be bigger if some cells were deleted. To avoid that, you can use Find and the asterisk wildcard to find the real last cell.

Finally, make sure you’re only selecting if you really need to. Most of what you need to do in Excel VBA you can do directly to the Range rather than selecting it first. Instead of

Главные свойства Range и Cells

Эта статья охватывает все, что вам нужно знать об использовании ячеек и диапазонов в VBA. Вы можете прочитать его от начала до конца, так как он сложен в логическом порядке. Или использовать оглавление ниже, чтобы перейти к разделу по вашему выбору.

Рассматриваемые темы включают свойство смещения, чтение значений между ячейками, чтение значений в массивы и форматирование ячеек.

Краткое руководство по диапазонам и клеткам

Введение

Это третья статья, посвященная трем основным элементам VBA. Этими тремя элементами являются Workbooks, Worksheets и Ranges/Cells. Cells, безусловно, самая важная часть Excel. Почти все, что вы делаете в Excel, начинается и заканчивается ячейками.

Вы делаете три основных вещи с помощью ячеек:

1. Читаете из ячейки.
2. Пишите в ячейку.
3. Изменяете формат ячейки.

В Excel есть несколько методов для доступа к ячейкам, таких как Range, Cells и Offset. Можно запутаться, так как эти функции делают похожие операции.

В этой статье я расскажу о каждом из них, объясню, почему они вам нужны, и когда вам следует их использовать.

Давайте начнем с самого простого метода доступа к ячейкам — с помощью свойства Range рабочего листа.

Важное замечание

Я недавно обновил эту статью, сейчас использую Value2.

Вам может быть интересно, в чем разница между Value, Value2 и значением по умолчанию:

Использование Value может усечь число, если ячейка отформатирована, как валюта. Если вы не используете какое-либо свойство, по умолчанию используется Value.

Лучше использовать Value2, поскольку он всегда будет возвращать фактическое значение ячейки.

Свойство Range

Рабочий лист имеет свойство Range, которое можно использовать для доступа к ячейкам в VBA. Свойство Range принимает тот же аргумент, что и большинство функций Excel Worksheet, например: «А1», «А3: С6» и т.д.

В следующем примере показано, как поместить значение в ячейку с помощью свойства Range.

Как видно из кода, Range является членом Worksheets, которая, в свою очередь, является членом Workbook. Иерархия такая же, как и в Excel, поэтому должно быть легко понять. Чтобы сделать что-то с Range, вы должны сначала указать рабочую книгу и рабочий лист, которому она принадлежит.

В оставшейся части этой статьи я буду использовать кодовое имя для ссылки на лист.

Следующий код показывает приведенный выше пример с использованием кодового имени рабочего листа, т.е. Лист1 вместо ThisWorkbook.Worksheets («Лист1»).

Вы также можете писать в несколько ячеек, используя свойство Range

Свойство Cells рабочего листа

У Объекта листа есть другое свойство, называемое Cells, которое очень похоже на Range . Есть два отличия:

1. Cells возвращают диапазон только одной ячейки.
2. Cells принимает строку и столбец в качестве аргументов.

В приведенном ниже примере показано, как записывать значения в ячейки, используя свойства Range и Cells.

Вам должно быть интересно, когда использовать Cells, а когда Range. Использование Range полезно для доступа к одним и тем же ячейкам при каждом запуске макроса.

Например, если вы использовали макрос для вычисления суммы и каждый раз записывали ее в ячейку A10, тогда Range подойдет для этой задачи.

Использование свойства Cells полезно, если вы обращаетесь к ячейке по номеру, который может отличаться. Проще объяснить это на примере.

В следующем коде мы просим пользователя указать номер столбца. Использование Cells дает нам возможность использовать переменное число для столбца.

В приведенном выше примере мы используем номер для столбца, а не букву.

Чтобы использовать Range здесь, потребуется преобразовать эти значения в ссылку на буквенно-цифровую ячейку, например, «С1». Использование свойства Cells позволяет нам предоставить строку и номер столбца для доступа к ячейке.

Иногда вам может понадобиться вернуть более одной ячейки, используя номера строк и столбцов. В следующем разделе показано, как это сделать.

Использование Cells и Range вместе

Как вы уже видели, вы можете получить доступ только к одной ячейке, используя свойство Cells. Если вы хотите вернуть диапазон ячеек, вы можете использовать Cells с Range следующим образом:

Как видите, вы предоставляете начальную и конечную ячейку диапазона. Иногда бывает сложно увидеть, с каким диапазоном вы имеете дело, когда значением являются все числа. Range имеет свойство Address, которое отображает буквенно-цифровую ячейку для любого диапазона. Это может пригодиться, когда вы впервые отлаживаете или пишете код.

В следующем примере мы распечатываем адрес используемых нами диапазонов.

В примере я использовал Debug.Print для печати в Immediate Window. Для просмотра этого окна выберите «View» -> «в Immediate Window» (Ctrl + G).

Свойство Offset диапазона

У диапазона есть свойство, которое называется Offset. Термин «Offset» относится к отсчету от исходной позиции. Он часто используется в определенных областях программирования. С помощью свойства «Offset» вы можете получить диапазон ячеек того же размера и на определенном расстоянии от текущего диапазона. Это полезно, потому что иногда вы можете выбрать диапазон на основе определенного условия. Например, на скриншоте ниже есть столбец для каждого дня недели. Учитывая номер дня (т.е. понедельник = 1, вторник = 2 и т.д.). Нам нужно записать значение в правильный столбец.

Сначала мы попытаемся сделать это без использования Offset.

Как видно из примера, нам нужно добавить строку для каждого возможного варианта. Это не идеальная ситуация. Использование свойства Offset обеспечивает более чистое решение.

Как видите, это решение намного лучше. Если количество дней увеличилось, нам больше не нужно добавлять код. Чтобы Offset был полезен, должна быть какая-то связь между позициями ячеек. Если столбцы Day в приведенном выше примере были случайными, мы не могли бы использовать Offset. Мы должны были бы использовать первое решение.

Следует иметь в виду, что Offset сохраняет размер диапазона. Итак .Range («A1:A3»).Offset (1,1) возвращает диапазон B2:B4. Ниже приведены еще несколько примеров использования Offset.

Использование диапазона CurrentRegion

CurrentRegion возвращает диапазон всех соседних ячеек в данный диапазон. На скриншоте ниже вы можете увидеть два CurrentRegion. Я добавил границы, чтобы прояснить CurrentRegion.

Строка или столбец пустых ячеек означает конец CurrentRegion.

Вы можете вручную проверить CurrentRegion в Excel, выбрав диапазон и нажав Ctrl + Shift + *.

Если мы возьмем любой диапазон ячеек в пределах границы и применим CurrentRegion, мы вернем диапазон ячеек во всей области.

Range («B3»). CurrentRegion вернет диапазон B3:D14

Range («D14»). CurrentRegion вернет диапазон B3:D14

Range («C8:C9»). CurrentRegion вернет диапазон B3:D14 и так далее

Как пользоваться

Мы получаем CurrentRegion следующим образом

Только чтение строк данных

Прочитать диапазон из второй строки, т.е. пропустить строку заголовка.

Удалить заголовок

Удалить строку заголовка (т.е. первую строку) из диапазона. Например, если диапазон — A1:D4, это возвратит A2:D4

Использование Rows и Columns в качестве Ranges

Если вы хотите что-то сделать со всей строкой или столбцом, вы можете использовать свойство «Rows и Columns» на рабочем листе. Они оба принимают один параметр — номер строки или столбца, к которому вы хотите получить доступ.

Использование Range вместо Worksheet

Вы также можете использовать Cella, Rows и Columns, как часть Range, а не как часть Worksheet. У вас может быть особая необходимость в этом, но в противном случае я бы избегал практики. Это делает код более сложным. Простой код — твой друг. Это уменьшает вероятность ошибок.

Код ниже выделит второй столбец диапазона полужирным. Поскольку диапазон имеет только две строки, весь столбец считается B1:B2

Чтение значений из одной ячейки в другую

В большинстве примеров мы записали значения в ячейку. Мы делаем это, помещая диапазон слева от знака равенства и значение для размещения в ячейке справа. Для записи данных из одной ячейки в другую мы делаем то же самое. Диапазон назначения идет слева, а диапазон источника — справа.

В следующем примере показано, как это сделать:

Как видно из этого примера, невозможно читать из нескольких ячеек. Если вы хотите сделать это, вы можете использовать функцию копирования Range с параметром Destination.

Функция Copy копирует все, включая формат ячеек. Это тот же результат, что и ручное копирование и вставка выделения. Подробнее об этом вы можете узнать в разделе «Копирование и вставка ячеек»

Использование метода Range.Resize

При копировании из одного диапазона в другой с использованием присваивания (т.е. знака равенства) диапазон назначения должен быть того же размера, что и исходный диапазон.

Использование функции Resize позволяет изменить размер диапазона до заданного количества строк и столбцов.

Когда мы хотим изменить наш целевой диапазон, мы можем просто использовать исходный размер диапазона.

Другими словами, мы используем количество строк и столбцов исходного диапазона в качестве параметров для изменения размера:

Мы можем сделать изменение размера в одну строку, если нужно:

Чтение Value в переменные

Мы рассмотрели, как читать из одной клетки в другую. Вы также можете читать из ячейки в переменную. Переменная используется для хранения значений во время работы макроса. Обычно вы делаете это, когда хотите манипулировать данными перед тем, как их записать. Ниже приведен простой пример использования переменной. Как видите, значение элемента справа от равенства записывается в элементе слева от равенства.

Для чтения текста в переменную вы используете переменную типа String.

Вы можете записать переменную в диапазон ячеек. Вы просто указываете диапазон слева, и значение будет записано во все ячейки диапазона.

Вы не можете читать из нескольких ячеек в переменную. Однако вы можете читать массив, который представляет собой набор переменных. Мы рассмотрим это в следующем разделе.

Как копировать и вставлять ячейки

Если вы хотите скопировать и вставить диапазон ячеек, вам не нужно выбирать их. Это распространенная ошибка, допущенная новыми пользователями VBA.

Вы можете просто скопировать ряд ячеек, как здесь:

При использовании этого метода копируется все — значения, форматы, формулы и так далее. Если вы хотите скопировать отдельные элементы, вы можете использовать свойство PasteSpecial диапазона.

В следующей таблице приведен полный список всех типов вставок.

Чтение диапазона ячеек в массив

Вы также можете скопировать значения, присвоив значение одного диапазона другому.

Значение диапазона в этом примере считается вариантом массива. Это означает, что вы можете легко читать из диапазона ячеек в массив. Вы также можете писать из массива в диапазон ячеек. Если вы не знакомы с массивами, вы можете проверить их в этой статье.

В следующем коде показан пример использования массива с диапазоном.

Имейте в виду, что массив, созданный для чтения, является двумерным массивом. Это связано с тем, что электронная таблица хранит значения в двух измерениях, то есть в строках и столбцах.

Пройти через все клетки в диапазоне

Иногда вам нужно просмотреть каждую ячейку, чтобы проверить значение.

Вы можете сделать это, используя цикл For Each, показанный в следующем коде.

Вы также можете проходить последовательные ячейки, используя свойство Cells и стандартный цикл For.

Стандартный цикл более гибок в отношении используемого вами порядка, но он медленнее, чем цикл For Each.

Форматирование ячеек

Иногда вам нужно будет отформатировать ячейки в электронной таблице. Это на самом деле очень просто. В следующем примере показаны различные форматы, которые можно добавить в любой диапазон ячеек.