6) Создадим теперь код процедуры, обрабатывающей событие «нажатие кнопки». В результате обработки этого события должен активизироваться рабочий лист Лист3.
Для этого щелкните два раза на созданной кнопке (напоминаем, что в этом случае кнопка Режим конструктора в разделе Элементы управления на вкладке Разработчик является нажатой): откроется редактор VBA с активизированным модулем рабочего листа (в данном случае Лист1), в который автоматически добавились первая и последняя процедуры обработки события «нажатие кнопки» (Click) , см. листинг 2 а):
Листинг 2 а). Первая и последняя инструкции обработки события «нажатие кнопки». Модуль рабочего листа Лист2.
Private Sub CommandButton1_Click()
End Sub
Далее откройте файл из ОС Windows, в котором вы создавали макрос АктивизацияЛиста; проверьте, чтобы в окне Project – VBAProject отобразился необходимый модуль.
Скопируйте через буфер обмена (см. рис. 11) инструкцию из макроса:
Sheets("Лист2").Select
в процедуру обработки события «нажатие кнопки». Конечно, можно было бы набрать эту инструкцию и вручную, однако этот довольно медленно и чревато ошибками при наборе кода.
Исправьте номер листа в коде с «2» на «3»:
Sheets("Лист3").Select
Листинг 2 б). Процедура обработки события «нажатие кнопки», при котором будет активизирован рабочий Лист3. Модуль рабочего листа Лист2
Private Sub CommandButton1_Click()
Sheets("Лист3").Select
End Sub
Рис. 11. Окно редактора VBA и открытые окна модулей.
7) Вернитесь на рабочий лист Лист2. Созданная кнопка будет обрабатывать событие (нажатие на нее) только после выхода из режима конструктора. Поэтому отключите режим конструктора, щелкнув на кнопке Режим конструктора, в разделе Элементы управления на вкладке Разработчик ленты.
8) Проверьте работу кнопки. Нажмите её. Если вы всё правильно, то её нажатие приведёт к активизации рабочего листа Лист3.
9) В качестве упражнения попробуйте самостоятельно:
а) Создать макрос, который добавляет новый рабочий лист в книгу Excel и устанавливает указатель ячейки на рабочем листе Лист1;
б) Далее попробуйте связать созданную процедуру с кнопкой, также расположенной на рабочем листе Лист1.
Пример 3.
Построение шаблона таблицы.
Рассмотрим ещё пример, автоматизирующий деятельность некоторой коммерческой фирмы. Пусть вы являетесь менеджером некоторой фирмы и вам необходимо каждый месяц составлять таблицу учета расходов. Шаблон таблицы приведен ниже на рис 12:
Рис. 12. Шаблон таблицы расходов.
Создадим, а потом отредактируем макрос, который позволит вам быстро получать требуемый шаблон для простой неотформатированной таблицы учета расходов.
Для этого:
1) Запустите макрорекордер, для чего выполните команду: Разработчик → Код → Запись макроса.
2) В открывшемся окне Запись макроса введите в поле Имя макроса - Построение_шаблона_таблицы_Простой, а в поле Сохранить выберите из списка Эта книга.
3) Перейдите к ячейке В1 и введите в нее слово Расходы.
4) Заполните ячейки А1…А7 так, как показано на рис. 12.
5) Далее укажите ячейку В7 и введите в неё формулу: = СУММ(В2:В6).
6) Выделите столбец А и перейдите на вкладку Главная. В разделе Ячейки щелкните на кнопке с выпадающим списком Формат и выберите команду Автоподбор ширины столбца.
7) Проделайте аналогичные действия и для столбца В: выделите столбец В, выполните команду: Главная → Ячейки → Формат → Автоподбор ширины столбца.
8) Установите указатель ячейки в ячейку В2.
9) Завершите работу макрорекордера, для чего на вкладке Разработчик в разделе Код нажмите на кнопку Остановить запись.
В результате на листе стандартного модуля будет записан следующий макрос (см. листинг 3 и рис. 13):
Листинг 3. Макрос по составлению шаблона отчета
Sub Построение_шаблона_таблицы_Простой()
'
' Построение_шаблона_таблицы_Простой Макрос
'
'
Range("B1").Select
ActiveCell.FormulaR1C1 = "Расходы"
Range("A1").Select
ActiveCell.FormulaR1C1 = "Статья расходов"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Телефон"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Аренда"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Амортизация"
Range("A5").Select
ActiveCell.FormulaR1C1 = "страховка"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Заработная плата"
Range("A7").Select
ActiveCell.FormulaR1C1 = "Итого"
Range("B7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"
Range("A1:A7").Select
Selection.Columns.AutoFit
Range("B1:B7").Select
Selection.Columns.AutoFit
Range("B2").Select
End Sub
Рис. 13. Вид окна редактора VBA c записанным с помощью макрорекордера макросом для построения шаблона таблицы расходов.
Итак, макрос записан. Рассмотрим, как можно упростить его код для создания шаблона отчета.
Первые 15 строк кода макроса осуществляют ввод текстовой информации в выбранные ячейки рабочего листа. Причем, начиная со 2-ой строки кода для ввода используется парная инструкция. Поэтому разумно вместо
Range("B1").Select
ActiveCell.FormulaR1C1 = "Расходы"
записать инструкцию
Range("B1").Value= "Расходы"
Аналогично можно записать и следующую инструкцию макроса, т.е.:
Range("A1").Value= "Статья расходов"
Следующие две инструкции реализуют ввод формулы = СУММ(В2:В6) в ячейку В7. В макросе эта формула записана в относительном формате R1C1 (этот стиль ссылок используется и в программах на VBA):
Range("B7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"
Разумно эти две инструкции заменить одной, используя привычный стиль ссылок А1 и формулу локальной версии:
Range("B7").FormulaLocal = "=CУММ(B2:B6)"
Предпоследние четыре инструкции макроса реализуют автоматический подбор ширины столбцов А и В так, чтобы в них помещались все введенные строки текста:
Range("A1:A7").Select
Selection.Columns.AutoFit
Range("B1:B7").Select
Selection.Columns.AutoFit
Можно сократить данный код, заменив эти четыре инструкции двумя:
Columns(“A:A”).AutoFit
Columns(“B:B”).AutoFit
Последняя инструкция макроса устанавливает указатель в ячейку В2. Оставим её без изменения.
Отметим, что для пользователя было бы удобно, если бы имя рабочего листа совпадало с названием месяца, для которого составляется отчет. Поэтому логично было бы добавить в процедуру инструкции, которые выводят на экран диалоговое окно, предлагающее изменить название листа. Если пользователь соглашается переименовать лист, то он сам вводит в поле ввода появившегося диалогового окна новое название рабочего листа и нажимает ОК, а процедура уже сама переименует лист.
Итак, код окончательного варианта процедуры составления шаблона состоит всего из нескольких инструкций, и значительно более функционален, чем макрос на основе которого он был составлен (см. листинг 3*):