Использование макросов и элементов управления для формирования списков (выборок данных) согласно постановке задачи.

Основные определения

Макрокоманда или макрос — программный алгоритм действий, записанный пользователем. Часто макросы применяют для выполнения рутинных действий.

В табличном процессоре Excel для создания макросов используется язык программирования VBA.

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

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

Visual Basic for Applications (VBA, Visual Basic для приложений) — немного упрощённая реализация языка программирования Visual Basic, встроенная в линейку продуктов Microsoft Office (включая версии для Mac OS), а также во многие другие программные пакеты, такие как AutoCAD, SolidWorks, CorelDRAW, WordPerfect и ESRI ArcGIS. VBA покрывает и расширяет функциональность ранее использовавшихся специализированных макро-языков, таких как WordBasic.

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

Сведения о безопасности

Макросы, как и некоторые другие элементы программного кода на компьютерах могут содержать вредоносный код (так называемые макровирусы), поэтому по умолчанию в Excel выполнение макросов отключено. Для включения необходимо воспользоваться командой Сервис – Макросы – Безопасность и в появившемся окне установить низкий (не рекомендуется) или средний уровень безопасности.

Рис. 5. Установка уровня безопасности макросов.

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

Рис 6. Оповещение о наличии макросов в файле.

Создание нового макроса

Для создания нового макроса необходимо воспользоваться командой Сервис – Макрос – Макросы… в появившемся окне ввести имя макроса, например, My_Macros, после чего нажать кнопку «Создать». Откроется редактор макросов VBA, где будет создана функция с аналогичным названием. Имя макроса не должно содержать пробелов и должно начинаться с буквы. Использовать русские буквы в названии можно, но это считается плохим стилем среди программистов. Также рекомендуется давать названия макросам, функциям, переменным на английском языке.

Рис. 7. Создание нового макроса.

Также существует и другой способ создания макросов – с помощью команды Сервис – Макрос – Начать запись. В таком случае появится сначала окно, где пользователю будет предложено ввести его имя и начнется запись. Также появится панель записи с кнопкой «Стоп», а пользователю достаточно произвести однократно вручную все необходимые действия, после чего нажать вышеуказанную кнопку. Макрос будет записан.

Выполнить макрос можно либо по нажатию кнопки “Выполнить" (в окне команды Сервис – Макрос – Макросы…) либо ассоциировав его с пользовательским элементом управления либо назначив ему сочетание клавиш.

Рис 8. Запись макроса.

Рис. 9. Панель записи макроса.

Также в окне команды Сервис – Макрос – Макросы… можно отредактировать любой ранее созданный или записанный макрос, выбрав его из списка и нажав кнопку «Изменить». Макрос будет открыт в окне редактора VBA.

Рис 10. Окно редактора VBA.

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

Формирование списка магазинов, расположенных на двух улицах заданных пользователем и сортировка его согласно постановке задачи.

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

1. Подготовить диапазон для вывода данных, очистив его от возможных предыдущих значений.

2. Запросить у пользователя название первой улицы и сохранить его в строковую переменную.

3. Запросить у пользователя название второй улицы и сохранить его в другую строковую переменную.

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

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

6. Применить к исходным данным расширенный фильтр с копированием данных вовне диапазона базы данных.

7. Удалить ненужные значения из диапазона вывода.

8. Отсортировать диапазон вывода согласно постановке задачи.

9. Выдать пользователю сообщение о том, что процесс завершен.

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

В VBA комментарии начинаются с символа «'» и продолжаются от него до конца строки.

Sub Task5()

Range("$A$41:$H$60").Clear 'Очищаем область для вывода данных фильтра (иначе ошибка)

' Запрашиваем названия улиц и сохраняем их в строковую ($) переменную

Street1$ = InputBox("Введите первую улицу", "Ввод данных")

Street2$ = InputBox("Введите вторую улицу", "Ввод данных")

Range("B38").Select 'выбираем ячейку где будем хранить 1 название

ActiveCell.FormulaR1C1 = Street1$ 'и записываем его туда

Range("B39").Select 'выбираем ячейку где будем хранить 2 название

