«Bill Jelen, Mr. Excel Tracy Syrstad 800 East 96th Street Indianapolis, Indiana 46240 ?изнес-решения Применение VBA и макросов в Microsoft® Excel Билл Джелен, “Мистер Excel” Трейси Сирстад Москва • Санкт-Петербург • Киев ...»

R1C1 — сильные стороны И все же необходимо отдать должное Microsoft. Познакомившись с R1C формулами, вы поймете, что они намного полезнее обычных формул (особенно ярко это проявляется при использовании формул в VBA). Приме нение ссылок в стиле R1C1 позволяет создавать более эффективный код.

Кроме того, употребление адресации R1C1 является обязательным требовани ем при создании формул массивов и при задании условного форматирования ячеек. Именно последнее и обуславливает необходимость ознакомления с та инственным стилем записи ссылок R1C1.

Использование стиля ссылок R1C1 в Excel Чтобы указать на необходимость использования в Excel адресации R1C1, выберите команду главного меню Сервис Параметры (Tools Options), пе рейдите во вкладку Общие (General) и установите флажок Стиль ссылок R1C1 (R1C1 reference style) (рис. 6.1).

Рис. 6.1. Чтобы выбрать адресацию R1C1, установите флажок Стиль ссылок R1C1 на вкладке Общие диалогового окна Параметры После перехода к стилю ссылок R1C1 буквы в заголовках столбцов (A, B, C и т.д.) будут заменены цифрами (1, 2, 3 и т.д.), как показано на рис. 6.2.

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

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

Как “размножаются” формулы Рассмотрим достаточно простую электронную таблицу, показанную на рис. 6.3.

Рис. 6.3. С помощью маркера заполнения формула может быть скопирована из одной ячейки в другую Введите в ячейку D4 формулу =C4*B4 и с помощью маркера заполнения скопируйте ее в ячейки D5:D9.

164 Часть I Первые шаги Формула в ячейке F4 включает в себя как абсолютные, так и относитель ные ссылки: =ЕСЛИ(E4;ОКРУГЛ(D4*$B$1;2);0) (=IF(E4,ROUND(D4*$B$1, 2),0)). Благодаря наличию знаков доллара ($) эта формула всегда вычисляет произведение общей цены товара, находящейся в столбце D текущей строки, на величину налога, внесенную в ячейку B1.

Результат вычисления значений ячеек, показанный на рис. 6.4, был достигнут с помощью формул, показанных на рис. 6.5. (Чтобы переключиться в режим ото бражения формул в Excel, воспользуйтесь комбинацией клавиш.) Рис. 6.4. Результат вычисления значений ячеек в столбцах D, Рис. 6.5. Чтобы переключиться в режим отображения формул в Excel, воспользуйтесь комбинацией клавиш. Возможность автоматического копирования форму лы из одной ячейки в другую поистине удивительна!

Обратите внимание, что для достижения показанного на рис. 6.5 результа та необходимо ввести вручную всего лишь 4 формулы: 3 в ячейки D4, F4, G и 1 в ячейку G10. В остальные ячейки формулы могут быть скопированы ав томатически. Превращение формулы =F4+D4 в ячейке G4 при ее копировании в ячейку G5 в формулу =F5+D5 приводит начинающих пользователей Excel в настоящий восторг!

Разоблачение На самом деле Excel оперирует только R1C1 формулами. Поддержка формул в стиле A1 для совместимости со стандартом VisiCalc и Lotus реализо вана исключительно на уровне интерфейса Excel.

Перейдя к стилю ссылок R1C1, можно заметить, что ‘‘различные’’ форму лы в ячейках D4:D9 на самом деле являются одной и той же R1C1 формулой.

Это же справедливо и по отношению к ячейкам F4:F9 и G4:G9 (рис. 6.6).

Рис. 6.6. Переход к стилю ссылок R1C1 дает неожиданный результат — все формулы, расположенные в столбцах 4 и 6, одинаковые Поскольку поддержка формул в стиле A1 реализована исключительно на уров не интерфейса Excel, становится ясно, что автоматическое копирование формулы из одной ячейки в другую отнюдь не является чем то экстраординарным.

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

Практикум Использование ссылок в стиле A1 и R1C1 в VBA Заполним ячейки рассмотренной выше таблицы с помощью VBA, используя ссыл ки в стиле A1. Прежде всего, введем формулы в ячейки D4, F4 и G4. Далее, скопи руем их и вставим в требуемые ячейки, как показано ниже:

Sub BookA1Style() ' Нахождение последней строки с данными.

FinalRow = Range("B65536").End(xlUp).Row ' Ввод формул.

Range("D4").Formula = "=B4*C4" ' В англоязычной версии Excel:

' Range("F4").Formula = "=IF(E4,ROUND(D4*$B$1,2),0)" Range("F4").FormulaLocal = "=ЕСЛИ(E4;ОКРУГЛ(D4*$B$1;2);0)" Range("G4").Formula = "=F4+D4" ' Копирование формул в строке 4 в требуемые ячейки.

Range("D4").Copy Destination:=Range("D5:D" & FinalRow) Range("F4:G4").Copy Destination:=Range("F5:G" & FinalRow) ' Формирование итоговой строки.

Cells(FinalRow + 1, 1).Value = "Всего" ' В англоязычной версии Excel:

' Cells(FinalRow + 1, 6).Formula = "=SUM(G4:G" & FinalRow & ")" Cells(FinalRow + 1, 6).FormulaLocal = "=СУММ(G4:G" & _ FinalRow & ")" End Sub 166 Часть I Первые шаги Как видим, понадобилось 3 строки для ввода формул и еще 2 для их копирования в требуемые ячейки.

Эквивалентный код, в котором используются ссылки в стиле R1C1, позволяет вве сти формулу сразу в целый диапазон ячеек с помощью одной строки кода, как по Sub BookR1C1Style() ' Нахождение последней строки с данными.

FinalRow = Range("B65536").End(xlUp).Row Range("D4:D" & FinalRow).FormulaR1C1 = "=RC[-1]*RC[-2]" ' В англоязычной версии Excel:

' Range("F4:F" & FinalRow).FormulaR1C1 = _ "=IF(RC[-1],ROUND(RC[-2]*R1C2,2),0)" Range("F4:F" & FinalRow).FormulaR1C1Local = _ "=ЕСЛИ(RC[-1];ОКРУГЛ(RC[-2]*R1C2;2);0)" Range("G4:G" & FinalRow).FormulaR1C1 = "=+RC[-1]+RC[-3]" ' Формирование итоговой строки.

Cells(FinalRow + 1, 1).Value = "Всего" ' В англоязычной версии Excel:

' Cells(FinalRow + 1, 6).Formula = "=SUM(G4:G" & FinalRow & ")" Cells(FinalRow + 1, 6).FormulaLocal = _ "=СУММ(G4:G" & FinalRow & ")"End Sub Ссылки в стиле R1C Согласно стилю R1C1, обращение к ячейке осуществляется по номеру ее строки (R) и столбца (С). Подробное рассмотрение ссылок в стиле R1C1 нач нем с относительных ссылок как наиболее часто используемых в формулах на рабочем листе и в VBA.

Относительные ссылки в стиле R1C Рассмотрим задачу обращения к ячейке в формуле Excel. Чтобы определить адрес ячейки, следует задать смещение по строкам и столбцам в квадратных скобках после букв ‘‘R’’ и ‘‘C’’, соответственно.

Положительное смещение по столбцам обозначает смещение вправо на указанное число позиций, а отрицательное смещение по столбцам смеще ние влево на указанное число позиций. К примеру, адрес ячейки F5 относи тельно ячейки E5 выглядит как RC[1], а адрес ячейки D5 относительно ячей ки E5 — как RC[-1].

Положительное смещение по строкам обозначает смещение вниз на ука занное число позиций, а отрицательное смещение по строкам смещение вверх на указанное число позиций. К примеру, адрес ячейки E6 относительно ячейки E5 выглядит как R[1]C, а адрес ячейки E4 относительно ячейки E5 — как R[-1]C.

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

Чтобы обратиться к ячейке D4 из ячейки E5, следует воспользоваться формулой =R[-1]C[-1].

Чтобы обратиться к ячейке D5 из ячейки E5, следует воспользоваться формулой =RC[-1].

Чтобы обратиться к ячейке F5 из ячейки E5, следует воспользоваться формулой =RC[1].

Чтобы обратиться к ячейке E5 из ячейки E5, следует воспользоваться формулой =RC. Последняя формула определяет так называемую цик лическую ссылку и, как правило, никогда не применяется на практике.

Более наглядно обращение к различным ячейкам из ячейки E5 продемон стрировано на рис. 6.7.

Рис. 6.7. Пример использования относительных ссылок в Относительные ссылки в стиле R1C1 можно применять и для обращения к диапазону ячеек. К примеру, следующая формула суммирует значение 12 ячеек, расположенных слева от ячейки с формулой:

=СУММ(RC[-12]:RC[-1]) (В англоязычной версии Excel эта формула записывается как =SUM(RC[RC[-1]).) Абсолютные ссылки в стиле R1C Абсолютная ссылка всегда указывает на ячейку, расположенную в опреде ленном месте. При использовании адресации в стиле A1 абсолютные ссылки задаются путем предварения буквы столбца и номера строки знаком доллара ($), например $B$2.

При использовании адресации в стиле R1C1 абсолютные ссылки задаются путем опускания квадратных скобок, например R2C2.

Смешанные ссылки в стиле R1C Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец.

168 Часть I Первые шаги Рассмотрим макрос, импортирующий в Excel файл Счет.txt. Адрес ито говой строки вычисляется с помощью выражения.End(xlUp). В определен ные ячейки итоговой строки необходимо поместить сумму значений всех ячеек, расположенных выше ячейки с формулой вплоть до 2 й строки включительно:

Sub MixedReference() TotalRow = Cells(65536, 1).End(xlUp).Row + Cells(TotalRow, 1).Value = "Всего" ' В англоязычной версии Excel:

' Cells(TotalRow, 5).Resize(1, 3).FormulaR1C1 = _ '"=SUM(R2C:R[-1]C)" Cells(TotalRow, 5).Resize(1, 3).FormulaR1C1Local = _ "=СУММ(R2C:R[-1]C)" Как показано в приведенном выше коде, упомянутый диапазон ячеек зада ется как R2C:R[-1]C. Таким образом, единственная R1C1 формула может быть использована для обращения к диапазону ячеек неопределенного разме ра, что еще раз подтверждает эффективность использования ссылок в стиле R1C1 (рис. 6.8).

Рис. 6.8. Пример использования смешанной ссылки в стиле R1C Обращение к строке или столбцу с помощью ссылок в стиле R1C Рассмотрим задачу нахождения максимального значения в столбце G. По скольку точное число строк с данными заранее неизвестно, для этого можно применить A1 формулу =МАКС($G:$G) (=MAX($G:$G)) или R1C1 формулу =МАКС(C7) (=MAX(C7)). Аналогично, для того чтобы найти минимальное значение в строке 1, можно применить A1 формулу =МИН($1:$1) (=MIN($1:$1)) или R1C1 формулу =МИН(R1) (=MIN(R1)). Обратиться к строке или столбцу можно также с помощью относительной ссылки в стиле R1C1. Например, чтобы найти среднее всех значений в строке, расположенной над текущей ячейкой, можно воспользоваться формулой =СРЗНАЧ(R[-1]) (=AVERAGE(R[-1])).

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

Построение таблицы умножения с помощью единственной R1C1 формулы Введите числа от 1 до 12 в ячейки B1:M1. Выделите эти ячейки и скопируй те их с применением транспонирования в ячейки A2:A13. Создадим формулу, вычисляющую для каждой ячейки из диапазона B2:M13 произведение соот ветствующих чисел из 1 го столбца и 1 й строки. Естественно, здесь не обой тись без ссылок в стиле R1C1:

Sub MultiplicationTable() ' Построение таблицы умножения с помощью одной R1C1-формулы.

Range("B1:M1").Value = Array(1, 2, 3, 4, 5, 6, 7, _ 8, 9, 10, 11, 12) Range("B1:M1").Font.Bold = True Range("B1:M1").Copy Range("A2:A13").PasteSpecial Transpose:=True Range("B2:M13").FormulaR1C1 = "=RC1*R1C" Cells.EntireColumn.AutoFit End Sub Формула =RC1*R1C проста до гениальности. Результатом выполнения при веденного выше кода является таблица умножения, показанная на рис. 6.9.

Внимание Обратите внимание, что после выполнения макроса диапазон ячеек B1:M1 остается активным элементом буфера обмена. Если нажать клавишу, содержимое этого диапазона будет скопировано в текущую выделенную область рабочего листа, что, вообще говоря, нежелательно. Чтобы выйти из режима копирования/вставки, добавьте в конце макроса строку Application.CutCopyMode = False.

170 Часть I Первые шаги Интересный факт Проведем небольшой эксперимент. Выделите ячейку F6 и начните запись нового макроса, выбрав в меню Excel команду Сервис Макрос Начать запись (Tools Macro Record New Macro). Щелкните на кнопке Относительная ссылка (Relative Reference), расположенной на панели инструментов Остановить запись (Stop Recording). Введите формулу =A1 и нажмите комби нацию клавиш, чтобы остаться в ячейке F6. Щелкните на кноп ке Остановить запись, расположенной на одноименной панели инструмен тов, чтобы остановить запись макроса.

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

Sub PointFiveRowsUp() ActiveCell.FormulaR1C1 = "=R[-5]C[-5]" А теперь выделите ячейку A1 и выполните только что созданный макрос.

Вероятно, вы ожидаете получить сообщение о распространенной ошибке вре мени выполнения 1004? Как бы не так! В результате выполнения макроса формула в ячейке A1 будет ссылаться на ячейку IR65532, что фактически оз начает переход из левой верхней в правую нижнюю часть рабочего листа. Не смотря на то, что этот весьма любопытный факт не имеет какой либо практи ческой ценности, его знание может пригодиться при написании программ ного кода.

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

К сожалению, запомнить, что буква ‘‘U’’ является 21 й буквой латинского алфавита, получается далеко не сразу. Следующая игра поможет вам быстрее натренировать свою память:

Sub QuizColumnNumbers() Ans = InputBox("Заголовком какого столбца является _ буква " & Chr(64 + i) & "?") заголовком столбца # " & i Если вы не считаете подобную игру забавной или же вам необходимо уз нать порядковый номер какого нибудь ‘‘удаленного’’ столбца (например, DG), обратитесь к интерфейсу Excel. Выделите ячейку A1 и, удерживая нажатой клавишу, нажмите несколько раз клавишу. Пока длина выделен ного диапазона ячеек не вышла за пределы экрана, порядковый номер по следнего столбца можно узнать с помощью поля Имя (Name Box), располо женного слева от поля ввода формулы, как показано на рис. 6.10.

Рис. 6.10. Пока длина выделенного диапазона ячеек не вышла за пределы экрана, количество выделенных строк и столбцов отображается в поле Имя слева от поля ввода Когда длина выделенного диапазона ячеек выйдет за пределы экрана, ко личество выделенных строк и столбцов будет отображаться с помощью всплы вающей подсказки. В частности, порядковый номер столбца DG равен 111, как показано на рис. 6.11.

Рис. 6.11. Когда длина выделенного диапазона ячеек выходит за пределы экрана, количество выделенных строк и столбцов ото бражается с помощью всплывающей подсказки Использование ссылок в стиле R1C1 при условном форматировании ячеек При условном форматировании ячеек рекомендуется использовать R1C формулы. Применение A1 формул является нежелательным (по имеющимся наблюдениям, примерно 1 из 50 ячеек с условным форматированием, опреде ленным с помощью A1 формул, содержит ошибку).

Задание условного форматирования с помощью пользовательского интерфейса Существует два вида условного форматирования. Открыв диалоговое окно Условное форматирование (Conditional Formatting) с помощью команды ме ню Формат Условное форматирование (Format Conditional Formatting), можно заметить, что по умолчанию в качестве критерия форматирования ис пользуется значение ячейки. Например, вы можете определить особый фор 172 Часть I Первые шаги мат для ячеек, содержащих отрицательные числа. Согласно второму типу ус ловного форматирования критерий форматирования определяется с помощью формулы, принимающей значение True или False. Формула может содер жать ссылки на любые ячейки рабочего листа, а также объединять несколько критериев с помощью функций ИЛИ (OR) и И (AND). Для каждой ячейки можно определить 3 различных критерия форматирования.

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

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

Для реализации такого решения создадим небольшой макрос VBA.

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

Первый критерий форматирования основывается на R1C1 формуле (тип xlExpression), второй на значении ячейки (тип xlCellValue). Обрати те внимание, что кроме значения ячейки необходимо указать также и опера тор сравнения. После задания критериев форматирования определяется сам формат ячеек (в данном случае цвет текста), как показано ниже:

Sub ApplySpecialFormattingAll() ' Сокрытие текста ячеек, содержащих сообщения об ошибке.

For Each ws In ThisWorkbook.Worksheets ws.UsedRange.FormatConditions.Delete For Each cell In ws.UsedRange.Cells ' В англоязычной версии Excel:

' cell.FormatConditions.Add Type:=xlExpression, _ cell.FormatConditions.Add Type:=xlExpression, _ Formula1:="=ИЛИ(ЕОШИБКА(RC);ЕНД(RC))" cell.FormatConditions(1).Font.Color = _ cell.Interior.Color cell.FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLess, Formula1:="0" cell.FormatConditions(2).Font.ColorIndex = End Sub Внимание Не используйте A1 формулы для задания критерия условного форматирования в VBA. Соответствующий код может выполняться корректно лишь для небольшого числа ячеек, однако при попытке его применения к 50 и более ячейкам существует вероятность сбоя (более подробно об этом рассказывается в следующем практи куме). Использование R1C1 формул полностью лишено подобного недостатка.

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

Рис. 6.13. Классический пример, позволяющий выявить одну из про блем задания условного форматирования ячеек с помощью VBA 174 Часть I Первые шаги Ниже приведен соответствующий код VBA:

Sub FindMinMax() ' Выделить строку, содержащую наибольший доход, зеленым цветом.

' Выделить строку, содержащую наименьший доход, желтым цветом.

FinalRow = Cells(Application.Rows.Count, 1).End(xlUp).Row With Range("A2:I" & FinalRow) ' В англоязычной версии Excel:

'.FormatConditions.Add Type:=xlExpression, _ 'Formula1:="=RC7=MAX(C7)".FormatConditions.Add Type:=xlExpression, _ Formula1:="=RC7=МАКС(C7)".FormatConditions(1).Interior.ColorIndex = ' В англоязычной версии Excel:

'.FormatConditions.Add Type:=xlExpression, _ 'Formula1:="=RC7=MIN(C7)".FormatConditions.Add Type:=xlExpression, _ Formula1:="=RC7=МИН(C7)".FormatConditions(2).Interior.ColorIndex = Для нахождения строки с наибольшим значением в столбце G используется фор мула МАКС(C7) (MAX(C7)), синтаксис которой позволяет считать ее и A1, и R1C формулой. Вспомним, что Microsoft изначально использовала ссылки в стиле R1C и ввела поддержку ссылок в стиле A1 только для совместимости с Lotus 1 2 3. По скольку в первую очередь Excel пытается проинтерпретировать формулу с исполь зованием ссылок в стиле R1C1 (это недокументированная особенность Excel), вы полнение приведенного выше кода приведет к желаемому результату.

А теперь представьте себе, что случится, если вы зададите условное форматиро вание с помощью формулы, ссылающейся на ячейку C7 или R22. Вы получите со всем не тот результат, на который рассчитывали, поскольку Excel воспримет эти ссылки как ссылки на 7 й столбец и 22 ю строку, соответственно. Именно поэтому при задании условного форматирования рекомендуется использовать только R1C1 формулы.

Использование ссылок в стиле R1C при создании формулы массива Формулы массива это одни из наиболее мощных формул в Excel. Ино гда их называют CSE формулами, поскольку при вводе формулы массива ис пользуется комбинация клавиш.

Формула массива, введенная в ячейке E20 (рис. 6.14), реализует 18 операций умножения и суммирование полученных при каждом умножении результатов.

Если ввести формулу массива без применения комбинации клавиш, в ячейке будет выведено сообщение об ошибке #ЗНАЧ!

(#VALUE!). Обратите внимание, что при вводе формулы массива брать ее в фигурные скобки не нужно.

Рис. 6.14. Формула массива, введенная в ячейке E20, реали зует 18 операций умножения и суммирование полученных при каждом умножении результатов. Для ввода формулы необхо димо использовать комбинацию клавиш Ниже приведен код, использующийся для ввода формулы массива:

Sub EnterArrayFormulas() ' Ввод формулы массива.

FinalRow = Cells(65536, 1).End(xlUp).Row Cells(FinalRow + 1, 5).FormulaArray = _ "=SUM(R2C[-1]:R[-1]C[-1]*R2C:R[-1]C)" End Sub Несмотря на то что в интерфейсе Excel отображаются ссылки в стиле A1, помните, что формулу массива необходимо вводить с использованием ссылок в формате R1C1.

Следующий шаг Согласно стилю R1C1, обращение к ячейке осуществляется по номеру ее строки (R) и столбца (С). Использование R1C1 формул позволяет создавать более эффективный и корректный код VBA. Кроме того, употребление адре сации R1C1 является обязательным требованием при создании формул масси вов и при задании условного форматирования ячеек. В следующей главе будут рассмотрены именованные диапазоны ячеек еще одно средство, упро щающее разработку макросов в Excel.

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

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

Глобальные и имени

локальные имена Все имена можно разделить на две категории действия распространяется на всю ра бочую книгу) и локальные имена (область действия ограничена одним рабочим листом). В рабочей книге мо жет содержаться несколько одинако вых локальных имен, однако только одно уникальное глобальное имя. При обращении к локальному имени необ ходимо указывать соответствующий рабочий лист (Лист1!Фрукты), тогда как при обращении к глобальному имени никаких дополнительных тре бований не ставится (Фрукты).

Показанное на рис. 7.1 диалого вое окно Присвоение имени (Define Name) содержит смешанный спи сок, состоящий из глобальных и ло кальных имен.

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

178 Часть I Первые шаги При совпадении глобального и локального имени в окне Присвоение имени выводится только локальное имя при условии, что соответствующий ему рабочий лист является активным (рис. 7.2).

Если же активным является другой рабочий лист, в окне Присвоение имени выводится только глобальное имя, как показано на рис. 7.3.

Средство проверки вводимых значений (чтобы воспользоваться этим средством, выберите команду меню Excel Данные Проверка (Data Validation)) позволяет выделить диапазон ячеек только на активном рабочем листе. Чтобы обойти это ограничение, присвойте имя диапазону данных, которые вы хотите проверить.

Рис. 7.3. При совпадении глобального и локаль бальное имя при условии, что соответствующий Создание имен Рассмотрим пример кода, сгенерированного средством записи макросов при создании именованного диапазона ячеек:

ActiveWorkbook.Names.Add Name:="Фрукты", _ RefersToR1C1:="=Лист2!R1C1:R6C6" Как следует из приведенного выше кода, именованный диапазон ячеек но сит имя Фрукты и охватывает ячейки A1:F6 (R1C1:R6C6). Формулу, опреде ляющую диапазон ячеек, следует предварить знаком равенства и взять в ка вычки. Адрес диапазона задается с помощью абсолютной ссылки (с использо ванием знаков $) или с помощью ссылки в стиле R1C1. Если диапазон ячеек находится на текущем активном листе, имя последнего можно не указывать (тем не менее, указание полного адреса диапазона ячеек повышает читабель ность кода).

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

ActiveWorkbook.Names.Add Name:="Лист2!Фрукты", _ RefersToR1C1:="=Лист2!R1C1:R6C6" или воспользуйтесь коллекцией Names данного рабочего листа:

Worksheets("Лист1").Names.Add Name:="Фрукты", _ RefersToR1C1:="=Лист1!R1C1:R6C6" Существует и более простой способ создания имен. Ниже приведены при меры создания глобального и локального имени, соответственно:

Range("A1:F6").Name = "Фрукты" Range("A1:F6").Name = "Лист1!Фрукты" К сожалению, указанный выше способ имеет одно существенное ограни он позволяет создавать только имена диапазонов ячеек. Для создания чение имен формул, строк, чисел и массивов необходимо использовать метод Add.

180 Часть I Первые шаги Чтобы изменить существующее имя, воспользуйтесь свойством Name, как показано ниже:

Names("Фрукты").Name = "Товар" В результате выполнения приведенного выше кода имя Фрукты становится недействительным; к соответствующему диапазону ячеек можно обратиться по новому имени Товар.

Если строка Range("A1:F6").Name = "Фрукты" расположена выше строки Range("A1:F6").Name = "Товар", имя Товар переопределяет имя Фрукты, как показано на рис. 7.4.

Попытка обращения к локальному имени Фрукты приведет к возникнове нию ошибки, поскольку такое имя больше не существует.

Удаление имен Чтобы удалить имя, воспользуйтесь методом Delete, как показано ниже:

Names("ТоварНомер").Delete Попытка удаления несуществующего имени приведет к возникновению ошибки.

При совпадении глобального и локального имени следует обратить особое вни мание на необходимость корректного указания удаляемого имени.

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

В следующих разделах рассматриваются имена формул, строк, чисел и массивов.

Имена формул Синтаксис создания имени формулы аналогичен синтаксису создания имени диапазона ячеек, поскольку при определении последнего используется формула:

Names.Add Name:="ТоварСписок", RefersTo:="=СМЕЩ(_ Лист2!A2;0;0;СЧЁТЗ(Лист2!$A:$A))" (В англоязычной версии Excel функции СМЕЩ соответствует функция OFFSET.) В результате выполнения приведенной выше строки кода будет определено имя формулы, ссылающейся на динамический столбец (рис. 7.5).

Имена строк При создании имени строки значение последней заключается в кавычки (знак равенства, как при создании имени диапазона и формулы, не ставится):

Names.Add Name:="Компания", RefersTo:="КомпанияА" На рис. 7.6 показано диалоговое окно Присвоение имени (Define Name), в списке имен которого содержится имя строки.

Совет Поскольку имена не теряют свои значения между сеансами работы в Excel, они больше подходят для хранения данных, чем ячейки. Рассмотрим задачу хранения названия компании, ставшей лучшим поставщиком за определенный промежуток времени. Одним из решений этой задачи является создание имени ЛучшийПоставщик. Альтернативный способ хранения имени лучшего поставщика заключа ется в использовании ячейки на отдельном рабочем листе, что менее удобно.

182 Часть I Первые шаги Следующая процедура демонстрирует пример использования ячеек для хранения данных между сеансами работы в Excel:

Sub NoNames(ByRef CurrentTop As String) TopSeller = Worksheets("Переменные").Range("A1").Value If CurrentTop = TopSeller Then MsgBox ("Лучшим поставщиком снова стал ' &&TopSeller &'!") MsgBox ("Лучшим поставщиком стал " & CurrentTop) Ниже приведен код аналогичной процедуры, использующий для хранения данных между сеансами работы в Excel имена:

Sub WithNames() If Evaluate("Текущий") = Evaluate("Предыдущий") Then MsgBox ("Лучшим поставщиком снова стал " & _ Evaluate("Предыдущий") & "!") MsgBox ("Лучшим поставщиком стал " & Evaluate("Текущий")) Поскольку Текущий и Предыдущий являются заранее созданными имена ми, доступ к их значениям осуществляется напрямую, без необходимости соз дания переменных. Обратите внимание, что для доступа к значению имени используется метод Evaluate.

Длина строки не может превышать 255 символов.

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

NumofSales = Names.Add Name:="ПродажиВсего", RefersTo:=NumofSales Names.Add Name:="ПродажиВсего", RefersTo:= Обратите внимание на отсутствие кавычек и знака равенства. Кавычки способны превратить число в строку, а знак равенства в формулу.

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

NumofSales = Names("ПродажиВсего").Value NumofSales = [ПродажиВсего] Следует отметить, что использование квадратных кавычек равносильно вызову метода Evaluate и способно в определенной степени затруднить по нимание программного кода. Чтобы сделать код более читабельным, реко мендуется воздержаться от использования метода Evaluate или снабдить со ответствующие фрагменты кода комментариями.

Имена массивов Имена могут использоваться для хранения целых массивов. Размер масси ва ограничен 256 столбцами и 5461 элементами (более подробно массивы рас сматриваются в главе 17, ‘‘Массивы’’).

Создание имени массива похоже на создание имени числа:

Sub NamedArray() Dim myArray(10, 5) Dim i As Integer, j As Integer ' Заполнение массива myArray значениями.

' Создание имени массива.

Names.Add Name:="Массив", RefersTo:=myArray End Sub Как и при создании имени числа, обратите внимание на отсутствие кавы чек и знака равенства.

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

Рассмотрим небольшой пример. Выделите произвольный диапазон ячеек на рабочем листе и выберите команду меню Excel Файл Область печати Задать (File Print Area Set Print Area).

Как показано на рис. 7.7, Excel автоматически присваивает выделенному диапазону ячеек имя Область_печати (Print_Area).

Это одно из зарезервированных имен, значение которого сохраняется меж ду сеансами работы в Excel (попробуйте сохранить, закрыть, а затем снова от крыть рабочую книгу, и вы убедитесь, что имя Область_печати соответству ет все тому же диапазону ячеек).

184 Часть I Первые шаги Каждый рабочий лист имеет собственную область печати. Назначение новой об ласти печати переопределяет текущее значение имени Область_печати для дан ного рабочего листа.

Ниже приведен список зарезервированных имен Excel:

Область_печати (Print_Area);

Заголовки_для_печати (Print_Titles).

Имена Критерии и Извлечь используются при копировании результата применения расширенного фильтра в новое место (чтобы применить рас ширенный фильтр, выберите команду меню Excel Данные Фильтр Расширенный фильтр (Data Filter Advanced Filter)).

Имя База_данных использовалось в предыдущих версиях Excel для обо значения данных, которыми манипулировали определенные функции. Сейчас имя База_данных используется разве что при создании форм ввода данных (чтобы создать форму ввода данных, выберите команду меню Excel Данные Форма (Data Form)).

Имя Область_печати используется при задании области печати (выберите команду Файл Область печати Задать) или ее изменении посредством диалогового окна Параметры страницы (Page Setup) (выберите команду Файл Параметры страницы (File Page Setup)).

Имя Заголовки_для_печати используется при установке заголовков пе чати (выберите команду Файл Параметры страницы, перейдите во вкладку Лист (Sheet) и установите флажок Заголовки строк и столбцов (Row and co lumn headings)).

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

Worksheets("Лист4").Names("Область_печати").Delete В результате выполнения приведенного выше кода вместо имени ОбластьПечати будет удалено зарезервированное имя Excel.

Скрытие имен Благодаря наличию у объекта Name свойства Visible имя можно скрыть.

Чтобы скрыть имя, установите значение свойства Visible равным False;

чтобы вновь сделать имя видимым, установите значение свойства Visible равным True:

Names.Add Name:="ТоварНомер", RefersTo:="=$A$1", Visible:=False Внимание Если пользователь создаст имя, совпадающее с существующим скрытым именем, последнее будет переопределено безо всякого предупреждения. Чтобы избежать этого, защитите рабочий лист.

Проверка существования имени Следующая функция проверяет существование имени (включая скрытые имена), за исключением зарезервированных имен Excel:

Function NameExists(FindName As String) As Boolean Dim Rng As Range Dim myName As String On Error Resume Next myName = ActiveWorkbook.Names(FindName).Name If Err.Number = 0 Then End Function Предыдущий код также является великолепным примером того, как можно извлечь выгоду из ошибок. Если переданное функции имя не существует, гене рируется ошибка, однако благодаря строке On Error Resume Next код про должает выполняться как ни в чем не бывало. Определить факт наличия ошибки поможет свойство Err.Number. Если значение этого свойства равно 0, пере данное функции имя существует (при выполнении кода не было сгенерировано ошибки), в противном случае нет (была сгенерирована ошибка).

Практикум Использование именованных диапазонов ячеек при поиске значений в столбце данных Рассмотрим следующую задачу. Каждый день в Excel импортируется файл со све дениями о продажах товара в сети розничных магазинов. Файл содержит номера 186 Часть I Первые шаги магазинов, но не их названия. Необходимо реализовать автоматическое добавле ние названий магазинов с целью последующего создания отчетов.

Типичное решение этой задачи заключается в создании таблицы с названиями всех магазинов на дополнительном рабочем листе и применении макроса для до бавления названий магазинов на основном рабочем листе.

Всю процедуру можно разбить на 6 этапов.

1. Импортирование файла данных.

2. Нахождение всех уникальных номеров магазинов.

3. Проверка, все ли номера магазинов внесены в таблицу соответствия номеров и названий магазинов.

4. Если необходимо, занести в таблицу соответствия номеров и названий магази нов сведения о новых магазинах.

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

6. Добавить названия магазинов на основном рабочем листе.

Представленный далее исходный код макроса реализует приведенную выше по следовательность действий:

Sub ImportData() ' Основной рабочий лист - "Данные".

Set WSD = ThisWorkbook.Worksheets("Данные") ' Вспомогательный рабочий лист - "Таблица".

Set WSM = ThisWorkbook.Worksheets("Таблица") ' Код импорта файла закомментирован.

' Предполагается, что данные уже импортированы.

' Импортировать файл.

' Workbooks.Open Filename:="C:\Sales.csv" ' Скопировать данные и закрыть файл.

'Range("A1").CurrentRegion.Copy Destination:=WSD.Range("A1") 'ActiveWorkbook.Close SaveChanges:=False ' Активизировать рабочий лист с данными.

' Составить список уникальных номеров магазинов.

FinalRow = Cells(65536, 1).End(xlUp).Row Range("A1").Resize(FinalRow, 1).AdvancedFilter _ Action:=xlFilterCopy, CopyToRange:=Range("Z1"), Unique:=True ' Проверить, все ли номера магазинов находятся в таблице ' соответствия номеров и названий магазинов.

FinalStore = Range("Z65536").End(xlUp).Row Range("AA1").Value = "В списке?" ' В англоязычной версии Excel:

' Range("AA2:AA" & FinalStore).FormulaR1C1 = _ ' "=ISNA(VLOOKUP(RC[-1],СписокМагазинов,1,False))" Range("AA2:AA" & FinalStore).FormulaR1C1Local = _ "=ЕНД(ВПР(RC[-1];СписокМагазинов;1;ЛОЖЬ))" ' Найти строку для ввода сведений о новом магазине.

NextRow = WSM.Range("A65536").End(xlUp).Row + ' Добавить сведения о новых магазинах.

For i = 2 To FinalStore If Cells(i, 27).Value = True Then WSM.Cells(NextRow, 1).Value = ThisStore WSM.Cells(NextRow, 2).Value = InputBox(Prompt:=" _ Введите имя магазина " & ThisStore, Title:="Найден новый магазин") ' Удалить вспомогательный список магазинов.

Range("Z1:AA" & FinalStore).Clear ' Переопределить имя "СписокМагазинов".

FinalStore = WSM.Range("A65536").End(xlUp).Row WSM.Range("A1:B" & FinalStore).Name = "СписокМагазинов" ' Добавить названия магазинов.

Range("B1").EntireColumn.Insert Range("B1").Value = "МагазинНазвание" ' В англоязычной версии Excel:

' Range("B2:B" & FinalRow).FormulaR1C1 = _ "=VLOOKUP(RC1,СписокМагазинов,2,False)" Range("B2:B" & FinalRow).FormulaR1C1Local = _ "=ВПР(RC1;СписокМагазинов;2;ЛОЖЬ)" 'Заменить формулы значениями.

Range("B2:B" & FinalRow).Value = Range("B2:B" & FinalRow).Value End Sub Следующий шаг Следующая глава посвящена событиям концепции, позволяющей VBA реагировать на действия пользователя, такие как выделение ячейки, смена те кущего активного рабочего листа и т.п.

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

Различают следующие уровни со бытий.

Уровень приложения. Охваты вает действия, имеющие не посредственное отношение к События листа диаграммы....... Excel, например Applica- События приложения................. tion_NewWorkbook. Следующий шаг

Уровень рабочей книги. Охва тывает действия, имеющие непосредственное отношение к рабочей книге, например Workbook_Open.

Уровень рабочего листа. Охваты вает действия, имеющие непо средственное отношение к ра бочему листу, например Worksheet_SelectionChange.

Уровень листа диаграммы. Ох ватывает действия, имеющие непосредственное отношение к листу диаграммы, например Chart_Activate.

Код обработки событий рабочей книги размещается в модуле ЭтаКнига (ThisWorkbook), код обработ ки событий рабочего листа в моду ле этого рабочего листа (например, Лист1), код обработки событий листа диаграммы и Excel — в соответствую щих модулях классов. При обработке событий можно вызывать процедуры и функции из других модулей.

190 Часть I Первые шаги Таким образом, нет необходимости дублировать один и тот же код для не скольких рабочих листов его можно поместить в отдельный модуль и вызы вать при обработке события каждого листа.

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

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

При выборе модуля ЭтаКнига (ThisWorkbook), модуля листа или модуля класса соответствующие события становятся доступны посредством рас крывающихся списков Object (Объект) и Procedure (Процедура), как по казано на рис. 8.1.

Рис. 8.1. Редактор Visual Basic позволяет выбрать требуемое событие с помощью раскрываю щихся списков Object и Procedure После выбора объекта из раскрывающегося списка Object в раскрывающем ся списке Procedure появляются соответствующие этому объекту события. Вы бор события приводит к автоматической генерации верхнего (Private Sub) и нижнего (End Sub) заголовка процедуры, как показано на рис. 8.2.

Рис. 8.2. Редактор Visual Basic автоматически генерирует верхний и нижний заголовок процедуры Параметры событий Некоторые события имеют параметры (например, Target и Cancel), предназначенные для передачи коду обработки события определенной ин формации. Рассмотрим событие Worksheet_BeforeRightClick, срабаты вающее перед выполнением действия, соответствующего щелчку правой кнопкой мыши в пользовательском интерфейсе Excel. Присвоение параметру Cancel значения True предотвращает выполнение стандартного действия, в данном случае отображения контекстного меню:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean) Cancel = True End Sub Запрет обработки событий Некоторые события могут вызывать другие события, включая самих себя.

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

Чтобы избежать описанной выше ситуации, запретите обработку событий в начале кода процедуры и разрешите ее в конце, как показано ниже:

Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Range("A1").Value = Target.Value Application.EnableEvents = True End Sub На заметку Чтобы прервать выполнение макроса, нажмите клавишу или воспользуй тесь комбинацией клавиш.

События рабочей книги Ниже приведен список событий, существующих на уровне рабочей книги Excel:

Deactivate;

BeforeSave;

BeforePrint;

BeforeClose;

SheetActivate;

SheetDeactivate;

SheetCalculate;

SheetBeforeDoubleClick;

SheetSelectionChange;

192 Часть I Первые шаги Событие Workbook_Activate() Событие Workbook_Activate срабатывает, когда содержащая это собы тие рабочая книга становится активной.

Событие Workbook_Deactivate() Событие Workbook_Deactivate срабатывает, когда содержащая это со бытие рабочая книга перестает быть активной.

Событие Workbook_Open() Событие Workbook_Open является стандартным событием рабочей книги.

Код его обработки выполняется непосредственно при открытии файла рабочей книги, перед выводом на экран пользовательского интерфейса. Одним из наи более распространенных применений события Workbook_Open является про верка имени пользователя и определение его прав на доступ к рабочей книге.

Следующий код проверяет имя пользователя и, если оно не равно ‘‘Администратор’’, защищает рабочий лист от внесения изменений со сторо ны пользователя (за это отвечает параметр UserInterfaceOnly):

