Часто при разработке различных проектов в VBA требуется где-то хранить различные служебные данные: списки, константы(вроде путей к файлам, email-ов и т.п.). Обычно это делается на отдельных листах, которые потом делают очень скрытыми. А на листах делает еще и потому, что списки удобнее хранить именно на них, да и порой необходимо дать конечному пользователю возможность самому изменять некие данные. Например, программа должна собирать информацию с различных файлов. Но файлы могут располагаться в различных папках - зависит от того, на каком компьютере программа запускается. Или лист содержит списки артикулов, на основании которых программа отбирает некоторые файлы. И эти артикулы могут изменяться со временем. В таких случаях лист не скрывают, а оставляют на виду, чтобы пользователь сам мог прописать путь к нужным папкам и файлам, вписать нужные артикулы и т.п.
В коде к этому листу можно обращаться по имени. Предположим, лист называется "SETS". Тогда код получения данных из ячейки A2 будет выглядеть так:
Dim v v = ThisWorkbook.Sheets("SETS").Range("A2").Value |
Все бы ничего, но есть одна ложка дегтя: пользователь может случайно этот лист переименовать. Тогда обращение к листу по заранее заданному имени вызовет ошибку(
Тогда код получения данных из ячейки A2 будет таким:
Dim v v = ThisWorkbook.Sheets(2).Range("A2").Value |
Но и здесь проблема: пользователь может удалить первый лист или просто переместить наш лист "SETS" в другое место. Что опять либо вызовет ошибку
Как же быть? Есть такое понятие - кодовое имя листа. Т.е. у листа есть два имени: одно отображаемое - это то, которое мы видим на ярлыке(на рисунке выше это Лист1, SETS, Лист2). И есть кодовое имя, которое не видно напрямую. Чтобы увидеть кодовое имя листа необходимо перейти в редактор Visual Basic for Application(
Где искать разобрались. Что нам это дает? Т.к. это имя просто так не найти - пользователь не сможет уже изменить его имя. А обратиться к такому листу из кода VBA проще простого - как и к любому из компонентов проекта - просто по его кодовому имени(для нашего листа "SETS" это Лист2)
Dim v v = Лист2.Range("A2").Value |
Теперь ни переименование ярлыка, ни перемещение не вызовет ошибку. Ошибку теперь может вызвать удаление этого листа. Но это другая история.
И еще важно помнить: обращение к листу по его кодовому имени приведенным выше способом возможно исключительно внутри той книги, в которой этот лист расположен. Обратиться к нему по привычке с указанием книги уже не получится:
Чтобы самостоятельно изменить имя кодового листа, необходимо выделить этот лист в проводнике проекта -отобразить окно свойств, если оно еще не отображено(View -Properties Window или
Для чего переименовывать? Во-первых, не на всех ПК кириллица воспринимается нормально. Поэтому лучше в кодах использовать латиницу. Во-вторых - код будет более читаемый и наглядный, если обращение будет к объектам вроде
Но при переименовании необходимо помнить, что кодовое имя листа не должно содержать пробелов и иных знаков препинания(кроме нижнего подчеркивания), не должно начинаться с цифр. В общем такие же требования, как и к именам макросов.
Порой необходимо проверить - есть ли лист с указанным кодовым именем. Это к слову о том, как избежать ситуации обращения к несуществующему листу, если обращение идет по кодовому имени(и при этом в проектах Вы почему-то не используете Option Explicit). Можно применить такую простенькую функцию:
'--------------------------------------------------------------------------------------- ' Procedure : FindSheetByCodeName ' 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 |
И вдогонку код, который поможет быстро переименовать кодовое имя листа по его отображаемому имени. Т.е. все что надо - знать имя листа, отображаемое на ярлыке. И указать новое кодовое имя:
'--------------------------------------------------------------------------------------- ' 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 |
Для переименования кодового имени листа программно необходимо, чтобы было проставлено доверие к объектной модели проекта VBA и изменяемый проект не должен быть защищен. Подробнее читайте в статье: Что необходимо для внесения изменений в проект VBA(макросы) программно
Без этого будет невозможно программное вмешательство в проект VBA.
Кодовое имя листа.xls (59,0 КиБ, 732 скачиваний)
А как макросом просто узнать кодовое имя листа?
Nik, ws.CodeName