ActiveCell.FormulaR1C1 = Street2$ 'и записываем его туда

' Применяем к БД расширенный фильтр

Range("A6:H25").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _

"B37:B39"), CopyToRange:=Range("A41:H60"), Unique:=False

'Выделяем результирующий диапазон

Range("$A$41:$H$60").Select

'Сортируем его

Selection.Sort Key1:=Range("B42"), Order1:=xlAscending, Key2:=Range("F42" _

), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _

False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _

:=xlSortNormal

'Удаляем лишнее

Range("$F$41:$F$60").Select

Selection.Cut

Range("$D$41").Select

ActiveSheet.Paste

Range("$E$41:$H$60").Select

Selection.ClearContents

Range("B40").Select 'убираем куда-нибудь курсор

MsgBox "Выполнено!", vbInformation + vbOKOnly, "Готово" 'сообщаем что все готово

End Sub

Теперь дадим пояснения некоторым командам.

Range("$A$41:$H$60").Clear – означает, что в диапазоне $A$41:$H$60 необходимо очистить значения ячеек.

Street1$ = InputBox("Введите первую улицу", "Ввод данных")

– Функция, запрашивающая ввод пользователем значения. В общем виде выглядит как Variable=InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)

где

Prompt – Сообщение, выдаваемое пользователю.

Title – Заголовок окна.

Default – Значение по умолчанию.

Left – Координата X (верхнего левого угла).

Top - Координата Y (верхнего левого угла).

HelpFile – Файл помощи.

HelpContextId – Идентификатор данных в файле помощи.

Type – Тип значения, которое будет записано в переменную (0 - формула,1 - число,2 – Текстовая строка, 4 – Логическое значение (Истина или Ложь),8 – Диапазон ячеек,16 – Неопределенное значение, 64 – Массив значений)

Variable –Переменная, в которую будет помещено значение введенное пользователем при нажатии им кнопки «ОК». Если пользователь ничего не введет или нажмет кнопку «Cancel», то в переменную ничего не будет записано. При дальнейшей обработке, если она будет вестись как обработка строки в переменное окажется пустая строка (“”), 0 – если это числовая переменная и ЛОЖЬ, если логическая.

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

Все параметры кроме первого не являются обязательными.

Рис. 11. Окно запроса.

Range("B38").Select– в общем виде выглядит как Range("диапазон").Select и позволяет выбрать диапазон ячеек, если в диапазоне одна ячейка, то курсор ввода будет установлен в нее.

ActiveCell.FormulaR1C1 = Street1$ - данной командой мы присваиваем значение нашей переменной ранее выделенной ячейке.

Range("A6:H25").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _

"B37:B39"), CopyToRange:=Range("A41:H60"), Unique:=False

Где

Action:=xlFilterCopy – указывает функции Range("диапазон").AdvancedFilter, что нужно скопировать значения в другое место.

CriteriaRange:=Range("B37:B39") – указывает ей же диапазон условий фильтра

CopyToRange:=Range("A41:H60") – указывает ей диапазон куда надо скопировать отфильтрованные данные.

Unique:=False – копировать нетолько уникальные записи (Unique:=True – только уникальные).

Selection.ClearContents – Очищает содержимое в выделенных ячейках

Selection.Cut –Вырезает содержимое выделенных ячеек (аналогично команде Правка - Вырезать)

ActiveSheet.Paste -Вставляет содержимое из буфера обмена в выделенные ячейки (аналогично команде Правка - Вставить)

MsgBox "Выполнено!", vbInformation + vbOKOnly, "Готово"

MsgBox - Функция выдачи пользователю диалогового окна, в нашем случае сообщающего о завершении процесса. Здесь первым параметром идет сообщение, далее определяется вид диалогового окна – vbInformation указываетна то, что окно должно содержать стандартную иконку «Информация», а vbOKOnly на то, что в диалоговом окне должна быть единственная кнопка «ОК»

Рис. 12. Диалоговое окно с сообщением о завершении процесса.

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

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

1. Подготовить диапазон для вывода данных, очистив его от возможных предыдущих значений.

2. Запросить у пользователя верхнюю границу диапазона (т.е. с какого значения площади включительно начинается диапазон).

3. Запросить у пользователя нижнюю границу диапазона (т.е. каким значением площади включительно заканчивается диапазон).

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

5. Применить к исходным данным расширенный фильтр с копированием данных вовне диапазона базы данных.

6. Удалить ненужные значения из диапазона вывода.

7. Отсортировать диапазон вывода согласно постановке задачи.

8. Выдать пользователю сообщение о том, что процесс завершен.

Ниже приведен текст программы на языке VBA, реализующий данный алгоритм. Текст снабжен комментариями.

'Запрашиваем ввод минимального и максимального значения диапазона

'Преобразуем введенное значение в число

MinS = Val(InputBox("От:", "Ввод диапазона"))

MaxS = Val(InputBox("До:", "Ввод диапазона"))

'Проверяем, не ошибся ли пользователь (ввел максимум меньше минимума)

If MaxS < MinS Then

'Если ошибся то выдаем ошибку

MsgBox "Вы ошиблись при вводе, максимальное значение (от) должно быть больше минимального (до)", vbCritical + vbOKOnly, "Ошибка ввода"

Exit Sub ' и выходим

End If

'формируем условия для фильтра

DiapMin$ = ">=" + Trim$(Str$(MinS))

DiapMax$ = "<=" + Trim$(Str$(MaxS))

Range("B66").Select 'выбираем ячейку где будем хранить 1 условие

ActiveCell.FormulaR1C1 = DiapMin$ 'и записываем его туда

Range("C66").Select 'выбираем ячейку где будем хранить 2 условие

ActiveCell.FormulaR1C1 = DiapMax$ 'и записываем его туда

Range("$A$68:$H$87").Clear 'Очищаем область для вывода данных фильтра (иначе ошибка)

' Применяем к БД расширенный фильтр

Range("A6:H25").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _

"B65:C66"), CopyToRange:=Range("A68:H87"), Unique:=False

'Удаляем лишнее

Range("E68:H87").Select

Selection.ClearContents

'Сортируем получившийся список по убыванию площадей

Range("A68:D87").Select

Selection.Sort Key1:=Range("D69"), Order1:=xlDescending, Header:=xlGuess _

, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

Range("B67").Select 'убираем куда-нибудь курсор

MsgBox "Выполнено!", vbInformation + vbOKOnly, "Готово" 'сообщаем что все готово

End Sub

Конвертация строк и чисел в VBA

Интерпретатор VBA в отличии внутренних функций Excel не всегда может определить с каким типом данных он работает (строкой или числом). Поэтому, правильным считается явно преобразовывать типы данных. Для этого применяются функции Val (строка), соответственно преобразующая строку в число, и Str$ (число),соответственно выполняющая обратное преобразование.

MinS = Val(InputBox("От:", "Ввод диапазона"))

MaxS = Val(InputBox("До:", "Ввод диапазона"))

В нашем случае функция Valполучаетстроку,введенную пользователем в InputBoxи преобразует ее в число. Примечание: Если пользователь введет не число, функция Valавтоматически преобразует его в 0, а после проверки на ошибочный ввод с помощью условного оператора, функцией Str$ выполняется обратное преобразование:

Str$(MinS)

Str$(MaxS)

Функция Trim$ (строка)далее убирает лишние пробелы (Функция Str$ при конвертации вставляет перед числом лишний пробел), далее осуществляется контактация строк оператором +.

Примечание: в VBA для контактации строк используется оператор +.

После чего результат операции записывается в строковую переменную (DiapMin$и DiapMax$ соответственно для минимального и максимального значения диапазонов):

DiapMin$ = ">=" + Trim$(Str$(MinS))

DiapMax$ = "<=" + Trim$(Str$(MaxS))

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

Range("B66").Select 'выбираем ячейку где будем хранить 1 условие

ActiveCell.FormulaR1C1 = DiapMin$ 'и записываем его туда

Range("C66").Select 'выбираем ячейку где будем хранить 2 условие

ActiveCell.FormulaR1C1 = DiapMax$ 'и записываем его туда

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

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

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

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