Присвоить имя ячейке excel vba

Обновлено: 06.01.2025

Знакомство с объектной моделью Excel следует начинать с такого замечательного объекта, как Range . Поскольку любая ячейка - это Range , то без знания, как с этим объектом эффективно взаимодействовать, вам будет затруднительно программировать для Excel. Это очень ладно-скроенный объект. При некоторой сноровке вы найдёте его весьма удобным в эксплуатации.

Что такое объекты?

Мы собираемся изучать объект Range , поэтому пару слов надо сказать, что такое, собственно, " объект ". Всё, что вы наблюдаете в Excel, всё с чем вы работаете - это набор объектов. Например, лист рабочей книги Excel - не что иное, как объект типа WorkSheet . Однотипные объекты объединяют в коллекции себе подобных. Например, листы объединены в коллекцию Sheets . Чтобы не путать друг с другом объекты одного и того же типа, они имеют отличающиеся имена, а также номер индекса в коллекции. Объекты имеют свойства , методы и события .
Свойства - это информация об объекте. Часто эти свойства можно менять, что автоматически влечет изменения внешнего вида объекта или его поведения. Например свойство Visible объекта Worksheet отвечает за видимость листа на экране. Если ему присвоить значение xlSheetHidden (это константа, которая по факту равно нулю), то лист будет скрыт.
Методы - это то, что объект может делать. Например, метод Delete объекта Worksheet удаляет себя из книги. Метод Select делает лист активным.
События - это механизм, при помощи которого вы можете исполнять свой код VBA сразу по факту возникновения того или иного события с вашим объектом. Например, есть возможность выполнять ваш код, как только пользователь сделал текущим определенный лист рабочей книги, либо как только пользователь что-то изменил на этом листе.

Объекты Range

Range это диапазон ячеек. Минимум - одна ячейка, максимум - весь лист, теоретически насчитывающий более 17 миллиардов ячеек (строки 2^20 * столбцы 2^14 = 2^34).
В Excel объявлены глобально и всегда готовы к использованию несколько коллекций, имеющий членами объекты типа Range , либо свойства это же типа. Коллекции глобального объекта Application : Cells , Columns , Rows , а также свойства Range , Selection , ActiveCell , ThisCell .
ActiveCell - активная ячейка текущего листа, ThisCell - если вы написали пользовательскую функцию рабочего листа, то через это свойство вы можете определить какая конкретно ячейка в данный момент пересчитывает вашу функцию. Об остальных перечисленных объектов речь пойдёт ниже.

Работа с отдельными ячейками

Синтаксическая форма	Комментарии по использованию
Range (" D5 ") или [ D5 ]	Ячейка D5 текущего листа. Полная и краткая формы. Тут применим только синтаксис типа A1, но не R1C1. То есть такая конструкция Range (" R1C2 ") - вызовет ошибку, даже если в книге Excel включен режим формул R1C1. Разумеется после этой формы вы можете обратиться к свойствам соответствующей ячейки. Например, Range (" D5 ") .Interior.Color = RGB(0, 255, 0) .
Cells(5, 4) или Cells(5, "D")	Ячейка D5 текущего листа через свойство Cells . 5 - строка (row), 4 - столбец (column). Допустимость второй формы мало кому известна.
Cells(65540)	Ячейку D5 можно адресовать и через указание только одного параметра свойсва Cells . При этом нумерация идёт слева направо, потом сверху вниз. То есть сначала нумеруется вся строка (2^14=16384 колонок) и только потом идёт переход на следующую строку. То есть Cells(16385) вернёт вам ячейку A2 , а D5 будет Cells(65540) . Пока данный способ выглядит не очень удобным.

Работа с диапазоном ячеек

Синтаксическая форма	Комментарии по использованию
Range ( "A1:B4 ") или [ A1:B4 ]	Диапазон ячеек A1:B4 текущего листа. Обратите внимание, что указываются координаты верхнего левого и правого нижнего углов диапазона. Причём первый указываемый угол вполне может быть правым нижним, это не имеет значения.
Range(Cells(1, 1), Cells(4, 2))	Диапазон ячеек A1:B4 текущего листа. Удобно, когда вы знаете именно цифровые координаты углов диапазона.

Работа со строками

