Excel именованный диапазон vba excel
Диапазон — это свойство в VBA похоже на свойство рабочего листа, свойство диапазона также имеет множество приложений и применений, когда мы пишем наш код и указываем конкретный диапазон ячеек или конкретную ячейку, это делается методом свойства диапазона, оно используется для ссылки на строки ячеек и столбцы.
Как вы знаете, VBA используется для записи и запуска макросов и автоматизации задач Excel, а также для более быстрого и точного выполнения повторяющихся задач.
В контексте рабочего листа Excel объект диапазона VBA обозначает ячейки, как одиночные, так и множественные. Объект диапазона может включать одну ячейку, всю строку или столбец или несколько ячеек, распределенных по строкам и столбцам.
Чтобы VBA запускал макросы и выполнял задачи, ему необходимо определить ячейки, в которых должны выполняться вызываемые задачи. Именно здесь концепция Range Objects находит свое применение.
Как использовать объект Range?
Чтобы ссылаться на объекты в VBA, мы используем иерархическую технику. Есть 3 иерархии:
- Квалификатор объекта: Он относится к местоположению объекта, например, где он находится, т. Е. К книге или листу, на которые имеется ссылка.
- Два других используются для манипулирования значениями ячеек. Это свойство и методы.
- Свойство: Здесь хранится информация об объекте.
- Метод: Это относится к действию, которое объект будет выполнять.
Например, для Range методом будут такие действия, как сортировка, форматирование, выбор, очистка и т. Д.
Это структура, которой следуют всякий раз, когда упоминается объект VBA. Эти 3 разделены точкой (.)
Application.Workbooks.Worksheets.Range
синтаксис
Application.Workbooks («Booknew.xlsm»). Рабочие листы («Sheet3»). Range («B1»)
Примеры
Пример №1 — Обращение к отдельной ячейке
Предположим, нам нужно выбрать ячейку «B2» в «sheet1» в книге.
Выполните следующие шаги:
- Откройте Excel. Откройте файл с расширением Excel «.xlsm», что означает «Книга с поддержкой макросов Excel». Книга Excel с типами «.xlsx» не позволит вам сохранить макросы, которые вы будете писать сейчас.
- Теперь, когда вы открыли книгу, вам нужно перейти в редактор VBA. Вы можете использовать сочетание клавиш «ALT + F11», чтобы открыть редактор, или воспользуйтесь приведенным ниже методом, как показано на снимке экрана:
Вы увидите экран, похожий на показанный ниже:
Теперь напишите код, как показано на скриншоте ниже.
Смотрите на скриншоте Excel ниже, что в настоящее время активирована ячейка A2. После запуска кода обратите внимание, где находится активированная ячейка.
Запустите код, как показано на скриншоте ниже:
Совет: вы также можете использовать горячую клавишу Excel, например F5, для запуска кода.
Вы увидите, что ячейка «B2» выбрана после выполнения программы.
Здесь вы даете инструкции программе перейти к определенной ячейке на определенном листе конкретной книги и выполнить действие, указанное здесь, для выбора.
Точно так же вы можете использовать синтаксис для выбора широкого спектра ячеек и диапазонов, а также выполнять с ними различные действия.
Например, здесь, чтобы выбрать вторую строку. Запустите приведенный ниже код, чтобы выбрать всю строку
Здесь диапазон («2: 2») означает вторую строку. Вы можете вернуться к своему листу Excel и увидеть результаты, как показано на скриншоте ниже.
Например, здесь, чтобы выбрать весь столбец C. Запустите приведенный ниже код и просмотрите результаты.
После ввода приведенного выше кода вы увидите, что весь столбец выбран на вашем листе Excel. См. Снимок экрана ниже.
Здесь диапазон («C: C») означает столбец C.
Точно так же вы можете выбрать непрерывные ячейки или несмежные ячейки, пересечение диапазонов ячеек и т. Д.
Просто внесите следующие изменения в часть диапазона, показанную в коде.
Пример №4 — Выбор смежных ячеек: диапазон («B2: D6»)
Пример № 5 — Выбор несмежных ячеек: диапазон («B1: C5, G1: G3»)
Пример №6 — Выбор пересечения диапазона: Диапазон («B1: G5 G1: G3»)
[Note the absence of comma here]. Здесь вы увидите, что выбираются от G1 до G3, которые являются общими ячейками в указанном диапазоне.
Теперь следующим примером будет выбор группы ячеек на листе и объединение их в одну ячейку.
Предположим, вы хотите объединить ячейки «B1: C5» в одну. См. Приведенный ниже код и следуйте инструкциям.
Здесь «.merge» — это действие, которое мы выполняем над группой ячеек, заданной в диапазоне.
Предположим, что ячейки «F2: H6» выделены желтым, и мы хотим очистить это форматирование Excel. Другой сценарий, возможно, вы хотите удалить все форматирование либо на всем листе, либо в группе ячеек.
Смотрите скриншоты ниже, чтобы продолжить. Сначала я покажу вам отформатированные ячейки (F2: H6).
Пожалуйста, запустите коды, показанные на снимке экрана ниже, чтобы удалить это форматирование в выбранном диапазоне ячеек.
Синтаксис: ThisWorkbook.Worksheets («Sheet1»). Range («F2: H6»). ClearFormats
Вы можете обратиться к этому снимку экрана, приведенному ниже:
Точно так же вы можете очистить содержимое диапазона ячеек с помощью действия «.ClearContents».
Диапазоны легче идентифицировать по имени, чем с помощью нотации A1. Чтобы присвоить имя выбранному диапазону, щелкните поле имени с левой стороны строки формул, введите имя и нажмите клавишу ВВОД.
Примечание. Существует два типа именованных диапазонов: именованный диапазон книги и именованный диапазон определенного листа.
Именованный диапазон книги
Именованный диапазон книги относится к определенному диапазону в любом месте книги (применяется глобально).
Как создать именованный диапазон книги:
Как указано выше, обычно он создается путем ввода имени в поле "Имя" с левой стороны строки формул. Обратите внимание, что имя не может содержать пробелов.
Именованный диапазон определенного листа
Именованный диапазон определенного листа относится к диапазону конкретного листа и не является глобальным для всех листов в книге. Ссылайтесь на такой именованный диапазон с этого же листа просто с помощью имени, но из другого листа потребуется использовать имя листа с добавлением "!" и имени диапазона (пример: диапазон "Имя" "= Лист1!Имя").
Преимущество заключается в возможности использования кода VBA для создания новых листов с одинаковыми именами для одних и тех же диапазонов на этих листах без возникновения ошибки, сообщающей, что имя уже используется.
Как создать именованный диапазон определенного листа:
Пример именованного диапазона определенного листа: выделенный диапазон A1:A10 для присвоения имени.
Выбранное имя диапазона — "Имя". В пределах одного листа ссылайтесь на именованный диапазон, просто введя в ячейку "=Имя". Из другого листа ссылайтесь на диапазон определенного листа, указав в ячейке имя листа: "= Лист1!Имя".
Ссылка на именованный диапазон
В следующем примере выполняется ссылка на диапазон с именем MyRange в книге с именем MyBook.xls.
В следующем примере выполняется ссылка на диапазон определенного листа с именем Sheet1!Sales в книге с именем Report.xls.
Чтобы выбрать именованный диапазон, используйте метод GoTo, который активирует книгу и лист, а затем выбирает диапазон.
В следующем примере показано, как можно написать эту же процедуру для активной книги.
В этом примере в качестве формулы для проверки данных используется именованный диапазон. В этом примере данные проверки должны быть на листе 2 в диапазоне A2:A100. Они используются для проверки данных, введенных на листе 1 в диапазоне D2:D10.
Циклический переход по ячейкам в именованном диапазоне
В приведенном ниже примере выполняется циклический переход по каждой ячейке именованного диапазона с помощью цикла For Each. Next. Если значение любой ячейки в диапазоне превышает значение Limit , цвет ячейки изменяется на желтый.
Об участнике
Деннис Валлентин (Dennis Wallentin) — автор блога VSTO & .NET & Excel, посвященного решениям .NET Framework для Excel и службам Excel. Деннис разрабатывает решения Excel более 20 лет и также является соавтором книги "Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET (2nd Edition)".
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Когда мы работаем с большим объемом данных, чтобы избежать ссылки на конкретную ячейку или диапазоны ячеек, мы обычно создаем именованные диапазоны, и это позволяет нам ссылаться на требуемый диапазон ячеек через именованный диапазон. В VBA для создания диапазона имен у нас есть функция «Добавить имя».
Мы можем выбрать ячейку или диапазон ячеек и дать ей имя. После именования ячеек мы можем обращаться к этим ячейкам, вводя эти определенные имена вместо обычных ссылок на строки или столбцы.
Вы можете скачать этот шаблон Excel с именованным диапазоном VBA здесь — Шаблон Excel с именованным диапазоном VBA
Как создать именованные диапазоны?
Создание именованных диапазонов — это прогулка в парке. Первое, что нам нужно сделать, это определить ячейки, которые мы хотим создать. диапазон имен в excel Диапазон имен в Excel Диапазон имен в Excel — это имя, присвоенное диапазону для дальнейшего использования. Чтобы назвать диапазон, сначала выберите диапазон данных, а затем вставьте таблицу в диапазон, затем введите имя диапазона из поля имени в левой части окна. читать далее .
Например, посмотрите на изображение ниже.
Чтобы получить прибыль в ячейке B4, я применил формулу B2 – B3.
Это обычное дело, которое делают все. Но как насчет того, чтобы создать Имена и применить формулу типа «Продажи» — «Стоимость».
Поместите курсор в ячейку B2 > Перейти Поле имени Поле имени В Excel поле имени расположено в левой части окна и используется для присвоения имени таблице или ячейке. Имя обычно представляет собой символ строки, за которым следует номер столбца, например ячейка A1. читать далее и назовите это продажами.
Поместите курсор в ячейку B3 и назовите ее Стоимость.
Теперь в столбце прибыли мы можем ссылаться на эти имена вместо ссылок на ячейки.
Это основная вещь в именованных диапазонах.
Как создать именованные диапазоны с помощью кода VBA?
Пример №1
Вы когда-нибудь задумывались о создании именованного диапазона с помощью Код VBA Код VBA Код VBA относится к набору инструкций, написанных пользователем на языке программирования приложений Visual Basic в редакторе Visual Basic (VBE) для выполнения определенной задачи. читать далее ?
Выполните следующие шаги, чтобы создать именованный диапазон.
Шаг 1: Определите переменную как «Диапазон».
Код:
Шаг 2: Теперь установите переменную «Rng» для конкретных ячеек, которые вы хотите назвать.
Код:
Шаг 3: Используя объект «ThisWorkbook», получите доступ к свойству Names.
У нас так много параметров с Имена.Добавить метод. Ниже приведены пояснения.
[Name]: Имя ничего не значит, но какое имя мы хотели бы дать указанному нами диапазону.
Имя ячейки не должно содержать никаких специальных символов, кроме символа подчеркивания (_), а также пробелов. Он не должен начинаться с числовых значений.
[Refers to]: Это не что иное, как диапазон ячеек, на который мы ссылаемся.
Я думаю, что этих двух параметров достаточно, чтобы начать разбирательство.
Шаг 4: В имени аргумент вводит имя, которое вы хотите дать. Я назвал «SalesNumbers».
Код:
Шаг 5: В ссылке на аргумент введите диапазон ячеек, которые мы хотим создать. В имени переменной «Rng» мы уже назначили диапазон ячеек от A2 до A7, поэтому укажите аргумент как «Rng».
Код:
Хорошо, этот код создаст именованный диапазон для ячеек от A2 до A7.
Теперь на листе я создал несколько чисел от A2 до A7.
В ячейке A8 я хочу получить общее количество указанных выше номеров ячеек. Используя именованный диапазон, мы создадим СУММУ этих чисел.
Код:
Если вы запустите этот код вручную или нажав клавишу f5, мы получим сумму именованного диапазона в ячейке A8.
Это основные факты, которые необходимо знать об «Именованных диапазонах».
В VBA с использованием объекта RANGE VBA с использованием объекта RANGE Диапазон — это свойство в VBA, которое помогает указать конкретную ячейку, диапазон ячеек, строку, столбец или трехмерный диапазон. В контексте рабочего листа Excel объект диапазона VBA включает одну или несколько ячеек, распределенных по различным строкам и столбцам. читать далее , мы можем обратиться к ячейкам. Точно так же мы также можем ссылаться на эти ячейки, используя именованные диапазоны.
Например, в приведенном выше примере мы назвали ячейку B2 как «Продажи» и B3 как «Расходы.»
Используя фактическую ссылку на ячейку, мы обращаемся к этим ячейкам следующим образом.
Код:
Поскольку мы уже создали эти ячейки, мы можем использовать эти имена, как показано ниже.
Код:
Таким образом, используя именованные диапазоны, мы можем использовать эти ячейки. Используя эти имена, мы можем рассчитать сумму прибыли в ячейке B4. Для этого имени ячейка B4 как Прибыль.
Сейчас в редактор VBA Редактор VBA Редактор Visual Basic для приложений — это интерфейс сценариев. Эти сценарии в первую очередь отвечают за создание и выполнение макросов в программном обеспечении Microsoft. читать далее , примените этот код.
Переменные — это сердце и душа любого крупного проекта VBA, поскольку переменные — это сердце и душа, то тип данных, который мы им назначаем, также является очень важным фактором в этом отношении. В наших многочисленных предыдущих статьях мы много раз обсуждали переменные и важность их типов данных. Одной из таких переменных и типов данных является «переменная диапазона» в этой специальной специальной статье. Мы дадим полное руководство по «Переменной диапазона» в Excel VBA.
Что такое переменная диапазона в Excel VBA?
Как и любая другая переменная Диапазон в VBA Диапазон в VBA Диапазон — это свойство в VBA, которое помогает указать конкретную ячейку, диапазон ячеек, строку, столбец или трехмерный диапазон. В контексте рабочего листа Excel объект диапазона VBA включает одну или несколько ячеек, распределенных по различным строкам и столбцам. читать далее , переменная также является переменной, но это «переменная объекта», которую мы используем для установки ссылки на определенный диапазон ячеек.
Как и любой другой переменной, мы можем дать переменной любое имя, но тип данных, который мы им назначаем, должен быть «диапазон». Как только тип данных назначается переменной, она становится «переменной объекта», и, в отличие от другой переменной, мы не можем начать использовать переменную, пока не установим ссылку на объекты в случае объектных переменных.
Итак, после того, как мы объявим переменную, нам нужно использовать «НАБОР» ключевое слово для установки ссылки на объект, т. е. объект Range в данном случае.
Хорошо, теперь мы увидим некоторые примеры переменных диапазона Excel VBA на практике.
Примеры переменных диапазона в Excel VBA
Вы можете скачать этот шаблон Excel с диапазоном переменных VBA здесь — Шаблон Excel с диапазоном переменных VBA
Например, предположим, что вы хотите выбрать диапазон ячеек от A2 до B10 для снимка экрана ниже.
Чтобы выбрать этот упомянутый диапазон ячеек, все это, пока у нас есть объект RANGE, и внутри объекта диапазона мы указали адрес ячейки в двойных кавычках.
Код:
Как только диапазон ячеек упоминается с использованием объекта RANGE, если вы поставите точку, мы увидим все свойства и методы, связанные с этим объектом диапазона.
Код:
Поскольку нам нужно просто выбрать упомянутые ячейки, выберите метод «Выбрать» из списка IntelliSense.
Код:
Запустите код, и он выберет указанные ячейки.
Это очевидно, не так ли, но представьте себе сценарий использования одного и того же диапазона в длинном проекте VBA, скажем, сто раз, написания одного и того же кода «Диапазон («A2: A10»)» 100 раз займет некоторое время, но вместо этого мы объявим переменную и назначим тип данных как объект «Диапазон».
Хорошо, давайте дадим ваше собственное имя переменной и назначим тип данных «Диапазон».
Помимо «Переменных объекта», мы можем начать использовать переменные по их имени, но в случае «Переменных объекта» нам нужно установить ссылку.
Например, в этом случае наш объект переменной (Rng) представляет собой диапазон, поэтому нам нужно установить ссылку на слово «Rng», на которое будет ссылаться. Чтобы установить ссылку, нам нужно использовать ключевое слово «Set».
Теперь переменная «Rng» относится к диапазону ячеек от A2 до B10. Вместо того, чтобы писать «Диапазон («A2: B10»))» каждый раз мы можем просто написать слово «Рнг.»
В следующей строке упомяните имя переменной «Rng» и поставьте точку, чтобы увидеть волшебство.
Как вы можете видеть выше, мы можем видеть все свойства и методы объектов диапазона, как и в предыдущем случае.
Сделайте переменную динамической
Теперь мы знаем, как установить ссылку на диапазон ячеек, но как только мы упомянем диапазон ячеек, он будет привязан только к этим ячейкам. Любое добавление или удаление ячеек не повлияет на эти ячейки.
Таким образом, нахождение нового диапазона ячеек после любого добавления или удаления ячеек делает переменную динамической по своей природе. Это возможно путем нахождения последней использованной строки и столбца.
Чтобы найти последнюю использованную строку и столбец, нам нужно определить еще две переменные.
Код:
Теперь приведенный ниже код найдет последнюю использованную строку и столбец, прежде чем мы установим ссылку на переменную объекта диапазона.
Код:
Теперь откройте оператор ключевого слова «Set».
Код:
В отличие от предыдущего метода, мы использовать свойства VBA CELLS Использовать свойства VBA CELLS Ячейки — это ячейки рабочего листа, и в VBA, когда мы ссылаемся на ячейки как на свойство диапазона, мы ссылаемся на одни и те же ячейки. В концепциях VBA ячейки также одинаковы, ничем не отличаются от обычных ячеек Excel. читать далее в этот раз.
Код:
Я упомянул ячейки (1,1), т. е. это относится к первой ячейке в активном листе, но нам нужна ссылка на диапазон данных, поэтому используйте свойство «ИЗМЕНИТЬ РАЗМЕР» и упомяните переменные «последняя использованная строка и столбец».
Код:
Теперь это установит последнюю ссылку на переменную объекта диапазона «Rng». Затем укажите имя переменной и используйте метод «Выбрать».
Теперь я добавлю еще несколько строк к своим данным.
Я добавил три дополнительные строки данных. Если я запущу код сейчас, он должен выбрать последний диапазон данных.
Для чего вообще нужны именованные диапазоны? Обращение к именованному диапазону гораздо удобнее, чем прописывание адреса в формулах и VBA:
- Предположим, что в формуле мы ссылаемся на диапазон A1:C10 (возможно даже не один раз). Для примера возьмем простую функцию СУММ(суммирует значения указанных ячеек):
=СУММ( A1:C10 ; F1:K10 )
Затем нам стало необходимо суммировать другие данные(скажем вместо диапазона A1:C10 в диапазоне D2:F11 ). В случае с обычным указанием диапазона нам придется искать все свои формулы и менять там адрес диапазона на новый. Но если назначить своему диапазону A1:C10 имя(к примеру ДиапазонСумм ), то в формуле ничего менять не придется - достаточно будет просто изменить ссылку на ячейки в самом имени один раз. Я привел пример с одной формулой - а что, если таких формул 10? 30?
Примерно такая же ситуация и с использованием в кодах: указав имя диапазона один раз не придется каждый раз при изменении и перемещении этого диапазона прописывать его заново в коде. - Именованный диапазон не просто так называется именованным. Если взять пример выше - то отображение в формуле названия ДиапазонСумм куда нагляднее, чем A1:C10 . В сложных формулах куда проще будет ориентироваться по именам, чем по адресам. Почему удобнее: если сменить стиль отображения ссылок (подробнее про стиль), то диапазон A1:C10 будет выглядеть как-то вроде этого: R1C1:R10C3 . А если назначить имя - то оно как было ДиапазонСумм , так им и останется.
- При вводе формулы/функции в ячейку, можно не искать нужный диапазон, а начать вводить лишь первые буквы его имени и Excel предложит его ко вводу:
Данный метод доступен лишь в версиях Excel 2007 и выше
MsgBox Range("ДиапазонСумм").Address MsgBox [ДиапазонСумм].Address
Обращение к именованному диапазону в формулах/функциях
- =СУММ( ДиапазонСумм )
- =ВПР("Критерий"; ДиапазонСумм ;2;0)
Читать подробнее про функцию ВПР
Если при указании диапазона в формуле выделить именованный диапазон, то его имя автоматически подставится в формулу вместо фактического адреса ячеек:
Ограничения, накладываемые на создание имен
- В качестве имени диапазона не могут быть использованы словосочетания, содержащие пробел. Вместо него лучше использовать нижнее подчеркивание _ или точку: Name_1, Name.1
- Первым символом имени должна быть буква, знак подчеркивания (_) или обратная косая черта (\). Остальные символы имени могут быть буквами, цифрами, точками и знаками подчеркивания
- Нельзя в качестве имени использовать зарезервированные в Excel константы - R, C и RC(как прописные, так и строчные). Связано с тем, что данные буквы используются самим Excel для адресации ячеек при использовании стиля ссылок R1C1 (читать подробнее про стили ссылок)
- Нельзя давать именам названия, совпадающие с адресацией ячеек: B$100, D2(для стиля ссылок А1) или R1C1, R7(для стиля R1C1). И хотя при включенном стиле ссылок R1C1 допускается дать имени название вроде A1 или D130 - это не рекомендуется делать, т.к. если впоследствии стиль отображения ссылок для книги будет изменен - то Excel не примет такие имена и предложит их изменить. И придется изменять названия всех подобных имен. Если очень хочется - можно просто добавить нижнее подчеркивание к имени: _A1
- Длина имени не может превышать 255 символов
Создание именованного диапазона
Способ первый
обычно при создании простого именованного диапазона я использую именно его. Выделяем ячейку или группу ячеек, имя которым хотим присвоить -щелкаем левой кнопкой мыши в окне адреса и вписываем имя, которое хотим присвоить. Жмем Enter:
Способ второй
Выделяем ячейку или группу ячеек. Жмем правую кнопку мыши для вызова контекстного меню ячеек. Выбираем пункт:
- Excel 2007: Имя диапазона (Range Name)
- Excel 2010: Присвоить имя (Define Name)
либо:
Жмем Ctrl + F3
либо:
- 2007-2016 Excel : вкладка Формулы (Formulas) -Диспетчер имен (Name Manager) -Создать (New) (либо на той же вкладке сразу - Присвоить имя (Define Name) )
- 2003 Excel : Вставка -Имя -Присвоить
Появляется окно создания имени
Имя (Name) - указывается имя диапазона. Необходимо учитывать ограничения для имен, которые я описывал в начале статьи.
Область (Scope) - указывается область действия создаваемого диапазона - Книга , либо Лист1 :
- Лист1 (Sheet1) - созданный именованный диапазон будет доступен только из указанного листа. Это позволяет указать разные диапазоны для разных листов, но указав одно и тоже имя диапазона
- Книга (Workbook) - созданный диапазон можно будет использовать из любого листа данной книги
Примечание (Comment) - здесь можно записать пометку о созданном диапазоне, например для каких целей планируется его использовать. Позже эту информацию можно будет увидеть из диспетчера имен ( Ctrl + F3 )
Диапазон (Refers to) - при данном способе создания в этом поле автоматически проставляется адрес выделенного ранее диапазона. Его можно при необходимости тут же изменить.
Изменение диапазона
Чтобы изменить имя Именованного диапазона, либо ссылку на него необходимо всего лишь вызывать диспетчер имен( Ctrl + F3 ), выбрать нужное имя и нажать кнопку Изменить (Edit. ) .
Изменить можно имя диапазона (Name) , ссылку (RefersTo) и Примечание (Comment) . Область действия (Scope) изменить нельзя, для этого придется удалить текущее имя и создать новое, с новой областью действия.
Удаление диапазона
Чтобы удалить Именованный диапазон необходимо вызывать диспетчер имен( Ctrl + F3 ), выбрать нужное имя и нажать кнопку Удалить (Delete. ) .
Так же можно создавать списки с автоматическим определением его размера. Например, если значения в списке периодически пополняются или удаляются и чтобы каждый раз не переопределять границы таких диапазонов. Такие диапазоны называют динамическими.
Читайте также: