Хамухин А.А. Введение в информатику для инженеров нефтегазового дела:
Подождите немного. Документ загружается.
П.3.5.Лабораторная работа №5. Работа с макросами в приложениях
Word и Excel
Цель работы: научиться работать со встроенным редактором VBA (создавать,
выполнять, останавливать, выполнять по шагам макросы в Word и Excel).
Порядок работы
Уровень
A
1. В MS Word найти редактор VBA первым способом (вход через
меню «сервис-макрос-редактор Visual Basic») и открыть в нем окно
программ, окно проектов и окно свойств.
2. С помощью меню VBA «вставить модуль», набрать в окне
программ заданный текст программы. С помощью пошагового
выполнения (F8) и просмотра значений переменных (наводить
курсор мышки на имя переменной) добиться правильной работы
макроса (количество баллов должно соответствовать правильным
ответам).
3. Выполнить ту же задачу вторым способом. Для этого вставить в
документ элемент управления Command Button (командная кнопка),
изменить его свойство Caption (надпись на кнопке), зайти в редактор
VBA (двойной щелчок по кнопке в режиме «конструктор») и набрать
(скопировать) ту же программу.
4. Повторить п. 3 в MS Excel. Добиться, чтобы программа
аналогично проработала при нажатии на вставленную кнопку (при
выключенном режиме «конструктор»)
B
5. Выполнить индивидуальное задание (см. ниже). Внести ошибку в
текст программы (делается преподавателем) и с помощью
пошаговой отладки найти и устранить ее (делается студентом).
6. По ходу работы составить отчет, позволяющий повторить
выполненные действия нажатием на указанные клавиши или пункты
меню, чтобы получить заданный результат
Программа
Private Sub CommandButton1_Click()
Sum = 0
o1 = MsgBox("Является ли макрос программой?", 3, "Вопрос 1")
If o1 = 6 Then Sum = Sum + 10
'***********************************************************
o2 = InputBox("Как называется программа VBA?", "Вопрос 2", "не знаю")
If o2 = "макрос" Then Sum = Sum + 10
'***********************************************************
If Sum >= 20 Then
181
Call MsgBox("Вы набрали " & Sum & " баллов. Можете продолжать
дальше", 0,"Результаты")
Else
Call MsgBox("Вы набрали " & Sum & " баллов. Этого мало. Необходимо
повторить изучение", 0, "Результаты")
End If
End Sub
Индивидуальные задания
1. Изменить вопросы по заданию преподавателя
2. Изменить ответы по заданию преподавателя
3. Добавить вопрос 3 с кнопками для ответа
4. Добавить вопрос 3 с произвольным текстом для ответа
5. Изменить формулу расчета набранных баллов
6. Изменить резюме по сумме набранных баллов
7. Добавить в документ вторую кнопку с контрольными вопросами
8. Поменять местами вопрос 1 и вопрос 2
Пример окна приведен на рис. П5.10 (Приложение 5).
Контрольные вопросы
1. Как открыть окно для набора программы VBA?
2. Как вставить командную кнопку на лист?
3. Как вставить программу обработки нажатия на командную кнопку?
4. Как проверить работу программы обработки нажатия на кнопку по шагам?
5. Как просматривать значения переменных при выполнении программы по
шагам?
П3.6. Лабораторная работа №6. Обмен данными между листами
Excel и программой на VBA
Цель работы: научиться: считывать исходные данные с листа Excel в
переменные VBA, записывать результаты расчетов на лист Excel из переменных
VBA и очищать ячейки листов из программы на VBA.
182
Порядок работы
Уровень
A
1. Набрать на листе 1 в первых двух столбцах таблицу исходных
данных X и Y, например: «Прогноз температуры» на 10 дней
(A2:A11, B2:B11), первую строку использовать для подписи
заголовков столбцов. В ячейку B14 занести количество точек в
массивах.
2. На листе 1 разместить 3 командных кнопки, одну подписать
«ЧТЕНИЕ С ЛИСТА», вторую «ЗАПИСЬ НА ЛИСТ», третью
«ОЧИСТКА».
3. Через кнопку «ЧТЕНИЕ С ЛИСТА» открыть редактор VBA и
набрать программу считывания данных с листа 1 в переменные VBA
(X, Y, N). С помощью пошаговой отладки убедиться, что переменные
VBA заполняются числами из 1 и 2 столбцов листа Excel.
4. Через кнопку «ЗАПИСЬ НА ЛИСТ» открыть редактор VBA и
набрать программу записи данных на лист 2. С помощью кнопки
«ОЧИСТКА» набрать программу, стирающую результаты с листа 2.
5. Проверить работу кнопок: после нажатия кнопок «ЧТЕНИЕ С
ЛИСТА», «ЗАПИСЬ НА ЛИСТ», на листе 2 должны появиться все
данные с листа 1. После нажатия на кнопку «ОЧИСТКА» все данные
с листа 2 должны исчезнуть. Если нажимается только кнопка
«ЗАПИСЬ НА ЛИСТ», без нажатия на кнопку «ЧТЕНИЕ С ЛИСТА»,
то лист 2 должен оставаться пустым.
B
6. Дополнить исходные данные в соответствии индивидуальным
числом N (как в лаб.4), внести изменения в программу и проверить
правильность работы. По ходу работы составить отчет
Программа
Dim X(), Y(), N As Variant ' объявление переменных уровня модуля
‘ фрагмент программы под кнопку «ЧТЕНИЕ С ЛИСТА»
Private Sub CommandButton1_Click()
N = Worksheets(1).Range("B14").Value ' чтение ячейки B14 листа 1 в
переменную N
ReDim X(N), Y(N)
' цикл считывания N ячеек первого и второго столбца листа 1 в массивы X
и Y
For i = 1 To N
X(i) = Worksheets(1).Cells(i + 1, 1).Value
Y(i) = Worksheets(1).Cells(i + 1, 2).Value
Next i
End Sub
183
‘ фрагмент программы под кнопку «ЗАПИСЬ НА ЛИСТ»
Private Sub CommandButton2_Click()
' Запись на лист 2 содержимого переменных N (в ячейку D14), X и Y ( 3 и 4
столбцы)
Worksheets(2).Range("D14").Value = N
For i = 1 To N
Worksheets(2).Cells(i + 1, 3).Value = X(i)
Worksheets(2).Cells(i + 1, 4).Value = Y(i)
Next i
End Sub
‘ фрагмент программы под кнопку «ОЧИСТКА»
Private Sub CommandButton3_Click()
' Очистка на 2 листе ячейки D14, 3 и 4 столбца
Worksheets(2).Range("D14").ClearContents
For i = 1 To N
Worksheets(2).Cells(i + 1, 3).Value = " "
Worksheets(2).Cells(i + 1, 4).Value = " "
Next i
End Sub
Индивидуальные задания
Взять из лабораторной работы №4.
Пример окна приведен на рис. П5.11 (Приложение 5).
Контрольные вопросы
1. Как считать значение ячейки листа Excel в переменную VBA?
2. Как записать значение переменной VBA в ячейку листа Excel?
3. Как заполнить диапазон ячеек одной строкой на VBA?
4. Как очистить содержимое диапазона ячеек листа с помощью VBA?
5. Как заполнить массив VBA содержимым ячеек на листе Excel?
П3.7. Лабораторная работа №7. Программирование
арифметических формул. Оконный ввод-вывод информации на
VBA
Цель работы: научиться на VBA: программировать арифметические выражения,
вводить исходные данные и выводить результаты через окна.
Порядок работы
Уровень
A
1. Набрать на листе 1 в первых двух столбцах таблицу исходных
данных X и Y, например Ваш прогноз температуры на 10 дней
184
(A2:A11, B2:B11), первую строку использовать для подписи
заголовков столбцов (взять из лаб. раб. №4). Под таблицей поместить
командную кнопку «РАСЧЕТ».
2. Добавить на лист кнопку «РАСЧЕТ», открыть редактор VBA и
набрать фрагмент программы чтения с листа массивов X и Y (из
предыдущей лаб. раб. № 6). Переменную N (количество
экспериментальных точек в соответствии с индивидуальным
заданием) ввести с помощью функции оконного ввода INPUTBOX.
3. По расчетной формуле из лаб. раб. № 4 набрать программу расчета
коэффициента линейной регрессии (а) и и сравнить его с результатом,
полученным в лабораторной работе № 4 (должны полностью
совпасть).
4. Изменить заданный фрагмент программы в соответствии с
индивидуальным заданием
B
5. Проверить правильность расчета по индивидуальному заданию
путем набора заданной формулы в ячейке Excel, в отчете отразить
внесенные в программу изменения и полученные результаты
Фрагмент программы
'Считываем N из окна ввода
N = InputBox("ВВеди N", "Окно ввода", «Значение по умолчанию»)
‘************** сюда вставить фрагмент чтения с листа массивов X и Y,
подобный предыдущим лабораторным работам
'Вычислим все суммы в расчетной формуле
s1 = 0
s2 = 0
s3 = 0
s4 = 0
For i = 1 To N
s1 = s1 + X(i)
s2 = s2 + Y(i)
s3 = s3 + X(i) * Y(i)
s4 = s4 + X(i) ^ 2
Next i
'Вычисляем коэффициент линейной регрессии (а )и выводим его в окно
a = (N * s3 - s1 * s2) / (N * s4 - s1 ^ 2)
Call MsgBox("Коэффициент А= " & a, 0, "Результаты")
Индивидуальные задания
№
вар.
Расчетная формула
1 2
185
1 2
1
∑∑
∏∏∏
−=
===
−
−
=
N
i
N
i
ii
N
i
N
i
N
i
iii
XXN
YXXN
a
11
22
111
)()(*
**
2
∏∏
∑∑∑
==
==
−
−
=
N
i
N
i
ii
N
i
N
i
N
i
iiii
XXN
YXYXN
a
11
22
11
)()(*
***
=1
3
∑∑
∏∏
∑
−=
==
=
−
−
=
N
i
N
i
ii
N
i
N
i
N
j
jii
XXN
YXXN
a
11
22
11
1
)()(*
**
4
∏∏
∑∑
∏
==
==
=
−
−
=
N
i
N
j
ji
N
i
N
i
N
j
jiii
XXN
YXYXN
a
11
22
11
1
)()(*
***
5
∑∑
∏
∑∑
−=
=
==
−
−
=
N
i
N
i
ii
N
j
N
i
N
i
iijj
XXN
YXYXN
a
11
22
1
11
)()(*
***
6
∑∑
∑
∏∏
−=
==
−
−
=
N
i
N
i
ii
N
i
N
j
N
i
iji
XXN
YXXN
a
11
22
11
)()(*
**
7
∑∑
∏∏∏
−=
==
−
−
=
N
i
N
i
ii
N
i
N
i
N
i
iiii
XXN
YXYXN
a
11
22
11
)()(*
***
=1
8
∏∏
∏
∑∑
==
=
==
−
−
=
N
i
N
i
ii
N
i
N
j
N
i
jjii
XXN
YXYXN
a
11
22
1
11
)()(*
***
9
∏∏
∑
∏∏
==
=
==
−
−∗
=
N
i
N
i
ii
N
i
N
j
N
j
jjii
XXN
YXYXN
a
11
22
1
11
)()(*
**
186
1 2
10
∏∏
∑∑
==
==
+
−−
=
N
i
N
i
ii
N
i
N
j
N
j
jiii
XXN
YXYXN
a
11
22
11
)()(*
*)(*
∑
=1
11
∏∏
∑∑
==
==
+
−+
=
N
i
N
i
ii
N
i
N
j
N
j
jiii
XXN
YXYXN
a
11
22
11
)()(*
*)(*
∑
=1
12
∏∏
∑∑
==
==
+
−
=
N
i
N
i
ii
N
i
N
j
N
j
jiii
XXN
YXYXN
a
11
22
11
)()(*
*)*(*
∑
=1
13
∏∏
∑∑
==
==
+
−
=
N
i
N
i
ii
N
i
N
j
N
j
jiii
XXN
YXYXN
a
11
22
11
)()(*
*)/(*
∑
=1
14
∑∑
∏∏
−=
==
−
−−
=
N
i
N
i
ii
N
i
N
i
N
i
iiii
XXN
YXYXN
a
11
22
11
)()(*
*)(*
∏
=1
15
∑∑
∏∏
−=
==
−
−+
=
N
i
N
i
ii
N
i
N
i
N
i
iiii
XXN
YXYXN
a
11
22
11
)()(*
*)(*
∏
=1
16
∑∑
∏∏
−=
==
−
−
=
N
i
N
i
ii
N
i
N
i
N
i
iiii
XXN
YXYXN
a
11
22
11
)()(*
*)*(*
∏
=1
17
∑∑
∏∏
−=
==
−
−
=
N
i
N
i
ii
N
i
N
i
N
i
iiii
XXN
YXYXN
a
11
22
11
)()(*
*)/(*
∏
=1
18
∑∑
∏
∑∑
−=
=
==
−
−
=
N
i
N
i
ii
N
j
N
i
N
i
iijj
XXN
YXYXN
a
11
22
1
11
)()(*
*)*(*
187
1 2
19
∑∑
∏
∑∑
−=
=
==
−
−−
=
N
i
N
i
ii
N
j
N
i
N
i
iijj
XXN
YXYXN
a
11
22
1
11
)()(*
*)(*
20
∑∑
∏
∑∑
−=
=
==
−
−+
=
N
i
N
i
ii
N
j
N
i
N
i
iijj
XXN
YXYXN
a
11
22
1
11
)()(*
*)(*
21
∑∑
∏
∑∑
−=
=
==
−
−
=
N
i
N
i
ii
N
j
N
i
N
i
iijj
XXN
YXYXN
a
11
22
1
11
)()(*
*)/(*
22
∏∏
∑∑
∏
==
==
=
−
+−
=
N
i
N
j
ji
N
i
N
i
N
j
jiii
XXN
YXYXN
a
11
22
11
1
)()(*
**
23
∏∏
∑∑
∏
==
==
=
−
−−
=
N
i
N
j
ji
N
i
N
i
N
j
jiii
XXN
YXYXN
a
11
22
11
1
)()(*
**
24
∏∏
∑∑∑
==
==
−
−−
=
N
i
N
j
ji
N
i
N
i
N
j
jiii
XXN
YXYXN
a
11
22
11
)()(*
**
=1
25
∏∏
∑∑∑
==
==
−
+−
=
N
i
N
j
ji
N
i
N
i
N
j
jiii
XXN
YXYXN
a
11
22
11
)()(*
**
=1
188
1 2
26
∏∏
∑∑∑
==
==
−
++
=
N
i
N
j
ji
N
i
N
i
N
j
jiii
XXN
YXYXN
a
11
22
11
)()(*
**
=1
27
∏∏
∑∑∑
==
==
−
−+
=
N
i
N
j
ji
N
i
N
i
N
j
jiii
XXN
YXYXN
a
11
22
11
)()(*
**
=1
28
∑∑
∏∏
∑
−=
==
=
−
+−
=
N
i
N
i
ii
N
i
N
i
N
j
jii
XXN
YXXN
a
11
22
11
1
)()(*
*
29
∑∑
∏∏
∑
−=
==
=
−
−−
=
N
i
N
i
ii
N
i
N
i
N
j
jii
XXN
YXXN
a
11
22
11
1
)()(*
*
30
∑∑
∏∏
∑
−=
==
=
−
++
=
N
i
N
i
ii
N
i
N
i
N
j
jii
XXN
YXXN
a
11
22
11
1
)()(*
*
31
∑∑
∏∏
∑
−=
==
=
−
−+
=
N
i
N
i
ii
N
i
N
i
N
j
jii
XXN
YXXN
a
11
22
11
1
)()(*
*
32
∑∑
∏∏
∑
−=
==
=
−
−+
=
N
i
N
i
ii
N
i
N
i
N
j
jii
XXN
YXYN
a
11
22
11
1
)()(*
*
Пример окна приведен на рис. П5.12 (Приложение 5).
189
Контрольные вопросы
1. Как вывести в одном окне пояснительный текст и значение переменной?
2. Как в окне ввода указать значение по умолчанию?
3. Как определить в программе, какую кнопку нажмет пользователь в окне
ввода?
4. Как вывести окно с тремя фиксированными кнопками (Yes, No, Cancel)?
5. Чем отличается программа расчета суммы элементов от программы расчета
произведения элементов?
П3.8. Лабораторная работа №8. Работа с массивами и чтение/запись
на диск
Цель работы: научиться на VBA: применению статических и динамических,
одномерных и двухмерных массивов, а также читать и записывать данные в файлы
на диске.
Порядок работы
Уровень
A
1. Создать на диске в корневом каталоге файл 2050.txt и записать туда
одно число N(в соответствии с индивидуальным заданием).
2. Набрать на листе 1 в первых трех столбцах таблицу исходных
данных X и Y, например «Прогноз температуры и давления» на N дней
(это число дано в индивидуальном задании), первую и вторую строки
использовать для подписи заголовков столбцов. Под таблицей
поместить командные кнопки «РАСЧЕТ» и «ОЧИСТКА».
3. Через кнопку «РАСЧЕТ» открыть редактор VBA и набрать
фрагмент программы чтения данных из таблицы листа в одномерный
массив X(i) и двухмерный массив Y(i, j), количество элементов
массива N при этом прочитать с диска из файла 2050.txt.
4. Дополнить программу пересчета элементов двухмерного массива Y
из град. Цельсия в град. Фаренгейта и из мм ртутного столба – в МПа
и программой записи элементов массива на лист (в столбцы 4 и 5).
5. Через кнопку «ОЧИСТКА» открыть редактор VBA и набрать
фрагмент программы, стирающий содержимое столбцов 4 и 5.
6. Дополнить программу фрагментом записи массива Y на диск в виде
текстового файла ФИО.txt, повторить расчет и убедиться, что именно
этот файл появился на диске (сравнить с данными на листе Excel, они
должны полностью совпасть)
B
7. Заменить статические массивы динамическими и добиться такого
же результата работы программы.
8. В отчете привести фрагмент дополненной программы и показать
преимущество динамических массивов
190