Часто используемые встроенные функции Visual Basic

Категория функций	Примеры функций
Математические	ABS() - абсолютное значение числа
RND() - случайное число
INT() - целая часть числа
SQR() - квадратный корень
Тригонометрические функции, логарифмы и др.
Строковые	LCASE() – преобразование строки в строчные буквы
UCASE() – преобразование строки в заглавные буквы
LEN() - определение длины строки и др.
INSTR() – позиция первой встречи одной строки внутри другой
Даты и времени	DATE() - текущая дата
TIME() - текущее время
NOW() - текущая дата и время
DAY() - номер дня года и др.
Преобразования типов данных	CINT() - действительного числа в целое (с округлением)
CSTR() - числа в строку символов
CVAR() - преобразование в тип данных VARIANT
FORMAT()- форматирование данных, формирование строк
VAL() - преобразование строки символов в число и др.

 

Кроме модулей, которые автоматически создаются при размещении элементов управления на листах книги Excel и на формах, можно создавать дополнительные модули с помощью пункта меню Вставка/Модуль в окне Visual Basic. Такой модуль удобно использовать в крупных проектах для размещения общих процедур и глобальных (доступных всем процедурам проекта) переменных.

Второе замечание относится к защите модуля от доступа к нему из других рабочих книг. Для этой цели в начале модуля указывают: Option Private Module. Такая защита полезна во избежание недоразумений, связанных с непредвиденной реакцией некоторых процедур на события в других, одновременно используемых рабочих книгах Excel.

 

10.4. Работа с переменными, массивами, константами и со свойствами объектов

Приложение, созданное средствами VBA и встроенное в рабочую книгу Excel, хранит исходные, промежуточные данные и результаты их обработки в таблицах и отдельных ячейках на листах книги Excel, в переменных, в массивах переменных и в виде констант, а также в виде значений свойств объектов – элементов управления. Кроме того, VBA предоставляет средства для чтения данных из файлов и для записи в файлы, но этот вопрос мы оставим читателю для самостоятельного изучения [4, 11, 15].

Для работы с данными используются инструкции языка программирования, процедуры и функции, но все эти средства рассчитаны на работу с данными определенных типов. Предварительное представление о типах данных было уже дано в разделе 3. Теперь подробнее рассмотрим встроенные типы данных VBA (Таблица 10.2). Возможности конструирования в приложении собственных типов данных поясним позже.

 

Таблица 10.2

Встроенные типы данных VBA

 

Тип данных	Размер в байтах	Разрядность (цифр)	Диапазон, пояснение
Boolean Byte Integer Long Single   Double Currency   Date String Object   Variant	1+L	≤ 3 ≤ 5 ≤ 10 ≤ 7   ≤ 15 ≤ 19	True, False - логические значения 0, 255 - целые положительные числа -32768, 32767 – целые числа -2147483648, 2147483647 – длинные целые -3.402823^.10^38, -1.401298^.10^-45– отрицательные числа 1.401298^.10^-45, 3.402823^.10^38– положительные числа Действительные числа с двойной точностью Действительные числа с 4 знаками после десятичной точки. Используются для точных денежных расчетов 01.01.0100, 31.12.9999 - даты Строка из L символов; L≤ 65535 Объект – фактически, ссылка на него, т.е. адрес его размещения в оперативной памяти Может принимать любой тип данных, т.е. настройка на тип операнда происходит во время исполнения инструкций или процедур

 

Когда при разработке приложения возникает необходимость в переменной для хранения данных, ее описывают в модуле перед процедурами или в конкретной процедуре. В первом случае переменная будет доступна всем процедурам модуля (как контейнер общего пользования), а если в ее описании применено ключевое слово PUBLIC, то переменную смогут использовать даже процедуры других модулей. Такую переменную называют глобальной. В ней удобно размещать данные, требуемые многим процедурам. Переменная, описанная внутри процедуры, называется локальной – она используется только в этой процедуре, а в других могут быть переменные с таким же именем. Инструкция, содержащая описание переменной, имеет вид:

Dim ИмяПеременной As ТипДанных

Для определения глобальной переменной вместо Dim пишут Public. Пример описания переменных:

Pablic Nprof, X0 As Single, Y0 As Single

Dim I As Integer, J As Integer, Date1 As Date, Date2 As Date, ФИО As String*32

