Методические указания по выполнению лабораторных работ VBA
методическая разработка (информатика и икт) на тему

УПРАВЛЕНИЕ ОБРАЗОВАНИЯ

АДМИНИСТРАЦИИ МУНИЦИПАЛЬНОГО ОБРАЗОВАНИЯ

ГОРОДСКОГО ОКРУГА «ВОРКУТА»

МУНИЦИПАЛЬНОЕ ОБЩЕОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ «ЛИЦЕЙ №1»

ЛАБОРАТОРНЫЕ РАБОТЫ ПО ИНФОРМАТИКЕ

Методические указания по выполнению лабораторных работ VBA

Составитель Яменко Ольга Петровна, учитель информатики

Воркута 2012

В методических указаниях рассмотрены основы работы с VBA.

Методические указания предназначены как для обучающихся основного общего (полного) образования., так и для студентов очно-заочной и заочной форм обучения всех специальностей.

Содержание:

Лабораторная работа №1. «Программирование линейного алгоритма в среде VBA»        

Лабораторная работа №2. «Программирование разветвляющегося  алгоритма в среде VBA»        

Лабораторная работа №3 «Циклы. Одномерный массив»        

Лабораторная работа №3 «Циклы. Одномерный массив»        

Лабораторная работа №4 «Вложенные циклы. Двумерный массив»        

Лабораторная работа №5 «Обработка строковых данных»        

Начало процедуры        

Лабораторная работа №6 «Создание форм в VBA»        

Dim St As String        

Лабораторная работа №1. «Программирование линейного алгоритма в среде VBA»

Цель работы: ознакомиться со средой VBA. Овладеть навыками программирования в VBA.

Задание 1. Программирование линейного алгоритма.

Пример1 : Написать программу вычисления функции f(x)=x2+1 в диалоговом режиме.

В программе нам понадобятся две переменные – x и f. Для ввода значения х будем использовать функцию ввода InputBox, а для вывода значения f – оператор вывода  MsgBox. Переменные х и у опишем как переменные вещественного типа, т.е. Single.

Чтобы попасть в VBA необходимо выполнить команду СервизМакросРедактор Visual Basic или нажать клавиши <Alt>+<F11>. Появиться окно VBA. Для вставки модуля выполним команду ВставкаМодуля (рис.7.).

В открывшемся окне модуля наберите следующую строку Sub PR1() и нажмите клавишу <Enter>. Таким образом у вас будет создан каркас процедуры:

Sub Pr1()

End Sub

Теперь необходимо наполнить этот каркас операторами.

Во-первых, опишем наши переменные x и f – Dim x as Single, f as Single

Во-вторых, огранизуем ввод переменной х – x = InputBox(“Введите значение х”)

В-третьих, запишем оператор для вычисления f

f = x^2+1

В-четвертых, выведем значение переменной f – MsgBox "f=" & Format(f, "###.#0"). Функцию Format использовали для того, чтобы вывести значение функции с 2-мя знаками после запятой. Итак, процедура должна иметь следующий вид:

Sub Pr1()

Dim x As Single, f As Single

x = InputBox("Введите значение х")

f = x ^ 2 + 1

MsgBox "f=" & Format(f, "###.#0")

End Sub

Для запуска программы на выполнение нажмите клавишу <F5>. Для запуска программы с листа Excel необходимо выполнить следующие действия:

Зайти на лист Excel.

Выполнить команду ВидПанели инструментовЭлементы управления (рис.8.).

Щелкнуть на элементе управления  кнопка и нарисуйте кнопку на листе Excel

Вызовите контекстное меню для нарисованной кнопки (щелчок правой кнопкой мыши) и выполните команду Свойства. Откроется окно свойств кнопки (Рис.3.). Используя свойства BackColor, Font, ForeColor выберите закраску фона кнопки и шрифт для надписи на кнопке.

В окне свойства Caption введите текст надписи на кнопке – Диалоговый режим.

Выполните двойной щелчок на кнопке. Откроется окно процедуры и каркас заготовки процедуры для кнопки

Private Sub CommandButton1_Click()

End Sub

Внутри каркаса напишите операторы, которые должны будут выполняться для при щелчке на этой кнопке. В нашем случае щелчок на кнопке, должен вызывать процедуру вычисления функции. Для вызова процедуры достаточно указать её имя. Итак, процедура для кнопки будет иметь следующий вид:

Private Sub CommandButton1_Click()

Pr1

End Sub

Зайдите на лист Excel  и отключите режим конструктора на панели элементов управления («отожмите» кнопку конструктора)

Щелкните на кнопке для запуска процедуры расчета функции.

Пример 2. Написать программу вычисления функции f(x)=x2+1 с использованием ячеек Excel.

Любая ячейка или группа ячеек является объектом Range, который в свою очередь, является производным от объекта Worksheet. Например, для обращения  к значению ячейки А1 на втором листе Excel, необходимо использовать следующую конструкцию : Worksheets(2).Range(“A1”)

Итак, оформим программу вычисления функции, причем значение переменной х будем считывать из ячейки листа Excel и значение переменной f, так же будем помещать в ячейку.

В ячейку A1 введите – х=, а в ячейку A2 – f(x)=

В ячейку B1 введите любое числовое значение переменной х

Зайдите в редактор VBA , откройте первый модуль, установите курсор под оператором End Sub

Наберите оператор начала процедуры для второго примера – Sub Pr2() и нажмите <Enter>

Опишите переменные x и f

Переменной х присвоим значение содержимого ячейки B1. Для этого запишите следующую конструкцию – x=Range(“B1”)

Напишите оператор вычисления функции (см. пример 1)

Поместим значение переменной f в ячейку B2 – Range(“B2”)=f

Оформите вызов программы Pr2 c листа Excel (с помощью кнопки, см. пример 1)

Задание 2.

Напишите программу вычисления  функции из лабораторной №4  для одного значения х.

Задание 3.

Напишите программу вычисления заработной платы для отдельного сотрудника. Исходные данные – ФИО, оклад и северные вводить в диалоговом режиме, а расчетный лист сотрудника оформите на втором листе Excel в виде таблицы

Лабораторная работа №2. «Программирование разветвляющегося  алгоритма в среде VBA»

Цель работы: овладеть навыками программирования разветвляющихся алгоритмов. Освоить структуру операторов If…Then и Select…Case

Оператор If...Then...Else

Синтаксис этого оператора следующий:

If условие Then [инструкции] [Else инструкции_else]

Допускается также использование блоковой формы синтаксиса:

If условие Then

[инструкции]

[Else

[инструкции_else]]

End If

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

Однострочная форма допускает выполнение нескольких инструкций в результате проверки одного условия If...Then, но все инструкции должны находиться на одной строке и разделяться двоеточием, как в следующем примере:

If A > 10 Then A = A + 1 : B = B + A : C = C + B

Допускаются вложенные блоки инструкций If; то есть блоки If, содержащиеся в других блоках.

Пример 1: Выбрать максимальное из двух введенных чисел. Рассмотрим 2 примера программы-в первой используется строчная форма записи условного оператора, во-второй – блочная

Пример2: Вычислить площадь треугольника, если таковой существует.

Sub pr1()

Dim x As Integer, y As Integer, z As Integer

x = Range("A1")

y = Range("A2")

z = Range("A3")

If (x + y) > z And (x + z) > y And (y + z) > x Then

   p = (x + y + z) / 2

   s = Sqr((p - a) * (p - b) * (p - c))

   Range("a4") = s

   Else

   Range("a4") = "Треугольник с такими сторонами не существует"

End If

End Sub

Оператор Select … Case

Если приходится проверять одно и то же значение в сравнении с различными выражениями, то самым подходящим обычно оказывается оператор Select…Case. Синтаксис этого оператора следующий:

Select Case значение

        Case критерий1

(операторы, выполняемые, когда значение удовлетворяет критерий1)

        Case критерий2

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

…’дополнительные операторы Case

Case Else ‘необязательный

(операторы, выполняемые, когда значение не удовлетворяет ни одному из приведенных критериев)

End Select

В каждом предложении Case допускается использование нескольких выражений или диапазонов.  Например, допустима следующая строка:

Case 1 To 4, 7 To 9, 11, 13, Is > MaxNumber

Пример 3

Sub pr()

Dim x as Integer

X= InputBox(“Введите число”)

Select Case x

Case 1 To 5

        MsgBox "Между 1 и 5"

Case 6, 7, 8        

        MsgBox "Между 6 и 8"

Case x > 8 And  x < 11        

MsgBox "Больше 8"

Case Else        

        MsgBox "Вне интервала 1 -- 10"

End Select

End Sub

Задание 1. Запрограммировать в VBA вычисление функции f(x) из лабораторной работы №6. В программе использовать обращение к ячейкам. Вызов программы организовать с листа Excel.

Задание 2. Написать программу решения квадратного уравнения ах2+bx+c=0 с помощью условного оператора Select…Case. В программе рассмотреть три случая: D=0,D>0,D<0. В программе использовать обращение к ячейкам и операторы ввода/вывода. Вызов программы организовать с листа Excel. Пример оформления задачи на рисунке 10

Лабораторная работа №3 «Циклы. Одномерный массив»

Цель работы: овладеть навыками применения циклического процесса при обработке одномерных массивов и табулирования функции.

Наиболее часто используемыми циклами являются следующие:

For… Next – заданное число раз

For счетчик = начало To конец [Step шаг]

[инструкции]

Next [счетчик]

Синтаксис инструкции For…Next содержит следующие элементы:

Счетчик        -  числовая переменная, используемая в качестве счетчика цикла.

Начало - начальное значение переменной счетчик.

Конец - конечное значение переменной счетчик.

Шаг - значение, на которое изменяется счетчик при каждом выполнении тела цикла.  Если это значение не задано, по умолчанию шаг равен единице.

Инструкции - одна или несколько инструкций между For и Next, которые выполняются указанное число раз.

Do While…Loop – пока или до тех пор, пока некоторое условие имеет значение  True

Do While  условие

[инструкции]

Loop

Синтаксис инструкции Do… Loop содержит следующие элементы:

Условие - числовое выражение или строковое выражение, которое имеет значение True или False.

Инструкции -одна или несколько инструкций, выполнение которых повторяется, пока условие имеет значение True .

Пример 1. Посчитать количество отрицательных элементов в массиве

Пример 2. Указать индекс первого нулевого элемента в массиве.

Задание 1. Сформировать указанный массив и  вывести результат на лист Excel.

Задание 2. Упорядочить элементы массива по возрастанию.

Задание 3. Для функции из лабораторной работы №4 написать программу в редакторе VBA.

Лабораторная работа №4 «Вложенные циклы. Двумерный массив»

Цель работы: овладеть навыками применения циклического процесса при обработке двумерных массивов.

Пример 1. Посчитать кол-во нулевых элементов   каждой строки массива А. Результат записать в вектор В .

Элементы массива А задать с помощью функции случайных чисел.

Задание 1. С помощью справочной системы VBA ознакомиться с инструкцией Randomize. Разобрать пример из справки.

Задание 2. Сформировать массив произвольной размерности, используя счетчик случайных чисел и результат вывести на лист Excel.

Лабораторная работа №5 «Обработка строковых данных»

Цель работы: овладеть навыками обработки строковых данных в среде VBA

При обработке строк , в случае удаления или добавления каких-либо символов, удобнее всего использовать циклы типа Do…Loop. Если же длина строки в процессе обработки не меняется, то можно использовать цикл For…Next.

При обработке строк используются следующие функции: InStr, Mid, Len и другие (см. лекции).

 Пример 1. Посчитать количество слов, начинающихся на указанную букву

В этой программе нам понадобятся переменная строкового типа, в которой будет храниться исходное предложение (st), переменная целого типа, которая будет отвечать за количество слов (k), и счетчик для цикла (i).

Задание1. Ознакомиться с функциями обработки строк в справке VBA

Задание2. Написать программу в VBA. Исходные и расчетные данные выводить на лист Excel. Запуск программ организовать с листа Excel.

Лабораторная работа №6 «Создание форм в VBA»

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

Наша программа-пример должна будет открывать на экране новое окно с показанной в нем цитатой, а также датой и временем (сообщением). Окно будет оставаться на экране до тех пор, пока пользователь не щелкнет на кнопке ОК.

Программа будет состоять из следующих элементов:

форма (UserForm), т.к. программа имеет одно окно;

два элемента управления для формы (надпись для сообщения и кнопка для команды OK);

программный код для двух процедур(одной – для надписи, в которую нужно поместить сообщение, а другой – для выхода из программы, когда пользователь щелкнет на кнопке OK).

Обе наши процедуры можно разместить в окне программного кода, ассоциированом с формой. Так как эти процедуры отвечают на события, которые происходят с формой.

Создание новой формы.

Для создания новой формы, которая будет служить окном программы, из меню редактора VBA выберите InsertUserForm.

Новая пользовательская форма представляет собой серую панель. Можно изменять её размеры, перетаскивая маленькие белые квадратики, примыкающие к правому и нижнему краям формы. Окно нашей программы должно быть немного шире и немного меньше по высоте (рис.11).

Добавим в форму пару элементов управления. Начнем с элемента управления надписью, который просто отображает текст. Во время выполнения программы элемент управления надписью отображает текст, который пользователь видит, но изменить не может. Но программа это сделать может и ей придется это сделать, так как  она будет отображать дату и время, которые зависят от того, когда программа выполняется.

Чтобы поместить надпись в новую пользовательскую форму, выполните следующее:

Убедитесь, что форма активна, щелкнув на ней;

Щелкните на пиктограмме с буквой А в панели элементов управления;

Каждой новой надписи VBA автоматически приписывает заголовок, поэтому в прямоугольной рамке, ограничивающий наш элемент управления надписью будет текст Label1. Это не тот текст, который нам нужен. Чтобы удалить автоматически созданный текст надписи, придется обратиться в окно свойств.

Окно свойств.

Каждый элемент управления имеет список свойств. Они определяют внешний вид и поведение элемента управления при выполнении программы. VBA дает возможность менять свойства элементов управления без печатания программного кода.

Для управления свойствами предназначено окно свойств. Как видно из рис.12., в окне свойств представлен список всех свойств выделенного в данный момент элемента управления. Чтобы изменить нужное нам свойство, просто найдите его в левом столбце и измените значение этого свойства в правом.

Для надписи в нашей программе нужно изменить лишь два свойства – Name (Имя) и Caption (Заголовок) . Замените имя элемента управления с Label1 на lblNow. Чтобы сделать это, найдите свойство Name в списке свойств. Двойным щелчком в правом столбце строки этого свойства выберите его, а затем впечатайте новое имя.

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

Кнопка команды

Элемент управления кнопкой команды отличается от надписи тем, что предполагает определенные действия со стороны пользователя программы. Когда кто-нибудь щелкает на кнопке, её изображение на экране меняется так, что она выглядит нажатой, а программа в ответ выполняет какие-нибудь действия.

Чтобы поместить такую кнопку в форму выполните следующее:

Щелкните в окне формы, чтобы сделать его активным снова;

Щелкните на той пиктограмме в панели элементов управления, на которой изображена кнопка;

Начав немного левее середины формы и нажав левую кнопку мыши, перетащите указатель по диагонали вниз, чтобы создать кнопку.

Для установления нужных значений свойств, выполните следующие шаги:

Измените значение свойства Name на OKButton

Измените значение свойства Caption на OK. Этот текст увидит пользователь на кнопке во время выполнения вашей программы.

Добавление программного кода

Чтобы напечатать связанный с формой или одним из её элементов управления программный код, вам нужно вместо окна формы открыть окно программного кода этой формы. Для этого выделите форму или элемент управления в ней. Затем воспользуйтесь одним из следующих способов:

Выбрать ViewCode из меню.

Нажать <F7>.

Щелкнуть на форме правой кнопкой мыши и в появившемся контектном меню выбрать View Code.

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

Private Sub OKButton_Click()

В любой VBA-процедуре первая строка программного кода определяет тип процедуры ( в данном случае это процедура типа Sub, т.е. подпрограмма) и имя процедуры. Для процедуры VBA предлагает имя OKButton_Click, которое представляет собой комбинацию имени кнопки и типа события.

Последней строкой автоматически генерируемого программного кода является

End Sub

Такой строкой должны заканчиваться все процедуры типа Sub. Эта строка сообщает VBA о том, что выполнение процедуры пора завершить.

Текстовый курсор будет мигать между этими двумя созданными VBA строками. Напечатав лишь одну дополнительную строку программного кода, мы дадим возможность программе завершить работу. Вот её программный код:

Unload Me

Оператор Unload убирает указанный объект из памяти. Здесь это объект с именем Me, имеющим в VBA специальный смысл. В данном случае оно означает форму и весь её программный код.

Вторая процедура  должна отображать на экране сообщение. В окне программного кода, которое должно было остаться активным, выполните следующие шаги:

В текстовом поле слева вверху окна программного кода, в котором до сих пор должно быть написано OKButton, щелкните на стрелке в правом конце поля. В результате откроется сисок объектов, имеющих отношение к форме (рис.13.);

Из раскрывающего списка выберите  UserForm.

VBA создаст новую процедуру для события Click (щелчок). Эта процедура будет вызвана, если пользователь вашей программы щелкнет кнопкой мыши в любом месте формы, где нет элементов управления. Нам пока эта процедура не нужна.

Теперь щелкните на стрелке в конце текстового поля, находящегося справа, чтобы открыть список процедур, в котором будут перечислены все события, которые VBA распознает в связи с объектом UserForm;

Выберите Activate – самый первый пункт в списке. VBA создаст заготовку процедуры UserForm_Activate.

Можете удалить пустую процедуру UserForm_Click, выделив весь её текст и нажав клавишу <Del>.

Внутри заготовки процедуры наберите следующие три строки:

первая строка

Dim St As String

Создает переменную с именем St и определяет её как строковую.

Вторая строка

St=”Сегодня:”

Сохраняет текст “Сегодня” в созданной переменной St.

Третья строка

LblNow.Caption = St & Format(Now,   “dddddd,   hh ч.  mm  мин.”)

Содержит программный код, который непосредственно отображает нужное сообщение в форме. Эта строка начинается с идентификации элемента управления с именем lblNow как объекта с которым идет работа. После имени идет точка, означающая, что далее идет свойство объекта lblNow – в данном случае это свойство Caption. Оставшаяся часть строки определяет сообщение, которое должно появиться на экране в виде надписи.

Теперь программа готова к запуску. Чтобы запустить программы из редактора VBA, выполните следующее:

Щелкните либо в окне форме, либо в окне программного кода, чтобы соответствующее окно стало активным;

Нажмите <F5>.

Описанные методы работают только в редакторе VBA. Чтобы вывести форму на экран из VBA-приложения, нужно с помощью редактора Visual Basic создать процедуру типа Sub, которая вызывает форму, а затем выполнить эту процедуру в приложении. Для этого сделайте следующее:

В редакторе VBA выберите InsertModule, чтобы создать модуль и открвть окно его программного кода.

Напечатайте следующие строки программного кода:

Sub ShowSt()

        UserForm1.Show

End Sub

Если ваша форма названа не UserForm1, а как-то иначе, замените это имя во второй строке на правильное.

Задание 1

Написать программу в VBA c использованием форм. На форме должны присутствовать окна для ввода данных и вывода данных, кнопки ОК и Отмена. Вызов программы организовать с листа Excel. Организовать проверку на правильность ввода данных. Вычисленное значение выводить с точностью до двух знаков после запятой.

Рис.7. Окно VBA

кнопка

конструктор

Рис.8. Элементы управления

Рис.9. Окно свойств

Рис.10. Оформление решения

Панель элементов управления

Кнопка включения панели инструментов

Рис.11. Новая форма

Рис.12. Окно свойств

Свойство – Name (Имя)

Это имя будет использовано при создании программного кода

Свойство – Caption (Заголовок)

Рис.13. Окно программного кода

Список объектов, имеющих отношение к форме

Список событий, связанный с выбранным объектом