Именованный диапазон VBA | Как создавать и использовать именованные диапазоны?

Именованный диапазон Excel VBA

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

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

Вы можете скачать этот шаблон Excel для именованного диапазона VBA здесь - Шаблон Excel для именованного диапазона VBA

Как создавать именованные диапазоны?

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

Для примера посмотрите на изображение ниже.

Чтобы получить прибыль в ячейке B4, я применил формулу B2 - B3.

Это обычное дело. Но как насчет создания имен и применения формулы вроде «Продажи» - «Стоимость».

Поместите курсор в ячейку B2> Перейти к полю имени и назовите его Продажи.

Поместите курсор в ячейку B3 и назовите ее Стоимость.

Теперь в столбце прибыли мы можем ссылаться на эти имена вместо ссылок на ячейки.

Это основная особенность именованных диапазонов.

Как создать именованные диапазоны с помощью кода VBA?

Пример # 1

Вы когда-нибудь думали о создании именованного диапазона с помощью кода VBA?

Выполните следующие шаги, чтобы создать именованный диапазон.

Шаг 1: Определите переменную как «Диапазон».

Код:

 Sub NamedRanges_Example () Dim Rng As Range End Sub 

Шаг 2: Теперь установите переменную «Rng» для конкретных ячеек, которые вы хотите назвать.

Код:

 Sub NamedRanges_Example () Dim Rng As Range Set Rng = Range ("A2: A7") End Sub 

Шаг 3. Использование объекта «ThisWorkbook» для доступа к свойству Names.

У нас так много параметров с методом Names.Add . Ниже приведены объяснения.

[Имя]: Имя - это не что иное, как имя, которое мы хотели бы дать указанному диапазону.

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

[Относится к]: Это не что иное, как диапазон ячеек, о которых мы говорим.

Я думаю, что этих двух параметров достаточно, чтобы начать работу.

Шаг 4: В имени аргумент входит имя, которое вы хотите дать. Я назвал его «SalesNumbers».

Код:

 Sub NamedRanges_Example () Dim Rng As Range Set Rng = Range ("A2: A7") ThisWorkbook.Names.Add Name: = "SalesNumbers" End Sub 

Шаг 5: В аргументе ссылается на диапазон ячеек, который мы хотим создать. В имени переменной «Rng» мы уже присвоили диапазон ячеек от A2 до A7, поэтому укажите аргумент как «Rng».

Код:

 Sub NamedRanges_Example () Dim Rng As Range Set Rng = Range ("A2: A7") ThisWorkbook.Names.Add Name: = "SalesNumbers", RefersTo: = Rng End Sub 

Хорошо, этот код создаст именованный диапазон для ячеек от A2 до A7.

Теперь на листе я создал несколько чисел от A2 до A7.

In the A8 cell, I want to have the total of the above cell numbers. Using named range, we will create a SUM of these numbers.

Code:

 Sub NamedRanges_Example() Dim Rng As Range Set Rng = Range("A2:A7") ThisWorkbook.Names.Add Name:="SalesNumbers", RefersTo:=Rng Range("A8").Value = WorksheetFunction.Sum(Range("SalesNumbers")) End Sub 

If you run this code manually or by pressing f5 key then, we will get the total of a named range in cell A8.

This is the basic must-know facts about “Named Ranges”.

Example #2

In VBA using RANGE object, we can refer to the cells. Similarly, we can also refer to those cells by using named ranges as well.

For example, in the above example, we have named the cell B2 as “Sales” and B3 as “Cost”.

By using actual cell reference we refer to those cells like this.

Code:

 Sub NamedRanges() Range("B2").Select 'This will select the B2 cell Range("B3").Select 'This will select the B3 cell End Sub 

Since we already created these cells we can refer to using those names like the below.

Code:

 Sub NamedRanges() Range("Sales").Select 'This will select cell named as "Sales" i.e. B2 cell Range("Cost").Select 'This will select cell named as "Cost" i.e. B3 cell End Sub 

Like this using Named Ranges, we can make use of those cells. Using these named we can calculate the profit amount in cell B4. For this first name the cell B4 as Profit.

Now in the VBA editor apply this code.

Code:

 Sub NamedRanges_Example1() Range("Profit").Value = Range("Sales") - Range("Cost") End Sub 

This will calculate the profit amount in the cell named “Profit”.