Задачник на VBA (часть I)*
.pdfМИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ
Государственное образовательное учреждение высшего профессионального образования
«КАЗАНСКИЙ ГОСУДАРСТВЕННЫЙ ЭНЕРГЕТИЧЕСКИЙ УНИВЕРСИТЕТ»
Н.К. ПЕТРОВА, М.М. ВОЛЧЕНКО
ПРОГРАММИРОВАНИЕ НА VBA В ПРИМЕРАХ И ЗАДАЧАХ
ЧАСТЬ I. БАЗОВЫЕ АЛГОРИТМИЧЕСКИЕ СТРУКТУРЫ
Практикум к лабораторным работам, практическим занятиям, расчетному заданию и самостоятельной работе студентов по дисциплинам
«Информатика», «Компьютерные технологии в науке и образовании», «Программные средства информатики», «Новые информационные технологии»
Казань 2010
УДК 681.3 ББК 32.973 П78
Рецензенты:
кандидат физико-математических наук, доцент Татарского государственного педагогического университета И.Н. Голицына;
кандидат физико-математических наук, доцент Казанского государственного энергетического университета Р.А. Ишмуратов
П78 Петрова Н.К., Волченко М.М.
Программирование на VBA. Часть I. Базовые алгоритмические структуры. Практикум / Н.К. Петрова, М.М. Волченко. – Казань: Казан. гос. энерг. ун-т, 2010. – 55 с.
Предлагается своего рода «Задачник» по программированию, ориентированный на обучение студентов основам алгоритмизации математических и инженерных задач средствами языка VBA в приложении к Excel.
Содержание I части охватывает разделы программы, посвященные базовым алгоритмическим структурам: линейные программы, «ветвление», «цикл» - арифметический и итерационный. Изложение материала оформлено в виде отдельных разделов, в которых кратко представлены основные теоретические сведения.
Типовые задачи даются с подробными решениями. Имеется большое количество задач для самостоятельной работы. Задания делятся на три типа: 1 – умение «читать» готовые программы, 2 – разрабатывать программы по шаблонному алгоритму и 3 – разрабатывать программы с неявным алгоритмом.
Практикум предназначен для студентов всех специальностей КГЭУ, изучающих «Информатику».
УДК 681.3 ББК 32.973
© Казанский государственный энергетический университет, 2010
3
Предисловие
Вучебной литературе имеется не так много пособий по практической работе на VBA, ориентированных на решение математических, инженерных задач. Данный практикум разработан с целью восполнить существующий пробел. Он предназначен для обучения – самостоятельно или под руководством преподавателя – основам алгоритмизации с использованием языка VBA (Visual Basic for Application) в приложении к одному из самых популярных приложений MS Office – MS Excel.
Впрактикум включены типовые задачи и даются методы и примеры их решения. Каждому разделу предшествует краткое введение, состоящее из определений и описания операторов языка. Многочисленные примеры демонстрируют разные возможности работы с ячейками рабочего листа Excel, с диалоговыми окнами ввода и вывода. Основной акцент сделан на умение работать с математическими формулами, с числами в формате с плавающей и фиксированной точкой, грамотно сопоставлять фактические и формальные параметры при вызове программ как с листа Excel, так и из программ пользователя.
Втексте практикума приняты такие соглашения:
1.Элементы языка VBA в текстовой части практикума выделены
таким стилем.
2.Курсивом выделены новые термины, имена переменных в текстовой части практикума, в комментариях к операторам программ.
3.Примеры разбираемых программ даются, как правило, с комментариями, при этом текст программы пишется курсивом. Тексты программ для самостоятельного разбора приводятся, как правило, в рамке.
4.При описании структуры операторов[необязательные операнды] взяты в квадратные скобки, альтернативные параметры – Yes | No –
написаны через вертикальную черту.
5. В тексте используются следующие сокращения:
ОП – оперативная память; ПК – компьютер; п/п – программа-процедура; п/ф – программа-функция
При подготовке данного практикума авторы использовали многолетний опыт работы по преподаванию курса информатики в Казанском государственном энергетическом университете и Казанском государственном университете.
4
Общие теоретические сведения по программированию на VBA
I. Структура программ на VBA. Процедуры и функции пользователя
Программа VBA представляет собой совокупность процедур и функций, размещенных в зависимости от особенностей решаемой задачи, в одном или нескольких модулях. Каждый модуль имеет две области: общую область и область подпрограмм. В общей области помещаются операторы описания переменных, которые являются общими для всех процедур и функций этого модуля. В области подпрограмм помещается только код подпрограммы.
В VBA программный код, реализующий какие-либо действия, оформляется в виде процедур и функций. Благодаря этому создаваемые программы имеют хорошую структурированность и наглядность. Разработанные отдельные функции или процедуры можно накапливать в библиотеках и в дальнейшем использовать их по мере необходимости.
Программа-процедура (п/п) на VBA имеет следующую структуру:
[Private|Public] Sub ИмяПроцедуры (СпискиПараметров) <<Тело п/п>>
End Sub
где [Private|Public] – необязательные ключевые слова, определяющие область видимости программы; Sub – ключевое слово, определяющее тип п/п. ИмяПроцедуры – имя п/п (дает сам пользователь). СписокПараметров служит для передачи процедуре исходных данных для вычислений (может отсутствовать). Он состоит из элементов списка, разделенных запятыми.
Этот элемент списка параметров имеет синтаксис:
ИмяЭлемента [As ТипДанных]
где ИмяЭлемента – идентификатор; As – ключевое слово; ТипДанных – тип данных элемента списка (Табл. 1.1).
Процедура пользователя может быть вызвана из другой п/п оператором Call или указанием ее имени.
5
Например: |
|
Private Sub Prog1( ) |
Начало процедуры с именем Prog( ) |
Dim t As Integer |
Описание типа переменной t |
t = 2 |
Присвоение переменной t значения 2 |
Call Prog2 (t) |
Вызов процедуры Prog2 (t) с фактическим |
[Другие операторы] |
параметром t |
Prog2 (t) |
другой способ вызова п/п Prog2 |
[Другие операторы] |
|
End Sub |
Конец процедуры Prog1 |
Public Sub Prog2(x As |
Начало п/п Prog2 с формальным параметром х |
Integer) |
|
MsgBox x |
Выдача значения x в специальном окне |
End Sub |
Конец процедуры Prog2 |
Программа–функция (п/ф) – это программа, которая выполняет действия в пределах своего блока и возвращает единственное значение. Функция пользователя имеет следующий вид:
[Private|Public]Function ИмяФункции([СпискиПараметров])[As ТипДанных]
<<Тело п/ф>>
ИмяФункции = ВозвращаемоеЗначение
End Sub
Function – ключевое слово, указывающее на то, что это функция; остальные параметры те же, что и в Sub. ВозвращаемоеЗначение – значение, возвращаемой функцией.
Обращение к п/ф может производиться из процедуры другой функции. Если в функции предусмотрено рекурсивное обращение, то ее можно вызвать из нее самой. Если функция записана в модуле, то ее можно вызвать из Excel с помощью Мастера функций, так как функция пользователя заносится в библиотеку функций.
Пример:
Private Sub Prog3( ) Dim u,t As Integer
t = 2
y = Func ( t ) [Другие операторы] End Sub
Public Function Func(x As Integer) As Integer
f % = x ^ 2 + x + 5
func = f % End Function
6
Начало вызывающей п/п Задание типа переменным u,t
Присвоение переменной t значения 2 Вызов п/ф Func с фактическим аргументом t
Конец п/п
Начало п/ф Func, имеющей целочисленный тип. Формальный аргумент t имеет целочисленный тип
Вычисление полинома по t и присвоение результата целочисленной переменной f % Присвоение возвращаемого значения Конец п/ф
При работе с обоими типами программ следует аккуратно соблюдать соответствие между фактическими и формальными параметрами, как по количеству их, так и по типу.
II. Типы констант. Представление числовых констант в формате с фиксированной и плавающей десятичной точкой
Константой называется некоторая величина, не изменяющая своего числового или символьного значения в течение выполнения всей программы. Имеются два типа константчисловые и символьные.
Числовые константы: представляют собой положительные или отрицательные числа двух видов:
а) целочисленные, представляющие собой положительные и отрицательные числа и ноль, при их записи не должна использоваться
десятичная запятая: -100 |
2 |
0 +458 ; |
|
|
|
б) вещественные константы – все действительные числа, включая и |
|||||
целые. Записываются в двух форматах: |
100. |
–0.001 |
+2.563 |
||
с фиксированной точкой, например, |
|||||
(вместо десятичной запятой используется при записи точка) |
|
||||
с плавающей точкой, например, |
2.5·10-2, записываемое как |
||||
2.5Е-02 или 2.5D-02. |
Здесь |
2.5 – |
мантисса, Е, D – |
десятичная |
экспонента одинарной (Е) или двойной (D) точности.
Например: 1,5 10-3 1.5Е-3=0.15Е-02=15Е-04=0.0015,
150000 1.5Е+05,
0,0000254 2.54Е-05.
7
III. Объявление переменных на VBA
Переменные – это объекты, предназначенные для хранения данных. В различные моменты времени переменные могут хранить разные значения. Имена переменных позволяют различать их в программе, осуществлять доступ к различным участкам памяти для записи данных и их извлечения.
Перед использованием переменных в программе их нужно объявить (декларировать). При объявлении переменной необходимо указать, что объявляется переменная, задать имя переменной и указать ее тип. Тип определяет способ представления/хранения переменной в оперативной памяти.
Для эффективного использования памяти и времени ПК необходимо правильно выбрать тип переменной. Объявить переменную – значит заранее сообщить программе о ее существовании. Объявление переменной производится специальным оператором: Dim переменная [As тип]
Одновременно с объявлением переменной после ее имени можно
записать ключевое слово |
As, после которого |
задается |
тип переменной |
(Табл. 1.1). |
Dim power As |
|
|
Например: оператор |
single |
приписывает |
переменной power вещественный тип одинарной точности.
Задать тип переменной также можно, используя специальный символ в конце имени – постфикс (см. табл. 2) – или, используя инструкцию
DefТип.
Например, если на уровне модуля дана инструкция DefInt I-N, это означает, что всем переменным, имена которых начинаются с букв, лежащих в диапазоне от I до N (и прописных, и строчных), в программах данного модуля будет присвоен тип Integer. Другие значения инструкции DefТип представлены в Табл. 1.2.
8
Таблица 1.1 Некоторые типы переменных VBA
Тип |
Хранимая |
Занимаемая |
|
Диапазон значений |
информация |
память |
|
||
|
|
|
||
|
Целочисленные типы |
|
||
Byte |
Целые числа |
1 байт |
|
От 0 до 255 |
Boolean |
Логические |
2 байт |
|
True или False |
значения |
|
|||
Integer |
Целые числа |
2 байт |
|
От -32 768 до 32 767 |
Long |
Длинное целое |
4 байт |
|
От -2 147 483 648 до 2 147 483 |
|
647 |
|||
|
|
|
|
|
|
Типы с плавающей точкой |
|||
|
|
|
|
От -3,402823E38 до |
|
Вещественные |
|
|
-1,401298E-45 |
Single |
числа с плавающей |
4 байт |
|
для отрицательных значений; |
|
точкой одинарной |
(7 цифр) |
|
от 1,401298E-45 до |
|
точности |
|
|
3,402823E38 |
|
|
|
|
для положительных значений |
|
|
|
|
От -1,79769313486232E308 |
|
Вещественные с |
|
|
до -4,94065645841247E-324 |
|
8 байт |
|
для отрицательных значений; |
|
Double |
плавающей точкой |
|
||
(15 цифр) |
|
от 4,94065645841247E-324 |
||
|
двойной точности |
|
||
|
|
|
до 1,79769313486232E308 для |
|
|
|
|
|
|
|
|
|
|
положительных значений |
|
|
Строковые типы |
|
|
|
|
|
|
|
|
Текстовая |
|
|
|
String |
информация |
10 байт + 1 байт |
|
От 0 до приблизительно |
|
(строка) переменной |
на каждый символ |
|
2 миллиардов символов |
|
длины |
|
|
|
String*n |
Строка постоянной |
Длина строки |
|
От 1 до приблизительно |
длины в n символов |
|
65 400 |
||
|
|
Типы Variant |
|
|
Variant |
Значения любого из |
16 байт для чисел; |
|
Любое числовое или |
перечисленных |
22 байт + 1 байт |
|
строковое значение вплоть до |
|
|
типов данных |
на каждый символ |
|
границ диапазона для типа |
|
строки |
|
Double. |
|
|
|
|
9
Таблица1.2. ЗначенияинструкцииDefТиписоответствующихпостфиксов
Инструкция |
Тип данных |
Постфикс |
Инструкция |
Тип данных |
Постфикс |
DefBool |
Boolean |
нет |
DefSng |
Single |
! |
DefByte |
Byte |
нет |
DefDbl |
Double |
# |
DefInt |
Integer |
% |
DefStr |
String |
$ |
DefLng |
Long |
& |
DefVar |
Variant |
нет |
IV. Значения и типы переменных по умолчанию
Если в программах модуля операторы описания типа или постфиксы в именах переменных отсутствуют, то работает принцип умолчания
(табл. 1.3), согласно которому все переменные принимают тип Variant и
соответствующие значения. Применение данного типа позволяет выполнять операции, не обращая внимания на тип данных, которые они содержат. Удобен для объявления переменных, тип которых заранее неизвестен. Переменные этого типа могут содержать специальные значения: Empty (пусто), Null (Нуль), Error (ошибка).
Таблица 1.3. Значения и типы переменных разных типов по умолчанию
Тип |
Значение |
Тип |
Значение |
Тип |
Значение |
|
Boolean |
Ложь/False |
Single |
0,0 |
String |
““ |
|
Byte |
0 |
Double |
0,0 |
String*n |
“¬ . . . ¬“ |
|
Integer |
0 |
Long |
0 |
|
n пробелов |
|
|
|
|
|
|
|
|
Variant |
Empty, Null, Error |
|
|
|
|
Из таблицы следует, что если переменная описана как числовая
(любого типа – Byte, Integer, Long, Single, Double) то ее значение по умолчанию равно 0 (ноль). Для символьных переменных типа String значение по умолчанию "" – отсутствие символов, если String*n, то ее значение будет равно n пробелам. Для логических (Boolean) переменных значение по умолчанию будет «ложь»
Несколько правил для корректного использования разных типов данных в одной программе или в одном выражении:
1. Переменные, описанные с помощью DIM на уровне модуля, доступны для всех процедур в данном модуле. Переменные, описанные на уровне п/п, доступны только в данной п/п.
10
2.В операторах присваивания следует иметь в виду, что значение выражения может быть присвоено переменной, только если оно имеет совместимый с этой переменной тип данных. Невозможно присвоить строковое выражение числовой переменной или числовое выражение строковой переменной. Такая попытка приведет к ошибке во время компиляции.
3.Переменным типа Variant могут присваиваться как строковые, так и
числовые выражения. Однако обратное не всегда верно.
4.Присвоение выражения с одним из числовых типов переменной с другим числовым типом данных преобразует значение выражения в тип данных результирующей переменной. Например: если a % = 2.33, то а % будет иметь целое значение 2 (округление с недостатком) или b % = 2.65, то b % = 3 (округление с избытком), таким образом, присвоение вещественного числа целочисленной переменной приводит к округлению его до ближайшего целого (аналогично функции INT).
5.Если присваивается численное выражение типизированной переменной с меньшей точностью (например, Double → Long), VBA
округляет значение выражения для совпадения с точностью переменной, принимающей новое значение.
6. Если переменной типа String присваивается переменная типа Variant, содержащая число, VBA автоматически преобразует это число в строку.
V. Встроенные математические функции
Таблица 1.4. Некоторые стандартные функции VBA
|
|
|
Функция |
|
Выполняемое действие |
|
|
|
Atn(аргумент) |
|
Возвращение арктангенса угла в радианах |
|
|
|
Sin (аргумент) |
|
Возвращение синуса угла, заданного в радианах |
|
|
|
Cos(аргумент) |
|
Возвращение косинуса угла, заданного в радианах |
|
|
|
Tan(аргумент) |
|
Возвращение тангенса угла, заданного в радианах |
|
|
|
Exp(аргумент) |
|
Возвращение значения ex, где x - значение переменной |
|
|
или введенное число |
|
|
|
Log(аргумент) |
|
Возвращение натурального ln(x) логарифма числа x |
|
|
|
Sqr(аргумент) |
|
Возвращение квадратного куреня числа |
|
|
|
Randomize |
|
Запуск генератора случайных чисел |
|
|
|