Этап. Разработка пользовательского интерфейса 2 страница
2 Разработка программного кода
2.1 Структура проекта VBA
Цель: Научить студентов разрабатывать в проекте VBA программный код с помощью пользовательских подпрограмм-функций и подпрограмм-процедур.
Проект представляет собой реестр файлов, которые необходимы для генерации исполняемого файла приложения. Реестр содержится в файле проекта с расширением *.xls.
Проект состоит из иерархической структуры файлов форм и модулей.
Формы служат для создания диалоговых окон приложений VBA. Редактор форм является основным инструментом визуального программирования.
Форма – это основа графического интерфейса Windows-приложения.
Модуль – это файл, содержащий программный код, не связанный ни с одной формой или элементом управления.
Модули содержат только программные инструкции, отображенные в окне «Module1(Code)». В них декларируются переменные, константы, размещаются коды пользовательских функции и процедур.
Модули создаются для каждого рабочего листа, для всей книги, для каждой пользовательской формы, класса, макроса. Различают стандартные модули и модули объектов.
Стандартные модули содержат макросы. Они добавляются в проект командой меню Вставка/Модуль.
К модулям объектов относятся модули рабочих книг, листов, форм и классов. Формы добавляются в проект командой меню Вставка/UserForm, а модули классов – командой Вставка/Модуль классов.
2.2 Пользовательские подпрограммы-функции
Функция – это специальным образом оформленный блок программного кода, который выполняет действия в пределах своего блока и возвращает значение.
Пользовательские функции строятся в стандартном модуле редактора VBA.
Процедура типа Function представляет собой группу операторов, содержащихся между оператором Function и оператором End Function в стандартном модуле. Операторы в функции обрабатывают текст, управляют входными данными или вычисляют значения. Функция вызывается в программу помещением имени функции в программный оператор с указанием требуемых аргументов (данных, обрабатываемых функцией).
Структура кода функции пользователя.
Функция состоит из трех частей: заголовка, тела функции, конца функции.
1Заголовок содержит:
- зарезервированное слово Function;
- идентификатор (имя) функции,
- заключенный в круглые скобки список аргументов и тип возвращаемого функцией значения.
2Тело функции состоит из группы операторов
3Конец функции представлен оператором End Function
Синтаксис:
[Private | Public] FunctionИмяФункции (СписокПараметров) As Тип
Тело функции (строки кода)
ИмяФункции = ВозвращаемоеЗначение
End Function
где
[Private | Public] – необязательные ключевые слова, определяющие
область видимости функции;
Function – ключевое слово, указывающее на то, что это функция;
ИмяФункции – имя функции (задается самим пользователем);
СписокПараметров – список формальных параметров, который может
отсутствовать;
As – (как) ключевое слово, предваряющее значение типа
данных;
Тип – тип данных возвращаемого значения;
ВозвращаемоеЗначение–значение, возвращаемое функцией;
End Function–ключевые слова, указывающие на окончание
блока функции.
Особенность:
1) функция вычисляет единственное результирующее значение простого типа;
2) носителем возвращаемого значения является ее имя, т.е. идентификатор Имя_функции. Поэтому в теле функции должен присутствовать хотя бы один оператор присваивания, в левой части которого стоит имя описываемой функции, причем хотя бы один оператор такого вида должен быть выполнен. Этот оператор присваивания и определяет значение, вычисляемое с помощью функции.
Пример 1.
Постановка задачи. Построить функцию пользователя, вычисляющую значение функции в заданных точках.
Технология выполнения задания:
1 Исходные данные:
a, b, x Î R
Результат: z Î R.
2 Набрать в стандартном модуле проекта следующую пользовательскую функцию:
Function z(a, b, x)
z1 = Abs(Log(x) / Log(10)) - Sqr(Abs(Cos(x) - Exp(x)))
z2 = Abs(Tan(Abs(a * x - b)) / Sin(Abs(x)) + b)
z3 = Atn(z2 / Sqr(Abs(1 - z2 ^ 2)))
z = Log(Abs(z1 * z3))
End Function
По умолчанию она попадает в раздел Определенные пользователем списка Категория окна Мастер функций.
3 Вычислить:
- перейти из окна VBA в окно MS Excel;
- выбрать ячейку А1 и ввести число 0,126;
- выбрать ячейку А2 и ввести число 0,842;
- выбрать ячейку А3 и ввести число 0,34;
- выделить ячейку А4, в которой найти значение функции;
- выбрать команду Вставка→Функция;
- в первом окне мастера функций в списке Категория найти значение Определенные пользователем;
- в списке Функция выбрать функцию zи нажать кнопку ОК;
- во втором окне Мастера функций в поле А ввести ссылку на ячейку А1, в поле В ввести ссылку на ячейку А2, в поле Х ввести ссылку на ячейку А3 и нажать кнопку ОК.
Пример 2.
Поставка задачи. Построить функцию, которая возвращает стоимость товара по его стоимости без НДС и значению ставки НДС.
Технология выполнения задания:
1 Исходные данные:
СтоимостьБезНДС, НДС Î Z
Стоимость Î Z.
2 Набрать в стандартном модуле проекта следующую пользовательскую функцию:
Function Стоимость (СтоимостьБезНДС, НДС)
Стоимость = СтоимостьБезНДС * (1 + НДС / 100)
End Function
3 Вычислить, например, стоимость товара с учетом НДС при условии, что НДС равен 25%, а стоимость товара без учета НДС равна 1000. Для этого:
- выбрать ячейку А2 и ввести число 1000 (стоимость без НДС);
- выбрать ячейку В2 и ввести число 25 (НДС);
- выделить ячейку С2, в которой найдем значение функции;
- выбрать команду Вставка→Функция;
- в первом окне мастера функций в списке Категория найти значение Определенные пользователем
- в списке Функция выбрать функцию стоимость и нажать кнопку ОК.
4 Во втором окне Мастера функций в поле СтоимостьБезНДС ввести ссылку на ячейку А2, а в поле НДС – ссылку на ячейку В2 и нажать кнопку ОК.
2.3 Пользовательские подпрограммы-процедуры
Зарезервированное слово Sub происходит от слова subroutine (подпрограмма), которое является синонимом термина процедура. Процедура представляет собой именованную часть кода, выполняющую определенные действия: вывод текста, выполнение арифметических действий, проигрывание видеофрагмента. Процедура может иметь параметры и в результате выполнения последовательности инструкций изменять их значения.
Синтаксис:
[Private | Public] SubИмяПроцедуры (СписокПараметров) As Тип
Тело процедуры (строки кода)
End Sub
2.4 Данные и их описание
2.4.1 Алфавит и лексемы языка
Алфавит VBA включает:
- прописные и строчные буквы латинского алфавита: А-Z, a-z;
- прописные и строчные буквы кириллицы: А-Я, а-я;
- цифры от 0-9;
- символ подчеркивания «_»;
- неизображаемые символы, используемые для отделения лексем друг от друга (пробел, табуляция, переход на новую строку);
- специальные символы, участвующие в построении конструкций языка:
+,-,*,/,\,^,=,>,<,[,],(,), . , : ,{,},’,@,&;
- составные символы, воспринимаемые как один символ: <=, >=, <>;
- комментарии, используемые для читаемости и понятности текста программы. Представляют собой последовательность любых символов, которая начинается со знака «’» (апостроф) или с ключевого слова Rem.
Пример.
‘ Это комментарий
Rem Это тоже комментарий
Программный код VBA представляет собой последовательность лексических единиц (лексем), записанных в соответствии с принятыми синтаксическими правилами.
Лексема – это минимальная неделимая единица текста программы, которая имеет определенный смысл для компилятора.
Различают шесть классов лексем:
- свободно выбираемые и используемые идентификаторы;
- служебные (зарезервированные) слова;
- константы;
- строки (строковые константы);
- операции (знаки операций);
- разделители (знаки пунктуации).
2.4.2 Переменные и константы
Переменная – это объект, занимаемый именованную область оперативной памяти. Содержимое этого объекта может изменяться в ходе выполнения программы.
Константа – это объект, значение которого фиксировано и не может быть изменено во время выполнения программы.
Идентификатор - это имя переменной, константы, процедуры, функции, и других объектов.
Правила написания идентификаторов:
1) в программе он должен быть уникальным, не должен совпадать с зарезервированными словами VBA;
2) он должен начинаться только с буквы;
3) он может состоять из букв, цифр и знака подчеркивания. Не допустимы - пробелы, точки, разделительных символов, знаков операций и др.спецсимволов. Прописные и строчные буквы различаются;
4) длина идентификатора может включать до 255 символов, но VBA учитывает только первый 31 символ;
5) они могут быть простыми и составными.
Пример,
N, I, Number, Номер – простые имена переменных;
ФамилияСтудента – составное имя переменной.
Соглашения по стилю идентификаторов:
- лучше использовать имена из строчных букв, в случае составных названий нужно отделять друг от друга составляющие их слова подчеркиванием или начинать новое слово с прописной буквы;
- имена из прописных букв используются для определения констант;
- название идентификатора нужно начинать со специального знака (приставки или префикса), который указывает на тип данных, связанный с этим идентификатором.
2.4.3 Объявление переменных
Переменные – это объекты, предназначенные для хранения данных. В разные моменты времени переменные могут хранить различные значения. Имена переменных позволяют их различать в программе, осуществлять доступ к различным участками памяти для записи данных и их извлечения.
Основной принцип VBA.
Каждый именованный объект (переменная, константа), перед своим использованием должен быть предварительно объявлен (декларирован).
Декларация переменных:
При объявлении переменной надо указать, что объявляется переменная:
- задать имя переменной
- указать ее тип.
Тип указывает способ представления переменной. В переменных можно хранить любые типы данных: число, строку текста, экземпляр объекта, элементы управления, базы данных.
Синтаксис:
Static | Public | Private | Dim <имя Переменной1> [As <тип данных1>], [<имя Переменной2>[As <тип данных2>]]…
где
Dim (dimension – размер) – ключевое слово, которое сообщает VBA, что декларируется переменная и резервируется область памяти для хранения;
Имя переменной – имя переменной (идентификатор, не входящий в перечень ключевых слов VBA);
As (как) – ключевое слово, которое сообщает VBA, что определяется тип данных для переменной;
Тип – тип данных для объявляемой переменной;
Private (Частный), Public (Общий) – ключевые слова, определяющие область видимости переменной;
Static (Статический) – ключевое слово, которое определяет, сохраняет ли переменная свое значение при завершении блока программы (процедуры, функции) и выходе из него.
Пример 1.
Dim intName As Integer
Если Тип переменной не объявлен, по умолчанию переменная получает тип Variant.
Примечание. Распространенная ошибка в VBA заключается в использовании следующей инструкции:
Dim a, b As Long
Переменная b имеет тип Long, а переменная а – тип Variant.
2.4.4 Объявление констант
Константы могут быть неименованными и именованными.
Неименованные константы: символьные, целые и вещественные.
1 Символьная константа служит для изображения отдельных знаков и представляет собой лексему, состоящую из символов, заключенных в кавычки.
Пример 1.
“Р”, “Program”, “3.14”, “+”
2 Целые константы
Пример 1.
-3, -485, 44, 0
3 Вещественные константы
Представляются в памяти ЭВМ в двух формах:
- в форме с десятичной точкой (фиксированной): 0.1, -0.125;
- в форме с плавающей точкой вида <мантисса>Е<порядок>: 7.32Е+00 (=7.32), 1.5Е-02 (=0.015).
Встроенные константы (предопределенные).
Такие константы используются при работе с объектами приложения. Эти константы не требуют предварительного описания.
Встроенные в VBA константы имеют префикс VB. Для того чтобы узнать конкретное значение константы, можно воспользоваться окном “Object Browser” (Обозреватель объектов).
Пример 2.
MsgBox "Текстовое сообщение", vbInformation
(предопределенная константа vbInformation указывает, что в окне сообщения должен быть значок «Информация»).
Константы, создаваемые пользователем
Синтаксис:
[Public/Private] Const <имя_константы> [As <тип данных>] = <значение константы>,
где
<значение константы> - любое значение или формула, возвращающая значение, которое должно использоваться в качестве константы.
Имя константы и тип данных устанавливаются так же, как и для переменной.
Пример 3.
Const PI As Single = 3.14 ‘Объявлена именованная числовая константа для
‘хранения значения числа Pi
Const Firm = “ООО Бахус”
2.4.5 Строковые переменные
Различают строки переменной и фиксированной длины.
Строки переменной длины могут содержать до двух миллиардов символов.
Строки фиксированной длины – это строка постоянного размера, указанного при объявлении переменной. Если такой строке присваивается значение более длинное, то лишние символы заполняются пробелами.
Синтаксис:
Dim VarName As String * ДлинаСтроки
где
ДлинаСтроки – целочисленная переменная или константа, содержащая число, которое указывает длину строковой переменной.
Пример 1.
Dim strMyName As String * 20 (объявляется строковая переменная фиксированной длины в 20 символов)
В VBA имеется строковая операция – конкатенация, которая применяется для объединения нескольких строк в одну, и обозначается символом амперсенда &. Этот символ должен слева и справа обрамляться пробелами.
Пример 2.
strMy = “Visual” & “Basic for Applications”
2.4.6 Области видимости переменных и констант
Переменная или константа может существовать и быть доступной для некоторых частей программы и при этом быть недоступной (невидимой) для других частей программы.
Область программы, в которой может быть использована переменная или константа, называется областью видимости переменной.
Если переменная видима, то она доступна и, следовательно, существует.
Переменные или константы можно объявлять в трех местах:
- внутри процедуры (видимы только внутри процедуры);
- в верхней части модуля, которая называется областью общих объявлений (видимы всем процедурам данного модуля),
- глобальный уровень видимости (видимы в любой процедуре любого модуля приложения). Объявляются в верхней части модуля в области общих объявлений с помощью оператора Public.
Локальные объекты (местные)-это все объекты (константы, переменные), которые описываются после заголовка процедуры или функции, и доступны только в их пределах. Они создаются при входе в подпрограмму и уничтожаются при выходе из нее.
Глобальные объекты – это все объекты, описанные в вызывающей программе, которые являются доступными внутри всех процедур и функций.
Операторы объявления:
Dim– объявляет локальные переменные только внутри процедуры или функции, в которых они объявлены.Они сохраняют свои значения, только пока выполняется процедура. При завершении процедуры значении этих переменных теряются.
Static – аналогичный оператору Dim. Отличие: переменные сохраняют свое значение после выхода из процедуры, пока работает программа.
Dim, Private – объявляет переменные на уровне модуля (формы), в котором они описаны, но для других модулей данного проекта не доступны. Объявляются в области описания модуля (перед описанием процедур или функций).
Public – объявляет глобальную переменную на уровне проекта, которая доступна для всех его модулей.
2.4.7 Типы данных
Типы данных относятся к самым фундаментальным понятиям любого языка. Тип данных определяет множество допустимых значений, которое может принимать указанная переменная.
Переменную можно представить как простейший объект программы следующим образом:
| Переменная | ||
| Логический уровень | Имя переменной (идентификатор) | Тип переменной |
| Физический уровень | Область памяти | Формат представления в памяти |
Имя переменной представляет логический уровень. Оно однозначно связывает переменную с некоторой физической областью памяти. Имена переменных позволяют их различать в программе, осуществлять доступ к различным участкам памяти для записи данных и их извлечения.
Для эффективного использования памяти необходимо правильно выбрать тип переменной. В таблице 1 приведены базовые типы переменных VBA, необходимая для их размещения память и диапазон возможных значений.
Таблица 1 - Основные типы данных
| Тип | Занимаемая память | Интервалы значений | |||||
| Целочисленные типы | |||||||
| Byte (целые числа) | 1 байт | 0…255 | |||||
| Boolean (логические значения) | 2 байта | True…False | |||||
| Integer (целые числа) | 2 байта | -32768…32767 | |||||
| Long Integer (длинные целые числа) | 4 байта | +/-2.1Е9 | |||||
| Вещественные типы (типы с плавающей точкой) | |||||||
| Single (одинарной точности с плавающей точкой) | 4 байта | -3.402823Е38…-1.401298Е-45 для отрицательных чисел 1.401298Е-45…3.402823Е38 для положительных чисел | |||||
| Double (двойной точности с плавающей точкой) | 8 байт | -1.7976313486232Е308…-4.94065645841247Е-324 для отрицательных чисел 4.94065645841247Е324…1.7976313486232Е308 для положительных чисел | |||||
| Строковые типы | |||||||
| String (текстовая информация - строка фиксированной длины) | 1 байт на каждый символ | От 1 до 65400 | |||||
| String (текстовая информация - строка переменной длины) | 10 байт + 1 байт на каждый символ | От 0 до двух миллиардов символов | |||||
| Объектные типы | |||||||
| Object (Рисунок или ссылка на любой другой объект) | 4 байта | Ссылка на объект | |||||
| Типы Variant | |||||||
| Variant значения любого из перечисленных типов данных | 16 байт для чисел, 22 байта + 1 байт на каждый символ для строк | Любое числовое или строковое значение | |||||
2.5 Функции в VBA
В VBA используются следующие виды функций:
- математические встроенные функции;
- математические функции, не представленные в VBA;
- функции форматирования данных;
- функции преобразования типов
2.5.1 Математические встроенные функции
Таблица 2 - Встроенные математические функции VBA
| Функция | Возвращаемое значение |
| Abs (x) |  - абсолютная величина числа
|
| Atn (x) | arctg (x) – арктангенс от значения параметра, заданного в радианах |
| Sin (x) | sin (x) – возвращает синус угла от значения параметра, заданного в радианах |
| Cos (x) | cos (x) – косинус указанного в радианах угла |
| Tan (x) | tg (x) – возвращает тангенс угла от значения параметра, заданного в радианах |
| Exp (x) | ex – возвращает число e, возведенное в указанную степень, где е – основание натурального логарифма |
| Log (x) | ln (x) – возвращает натуральный логарифм от значения числового выражения |
| Sqr (x) |  - возвращает квадратный корень числового выражения
|
| Rnd (x) | Случайное число из интервала [0,1). Перед вызовом функции надо использовать оператор Randomize (рандомизации) – запуск генератора псевдослучайных чисел) |
| Sgn (x) | Возвращает +1, если значение параметра положительно, -1, если отрицательное, 0, если 0 |
| Fix (x) | Возвращает результат округления выражения с плавающей точкой до целой части. В случае отрицательного параметра возвращает ближайшее большее отрицательное число |
| Int (x) | Возвращает результат округления выражения с плавающей точкой до целой части. В случае отрицательного параметра возвращает ближайшее меньшее отрицательное число |
2.5.2 Математические функции, не представленные в VBA
Таблица 3 - Математические функции, не представленные в VBA
| Функция | Возвращаемое значение |
| Log(X)/Log(10) | lg(х) – возвращает десятичный логарифм от значения числового выражения |
Atn 
| arcsin (x) – возвращает арксинус угла от значения параметра, заданного в радианах |
Atn 
| arccos (x) – возвращает арккосинус угла от значения параметра, заданного в радианах |
| Cos (x)/Sin (x) | ctg (x) - возвращает котангенс угла от значения параметра, заданного в радианах |
| Значение числа π | Pi = 4 * Atn (1) |
2.5.3 Функция форматирования данных
Для того чтобы представить выражение отформатированным в специфицированном формате, необходимо воспользоваться функцией Format. Она возвращает значение типа Variant (String), содержащее выражение, отформатированное согласно указанным спецификациям.
Синтаксис:
Format (выражение [ , “Имя формата (или символ формата)“])
Именованные числовые форматы, показанные в таблице 4.
Таблица 4 - Именованные числовые форматы
| Имя формата | Описание |
| General Number | Число без разделителя тысяч |
| Currency | Отображает две цифры справа от десятичной точки |
| Fixed | Отображает одну цифру слева и две справа от десятичной точки |
| Standard | Отображает одну цифру слева и две справа от десятичной точки и выводит разделитель тысяч |
| Percent | Отображает число в виде процентов и выводит две цифры справа от десятичной точки |
| Scientific | Использует формат с плавающей десятичной точкой |
| Yes/No | Отображает No, если число равно 0, и Yes – в противном случае |
| True/False | Отображает False, если число равно 0, и True – в противном случае |
| On/Off | Отображает Off, если число равно 0, и On – в противном случае |
Рисунок 11 - Программа, показывающая работу различных именованных
форматов с помощью окна Immediate
2 Пользовательские числовые форматы
При построении пользовательского формата возможно использование символов, указанных в таблице 5.
Таблица 5 - Символы пользовательского формата
| Символ | Описание |
| Резервирует позицию цифрового разряда. Отображает цифру или нуль. Если у числа, представленного параметром, есть какая-нибудь цифра в той позиции разряда, в которой в строке формата находится 0, функция отображает эту цифру параметра, если нет – в этой позиции отображается нуль. Пример. Debug.Print Format (1.2 ^2, “##.000”) | |
| # | Резервирует позицию цифрового разряда. Отображает цифру или ничего. Если у числа, представленного параметром, есть какая-нибудь цифра в той позиции разряда, в которой в строке формата находится #, функция отображает эту цифру параметра, если нет – в этой позиции ничего не отображается. Пример. Debug.Print Format (1.2 ^2, “##.###”) |
Продолжение таблицы 5
| . (точка) | Резервирует позицию десятичного разделителя. Указание точки в строке формата определяет, сколько разрядов необходимо отображать слева и справа от десятичной точки. |
| E+,E-, e+, e- | Разделитель мантиссы и порядка в экспоненциальном формате. Пример. Debug.Print Format (sin(x) * exp(5), “#.###e+##”) |
| % | Резервирует процентное отображение числа Пример. Debug.Print Format (0.5, «#.%») (отобразится 50 %) |
| d,m,y | Резервирует позицию при выводе дня, месяца, года в категории форматов Data Пример. Debug.Print Format (Now, “dd/mm/yyyy”) (отобразится текущая дата, например 25.09.2005 |
| / | Разделитель дня, месяца, года в категории форматов Data |
2.5.4 Функции преобразования типов
Преобразование строки в число и обратно осуществляют следующие две функции, представленные в таблице 6.
Таблица 6 - Функции преобразования типов
| Функция | Описание |
| Val (String) | Возвращает число, содержащееся в строке string, как числовое значение (Val от англ. Value – значение, String – строка) |
| Str (number) | Возвращает значение типа Variant (String), являющееся строковым представлением числа number. В качестве допустимого десятичного разделителя функция Str воспринимает только точку. |
| CStr (Выражение) | Возвращает числовое выражение или строку в строку |
| CInt (Выражение) | Возвращает числовое выражение или строку в число типа Integer |
| CSng (Выражение) | Возвращает числовое значение или строку в число типа Single |
2.6 Операторы, выражения и операции
Строка с кодом в исходном тексте программы VBA называется программным оператором.
Программный оператор – это неделимое предложение, выполняющее какое-либо действие. Он может состоять из любой комбинации ключевых слов VBA, свойств, функций, операций и символов, совокупность которых представляет собой корректную конструкцию, распознаваемую компилятором VBA. Например,
Label1.Caption = Time
Правила, применяемые при построении программных операторов, называются синтаксисом.
Программный оператор может включать выражения.
Выражение (Expression) – это комбинация знаков операций и операндов, а также скобки. Назначение любого выражения – получение некоторого значения.
Синтаксическая конструкция выражения: