Главная » Статьи » Excel » Макросы и программы VBA

Sheet happens

Поговорим про то, как в VBA обращаться к листам книги Excel.

Вариант 1

У глобального объекта Excel Application есть 2 коллекции листов: Sheets и Worksheets. Отличаются они тем, что вторая коллекция не включает в себя специальные листы макросов, которые поддерживала Excel 4.0 (на минуточку - 1992 год) во времена, когда VBA ещё не было в природе (появился в Excel 5.0). Тяжело найти эстетов, которые всё ещё пользуются такими листами с макросами, поэтому можно считать, что эти коллекции идентичны. Лично я использую коллекцию Sheets просто потому, что 5 букв лучше, чем 9.

    Sheets(1).Cells(1,1)

Sheets(1) - это не первый лист, созданный в книге, а лист, который на панели ярлыков листов занимает КРАЙНЕЕ ЛЕВОЕ положение. То есть Sheets(2) будет указывать на второй слева лист на панели ярлыков. Если вам нужен конкретный лист, то ТАК ссылаться на него, как вы понимаете, плохая идея, ибо пользователь, изменив порядок следования листов, нарушит работоспособность вашей программы.

Во всех примерах я буду обращаться к ячейки A1, так как речь не о свойствах объекта Worksheet, а о том, какие есть способы получить синтаксическую конструкцию, указывающую на нужный нам лист.

Вариант 2


    Sheets("First").Cells(1,1)

Пожалуй, такое обращение к листу наиболее популярно в народе. В скобках и кавычках мы видим пользовательское имя листа. Плохо только то, что имя листа, что называется "hard coded" (жёстко запрограммировано), что является моветоном в программировании. Ибо, если у вас в модуле конструкция Sheets("First") встречается 100 раз, то при смене имени листа придётся делать поиск с заменой 100 констант в коде. Поэтому обычно делают иначе...

Вариант 3

Гораздо разумней добавить ещё один уровень абстракции и объявить константу, ссылающуюся на имя листа.

    Public Const wsFirst = "First"

    Sheets(wsFirst).Cells(1,1)

В случае смены имени вам придётся исправить ТОЛЬКО эту константу в начале модуля. Однако, если программа написана и сдана пользователю, а тот в один прекрасный момент берёт и переименовывает лист, то наш код тут же перестаёт работать, что, согласитесь, не слишком хорошо...

Вариант 4

У листа есть свойство Name, которое мы видим на ярлыке листа и которое можем менять (если не включена защита структуры книги, но сейчас не об этом), но ещё есть свойство CodeName, которое присваивается листу в момент создания, и которое пользователь сменить не может. В русской редакции офиса эти CodeName-ы формируются по принципу: Лист1, Лист2, Лист3 и т.д. В англоязычной версии это: Sheet1, Sheet2, Sheet3, etc.

Если вы удалите лист, то его CodeName повторно не будет использовано. Так вот - VBA разпознаёт имена совпадающие с CodeName листа, как объекты типа Worksheet.

    Лист1.Cells(1,1) или [Лист1].Cells(1,1)

Это отличная защита от того, что пользователь перенесёт или переименует лист. Но подобные имена ни о чём не говорят нам в процессе программирования в плане того, что располагается на листе, поэтому вы будете частенько ошибаться, путая листы. А те, кто будут потом разбираться в вашем коде, тоже спасибо вам не скажут. Поэтому я предлагаю следующую модификацию этого способа:

Вариант 5


    Public wsData As Worksheet

    Set wsData = Лист1

    wsData.Cells(1,1)

Строку 1 размещаем в объявлениях модуля. Строку 2 размещаем в обработчике события Workbook_Open. И после этого в любом месте книги мы можем использовать объявленные переменные (wsData, строка 3). На мой взгляд, это самый удобный и продвинутый вариант из рассмотренных.

Вариант 6

Не забываем про свойство ActiveSheet объекта Application, указывающее на активный лист книги. Если вы работаете с текущим листом, то использовать его, я считаю, оптимально.

    ActiveSheet.Cells(1,1)


Вариант 7

Ну и последнее, если вам передан в подпрограмму объект типа Range, то не забываем, что ссылку на лист возвращает его свойство Worksheet.


Это всё, что я имел сказать по поводу ссылок на листы в Excel VBA :)

Категория: Макросы и программы VBA | Добавил: dsb75 (27.04.2015) | Автор: Батьянов Денис E W
Просмотров: 3354 | Комментарии: 10 | Теги: Workshets, CodeName, Name, ActiveSheet, VBA, Sheets | Рейтинг: 5.0/1
Всего комментариев: 10
0
4 Kokonoko   (04.09.2015 10:38)
Я раньше тоже через глобальные константы страниц имён делал, а потом увидел в коде у одного из наших сотрудников.
Сейчас так делаю:
https://fotki.yandex.ru/next/users/kokonoko/album/496947/view/1323691

0
5 dsb75   (04.09.2015 15:05)
Но CodeName нельзя исправить в окне Properties... Там через манипуляцию объектами VBA проекта это делается же.

0
6 Kokonoko   (04.09.2015 16:41)
Да... А я там всегда правлю smile

0
7 dsb75   (04.09.2015 22:37)
Ёбаный стыд! surprised

А что же я тогда читал на этот счёт...

0
8 dsb75   (04.09.2015 22:47)
Думал, что можно только так:
ThisWorkbook.VBProject.VBComponents("Sheet2").Name= "NewCodeName"
Спасибо, что сказали ))))

0
9 dsb75   (05.09.2015 10:15)
Спасибо, мил человек, раскрыл глаза!

10 Kokonoko   (05.09.2015 10:53)
Рад был помочь smile

0
1 Kokonoko   (03.09.2015 17:16)
К "Вариант 5" - я обычно переименовываю CodeName часто используемой страницы на манер имени переменной. Например CodeName  "Лист1" -> CodeName "wsData" и сразу работаю с ним.

0
2 dsb75   (03.09.2015 20:33)
Всё правильно. Я, когда статью писал, тоже пытался переименовывать и с разбега почему-то не вышло, хотя действовал казалось бы по инструкциям... Где-то что-то упустил, ясное дело, но так и не разобрался в итоге smile

0
3 dsb75   (03.09.2015 20:34)
Через Workbook_Open кстати говоря не очень-то удачный подход из-за того, что отлаживать неудобно.

Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс.Метрика