Листинг 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*):
Листинг 3*. Макрос по составлению шаблона отчета (отредактированный)
Sub ChartBuilder()
' отредактированная процедура для макроса Построение_шаблона_таблицы_простой
Dim sheetName As String
sheetName = InputBox("Введите имя листа")
If sheetName <> Empty Then
ActiveSheet.Name = sheetName
End If
Range("A1").Value = "Статья расходов"
Range("B1").Value = "Расходы"
Range("A2").Value = "Телефон"
Range("A3").Value = "Аренда"
Range("A4").Value = "Амортизация"
Range("A5").Value = "Страховка"
Range("A6").Value = "Заработная плата"
Range("A7").Value = "Итого"
Range("B7").FormulaLocal = "=СУММ(B2:B6)"
Columns("A:A").AutoFit
Columns("B:B").AutoFit
Range("B2").Select
End Sub
В листинге 4 в строках с 3-6 содержатся инструкции, которые приводят к отображению на экране диалогового окна, предлагающего изменить его название листа, если пользователь желает этого:
Dim sheetName As String
sheetName = InputBox("Введите имя листа")
If sheetName <> Empty Then
ActiveSheet.Name = sheetName
End If
Если пользователь не желает изменять название листа, то в предложенном диалоговом окне он нажимает кнопку Cancel, поле ввода остается пустым и лист сохраняет прежнее название.
10) Создайте самостоятельно с помощью макрорекордера макрос, который очищает рабочий лист от имеющихся данных и форматирования, полученного в результате выполнения макроса форматирования.
Пример 4.
Управление диаграммой.
Предположим вам, создавая годовой отчет, предстоит построить диаграммы дохода некой фирмы в течение года. Например, только за январь, с января по февраль, с января по март, и т.д., т.е. всего 12 диаграмм. Конечно, можно построить все 12 диаграмм, а можно обойтись одной. В этом случае нужно применить инструмент, позволяющий вводить заданный временной интервал, а диаграмма должна автоматически перестраиваться в соответствии с ним. Для этого необходимо использовать элементы управления: либо Список, либо Поле со списком.
Для этого:
1) В ячейки А1 и В1 введите текст для заголовков столбцов: Месяц и Доход.
2) В диапазон ячеек А2:А13 введите названия месяцев, используя автозаполнение.
3) В диапазон В2:В13 введите доходы фирмы.
4) По эти двум диапазонам (А2:А13 и В2:В13) постройте диаграмму. Для этого выполните команду: Вставка → Диаграммы → Гистограммы → выберите понравившийся вам тип (см. рис. 14).
5) На рабочем листе расположите элемент Поле со списком 
, который позволит произвести выбор временного интервала. Для этого выполните команду: Разработчик → Элементы управлении → Вставить → Элементы ActiveX → элемент управления Поле со списком. Далее перейдите на рабочий лист и нарисуйте элемент управления необходимого размера.
6) Установите в окне Properties для элемента управления Поле со списком значение свойства ListFillRange равным A2:A13. Это свойство заполняет список на основе данных из указанного диапазона (см. рис. 15).
7) Щелкните дважды по созданному элементу управления Поле со списком и перейдите в окно модуля рабочего листа.
8) Наберите в модуле рабочего листа код на языке VBA из листинга 4.
9) Перейдите на лист рабочего листа и проверьте с использованием элемента управления Поле со списком возможность быстрого построения диаграммы о доходах фирмы. Для того, чтобы построить диаграмму, например, за январь,
в поле элемента со списком выберите Январь, за январь и февраль: Февраль и т.д.
10) Результат быстрого построения диаграммы о доходах фирмы (например, за январь, февраль и март) см. рис. 16.
Дата добавления: 2018-04-05; просмотров: 382; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!