1. Откройте созданную вами рабочую книгу Excel. Скопируйте свою таблицу на новый лист. Удалите в ней все формулы. Запустите интегрированную среду разработки VBA.
2. Скопируйте созданную вами программу в окне редактора кода, измените название программы.
3. Убедитесь в том, что строкой Option explicit задан режим обязательного объявления переменных.
4. Напишите (скорректируйте) коды объявления внутренних переменных своей программы и задайтесь их типом данных. Прокомментируйте их в тексте программы.
5. Введите в текст программы проверку считанных из таблицы Excel значений переменных. Для этого задайтесь диапазоном возможных значений переменных, например минимальное и максимальное значение, и в случае выхода переменной из указанного диапазона присвойте ей необходимое граничное значение за счет использования оператора выбора последовательности вычислений If Then Else EndIf.
6. Организуйте повторяющиеся вычисления в программе с помощью оператора Do While Loop и Do Until Loop с предусловием.
7. Модернизируйте цикл вычислений или введите дополнительный цикл с использованием оператора Do Loop While и Do Loop Until с постусловием.
8. Продемонстрируйте возможности работы оператора цикла For To Next.
9. Изменяя значения переменных в таблице Excel и используя отладчик, убедитесь в правильности выполнения программы.
10. Проверьте правильность комментариев с учетом изменений в тексте программы, дополните и, при необходимости, скорректируйте их.
Контрольные вопросы
1. Как задать последовательность выполнения операций?
2. Какие операции разрешены в языке VBA?
3. Чем оператор отличается от операции?
4. Каково назначение оператора присваивания? Как он обозначается в алгоритмах?
5. Для каких целей используется символ модификации в алгоритмах?
6. Чем отличается оператор If Then Else EndIf от оператора Select Case End Select?
7. В чем заключается необходимость применения операторов цикла в программировании?
8. В каких случаях целесообразно использовать операторы Do Loop While с постусловием?
9. В каких случаях целесообразно использовать оператор For To Next?
10. Как можно запрограммировать бесконечно выполняющийся цикл?
Отчет о работе
Подготовьте отчет о выполненной лабораторной работе. Он должен содержать титульный лист, рисунок алгоритма и текст написанной вами программы с построчным комментарием ее действий. Сформулируйте выводы, которые можно сделать по результатам выполненной работы.
Лабораторная работа №5
Функции и процедуры. Создание пользовательской функции Excel
Методические указания
Составление программ для ЭВМ требует большого объема трудозатрат. Вполне очевидно, что один раз созданные и проверенные программы представляют собой самостоятельную ценность. Программисты стремятся использовать свои разработки в новых программных проектах и, как следствие, создают методы, позволяющие относительно несложно включать ранее разработанные коды в новые программные изделия. Достаточно быстро стало понятно, что обычное механическое копирование кодов в новую программу чревато серьезными ошибками. Для уменьшения вероятности появления ошибок при использовании ранее созданных программ в языках программирования высокого уровня была внедрена концепция так называемых функций и процедур. Для ее практической реализации потребовались существенные доработки в системе команд процессора, результатом которых явилось появление специальных команд вызова функции (процедуры) и возврата в точку вызова.
Ключевая идея создания функций и процедур заключалась в обеспечении возможности многократного обращения к одной и той же последовательности кодов из разных мест программы. По своей сути термины функция и процедура в языках высокого уровня взаимозаменяемы. Отличие одного от другого сводится к непринципиальной разнице в способах их оформления в теле программы и, что более важно, в способах оформления вызова. Некоторые языки программирования, например Си, вообще рассматривают только функции. В языке VBA сохранились описатели Function для обозначения функций и Sub для обозначения процедур.
Функциейили процедуройназывается самостоятельная программа, предназначенная для решения определенной задачи. Поскольку любая работоспособная программа попадает под это определение, можно сделать вывод, что функцией может быть любая последовательность кодов. На самом деле это действительно так. Написанная нами программа на языке высокого уровня, оформленная в соответствии с правилами языка, оттранслированная и запущенная на выполнение представляет собой функцию, запускаемую, например, операционной системой. В данном случае правила оформления такой программы представляют собой ничто иное, как правила оформления функций операционной системы. Самостоятельный интерес представляют собой правила оформления функций, написанных на языке высокого уровня и вызываемых из других программ, написанных также на том же или другом языке высокого уровня или на ассемблере. Наиболее строго определены правила создания функций в том случае, когда для написания вызывающей программы и собственно функции используется один и тот же язык программирования.
Практический смысл использования функций (процедур) в программировании определяется следующими обстоятельствами. Появляется возможность разбиения большой программы на отдельные составляющие, разработка которых гораздо проще разработки всей программы. Сокращается объем кодов программы за счет удаления повторяющихся действий и замены их вызовами. Повышается надежность программного обеспечения, поскольку программа использует уже многократно проверенные последовательности кодов. Все это, в конечном итоге, ведет к росту производительности труда программиста.
Изложенные соображения играют важную роль при проектировании сложных программ. Так, например, взявшись за выполнение задачи, программист разбивает ее на набор возможно менее связанных между собой функций или процедур, каждая из которых решает некую самостоятельную задачу. Подобный прием называется декомпозициейи широко используется на практике. Очевидно, что для любой более или менее сложной задачи можно найти чрезвычайно большое, если даже не бесконечное количество вариантов декомпозиции. Поэтому выбор конкретного варианта разбиения задачи во многом определяется используемой методологией ее проектирования, а также опытом и пристрастиями разработчика.
При изучении способов создания функций (процедур) следует принимать во внимание следующие моменты:
· Каждая функция (процедура) имеет имя. Это имя является идентификатором и должно быть тем или иным способом объявлено.
· Каждая функция (процедура) имеет свои коды, которые должны быть оформлены заданным языком программирования способом. Эти коды называются определением функции.
· Для решения задачи функция (процедура) может потребовать набор аргументов (исходные данные), которые передаются ей в момент вызова.
· Функция (процедур) может возвращать результаты своих вычислений (возвращаемые данные) в вызывающую программу. Возврат значений может, в частности, производиться через список аргументов.
· Каждая функция (процедура) должна быть вызвана по имени. Если вызов отсутствует, то функция выполняться не будет.
Имя функции (процедуры)рассматривается как ее идентификатор и составляется исходя из правил составления идентификаторов конкретного языка программирования.
Можно выделить два вида функций, которые используются в программах. С одной стороны, это функции, которые созданы программистом для решения своей собственной задачи. В этом случае программист создает коды необходимой ему функции и оформляет их в соответствии с правилами языка программирования. С другой стороны, в программах могут использоваться так называемые библиотечные функции. Обычно с их помощью выполняются некие часто встречающиеся действия: некоторые математические вычисления, операции проверки типов, преобразования форматов, обработки строк, работы со временем и датами и тому подобное. Полный список имеющихся в языке функций можно получить, воспользовавшись, например, системой помощи, которая стандартно вызывается нажатием клавиши F1 при запущенной системе программирования.
При использовании библиотечных функций, обращаясь к ним в своей программе, программист использует написанные другими неизвестными ему программистами коды для решения собственной задачи. Для обеспечения работы всей системы программисту необходимо позаботиться о подключении к программе кодов библиотечных функций. Обычно это происходит на этапе редактирования связей. Заметим, что в большинстве случаев программист не имеет доступа к исходным кодам библиотечных функций и пользуется только описанием их действий (назначением функции) и описанием списка аргументов.
Под определением функции или процедурыобычно понимают создаваемую программистом последовательность операторов программы, которая после трансляции может быть вызвана из другой программы. Если в программе используются встроенные процедуры или функции, то об их определении программисту беспокоиться не приходится. Их объектные коды заранее размещены в специальных библиотеках, используемых компилятором, и автоматически подключаются к итоговой программе. Задумываться об определениях программисту надо в том случае, когда он хочет создать собственные функции или процедуры.
Формально функция в VBA может быть описана так:
[Public или Private] [Static] Function Имя [(СписокАргументов)] [As Тип]
[Операторы]
[Имя=Выражение]
[Exit Function]
[Операторы]
[Имя=Выражение]
End Function
Если указано ключевое слово Public (используется по умолчанию), процедура может быть вызвана из других процедур любых модулей. Ключевое слово Private означает, что процедура может быть вызвана только из того модуля, в котором она описана. Из соображений повышения надежности программирования рекомендуется, как правило, использовать ключ Private.
Установленный ключ Static означает, что локальные переменные процедуры сохраняют свои значения между вызовами и могут быть использованы в последующих вычислениях при следующем вызове процедуры (время жизни переменной). Из соображений повышения надежности программирования использовать ключ Static не рекомендуется
Имя функции- это обычный идентификатор языка VBA.
СписокАргументов представляет собой перечисление аргументов функции. Он имеет еще одно название: список формальных параметров. Функция может иметь один аргумент (формальный параметр) или несколько. Как и обычные переменные, формальные параметры имеют определенный тип. Их основным отличием от обычных переменных является то обстоятельство, что под их хранение не выделяется память машины, а сами они используются в определении функции только для указания последовательности действий с аргументами функции. Каждый элемент списка формальных параметров имеет следующий формат:
[Optional] [ByVal или ByRef] [ParamArray] ИмяПеременной[()] [As Тип] [=поУмолчанию]
Ключевое слово Optional означает, что элемент списка является необязательным аргументом и должен иметь тип Variant. Все последующие элементы списка должны иметь такой же ключ и тип. Необязательные аргументы могут отсутствовать в списке переменных функции при записи оператора ее вызова.
Ключ ByVal означает, что параметр передается по значению. Если задан этот ключ, то вызывающая программа может передать в функцию значение аргумента, однако изменить это значение функция никаким способом не может. Этот прием призван защитить данные вызывающей программы, и может использоваться как основной при односторонней передаче данных от вызывающей программы к функции.
Ключ ByRef (используется по умолчанию) указывает, что параметр передается по ссылке. Это означает, что функции известен физический адрес памяти формального параметра. При необходимости функция может произвести запись по этому адресу (например, оператором присваивания). Подобный прием оказывается удобным для возврата результатов вычислений функции в вызывающую программу через список формальных параметровв том случае, когда оказывается необходимым вернуть больше одного параметра. При использовании процедур это вообще единственный способ возврата результатов вычислений.
Ключевое слово ParamArray может быть использовано только с последним элементом списка формальных параметров и позволяет передавать динамически объявляемый массив.
Ключ Тип представляет собой тип передаваемого параметра (табл. 2.2), а значение поУмолчанию может использоваться только с ключом Optional и задает значение переменной.
После заголовка функции следует конечное число обычных операторов языка VBA, представляющих собой тело определения функции. Если в их состав входит оператор объявления переменных Dim, то имеет место объявление собственных локальных переменных функции. Если в заголовке функции не указан ключ Static, то эти переменные не сохраняют свои значения между вызовами, и каждый раз значения в них должны записываться заново. Кроме операторов объявления, в состав тела определения могут входить операторы присваивания, цикла и другие. В качестве их аргументов могут выступать как локальные переменные, константы, так и формальные параметры. Последние выступают как полноправные участники любых операций и операторов с той лишь оговоркой, что свое конкретное значение они получат только в момент вызова.
Результатом работы функции является некое значение, например число, которое вычисляется в теле функции. Возвращаемое значение должно иметь некий тип, указанный в заголовке функции как As Тип, соответствующий типу возвращаемого функцией значения. Для указания того, что все-таки является результатом вычислений функции и должно быть возвращено в вызывающую программу, в определении функции записывается отдельный оператор присваивания. В его левой части указывается Имя функции(из ее заголовка), а в правой ‑ возвращаемое значение.
Формальное описание процедуры в VBA похоже на формальное описание функции и имеет вид:
[Public или Private] [Static] Sub Имя [(СписокАргументов)]
[Операторы]
End Sub
Формат элементов списка формальных параметров процедуры аналогичен формату формальных параметров функции. Таким образом, кроме ключевых слов заголовка и окончания, единственным принципиальным отличием определения функции от определения процедуры является наличие ее в тексте определения функции оператора [Имя=Выражение], указывающего возвращаемое в точку вызова значение.
Встречаются задачи, в которых в точку вызова необходимо возвращать не одно, а несколько значений. Поскольку функция может вернуть в вызывающую программу через оператор присваивания только одно значение, похожие задачи приходится решать способом передачи данных через список формальных параметровпо ссылке. Если формальный параметр описан с ключом ByRef, то это означает, что вызываемая функция получает в свое распоряжение не копию данных, а адрес ячейки памяти, в которой эти данные находятся. Как следствие, у вызываемой функции появляется возможность изменить содержимое ячейки памяти вызывающей программы. Для этого в определении функции оператором присваивания задаются необходимые значения формальному параметру. В момент вызова процедуры (функции) формальному параметру ставится в соответствие фактическая ячейка памяти вызывающей программы. Именно в ней и произойдут указанные в определении изменения.
Вызов процедурыв языке VBA производится из любого места основной (вызывающей) программы за счет включения в ее текст специального оператора вызова. Вызов процедуры записывается как отдельный оператор с использованием ключевого слова Call. После него должно стоять имя процедуры и список ее фактических параметров, записанный в круглых скобках. Под фактическими параметрамипонимаются имена ячеек памяти, объявленных в вызывающей программе. Очевидно, что если процедуре должно быть передано некоторое значение в виде аргумента, то вызывающая программа предварительно должна занести это значение в свою ячейку с использованием, например, оператора присваивания. Далее эта ячейка должна быть указана на соответствующем месте в списке формальных параметров.
Примечание. Альтернативным и часто используемым вариантом вызова процедур в VBA является просто запись имени процедуры с перечислением ее аргументов (фактических параметров) без заключения их в круглые скобки.
В отличие от процедуры, функция возвращает некоторое значение в точку вызова. Поэтому вызов функциипроизводится с оператором присваивания. В левой части оператора указывается имя переменной, куда должен быть записан результат вычислений функции, а в правой ее имя и в круглых скобках аргументы (фактические параметры). Фактические аргументы функции выбираются из числа ячеек вызывающей программы.
Имя функции(процедуры) заносится компилятором в таблицу идентификаторов при первом вызове или при компиляции ее кодов, оформленных в виде текста программы и соответствующих заголовков с окончаниями (Sub Имя ([Аргументы]) [Операторы тела функции] End Sub или Function Имя ([Аргументы]) As Тип [Операторы тела функции] End Function).
Пример 17. Пример программы, реализующей задачу рис. 1 использованием процедуры задания цвета шрифта в ячейке Excel.
Sub Расчет_заработной_платы6()
Dim Начислено(1 To 4) As Currency, Налог(1 To 4) As Currency, _
К_Выдаче(1 To 4) As Currency, i As Integer
For i = 1 To 4
Начислено(i) = Cells(i + 1, 2) 'В первую ячейкe массива Начислено записывается
'содержимое второй строки и второй колонки исходной таблицы Excel
If Начислено(i) > 1000000 Then
Начислено(i) = 1000000
Call Изменение_цвета_шрифта_в_ячейке(i + 1, 2, "Желтый") 'Вызов процедуры с
' ключевым словом Call. Параметры процедуры заключены в круглые скобки
Else
End If
If Начислено(i) < 0 Then
Начислено(i) = 0
Изменение_цвета_шрифта_в_ячейке i + 1, 2, "Красный" 'Вызов процедуры без
' ключевого слова Call. Параметры процедуры в круглые скобки не заключаются
Else
Call Изменение_цвета_шрифта_в_ячейке(i + 1, 2, "Сброс")
End If
Налог(i) = Начислено(i) * 0.12 'Рассчитывается значение налога и запоминается
'в соответствующей ячейке
Cells(i + 1, 3) = Налог(i) 'Значение налога возвращается в таблицу Excel
К_Выдаче(i) = Начислено(i) - Налог(i) 'Рассчитывается значение к выдаче
'и запоминается в соответствующей ячейке
Cells(i + 1, 4) = К_Выдаче(i) 'Значение к выдаче возвращается в таблицу Excel
Next i
End Sub
Sub Изменение_цвета_шрифта_в_ячейке(Строка As Integer, Столбец As Integer, Цвет As String)
Dim C As Integer
Select Case Цвет
Case "Красный": C = 3
Case "Желтый": C = 6
Case "Зеленый": C = 10
Case Else: C = 0 'Автоматический выбор (Авто)
End Select
Cells(Строка, Столбец).Font.ColorIndex = C
End Sub
Примечание. Интегрированная среда разработки VBA в окне редактора кодов предлагает в качестве сервиса возможность указания имени переменной и типа данных при наборе операторов вызова функции или процедуры. Если функция или процедура ранее была объявлена и была выполнена компиляция проекта, после набора ее имени всплывает перечень ее аргументов.
При программировании в Excel иногда возникает необходимость создания дополнительных по отношению к стандартному библиотечному набору функций. Такие функции могут быть запрограммированы в ячейках таблицы обычным для Excel способом (Вставка, Функция…). Далее программист выбирает нужную ему Категорию функции, в соответствии с которой произведена их классификация. Если создать в модуле проекта VBA функцию, имеющую атрибут Public, используемый по умолчанию, то эта функция появится в списке библиотечных функций Excel в категории Определенные пользователем. Этот прием оказывается удобным для введения в систему Excel дополнительных возможностей программирования нестандартных, но многократно используемых действий. Так с его помощью легко реализовать возможности операторов ветвления и цикла, которые в обычной конфигурации оказываются недоступными.
Пример 18. Пример создания пользовательской функции Excel на VBA.
'Функция, определенная пользователем
Public Function Расчет_налога(Начислено As Integer)
Расчет_налога = Начислено * 0.12
End Function
Задание
Используйте согласованный с преподавателем вариант задания (табл. 1), выполненную на его основе таблицу Excel, написанную программу вычислений в таблице с использованием переменных VBA. Модифицируйте созданную вами программу так, чтобы перенести часть вычислений в процедуру или функцию. При этом исходные данные первоначально должны быть считаны из таблицы Excel, а результаты вычислений возвращены в нее.