Private Sub Workbook_Open() If Application.UserName "Администратор" Then sht.Protect UserInterfaceOnly:=True Событие Workbook_Open можно применять и для создания пользователь ских меню или панелей управления. Следующий код создает меню Меню MrExcel с двумя пунктами, как показано на рис. 8.3.

Более подробно создание пользовательского меню рассматривается в разделе “Создание пользовательского меню” главы 24 на с. 575.

Private Sub Workbook_Open() Dim cbWSMenuBar As CommandBar Dim Ctrl As CommandBarControl, muCustom As CommandBarControl Dim iHelpIndex As Integer Dim sht As Worksheet ' Инициализация событий приложения и листа диаграммы.

InitializeAppEvent InitializeChart CreateToolbar Set cbWSMenuBar = Application.CommandBars("Worksheet menu bar") iHelpIndex = cbWSMenuBar.Controls("Справка").Index Set muCustom = cbWSMenuBar.Controls.Add(Type:=msoControlPopup, _ Before:=iHelpIndex, Temporary:=True) For Each Ctrl In cbWSMenuBar.Controls If Ctrl.Caption = "Меню &MrExcel" Then cbWSMenuBar.Controls("Меню MrExcel").Delete Next Ctrl With muCustom.Caption = "Меню &MrExcel" With.Controls.Add(Type:=msoControlButton) With.Controls.Add(Type:=msoControlButton) End Sub Рис. 8.3. Событие Workbook_Open можно применять для создания пользова Событие Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Событие Workbook_BeforeSave срабатывает при попытке сохранения рабочей книги. Чтобы отобразить диалоговое окно Сохранение документа (Save As), установите значение параметра SaveAsUI равным True. Чтобы за претить сохранение рабочей книги, установите равным True значение пара метра Cancel.

Событие Workbook_BeforePrint(Cancel As Boolean) Событие Workbook_BeforePrint срабатывает при попытке печати ра бочей книги (способ инициирования процесса печати с помощью команды меню, кнопки панели инструментов, комбинации клавиш или программного 194 Часть I Первые шаги кода не играет роли). Чтобы запретить печать, установите значение пара метра Cancel равным True.

Приведенный ниже код отслеживает попытки печати рабочего листа, зано ся в журнал дату и время печати, имя пользователя и имя рабочего листа (рис. 8.4):

Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim PrintLog As Worksheet Set PrintLog = Worksheets("PrintLog") LastRow = PrintLog.Range("A65536").End(xlUp).Row +.Cells(LastRow, 1).Value = Now().Cells(LastRow, 2).Value = Application.UserName.Cells(LastRow, 3).Value = ActiveSheet.Name Событие Workbook_BeforePrint можно использовать для создания верхнего и нижнего колонтитула печатаемой страницы. Несмотря на то, что теперь это можно сделать с помощью диалогового окна Параметры страницы (Page Setup), раньше подобная возможность реализовывалась только с помо щью программного кода:

Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.RightFooter = ActiveWorkbook.FullName Событие Workbook_BeforeClose(Cancel As Boolean) Событие Workbook_BeforeClose срабатывает при попытке закрыть ра бочую книгу. Чтобы сделать закрытие рабочей книги невозможным, устано вите значение параметра Cancel равным True.

