Автоматизация работы в MS Excel. Создание и использование макросов

1.

Автоматизация работы в MS Excel.
Создание и использование макросов.
MS Excel включает в себя
объектно-ориентированный язык программирования
Visual Basic for Application (VBA) расширение языка Visual Basic.
VBA используется в таких приложениях
пакета Microsoft Office как Excel, Word, Access,
PowerPoint, Outlook.
Рабочая книга, содержащая макросы, сохраняется как
книга MS Excel с поддержкой макросов. Расширение *.xlsm

2.

Два метода создания макросов:
1. В автоматическом режиме с помощью
Макрорекодера. (MacroRecoder)
2. С помощью среды VB.

3.

Запись макроса в
автоматическом режиме
I. Этап: Подготовка к записи макроса:
ввод параметров, необходимых для
оформления макроса.
1. Открыть ДО «Запись макроса»,
для этого выполнить
последовательность действий:
Вкладка «Разработчик» - Группа
«Код» - Команда «Начать запись»
Диалоговое окно Запись макроса
Личная книга макросов - макрос сохраняется как
глобальный в личной книге макросов
PERSONAL.XLS, и доступен для всех других
рабочих книг Excel.

4.

II. Этап: Запись макроса:
выполнение действий, которые
составят тело макроса.
Действия с
указанным макросом:
1. Выполнить макрос
2. Изменить код макроса
3. Удалить макрос
4. Изменить внешние
параметры

5.

Создание кнопки

6.

Создание графического объекта

7.

Кнопки на панели быстрого доступа(ПБД)
1
2
3

8.

Уровень безопасности

9.

Обзор элементов
программирования на VBA
Редактор VB.
Каждое из основных приложений MS Office имеет собственную
интегрированную среду разработки (IDE – Integrated Development
Environment).
Компания Microsoft называет эту среду редактором Visual Basic.
Среда разработки программ – редактор VB, который является
частью инсталляционного пакета Ms Office.
Программирование в Excel – это объектно-ориентированное
программирование, которое основано на двух компонентах:
- язык программирования VBA;
- объектная модель Excel.

10.

Процедура обработки события Open объекта Workbook, генерируемого при
открытии книги
Private Sub Workbook_Open()
MsgBox "Вы сегодня немножко ленивы, но все-таки начнем!", vbInformation, "ЭС-121"
End Sub

11.

Редактор VBA
Вызов редактора:
Alt+F11
Процедуры сохраняются в рабочих книгах. Вся совокупность
кодов, сохраненных в одной рабочей книге, образует
проект VBA(VBA project).

12.

13.

VBA проект имеет иерархическую структуру:
VBA проект(ИмяКниги1)
Ms Excel Objects
Лист1(ИмяЛиста1)
Лист2(ИмяЛиста2)
Лист3 ИмяЛиста3)
Эта книга
Модули
Модуль1
Модуль2
Пользовательские формы
UserForm1
UserForm2
Модули классов

14.

15.

Интерфейс редактора VBA
ОСНОВНЫЕ КОМПОНЕНТЫ:
окно проекта (Project Explorer), окно свойств (Properties Window), окно редактирования
кода (Code), окно редактирования форм (UserForm), окно просмотра объектов (Object
Browser), меню и панели инструментов.
Окно редактора VBA

16.

Окно проекта (Project Explorer)
Окно свойств (Propeties)

17.

Окно кода (Code)

18.

Окно редактирования форм (UserForm)

19.

Окно просмотра объектов (Object Browser)

20.

Управляющее меню
Панель инструментов (Standard)

21.

Кнопка
View
Insert
Save
Действие
Перейти в приложение, из которого запускался
Редактор VBA
Отобразить список объектов, которые можно
вставить в текущий проект
Сохранить текущий проект
Find
Вырезать выделенный текст или объект и
поместить его в буфер обмена
Скопировать выделенный текст или объект в
буфер обмена
Вставить выделенный текст или объект из буфера
обмена в окно Code или пользовательскую форму
Поиск слова или фразы в тексте модуля
Undo
Отменить последнее выполненное действие
Redo
Вернуть последнее отмененное действие
Run Macro
Break
Запустить текущую процедуру или форму
Прервать выполнение кода VBA
Reset
Перезапустить код VBA
Design Mode
Перейти в режим (конструктора)
Cut
Copy
Paste
Project Explorer Отобразить окно Project Explorer (окно проекта)
Properties
Window
Object Browser
Toolbox
Отобразить окно Properties Window (окно свойств)
Отобразить
окно
Object
Browser
(окно
обозреватель объектов)
Отобразить панель Toolbox (панель инструментов)

22.

Пример 1 Создать процедуру, исполняемую при
открытии книги.
1.Вызов редактора VB
2.В окне проекта выбрать объект ЭтаКнига
3.В левой части окна кода выбрать Эта книга
4.В правой части окна кода выбрать событие Open
5.В окне кода появятся две строчки – заготовка
процедуры события «открытие книги»
Private Sub Workbook_Open()
MsgBox(“Добрый день!”)
End Sub
6.Закрыть книгу с сохранением
7.Снова открыть книгу

23.

Пример . Создать процедуру, которая либо убирает
сетку с рабочего листа, либо отображает ее.
Разместим эту процедуру в стандартном модуле:
1.Вызов редактора
2.Команда меню Insert-модуль
3.В окне кода Insert-Sub
4.Ввод имени – Сетка
Sub Сетка()
ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines
End Sub

24.

Процедуры-функции.
Создание функций пользователя
Синтаксис объявления процедуры-функции:
[Public или Private] Function Название (формальные_параметры) [As тип]
Операторы
Название=Выражение
End Function
Обращение к функции (находящееся в программе) имеет вид:
Название (фактические_параметры),

25.

Для создания функции надо выполнить:
1.Вызов редактора
2.Команда меню Insert-Module
3.В окне кода Insert-Procedure
4.Установить переключатель ● Function
5. В окне кода ввести операторы для этой функции.
ПРИМЕР:
Создать функцию, которая будет вычислять длину гипотенузы
прямоугольного треугольника по значениям двух катетов
Public Function Гипотенуза(катет1 As Single, катет2 As Single) As Single
Гипотенуза = Sqr((катет1) ^ 2 + (катет2) ^ 2)
End Function

26.

27.

28.

29.

ПРИМЕЧАНИЕ:
В кодах VBA десятичным знаком является точка.
Арифметические выражения
+
*
/
^
\
сложение
вычитание, изменение знака
умножение
деление
возведение в степень
целочисленное деление (деление целых чисел с отбрасыванием остатка)
Mod остаток от деления целых чисел – целое число
Функции VBA
Функция
sinx
cosx
tgx
lnx
ex
|x|
x
Запись на VBA
Sin(x)
Cos(x)
Tan(x)
Log(x)
Exp(x)
Abs(x)
Sqr(x)
Примеры:
1/3
X
---> X^(1/3)
a+b
---> (a+b)/(c+d)
c+d
b
Log a ---> log(b)/log(a)