2 семестр / vba_2002

Что необходимо знать

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

•Идентифицировать активную рабочую книгу.

•Сосчитать листы активной рабочей книги.

•Получить список названий листов.

•Отсортировать список.

•Изменить порядок следования листов в соответствии с отсортированным списком.

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

Предварительные результаты записи макросов

Ниже приведен пример, который получен в результате записи макроса, позволяющей больше узнать о необходимых для решения поставленной задачи средствах VBA. Начнем с рабочей книги, содержащей три рабочих листа. Затем включим функцию записи макросов и укажем, что макрос должен сохраняться в личной книге макросов. В режиме записи перетащим третий рабочий лист на место первого. Результат записи средствами Excel будет таким:

Sub MacrolO

Sheets("ЛистЗ").Select

Sheets("ЛистЗ").Move Before:=Sheets(1) End Sub

Найдем в справочной системе слово Move: это метод, перемещающий лист в рабочей книге на новое место. Данный метод имеет один аргумент, определяющий положение листа. Таким образом, он имеет непосредственное отношение к нашей задаче.

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

? ActiveWorkbook.Count

Ошибка! Возможно, требуется посчитать листы в книге: ? ActiveWorkbook.Sheets.Count

На этот раз успешно. На рис. 9.8 показан результат — еще немного ценной информации-

Рис. 9.8. Использование окна immediate в VBE для тестированияоператора

Часть ///. VisualBasic forApplications

229

\

Ачто с названиями листов? Проведем еще один тест. Введем в окне Immediate оператор

?ActiveWorkbook . Sheets(I) . Name

Врезультате будет получено название первого листа — ЛистЗ (так и есть на самом деле). Таким образом, мы получили положительный результат.

Далее, конструкция F o r Each - Next используется	для циклического просмотра всех
членов коллекции. Просмотрев соответствующий раздел	справочной системы, напишем ко-

роткую процедуру с использованием этой конструкции:

Sub	T e s t ( )
For Each sht In ActiveWorkbook.Sheets
	MsgBox Sht.Name
Next Sht
End	Sub

И вновь успешно! Макрос отобразил три окна сообщения, в каждом из которых— новое название листа.

Наконец, пришло время подумать о функциях сортировки. Справочная система укажет, что метод S o r t относится к объекту Range. Поэтому одним из решений задачи будет перенесение названия листов в диапазон ячеек и сортировка этого диапазона. Однако такая задача оказалась слишком сложной для данного приложения. Возможно, целесообразнее сформировать из названий листов массив строк, а затем отсортировать этот массив с использованием кода VBA.

Начальная подготовка

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

1.Создайте пустую рабочую книгу с пятью рабочими листами: названия Лнст1, ЛистЗ, ЛистЗ, Лист4 и Лист5.

2.Разместите листы произвольно, чтобы они следовали не по порядку.

3.Сохраните рабочую книгу как Testxls.

4.Активизируйте VBE и выберите проект Personal.xls в окне Project.

Если P e r s o n a l . x l s не появляется в окне Project, это означает, что вы никогда не использовали личную книгу макросов. Excel создаст для вас эту книгу, когда вы запишете макрос (любой) и определите, что он должен сохраняться в личной книге макросов.

5.Добавьте новый модуль VBA (используя команду lnsert=>Modu!e).

6.Создайте пустую процедуру с названием S o r t S h e e t s (рис. 9.9).

Макрос можно сохранить в любом модуле личной книги макросов. Однако лучше хранить каждый макрос в отдельном модуле. Таким образом, вы сможете легко экспортировать модуль и импортировать его is другой проект.

7.Активизируйте Excel. Выполните команду Сервис^Макрос^Макросы и нажмите кнопку Параметры, чтобы присвоить данному макросу комбинацию клавиш. Хорошим выбором будет <Ctrl+Shift+S>.

230	Глава А Работа с процедурами VBA

Рис.9.9. Пустая процедура вмодуле,расположенном вЛичнойкнигемакросов

Создание программы

Теперь настало время приступить к написанию программы. Вначале необходимо поместить названия листов в массив строк. Так как пока неизвестно, сколько листов содержит активная рабочая книга, для объявления массива используем оператор Dim с пустыми скобками. Помните, что всегда можно применить оператор ReDim и изменить размерность массива на требуемое число элементов.