Событие Workbook_BeforeClose можно применить для удаления поль зовательского меню:

Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim cbWSMenuBar As CommandBar Set cbWSMenuBar = Application.CommandBars("Worksheet menu bar") cbWSMenuBar.Controls("Меню MrExcel").Delete Приведенный выше код имеет один недостаток. Если в рабочую книгу бы ли внесены изменения, Excel предложит сохранить их, отобразив соответст вующее диалоговое окно после выполнения кода обработки события Workbook_BeforeClose. Если вы передумаете закрывать книгу и щелкните на кнопке Отмена (Cancel), то уже не сможете вернуть удаленное меню.

Решение этой проблемы заключается в выводе на экран собственного диа логового окна сохранения рабочей книги:

Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim cbWSMenuBar As CommandBar If Not ThisWorkbook.Saved Then Msg = "Сохранить изменения, внесенные в " & Me.Name & "?" Response = MsgBox(Msg, vbQuestion + vbYesNoCancel) Select Case Response Set myAppEvent = Nothing Set myClassModule = Nothing On Error Resume Next Set cbWSMenuBar = Application.CommandBars("Worksheet menu bar") cbWSMenuBar.Controls("Меню MrExcel").Delete End Sub Событие Workbook_NewSheet(ByVal Sh As Object) Событие Workbook_NewSheet срабатывает при добавлении в текущую активную рабочую книгу нового листа. Sh — это объект нового рабочего листа или листа диаграммы.

Событие Workbook_WindowResize(ByVal Wn As Window) Событие Workbook_WindowResize срабатывает при изменении размера окна текущей активной рабочей книги. Wn — это объект окна.

На заметку Событие Workbook_WindowResize срабатывает только при изменении размера окна текущей активной рабочей книги. Изменение размера окна Excel является со бытием уровня приложения и не имеет никакого отношения к событиям уровня рабочей книги.

196 Часть I Первые шаги Следующий код делает невозможным изменение размера окна текущей ак тивной рабочей книги:

Private Sub Workbook_WindowResize(ByVal Wn As Window) Wn.EnableResize = False Запрет изменения размера окна рабочей книги приводит к удалению кнопок окна Свернуть (Minimize) и Развернуть (Maximize). Чтобы вернуть кнопки на место, выполните в окне Immediate (Быстрое выполнение) строку ActiveWindow.EnableResize = True.

Событие Workbook_WindowActivate(ByVal Wn As Window) Событие Workbook_WindowActivate срабатывает при активизации ок на любой рабочей книги. Wn — это объект окна.

Событие Workbook_WindowActivate срабатывает только при активизации окна рабочей книги.

Событие Workbook_WindowDeactivate(ByVal Wn As Window) Событие Workbook_WindowDeactivate срабатывает при деактивизации окна любой рабочей книги. Wn — это объект окна.

Событие Workbook_WindowDeactivate срабатывает только при деактивизации окна рабочей книги.

Событие Workbook_AddinInstall() Событие Workbook_AddinInstall срабатывает при установке рабочей книги в качестве надстройки (чтобы установить надстройку, выберите коман ду меню Excel Сервис Надстройки (Tools Add Ins)). Обратите внимание, что событие Workbook_AddinInstall не срабатывает при двойном щелчке на значке файла надстройки (файл с расширением.xla).

Событие Workbook_AddinUninstall() Событие Workbook_AddinUninstall срабатывает при удалении рабочей книги, используемой в качестве надстройки. Обратите внимание, что над стройка не закрывается автоматически.

Событие Workbook_SheetActivate(ByVal Sh As Object) Событие Workbook_SheetActivate срабатывает при активизации лю бого рабочего листа или листа диаграммы в рабочей книге. Sh — это объект активного листа.

Кроме того, активизация рабочего листа или листа диаграммы приводит к срабатыванию событий активного рабочего листа (Worksheet_Activate) или листа диаграммы (Chart_Activate).

Событие Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Событие Workbook_SheetBeforeDoubleClick срабатывает при вы полнении двойного щелчка на рабочем листе или листе диаграммы текущей активной рабочей книги. Sh — это объект активного рабочего листа или листа диаграммы, а Target — объект, на котором был выполнен двойной щелчок.

Чтобы запретить стандартное действие, предпринимаемое при обработке двойного щелчка, установите значение параметра Cancel равным True.

Кроме того, двойной щелчок на рабочем листе или листе диаграммы приводит к срабатыванию событий активного рабочего листа (Worksheet_BeforeDoubleClick) или листа диаграммы (Chart_BeforeDoubleClick).

Событие Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Событие Workbook_SheetBeforeRightClick срабатывает при выпол нении щелчка правой кнопкой мыши на рабочем листе или листе диаграммы текущей активной рабочей книги. Sh — это объект активного рабочего листа или листа диаграммы, а Target — объект, на котором был выполнен щелчок правой кнопкой мыши. Чтобы запретить стандартное действие, предприни маемое при обработке щелчка правой кнопкой мыши, установите значение параметра Cancel равным True.

Кроме того, щелчок правой кнопкой мыши на рабочем листе или листе диаграммы приводит к срабатыванию событий активного рабочего листа (Worksheet_BeforeRightClick) или листа диаграммы (Chart_BeforeRightClick).

Событие Workbook_SheetCalculate(ByVal Sh As Object) Событие Workbook_SheetCalculate срабатывает при пересчете рабо чего листа или обновлении листа диаграммы. Sh — это объект активного ра бочего листа или листа диаграммы.

198 Часть I Первые шаги Кроме того, пересчет рабочего листа или обновление листа диаграммы приводит к срабатыванию событий активного рабочего листа (Worksheet_ Calculate) или листа диаграммы (Chart_Calculate).

Событие Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Событие Workbook_SheetChange срабатывает при изменении любого диапазона ячеек на рабочем листе. Sh — это объект рабочего листа, Target — объект измененного диапазона ячеек.

Кроме того, изменение диапазона ячеек рабочего листа приводит к сраба тыванию события конкретного рабочего листа (Worksheet_Change).

Событие Workbook_SheetDeactivate(ByVal Sh As Object) Событие Workbook_SheetDeactivate срабатывает при деактивизации любого рабочего листа или листа диаграммы в текущей активной рабочей книге. Sh — это объект рабочего листа или листа диаграммы, который был де активизирован.

Кроме того, деактивизация рабочего листа или листа диаграммы приводит к срабатыванию событий конкретного рабочего листа (Worksheet_Deactivate) или листа диаграммы (Chart_Deactivate).

Событие Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink) Событие Workbook_SheetFollowHyperlink срабатывает при щелчке на гиперссылке, расположенной на любом рабочем листе активной рабочей книги. Sh — это объект активного рабочего листа, Target — объект гипер ссылки.

Кроме того, щелчок на гиперссылке, расположенной на рабочем листе, приводит к срабатыванию события активного рабочего листа (Worksheet_ FollowHyperlink).

Событие Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Событие Workbook_SheetSelectionChange срабатывает при выделе нии нового диапазона ячеек на любом рабочем листе активной рабочей книги.

Sh — это объект активного рабочего листа, Target — объект выделенного диапазона ячеек.

Кроме того, выделение нового диапазона ячеек приводит к срабатыванию события активного рабочего листа (Worksheet_SelectionChange).

События рабочего листа Ниже приведен список событий, существующих на уровне рабочего листа Excel:

BeforeDoubleClick;

BeforeRightClick;

Calculate;

Deactivate;

FollowHyperlink;

SelectionChange.

Событие Worksheet_Activate() Событие Worksheet_Activate срабатывает при активизации соответст вующего этому событию рабочего листа. Примером использования события Worksheet_Activate является создание плавающей панели инструментов:

Private Sub Worksheet_Activate() On Error Resume Next Application.CommandBars("Панель инструментов _ MrExcel").Visible = True End Sub Событие Worksheet_Deactivate() Событие Worksheet_Deactivate срабатывает при деактивизации соот ветствующего этому событию рабочего листа:

Private Sub Worksheet_Deactivate() On Error Resume Next Application.CommandBars("Панель инструментов _ MrExcel").Visible = False End Sub На заметку При переключении между рабочими листами первым срабатывает событие Worksheet_Deactivate исходного рабочего листа, и только затем — событие Worksheet_Activate активизированного рабочего листа.

Событие Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Событие Worksheet_BeforeDoubleClick срабатывает в результате двойного щелчка на соответствующем этому событию рабочем листе. TarЧасть I Первые шаги get — это объект выделенного диапазона ячеек. По умолчанию параметр Cancel имеет значение False. Присвоение этому параметру значения True приведет к отмене стандартного действия, предпринимаемого при обработке двойного щелчка (в данном случае переход в режим изменения содержи мого ячейки).

Следующий код делает невозможным переход в режим изменения содер жимого ячейки путем двойного щелчка на ней:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Приведенный выше код не запрещает применять двойной щелчок для изменения высоты строки и ширины столбца.

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

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Target.Interior.ColorIndex = Событие Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Событие Worksheet_BeforeRightClick срабатывает при щелчке пра вой кнопкой мыши на рабочем листе. Target — это объект, на котором щелкнули правой кнопкой мыши. Установка значения параметра Cancel равным True запрещает выполнение стандартного действия, предпринимае мого при обработке щелчка правой кнопкой мыши.

Событие Worksheet_Calculate() Событие Worksheet_Calculate срабатывает при пересчете рабочего Рассмотрим небольшой пример. Если прибыль, полученная за определен ный месяц текущего года, превысила прибыль, полученную за аналогичный период в прошлом году, под названием месяца выводится зеленая стрелка, указывающая вверх; в противном случае выводится красная стрелка, указы вающая вниз, как показано на рис. 8.5:

Private Sub Worksheet_Calculate() Select Case Range("C3").Value Case Is > Range("C4").Value SetArrow 10, msoShapeDownArrow Case Is < Range("C4").Value SetArrow 3, msoShapeUpArrow End Select End Sub Private Sub SetArrow(ByVal ArrowColor As Integer, ByVal ArrowDegree) ' Удалить все существующие фигуры.

For Each Sh In ActiveSheet.Shapes If Left(Sh.Name, 4) = "Auto" Then ActiveSheet.Shapes.AddShape(ArrowDegree, 17.25, _ 43.5, 5, 10).Select With Selection.ShapeRange.ForeColor.SchemeColor = ArrowColor.BackColor.RGB = RGB(255, 255, 255) Range("A3").Select End Sub Рис. 8.5. Пример использования события Worksheet_Calculate для добавления на рабочий 202 Часть I Первые шаги Событие Worksheet_Change(ByVal Target As Range) Событие Worksheet_Change срабатывает при изменении содержимого ячейки, например, при вводе, редактировании или удалении текста. Target — это объект ячейки, содержимое которой было изменено.

Событие Worksheet_Change срабатывает также при вставке значения в ячейку, однако оно не срабатывает при пересчете ячейки. При пересчете ячейки срабаты вает событие Worksheet_Calculate.

