6.3. Некоторые приемы программирования

Чтобы решить проблему, используя программирование на VBA, необходимо сначала понять, с какими объектами будет работать код. Важным средством изучения этих сведений является Справочник по объектной модели Excel, входящий в справочное руководство разработчика по Excel в библиотеке MSDN.

Первый шаг в программировании – это понять, как управлять конкретными объектами, нужными для решения задачи, такими как листы, имена листов, ячейки и содержимое ячеек. В Excel существует, по крайней мере, два способа решения этой проблемы:

• непосредственное использование справочного руководства по объектной модели;

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

Используем последний подход, т. е. запись макроса. Запись служит отправной точкой последующего процесса. В этом случае задача решается следующим образом.

1. Записать действия, которые нужно кодировать.

2. Просмотреть код и найти строки, выполняющие действия.

3. Удалить оставшуюся часть кода.

4. Изменить записанный код.

5. Добавить переменные, управляющие структуры и другой код, который невозможно записать при записи макроса.

Формулировка задачи. Предположим, что есть книга, содержащая списки на множестве листов, и нужно изменить имя каждого листа в соответствии с заголовком списка на этом листе.

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

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

Задание 24. Запись макроса, переименовывающего лист

1. Нажмите кнопку Записать макрос на вкладке Разработчик.

2. Назовите макрос RenameWorksheets (или другим именем).

3

Рис. 33. Код для макроса, переименовывающего лист

. Переименуйте Лист1 в New Name и нажмите кнопку Остановить запись.

4. На вкладке Разработчик нажмите кнопку Макрос и выберите Изменить, чтобы открыть редактор Visual Basic. Код в редакторе должен быть похож на код рис. 33.

5. Четыре строки комментариев в записанном макросе не решают никаких задач, поэтому удалите их.

6. Следующая строка использует метод Select, чтобы выбрать член Sheet1 (Лист1) объекта коллекции Sheets.

В коде VBA обычно не нужно выбирать объекты перед работой с ними, даже если это делается при записи макросов. Другими словами, эта строка кода является избыточной, поэтому ее тоже можно удалить.

7. Последняя строка записанного макроса изменяет свойство «Name» члена Sheet1 коллекции Sheets. Эту строку нужно сохранить.

Вручную измените имя листа New Name обратно на Лист1, затем выполните макрос. Имя должно измениться обратно на «New Name».

Изменение записанного кода. Изучим коллекцию Sheets, используемую при записи макроса. Раздел «Sheets» в справочном руководстве по объектной модели содержит следующий текст: «коллекция Sheets может содержать объекты Chart или Worksheet».

З

Sub RenameWorksheets()

Worksheets("Sheet1").Name = "New Name"

End Sub

Рис. 34. Измененный код в соответствии с коллекцией Sheets

адание 25. Измените в макросе код в соответствии с рис. 34, поскольку используется только коллекция Worksheets.

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

В VBA есть конструкция, называемая циклом For Each и идеально подходящая для этого случая. Цикл проверяет все элементы в объекте коллекции, таком как Worksheets, и может использоваться для выполнения действия (например, изменения имени) над некоторыми или над всеми этими элементами. Дополнительные сведения о цикле For Each можно посмотреть в справочном руководстве.

Задание 26. Измените созданный макрос так, чтобы он стал похож на код, представленный на рис. 35.

В

Sub RenameWorksheets()

For Each myWorksheet In Worksheets

myWorksheet.Name = "New Name"

Next

End Sub

Рис. 35. Измененный код с использованием цикла

новом коде myWorksheet является переменной, то есть ее значение меняется.

В нашем случае переменная myWorksheet последовательно представляет каждый лист в коллекции Worksheets. Этой переменной можно присвоить любое имя. Хорошей практикой является использование имен переменных, достаточно длинных, чтобы напомнить о ее смысле, но не настолько длинных, чтобы загромождать код.

