Методическое пособие по VBA для Excel

ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ПРОФЕССИОНАЛЬНОЕ
ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ

Московской области

«Мытищинский колледж»

МЕТОДИЧЕСКОЕ ПОСОБИЕ

«Основные способы создания пользовательских форм
в программе Visual Basic for Application»

Разработчик:

Мастер п/о Султанова Г.Х.

Рекомендовано методической комиссией

Для проведения практического занятия
в гр. М-54 второго курса

Зам. Директора по УМР                Кудряшова В.В.

«____» _________________ 2017 г.

Мытищи

2017

Создание пользовательской формы

Если Вы собираетесь заниматься проектированием приложения, то, наверное, представляете не только, как оно будет выглядеть (внешний вид интерфейса), но и степень сложности работы с Вашей программой, учитывая то обстоятельство, что с ней может работать и новичок, и опытный пользователь.

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

Если Вы хотите, чтобы с Вашим приложением с удовольствием работали и новичок и профессионал, то Вам следует воспользоваться Экранными формами или как их ещё называют -пользовательскими формами (от английского - UserForm). Созданием на форме объектов управления и установкой значений свойств этих объектов (о чём будет рассказано позже), пользователь создаёт себе условия для работы с будущим приложением как с обычным диалоговым окном. Проделайте следующие действия по вставке Экранной формы:

1. Выполните команду Сервис/ Редактор VisualBasic. Откроется окно редактораVisualBasic

2. Выберите в меню пункт Insert, а в нём пунктUserForm. Появится новая экранная форма

Рис. 1 Экранная форма для разрабатываемого приложения

Типы элементов управления

При создании экранной формы автоматически отображается панель инструментов Панель элементов (ToolBox). В Экранную форму можно вставить большое число разнообразных типов элементов управления. Элемент Надпись(Label) - это статическая область текста, обычно содержащая какую - либо поясняющую информацию, например к элементам интерфейса.

Рис. 2 Надпись - статическая область текста

Следующий инструмент, присутствующий на Панели элементов называется Текстовое поле (TextBox). В окнах, которые будут созданы с помощью этого инструмента на форме, Вы будете вводить данные и получать результаты (см. рис. 3)

Рис. 3 Текстовое поле - залог успешного ввода и вывода данных

С помощью инструмента Кнопка(CommandButton) на форме можно создать командную кнопку. Этот элемент управления знаком каждому пользователю, т.к. в Windows на каждом шагу мы встречаемся с кнопками OK, Отмена, Да, Нет.

Рис. 4 Создание командной кнопки

Следующие два элемента управления, флажок(CheckBox)ипереключатель(OptionButton) (см. рис.5, 6), используются в ситуациях, когда пользователю надо сделать выбор из нескольких возможностей. Эти элементы имеют два устойчивых состояния: включено и выключено. Если флажки организованы в группу, то пользователь может установить в этой группе один или несколько флажков, а то и все. В отличие от флажков, в группе переключателей можно выбрать только один.

Рис. 5 Окно формы с созданными флажками

Ещё два элемента управления, которые можно поместить на форму, - полосы прокрутки (ScrollBar) и счётчик(SpinButton). Полосы прокрутки как элементы управления - это не совсем то, чем мы пользуемся для прокрутки рабочего листа, в данном случае полосы прокрутки применяются в качестве механизмов выбора, например, в графических программах с их помощью можно сделать изображение более светлым или более тёмным.

Рис. 6 Окно формы с созданными переключателями

Элементы управления, можно вставлять не только в экранную форму, но и в рабочие листы.

Вставка элементов управления в рабочий лист

Процесс вставки элемента в рабочий лист почти ничем не отличается от рисования прямоугольника в программе Paintи заключается в следующем:

1. Выполните команду Вид/ Панели инструментов/ Формы

2. На панели элементов выберите элемент любой управления

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

4. Нажмите левую кнопку мыши и протащите указатель, рисуя прямоугольник. Отпустите кнопку мыши.

Рис. 17 Окно Excelcэлементами управления

Как осуществляется связь элемента управления с рабочим листом?

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

1. Поместите на рабочий лист флажок

2. Выделите его щелчком мыши

3. Щёлкните на нём правой кнопкой мыши и в контекстном меню выберите команду Формат объекта. Откроется диалоговое окно Формат элемента управления (см. рис. 18)

Рис. 18 Вкладка Элемент управления

4. Перейдите на вкладку Элемент управления

5. Щёлкнув в поле Связь с ячейкой и установив там курсор, щёлкните в ячейке А1, а затем на ОК

6. Щёлкните вне области флажка для снятия с него выделения

7. Установите флажок, щёлкнув на элементе Флажок. В ячейке А1 появится слово ИСТИНА

8. Сняв флажок, можно увидеть, что в ячейке А1 появилось слово ЛОЖЬ

Поработаем с инструментом Полоса прокрутки.

1. Создайте на рабочем листе полосу прокрутки

2. Щёлкните на ней правой кнопкой мыши и в контекстном меню выберите команду Формат объекта. Откроется диалоговое окно Формат элемента управления (см. рис. 9)

Рис. 9 Вкладка Элемент управления

3. Перейдите на вкладку Элемент управления

4. Щёлкнув в поле Связь с ячейкой и установив там курсор, щёлкните в ячейке А1, а затем на ОК

5. Щёлкните на полосе прокрутке на кнопке со стрелкой, направленной вправо. Значение в ячейке А1 увеличится на единицу. Продолжая щёлкать на этой кнопке, Вы увеличиваете значение в ячейке А3

Программирование на VBA

Выше было сказано, что процесс создания экранной формы, называется разработкой интерфейса. Под интерфейсом понимают совокупность средств, обеспечивающих взаимодействие пользователя и программ вычислительной системы. При разработке интерфейса пользователь должен быть заинтересован в максимально простом и удобном способе ввода и вывода данных. Важна и эстетическая форма подачи программы: это и размер формы, её фон, шрифт надписей, звуковое и графическое сопровождение. Также мы говорили о том, что на экранной форме пользователь создаёт элементы управления: текстовые поля, метки, командные кнопки. Эти элементы являются объектами языка Visual Basic for Applications. Объект- одно из основных понятий не только VBA, но и других объектно-ориентированных языков программирования. Каждый объект имеет своё имя и является представителем некоторого класса подобных себе объектов. Под классом объектов подразумевается общее описание таких объектов, для которых характерно наличие множества общих свойств и общих действий, которые способны выполнять эти объекты. Существует, например, класс Командная кнопка - общее описание кнопок в окнах приложений. Кнопки могут быть разных размеров и цветов, иметь разные подписи, но имеют множество общих свойств и событий (например, щелчок мыши по кнопке). Началом работы алгоритма, а, следовательно, и программы, является какое-нибудь событие. Событием (Event) называется характеристика класса объектов, описывающая внешнее воздействие, на которое реагирует объект этого класса во время работы приложения. Объектно-ориентированное программирование основано на следующей идее: программы управляются событиями. Пояснить это можно так: после проектирования экранной формы устанавливаются события, и только затем программируются действия, связанные с этими событиями.

Код программы, написанной пользователем на VBA, хранится в модулях. Модуль- это совокупность объявлений и процедур, хранящихся как единое целое. Рассмотрим термин “процедура”.

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

Процедура- это независимая часть программы, которую можно вызвать по имени для выполнения определённых действий. Процедура выполняет один или несколько операторов и не возвращает значения. Примером процедуры является код макроса. Например:

SubМойПервыйМакрос_Click()

WithSelection.Font

.Size = 18

.ColorIndex = 3

End With

End Sub

Процедура обозначается словом Sub,EndSubговорит о том, что процедура закончена. Процедура выполняется в ответ на какое-либо событие или говорят, что процедура обрабатывает событие. Имя процедуры состоит из названия объекта МойПервыйМакрос, и события,Click(щелчок), которое отделяется знаком подчёркивания от названия объекта.

Функцияаналогична процедуре, но отличается тем, что возвращает в точку вызова основной программы единственный результат. Например, Y= ABS(X); - определяет модуль числа X и возвращает результат в точку вызова Y.

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

PublicFunctionПроцент (число)

Процент = число*0.01/100

EndFunction

Функция может иметь аргумент Число. Аргумент Число может быть числом или ссылкой на ячейку рабочего листа. Величина процента является тем значением, которое возвращает функция.

В VBAсуществуют определённые правила для имён процедур и функций.

Первые три символа имени должны быть буквами

Имя может содержать буквы, цифры и знаки подчёркивания

Имя не может содержать пробелы, точки, запятые, восклицательные знаки и символы @, &, $, #

Имя не должно иметь более 255 символов

1. Объявление типа переменной

Переменные используются для хранения данных, которые могут изменяться в процессе выполнения процедуры. Объявление типа переменной означает, что пользователь устанавливает определённые границы, в которых может изменяться переменная. Тип переменной можно вообще не определять. Если тип переменной не объявляется, по умолчанию он принимается как тип Variant.В таблице 1 содержится информация о размере данных, т. е. об объёме памяти, выделяемом для хранения данных.

Типы данных в VBA

Таблица 1

Тип данных	Размер в байтах	Диапазон значений
Byte	1	0 … 255
Integer	2	-32768 … +32767
Long	4	-2 147 483 648 … [phone]
Boolean	2	True, False
String	10 + 1 байт на символ	0 … 2 млрд. символов
Variant	16	-1.797693Е308 … -4.9406564Е-324 для отрицательных чисел; 4.9406564Е-324 … 1.79769313Е308 для положительных чисел

Для объявления переменной используется оператор Dim. Этот оператор имеет следующий синтаксис:

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

Общие правила написания имени переменной

Ø Имя переменной должны начинаться с буквы

Ø Имя может содержать буквы, цифры и знаки подчёркивания

Ø Имя не может содержать пробелы, точки, запятые, восклицательные знаки и символы @, &, $, #

Ø Имя не должно иметь более 255 символов

Можно не указывать тип данных при написании своих процедур и тогда по умолчанию VBAприменит для Ваших переменных типVariant. Но, просмотрев таблицу 1 можно увидеть, что ни один из типов данных не требует 16 байт для сохранения значений переменных. Неплохо бы экономить ресурсы памяти компьютера. Кроме того, не объявляя тип переменных вы рискуете получить результат выполнения Вашей программы, который будет далёк от ожидаемого.

Описание констант

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

ConstИмяКонстантыAsТипДанных = Значение

Например:

Const Gruppa As Integer = 25

Применение условных операторов

До сих пор мы учились вводить данные и присваивать значения переменным. Теперь надо научиться организовывать различные потоки выполнения приложения в зависимости от ситуации, которая складывается в ходе работы программы. Не раз и не два Вы видели в окнах сообщений кнопки Да и Нет, Ок и Cancel, и знаете, что дальнейший ход программы будет зависеть от того, на какой кнопке щёлкнет пользователь.

Разветвляющийся алгоритм

Алгоритм называется разветвляющимся, если последовательность выполнения шагов алгоритма изменяется в зависимости от выполнения некоторых условий. Условие- это логическое выражение, которое может принимать одно из двух значений: “ДА” - если условие верно (истинно), и “НЕТ” - , если условие неверно (ложно).

Простой условный оператор

Общий вид в алгоритме конструкции простого условного оператора следующий:

 

Нет Да

Синтаксис простого условного оператора, если Вы пишите программу структурно, следующий:

If<логическое выражение> Then
P1
Else
P2

EndIf

Возможна и другая запись:

If<логическое выражение> ThenP1 ElseP2

где If, Then, Else,EndIf- зарезервированные слова, а P₁, P₂ - операторы.

Простой условный оператор работает по следующему алгоритму:

Сначала вычисляется логическое выражение. Если результат есть TRUE (истина), то выполняется оператор P₁, а оператор P₂пропускается.
Если результат есть FALSE (ложь), то выполняется оператор P₂, а оператор P₁пропускается.

Сокращённый условный оператор

Если необходимо выполнить некоторое действие только при истинности проверяемого условия, то в таком случае применяетсясокращённыйусловный оператор.

Общий вид в алгоритме конструкции сокращённого условного оператора следующий:

Нет Да

Синтаксис сокращённого условного оператора, если Вы пишите программу структурно, следующий:

If <логическое выражение> Then
P₁

EndIf

Возможна и другая запись:

If<логическое выражение> ThenP₁ 

где If, Then, Else,EndIf- зарезервированные слова, а P₁ - оператор.

Составной условный оператор

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

 

Нет Да

Синтаксис составного условного оператора, если Вы пишите программу структурно, следующий:

If<логическое выражение> Then

P₁

P₂

.

.

P_N

Else

M₁

M₂

.

.

M_n

End If

Возможна и другая запись:

If<лог. выраж.>Then P₁ P₂. . P_N Else M₁ M₂ . . M_n

где If, Then, Else,EndIf- зарезервированные слова, а P₁, P₂,P_N,M₁, M₂,M_N- операторы

Многозначные ветвления

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

Общий вид в алгоритме конструкции многозначных ветвлений следующий:

 

Нет Да

Нет Да

Нет Да

Синтаксис многозначных ветвлений, если Вы пишите программу структурно, следующий:

If <лог. выражение1> Then

P₁

ElseIf<лог. выражение2>Then

P₂

ElseIf<лог. выражение3>Then

P₃

Else

P₄

EndIf

Возможна и другая запись:

If <лог. выражение1> Then P₁ElseIf<лог. выражение2>ThenP₂ElseIf<лог. выражение3>ThenP₃ElseP₄

где If, Then, Else,EndIf- зарезервированные слова, а P₁, P₂,P₃P_4,- операторы

Алгоритм работы такой конструкции следующий: если логическое выражение1 истинно, то выполняется оператор или блок операторов, следующих за конструкцией Then, в противном случае этот блок пропускается. Если логическое выражение, следующее за оператором If ложно, то анализируется логическое выражение, следующее за ElseIf. Если оно истинно, то выполняется оператор или блок операторов, следующих за Then, а остальные операторы пропускаются. Операторы, следующие за последним Else, выполняются лишь в том случае, если ложны все логические выражения в конструкциях If. Любая встретившаяся часть Elseпринадлежит ближайшей к ней части Thenусловного оператора. Условные операторы Ifв такой конструкции называются вложенными.

Рассмотрим пример использования простого условного оператора If.

1. Откройте новую рабочую книгу

2. Создайте таблицу, показанную на рис. 30

Рис. 30 Рабочий лист с примером

3. Вставьте модуль в рабочую книгу

4. Создайте процедуру и назовите её Абитуриент

5. Введите следующий код процедуры:

DimballAsString

ball = MsgBox("Вы хотите поступить в КОЭК?", vbYesNo)

If ball = vbYes Then

Range("b4").Value = "Надо сдать экзамены"

Else

Range("b4").Value = "Идите в ПТУ"

End If

6. Перейдите в рабочую книгу

7. Вставьте командную кнопку в рабочий лист. В списке Имя макроса выберите процедуру Абитуриент и щёлкните на кнопке Ок

8. Дайте кнопке новое имя - Вопрос. Получим лист как на рис. 31

Рис. 31 Законченный рабочий лист

9. Щёлкните на кнопке Вопрос. Отобразится окно сообщения (рис.32)

Рис. 32 Вопрос, требующий ответа

В этом примере значение переменной ballпроверяется условным операторомIf. После применения оператора Range("b4").Value = "Надо сдать экзамены" в ячейкуb4 рабочего листа заносится, то выражение, которое находится справа от оператора присваивания либо - "Надо сдать экзамены", либо - "Идите в ПТУ"

Другой пример посвящён более сложной конструкции - многозначным ветвлениям. Но прежде несколько таблиц, содержимое которых пригодится в дальнейшем. Для создания логических условий используютсяоператоры сравнения.

Таблица 5

Оператор сравнения	Его назначение
=	Равно
>	Больше
<	Меньше
>=	Больше или равно
<=	Меньше или равно
<>	Не равно

Таблица 6

Логический оператор	Описание
And(И)	Если оба условия, соединённые этим оператором имеют значение ИСТИНА, то результат - ИСТИНА, в противном случае - ЛОЖЬ
Or(ИЛИ)	Если хотя бы одно из условий, соединённых этим оператором, имеет значение ИСТИНА, то результат - ИСТИНА, в противном случае - ЛОЖЬ
Not(Не)	Если логическое выражение имеет значение ЛОЖЬ, то результат применения к нему этого оператора - ИСТИНА. Если логическое выражение имеет значение ИСТИНА, то результат применения оператора - ЛОЖЬ.
Xor(исключающее ИЛИ)	Если одно и только одно из условий, соединённых этим оператором, имеет значение ИСТИНА, то результат - ИСТИНА. Если оба условия имеют значения ИСТИНА или ЛОЖЬ, то результат - ЛОЖЬ

2. Оператор выбора Select Case

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

1. 1.           В редакторе Visual Basic напишите код процедуры, как на рис. 34

Рис. 34 Код процедуры Ball

2.Запустите её на выполнение и убедитесь в том, что она работает

На этом примере видно, что за счёт вкладывания одного условного оператора в другой, конструкция усложняется, становится тяжёлой для восприятия. В подобных случаях хорошей альтернативой оператору Ifслужит оператор выбораSelectCase, который позволяет выбрать одно из нескольких возможных продолжений программы.

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

Select Case КлючВыбора

Case Is выражение

оператор

Case диапазон значений

оператор

Caseдиапазон значений

оператор

Case диапазон значений

оператор

Case Else

оператор

End Select

Например:

Select Case vozrast

CaseIs<=7

Msgbox”Ты дошкольник”

Case 8 to16

Msgbox”Ты учишься в школе”

Case17to30

Msgbox”Тебе пора заняться делом”

Case31to60

Msgbox”Кто не работает, тот не ест”

Case Else

Msgbox”Вы заслужили отдых”

End Select

Если значение переменной vozrastменьше или равно 7, отображается сообщение ”Ты дошкольник”. Если значение переменнойvozrastнаходится в диапазоне от 8 до 16, отображается сообщение ”Ты учишься в школе”. Если значение переменнойvozrastнаходится в диапазоне от 17 до 30, отображается сообщение ”Тебе пора заняться делом”. Если значение переменнойvozrastнаходится в диапазоне от 31 до 60, отображается сообщение ” Кто не работает, тот не ест”. Если значение возраста не равно ни одному из предложенных диапазонов значений, выводится сообщение ”Заслуженный отдых”.

1. В редакторе VisualBasicнапишите код процедуры, как на рис. 35

Рис. 35 Код процедуры Ballс операторомSelectCase

2. Запустите её на выполнение и убедитесь в том, что она работает

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

2. Циклический алгоритм

Очень часто в программах надо выполнить определённые операторы несколько раз. Не логично записывать эту последовательность действий 20 или 50 раз подряд. В этих случаях организуют циклические вычисления. Алгоритм называется циклическим, если определенная последовательность шагов выполняется несколько раз в зависимости от заданной величины, которая называется параметром цикла.

2. Оператор цикла For…Next

Общий вид в алгоритме конструкции оператора For…Next, который позволяет выполнить группу операторов или один оператор заданное количество раз, следующий:

 

Синтаксис для цикла For…Nextследующий:

For I= N₁ To N₂ Step h

P₁

.

. Тело цикла

P_n

Next

Где For(для),To(до),Step(шаг),Next(следующий) - служебные словаVBA, аP₁,P_n- операторы.Stepявляется необязательным параметром. Если он опущен в программе, то значение параметраIувеличивается на 1. ПараметрStepможет быть любым действительным числом, как целым, так и дробным, как положительным, так и отрицательным. Работает операторForследующим образом:

При первом вхождении в цикл, параметр цикла I принимает значение равное величине нижней границы N₁и выполняется оператор или операторы в теле цикла. Затем значение параметра увеличивается на величину шага и вновь выполняется тело цикла. Подобные действия будут повторяться до тех пор, пока значение параметра цикла не станет больше величины N₂, после чего осуществляется выход из цикла.

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

1. В редакторе VisualBasicсоздайте процедуру и назовите её Цикл

2. Введите код процедуры, как на рис. 36

Рис. 36 Код процедуры Цикл

3. Запустите её на выполнение и убедитесь в том, что она работает

 Переменная счётвыступает в этом примере в качестве параметра цикла, и будет менять своё значение от 1 до 4. В цикле 4 раза будет появляться окно сообщения за счёт использования функцииMsgBox. С помощью значка конкатенации к сообщению будет добавляться значение переменнойсчёт, а также слово год(а).

Ещё одним примером, демонстрирующим работу оператора For…Next, является решение задачи: Найти сумму целых чисел от 1 до 50.

Код процедуры представлен на рис. 37

Рис. 37 Код процедуры Сумма целых чисел

В этом примере, описав, переменные счётисуммакак целочисленные, обнуляется значение переменнойсумма. Для чего это делается? Мы берём произвольную ячейку памяти компьютера и должны быть уверены в том, что в ней ничего не содержится. Ведь в цикле будет выполняться операторсумма = сумма + счёт,то есть к тому что было в ячейкесуммаприбавляется значение переменнойсчёт. И мы должны знать, что предварительно туда занесёно значение нуля. В результате запуска процедуры на выполнение Вы получите ответ: 1275.

Рис. 38 Окно с ответом

Краткое описание документа:

Описан порядок разработки пользовательских форм в программе Visual Basic for Application для организации расчетов в электронных таблицах Excel. Наглядно показан порядок работы, который может использоваться для разработки плана урока.

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