Кодовое имя листа - что это и зачем нужно? Какие плюсы и минусы?

Часто при разработке различных проектов в VBA требуется где-то хранить различные служебные данные: списки, константы(вроде путей к файлам, email-ов и т.п.). Обычно это делается на отдельных листах, которые потом делают очень скрытыми. А на листах делает еще и потому, что списки удобнее хранить именно на них, да и порой необходимо дать конечному пользователю возможность самому изменять некие данные. Например, программа должна собирать информацию с различных файлов. Но файлы могут располагаться в различных папках - зависит от того, на каком компьютере программа запускается. Или лист содержит списки артикулов, на основании которых программа отбирает некоторые файлы. И эти артикулы могут изменяться со временем. В таких случаях лист не скрывают, а оставляют на виду, чтобы пользователь сам мог прописать путь к нужным папкам и файлам, вписать нужные артикулы и т.п.
В коде к этому листу можно обращаться по имени. Предположим, лист называется "SETS". Тогда код получения данных из ячейки A2 будет выглядеть так:

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

Но и здесь проблема: пользователь может удалить первый лист или просто переместить наш лист "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

Теперь ни переименование ярлыка, ни перемещение не вызовет ошибку. Ошибку теперь может вызвать удаление этого листа. Но это другая история.
Удаление листов можно запретить, защитив структуру книги(Рецензирование(Review) -Защитить книгу(Protect workbook))
И еще важно помнить: обращение к листу по его кодовому имени приведенным выше способом возможно исключительно внутри той книги, в которой этот лист расположен. Обратиться к нему по привычке с указанием книги уже не получится: ActiveWorkbook.Лист2.Range("A2").Value. Т.е. по факту нельзя обратиться к листу по кодовому имени из другой книги. Хотя в большинстве случаев это и не надо. Если уж реальная необходимость возникнет - ниже приведена функция поиска листа по его кодовому имени, которую легко можно переделать под получение отображаемого имени листа, найдя его по кодовому имени.
Чтобы самостоятельно изменить имя кодового листа, необходимо выделить этот лист в проводнике проекта -отобразить окно свойств, если оно еще не отображено(View -Properties Window или F4) и просто изменить свойство Name:

Для чего переименовывать? Во-первых, не на всех ПК кириллица воспринимается нормально. Поэтому лучше в кодах использовать латиницу. Во-вторых - код будет более читаемый и наглядный, если обращение будет к объектам вроде wsSets и wsData, чем к объектам вида Лист1, Лист2 и т.п.
Но при переименовании необходимо помнить, что кодовое имя листа не должно содержать пробелов и иных знаков препинания(кроме нижнего подчеркивания), не должно начинаться с цифр. В общем такие же требования, как и к именам макросов.

'---------------------------------------------------------------------------------------
' 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

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

'---------------------------------------------------------------------------------------
' 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 KiB, 679 скачиваний)

    Видеоуроки

Поиск по меткам