Рассмотрим задачу введения времени отправки и прибытия авиарейсов в часовом формате (например, 23:45). Ее можно упростить, поручив Excel ввод символа двоеточия.

Вот как это сделать с помощью события Worksheet_Change:

Private Sub Worksheet_Change(ByVal Target As Range) Dim UserInput As String, NewInput As String ThisColumn = Target.Column & ":" & Right(UserInput, 2) Приведенный выше код будет автоматически преобразовывать числа, введенные в ячейки столбцов A и B рабочего листа (If ThisColumn < 3), во время в часовом формате (например, 2345 в 23:45).

Чтобы избежать зацикливания в результате изменения содержимого ячейки, запретите обработку событий с помощью строки Application.EnableEvents = False.

Событие Worksheet_SelectionChange(ByVal Target As Range) Событие Worksheet_SelectionChange срабатывает при выделении но вого диапазона ячеек. Target — это объект выделенного диапазона ячеек.

Приведенный ниже код применяется для изменения цвета заливки столбца и строки, на пересечении которых находится выделенная ячейка:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim iColor As Integer On Error Resume Next iColor = Target.Interior.ColorIndex If iColor < 0 Then If iColor = Target.Font.ColorIndex Then iColor = iColor + Cells.FormatConditions.Delete With Range("A" & Target.Row, Target.Address) ' В англоязычной версии Excel:

'.FormatConditions.Add Type:=2, Formula1:="TRUE".FormatConditions.Add Type:=2, Formula1:="ИСТИНА".FormatConditions(1).Interior.ColorIndex = iColor With Range(Target.Offset(1 - Target.Row, 0).Address & ":" & _ Target.Offset(-1, 0).Address) ' В англоязычной версии Excel:

'.FormatConditions.Add Type:=2, Formula1:="TRUE".FormatConditions.Add Type:=2, Formula1:="ИСТИНА".FormatConditions(1).Interior.ColorIndex = iColor End Sub Внимание Указанный код обработки события Worksheet_SelectionChange отменяет лю бое условное форматирование, заданное на рабочем листе. Кроме того, он может очистить буфер обмена, затрудняя тем самым выполнение операций копирования и вставки в пределах данного рабочего листа.

Событие Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Событие Worksheet_FollowHyperlink срабатывает при щелчке на ги перссылке. Target — это объект гиперссылки.

204 Часть I Первые шаги События листа диаграммы События листа диаграммы срабатывают при изменении или активизации диаграммы. Для доступа к событиям встроенных диаграмм используются мо дули классов.

Более подробно модули классов рассматриваются в главе 20, “Создание пользо вательских объектов, типов и коллекций”.

Ниже приведен список событий, существующих на уровне листа диаграм мы Excel:

Встроенные диаграммы Поскольку встроенные диаграммы не имеют отдельного листа, для доступа к их событиям необходимо использовать модуль класса.

1. Добавьте к проекту модуль класса.

2. Переименуйте его в cl_ChartEvents.

3. Разместите в модуле класса следующую строку кода:

Public WithEvents myChartClass As Excel.Chart Это сделает события встроенной диаграммы доступными через модуль класса, как показано на рис. 8.6.

4. Добавьте к проекту стандартный модуль.

5. Разместите в стандартном модуле следующие строки кода:

Dim myClassModule As New cl_ChartEvents Sub InitializeChart() Set myClassModule.myChartClass = _ Worksheets(1).ChartObjects(1).Chart Рис. 8.6. События встроенной диаграммы доступны посредством модуля класса Тем самым вы сделаете объект встроенной диаграммы доступным как обычный объект диаграммы. Процедуру InitializeChart необходи мо выполнить один раз при открытии рабочей книги (воспользуйтесь событием Workbook_Open).

Внимание Первая часть имени события встроенной диаграммы будет совпадать с именем объекта диаграммы, заданном в модуле класса (в данном случае — myChartClass).

Событие Chart_Activate() Событие Chart_Activate срабатывает при активизации листа диаграммы.

Событие Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean) Событие Chart_BeforeDoubleClick срабатывает в результате двойного щелчка на любом элементе диаграммы. ElementID — это объект, представ ляющий элемент диаграммы, на котором был сделан двойной щелчок (например, легенду). Параметры Arg1 и Arg2 зависят от значения параметра ElementID. Чтобы запретить выполнение стандартного действия, предпри нимаемого при обработке двойного щелчка, установите значение параметра Cancel равным True.

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

Private Sub myChartClass_BeforeDoubleClick(ByVal ElementID As _ Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean) Select Case ElementID 206 Часть I Первые шаги myChartClass.HasLegend = False myChartClass.HasLegend = True Событие Chart_BeforeRightClick(Cancel As Boolean) Событие Chart_BeforeRightClick срабатывает в результате щелчка на диаграмме правой кнопкой мыши. Чтобы запретить выполнение стандартного действия, предпринимаемого при обработке щелчка правой кнопкой мыши, установите значение параметра Cancel равным True.

Событие Chart_Calculate() Событие Chart_Calculate срабатывает при изменении исходных дан ных диаграммы.

Событие Chart_Deactivate() Событие Chart_Deactivate срабатывает при деактивизации листа диа граммы.

Событие Chart_DragOver() Событие Chart_DragOver срабатывает при перетаскивании диапазона ячеек на диаграмму.

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

Событие Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long) Событие Chart_MouseDown срабатывает в результате нажатия любой кнопки мыши при условии, что указатель мыши находится над диаграммой. Параметр Button определяет кнопку мыши, которая была нажата; параметр Shift — были ли нажаты при этом кнопки, и ; параметр x — координату указателя мыши по горизонтали; параметр y — координату указателя мыши по вертикали.

Рассмотрим пример. При щелчке на диаграмме левой кнопкой мыши зна чения по оси Y сместятся на 50 единиц вверх, а при щелчке правой кнопкой мыши на 50 единиц вниз. Для запрета выполнения стандартного действия, предпринимаемого при обработке щелчка правой кнопкой мыши (в данном случае отображения контекстного меню) используется параметр Cancel события Chart_BeforeRightClick.

Private Sub myChartClass_MouseDown(ByVal Button As Long, ByVal _ Shift As Long, ByVal x As Long, ByVal y As Long) If Button = 1 Then ActiveChart.Axes(xlValue).MaximumScale = _ ActiveChart.Axes(xlValue).MaximumScale - If Button = 2 Then ActiveChart.Axes(xlValue).MaximumScale = _ ActiveChart.Axes(xlValue).MaximumScale + End Sub Событие Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long) Событие Chart_MouseMove срабатывает при перемещении указателя мыши над диаграммой. Параметр Button определяет кнопку мыши, которая могла быть при этом нажата; параметр Shift — были ли нажаты кнопки, и ; параметр x — координату указателя мыши по гори зонтали; параметр y — координату указателя мыши по вертикали.

Событие Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long) Событие Chart_MouseUp срабатывает в результате отпускания нажатой кнопки мыши при условии, что указатель мыши находится над диаграммой.

Параметр Button определяет кнопку мыши, которая была нажата, а затем от пущена; параметр Shift — были ли нажаты при этом кнопки, и ; параметр x — координату указателя мыши по горизонтали; параметр y — координату указателя мыши по вертикали.

Событие Chart_Resize() Событие Chart_Resize срабатывает при изменении размера диаграммы.

Если диаграмма находится на отдельном листе, событие Chart_Resize сра батывает при условии установки параметра Вид По размеру окна (View Sized with Window) в главном меню Excel.

Событие Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long) Событие Chart_Select срабатывает при выделении элемента диаграм мы. ElementID — это объект, представляющий выделенный элемент диа 208 Часть I Первые шаги граммы (например, легенда). Параметры Arg1 и Arg2 зависят от значения параметра ElementID.

В соответствии со следующим кодом выделение элемента диаграммы при водит к выделению соответствующего этому элементу набора данных:

Private Sub myChartClass_Select(ByVal ElementID As Long, _ ByVal Arg1 As Long, ByVal Arg2 As Long) Sheets("Встроенная диаграмма").Cells.Interior.ColorIndex = xlNone ' Выделить всю последовательность данных.

Sheets("Встроенная диаграмма").Range("A2:A22").Offset(0, _ Arg1).Interior.ColorIndex = ' Выделить значение, соответствующее указанному маркеру.

Sheets("Встроенная диаграмма").Range("A1").Offset(Arg2, _ Arg1).Interior.ColorIndex = Событие Chart_SeriesChange(ByVal SeriesIndex As Long, ByVal PointIndex As Long) Событие Chart_SeriesChange срабатывает при обновлении ряда дан ных на диаграмме. SeriesIndex — это смещение в коллекции Series обновленного ряда данных, PointIndex — смещение в коллекции Points обновленной точки данных.

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

1. Добавьте к проекту модуль класса.

2. Переименуйте его в cl_AppEvents.

3. Разместите в модуле класса следующую строку кода:

Public WithEvents AppEvent As Application Это сделает события приложения доступными через модуль класса, как 4. Добавьте к проекту стандартный модуль.

Рис. 8.7. События приложения доступны посредством модуля класса 5. Разместите в стандартном модуле следующие строки кода:

Dim myAppEvent As New cl_AppEvents Sub InitializeAppEvent() Set myAppEvent.AppEvent = Application Приведенный выше код делает объект AppEvent доступным как объект приложения. Процедуру InitializeAppEvent необходимо выпол нить один раз при открытии рабочей книги (воспользуйтесь событием Workbook_Open).

Внимание Первая часть имени события приложения будет совпадать с именем объекта при ложения, заданном в модуле класса (в данном случае — AppEvent).

Ниже приведен список событий, существующих на уровне приложения Excel:

NewWorkbook;

SheetActivate;

SheetBeforeDoubleClick;

SheetBeforeRightClick;

SheetCalculate;

SheetChange;

SheetDeactivate;

SheetFollowHyperlink;

SheetSelectionChange;

WindowActivate;

WindowDeactivate;

WindowResize;

WorkbookActivate;

210 Часть I Первые шаги Событие AppEvent_NewWorkbook(ByVal Wb As Workbook) Событие AppEvent_NewWorkbook срабатывает при создании новой рабо чей книги. Wb — это объект созданной рабочей книги. Следующий код упоря дочивает на экране окна открытых рабочих книг:

Private Sub AppEvent_NewWorkbook(ByVal Wb As Workbook) Application.Windows.Arrange xlArrangeStyleTiled Событие AppEvent_SheetActivate(ByVal Sh As Object) Событие AppEvent_SheetActivate срабатывает при активизации лю бого рабочего листа или листа диаграммы. Sh — это объект активизирован ного рабочего листа или листа диаграммы.

Событие AppEvent_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Событие AppEvent_SheetBeforeDoubleClick срабатывает в результа те двойного щелчка на любом рабочем листе. Sh — это объект активного рабо чего листа, Target — объект выделенного диапазона ячеек. Чтобы запретить выполнение стандартного действия, предпринимаемого при обработке двой ного щелчка, установите значение параметра Cancel равным True (по умол чанию значение параметра Cancel равно False).

