Файл: regeda_v_v_regeda_o_n_osnovy_programmirovaniya_na_vba.pdf
ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 02.12.2019
Просмотров: 3123
Скачиваний: 1
10.
АВТОМАТИЗАЦИЯ
ПРОЕКТОВ
С
ПОМОЩЬЮ
VBA
и
MS Excel
При
помощи
MS Excel
можно
решить
множество
разнообразных
за
-
дач
.
Использование
средств
VBA,
позволяет
дополнительно
создавать
мак
-
симально
удобный
и
гибкий
интерфейс
,
приспособленный
для
решения
конкретной
задачи
,
предусматривать
средства
защиты
от
несанкциониро
-
ванных
действий
,
а
также
автоматизировать
процесс
создания
электрон
-
ных
таблиц
с
помощью
написания
и
использования
собственных
макроко
-
манд
.
10.1.
Создание
макроса
в
среде
MS Excel
Написать
макрокоманду
,
создающую
рабочую
таблицу
,
в
которой
рассчитывается
налог
и
итоговая
цена
товара
.
Рабочая
таблица
содержит
ячейку
ввода
и
две
вычисляемые
ячейки
вывода
.
В
ячейку
ввода
необхо
-
димо
ввести
стоимость
товара
,
а
в
ячейках
вывода
будут
отображены
налог
и
цена
.
Для
этого
выполните
следующие
действия
:
1)
Открыть
новую
рабочую
книгу
в
среде
MS Excel.
2)
Выбрать
команду
Сервис
→
Запись
макроса
;
3)
В
поле
Имя
макроса
ввести
Макрос
1
;
4)
Щелкнуть
в
ячейке
С
6
и
ввести
Стоимость
;
5)
Щелкнуть
в
ячейке
С
7
и
ввести
Налог
;
6)
Щелкнуть
в
ячейке
С
8
и
ввести
Всего
;
7)
Щелкнуть
в
ячейке
D6
и
ввести
12,43;
8)
Щелкнуть
в
ячейке
D7
и
ввести
=D6*0,0825
;
9)
Щелкнуть
в
ячейке
D8
и
ввести
=D6+D7
;
10)
Выделить
ячейки
D6:D8
,
выбрать
команду
Формат
Ячейки
.
Выбрать
Денежный
формат
«
р
.»,
2
знака
после
запятой
;
11)
Щелкнуть
в
ячейке
D7,
выбрать
команду
Формат
Ячей
-
ки
→
Граница
.
Щелкнуть
в
поле
Сверху
и
затем
–
на
кнопке
ОК
;
12)
Остановить
запись
макроса
.
13)
Запустить
макрос
на
исполнение
,
в
результате
в
среде
MS Ex-
cel
создается
таблица
(
Рис
. 10.1),
содержащая
следующие
ячейки
.
Стоимость
12,30
р
.
Налог
10,15
р
.
Всего
22,45
р
.
Рис
. 10.1.
Текст
только
что
записанного
макроса
имеет
следующий
вид
:
Sub
Макрос
1()
Range("C6").Select
ActiveCell.FormulaR1C1 = "
Стоимость
"
Range("C7").Select
ActiveCell.FormulaR1C1 = "
Налог
"
Range("C8").Select
ActiveCell.FormulaR1C1 = "
Всего
"
Range("D6").Select
ActiveCell.FormulaR1C1 = "12.43"
Range("D7").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*0.825"
Range("D8").Select
ActiveCell.FormulaR1C1 = "=R[-2]C+R[-1]C"
Range("D6:D8").Select
Selection.NumberFormat = "#,##0.00$"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub
Если
сопоставить
текст
программы
с
только
что
сделанными
шага
-
ми
,
то
можно
увидеть
,
что
каждый
шаг
записан
в
одной
или
двух
строках
кода
программы
(
процедуры
).
В
процедуре
записано
также
много
допол
-
нительных
строк
:
в
них
устанавливаются
параметры
,
которые
не
задава
-
лись
явно
во
время
создания
рабочего
листа
.
Эти
дополнительные
строки
появляются
в
момент
щелчка
на
кнопке
ОК
в
диалоговом
окне
,
в
котором
устанавливаются
какие
-
либо
параметры
.
Хотя
можно
изменить
только
один
параметр
,
но
для
каждого
параметра
диалогового
окна
в
записывае
-
мом
макросе
появится
соответствующая
строка
.
Например
,
хотя
в
диало
-
говом
окне
Рамка
устанавливалась
только
опцию
Снизу
,
в
процедуру
вставлено
восемь
строк
кода
,
устанавливающих
значения
для
всех
осталь
-
ных
параметров
.
В
большинстве
случаев
удаление
этих
лишних
строк
ни
-
как
не
повлияет
на
выполнение
процедуры
.
За
заголовком
процедуры
следует
12
операторов
,
которые
последо
-
вательно
выделяют
каждую
ячейку
в
интервале
C6:D8
и
вставляют
в
эти
ячейки
текст
или
формулу
.
Четыре
последних
оператора
из
них
записыва
-
ют
в
ячейки
формулы
.
В
них
используется
система
адресации
ячеек
R1C1
,
а
не
прямая
ссылка
на
эту
ячейку
.
Система
адресации
R1C1
означает
стро
-
ку
(
Row
)
и
столбец
(
Colomn
),
соответствующие
активной
ячейке
.
10.2.
Оптимизция
макроса
При
написании
той
же
самой
процедуры
вручную
программный
код
можно
существенно
сократить
.
Скопировать
макрос
через
буфер
обмена
и
отредактировать
его
следующим
образом
:
Sub
Макрос
2()
Range("C6")="
Стоимость
"
Range("C7")="
Налог
"
Range("C8")="
Всего
"
Range("D6")= "12.43"
Range("D7")= "=R[-1]C*0.825"
Range("D8")="=R[-2]C+R[-1]C"
Range("D6:D8") = "#,##0.00$"
Range("D8").Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub
Проверить
работу
отредактированного
макроса
Макрос
2
.
10.3.
Автоматизация
создания
электронных
таблиц
Создать
в
MS Excel
программу
для
построения
графика
функции
y=xk
.
Результаты
работы
программы
приведены
на
Рис
. 10.2.
Рис
. 10.2.
1)
Объединить
ячейки
A1
и
B1
и
вписать
в
полученную
ячейку
слова
«
Исходные
данные
».
2)
Выделить
ячейки
А
3:
В
24
,
выбрать
в
контекстном
меню
команду
Формат
ячеек
→
Граница
и
задать
внешние
и
внутренние
границы
для
выбранных
ячеек
.
3)
Заполнить
ячейки
,
в
соответствии
с
таблицей
10.1.
Таблица
10.1.
Ячейка
A2 B2
D1
D2
D3
E1 E2 E3
Значение
x
y
Степень
(k) Min(x)
Шаг
изменения
х
2
-10 1
4)
Добавить
в
программу
с
помощью
панели
Элементы
управления
три
командные
кнопки
в
режиме
конструктора
и
через
контекстное
меню
назначить
им
следующие
значения
свойств
Name
и
Caption
,
указанные
в
таблице
10.2.
Таблица
10.2
Свойство
Кнопка
1
Кнопка
2
Кнопка
3
Name
CommandButton1 CommandButton2 Diagram
Caption
Очистить
таблицу
Заполнить
таблицу
Построить
диаграмму
5)
Выполнить
двойной
щелчок
на
клавише
CommandButton1
,
в
результате
откроется
окно
редактора
кода
,
в
котором
будет
помещена
заго
-
товка
для
процедуры
обработки
события
Click
объекта
CommandButton1
.
Внутри
процедуры
необходимо
дописать
следующий
код
:
Private Sub CommandButton1_Click()
Range("A3:B24).ClearContents
ActiveSheet.ChartObjects.Delete
End Sub
Записанная
процедура
очищает
ячейки
таблицы
в
диапазоне
ячеек
A3:B24
и
удаляет
диаграмму
ChartObjects
из
рабочего
листа
.
6)
Выполнить
двойной
щелчок
на
клавише
CommandButton2
,
в
результате
откроется
окно
редактора
кода
,
в
котором
будет
помещена
заго
-
товка
для
процедуры
обработки
события
Click
объекта
CommandButton2
.
Внутри
процедуры
необходимо
дописать
код
,
с
помощью
которого
происходит
заполнение
соответствующих
ячеек
таблицы
в
диапазоне
ячеек
A3:B24
.
Private Sub CommandButton2_Click()
X = Range("E2").Value
k = Range("E1").Value
For i = 3 To 24
Cells(i, 1).Value = X
Cells(i, 2).Value = X^k
X = X+Range("E3").Value
Next i
End Sub
7)
Выполнить
двойной
щелчок
на
клавише
Diagram
и
дописать
следующий
код
,
в
результате
откроется
окно
редактора
кода
,
в
котором
будет
помещена
заготовка
для
процедуры
обработки
события
Click
объ
-