Синтаксическая форма	Комментарии по использованию
Range (" 3:5 ") или [ 3:5 ]	Строки 3, 4 и 5 текущего листа целиком.
Range (" A3:XFD3 ") или [ A3:XFD3 ]	Строка 3, но с указанием колонок. Просто, чтобы вы понимали, что это тождественные формы. XFD - последняя колонка листа.
Rows (" 3:3 ")	Строка 3 через свойство Rows . Параметр в виде диапазона строк. Двоеточие - это символ диапазона.
Rows(3)	Тут параметр - индекс строки в массиве строк. Так можно сослаться только не конкретную строку. Обратите внимание, что в предыдущем примере параметр текстовая строка " 3:3 " и она взята в кавычки, а тут - чистое число.

Работа со столбцами

Синтаксическая форма	Комментарии по использованию
Range (" B:B ") или [ B:B ]	Колонка B текущего листа.
Range (" B1:B1048576 ") или [ B1:B1048576 ]	То же самое, но с указанием номеров строк, чтобы вы понимали, что это тождественные формы. 2^20=1048576 - максимальный номер строки на листе.
Columns (" B:B ")	То же самое через свойство Columns . Параметр - текстовая строка.
Columns(2)	То же самое. Параметр - числовой индекс столбца. "A" -> 1, "B" -> 2, и т.д.

Весь лист

Синтаксическая форма	Комментарии по использованию
Range (" A1:XFD1048576 ") или [ A1:XFD1048576 ]	Диапазон размером во всё адресное пространство листа Excel. Воспринимайте эту таблицу лишь как теорию - так работать с листами вам не придётся - слишком большое количество ячеек. Даже современные компьютеры не смогут помочь Excel быстро работать с такими массивами информации. Тут проблема больше даже в самом приложении.
Range (" 1:1048576 ") или [ 1:1048576 ]	То же самое, но через строки.
Range (" A:XFD ") или [ A:XFD ]	Аналогично - через адреса столбцов.
Cells	Свойство Cells включает в себя ВСЕ ячейки.
Rows	Все строки листа.
Columns	Все столбцы листа.

Следует иметь в виду, что свойства Range , Cells , Columns и Rows имеют как объекты типа Worksheet , так и объекты Range . Соответственно в первом случае эти коллекции будут относиться ко всему листу и отсчитываться будут от A1 , а вот в случае конкретного объекта Range эти коллекции будут относиться только к ячейкам этого диапазона и отсчитываться будут от левого верхнего угла диапазона. Например Cells(2,2) указывает на ячейку B2 , а Range("C3:D5").Cells(2,2) укажет на D4 .

Также много путаницы в умы вносит тот факт, что объект Range имеет одноименное свойство range . К примеру, Range("A100:D500").Range("A2") - тут выражение до точки ( Range("A100:D500") ) является объектом Range , выражение после точки ( Range("A2") ) - свойство range упомянутого объекта, но возвращает это свойство тоже объект типа Range . Вот такие пироги. Из этого следует, что такая цепочка может иметь и более двух членов. Практического смысла в этом будет не много, но синтаксически это будут совершенно корректно, например, так: Range("CV100:GR200").Range("J10:T20").Range("A1:B2") укажет на диапазон DE109:DF110 .

Ещё один сюрприз таится в том, что объекты Range имеют свойство по-умолчанию Item( RowIndex [, ColumnIndex] ) . По правилам VBA при ссылке на default свойства имя свойства ( Item ) можно опускать. Кстати говоря, то что вы привыкли видеть в скобках после Cells , есть не что иное, как это дефолтовое свойство Item , а не родные параметры Cells , который их не имеет вовсе. Ну ладно к Cells все привыкли и это никакого отторжения не вызывает, но если вы увидите нечто подобное - Range("C3:D5")(2,2) , то, скорее всего, будете несколько озадачены, а тем временем - это буквально тоже самое, что и у Cells - всё то же дефолтовое свойство Item . Последняя конструкция ссылается на D4 . А вот для Columns и Rows свойство Item может быть только одночленным, например Columns(1) - и к этой форме мы тоже вполне привыкли. Однако конструкции вида Columns(2)(3)(4) могут сильно удивить (столбец 7 будет выделен).

Примеры кода

Типовые задачи

