Создание простых макросов с помощью VIsual Basic For application

 

Цель работы: Получить начальные навыки по созданию и редактированию простых макросов в Microsoft Excel

 

Теоретические сведения

 

Общие положения

 

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

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

 

Создание макроса

 

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

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

1. В меню Tools (Сервис) выбрать подменю Macro (Макрос) и команду Начать запись….

2. Ввести Macro name (Имя макроса). Первым символом имени макроса должна быть буква. Остальные символы могут быть буквами или цифрами. В имени макроса не допускаются пробелы; в качестве разделителей слов можно использовать знаки подчеркивания.

3. Для того чтобы макрос запускался с клавиатуры с помощью желаемой клавиши, необходимо ввести соответствующий символ (букву) этой клавиши в поле Shortcut Key (Сочетание клавиш). Для строчных букв используется сочетание CTRL + буква, а для заглавных ‑ CTRL + SHIFT + буква.

4. В поле Store macro in (Сохранить в книге) выбрать книгу, в которой должен быть сохранен макрос. Чтобы макрос был доступен независимо от того, используется ли в данный момент Excel, его следует сохранить в личной книге в папке XLStart. Чтобы создать краткое описание макроса, введите необходимый текст в поле Описание.

5. Нажать кнопку OK. По умолчанию, при записи макроса используются абсолютные ссылки. Макрос, записанный с абсолютными ссылками или адресами (Absolute reference), при выполнении всегда обрабатывает те же ячейки, которые обрабатывались при его записи. Для того чтобы с помощью макроса обрабатывать произвольные ячейки, следует записать его с относительными ссылками (Relative reference). Для этого нажмите кнопку Относительная ссылка на панели инструментов Остановка записи. Относительные ссылки будут использоваться до конца текущего сеанса работы в Microsoft Excel или до повторного нажатия кнопки Относительная ссылка. При использовании абсолютных ссылок координаты ячеек отсчитываются от верхнего левого угла листа. В режиме с относительными ссылками отсчет начинается от текущей ячейки.

6. Выполнить макрокоманды, которые нужно записать.

7. Нажать кнопку Stop recording (Остановить запись) панели инструментов.

Если возникает необходимость создания макроса со сложным алгоритмом работы (например, большое число исполняемых действий, наличие условий выполнения операций, циклов и вычислений), то следует воспользоваться редактором Visual Basic и набрать текст этого макроса.

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

 

Sub FirstProc()

MsgBox "Результат умножения равен:" _

& CStr(2*2), vbOKOnly, "Дважды два"

End Sub

 

Поясним приведенный текст. Ключевыми словами Sub и End Sub в языке Visual Basic указываются начало и конец процедуры (Subroutine). FirstProc – название созданной нами процедуры. Оно может быть любым и содержать цифры и символы английского алфавита. MsgBox – это процедура языка Visual Basic, которая показывает на экране простейший диалог с сообщением. Запись “Результат умножения равен:” ‑ это сообщение, которое будет находиться внутри диалога. Символ & обозначает объединение двух строк. Процедура языка Visual Basic CStr( 2*2 ) преобразует числовой результат умножения в строку. Константа vbOkOnly сообщает процедуре MsgBox, что диалог должен содержать только одну кнопку ОК. Запись “Дважды два” ‑ это заголовок диалога. Более подробную информацию о функции MsgBox и о других функциях Visual Basic, а также об их параметрах можно почерпнуть из справки по языку, которая вызывается нажатием клавиши F1. Обратите внимание на символ подчеркивания, который разрывает строку с оператором MsgBox, и предшествующий ему пробел. Таким образом, вы можете перенести длинные строки программы, не нарушая синтаксиса языка Visual Basic.

Приведем пример текста макроса с диалогом для ввода информации.

 

Sub InputDigit()

MyStr = InputBox("Введите число:")

If IsNumeric(MyStr) Then

MsgBox "Результат умножения равен: " _

& CStr(MyStr * 2), vbOKOnly, "Умножение"

End If

End Sub

 

Здесь InputBox – это функция, которая выводит на экран диалог с предложением “Введите число” и содержит поле ввода текста. Введенный текст присваивается переменной MyStr. Если строка содержит число (что проверяется с помощью функции IsNumeric), то на экран выводится результат умножения.

 

Выполнение макроса

 

После того как макрос записан, его можно запустить из Microsoft Excel или из редактора Visual Basic. Запуск макроса вызывает выполнение всех записанных в нем операций. Для прерывания выполнения макроса нажать клавишу ESC.

Для выполнения макроса из Microsoft Excel необходимо:

1. в меню Tools (Сервис) выбрать пункт Macro (Макрос), а затем команду Macros (Макросы…);

2. выбрать из списка имя того макроса, который нужно выполнить;

3. нажать кнопку Run (Выполнить).

Для выполнения макроса из Visual Basic необходимо:

1. в меню Run (Выполнить) выбрать пункт Run Macro (Выполнить Макрос);

2. выбрать из списка имя того макроса, который нужно выполнить;

3. нажать кнопку Run (Выполнить).

Для отладки и поиска ошибок в макросах существует возможность их пошагового выполнения. Если в меню Debug (Отладка) щелкнуть по кнопке Step Into (Шаг), то работа макроса будет приостановлена после выполнения текущей команды.

Макрос можно также запустить с помощью назначенной кнопки на встроенной или специальной панели инструментов.

Для назначения кнопки запуска макроса необходимо в Microsoft Excel выполнить следующие действия:

1. в меню View (Вид) выбрать меню Панели инструментов, а затем меню Forms (Формы);

2. в появившемся окне выбрать подменю Button (Кнопка);

3. задать размер кнопки;

4. в появившемся окне Assign macro (Назначить макрос объекту) выбрать из списка нужный макрос;

5. нажать кнопку OК.

Для добавления кнопки запуска макроса на панель инструментов необходимо в Microsoft Excel выполнить следующие действия:

1. в меню View (Вид) выбрать меню Панели инструментов, а затем меню Настройка…;

2. в появившемся окне выбрать закладку Команды и в списке Категории выбрать строку Макросы;

3. из появившегося списка команд перетянуть команду Настраиваемая кнопка на панель инструментов;

4. щелкнуть левой кнопкой мыши по созданной кнопке;

5. в появившемся окне Assign macro (Назначить макрос объекту) выбрать из списка нужный макрос;

6. нажать кнопку OК.

 

Редактирование макроса

 

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

Последовательность действий при редактировании макроса:

1. в меню Tools (Сервис) выберите пункт Macro (Макрос), а затем команду Macros (Макросы…);

2. выберите из списка имя того макроса, который нужно отредактировать;

3. нажмите кнопку Изменить.

4. в редакторе Visual Basic выполните необходимые изменения.

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

Для задания точки прерывания необходимо выполнить следующие действия в редакторе Visual Basic:

1. установить курсор на требуемой строке;

2. в меню Debug (Отладка) выбрать команду Toggle Breakpoint (Точка прерывания). При этом оператор будет выделен цветом. Так можно установить несколько точек прерывания.

 

Порядок выполнения работы

 

1. Изучите теоретические сведения.

2. Создайте макрос для автоматической нумерации строк таблицы.

3. Создайте макросы по заданному преподавателем варианту.

4. Создайте кнопки для запуска созданных макросов.

5. Сохраните файл с созданными макросами.

6. Перепишите текст макросов в отчет.

7. Закройте Visual Basic и Microsoft Excel.

8. Ответьте на контрольные вопросы по указанию преподавателя.

 

14.3 Контрольные вопросы

 

1. Для чего предназначены макросы?

2. Как создать новый макрос?

3. Чем отличаются абсолютные ссылки на адреса ячеек от относительных?

4. Зачем нужен пошаговый режим выполнения макроса?

5. Каким способом может быть изменен существующий макрос?

 

14.4 Задания для самостоятельной работы

 

1. Создайте макрос для вывода суммы чисел в ячейках А1 и В1 в ячейку С1.

2. Создайте макрос, который устанавливает у активной ячейки черный цвет фона и переходит на одну ячейку по диагонали (вниз и вправо).

3. В редакторе Visual Basic напишите макрос для определения гипотенузы прямоугольного треугольника по двум катетам. Значения катетов должны вводиться с клавиатуры каждый раз при выполнении макроса.

4. В редакторе Visual Basic напишите макрос для решения системы линейных уравнений: { .

5. В редакторе Visual Basic напишите макрос для решения квадратного уравнения вида a·x² + b·x + c = 0.

6. В редакторе Visual Basic напишите макрос для определения расстояния между заданными точками с координатами (x₁,y₁) и (x₂,y₂) по выражению: .

 

ЛАБОРАТОРНАЯ РАБОТА № 15