Excel VBA — Как обратиться к ячейке по ее имени ?
Q: Как обратиться к ячейки по ее имени ? Т.е. есть Лист1 и в нем ячейки с именем Дебет и Кредит. Хочy подсчитать Дебет-Кредит средствами Excel VBA. Попробовал Range(Дебет)-Range(Кредит), ругается, что не описаны переменные.
A: Если я правильно тебя понял, нужно разыменовать ячейку из кода Excel VBA. Вот фрагмент кода, который решает такую задачу:
' Function ValueOfNamedCell ' Возвращает значение ячейки с именем sCellName. в активной рабочей книге. ' Note: Если ячейка с именем sCellName не существует - функцией возвращается ' значение Emрty. Рublic Function ValueOfNamedCell(sCellName As String) As Variant On Error GoTo errНandle ValueOfNamedCell = ActiveWorkbook.Names(sCellName).RefersToRange.Value Exit Function errНandle: ValueOfNamedCell = Emрty End Function
Ссылки на листы по имени
Листы можно определить по имени с помощью свойств Worksheets и Charts . Следующие инструкции активируют различные листы в активной книге.
Worksheets("Sheet1").Activate Charts("Chart1").Activate DialogSheets("Dialog1").Activate Используйте свойство Sheets для возврата листа, диаграммы, модуля или диалогового листа. Коллекция Листов содержит все эти типы листов. В следующем примере активируется лист с именем Chart1 в активной книге.
Sub ActivateChart() Sheets("Chart1").Activate End Sub Примечание Диаграммы, внедренные в лист, являются членами коллекции ChartObjects, а диаграммы , существующие на собственных листах, относятся к коллекции Диаграммы .
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Обратная связь
Были ли сведения на этой странице полезными?
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по
[VBA] Обращение к листу по имени в макросе Excel
Вообщем, у вас в VBA IDE вместо имён листов стоят знаки ______, и макросы не воспринимают вариант Sheets(«лютий»), потому что в языковых настройках системы неверно выставлен параметр: Язык программ, не поддерживающих Юникод — нужно выбрать Русский (или украинский).
- Откройте Панель управления (для этого, нажмите комбинацию клавиш Win+R и введите слово control затем кнопку ОК).
- Найдите пункт Изменение форматов даты, времени и чисел и нажмите его (может называться немного иначе, например «Часы и регион«) — дальше вероятно, придётся ещё раз нажать пункт «Регион«
- В открывшемся диалоговом окне перейдите на вкладку Дополнительно и убедитесь, что Язык программ, не поддерживающих Юникод выбран Русский (Россия). Если другой, нажимаете изменить язык системы и выбираете Русский или Украинский.
- После нажатия ОК перезагружайте компьютер.
После этого к листу можно будет обращаться макросом Sheets(«лютий») т.е. по имени.
// Прим.: также, вариант решения см. в посте № 4.
Кодовое имя листа — что это и зачем нужно? Какие плюсы и минусы?
Часто при разработке различных проектов в VBA требуется где-то хранить различные служебные данные: списки, константы(вроде путей к файлам, email-ов и т.п.). Обычно это делается на отдельных листах, которые потом делают очень скрытыми. А на листах делает еще и потому, что списки удобнее хранить именно на них, да и порой необходимо дать конечному пользователю возможность самому изменять некие данные. Например, программа должна собирать информацию с различных файлов. Но файлы могут располагаться в различных папках — зависит от того, на каком компьютере программа запускается. Или лист содержит списки артикулов, на основании которых программа отбирает некоторые файлы. И эти артикулы могут изменяться со временем. В таких случаях лист не скрывают, а оставляют на виду, чтобы пользователь сам мог прописать путь к нужным папкам и файлам, вписать нужные артикулы и т.п.
В коде к этому листу можно обращаться по имени. Предположим, лист называется «SETS». Тогда код получения данных из ячейки A2 будет выглядеть так:
Dim v v = ThisWorkbook.Sheets("SETS").Range("A2").Value
Dim v v = ThisWorkbook.Sheets(«SETS»).Range(«A2»).Value
Подробнее про обращение к диапазонам можно узнать из статьи: Как обратиться к диапазону из VBA
Все бы ничего, но есть одна ложка дегтя: пользователь может случайно этот лист переименовать. Тогда обращение к листу по заранее заданному имени вызовет ошибку( 9 — Subscript out of range ). Можно пойти другим путем — обратиться к листу по его порядковому номеру. Например, наш лист «SETS» расположен вторым по порядку в книге:
Тогда код получения данных из ячейки A2 будет таким:
Dim v v = ThisWorkbook.Sheets(2).Range("A2").Value
Dim v v = ThisWorkbook.Sheets(2).Range(«A2»).Value
Но и здесь проблема: пользователь может удалить первый лист или просто переместить наш лист «SETS» в другое место. Что опять либо вызовет ошибку 9 — Subscript out of range (если количество листов в книге меньше указанного числа. В нашем примере если в книге будет только один лист) либо запишет нам в переменную v значение из совершенно другого листа и как следствие — это будет не то значение, которое нам нужно.
Как же быть? Есть такое понятие — кодовое имя листа. Т.е. у листа есть два имени: одно отображаемое — это то, которое мы видим на ярлыке(на рисунке выше это Лист1, SETS, Лист2). И есть кодовое имя, которое не видно напрямую. Чтобы увидеть кодовое имя листа необходимо перейти в редактор Visual Basic for Application( Alt + F11 ), отобразить окно проводника проекта —View —Project explorer(или Ctrl + R ), раскрыть папку Microsoft Excel Objects. Там мы увидим все объекты проекта VBA и их имена. Само название листов в этом окне состоит из двух частей: сначала идет кодовое имя листа, а в скобках — имя, отображаемое на ярлыке:
Где искать разобрались. Что нам это дает? Т.к. это имя просто так не найти — пользователь не сможет уже изменить его имя. А обратиться к такому листу из кода VBA проще простого — как и к любому из компонентов проекта — просто по его кодовому имени(для нашего листа «SETS» это Лист2)
Dim v v = Лист2.Range("A2").Value
Dim v v = Лист2.Range(«A2»).Value
Теперь ни переименование ярлыка, ни перемещение не вызовет ошибку. Ошибку теперь может вызвать удаление этого листа. Но это другая история.
Удаление листов можно запретить, защитив структуру книги(Рецензирование (Review) —Защитить книгу (Protect workbook) )
И еще важно помнить: обращение к листу по его кодовому имени приведенным выше способом возможно исключительно внутри той книги, в которой этот лист расположен. Обратиться к нему по привычке с указанием книги уже не получится: ActiveWorkbook.Лист2.Range(«A2»).Value . Т.е. по факту нельзя обратиться к листу по кодовому имени из другой книги. Хотя в большинстве случаев это и не надо. Если уж реальная необходимость возникнет — ниже приведена функция поиска листа по его кодовому имени, которую легко можно переделать под получение отображаемого имени листа, найдя его по кодовому имени.
Чтобы самостоятельно изменить имя кодового листа, необходимо выделить этот лист в проводнике проекта -отобразить окно свойств, если оно еще не отображено(View —Properties Window или F4 ) и просто изменить свойство Name:
Для чего переименовывать? Во-первых, не на всех ПК кириллица воспринимается нормально. Поэтому лучше в кодах использовать латиницу. Во-вторых — код будет более читаемый и наглядный, если обращение будет к объектам вроде wsSets и wsData , чем к объектам вида Лист1 , Лист2 и т.п.
Но при переименовании необходимо помнить, что кодовое имя листа не должно содержать пробелов и иных знаков препинания(кроме нижнего подчеркивания), не должно начинаться с цифр. В общем такие же требования, как и к именам макросов.
Порой необходимо проверить — есть ли лист с указанным кодовым именем. Это к слову о том, как избежать ситуации обращения к несуществующему листу, если обращение идет по кодовому имени(и при этом в проектах Вы почему-то не используете Option Explicit). Можно применить такую простенькую функцию:
'--------------------------------------------------------------------------------------- ' Procedure : RenameSheetCodeName ' Purpose : Ищет в указанной книге лист с указанным кодовым именем ' wb - Книга, лист в которой необходимо найти ' sName - Кодовое имя листа, которое надо проверить на наличие в книге '--------------------------------------------------------------------------------------- Function FindSheetByCodeName(wb As Workbook, sName As String) Dim ws As Worksheet 'цикл по всем листам For Each ws In wb.Worksheets If StrComp(ws.CodeName, sName, 1) = 0 Then 'сравнение имени без учета регистра 'назначаем функции возвращаемое значение FindSheetByCodeName = True Exit Function End If Next End Function
‘————————————————————————————— ‘ Procedure : RenameSheetCodeName ‘ Purpose : Ищет в указанной книге лист с указанным кодовым именем ‘ wb — Книга, лист в которой необходимо найти ‘ sName — Кодовое имя листа, которое надо проверить на наличие в книге ‘————————————————————————————— Function FindSheetByCodeName(wb As Workbook, sName As String) Dim ws As Worksheet ‘цикл по всем листам For Each ws In wb.Worksheets If StrComp(ws.CodeName, sName, 1) = 0 Then ‘сравнение имени без учета регистра ‘назначаем функции возвращаемое значение FindSheetByCodeName = True Exit Function End If Next End Function
Проверить при этом наличие листа с кодовым именем wsSets можно так:
Sub IsShhetCodeName_Exist() If FindSheetByCodeName(ActiveWorkbook, "wsSets") = False Then MsgBox "Нет такого листа", vbCritical, "www.excel-vba.ru" End If End Sub
Sub IsShhetCodeName_Exist() If FindSheetByCodeName(ActiveWorkbook, «wsSets») = False Then MsgBox «Нет такого листа», vbCritical, «www.excel-vba.ru» End If End Sub
И вдогонку код, который поможет быстро переименовать кодовое имя листа по его отображаемому имени. Т.е. все что надо — знать имя листа, отображаемое на ярлыке. И указать новое кодовое имя:
'--------------------------------------------------------------------------------------- ' Procedure : RenameSheetCodeName ' Purpose : Ищет в указанной книге лист с указанным именем ' и переименовывает кодовое имя листа на указанное ' wb - Объект. Книга, лист в которой необходимо переименовать ' sOldName - Текст. Имя листа для переименования ' (может быть как отображаемым на ярлыке, так и кодовым) ' sNewName - Новое кодовое имя листа ' SearchByCodeName - Если True, то лист для переименования ищется ' по кодовому имени листа ' Если False - лист ищется по отображаемому на ярлыке имени '--------------------------------------------------------------------------------------- Function RenameSheetCodeName(wb As Workbook, sOldName As String, sNewName As String, _ Optional SearchByCodeName As Boolean = True) Dim vbc As Object, ws As Worksheet Dim sn As String 'проверяем, нет ли уже в книге компонета с таким именем(sNewName) On Error Resume Next Set vbc = wb.VBProject.VBComponents(sNewName) If Not vbc Is Nothing Then MsgBox "The worksheet '" & sNewName & "' is already exist", vbCritical, "www.excel-vba.ru" 'MsgBox "Компонент с именем '" & sNewName & "' уже есть в проекте", vbCritical, "www.excel-vba.ru" Exit Function End If 'цикл по всем листам и проверка имени For Each ws In wb.Worksheets If SearchByCodeName Then 'если ищем по кодовому имени sn = ws.CodeName Else sn = ws.Name 'если ищем по отображаемому имени End If If StrComp(sn, sOldName, 1) = 0 Then 'сравнение имени без учета регистра 'переименовываем, если имя совпадает Set vbc = wb.VBProject.VBComponents(ws.CodeName) vbc.Name = sNewName 'назначаем функции возвращаемое значение RenameSheetCodeName = True 'выходим из функции(нет смысла продолжать - переименовали) Exit Function End If Next End Function
‘————————————————————————————— ‘ Procedure : RenameSheetCodeName ‘ Purpose : Ищет в указанной книге лист с указанным именем ‘ и переименовывает кодовое имя листа на указанное ‘ wb — Объект. Книга, лист в которой необходимо переименовать ‘ sOldName — Текст. Имя листа для переименования ‘ (может быть как отображаемым на ярлыке, так и кодовым) ‘ sNewName — Новое кодовое имя листа ‘ SearchByCodeName — Если True, то лист для переименования ищется ‘ по кодовому имени листа ‘ Если False — лист ищется по отображаемому на ярлыке имени ‘————————————————————————————— Function RenameSheetCodeName(wb As Workbook, sOldName As String, sNewName As String, _ Optional SearchByCodeName As Boolean = True) Dim vbc As Object, ws As Worksheet Dim sn As String ‘проверяем, нет ли уже в книге компонета с таким именем(sNewName) On Error Resume Next Set vbc = wb.VBProject.VBComponents(sNewName) If Not vbc Is Nothing Then MsgBox «The worksheet ‘» & sNewName & «‘ is already exist», vbCritical, «www.excel-vba.ru» ‘MsgBox «Компонент с именем ‘» & sNewName & «‘ уже есть в проекте», vbCritical, «www.excel-vba.ru» Exit Function End If ‘цикл по всем листам и проверка имени For Each ws In wb.Worksheets If SearchByCodeName Then ‘если ищем по кодовому имени sn = ws.CodeName Else sn = ws.Name ‘если ищем по отображаемому имени End If If StrComp(sn, sOldName, 1) = 0 Then ‘сравнение имени без учета регистра ‘переименовываем, если имя совпадает Set vbc = wb.VBProject.VBComponents(ws.CodeName) vbc.Name = sNewName ‘назначаем функции возвращаемое значение RenameSheetCodeName = True ‘выходим из функции(нет смысла продолжать — переименовали) Exit Function End If Next End Function
Вызвать переименование кодового имени листа можно будет так:
Sub TestRename() 'RenameSheetCodeName(ActiveWorkbook, "Sheet1", "Лист1") 'RenameSheetCodeName(ActiveWorkbook, "Лист1", "Sheet1") If RenameSheetCodeName(ActiveWorkbook, "Sheet1", "Лист1") Then MsgBox "Кодовое имя листа переименовано", vbCritical, "www.excel-vba.ru" End If End Sub
Sub TestRename() ‘RenameSheetCodeName(ActiveWorkbook, «Sheet1», «Лист1») ‘RenameSheetCodeName(ActiveWorkbook, «Лист1», «Sheet1») If RenameSheetCodeName(ActiveWorkbook, «Sheet1», «Лист1») Then MsgBox «Кодовое имя листа переименовано», vbCritical, «www.excel-vba.ru» End If End Sub
Для переименования кодового имени листа программно необходимо, чтобы было проставлено доверие к объектной модели проекта VBA и изменяемый проект не должен быть защищен. Подробнее читайте в статье: Что необходимо для внесения изменений в проект VBA(макросы) программно
Без этого будет невозможно программное вмешательство в проект VBA.
Скачать файл с примером функций:
Кодовое имя листа.xls (59,0 KiB, 682 скачиваний)
Статья помогла? Поделись ссылкой с друзьями!