Nprf=40

При описании переменной ФИО, кроме ее имени и типа, указана длина в байтах. Длину назначают для строковых переменных. Если длину не задать, то будет считаться, что строка имеет переменную длину. Такой подход менее экономичен. Для переменной Nprof тип вообще не указан – по умолчанию ей будет назначен тип Variant – это удобно для программиста, но также не экономично: число в формате Variant занимает 16 байт, а строка – 22 байта плюс по 1 байту на каждый символ. Переменная Nprf вообще не определена – сразу используется в инструкции присваивания. Она также имеет тип Variant по умолчанию. Такой стиль “ввода в действие” переменных довольно опасен. Если Nprf – это опечатка, а надо было написать Nprof, то, возможно, что эту ошибку будет нелегко обнаружить. Поэтому VBA позволяет наложить требование на явное описание всех переменных в модуле. Для этого в описательную часть модуля (до процедур) надо включить инструкцию Option Explicit. Если теперь попытаться использовать в тексте процедур необъявленные переменные, VBA сразу обнаружит ошибки.

Имена переменных должны начинаться с буквы и могут иметь любую разумную длину – не больше 255 символов. Имена не должны содержать пробелов и специальных символов:

! # $ % & @

Иногда имена записывают так: Номер_строки или НомерСтроки. Применение полных имен особенно полезно для глобальных переменных, которые хранят параметры моделируемых объектов и явлений. В компактных процедурах удобнее пользоваться переменными, применяемыми в математике и физике, например: X – координата, M –число строк в матрице или таблице, i – номер очередной строки, N – число столбцов, j – номер текущего столбца, V – скорость, S – путь и т.п. При использовании кратких имен их поясняют в комментариях – см. текст процедуры Mrows в подразделе 10.3.

Кроме возможности явного задания типа переменной, VBA позволяет указать тип переменной неявно, добавив к ее имени символ-указатель:

$ - для типа данных String, например, ФИО$=”Иванов И.И.”;

% - для данных типа Integer, например, i%=1;

& - для данных типа Long, например, CX&=6510200;

! - для данных типа Single, например, x!=107.315;

# - для данных типа Double, например, Pi#=3.14159;

@ - для данных типа Currency, например, SS@=67920.1252

Такой подход вряд ли стоит рекомендовать, но иногда он может способствовать наглядности.

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

Dim V(1 to 200) As Single, T(1 to 200) As Single, i As Integer

Так можно описать массивы измеренных значений скорости и времени для примера в разделе 4. При вычислениях i-ый элемент массива указывают с помощью индекса:

S=S+(V(i)+ V(i+1))*0.5*(T(i+1)-T(i))

Так можно записать блок 2, изображенный на рис. 4.1.

Если при описании массива размерность задать одним числом, например, V(200), то будет считаться, что нумерация элементов массива начинается с 0 и оканчивается числом 200. Кроме одномерных массивов, используются многомерные. Предположим, что на листе Excel в первом столбце размещены идентификаторы строк, например, Y1,Y2,…,YI,…,YM, а в первой строке – идентификаторы столбцов: X1,X2,…,XJ,…,XN. На пересечении I-ой строки и J-го столбца находится элемент aIJ матрицы (Рис.10.5).

В этом примере матрица представляет собой таблицу коэффициентов, позволяющих связать любой YI уравнением со значениями X1,…,XN:

YI=aI1*X1+aI2*X2+…+aIJ*XJ+…+aIN*XN

Другими словами мы имеем матричную запись системы линейных уравнений. Пусть для последующей работы с матрицей требуется определить M, N, разместить идентификаторы YI (I=1,…,M) и XJ (J=1,…N) в массивах CY, CX, а элементы матрицы в двумерном массиве A(I,J).

Этот пример иллюстрирует часто встречающуюся ситуацию: в момент составления программы обычно не известно количество элементов массива – оно определяется программой во время исполнения. Если заранее можно оценить максимальную длину массива, то его часто резервируют с расчетом на максимальную размерность. Так, в нашем случае можно принять ограничение: разрабатываемая программа предназначается для работы с матрицами, у которых число строк и столбцов не превышает 40. Тогда инструкция для резервирования массивов будет иметь вид:

 

Dim CY(1 to 40), CX(1 to 40), A(1 to 40,1 to 40) As Double

 

 

Но Visual Basic предоставляет и другую возможность – динамического переопределения размерности массивов. Рассмотрим следующий модуль:

Option Explicit

Dim CY(), CX(), A() As Double

Dim I As Integer, J As Integer, M As Integer, N As Integer

Private Sub CommandButton1_Click()

MNTab 'Определение M, N - размерности матрицы

ReDim CY(1 To M)

ReDim CX(1 To N) 'Изменение размерности массивов

ReDim A(1 To M, 1 To N)

TabCXCY ' Копирование идентификаторов столбцов и строк в массивы CX, CY

TabA ' Копирование матрицы в массив A

End Sub

Private Sub MNTab()

I = 2: Do Until IsEmpty(Cells(I, 2))

I = I + 1

Loop

M = I - 2 'M - число строк в таблице

J = 2: Do While Not IsEmpty(Cells(2, J))

J = J + 1

Loop

N = J - 2 'N - число столбцов в таблице

End Sub

Private Sub TabCXCY()

For J = 1 To N

CX(J) = Cells(1, J + 1).Value 'Идентификаторы столбцов

Next J

For I = 1 To M

CY(I) = Cells(I + 1, 1).Value 'Идентификаторы строк

Next I

End Sub

Private Sub TabA()

For I = 1 To M

For J = 1 To N

A(I, J) = Cells(I + 1, J + 1).Value

Next J

Next I

End Sub

 

Этот модуль содержит событийную процедуру CommandButton1_Click, решающую поставленную выше задачу при нажатии кнопки на листе Excel (на рис. 10.5 показана только сама матрица, причем символически), и общие процедуры: MNTab – определение числа строк и столбцов матрицы (M и N); TabCXCY – копирование идентификаторов столбцов и строк в массивы CX, CY; TabA – копирование матрицы из таблицы на листе Excel в двумерный массив A.

Массивы CX, CY, A и основные переменные, требуемые для работы с матрицей, определены на уровне модуля, т.е. доступны всем его процедурам. Элементы массивов CX и CY имеют тип VARIANT, т.к. для них тип явно не указан. Выбор типа VARIANT для этих массивов обусловлен тем, что при последующем расширении написанного модуля в этих массивах, возможно, потребуется размещать не имена строк и столбцов, а сами значения YI и XJ. Обратим внимание на то, что все три массива в начале модуля определены без указания размерности, т.е. как динамические, а в процедуре CommandButton1_Click наши массивы переопределены – но только после определения M и N с помощью процедуры MNTab. Приведенные процедуры еще пригодятся для пояснения циклов и как пример для самостоятельной разработки процедур в практических работах.

Завершая рассмотрение массивов, заметим, что в математике аналогом программисткого термина одномерныймассив является понятие вектор. Вектор можно и нужно толковать не только геометрически: в общем случае это набор значений, например, свойств (признаков) объекта. Если представить себе многомерную систему координат, где по каждой оси отложено значение одного из свойств объекта (скажем, длина автомобиля, расход горючего, максимальная скорость и т.д.), то каждый объект в такой системе координат будет точкой – точкой в признаковом пространстве. Совокупность значений отдельных координат (в нашем примере – длины автомобиля, расхода горючего, максимальной скорости и др.) и есть вектор. Понятие вектора очень удобно для краткой записи операций с массивами чисел – см. самостоятельную работу “Операции с матрицей” в конце книги. Кстати, математический термин матрица соответствует двумерному массиву. Матрицу можно рассматривать как совокупность векторов - строк или столбцов.

Теперь рассмотрим конструирование собственных типов данных. Например, для хранения записей о книгах определим в начале модуля тип данных RecBook с помощью инструкции:

 

Type RecBook

Название As String*50

Автор As String*20

Издательство As String*16

Год As Integer

End Type

Теперь в этом же модуле или в одной из его процедур зарезервируем массив для хранения записей о книгах:

Dim Books(500) As RecBook

Для доступа к полям Издательство и Год в i-ой записи массива воспользуемся инструкциями:

Books(i).Издательство=”Недра”: Books(i).Год=1999

В этой книге мы намеренно пользуемся примерами, а не строгими определениями синтаксиса инструкций VBA. Как уже отмечалось, такие определения легко получить в справочной системе Visual Basic – когда программист тонет, он кричит <F1>!

Кроме переменных, в программах используются данные, значения которых не изменяются – это константы. Приведем примеры их определения:

Const Pi As Double = 3.14159

Const Nmin As Long = 0

Const Nmax As Long = 999999

Определив константы один раз, например, в начале модуля, ими можно пользоваться во многих инструкциях, не вспоминая каждый раз точное значение числа π. Если же в процессе разработки программы потребуется изменить ограничение Nmax, то это надо будет сделать в единственной инструкции.

Некоторые данные во время работы приложения могут храниться в качестве свойств объектов: свойств ячеек листа Excel, свойств элементов управления и др. Приведем примеры:

Лист2.Cells(2,6).Formula = “=Sum(B1:B5)”

Лист2.Range("A1:D4").Value = Range("A1:D4").Value

TextBox1.Text = “M=” & Str(M) & “ N=” & Str(N)

В первом примере свойству Formula объекта Cells(2,6), принадлежащего, в свою очередь, объекту Лист2, присваивается конкретная формула. Этот пример, кстати, показывает, как при программировании на VBA можно пользоваться встроенными в Excel формулами. Но верно и обратное: разрабатывая функции на VBA, можно расширить набор формул, доступных в Excel. Во втором примере значения, хранимые в прямоугольной области ячеек на текущем листе, присваиваются ячейкам одноименной области на листе 2. В третьем примере с помощью операции конкатанации (&) три подстроки объединяются (суммируются) в одну строку, и эта строка присваивается свойству Text объекта TextBox1. Последний пример показывает, как значения M, N вывести в поле, размещенное на форме или на листе Excel.

 

 

10.5. Основные инструкции языка Visual Basic и отладка программ

 

Сгруппируем основные инструкции языка Visual Basic по типу операций:

- присваивания, в том числе с выполнением математических операций в правой части инструкции;

- проверки условий, управления последовательностью исполнения инструкций, организации циклов;

- инструкции для работы с файлами.

Последняя группа инструкций в этой краткой книге не рассматривается. При необходимости с ними можно познакомиться самостоятельно [4,11,13,15].

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

Арифметические операции перечислены в таблице 10.3. При программировании формул необходимо учитывать приоритеты операций и в случае необходимости использовать скобки, как это принято в математике. Поясним таблицу 10.3. Смена знака имеет место, когда минус ставится перед именем переменной, константы или перед числом. Например: F=CX*(-G^2); если CX=3, а G=1.5, то F = -6.75. Чтобы убедиться в правильности результата, достаточно на листе Excel разместить кнопку CommandButton1 и поле TextBox1, а в событийную процедуру CommandButton1_Click включить инструкции:

 

Dim I As Integer, F As Single

F = 3 * (-1.5 ^ 2): I = 3 * (-1.5 ^ 2)

TextBox1.Text = "F=" & Format(F, "0.00") & " I=" & Str(I)

 

Выйдя из режима конструктора и нажав кнопку, увидим в поле TextBox1 результаты.

 

 

Таблица 10.3

Арифметические операции

Операция	Наименование	Приоритет	Примеры	Результаты
I – Целое число	F – действитель- ное число	I	F
^	Возведение в степень		I = 1.5^2	F = 1.5^2		2.25
-	Смена знака		I = -6.75	F = -6.75	-7	-6.75
*	Умножение		I = 1.3*4	F = 1.3*4		5.20
/	Деление		I = 10/3	F = 10/3		3.33
\	Деление нацело		I = 10\3	F = 10\3		3.00
Mod	Остаток от деления		I = 10 Mod 3	F = 10 Mod 3		1.00
+	Сложение		I = 12 + 7	F = 11.45+7.55		19.00
-	Вычитание		I = 7-12	F = 7.55-11.45	-5	-3.90

 

Все результаты примеров легко проверить, подставляя в текст приведенной только что процедуры формулы вычисления I и F. Из этих примеров должно быть понятно, что результат арифметических операций зависит также от типа переменной, которой он присваивается – ведь переменная целого типа не может хранить число с плавающей точкой, и поэтому перед записью в ячейку I результат округляется.

Приведенная выше процедура иллюстрирует и конкатанацию (&) – слияние строк. К строке “F=” прибавляется строка, содержащая отформатированное значение F (с двумя цифрами после десятичной точки). К этой объединенной строке добавляется “ I=” и далее - превращенное в последовательность символов (в строку) число I.

Операции сравнения используют для сравнения чисел, значений переменных, констант, ячеек таблиц. Результат операции сравнения имеет тип Boolean – может принимать значение True или False (Таблица 10.4).

Таблица 10.4

Операции сравнения

Операция	Наименование	Пример (R имеет тип Boolean)	Результат (R)
=	Равно	R = (10=5)	False
>	Больше	R = 10>5	True
<	Меньше	R = 10<5	False
>=	Больше или равно	R = “a” >= “b”	False
<=	Меньше или равно	R = “a” <= “b”	True
<>	Не равно	R = “a” <> “b”	True

В логических операциях не только результат, но и операнды имеют тип Boolean. Поясним назначение таких операций на простом примере. Пусть переменная типа Boolean с именем R должна принимать значение True, если точка с координатами X,Y попадает в прямоугольную область, ограниченную координатами X1,X2 и Y1,Y2. Если точка за пределами прямоугольника, то R=False. Применив логическую операцию AND (И), вычислим значение R:

R = (X>=X1) AND (X<=X2) AND (Y>=Y1) AND (Y<=Y2)

Смысл этой операции в том, что если и первое выражение (X больше или равно X1) истинно, и второе истинно, и третье истинно, и четвертое истинно, то R=True. Если один из операндов логической операции AND представляет собой ложное выражение (утверждение), то и результатом будет False.

Кроме операции AND, к числу часто используемых логических операций относятся OR и NOT. Первая из них дает результат True, если истинно хотя бы одно выражение, являющееся операндом. Операция NOT изменяет результат на противоположный, подобно смене знака в арифметическом выражении. Вычислим R с помощью этих операций:

R = NOT ((X<X1) OR (X>X2) OR (Y<Y1) OR (Y>Y2))

Выражение, стоящее в скобках после операции NOT, истинно (True), если точка или левее, или правее, или ниже, или выше воображаемого прямоугольника, т.е. если точка в него не попала. В этом случае R будет иметь значение False благодаря логическому отрицанию NOT.

Несколько реже используются логические операции XOR (исключающее ИЛИ), EQV (эквивалентность) и IMP (импликация). Первая из них дает результат True, если только одно из выражений-операндов имеет результат True. Результатом операции EQV будет True, если выражения-операнды дают одинаковый результат. Результатом IMP будет True, если выражения-операнды дают разные результаты.

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

Инструкция IF (ЕСЛИ) используется в двух нотациях: в виде простой и блочной структур. Инструкция простой структуры имеет следующую форму записи:

If <Выражение> Then <Инструкция, исполняемая, если выражение-условие истинно>

Конструкция блочной структуры может включать один блок инструкций (<Блок 1>), исполняемых, если выражение 1 истинно, или же еще дополнительные условия, сопровождаемые блоками инструкций:

 

If <Выражение1> Then

<Блок 1 – исполняется, если выражение 1 истинно>

ElseIf <Выражение2> Then

<Блок 2 – исполняется, если выражение 2 истинно >

ElseIf <Выражение3> Then

<Блок 3 – исполняется, если выражение 3 истинно >

…………………..

Else

<Блок инструкций, исполняемых, если ложны выражения в инструкциях If и ElseIf>

End If

 

Приведем пример простой инструкции If:

If R Then MsgBox “Точка попала в прямоугольник”

Теперь пример блочной структуры:

R = False

If X < X1 Then

GoTo 10 ’уже ясно, что точка не попала в прямоугольник

ElseIf X > X2 Then

GoTo 10 ‘управление передается на метку 10

ElseIf Y < Y1 Then

GoTo 10

ElseIf Y > Y2 Then

GoTo 10

Else

R = True

End If

10: MsgBox R ’после метки ставится двоеточие

 

Блоки ElseIf и (или) Else могут отсутствовать. В любом случае блочная конструкция оканчивается инструкцией End If.

Приведенный пример, кроме блочной конструкции IF, поясняет возможность использования в программе меток и передач управления с помощью инструкции GoTo. Метка не обязательно обозначается числом – можно было написать и N10 или MsgR.

В ситуациях, когда требуется запрограммировать три и более разветвлений в зависимости от значения одной переменной, удобно использовать структуру Select Case:

Select Case <Переменная>

Case <Значение 1> либо Case is <Условие 1>

<Блок 1 – выполняется, если значение переменной равно значению 1

или соответствует условию 1>

Case <Значение 2> либо Case is <Условие 2>

<Блок 2 – выполняется, если значение переменной равно значению 2

или соответствует условию 2>

………………….

End Select

Приведем пример:

Возраст = TextBox1.Value

Select Case Возраст

Case Is < 13

Label1.Caption = "После 22-00 спать!"

Case 16

Label1.Caption = "Пора получать паспорт"

Case Is < 18

Label1.Caption = "Только чай!"

Case Is >= 18

Label1.Caption = "Можно почти все!"

End Select

 

Теперь рассмотрим организацию циклов. Вообще говоря, цикл нетрудно организовать и без специальных инструкций по схеме:

1) инструкции присваивания начальных значений переменной цикла и другим переменным;

2) инструкции, исполняемые при очередном значении переменной цикла;

3) наращивание (изменение) переменной цикла и, возможно, других переменных, изменяемых одновременно с переменной цикла;

4) проверка переменной цикла на соответствие условию, при котором цикл должен исполняться повторно, и передача управления блоку 2 при выполнении этого условия (IF <условие> GoTo <метка в начале блока 2>).

Именно такой, классический цикл изображен на рис. 4.1 в виде блок-схемы. Но в языках высокого уровня, и в том числе в VBA, предусмотрены инструкции, позволяющие записывать циклы еще компактнее. Проще всего цикл записывается, если известны начальное, конечное значения переменной цикла и шаг ее изменения. Пусть в J-ом столбце на листе Excel надо суммировать четные ячейки до строки 20, но только пока не встретится число 7777:

J = 1: S = 0

For I = 2 To 20 Step 2

F = Cells(I, J).Value

If F = 7777 Then Exit For 'Выход из цикла - на инструкцию после Next

S = S + F

Next I

MsgBox "i= " & Str(I-2) & " S=" & Str(S)

После выхода из цикла сообщается номер последней ячейки, значение которой добавлено к S и сама накопленная сумма. Цикл этого примера называют циклом типа For-Next.

В большинстве случаев циклы этого типа выглядят проще, т.к. обычно не требуется задавать шаг изменения переменной цикла (Step) и не нужно выходить из цикла по дополнительному условию (Exit For). В модуле, приведенном после рис. 10.5 (см. подраздел 10.4), такие циклы используются в процедурах TabCXCY и TabA. В последней процедуре – вложенный (двойной) цикл.

В тех случаях, когда нет возможности указать начальное и конечное значения переменной цикла, используют циклы типа Do-Loop. Для таких циклов возможны 4 варианта записи:

 

условие True в начале цикла Do While <условие> <инструкции, исполняемые в цикле> Loop	условие False в начале цикла Do Until <условие> <инструкции, исполняемые в цикле> Loop
условие True в конце цикла Do <инструкции, исполняемые в цикле> Loop While <условие>	условие False в конце цикла Do <инструкции, исполняемые в цикле> Loop Until <условие>

 

Циклы такого типа уже неоднократно применялись в примерах процедур в подразделах 10.3, 10.4. В число инструкций, исполняемых в цикле, может быть включена инструкция Exit Do, если из цикла надо выйти “досрочно”, например, при выполнении какого-нибудь дополнительного условия. Заметим, что Do While означает Выполнять пока … Do Until означает Выполнять пока не … Другими словами, вместо Do Until можно написать Do While Not. В некоторых случаях эти проверки удобнее ставить в конец цикла (Loop – цикл, дословно – петля).

В заключительной части этого раздела приведем рекомендации по отладке приложений.

Во-первых, для отладки надо подбирать разнообразные примеры, не ограничиваясь самыми простыми – ведь многие ошибки проявляются только при определенных значениях исходных данных, а иногда даже только при определенных сочетаниях этих значений.

Во-вторых, для просмотра промежуточных и окончательных значений переменных можно на время отладки включать в тексты процедур обращения к процедуре MsgBox, а также выводить значения переменных в виде свойства Caption элемента управления Label (метка – статический текст) или в виде свойства Text элемента управления TextBox (поле с редактируемым текстом). Важную роль играет выбор точек процедуры для вывода значений переменных при отладке. Для выбора таких точек можно рекомендовать рассматривать процедуру состоящей из логически завершенных фрагментов программного кода, формирующих определенные «информационные сцены». Эти-то сцены, характеризующиеся содержимым переменных и массивов, и надо контролировать.

В-третьих, VBA предоставляет специальные средства для отладки процедур. Если в окне редактора слева от текста инструкции (на полях) щелкнуть мышью, то создастся точка останова. При этом инструкция выделится красным цветом. Теперь переключаемся через панель задач на окно Excel и запускаем процедуру (обычно – командной кнопкой). В точке останова выполнение программы приостановится, причем произойдет переключение на окно редактора Visual Basic, а очередная инструкция будет выделена желтым цветом. Теперь можно навести мышь на любую переменную (на ее имя в любой инструкции), и появится окошечко со значением этой переменной в момент останова. Далее можно установить курсор в следующую представляющую интерес позицию модуля и нажать <Ctrl>+<F8> - программа выполнится до этой позиции и снова приостановится. Можно снова “посмотреть” значения переменных, наводя на них мышь. Есть также возможность перейти к пошаговому исполнению инструкций, пользуясь клавишей <F8> или комбинациями клавиш <Shift>+<F8> (шаг с обходом вызываемой процедуры) и <Shift>+<Ctrl>+<F8> (шаг с выходом из процедуры – см. также пункт меню Отладка).

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

 

Контрольные вопросы к разделу 10: что такое программирование; особенности и достоинства VBA; создание интерфейса пользователя, как разместить элементы управления на листе Excel, как установить их свойства, как перейти в окно редактора VBA, как создать диалоговое окно; что такое модуль, процедура, функция; как оформляются и вызываются процедуры; как оформляются и вызываются функции; категории встроенных функций; встроенные типы данных VBA; назначение переменных, их описание, локальные и глобальные переменные; назначение инструкции Option Explicit; массивы, их описание, доступ к элементам массива, многомерные массивы, динамическое переопределение размерности массивов; пояснить связь математических понятий вектора и матрицы с понятиями одномерного и двумерного массивов в программировании; конструирование собственных типов данных; константы, их определение и использование; доступ к данным, хранимым в виде свойств объектов-элементов управления; основные группы инструкций языка Visual Basic; инструкция присваивания; арифметические операции; конкатанация; операции сравнения; логические операции; инструкции проверки условий и организации циклов; методы отладки приложений.

11. Самостоятельные работы

Самостоятельные работы ориентированы на создание базы данных Access и приложений VBA. Эти работы должны восполнить недостаток часов, отводимых на лабораторные работы при изучении баз данных и основ программирования.

 

 

11.1. База данных “Архив предприятия”

1. Использовать систему управления базами данных (СУБД) Access. Создать новую базу данных в своем рабочем каталоге, присвоив ей имя. В примере, который рассматривается далее, файлу базы данных присвоено имя Архив.mdb.

2. База данных должна содержать несколько таблиц: основную и дополняющие ее таблицы. Пусть основная таблица – Документы (ее состав виден на схеме данных – см. рис. База данных “Архив”, поясняющий задание). В этой таблице фиксируются все документы, поступающие в архив предприятия. Каждому документу присваивается архивный номер и номер экземпляра. В таблице указывается количество экземпляров документа, а номер конкретного экземпляра заносится в таблицу Журнал при его выдаче абоненту. Каждая запись основной таблицы содержит также код типа документа, код объекта, к которому относится документ, и код организации-автора документа. Эти коды являются ключевыми полями в следующих дополняющих таблицах: Типы_док, Объекты, и Организации. В таблице Журнал фиксируются даты выдачи документов абонентам и даты возврата. Таблица Абоненты дополняет таблицу Журнал. Далее приведем рекомендации по созданию таблиц, схемы данных, форм, запросов и отчетов.

3. Для создания таблиц используйте кнопку Создать и в следующем диалоговом окне – кнопку Конструктор. Конструктор позволяет ввести сведения о каждом поле создаваемой таблицы - см. подраздел 9.3. Ключевые поля, идентифицирующие записи в каждой таблице, на схеме данных изображены жирным шрифтом. Чтобы сделать поле ключевым, надо воспользоваться правой клавишей мыши или кнопкой с изображением ключа на панели инструментов. После ввода параметров полей таблицу надо закрыть и при этом сохранить, дав ей имя.

4. Для создания схемы данных воспользуйтесь одноименной кнопкой панели инструментов. Важно, чтобы на схеме были не только представлены таблицы базы данных, но и установлены связи между ними – см. подраздел 9.3. Например, чтобы связать номер абонента в таблице Журнал с номером абонента в таблице Абоненты, надо как-бы перетащить мышкой это поле из первой таблицы во вторую – на схеме появится стрелка. Установить связи на схеме данных надо до создания форм, запросов и отчетов, чтобы получить возможность включать в них поля из всех имеющихся таблиц.

5. Для создания формы выберите Формы в меню базы данных, нажмите мышью кнопку Создать и далее выберите из списка Мастера форм. Форма отличается от таблицы тем, что она отображает одновременно только одну запись, но в удобном для обзора и ввода данных виде - см. подраздел 9.3. Создайте формы «Документы» и «Журнал» - последняя облегчит ввод данных в одноименную таблицу.

6. Для создания запроса выберите Запрос в меню базы данных, нажмите кнопку Создать, выберите из списка Конструктор, далее укажите используемые таблицы и поля. Например, можно создать запрос по таблицам Журнал и Абоненты, чтобы определить, кому выданы экземпляры документа с архивным номером 1001. В этом случае для поля Ном арх надо задать условие (=1001). Сохранив запрос под именем «Журнал1001», выберите Отчеты в меню базы данных, нажмите мышью кнопку Создать и далее выберите из списка Мастера отчетов. Для построения отчета в качестве источника данных укажите запрос «Журнал1001».

Рис. 11.1. База данных “Архив”

В окне базы данных перечислены таблицы. На схеме данных показан состав таблиц и связи между ними. Справа изображена форма “Документы”, созданная для удобства ввода данных в таблицу “Документы”. Справа, внизу – одна из таблиц: таблица “Типы документов”.

Подобно базе данных «Архив» можно создавать разнообразные базы данных: книги в домашней библиотеке, музыкальные записи, семейные расходы и т.п.

11.2. Разработка приложения “Решение системы линейных уравнений”

1. Взять за основу модуль, приведенный после рис. 10.5. Модуль должен содержать следующие общие процедуры:

MNTab - Определение M, N - размерности матрицы,

TabCXCY - Копирование идентификаторов столбцов и строк в массивы CX, CY,

TabA - Копирование матрицы с листа Excel в массив A,

XAI (I) - вычисление скалярного произведения вектора x на вектор aI (функция) с записью результата в I-ю ячейку первого столбца на листе Excel,

Jordan (r) - Шаг жорданова исключения (процедура поясняется ниже) – преобразование всей матрицы A с записью новых значений ее элементов в таблицу на листе Excel, обращение к процедуре TabA, перемена местами элементов CX(r) и CY(r) с записью значений и в ячейки Excel, копирование матрицы с листа Excel в массив A.

2. Разместить на листе Excel командную кнопку BTN1 с надписью (свойство Caption) Решение системы уравнений.

3. В событийную процедуру BTN1_Click включить:

- определение M, N, вывод сообщения и завершение работы, если M не равно N;

- переопределение размерности массивов CX, CY, A и копирование в них содержимого соответствующих ячеек листа Excel;

- выполнение шага жорданова исключения N раз (r – номер диагонального элемента матрицы, называемого разрешающим; с ним выполняется шаг жорданова исключения); перед обращением к процедуре Jordan (r) надо проверять, не равен ли нулю элемент матрицы A(r,r) – этот фрагмент программы можно записать так:

For r = 1 To N

If Abs(A(r, r)) < 0.000000001 Then

MsgBox "Матрица содержит линейно зависимые строки (столбцы)": Exit Sub

End If

Jordan r

Next r

(проверка на нуль в программах выполняется с учетом точности вычислений);

- вычисление каждого значения Xi (i=1,…,N) путем умножения вектора y на вектор Ai с использованием функции XAI; заметим, что после того, как были выполнены N шагов жордановых исключений, на месте матрицы A находится обратная матрица