Если выполнить макрос в его текущем состоянии, то это приведет к ошибке, так как Excel требует, чтобы имя каждого листа в книге было уникальным, а строка myWorksheet.Name = "New Name" указывает Excel присваивать всем листам одно и то же имя.

Задание 27. Чтобы исправить строку так, чтобы можно было проверить работу цикла For Each, измените строку следующим образом:

myWorksheet.Name = myWorksheet.Name & "-changed"

Вместо попытки присвоить каждому листу одно и то же имя, эта строка изменяет текущее имя каждого листа (myWorksheet.Name) на текущее имя с добавлением суффикса "-changed".

Теперь нужно найти способ извлекать данные из самих листов (конкретно из ячейки B1 каждого листа) и помещать эти данные в имена листов.

Для этого случая можно использовать запись макроса, осуществляющего, например, выбор группы ячеек.

Записанный макрос будет содержать объект Range (диапазон). Он используется для задания диапазона ячеек или одной ячейки и имеет свойство Value. И снова, часть .Select не понадобится.

Задание 28. Измените созданный макрос так, чтобы он стал похож на код, представленный на рис. 36.

З

Sub RenameWorksheets()

For Each myWorksheet In Worksheets

myWorksheet.Name = myWorksheet.Range("B1").Value

Next

End Sub

Рис. 36. Измененный код с использованием Range

адание 29. Для извлечения данных из ячейки B1 каждого листа создайте три листа книги, подобные листам рис. 37, а затем выполните макрос.

У

Лист1

Лист2

Лист3

Рис. 37. Пример данных для макроса RenameWorksheets

словный оператор. Выполнение созданного кода для книги, содержащей листы с пустой ячейкой B1, приведет к ошибке, так как значение свойства Value пустого диапазона Range равно "" (пустая текстовая строка), которое не является допустимым именем листа. Нужно запрограммировать макрос так, чтобы осуществлялась проверка содержимого ячейки B1 каждого листа.

Для этого используем условный оператор If…Then. Он указывает выполнять весь код между строкой If и строкой End If, но только при выполнении условия, приведенного в строке If.

Задание 30. Перед строкой myWorksheet.Name добавьте следующую строку кода.

If myWorksheet.Range("B1").Value <> "" Then

А после строки myWorksheet.Name добавьте следующий текст.

End If

В примере проверяемое условие задается конструкцией myWorksheet.Range("B1").Value <> "".

Знак <> означает «не равно», а знаки кавычек, между которыми ничего нет, обозначают пустую текстовую строку. Следовательно, все строки кода между If и End If будут выполнены только в том случае, если значение в ячейке B1 содержит текст.

Объявления переменных. Другим улучшением, которое нужно внести, является помещение в начало макроса объявления переменной myWorksheet, рис. 38:

Dim myWorksheet As Worksheet

Dim является сокращением от «Dimension» (размерность), а Worksheet – типом переменной myWorksheet.

О

Sub RenameWorksheets()

Dim myWorksheet As Worksheet

For Each myWorksheet In Worksheets

'make sure that cell B1 is not empty

If myWorksheet.Range("B1").Value <> "" Then

'rename the worksheet to the contents of cell B1

myWorksheet.Name = myWorksheet.Range("B1").Value

End If

Next

End Sub

Рис. 38. Окончательный текст макроса, переименовывающего листы

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

Хотя объявления переменных в VBA не являются обязательными, их использование настоятельно рекомендуется. Объявление переменных резко упрощает отслеживание переменных и обнаружение ошибок в коде.

Кроме того, при объявлении переменной с типом объекта IntelliSense выводит соответствующий список свойств и методов, связанных с объектом, при последующем использовании этой переменной объекта в макросе.

Комментарии. Без комментариев трудно понять, что делается в коде, особенно когда лицо, изменяющее код, не является автором первоначального кода.

Количество используемых комментариев — это вопрос личного стиля, но в общем случае слишком много комментариев лучше, чем слишком мало комментариев.

Задание 31. Добавьте комментарии для условия If и строки, переименовывающей листы, рис. 38.