Событие AppEvent_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Событие AppEvent_SheetBeforeRightClick срабатывает в результате щелчка правой кнопкой мыши на любом рабочем листе. Sh — это объект ак тивного рабочего листа, Target — объект, на котором был выполнен щелчок правой кнопкой мыши. Чтобы запретить выполнение стандартного действия, предпринимаемого при обработке щелчка правой кнопкой мыши, установите значение параметра Cancel равным True.

Событие AppEvent_SheetCalculate(ByVal Sh As Object) Событие AppEvent_SheetCalculate срабатывает при пересчете любого рабочего листа или изменении данных любой диаграммы. Sh — это объект ак тивного листа.

Событие AppEvent_SheetChange(ByVal Sh As Object, ByVal Target As Range) Событие AppEvent_SheetChange срабатывает при изменении содержи мого любой ячейки. Sh — это объект рабочего листа, Target — объект изме ненного диапазона ячеек.

Событие AppEvent_SheetDeactivate(ByVal Sh As Object) Событие AppEvent_SheetDeactivate срабатывает при деактивизации любого рабочего листа или листа диаграммы. Sh — это объект деактивизиро ванного листа.

Событие AppEvent_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink) Событие AppEvent_SheetFollowHyperlink срабатывает при щелчке на любой гиперссылке. Sh — это объект активного рабочего листа, Target — объект гиперссылки.

Событие AppEvent_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Событие AppEvent_SheetSelectionChange срабатывает при выделе нии нового диапазона ячеек на любом листе. Sh — это объект активного лис та, Target — объект выделенного диапазона ячеек.

Событие AppEvent_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window) Событие AppEvent_WindowActivate срабатывает при активизации ок на любой рабочей книги. Wb — это объект рабочей книги, отображаемой в ак тивизированном окне, Wn — объект окна.

Событие AppEvent_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window) Событие AppEvent_WindowDeactivate срабатывает при деактивизации окна любой рабочей книги. Wb — это объект рабочей книги, отображаемой в деактивизированном окне, Wn — объект окна.

212 Часть I Первые шаги Событие AppEvent_WindowResize(ByVal Wb As Workbook, ByVal Wn As Window) Событие AppEvent_WindowResize срабатывает при изменении размеров окна активной рабочей книги. Wb — это объект активной рабочей книги, Wn — объект окна.

Запрет изменения размера окна рабочей книги (EnableResize = False) при водит к удалению кнопок окна Свернуть (Minimize) и Развернуть (Maximize). Что бы вернуть кнопки на место, выполните в окне Immediate (Быстрое выполнение) строку ActiveWindow.EnableResize = True.

Событие AppEvent_WorkbookActivate(ByVal Wb As Workbook) Событие AppEvent_WorkbookActivate срабатывает при активизации любой рабочей книги. Wb — это объект активизированной рабочей книги.

Следующий код указывает на необходимость развернуть окно рабочей книги при ее активизации:

Private Sub AppEvent_WorkbookActivate(ByVal Wb As Workbook) Wb.WindowState = xlMaximized Событие AppEvent_WorkbookAddinInstall(ByVal Wb As Workbook) Событие AppEvent_WorkbookAddinInstall срабатывает при установке рабочей книги в качестве надстройки (чтобы установить надстройку, выбери те команду меню Excel Сервис Надстройки (Tools Add Ins)). Обратите внимание, что событие AppEvent_WorkbookAddinInstall не срабатывает при двойном щелчке на значке файла надстройки (файл с расширением.xla). Wb — это объект рабочей книги, установленной в качестве надстройки.

Событие AppEvent_WorkbookAddinUninstall(ByVal Wb As Workbook) Событие AppEvent_WorkbookAddinUninstall срабатывает при удале нии рабочей книги, используемой в качестве надстройки. Обратите внимание, что надстройка не закрывается автоматически. Wb это объект удаляемой рабочей книги, использовавшейся в качестве надстройки.

Событие AppEvent_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean) Событие AppEvent_WorkbookBeforeClose срабатывает при закрытии рабочей книги. Wb — это объект рабочей книги. Чтобы запретить закрытие ра бочей книги, установите значение параметра Cancel равным True.

Событие AppEvent_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean) Событие AppEvent_WorkbookBeforePrint срабатывает при попытке печати рабочей книги (способ инициирования процесса печати с помощью команды меню, кнопки панели инструментов, комбинации клавиш или про граммного кода не играет роли). Wb — это объект рабочей книги. Чтобы за претить печать, установите значение параметра Cancel равным True.

Следующий код помещает имя пользователя в нижний колонтитул печа таемых страниц:

Private Sub AppEvent_WorkbookBeforePrint(ByVal Wb As Workbook, _ Cancel As Boolean) Wb.ActiveSheet.PageSetup.LeftFooter = Application.UserName End Sub Событие AppEvent_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean) Событие AppEvent_WorkbookBeforeSave срабатывает при попытке со хранения рабочей книги. Wb — это объект рабочей книги. Чтобы отобразить диалоговое окно Сохранение документа (Save As), установите значение пара метра SaveAsUI равным True. Чтобы запретить сохранение рабочей книги, установите значение параметра Cancel равным True.

Событие AppEvent_WorkbookDeactivate(ByVal Wb As Workbook) Событие AppEvent_WorkbookDeactivate срабатывает при деактивиза ции любой рабочей книги. Wb — это объект деактивизированной рабочей книги.

Событие AppEvent_WorkbookNewSheet(ByVal Wb As Workbook, By Val Sh As Object) Событие AppEvent_WorkbookNewSheet срабатывает при добавлении в активную рабочую книгу нового листа. Wb — это объект активной рабочей книги, Sh — объект нового рабочего листа или листа диаграммы.

214 Часть I Первые шаги Событие AppEvent_WorkbookOpen(ByVal Wb As Workbook) Событие AppEvent_WorkbookOpen срабатывает при открытии рабочей книги. Wb — это объект только что открытой рабочей книги.

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

Глава Способы с пользователем с пользователем

В отличие от окна ввода и окна со общения, формы Excel выводят взаи модействие с пользователем на совер шенно иной качественный уровень.

В этой главе будут рассмотрены Использование вкладок для базовые интерфейсы взаимодействия объединения форм

с пользователем, такие как окно вво Следующий шаг

да и окно сообщения, а также основы создания пользовательских форм.

Более подробно пользовательские формы рассматриваются в главе 21, ‘‘Пользовательские формы про фессиональный подход’’.

Окно ввода Окно ввода данных представляет собой один из основных интерфейсов взаимодействия с пользователем. На страиваемым является текст сообще ния, заголовок окна, значение по умолчанию, положение окна на экра не, а также файл справки. Окно ввода имеет две кнопки (Cancel). Функция InputBox возвра щает значение типа String. В резуль тате выполнения приведенного ниже кода пользователю будет предложено ввести количество месяцев, для кото рого необходимо подсчитать некото рые статистические данные:

216 Часть I Первые шаги AveMonths = InputBox(Prompt:="Введите количество месяцев", _ Title:="Введите количество месяцев", Default:="3") Соответствующее окно ввода показано на рис. 9.1.

Окно сообщения Не менее важным интерфейсом взаимодействия с пользователем является окно сообщения. В отличие от окна ввода, окно сообщения может иметь лю бую комбинацию из кнопок Да (Yes), Нет (No), OK и Отмена (Cancel). На страиваемым также является текст сообщения, заголовок окна и файл справ ки. В результате выполнения приведенного ниже кода пользователю будет предложено закрыть рабочую книгу и сохранить внесенные в нее изменения:

MyMsg = "Сохранить изменения?" MyTitle = "Закрытие рабочей книги" Response = MsgBox(MyMsg, vbExclamation + vbYesNoCancel, MyTitle) Select Case Response ActiveWorkbook.Close SaveChanges:=False ActiveWorkbook.Close SaveChanges:=True End Select Выражение Select Case используется для принятия решения относитель но хода выполнения программного кода в зависимости от сделанного пользова телем выбора. Соответствующее окно сообщения показано на рис. 9.2.

Создание пользовательской формы По сравнению с окном ввода и окном сообщения пользовательская форма имеет гораздо более широкие возможности. К примеру, ее можно применять для ввода пользователем личной информации, как показано на рис. 9.3.

Чтобы добавить к проекту пользовательскую форму, выберите команду ме ню редактора Visual Basic Insert UserForm (Вставить Пользовательская форма). В результате этого к проекту будет добавлен модуль новой формы, на месте области ввода программного кода будет отображена пустая форма и на экране появится окно панели инструментов, как показано на рис. 9.4.

Рис. 9.3. Пример пользо Рис. 9.4. Пустая пользовательская форма вательской формы и панель инструментов Изменить размер формы можно с помощью маркеров, расположенных по бокам и в углах окна формы. Чтобы добавить на форму элемент управления, щелкните на соответствующей ему кнопке на панели инструментов и нари суйте его на форме. Помещенные на форму элементы управления можно пе ремещать, а также изменять их размер.

На заметку По умолчанию на панели инструментов находятся только наиболее часто используе мые элементы управления. Чтобы получить доступ к остальным элементам управления, щелкните на панели инструментов правой кнопкой мыши и выберите команду контек стного меню Additional Controls (Дополнительные элементы управления).

После добавления элемента управления на форму его свойства становятся дос тупны посредством окна свойств редактора Visual Basic. Свойства элемента управ ления можно настроить как вручную, так и с помощью программного кода.

Совет Пользовательской форме, а также элементам управления рекомендуется назна чать описательные имена. Примером стандартного имени пользовательской формы является имя UserForm1. Измените его на что нибудь более значащее, например, frm_AddEmp.

218 Часть I Первые шаги Вызов и скрытие пользовательской формы Пользовательскую форму можно вызвать из любого модуля. Следующий код выводит на экран форму frm_AddEmp:

frm_AddEmp.Show Для вызова формы может применяться также метод Load, однако в этом случае форма будет лишь загружена, но не выведена на экран.

Для скрытия формы применяется метод Hide. Форма будет по прежнему активна, однако не видна на экране. Все элементы управления формы доступ ны посредством программного кода.

Метод Unload выгружает форму из памяти и убирает ее с экрана. В резуль тате выполнения приведенного ниже кода форма Me становится недоступной как посредством пользовательского интерфейса, так и посредством про граммного кода:

Unload Me Программирование пользовательской формы Код обработки событий элементов управления формы помещается в мо дуль этой формы. В отличие от других модулей, двойной щелчок на модуле формы приводит к ее открытию в режиме конструктора. Чтобы просмотреть код формы, щелкните правой кнопкой мыши на названии модуля формы или на самой форме в режиме конструктора и выберите команду контекстного ме ню View Code (Просмотр кода).

Чтобы ввести код обработки стандартного события элемента управления формы, выделите требуемый элемент управления и выберите команду меню View Code (Вид Код). Редактор Visual Basic автоматически сгенерирует за головки процедуры обработки стандартного события. Чтобы отобразить спи сок других событий данного элемента управления, выберите соответствую щий ему объект из раскрывающегося списка Object (Объект) и откройте рас крывающийся список Properties (Свойства), как показано на рис. 9.5.

Рис. 9.5. Раскрывающийся список Properties содержит все события, доступные для элемента управления формы, выбранного из списка Object Все элементы управления являются объектами с соответствующими свой ствами и методами. Обычно весь код, относящийся к элементу управления, размещается в модуле формы. При обращении к элементу управления извне этого модуля имя соответствующего объекта необходимо предварить именем объекта формы.

Private Sub btn_EmpCancel_Click() Unload Me End Sub Рассмотрим некоторые из составляющих элементов приведенного выше кода:

btn_EmpCancel — имя элемента управления;

Click — событие элемента управления;

Unload Me — код обработки события элемента управления (в данном случае форма будет убрана с экрана и выгружена из памяти).

Практикум Добавление элемента управления к существующей форме Добавление элемента управления к существующей форме представляет собой весьма непростую задачу. Щелкнув на новом элементе управления правой кноп кой мыши и выбрав команду контекстного меню View Code (Просмотр кода), вы обнаружите, что редактор Visual Basic даже не подозревает о существовании та кого элемента. Имени объекта элемента управления нет и в раскрывающемся спи ске Object (Объект).

Чтобы добавить элемент управления к существующей форме, выполните следую щие действия.

1. Добавьте к существующей форме все необходимые элементы управления.

2. Щелкните на названии модуля формы в диспетчере проектов правой кнопкой мыши и выберите команду контекстного меню Export File (Экспорт в файл).

В открывшемся диалоговом окне Export File (Экспорт в файл) щелкните на кнопке Сохранить (Save), чтобы сохранить файл формы в стандартном разме 3. Снова щелкните на названии модуля формы в диспетчере проектов правой кноп кой мыши и выберите команду контекстного меню Remove (Удалить). В ответ на предложение экспортировать файл формы щелкните на кнопке Нет (No).

4. Щелкните правой кнопкой мыши на незанятом участке окна диспетчера про ектов и выберите из контекстного меню команду Import File (Импорт из файла).

Выберите созданный выше файл формы и щелкните на кнопке Открыть (Open).

В результате выполнения приведенной выше последовательности действий ре дактор Visual Basic распознает все элементы управления, добавленные к сущест вующей форме.

220 Часть I Первые шаги Основные элементы управления формы Форма, показанная на рис. 9.6, состоит из надписей, полей ввода и командных кнопок.

После ввода требуемой информации пользователь щелкает на кнопке OK, что приводит к заполнению соответствующих ячеек на рабочем листе, как по казано на рис. 9.7.

Рис. 9.6. Простая форма, пред Рис. 9.7. Информация, введенная в форму, помещает назначенная для ввода пользо ся на рабочий лист Excel вательских данных Private Sub btn_EmpOK_Click() LastRow = Worksheets("Лист2").Range("A65536").End(xlUp).Row + Cells(LastRow, 1).Value = tb_EmpName.Value Cells(LastRow, 2).Value = tb_EmpPosition.Value Cells(LastRow, 3).Value = tb_EmpHireDate.Value Примечательно, что одна и та же форма может использоваться как для ввода, так и для извлечения информации. Чтобы приспособить показанную на рис. 9. форму для извлечения сведений о должности служащего и дате приема на рабо ту, измените код обработки события btn_EmpOK_Click, как показано ниже:

Private Sub btn_EmpOK_Click() With Range("EmpList") Set EmpFound =.Find(tb_EmpName.Value) Использование списков и комбинированных списков При вводе имени служащего можно допустить ошибку. Чтобы этого не произошло, предложите пользователю выбрать имя служащего из списка или комбинированного списка.

Список позволяет выбрать одно или несколько значений.

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

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

Private Sub btn_EmpOK_Click() Dim EmpFound As Range With Range("EmpList") Set EmpFound =.Find(lb_EmpName.Value) If EmpFound Is Nothing Then MsgBox("Служащий не найден!") With Range(EmpFound.Address) End Sub Выбор нескольких значений из списка Объект списка имеет свойство MultiSelect, позволяющее выбирать из списка несколько значений одновременно (рис. 9.9).

Рис. 9.8. Список позволяет избе Рис. 9.9. Список поддерживает 222 Часть I Первые шаги Ниже перечислены возможные значения этого свойства:

fmMultiSelectSingle — значение по умолчанию, разрешающее вы бор из списка только одного элемента;

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

Следующий код демонстрирует способ обращения к элементам списка, поддерживающего множественный выбор:

Private Sub btn_EmpOK_Click() Dim LastRow As Long, i As Integer LastRow = Worksheets("Лист2").Range("A65536").End(xlUp).Row + Cells(LastRow, 1).Value = tb_EmpName.Value 'Какие элементы из списка выбраны?

For i = 0 To lb_EmpPosition.ListCount - 'Если элемент выбран, добавить его на рабочий лист.

If lb_EmpPosition.Selected(i) = True Then Cells(LastRow, 2).Value = Cells(LastRow, 2).Value & _ lb_EmpPosition.List(i) & "," Cells(LastRow, 3).Value = tb_HireDate.Value Поскольку первый элемент списка имеет порядковый номер 0, при опре делении верхней границы значения переменной счетчика от значения свой ства ListCount необходимо отнять 1:

For i = 0 To lb_EmpPosition.ListCount - Использование переключателей Переключатели должны быть отделены от остальных элемен тов пользовательской формы с помощью панели, как показано Все переключатели одной группы должны иметь одинаковое значение свойства GroupName, чтобы гарантировать возможность выбора только од ного переключателя в группе.

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

Private Sub Lbl_Bldg1_Click() Рис. 9.10. Для группирования пере Рис. 9.11. Некоторые пользователи ключателей используется панель предпочитают выбирать переклю Использование изображений Изображения позволяют придать списку более наглядный вид, как показано на рис. 9.12.

Согласно приведенному ниже коду, выбор имени служащего из списка бу дет сопровождаться выводом фотографии этого служащего:

Private Sub lb_EmpName_Change() Dim EmpFound As Range With Range("EmpList") Set EmpFound =.Find(lb_EmpName.Value) If EmpFound Is Nothing Then MsgBox("Служащий не найден!") 224 Часть I Первые шаги (ThisWorkbook.Path & Application.PathSeparator & EmpFound & ".jpg") Использование счетчиков Как показано на рис. 9.12, поле Дата приема на работу позволяет вводить данные в произвольном формате, например, 1/1/1 или 1 января 2001 года.

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

Счетчик ограничивает пользователя вводом численного значения и содержит кнопки увеличения и уменьшения последнего.

Рассмотрим создание счетчика для ввода месяца. Поместите счетчик на форму. Установите свойство объекта счетчика Min равным 1 (январь), свойство Max 12 (декабрь), свойство Value 1 (начальное значение).

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

Private Sub SpBtn_Month_Change() tb_Month.Value = SpBtn_Month.Value Добавьте на форму еще два счетчика и два поля ввода, как показано на рис. 9.13.

Private Sub btn_EmpOK_Click() Dim LastRow As Long, i As Integer LastRow = Worksheets("Лист2").Range("A65536").End(xlUp).Row + Cells(LastRow, 1).Value = tb_EmpName.Value For i = 0 To lb_EmpPosition.ListCount - If lb_EmpPosition.Selected(i) = True Then Cells(LastRow, 2).Value = Cells(LastRow, 2).Value & _ lb_EmpPosition.List(i) & "," 'Создание даты путем конкатенации значений полей ввода.

Cells(LastRow, 3).Value = tb_Month.Value & "/" & _ tb_Day.Value & "/" & tb_Year.Value Использование вкладок для объединения форм Вкладки позволяют объединить воедино несколько различных форм.

На рис. 9.14 показан пример объединения форм для ввода служебной и личной информации о сотруднике.

Рис. 9.13. Чтобы унифициро Рис. 9.14. Используйте вкладки для объединения несколь вать ввод даты приема слу ких различных форм жащего на работу, восполь зуйтесь счетчиками Совет Старайтесь планировать формы с вкладками заранее. Чтобы создать форму с вкладками на основе уже существующих форм, необходимо создать новую фор му, добавить в нее требуемое число вкладок и скопировать на них элементы управления с имеющихся форм.

Проверка ввода обязательных данных Одним из преимуществ электронной формы является возможность про верки ввода обязательных данных:

If tb_EmpName.Value = "" Then frm_AddEmp.Hide MsgBox("Пожалуйста, введите имя служащего") frm_AddEmp.Show End If Закрытие формы Как и большинство окон Windows, окно пользовательской формы имеет кнопку Закрыть (Close) (кнопка с изображением крестика), расположенную в его правом верхнем углу. В зависимости от предназначения формы ее закры тие путем щелчка на этой кнопке может оказаться весьма нежелательным.

Определить способ закрытия окна формы и, при необходимости, соответст вующим образом среагировать на него поможет событие QueryClose:

226 Часть I Первые шаги Private Sub UserForm_QueryClose(Cancel As Integer, _ CloseMode As Integer) If CloseMode = vbFormControlMenu Then MsgBox "Для закрытия формы щелкните на кнопке OK _ или Отмена", vbCritical Согласно приведенному выше коду попытка закрытия формы с помощью недозволенного способа приведет к выводу окна сообщения, показанного на рис. 9.15.

Ниже перечислены оставшиеся способы закрытия окна формы:

vbFormCode — форма была закрыта с помощью метода Unload;

vbAppWindows — форма была закрыта в результате завершения рабо vbTaskManager — форма была закрыта с помощью диспетчера задач.

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

Часть II Автоматизация Excel 10. Диаграммы

11. Анализ данных с помощью расширенного фильтра..... 12. Сводные таблицы

13. Excel всемогущий

14. Взаимодействие с Internet

15. Поддержка XML в профессиональном выпуске Excel 2003

16. Автоматизация Word

Глава Говорят, лучше один раз увидеть, чем сто раз услышать. Наглядное пред ставление данных имеет неоспоримое преимущество перед сухими цифрами.

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

В этой главе рассматривается ис пользование VBA при выполнении следующих базовых задач: переменных для упрощения создание встроенных диаграмм и диаграмм, расположенных на отдельном листе;

выбор типа диаграммы; круговых диаграмм

изменение типа диаграммы; Интерактивные диаграммы..... форматирование, перемещение и удаление диаграммы и ее эле создание нестандартных диа Создание нестандартных Одной из особенностей програм мирования диаграмм в Excel является наличие двух объектных моделей, каждая из которых соответствует раз личному типу диаграмм.