Перебор ячеек в диапазоне (вариант 1)

В данном примере организован цикл For. Next и доступ к ячейкам осуществляется по их индексу. Вместо parRange(i) мы могли бы написать parRange.Item(i) (выше это объяснялось). Обратите внимание, что мы в этом примере успешно применяем, как вариант с parRange(i,c) , так и parRange(i) . То есть, если мы применяем одночленную форму свойства Item , то диапазон перебирается по строкам ( A1 , B1 , C1 , A2 , . ), а если двухчленную, то столбец у нас зафиксирован и каждая итерация цикла - на новой строке. Это очень интересный эффект, его можно применять для вытягивания таблиц по вертикали. Но - продолжим!

Количество ячеек в диапазоне получено при помощи свойства .Count . Как .Item , так и .Count - это всё атрибуты коллекций, которые широко применяются в объектой модели MS Office и, в частности, Excel.

Задать имя ряда в диаграмме значением из ячейки
Здравствуйте. С VBA до этого не работал.. Но жизнь заставила. Есть таблица со столбцами такого.

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

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

Присвоить переменной имя, заданное для текущей ячейки
Добрый день! Я пока не волшебник, а только учусь. Осваиваю макросы в excel. Задача такая.

Решение

вот это место
"=ЕПУСТО($k2)" это текст
его можно разбить на части и собрать
для примера
"=ЕПУСТО($" & k & "2)"
теперь k это переменная и ей можно задать любую букву
можно воспользоваться свойством
address
тогда будет выглядеть так
Виктор Юшкетов, чтобы код работал независимо от языка и стиля ссылок приложения Так вот как эта формула строится =""=K2 , а я сначала пытался прописывать ="""" - в результате и получал ошибку о неправильной формуле.

Как получить значение ячейки, имя которой записано в переменной?
как получить значение ячейки имя которой записано в переменной

Возможно ли средствами VBA удалить уже существующее имя ячейки рабочего листа?
Возможно ли средствами VBA удалить имя, уже существующее, ячейки рабочего листа ?

Как присвоить переменной имя ячейки ексель по которой кликнул пользователь
Здравствуйте. Известно только то что пользователь обязательно кликнет по ячейке после того как.

Как средствами VBA внутри ячейки Excel скопировать часть текста и присвоить его переменной
Как средствами VBA внутри ячейки Excel скопировать часть(. ) текста и присвоить его какой-нить.

Пожалуй, наиболее часто используемый объект в иерархии объектной модели Excel — это объект Range. Этот объект может представлять одну ячейку, несколько ячеек (в том числе несмежные ячейки или наборы несмежных ячеек) или целый лист. Если в Word вы могли для ввода данных использовать как объект Range, так и объект Selection, то в Excel все сводится к объекту Range:

если вам нужно ввести данные в ячейку или отформатировать ее, то вы должны получить объект Range, представляющий эту ячейку;
если вы хотите сделать что-то с выделенными вами ячейками, вам необходимо получить объект Range, представляющий выделение;
если вам нужно просто что-то сделать с группой ячеек, первое ваше действие — опять-таки получить объект Range, представляющий эту группу ячеек.

В Microsoft Knowledge Base есть статья под номером 291308, в котором описываются 22 способа получения объекта Range в Excel. Вряд ли вы будете пользоваться всеми эти способами. Мы рассмотрим только самые распространенные:

самый простой и очевидный способ — воспользоваться свойством Range. Это свойство предусмотрено для объектов Application, Worksheet и самого объекта Range (если вы решили создать новый диапазон на основе уже существующего). Например, получить ссылку на объект Range, представляющий ячейку A1, можно так:

Dim oRange As Range

А на диапазон ячеек с A1 по D10 — так:

Dim oRange As Range

С применением свойства Range самого объекта Range нужно быть очень осторожным. Дело в том, что Excel создает на основе объекта Range виртуальный лист со своей собственной нумерацией. Поэтому такой код:

Set oRange1 = Worksheets("Лист1").Range("C1")

пропишет значение 20 не в ячейку B1, как можно было понять из кода, а в ячейку D1 (то есть B1 по отношению к виртуальному листу, начинающемуся с C1).

второй способ — воспользоваться свойством Cells. Возможностей у этого свойства меньше — мы можем вернуть диапазон, состоящий только из одной ячейки. Зато мы можем использовать более удобный синтаксис (с точки зрения передачи переменных, перехода в любую сторону на любое количество ячеек и т.п.). Например, для получения ссылки на ячейку D1 можно использовать код вида:

Dim oRange As Range

Set oRange = Worksheets("Лист1").Cells(1, 4)

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

Set oRange = Range(Cells(1, 1), Cells(5, 3))

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

Обычно после того, как нужная ячейка найдена, в нее нужно что-то записать. Для этой цели используется свойство Value, например:

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

Address — позволяет вернуть адрес текущего диапазона, например, для предыдущего примера вернется $A$1:$C$5. Этому свойству можно передать много параметров — для определения стиля ссылки, абсолютного или относительного адреса для столбцов и строк, по отношению к чему этот адрес будет относительным и т.п. Свойство доступно только для чтения. AddressLocal — то же самое, но с поправкой на особенности локализованных версий Excel.

На практике встречается множество ситуаций, когда адрес ячейки нужно разобрать на части и вернуть из него имя столбца или номер строки. Это очень просто сделать при помощи строковых функций — спасибо знакам доллара. Например, имя столбца для объекта oRange, представляющего одну ячейку, можно вернуть так:

sColumnName = Mid(oRange.Address, 2, (InStr(2, oRange.Address, "$") — 2))

sRowNumber = Mid(oRange.Address, (InStr(2, oRange.Address, "$") + 1))

На первый взгляд кажется сложным, но на самом деле все очень просто — для имени столбца мы просто берем все, что у нас находится между первым знаком доллара (он у нас всегда первый символ) и вторым, а для номера строки бы берем все, что у нас находится после второго знака доллара. Найти этот второй знак доллара можно при помощи встроенной функции InStr(), а взять нужное количество символов начиная с какого либо проще всего при помощи встроенной функции Mid().

If Selection.Areas.Count > 1 Then

Debug.Print "Диапазон с несмежными областями"

Borders — возможность получить ссылку на коллекцию Borders, при помощи которой можно управлять рамками для нашего диапазона.
Cells — это свойство есть и для объекта Range. Работает оно точно так же, за исключением того, что опять-таки используется своя собственная виртуальная адресация на основе диапазона:

Dim oRange, oRange2 As Range

Set oRange = Range(Cells(2, 2), Cells(5, 3))

Set oRange2 = oRange.Cells(1, 1) 'Вместо A1 получаем ссылку на B2

Debug.Print oRange2.Address 'Так оно и есть

Точно такие же особенности у свойств Row и Rows, Column и Columns.

Characters — это простое с виду свойство позволяет решить непростую задачу: как изменить (текст или формат) части текста в ячейке, не затрагивая остальные данные. Например, чтобы ввести текст в ячейку A1 и изменить цвет первой буквы, можно воспользоваться кодом

Dim oRange As Range

Если же вам просто нужно изменить значение, то лучше воспользоваться свойством Value — как в третьей строке примера.

Count — возвращает количество ячеек в диапазоне. Может использоваться для проверок.
CurrentRegion — очень удобное свойство, которое может пригодиться, например, при копировании/экспорте данных, полученных из внешнего источника (когда сколько будет этих данных, нам изначально неизвестно). Оно возвращает объект Range, представляющий диапазон, окруженный пустыми ячейками (то есть непустую область, в которую входит исходный диапазон/ячейка). Например, чтобы выделить всю непустую область вокруг активной ячейки, можно воспользоваться кодом

Dependents — позволяет получить объект Range (скорее всего, включающий несмежные области) которые зависят от ячеек исходного диапазона. Работает только для текущего листа — ссылки во внешних листах этим свойством не отслеживаются. Например, чтобы выделить все ячейки, зависимые от активной, можно использовать код
Worksheets("Лист1").Activate
ActiveCell.Dependents.Select

Чтобы просмотреть обратную зависимость, можно использовать свойство Precedents. Чтобы просмотреть только первый уровень зависимостей, можно использовать свойства DirectDependents и DirectPrecedents.

Для чего вообще нужны именованные диапазоны? Обращение к именованному диапазону гораздо удобнее, чем прописывание адреса в формулах и 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. ) .

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

Читайте также: