Элементы управления в MS Excel
Лабораторная работа №10
АВТОМАТИЗАЦИЯ РАБОТЫ С КНИГОЙ В MS EXCEL
Цель работы: работа с макросами в MS Excel: создание, удаление, выполнение изменение, копирование макросов; использование элементов управления для автоматизации работы с книгой.
ТЕОРЕТИЧЕСКИЕ ПОЛОЖЕНИЯ
Макросы
Если требуется периодическое выполнение задачи в Microsoft Excel, можно автоматизировать задачу с помощью макроса. Макрос – это последовательность команд и функций, хранящаяся в модуле Visual Basic. С точки зрения программирования – это подпрограмма. Ее можно выполнять всякий раз, когда необходимо выполнить данную задачу.
Модуль – совокупность описаний, инструкций и процедур, сохраненная под общим именем. Существуют модули двух типов: стандартный модуль и модуль класса.
1.1.1 Создание макроса. Макросы можно создавать с помощью средства для записи макросов или введя его код на языке Visual Basic для приложений в редакторе Visual Basic. Можно использовать и оба метода сразу: записать часть шагов, а затем расширить макрос с помощью программного кода.
Для записи макроса необходимо:
1) Установите Средний или Низкий уровень безопасности:
- выберите команду меню Сервис Þ Параметры;
- откройте вкладку Безопасность;
- в группе Безопасность макросов нажмите кнопку Безопасность макросов;
- откройте вкладку Уровень безопасности, а затем выберите нужный уровень безопасности.
2) Выберите команду меню Сервис Þ Макрос Þ Начать запись (рис. 1.1).
3) В поле Имя макроса введите имя макроса (см. рис. 1.1):
- первым знаком имени макроса должна быть буква. Остальные знаки могут быть буквами, цифрами или знаками подчеркивания. В имени макроса не допускаются пробелы; в качестве разделителей слов следует использовать знаки подчеркивания;
- не используйте имя макроса, являющееся ссылкой на ячейку, т.к. будет выдано соответствующее сообщение об ошибке.
4) Если этот макрос потребуется запускать нажатием сочетания клавиш на клавиатуре, введите букву в поле Сочетание клавиш (см. рис. 1.1). Допускается использование сочетаний Ctrl+буква (для строчных букв) или Ctrl+Shift+буква (для прописных букв), где буква – любая буквенная клавиша на клавиатуре. Нельзя использовать сочетания клавиш с цифрами и специальными знаками, такими как @ или #.
Примечание. Выбранное сочетание клавиш заменяет все совпадающие стандартные сочетания клавиш Microsoft Excel на то время, пока открыта книга, содержащая данный макрос.
Рисунок 1.1 – Диалог Запись макроса
5) В поле Сохранить в выберите книгу, в которой требуется сохранить макрос. Если этот макрос требуется всегда при работе в Microsoft Excel, выберите вариант Личная книга макросов (см. рис. 1.1).
6) Если необходимо добавить описание макроса, введите его в поле Описание (см. рис. 1.1).
7) Нажмите кнопку OK.
8) Если макрос требуется выполнять относительно позиции активной ячейки, запишите его, используя относительные ссылки на ячейки. На панели инструментов Остановить запись нажмите кнопку Относительная ссылка, чтобы она осталась нажатой. Запись макроса будет продолжена с использованием относительных ссылок, пока не будет закрыт Microsoft Excel или не будет еще раз нажата кнопка Относительная ссылка, после чего она останется не нажатой.
9) Выполните макрокоманды, которые нужно записать.
10) На панели инструментов Остановить запись нажмите кнопку Остановить запись.
Чтобы запрограммировать макрос с помощью Visual Basic, необходимо:
1) Выберите команду меню Сервис Þ Макрос Þ Редактор Visual Basic (рис. 1.2).
2) В меню Insert выберите команду Module.
3) Введите или скопируйте программу в окно программы модуля.
4) Чтобы запустить данный макрос из окна модуля, нажмите кнопку F5.
5) Когда макрос будет создан, выберите команду меню File Þ Close and Return to Microsoft Excel.
1.1.2 Удаление макроса.Для удаления макроса:
1) Откройте книгу, содержащую макрос, который требуется удалить.
2) В выберите команду меню Сервис Þ Макрос Þ Макросы.
3) В списке Находится в выберите Эта книга.
4) В списке Имя макроса выберите имя макроса, который нужно удалить.
5) Нажмите кнопку Удалить.
Рисунок 1.2 – Окно редактора Visual Basic
1.1.3 Выполнение макроса. Для выполнения макроса:
1) Установите Средний или Низкий уровень безопасности.
2) Откройте книгу, содержащую нужный макрос.
3) В меню Сервис выберите пункт Макрос, а затем выполните команду Макросы.
4) В поле Имя макроса введите имя того макроса, который требуется выполнить.
5) Выполните одно из следующих действий.
- Запустите макрос в книге Microsoft Excel: нажмите кнопку Выполнить; чтобы прервать выполнение, нажмите кнопку ESC.
- Выполните макрос из модуля Microsoft Visual Basic: нажмите кнопку Изменить; нажмите кнопку Run Sub/UserForm.
Совет: чтобы выполнить другой макрос, находясь в редакторе Visual Basic, выберите команду Macros в меню Tools. В поле Macro name введите имя того макроса, который нужно выполнить, а затем нажмите кнопку Run.
1.1.4 Изменение макроса. Для изменения макроса необходимо знакомство с редактором Visual Basic, который используется для написания и изменения макросов Microsoft Excel.
1) Установите Средний или Низкий уровень безопасности.
2) Выполните команду меню Сервис Þ Макрос Þ Макросы.
3) Введите имя макроса в поле Имя макроса.
4) Нажмите кнопку Изменить.
1.1.5 Копирование модуля макроса в другую книгу.Для этого:
1) Установите Средний уровень безопасности.
2) Откройте книгу, содержащую модуль, который требуется скопировать, и книгу, в которую его требуется скопировать.
3) Выберите команду меню Сервис Þ Макрос Þ Редактор Visual Basic.
4) Выберите команду меню Вид Þ Окно проекта.
5) Перетащите требуемый модуль в конечную книгу.
Элементы управления в MS Excel
Элементы управления представляют собой графические объекты, помещаемые в форму для отображения или ввода данных, выполнения действий или облегчения чтения формы. Данные объекты включают надписи, поля со списками, переключатели и флажки, кнопки и многое другое. Элементы управления позволяют пользователю запускать макросы или веб-сценарии путем нажатия, установки или выбора соответствующих элементов управления.
Сценарий – тип компьютерных программ, используемый для выполнения на веб-страницах таких задач, как увеличение счетчика числа посещений при появлении каждого нового посетителя. Существует несколько языков для написания веб-сценариев. Сценарии выполняются без предварительной компиляции.
В Microsoft Excel существует два вида элементов управления. Элементы управления ActiveX подходят в большинстве случаев, и работают с макросами Visual Basic для приложений (VBA) и веб-сценариями.
Элементы управления ActiveX Элемент ActiveX. Элемент управления (такой как флажок или кнопка), служащий для выбора параметров либо для запуска макроса или сценария, автоматизирующего выполнение задачи. Макросы для таких элементов управления можно создавать в редакторе Visual Basic для приложений, а сценарии в редакторе сценариев.
1.2.1 Виды элементов управления. Чтобы определить, является ли элемент элементом управления ActiveX или элементом панели инструментов Формы, щелкните его правой кнопкой мыши. Если контекстное меню не появляется или содержит команду Свойства, значит это элемент ActiveX. Если контекстное меню содержит команду Назначить макрос, значит это элемент управления с панели инструментов Формы.
Чтобы задать свойства для имеющегося элемента управления, щелкните его правой кнопкой мыши, выберите команду Формат объекта, а затем откройте вкладку Элемент управления. Кнопки и надписи не имеют свойств.
В табл. 1.1 и табл. 1.2 приведены виды элементов управления ActiveX и панели инструментов Формы.
Таблица 1.1 – Элементы ActiveX
| Элемент | Назначение |
| Флажок | Включает или выключает действие определенного параметра. На листе может быть установлено несколько флажков одновременно. |
| Поле | Поле, в которое можно ввести текст. |
| Кнопка | При нажатии кнопки выполняется запрограммированное действие. |
| Переключатель | Кнопка, используемая для выбора только одного параметра из группы. |
| Список | Содержит список элементов. |
| Поле со списком | Текстовое поле с раскрывающимся списком. Можно ввести или выбрать нужное значение из списка. |
| Выключатель | Кнопка, которая остается нажатой после нажатия на нее. Чтобы изменить состояние кнопки, нужно нажать ее еще раз. |
| Счетчик | Кнопка, которая может быть вложена в ячейку или текстовое поле. Стрелка вверх служит для увеличения значения, а стрелка вниз – для уменьшения. |
| Полоса прокрутки | Прокручивает список значений при нажатии стрелок прокрутки или перемещении ползунка. Чтобы пролистать лист на одну страницу, достаточно щелкнуть мышью между ползунком и стрелкой прокрутки. |
| Заголовок | Текст, добавляемый к листу или форме и описывающий элемент ActiveX, лист или форму. |
| Рисунок | Элемент ActiveX, позволяющий внедрить рисунок в форму. |
| Дополнительные элементы | Список дополнительных элементов ActiveX. |
Таблица 1.2 – Элементы управления панели инструментов Формы
| Элемент | Назначение |
| Заголовок | Текст, описывающий элемент, лист или форму. |
| Поле «Группа» | Группы связывают между собой элементы, такие как переключатели и флажки. |
| Кнопка | При нажатии на кнопку запускается макрос |
| Флажок | Включает или выключает действие определенного параметра. На листе или в группе может быть установлено несколько флажков одновременно. |
| Переключатель | Выберите один из параметров, содержащихся в группе. Используйте переключатель для выбора только одной из существующих возможностей. |
| Список | Отображает список элементов. |
| Поле со списком | Раскрывающийся список. После выбора в списке какого-либо элемента этот элемент остается в текстовом поле списка. |
| Полоса прокрутки | Прокручивает список значений при нажатии стрелок прокрутки или перемещении ползунка. Чтобы пролистать лист на одну страницу, достаточно щелкнуть мышью между ползунком и стрелкой прокрутки. |
| Счетчик | Увеличивает или уменьшает значение. Стрелка вверх служит для увеличения значения, а стрелка вниз – для уменьшения. |
1.2.2 Создание активной кнопки. На листе рабочей книги можно располагать различные управляющие элементы (элементы аналогичные тем, которые есть на диалоговых окнах) и «заставить» эти элементы работать. Добавление новых управляющих элементов на лист производится с помощью панели инструментов Элементы управления. Для включения панели щелкните правой кнопкой мыши на панелях инструментов и выберите одноименную команду.
Данная панель, в частности, содержит инструмент Кнопка. Нажмите этот инструмент и укажите место и размер будущей кнопки на листе рабочей книги.
Из контекстного меню на новой кнопке выбрать команду Свойства. В поле Caption (рис. 1.3) заменить стандартное название кнопки на придуманное самостоятельно.
Рисунок 1.3 – Окно Properties, содержащее перечень свойств
создаваемой кнопки
Дважды щелкнуть на кнопке. Откроется окно редактора Visual Basic на процедуре созданной для данной кнопки. Между строками начала и конца процедуры допишите команду для запуска созданного макроса:
Private Sub CommandButton1_Click()
Call макрос1
End Sub
Примечание: макрос1 – это то имя макроса, которое указано для примера, а Вы должны написать вместо него – имя созданного Вами макроса.
Вернитесь в Excel и на панели инструментов Элементы управления отожмите кнопку Конструктор 
. Теперь можно закрыть всю эту панель. Ваша кнопка готова.
ХОД ВЫПОЛНЕНИЯ РАБОТЫ
2.1 Открыть новую книгу и сохранить под именем «ваша фамилия»_excel10.
2.2 Заполнить несколько строк и столбцов произвольными данными.
2.3 Создать макрос для форматирования ячейки (см. индивидуальное задание).
2.4 Создать кнопку для выполнения созданного макроса.
2.5 Сохранить все изменения в книге.
ИНДИВИДУАЛЬНЫЕ ЗАДАНИЯ
Номер варианта соответствует 2-й цифре по журналу академической группы (цифра 0 – вариант№10).
| № варианта | Параметры форматирования | |||||||
| шрифт | начертание | размер | подчеркивание | цвет | видоизменение | выравнивание | ||
| по горизонтали | по вертикали | |||||||
| 1. | Impact | полужирный | двойное, по значению | цвет | надстрочный | по центру | по нижнему краю | |
| 2. | Times New Roman | курсив | одинарное, по значению | цвет | зачеркнутый | по левому краю | по верхнему краю | |
| 3. | Verdana | курсив | двойное, по ячейке | цвет | зачеркнутый | по ширине | по высоте | |
| 4. | Courier New | полужирный курсив | одинарное, по ячейке | цвет | подстрочный | с заполнением | по центру | |
| 5. | Impact | полужирный курсив | двойное, по значению | цвет | подстрочный | распределенный | по верхнему краю | |
| 6. | Times New Roman | полужирный | одинарное, по значению | цвет | надстрочный | по центру выделения | распределенный | |
| 7. | Verdana | полужирный | двойное, по ячейке | цвет | надстрочный | по центру | по центру | |
| 8. | Courier New | курсив | одинарное, по ячейке | цвет | зачеркнутый | по левому краю | по нижнему краю | |
| 9. | Courier New | курсив | двойное, по ячейке | цвет | зачеркнутый | с заполнением | распределенный | |
| 10. | Times New Roman | полужирный курсив | одинарное, по ячейке | цвет | подстрочный | распределенный | по высоте |
КОНТРОЛЬНЫЕ ВОПРОСЫ
1) Что такое макрос в MS Excel?
2) Как создать макрос в MS Excel?
3) Как удалить макрос в MS Excel?
4) Как выполнить макрос в MS Excel?
5) Как изменить макрос в MS Excel?
6) Как скопировать модуль макроса в другую книгу?
7) Что такое элементы управления?
8) Какие виды элементов управления вы знаете?
9) Как создать активную кнопку?