Введем код, вставляющий названия листов в массив SheetNames. Кроме того, в цикл добавим функцию MsgBox, чтобы убедиться, что названия листов на самом деле вводятся в массив:

Sub SortSheets()

Dim SheetNames() as String Dim i as Integer

Dim SheetCount as Integer

SheetCount = ActiveWorkbook.Sheets.Count ReDim SheetNames{1 To SheetCount)

For i = 1 To SheetCount

SheetNames{i) = ActiveWorkbook.Sheets(i).Name MsgBox SheetNames(i)

Next i End Sub

Чтобы проверить эту программу, активизируем рабочую книгу T e s t . x l s и нажмем <Ctrl+Shift+S>. Появится пять окон сообщений с названиями листов активной рабочей книги.

Рекомендуем вам тестировать код по мерс его создания. Когда вы убедитесь, что программа работает правильно, удалите операторы MsgBox (через некоторое время они начнут вас раздражать).

На данном этапе процедура S o r t S h e e t s всего лишь создает массив названий листов в соответствии с порядком указания их в активной рабочей книге. Остается два шага: отсортировать значения в массиве SheetNames, а затем изменить порядок следования листов в книге согласно отсортированному массиву.

ЧастьIII. VisualBasicforApplications

~~231

Вместо того чтобы использовать дпя проверки работы функцию MsgBox, можно применять метод Print объекта Debug, отображающий информацию в окне Immediate. В рассмотренном примере вместо оператора MsgBox целесообразнее обратиться к оператору.

Debug.Print SheetNames(i)

Этот прием может показаться вам не таким навязчивым, как операторы MsgBox.

Создание процедуры сортировки

Переходим к сортировке массива SheetNames . Можно вставить программу сортировки в процедуру S o r t S h e e t s , но лучше написать общую процедуру сортировки, которую можно будет потом использовать для управления другими объектами (сортировка массивов — довольно популярная операция).

Возможно, вас несколько обескуражит идея создания процедуры сортировки. Однако не беспокойтесь: несложно найти стандартные процедуры, которые можно непосредственно или с небольшими изменениями использовать в своей программе. Конечно, наиболее полным источником такой информации является Internet.

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

В пузырьковом методе используется вложенный цикл For - Next, в котором оценивается каждый элемент массива. Если элемент массива больше, чем следующий, то эти два элемента меняются местами. Такое сравнение повторяется для каждой пары элементов (т.е. п- 1 раз).

В главе 11 описаны другие процедуры сортировки и приведено сравнение процедур по скорости выполнения.

Ниже отображена написанная мною процедура сортировки. Sub BubbleSort(Liat() As String)

1Сортировка массива List в возрастающем порядке Dim First As Integer, Last As Integer

Dim i As Integer, j As Integer Dim Temp As String

First = LBound(List) Last = UBound(List)

For i = First To Last - 1 For j = i + 1 To Last

If List(i) > List(j) Then

Temp = List(j)

List(j) = List(i)

List(i) = Temp

End If

Next j

Next i

End Sub

Эта процедура имеет один аргумент: одномерный массив с названием L i s t . Массив, который передается в процедуру, может быть любой длины. Для присвоения нижней и верхней границы массива переменным F i r s t и L a s t использовались функции LBound и UBound, соответственно.

232	Глава 9. Работа с процедурами VBA

После того, как мы убедимся, что процедура работает правильно, изменим процедуру
SortSheets . Для этого добавим вызов процедуры BubbleSort, аргументом которой явля-
ется массив SheetNames. На данном этапе модуль будет иметь такой вид.
Sub SortSheets()
Dim SheetNames()	As String
Dim SheetCount as	Integer
Dim i as Integer
SheetCount = ActiveWorkbook.Sheets.Count
ReDim SheetNames(1		To SheetCount)
For i = 1 To SheetCount
SheetNames(i)	= ActiveWorkbook.Sheets(i).Name
Next i
Call BubbleSort(SheetNames)
End Sub
Sub BubbleSort{List{)	As String)
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp As String
First = LBound(List)
Last = UBound(List)
For i = First To Last - 1
For j = i + 1 To Last
If List{i) > List(j) Then
Temp = List(j)
List(j)		= Lisc(i)
List(i)		= Temp
End If
Next j
Next i
End Sub
По окончании процедуры S o r t S h e e t s образуется массив, состоящий из отсортирован-
ных названий листов активной рабочей книги. Чтобы проверить это, можно отобразить со-
держимое массива в окне Intermediate, добавив в конец процедуры S o r t S h e e t s код:
For i = 1 То SheetCount
Debug.Print SheetNames(i)
Next i
Пока все идет нормально. Осталось написать программу для изменения порядка следова-
ния листов в книге в соответствии с отсортированными элементами массива SheetNames,
Программа, записанная раньше, вновь пригодилась, Помните инструкцию, которая полу-
чена при перемещении листа в рабочей книге в первую позицию?
Sheets{"ЛистЗ").Move Before:=Sheets(1)
Далее напишем цикл For-Next, который просматривает каждый лист и перемещает его
в соответствующее место, указанное в массиве SheetNames:
For i = 1 То SheetCount
Sheets(SheetNames(i)>.Move Before:=Sheets(i)
Next i
Например, в первой итерации цикла счетчик (ij равен 1. Первый элемент массива Sheet-
Names (в данном примере) — лист Лист!. Следовательно, выражение для метода Move в цикле
будет таким:
Sheets("Лист1").Move Sheets(l )
Часть ///. Visual Basic for Applications			233

Во второй итерации цикла выражение имеет следующий вид:

Sheets("Лист2").Move Sheets(2)

В процедуру SortSheets добавим новый код:

Sub SortSheetsО

SheetCount = ActiveWorkbook.Sheets.Count ReDim SheetNames(1 To SheetCount)

For i = 1 To SheetCount

SheetNames(i) = ActiveWorkbook.Sheets(i).Name Next i

Call BubbleSort(SheetNames) For i = 1 To SheetCount

ActiveWorkbook.Sheets ISheetNames{i)).Move _ Before:=ActiveWorkbook.Sheets(i)

Next i End Sub

Тестирование показало, что процедура прекрасно работает с рабочей книгой T e s t . x l s . Теперьнеобходимособратьвеськод.Объявимвсепеременные,используемыевпроцедурах,

идобавим несколько комментариев, а также пустых строк, чтобы программу можно было легче читать. В результате процедура S o r t S h e e t s будет преобразована к следующему виду:

Sub SortSheets{)

1 Эта процедура сортирует листы активной рабочей книги

'Б возрастающем порядке.

Dim SheetNames{) as String

Dim i as Integer

Dim SheetCount as Integer

1 Определение количества листов, изменение размерности массива SheetCount = ActiveWorkbook.Sheets.Count

ReDim SheetNames(1 To SheetCount)

' Заполнение массива названиями листов For i = 1 To SheetCount

SheetNames(i) = ActiveWorkbook.Sheets(i).Name Next I

' Сортировка массива в возрастающем порядке Call BubbleSort(SheetNames)

1 Перемещение листов

For i = 1 То SheetCount ActiveWorkbook.Sheets(SheetNamos(i)).Move _

ActiveWorkbook.Sheets(i) Next i

End Sub

Итак, все работает. Чтобы проверить программу дальше, добавим еще несколько листов в книгу T e s t . x l s и изменим некоторые названия. Программа работает прекрасно!

Дополнительное тестирование

Наверное, вы считаете, что работа окончена. Однако тот факт, что процедура работает с рабочей книгой T e s t . x l s , не означает, что она будет работать со всеми рабочими книгами. Чтобы проверить программу дальше, загрузим несколько других рабочих книг и вновь запустим

234	Глава 9. Работа с процедурами VBA

программу. Скоро вы убедитесь в том, что приложение неидеально (если быть точным, оно далеко от идеала). Были обнаружены следующие проблемы.

•Рабочие книги с большим количеством листов сортируются очень долго, так как при операциях перемещения окно постоянно обновляется.

•Сортировка не всегда выполняется. Например, в одном из тестов лист с названием SUMMARY (все буквы в верхнем регистре) был размещен перед листом под названием Sheet1. Эта проблема вызвана процедурой B u b b l e S o r t (так как U в верхнем регистре считается больше, чем Н в нижнем).

•Если на экране не отображаются окна рабочих книг, при нажатии <Ctrl+Shift+S> макрос выдает ошибку.

•Если структура рабочей книги защищена, метод Move не работает.

•Метод Move не работает корректно при скрытии рабочих листов. Лист фактически перемешается и размещается перед первым нескрытым листом. Такое неожиданное поведение не задокументировано, поэтому можно сделать вывод, что это дефект Excel.

•После сортировки последний лист рабочей книги становится активным листом. Изменение активного листа— не очень удачное решение проблемы; лучше, если бы активным оставался лист, который был таковым до начала выполнения программы.

ЙПри прерывании макроса с помощью комбинации клавиш <Ctrl+Break> VBA отображает сообщение об ошибке.

Решение проблем

Решить проблему обновления изображения на экране несложно. Вставил в начале процедуры Sort Sheets инструкцию

Application.ScreenUpdating = False

Можно было бы исправить и проблему с процедурой BubbleSort. Для этого используем функцию UCase, чтобы преобразовать названия листов в верхний регистр. Таким образом, все сравнения выполняются с названиями в верхнем регистре. Исправленная строка выглядит следующим образом:

If UCase(List(i)) > UCase(List(j)} Then

Иной способ решения "проблемы регистра" — добавить вверху модуля оператор Option Compare Text

В этом случае VBA выполняет сравнение строк на основе не чувствительных к регистру правил сортировки. Другими словами, символ А считается тем же, что и а-

Чтобы избежать сообщения об ошибке, которое появляется, когда все рабочие книги CBepicyты, добавим процедуру проверки ошибок. Если не существует активных рабочих книг, происхо-

дит ошибка. Применим оператор On		E r r o r	Resume	Next, чтобы проигнорировать ошибку,
и затем проверим значение Err . Если E r r			не равняется нулю, это означает, что произошла
ошибка. Следовательно, процедура заканчивается. Ниже приведен код проверки ошибок:
On E r r o r Resume Next
SheetCount	= ActiveWorkbook . Sheets . Count
If E r r <>	0 Then E x i t Sub '	Нет активной		рабочей	книги
Можно и не использовать оператор On E r r o r				Resume	Next. Следующий оператор
непосредственно определяет, свернута ли рабочая книга, и не реализует обработки ошибок:
If ActiveWorkBook Is Nothing Then Exit Sub
Часть ill. Visual Basic forApplications					235

Обычно для зашиты структуры рабочей книги имеется серьезная причина. Мы не будем снимать защиту; вместо этого программа должна отображать предупреждение, чтобы пользователь снял защиту и снова выполнил макрос. Проверку защищенной структуры книги выполнить легко — свойство P r o t e c t S t r u c t u r e объекта Workbook возвращает True, если книга защищена. Поэтому добавим такой блок кода:

' Проверка защиты структуры рабочей книги If ActiveWorkbook.ProtectStructure Then

MsgBox ActiveWorkbook.Name & ' защищена.•, _ vbCritical, •Сортировка листов невозможна."

Exit Sub End If

Сложнее решить проблему со скрытыми листами. Возможно такое решение: использовать массив, контролирующий состояние скрытия каждого листа (которое определено свойством Visible) . Далее можно написать программу отображения листов и снова скрыть листы после окончания сортировки. Определите новый массив (SheetHidden) и используйте такую программу для выявления скрытых листов и их отображения:

1 Заполнение массива скрытыми листами For i = 1 То SheetCount

SheetHidden{i) = Not ActiveWorkbook.Sheets(i).Visible 1 Отображение скрытых листов

If SheetHidden(i) Then ActiveVforkbook.Sheets(i).Visible = True Next i

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

Повторное скрытие		листов
For i = 1 То SheetCount
If	SheetHidden(i)	Then ActiveWorkbook.Sheets(i).Visible = False
Next	i

Так как обновление экрана не происходит, то отображение и скрытие листов осуществляется "незаметно".

Чтобы вновь активизировать рабочий лист после выполнения сортировки, напишите программу, которая присваивает первоначально активный лист переменной объекта (oldActive), а затем активизирует лис г после окончания программы.

При нажатии <Ctrl+Break> выполнение макроса обычно приостанавливается, и VBA выдает сообщение об ошибке. Но так как одна из целей проекта — избежать сообщений об ошибке, то необходимо вставить команду предотвращения подобной ситуации. В справочной системе указано, что объект A p p l i c a t i o n обладает свойством EnableCancelKey, которое может отключить комбинацию клавиш <Clrl+Break>. Поэтому мы добавнм следующий оператор в начало программы:

Application.EnableCancelKey = xlDisabled

Будьтеоченьвнимательны, когдаотключаетепрерываниемакроса. Если программа попадает в бесконечный цикл, выйти из него вы не сможете. Лучше вставлять этот оператор только после того, как вы убедитесь, что все работает идеально.

После внесения указанных выше исправлений процедура S o r t S h e e t s будет выглядеть так, как показано в листинге 9.1. ,

236 Глава 9. Работа с процедурами VBA

Листинг9.1.ОкончательнаяверсияпроцедурыSortSheets

Sub SortSheets{)

1Процедура сортировки листов в активной рабочей книге

Dim SheetNames() As String Dim SheetHiddenО As Boolean Dim i As Integer

Dim SheetCount As Integer Dim VisibleWins As Integer Dim Item As Object

Dim OldActive As Object

If ActiveWorkbook Is Nothing Then Exit Sub • Нет активных книг

SheetCount • ActiveWorkbook.Sheets.Count

1Проверка защиты книги

If ActiveWorkbook.ProtectStructure Then

MsgBox ActiveWorkbook.Name & " защищена.", _ vbCritical, "Невозможно сортировать листы."

Exit Sub End If

' Отключение комбинации клавиш Ctrl+Break Application.EnableCancelKey = xlDisabled

1Получение количества листов

SheetCount = ActiveWorkbook.Sheets.Count

1Изменение размерности массива

ReDim SheetNames(1 To SheetCount) ReDim SheetHidden{1 To SheetCount}

Сохранение ссылки на активный лист Set Old&ctive = ActiveSheet

'Заполнение массива именами листов For i = 1 То SheetCount

SheetNames(i) = ActiveWorkbook.Sheets(i).Name

Next i

	For i = 1 To SheetCount
	SheetHidden(i)	= Not ActiveWorkbook.Sheets{i).Visible
1	unhide hidden	sheets

If SheetHidden(i) Then ActiveWorkbook.Sheets(i).Visible = True

Next i

1Сортировка массива

Call BubbleSort(SheetNameg}

'Отключение функции обновления экрана Application.ScreenUpdating = False

1Перемещение листов

For i - 1 To SheetCount ActiveWorkbook.Sheets(SheetNames(i)}.Move _

Before:=ActiveWorkbook.Sheets(i)

Next i

Часть ///. Visual Basic for Applications

237

'	Повторное скрытие листов
	For i = 1 То SheetCount
	If SheetHidden(i) Then ActiveWorkbook.Sheets(i).Visible
False
	Next i
1	Повторная активизация исходного листа
	OldActive.Activate
End	Sub

Доступность утилиты

Так как макрос S o r t S h e e t s сохранен в личной книге макросов, он всегда доступен при запуске Excel. На этом этапе макрос может выполняться при рыборе названия макроса в диалоговом окне Макрос (это окно отображается при нажатии <AU+F8>) или нажатии <Ctrl+Shift+S>.

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

Оценка проекта

Итак, результат получен. Утилита соответствует всем изначальным требованиям: она сортирует все листы в активной рабочей книге, ее можно легко выполнить, она всегда доступна, выполняется (что легко проверить) для всех рабочих книг и пока еще не отображала сообщений об ошибке VBA.

В процедуре все еще присутствует одна небольшая проблема: сортировка достаточно строгая и не всегда кажется "логичной", Например, после сортировки лист Лист11 размещается перед листом Лист2, хотя большинство пользователей хотят видеть Лист2 перед Лист11.

На Web-узле издательства содержится еще одна версия утилиты, в которой решена описанная выше проблема. Эта версия утилиты разбивает названия листов на две компоненты: текст слева и число справа (если оно есть). Эти данные сохраняются на рабочем листе, а процедура использует функцию сортировки Excel для правильной организации частей имени. Тем не менее, и эта утилита не идеальна, поскольку даже в ней не будет правильно расположен лист с названиемЛист1Часть2,

В данном упражнении я попытался продемонстрировать процесс разработки процедур VBA. Таким образом, вы получили ценный урок, если до сих пор еще не занимались программированием на VBA на профессиональном уровне. Мы рассмотрели ряд основных моментов.

•Разработка удачной процедуры — не всегда линейный процесс.

•Зачастую полезно выполнять короткие процедуры в окне Immediate, чтобы проверить идеи или методы, прежде чем применять их в работе.

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

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

238	Глава 9. Работа с процедурами VBA