Основные принципы и концепции программирования на языке VBA в Excel

Процедуры, подпрограммы и функции

Разбить на страницы
Показывать лекцию целиком

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

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

Преимущества

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

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

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

    Важно

  • Все исполняемые операторы модуля размещаются в процедурах.
  • Вне процедур в начале модуля могут находиться только опции (например, Option Explicit ), объявления модульных, глобальных переменных и переменных пользовательского типа.
    •

    Классификация процедур

    Обычно в составе проекта присутствуют

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

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

    Замечание

  • Под понятием "функция возвращает значение" подразумевается, что в процессе выполнения процедуре-функции присваивается некоторое значение, которое доступно вызвавшей ее процедуре.
    •

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

    Процедуры обработки событий (событийные процедуры) связаны с конкретными событиями. Событие, например, - это нажатие командной кнопки в диалоге, закрытие или активизация окна таблицы. Если установлена связь процедуры с событием объекта приложения, то процедура является событийной. Особенность событийных процедур в том, что они запускаются автоматически, если происходит некоторое событие.

    Удобно

  • написать автоматически запускаемые процедуры со специальными именами, например: Auto_Open - автопоцедура, выполняемая при открытии документа.
    •

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

    Структура и объявление процедуры

    Каждая процедура начинается с оператора объявления процедуры (функции) Sub (Function) и заканчивается оператором завершения процедуры (функции) End Sub (End Function). Все операторы, заключенные между этими двумя операторами, составляют тело процедуры (функции).

    При вставке новой процедуры с использованием команды Insert-Procedure в окне кода автоматически появляются команды начала и окончания процедуры (функции) - достаточно в диалоге задать название новой процедуры и выбрать ее тип.

    Выход из процедуры обычно происходит естественным образом - при достижении оператора окончания процедуры (функции). Один или несколько операторов немедленного выхода Exit Sub (Exit Function) могут содержаться внутри процедуры (функции).

    Запомните

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

    Синтаксис объявления процедуры общего типа

    Оператор объявления процедуры присваивает ей имя и перечисляет формальные параметры процедуры. Синтаксис

    [Private|Public][Static] Sub name ([arglist])
  • Private или Public (указывается одно из двух) определяют область видимости процедуры:
  • Private определяет, что процедура доступна только в том модуле, в котором она объявлена;
  • Public объявляет процедуру доступной во всех модулях текущего проекта и во всех модулях любого проекта, связанного с данным. Это означает, что процедура может быть вызвана из любой процедуры любого модуля.
    •

    Важно

  • Если в модуле присутствует инструкция Option Private, то процедуры, размещенные в этом модуле, не доступны вне проекта.
  • Если Private и Public опущены, то процедура считается общедоступной - Public.
    •
  • Static указывает, что все локальные переменные процедуры сохраняют свои значения между вызовами процедуры.
  • name - имя процедуры, удовлетворяющее стандартам на имена в языке VB. Имя процедуры должно быть уникальным в пределах модуля.
  • ,). Список параметров необязателен. При отсутствии параметров после имени процедуры следуют открывающая и закрывающая скобки.
    •

    Синтаксис объявления функции

    Оператор объявления функции присваивает ей имя, перечисляет ее параметры и устанавливает тип возвращаемого значения. Синтаксис

    [Private|Public][Static] Function name ([arglist]) [As Type]

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

    Важно

  • Определение типа передаваемого значения позволяет повысить эффективность программы.
  • Если тип возвращаемого значения не указан, то VBA трактует его как Variant.
    •

    Вызов процедуры

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

  • Чтобы вызвать процедуру общего типа или процедуру-функцию без параметров, достаточно записать ее имя в вызывающей процедуре: proc_A или b=func_b.
  • Фактические параметры процедуры общего типа (аргументы) отделяются от имени процедуры пробелом и перечисляются через запятую: proc_A arg1, arg2.
  • Аргументы функции перечисляются в скобках после имени функции: func_b(arg1, arg2).
  • Порядок перечисления аргументов соответствует порядку формальных параметров процедуры (функции).
  • Для вызова процедуры общего типа можно воспользоваться оператором Call, задавая значения аргументов в скобках Call proc_A(arg1, Arg2).
  • Процедура-функция не может быть выполнена командой Сервис-Макрос-Макросы, а может быть только вызвана другой процедурой или функцией.
  • Так как процедура-функция возвращает значение, она используется в выражениях, например, в операторе присваивания.
  • Процедура общего типа не может быть использована в выражениях, но может быть выполнена командой Сервис-Макрос-Макросы.
    •

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

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

    Важно

  • Поиск процедуры производится сначала в текущем модуле, а затем в других модулях проекта или других открытых проектах.
  • Если имя процедуры не является уникальным (в разных модулях проекта или в разных проектах объявлены процедуры с одинаковыми именами), то при вызове процедуры необходимо уточнить, о какой именно процедуре идет речь.
    •

    Это производится стандартным способом - в операторе вызова процедуры указывается составное имя процедуры, которое строится из имени проекта, имени модуля и имени процедуры, разделенных точкой:

    ProjectName.ModuleName.ProcedureName

    Например, вызов процедуры Proc_A, расположенной в модуле Module1 проекта Project1 можно записать так: Project1.Module1.Proc_A.

    Замечание

  • Имя проекта не является именем рабочей книги.
    •

    Пример

    Процедура высвечивает приветствие после ввода пользователем своего имени.

    (рис 7.1) Пример основной и вызываемой процедуры

    В результате выполнения операторов 1-го и 2-го способов высвечивается приветствие "Hello, Conrad".

    Параметры и аргументы

    Через список параметров осуществляется связь между вызывающей и вызываемой процедурами. Параметр:

  • описывается при объявлении процедуры;
  • представляет собой переменную.
    •

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

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

    Пример

    Для первых пяти натуральных чисел рассчитать квадрат и куб числа (рис 7.2).

    Основная процедура Proc_param организует цикл на пять чисел и вызывает процедуру Proc_print для распечатки в окне Immediate самого числа, его квадрата и куба.

    (рис 7.2) Пример основной и вызываемой процедуры для расчета куба и квадрата числа

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

    Call Proc_print (i) - альтернативная запись вызова процедуры.

    Замечания

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

    Синтаксис описания каждого параметра процедуры имеет следующий вид:

    [[Optional][ByVal|ByRef][ParamArray] varname[( )] As type][= defaultvalue]
  • Optional - ключевое слово. Указывает, что параметр может быть опущен.
    •

    Важно

  • Необязательные параметры всегда размещаются в конце списка.
  • Тип необязательного параметра - Variant.
  • При наличии необязательного параметра в процедуре необходимо предусмотреть использование значения по умолчанию, если значение аргумента не задано.
  • В процедуре можно проверить, передается необязательный параметр или нет, используя функию IsMissing(argname). Она возвращает значение True, если значение параметра опущено, и False - в противном случае.
    •
  • Указывается одно из двух ByVal или ByRef. - передача параметра по значению. Предполагается, что при вызове процедуры передается только значение переменной и, следовательно, в процессе выполнения процедуры оно не может быть изменено. Используйте этот способ передачи аргументов, чтобы избежать случайной модификации передаваемых данных.
  • Указывается одно из двух ByVal или ByRef. - передача параметров по ссылке. Предполагается, что процедуре передается адрес переменной. В этом случае вызывающая процедура может не только использовать переданное значение переменной, но и изменить его в процессе выполнения. Используется по умолчанию.
  • ParamArray означает передачу необязательного аргумента - массива типа Variant с неопределенным количеством элементов.
    •

    Важно

    Ключевое слово ParamArray

  • используется только для последнего параметра процедуры;
  • позволяет задавать произвольное количество передаваемых аргументов;
  • не допускает использование ключевых слов ByVal, ByRef и Optional для описываемого параметра.
    •
  • Varname - идентификатор переменной.
  • type - тип передаваемого аргумента. Если тип данных не указан, то VBA трактует аргумент как Variant.
    •

    Важно

  • Разрешены все элементарные типы данных и типы, определенные пользователем.
  • Aргумент типа String должен иметь переменную длину.
  • Если для параметра процедуры описан тип данных, то передаваемый аргумент должен иметь тот же тип данных.
  • Если параметром процедуры является массив, то передаваемый массив должен иметь ту же размерность.
    •

    Нарушение одного из перечисленных условий может вызвать ошибку выполнения. Определение типа передаваемого значения позволяет повысить эффективность программы и избежать ошибок, связанных с неверно переданным аргументом.
  • Value on default - для необязательных аргументов устанавливает значение по умолчанию.
    •

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

    Пример

    Для сотрудников фирмы (максимально 2000 сотрудников) рассчитать выплаты в соответствии с количеством отработанных часов и ставкой оплаты за час работы. Налог составляет 13%. Если зарплата превышает 300$, то страховой взнос составляет 5%.

    Процедура salary_employee (основная) запрашивает ввод количества отработанных часов и размер почасовой оплаты максимально для 2000 сотрудников и передает по значению эти данные в процедуру salary_em_proc.

    Вызываемая процедура salary_em_proc рассчитывает и распечатывает денежную выплату. В вызываемой процедуре salary_em_proc определены три параметра. Это io - код сотрудника, ho - количество часов, ra - ставка почасовой оплаты.

    (рис 7.3) Передача аргументов по значению

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

    В основной процедуре salary_employee объявите переменную AC (Dim AC as Double), в которой будет накапливаться суммарная выплата. В вызываемой процедуре введите четвертый параметр SI - накапливаемая сумма. Этот параметр передается по ссылке, т.к. его значение изменяется в вызываемой процедуре. Оператор вызова процедуры будет выглядеть так: salary_em_sum id, h, r, AC.

    (рис 7.4) Передача аргумента по ссылке (параметр SI)

    Возврат значения функции

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

    function name=expression

    Если значение явно не присваивается, то оно устанавливается по умолчанию в соответствии с типом возвращаемого значения: 0 - для числовых типов, строку нулевой длины - для строковых переменных, Empty - для типа Variant и Nothing - для ссылок на объекты.

    Замечание

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

    Пример

    Функция возвращает количество выделенных ячеек, если тип выделения - объект Range (подробно об объекте Range см. Лекцию 8). Если выделен объект другого типа, например, встроенная диаграмма или рисунок, то возвращаемое значение - символьная строка с сообщением о некорректности выделения.

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

    В вызывающей процедуре вычисленное функцией значение сохраняется на активном листе в ячейках D1 (количество ячеек в интервале A1:C5 ) и D2 (сообщение о некорректности выделения).

    (рис 7.5) Пример сохранения вычисленного функцией значения на активном листе

    Использование процедур-функций на рабочем листе

    Созданные пользователем функции можно использовать не только в процедурах VBA, но и на рабочем листе MS Excel как обычные встроенные функции.

    При построении формулы с помощью мастера функций пользовательские функции можно найти в категории Определенные пользователем (User Defined).

    Пример

    Создать функцию расчета выплат, учитывая, что налог составляет 13% и, если зарплата превышает 300 $, то в страховой фонд дополнительно взимается 5%.

    В отличие от процедуры salary_em_proc функция salary_em_ возвращает рассчитанное значение. Возврат значения происходит при помощи операторов присваивания, в которых слева используется имя функции. В вызывающей процедуре salary_employee вместо оператора вызова процедуры salary_em_proc io, h, r записан оператор, распечатывающий рассчитанную выплату как MsgBox io " salary " salary_em(h, r).

    (рис 7.6) Пример высвечивания в диалоге значения, вычисленного функцией

    Функцию salary_em_ можно использовать как в процедурах VBA, так и на рабочем листе, как пользовательскую функцию.

    На рис 7.7 в ячейку E3 введены отработанные часы, а в ячейку F3 - ставка оплаты за час. В ячейке G3 рассчитана выплата по формуле =salary_em_(E3;F3).

    (рис 7.7) Использование функции salary_em_ как пользовательской функции на рабочем листе

    Замечания

  • Разделитель между аргументами при записи функций в ячейках рабочего листа русифицированной версии MS Excel - точка с запятой ( ,).
  • Не рекомендуется включать в пользовательские функции операторы, выполняющие какие-либо действия с объектами таблиц, так как при использовании подобной функции на рабочем листе может возникнуть ошибка. Для действий с объектами таблиц применяйте вызываемые процедуры.
    •

    Поименованные аргументы

    При задании аргументов процедур можно использовать либо позиционное, либо произвольное расположение аргументов.

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

    Использование поименованных аргументов исключает влияние позиции аргумента на его интерпретацию. Для задания поименованных аргументов после названия формального параметра вызываемой процедуры поставьте оператор назначения - двоеточие и знак равенства ( := ), за которым укажите передаваемое значение аргумента.

    Преимущества

  • Поименованные аргументы улучшают читабельность программы.
  • Исключаются ошибки, вызванные неверным порядком аргументов.
  • Облегчается перечисление аргументов в случае пропуска необязательных аргументов.
    •

    Используя поименованные аргументы, к функции расчета выплат можно было бы обратиться так: salary_em(ra:=r, ho:=h).

    Пример

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

    Диалоговое окно запроса на ввод имени и фамилии размещается на экране, при этом используются координаты xpos=100 и ypos=100. В первом операторе InputBox применяются позиционные аргументы - пропущенные два аргумента отмечены запятыми. Во втором операторе InputBox используются поименованные аргументы.

    (рис 7.8) Использование поименованных аргументов при вызове функции

    Функция Left выделяет первую букву имени. Подробно об аргументах функций InputBox и Left см. ниже.

    Использование необязательных аргументов

    Ключевое слово Optional, задаваемое при описании параметров процедуры, предполагает, что значение параметра не обязательно будет передано в процедуру. Иными словами, аргумент возможен, но необязателен.

    Пример

    Функция рассчитывает налоговые выплаты и имеет два параметра S - доход, R - ставка налога. Если ставка налога не задана, то налог рассчитывается, исходя из 13%.

  • Оператор (рис 7.9) Вызов функции без указания необязательного параметра
  • Оператор (рис 7.10) Задание необязательного параметра при вызове функции
    •

    Использование параметра ParamArray

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

    Замечание

  • Используйте функцию Ubound для определения количества элементов в передаваемом массиве.
    •

    Пример

    Создать функцию умножения аргументов.

    Для умножения произвольного количества аргументов можно предложить два варианта функций:

  • с вычислением верхней границы передаваемого массива;
  • без вычисления верхней границы передаваемого массива.
    •

    В вызывающей процедуре mult_arr создается массив из восьми чисел, элементы которого перемножаются при помощи функций multr или multa.

    Результат умножения выводится в окно Immediate Window.

    Напомним, что нижняя граница массива равна 0, если инструкция OptionBase не указывает на другое.

    (рис 7.11) Вызывающая процедура и оба способа записи функции

    Автопроцедуры

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

  • Процедура с именем Auto_Open автоматически запускается при открытии рабочей книги или приложения MS Excel.
  • Процедура с именем Auto_Close автоматически запускается при закрытии рабочей книги или приложения MS Excel.
    •

    Обычно в этих процедурах создаются панели инструментов, разрешается или отменяется их высвечивание, производятся настройки экрана или изменяются меню.

    Запомните

  • Если процедуры с указанными именами записываются в Личную книгу макросов (Personal Macro Workbook), то они автоматически выполняются при открытии или закрытии приложения.
  • Нажатие клавиши Shift при открытии или при закрытии рабочей книги предотвращает выполнение автоматической процедуры.
    •

    Событийные процедуры

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

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

    Событийные процедуры имеют составные имена: название или тип объекта и название события, разделенные нижним подчеркиванием ( _ ). Например, процедура с именем Workbook_Open выполняется при открытии рабочей книги, для которой эта процедура написана. Имена присваиваются событийным процедурам автоматически.

    Пример

    На рис 7.12 в окне проекта выбрана рабочая книга (объект Эта книга ). В списке объектов на процедурном листе рабочей книги выбран объект Workbook. Как следствие в списке процедур раскрыт список событий объекта Workbook.

    (рис 7.12) Выбор события Open для объекта Эта книга

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

    При открытии рабочей книги iserror название рабочей книги и активного рабочего листа помещаются в ячейки таблицы A1 и A2 соответственно.

    (рис 7.13) Результат выполнения событийной процедуры открытия рабочей книги iserror

    Встроенные функции

    Классы функций

    Язык Visual Basic имеет большое число встроенных функций. Это математические функции, функции для строковых переменных, функции даты и времени, функции преобразования данных, финансовые и информационные функции.

    (рис 7.14) Классы функций и перечень строковых функций

    Классы функций можно увидеть в Object Browser, выбрав библиотеку VBA.

    Дополнительно можно задать название класса функций на английском языке и выполнить поиск.

    Использование табличных функций

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

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

    ВНИМАНИЕ

  • Независимо от русскоязычной или англоязычной версий MS Office названия функций VBА и функций рабочего листа в процедурах записываются на английском языке.
    •

    Пример

    Рассчитать максимум из 4-х чисел и их среднее арифметическое.

    Процедура использует две функции рабочего листа: Max и Average. Ниже в примере приведены оба способа ссылок на функции рабочего листа.

    (рис 7.15) Уточнения при использовании табличных функций

    Строковые функции

    Visual Basic располагает большим набором встроенных функций для обработки алфавитно-цифровых (символьных) данных. Многие из них совпадают с табличными. Но есть табличные строковые функции, отсутствующие в языке VBA, и наоборот.

    Строковые функции VBA
    Название и синтаксис функции Описание Примеры
    Asc(string) Возвращает ASCII-код символа Asc("*") возвращает 42
    Chr(charcode) Возвращает символ по ASCII-коду Chr(42) возвращает *
    Format(еxpression [,format][,firstdayofweek][,firstweekofyear]) Преобразует аргумент в соответствии с заданным форматом Format(42.399, "#.00") возвращает 42,40

    Format("HELLO, MY FREND", "<") возвращает hello, my frend
    Hex(number) Возвращает шестнадцатиричное представление числа Hex(18) возвращает 12

    Hex(31) возвращает 1F
    InStr([start,] string1, string2[,compare]) Ищет подстроку в прямом направлении InStr(1, "To be or not to be", "be") возвращает 4
    InstrRev(stringcheck, stringmatch[, start][, compare]) Ищет подстроку в обратном направлении InstrRev("To be or not to be", "be") возвращает 17
    LCase(string) Преобразует строку в нижний регистр LCase("World") возвращает world
    Left(string, length) Выделяет левую часть строки Left("World", 2) возвращает Wo
    Len(string) Определяет длину строки Len("World") возвращает 5
    LTrim(string) Удаляет ведущие пробелы

    Пробелы справа сохраняются

    		 LTrim(" extra spaces ") возвращает extra spaces без левых пробелов
    Mid(string, start[, length]) Выделяет подстроку Mid("World", 2,3) возвращает orl
    Oct(number) Возвращает восьмеричное представление числа Oct(12) возвращает 14
    Replace(expression, find, replace[,start][,count][,compare]) Заменяет подстроку на новую подстроку Replace("The car is red", "red", "blue") возвращает The car is blue
    Right(string, length) Выделяет правую часть строки Right("World",2) возвращает ld
    RTrim(string) Удаляет завершающие пробелы

    Пробелы слева сохраняются

    		 RTrim(" extra spaces ") возвращает extra spaces без правых пробелов
    Space(number) Создает строку пробелов Space(6) создает строку из 6 пробелов
    Str(number) Преобразует число в строку Str(123) возвращает строку 123
    StrComp(string1, string2[, compare]) Сравнивает две строки:
  • compare=0 двоичное сравнение
  • compare=1 текстуальное сравнение
    •      		StrComp("WORld", "world", 1) возвращает 0 (равенство строк будет обнаружено при текстуальном сравнении)
    String(number, character) Создает строку символов String (5,"*") и String (5,42) возвращают ***** . 42 - ASCII-код символа *
    Trim(string) Удаляет пробелы с двух сторон строки Trim(" extra spaces ") возвращает extra spaces без левых и правых пробелов
    UCase(string) Преобразует строку в верхний регистр UCase("World") возвращает WORLD
    Val(string) Преобразует строку в число Val("6.28") возвращает число 6.28
    Функции преобразования текстовых данных в другие типы данных
    Функция Тип результата Функция Тип результата
    CBооl Воо1еап CCиr Сиrrепсу
    CDate Date CDbl Doublе
    CInt Iпtegеr CLng Long
    CSng Single CStr String
    CVаr Variant

    В качестве параметра функциям преобразования строк передается выражение ( expression ), которое и преобразуется в выбранный тип.

    Математические функции

    Каждая математическая функция имеет один параметр - число ( number ). При использовании действительных чисел как констант разделителем целой и дробной части является точка. В разделе Derived Math Functions справочника по VBA можно найти формулы расчета математических функций, не являющихся встроенными функциями.

    Математические функции
    Функция Описание
    Abs Возвращает абсолютную величину числа
    Atn Возвращает арктангенс числа
    Cоs Возвращает косинус угла*
    Eхр Возвращает степень числа e (еx)
    Fiх Возвращает целую часть числа
    Int Округляет число до меньшего целого числа
    Log Возвращает натуральный логарифм числа (основание е = 2.71828...)
    Randomize Инициирует генератор случайных чисел
    Rnd Возвращает случайное число
    Round Округляет число до целого, если не задан аргумент numdecimalplaces (десятичный разряд, до которого производится округление)
    Sin Возвращает синус угла*
    Sgn Возвращает знак числа
    Sqr Возвращает квадратный корень из числа
    Тап Возвращает тангенс угла*
    * Углы задаются в радианах. Для перевода градусов в радианы умножьте градусы на /180.

    Функции даты и времени

    Под датой подразумевается переменная типа Date или дата-константа, которая задана согласно системному краткому формату даты с разделителями даты, определенными системной настройкой, и заключена в кавычки c использованием символа "решетка" ( # ).

    Важно

  • Переменная типа Date в памяти компьютера сохраняется как число типа Double. Целая часть числа - это дата, а дробная часть - время.
  • Полезная информация при работе с переменными типа Date: одни сутки - это 24 часа, или 1440 минут, или 86400 секунд.
    •
    Таблица функций Даты и времени
    Название и синтаксис функций Описание Пример
    Date() * Возвращает или устанавливает системную дату Date_Today=Date() запоминает системную дату

    Date = #7/26/2007# устанавливает новую системную дату
    DateSerial(year, month, day) Преобразует в дату три целых числа: год, месяц и день DateSerial(2007,7,24) возвращает 24.07.2007
    DateValue(date) Преобразует в дату символьное представление даты DateValue("24 июля 2007") возвращает 24.07.2007
    Если год не задан, то используется год из системной даты
    Day(date) Из даты выделяет день месяца Day(#7/24/2007#) возвращает 24
    Hour(time) Из заданного времени выделяет часы Hour (#4:35:17 PM#) возвращает 16
    Minute(time) Из заданного времени выделяет минуты Minute(#4:35:17 PM#) возвращает 35
    Month(date) Из даты выделяет месяц Month (#7/24/2007#) возвращает 7
    Now() * Возвращает текущую дату и текущее время Date_Today=Now()
    Second(date) Из заданного времени выделяет секунды Second (#4:35:17 PM#) возвращает 17
    Time() * Возвращает или устанавливает системное время Time_Today= Time()
    Timer() * Возвращает временной интервал в секундах от полуночи (тип Single )
    TimeSerial(hour, minute, second) Преобразует в дату три целых числа: часы, минуты и секунды TimeSerial(16, 35, 17) возвращает 16:35:17
    TimeValue(time) Преобразует в дату символьное представление времени TimeValue("4:35:17PM") возвращает время 16:35:17
    Weekday(date, [firstdayofweek]) Возвращает день недели, соответствующий заданной дате Weekday(#7/25/2007#, 2) возвращает 3 (третий день недели - среда)
    Year(date) Из даты выделяет год Year(#7/24/2007#) возвращает 2007
    * Функции не имеют аргументов и могут записываться без скобок
    Страницы:

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

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

    Преимущества

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

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

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

    Важно

  • Все исполняемые операторы модуля размещаются в процедурах.
  • Вне процедур в начале модуля могут находиться только опции (например, Option Explicit ), объявления модульных, глобальных переменных и переменных пользовательского типа.
    •

    Классификация процедур

    Обычно в составе проекта присутствуют

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

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

    Замечание

  • Под понятием "функция возвращает значение" подразумевается, что в процессе выполнения процедуре-функции присваивается некоторое значение, которое доступно вызвавшей ее процедуре.
    •

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

    Процедуры обработки событий (событийные процедуры) связаны с конкретными событиями. Событие, например, - это нажатие командной кнопки в диалоге, закрытие или активизация окна таблицы. Если установлена связь процедуры с событием объекта приложения, то процедура является событийной. Особенность событийных процедур в том, что они запускаются автоматически, если происходит некоторое событие.

    Удобно

  • написать автоматически запускаемые процедуры со специальными именами, например: Auto_Open - автопоцедура, выполняемая при открытии документа.
    •

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

    Структура и объявление процедуры

    Каждая процедура начинается с оператора объявления процедуры (функции) Sub (Function) и заканчивается оператором завершения процедуры (функции) End Sub (End Function). Все операторы, заключенные между этими двумя операторами, составляют тело процедуры (функции).

    При вставке новой процедуры с использованием команды Insert-Procedure в окне кода автоматически появляются команды начала и окончания процедуры (функции) - достаточно в диалоге задать название новой процедуры и выбрать ее тип.

    Выход из процедуры обычно происходит естественным образом - при достижении оператора окончания процедуры (функции). Один или несколько операторов немедленного выхода Exit Sub (Exit Function) могут содержаться внутри процедуры (функции).

    Запомните

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

    Синтаксис объявления процедуры общего типа

    Оператор объявления процедуры присваивает ей имя и перечисляет формальные параметры процедуры. Синтаксис

    [Private|Public][Static] Sub name ([arglist])
  • Private или Public (указывается одно из двух) определяют область видимости процедуры:
  • Private определяет, что процедура доступна только в том модуле, в котором она объявлена;
  • Public объявляет процедуру доступной во всех модулях текущего проекта и во всех модулях любого проекта, связанного с данным. Это означает, что процедура может быть вызвана из любой процедуры любого модуля.
    •

    Важно

  • Если в модуле присутствует инструкция Option Private, то процедуры, размещенные в этом модуле, не доступны вне проекта.
  • Если Private и Public опущены, то процедура считается общедоступной - Public.
    •
  • Static указывает, что все локальные переменные процедуры сохраняют свои значения между вызовами процедуры.
  • name - имя процедуры, удовлетворяющее стандартам на имена в языке VB. Имя процедуры должно быть уникальным в пределах модуля.
  • ,). Список параметров необязателен. При отсутствии параметров после имени процедуры следуют открывающая и закрывающая скобки.
    •

    Синтаксис объявления функции

    Оператор объявления функции присваивает ей имя, перечисляет ее параметры и устанавливает тип возвращаемого значения. Синтаксис

    [Private|Public][Static] Function name ([arglist]) [As Type]

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

    Важно

  • Определение типа передаваемого значения позволяет повысить эффективность программы.
  • Если тип возвращаемого значения не указан, то VBA трактует его как Variant.
    •

    Вызов процедуры

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

  • Чтобы вызвать процедуру общего типа или процедуру-функцию без параметров, достаточно записать ее имя в вызывающей процедуре: proc_A или b=func_b.
  • Фактические параметры процедуры общего типа (аргументы) отделяются от имени процедуры пробелом и перечисляются через запятую: proc_A arg1, arg2.
  • Аргументы функции перечисляются в скобках после имени функции: func_b(arg1, arg2).
  • Порядок перечисления аргументов соответствует порядку формальных параметров процедуры (функции).
  • Для вызова процедуры общего типа можно воспользоваться оператором Call, задавая значения аргументов в скобках Call proc_A(arg1, Arg2).
  • Процедура-функция не может быть выполнена командой Сервис-Макрос-Макросы, а может быть только вызвана другой процедурой или функцией.
  • Так как процедура-функция возвращает значение, она используется в выражениях, например, в операторе присваивания.
  • Процедура общего типа не может быть использована в выражениях, но может быть выполнена командой Сервис-Макрос-Макросы.
    •

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

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

    Важно

  • Поиск процедуры производится сначала в текущем модуле, а затем в других модулях проекта или других открытых проектах.
  • Если имя процедуры не является уникальным (в разных модулях проекта или в разных проектах объявлены процедуры с одинаковыми именами), то при вызове процедуры необходимо уточнить, о какой именно процедуре идет речь.
    •

    Это производится стандартным способом - в операторе вызова процедуры указывается составное имя процедуры, которое строится из имени проекта, имени модуля и имени процедуры, разделенных точкой:

    ProjectName.ModuleName.ProcedureName

    Например, вызов процедуры Proc_A, расположенной в модуле Module1 проекта Project1 можно записать так: Project1.Module1.Proc_A.

    Замечание

  • Имя проекта не является именем рабочей книги.
    •

    Пример

    Процедура высвечивает приветствие после ввода пользователем своего имени.

    (рис 7.1) Пример основной и вызываемой процедуры

    В результате выполнения операторов 1-го и 2-го способов высвечивается приветствие "Hello, Conrad".

    Параметры и аргументы

    Через список параметров осуществляется связь между вызывающей и вызываемой процедурами. Параметр:

  • описывается при объявлении процедуры;
  • представляет собой переменную.
    •

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

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

    Пример

    Для первых пяти натуральных чисел рассчитать квадрат и куб числа (рис 7.2).

    Основная процедура Proc_param организует цикл на пять чисел и вызывает процедуру Proc_print для распечатки в окне Immediate самого числа, его квадрата и куба.

    (рис 7.2) Пример основной и вызываемой процедуры для расчета куба и квадрата числа

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

    Call Proc_print (i) - альтернативная запись вызова процедуры.

    Замечания

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

    Синтаксис описания каждого параметра процедуры имеет следующий вид:

    [[Optional][ByVal|ByRef][ParamArray] varname[( )] As type][= defaultvalue]
  • Optional - ключевое слово. Указывает, что параметр может быть опущен.
    •

    Важно

  • Необязательные параметры всегда размещаются в конце списка.
  • Тип необязательного параметра - Variant.
  • При наличии необязательного параметра в процедуре необходимо предусмотреть использование значения по умолчанию, если значение аргумента не задано.
  • В процедуре можно проверить, передается необязательный параметр или нет, используя функию IsMissing(argname). Она возвращает значение True, если значение параметра опущено, и False - в противном случае.
    •
  • Указывается одно из двух ByVal или ByRef. - передача параметра по значению. Предполагается, что при вызове процедуры передается только значение переменной и, следовательно, в процессе выполнения процедуры оно не может быть изменено. Используйте этот способ передачи аргументов, чтобы избежать случайной модификации передаваемых данных.
  • Указывается одно из двух ByVal или ByRef. - передача параметров по ссылке. Предполагается, что процедуре передается адрес переменной. В этом случае вызывающая процедура может не только использовать переданное значение переменной, но и изменить его в процессе выполнения. Используется по умолчанию.
  • ParamArray означает передачу необязательного аргумента - массива типа Variant с неопределенным количеством элементов.
    •

    Важно

    Ключевое слово ParamArray

  • используется только для последнего параметра процедуры;
  • позволяет задавать произвольное количество передаваемых аргументов;
  • не допускает использование ключевых слов ByVal, ByRef и Optional для описываемого параметра.
    •
  • Varname - идентификатор переменной.
  • type - тип передаваемого аргумента. Если тип данных не указан, то VBA трактует аргумент как Variant.
    •

    Важно

  • Разрешены все элементарные типы данных и типы, определенные пользователем.
  • Aргумент типа String должен иметь переменную длину.
  • Если для параметра процедуры описан тип данных, то передаваемый аргумент должен иметь тот же тип данных.
  • Если параметром процедуры является массив, то передаваемый массив должен иметь ту же размерность.
    •

    Нарушение одного из перечисленных условий может вызвать ошибку выполнения. Определение типа передаваемого значения позволяет повысить эффективность программы и избежать ошибок, связанных с неверно переданным аргументом.
  • Value on default - для необязательных аргументов устанавливает значение по умолчанию.
    •

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

    Пример

    Для сотрудников фирмы (максимально 2000 сотрудников) рассчитать выплаты в соответствии с количеством отработанных часов и ставкой оплаты за час работы. Налог составляет 13%. Если зарплата превышает 300$, то страховой взнос составляет 5%.

    Процедура salary_employee (основная) запрашивает ввод количества отработанных часов и размер почасовой оплаты максимально для 2000 сотрудников и передает по значению эти данные в процедуру salary_em_proc.

    Вызываемая процедура salary_em_proc рассчитывает и распечатывает денежную выплату. В вызываемой процедуре salary_em_proc определены три параметра. Это io - код сотрудника, ho - количество часов, ra - ставка почасовой оплаты.

    (рис 7.3) Передача аргументов по значению

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

    В основной процедуре salary_employee объявите переменную AC (Dim AC as Double), в которой будет накапливаться суммарная выплата. В вызываемой процедуре введите четвертый параметр SI - накапливаемая сумма. Этот параметр передается по ссылке, т.к. его значение изменяется в вызываемой процедуре. Оператор вызова процедуры будет выглядеть так: salary_em_sum id, h, r, AC.

    (рис 7.4) Передача аргумента по ссылке (параметр SI)

    Возврат значения функции

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

    function name=expression

    Если значение явно не присваивается, то оно устанавливается по умолчанию в соответствии с типом возвращаемого значения: 0 - для числовых типов, строку нулевой длины - для строковых переменных, Empty - для типа Variant и Nothing - для ссылок на объекты.

    Замечание

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

    Пример

    Функция возвращает количество выделенных ячеек, если тип выделения - объект Range (подробно об объекте Range см. Лекцию 8). Если выделен объект другого типа, например, встроенная диаграмма или рисунок, то возвращаемое значение - символьная строка с сообщением о некорректности выделения.

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

    В вызывающей процедуре вычисленное функцией значение сохраняется на активном листе в ячейках D1 (количество ячеек в интервале A1:C5 ) и D2 (сообщение о некорректности выделения).

    (рис 7.5) Пример сохранения вычисленного функцией значения на активном листе

    Использование процедур-функций на рабочем листе

    Созданные пользователем функции можно использовать не только в процедурах VBA, но и на рабочем листе MS Excel как обычные встроенные функции.

    При построении формулы с помощью мастера функций пользовательские функции можно найти в категории Определенные пользователем (User Defined).

    Пример

    Создать функцию расчета выплат, учитывая, что налог составляет 13% и, если зарплата превышает 300 $, то в страховой фонд дополнительно взимается 5%.

    В отличие от процедуры salary_em_proc функция salary_em_ возвращает рассчитанное значение. Возврат значения происходит при помощи операторов присваивания, в которых слева используется имя функции. В вызывающей процедуре salary_employee вместо оператора вызова процедуры salary_em_proc io, h, r записан оператор, распечатывающий рассчитанную выплату как MsgBox io " salary " salary_em(h, r).

    (рис 7.6) Пример высвечивания в диалоге значения, вычисленного функцией

    Функцию salary_em_ можно использовать как в процедурах VBA, так и на рабочем листе, как пользовательскую функцию.

    На рис 7.7 в ячейку E3 введены отработанные часы, а в ячейку F3 - ставка оплаты за час. В ячейке G3 рассчитана выплата по формуле =salary_em_(E3;F3).

    (рис 7.7) Использование функции salary_em_ как пользовательской функции на рабочем листе

    Замечания

  • Разделитель между аргументами при записи функций в ячейках рабочего листа русифицированной версии MS Excel - точка с запятой ( ,).
  • Не рекомендуется включать в пользовательские функции операторы, выполняющие какие-либо действия с объектами таблиц, так как при использовании подобной функции на рабочем листе может возникнуть ошибка. Для действий с объектами таблиц применяйте вызываемые процедуры.
    •

    Поименованные аргументы

    При задании аргументов процедур можно использовать либо позиционное, либо произвольное расположение аргументов.

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

    Использование поименованных аргументов исключает влияние позиции аргумента на его интерпретацию. Для задания поименованных аргументов после названия формального параметра вызываемой процедуры поставьте оператор назначения - двоеточие и знак равенства ( := ), за которым укажите передаваемое значение аргумента.

    Преимущества

  • Поименованные аргументы улучшают читабельность программы.
  • Исключаются ошибки, вызванные неверным порядком аргументов.
  • Облегчается перечисление аргументов в случае пропуска необязательных аргументов.
    •

    Используя поименованные аргументы, к функции расчета выплат можно было бы обратиться так: salary_em(ra:=r, ho:=h).

    Пример

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

    Диалоговое окно запроса на ввод имени и фамилии размещается на экране, при этом используются координаты xpos=100 и ypos=100. В первом операторе InputBox применяются позиционные аргументы - пропущенные два аргумента отмечены запятыми. Во втором операторе InputBox используются поименованные аргументы.

    (рис 7.8) Использование поименованных аргументов при вызове функции

    Функция Left выделяет первую букву имени. Подробно об аргументах функций InputBox и Left см. ниже.

    Использование необязательных аргументов

    Ключевое слово Optional, задаваемое при описании параметров процедуры, предполагает, что значение параметра не обязательно будет передано в процедуру. Иными словами, аргумент возможен, но необязателен.

    Пример

    Функция рассчитывает налоговые выплаты и имеет два параметра S - доход, R - ставка налога. Если ставка налога не задана, то налог рассчитывается, исходя из 13%.

  • Оператор (рис 7.9) Вызов функции без указания необязательного параметра
  • Оператор (рис 7.10) Задание необязательного параметра при вызове функции
    •

    Использование параметра ParamArray

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

    Замечание

  • Используйте функцию Ubound для определения количества элементов в передаваемом массиве.
    •

    Пример

    Создать функцию умножения аргументов.

    Для умножения произвольного количества аргументов можно предложить два варианта функций:

  • с вычислением верхней границы передаваемого массива;
  • без вычисления верхней границы передаваемого массива.
    •

    В вызывающей процедуре mult_arr создается массив из восьми чисел, элементы которого перемножаются при помощи функций multr или multa.

    Результат умножения выводится в окно Immediate Window.

    Напомним, что нижняя граница массива равна 0, если инструкция OptionBase не указывает на другое.

    (рис 7.11) Вызывающая процедура и оба способа записи функции

    Автопроцедуры

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

  • Процедура с именем Auto_Open автоматически запускается при открытии рабочей книги или приложения MS Excel.
  • Процедура с именем Auto_Close автоматически запускается при закрытии рабочей книги или приложения MS Excel.
    •

    Обычно в этих процедурах создаются панели инструментов, разрешается или отменяется их высвечивание, производятся настройки экрана или изменяются меню.

    Запомните

  • Если процедуры с указанными именами записываются в Личную книгу макросов (Personal Macro Workbook), то они автоматически выполняются при открытии или закрытии приложения.
  • Нажатие клавиши Shift при открытии или при закрытии рабочей книги предотвращает выполнение автоматической процедуры.
    •

    Событийные процедуры

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

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

    Событийные процедуры имеют составные имена: название или тип объекта и название события, разделенные нижним подчеркиванием ( _ ). Например, процедура с именем Workbook_Open выполняется при открытии рабочей книги, для которой эта процедура написана. Имена присваиваются событийным процедурам автоматически.

    Пример

    На рис 7.12 в окне проекта выбрана рабочая книга (объект Эта книга ). В списке объектов на процедурном листе рабочей книги выбран объект Workbook. Как следствие в списке процедур раскрыт список событий объекта Workbook.

    (рис 7.12) Выбор события Open для объекта Эта книга

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

    При открытии рабочей книги iserror название рабочей книги и активного рабочего листа помещаются в ячейки таблицы A1 и A2 соответственно.

    (рис 7.13) Результат выполнения событийной процедуры открытия рабочей книги iserror

    Встроенные функции

    Классы функций

    Язык Visual Basic имеет большое число встроенных функций. Это математические функции, функции для строковых переменных, функции даты и времени, функции преобразования данных, финансовые и информационные функции.

    (рис 7.14) Классы функций и перечень строковых функций

    Классы функций можно увидеть в Object Browser, выбрав библиотеку VBA.

    Дополнительно можно задать название класса функций на английском языке и выполнить поиск.

    Использование табличных функций

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

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

    ВНИМАНИЕ

  • Независимо от русскоязычной или англоязычной версий MS Office названия функций VBА и функций рабочего листа в процедурах записываются на английском языке.
    •

    Пример

    Рассчитать максимум из 4-х чисел и их среднее арифметическое.

    Процедура использует две функции рабочего листа: Max и Average. Ниже в примере приведены оба способа ссылок на функции рабочего листа.

    (рис 7.15) Уточнения при использовании табличных функций

    Строковые функции

    Visual Basic располагает большим набором встроенных функций для обработки алфавитно-цифровых (символьных) данных. Многие из них совпадают с табличными. Но есть табличные строковые функции, отсутствующие в языке VBA, и наоборот.

    Строковые функции VBA
    Название и синтаксис функции Описание Примеры
    Asc(string) Возвращает ASCII-код символа Asc("*") возвращает 42
    Chr(charcode) Возвращает символ по ASCII-коду Chr(42) возвращает *
    Format(еxpression [,format][,firstdayofweek][,firstweekofyear]) Преобразует аргумент в соответствии с заданным форматом Format(42.399, "#.00") возвращает 42,40

    Format("HELLO, MY FREND", "<") возвращает hello, my frend
    Hex(number) Возвращает шестнадцатиричное представление числа Hex(18) возвращает 12

    Hex(31) возвращает 1F
    InStr([start,] string1, string2[,compare]) Ищет подстроку в прямом направлении InStr(1, "To be or not to be", "be") возвращает 4
    InstrRev(stringcheck, stringmatch[, start][, compare]) Ищет подстроку в обратном направлении InstrRev("To be or not to be", "be") возвращает 17
    LCase(string) Преобразует строку в нижний регистр LCase("World") возвращает world
    Left(string, length) Выделяет левую часть строки Left("World", 2) возвращает Wo
    Len(string) Определяет длину строки Len("World") возвращает 5
    LTrim(string) Удаляет ведущие пробелы

    Пробелы справа сохраняются

    		 LTrim(" extra spaces ") возвращает extra spaces без левых пробелов
    Mid(string, start[, length]) Выделяет подстроку Mid("World", 2,3) возвращает orl
    Oct(number) Возвращает восьмеричное представление числа Oct(12) возвращает 14
    Replace(expression, find, replace[,start][,count][,compare]) Заменяет подстроку на новую подстроку Replace("The car is red", "red", "blue") возвращает The car is blue
    Right(string, length) Выделяет правую часть строки Right("World",2) возвращает ld
    RTrim(string) Удаляет завершающие пробелы

    Пробелы слева сохраняются

    		 RTrim(" extra spaces ") возвращает extra spaces без правых пробелов
    Space(number) Создает строку пробелов Space(6) создает строку из 6 пробелов
    Str(number) Преобразует число в строку Str(123) возвращает строку 123
    StrComp(string1, string2[, compare]) Сравнивает две строки:
  • compare=0 двоичное сравнение
  • compare=1 текстуальное сравнение
    •      		StrComp("WORld", "world", 1) возвращает 0 (равенство строк будет обнаружено при текстуальном сравнении)
    String(number, character) Создает строку символов String (5,"*") и String (5,42) возвращают ***** . 42 - ASCII-код символа *
    Trim(string) Удаляет пробелы с двух сторон строки Trim(" extra spaces ") возвращает extra spaces без левых и правых пробелов
    UCase(string) Преобразует строку в верхний регистр UCase("World") возвращает WORLD
    Val(string) Преобразует строку в число Val("6.28") возвращает число 6.28
    Функции преобразования текстовых данных в другие типы данных
    Функция Тип результата Функция Тип результата
    CBооl Воо1еап CCиr Сиrrепсу
    CDate Date CDbl Doublе
    CInt Iпtegеr CLng Long
    CSng Single CStr String
    CVаr Variant

    В качестве параметра функциям преобразования строк передается выражение ( expression ), которое и преобразуется в выбранный тип.

    Математические функции

    Каждая математическая функция имеет один параметр - число ( number ). При использовании действительных чисел как констант разделителем целой и дробной части является точка. В разделе Derived Math Functions справочника по VBA можно найти формулы расчета математических функций, не являющихся встроенными функциями.

    Математические функции
    Функция Описание
    Abs Возвращает абсолютную величину числа
    Atn Возвращает арктангенс числа
    Cоs Возвращает косинус угла*
    Eхр Возвращает степень числа e (еx)
    Fiх Возвращает целую часть числа
    Int Округляет число до меньшего целого числа
    Log Возвращает натуральный логарифм числа (основание е = 2.71828...)
    Randomize Инициирует генератор случайных чисел
    Rnd Возвращает случайное число
    Round Округляет число до целого, если не задан аргумент numdecimalplaces (десятичный разряд, до которого производится округление)
    Sin Возвращает синус угла*
    Sgn Возвращает знак числа
    Sqr Возвращает квадратный корень из числа
    Тап Возвращает тангенс угла*
    * Углы задаются в радианах. Для перевода градусов в радианы умножьте градусы на /180.

    Функции даты и времени

    Под датой подразумевается переменная типа Date или дата-константа, которая задана согласно системному краткому формату даты с разделителями даты, определенными системной настройкой, и заключена в кавычки c использованием символа "решетка" ( # ).

    Важно

  • Переменная типа Date в памяти компьютера сохраняется как число типа Double. Целая часть числа - это дата, а дробная часть - время.
  • Полезная информация при работе с переменными типа Date: одни сутки - это 24 часа, или 1440 минут, или 86400 секунд.
    •
    Таблица функций Даты и времени
    Название и синтаксис функций Описание Пример
    Date() * Возвращает или устанавливает системную дату Date_Today=Date() запоминает системную дату

    Date = #7/26/2007# устанавливает новую системную дату
    DateSerial(year, month, day) Преобразует в дату три целых числа: год, месяц и день DateSerial(2007,7,24) возвращает 24.07.2007
    DateValue(date) Преобразует в дату символьное представление даты DateValue("24 июля 2007") возвращает 24.07.2007
    Если год не задан, то используется год из системной даты
    Day(date) Из даты выделяет день месяца Day(#7/24/2007#) возвращает 24
    Hour(time) Из заданного времени выделяет часы Hour (#4:35:17 PM#) возвращает 16
    Minute(time) Из заданного времени выделяет минуты Minute(#4:35:17 PM#) возвращает 35
    Month(date) Из даты выделяет месяц Month (#7/24/2007#) возвращает 7
    Now() * Возвращает текущую дату и текущее время Date_Today=Now()
    Second(date) Из заданного времени выделяет секунды Second (#4:35:17 PM#) возвращает 17
    Time() * Возвращает или устанавливает системное время Time_Today= Time()
    Timer() * Возвращает временной интервал в секундах от полуночи (тип Single )
    TimeSerial(hour, minute, second) Преобразует в дату три целых числа: часы, минуты и секунды TimeSerial(16, 35, 17) возвращает 16:35:17
    TimeValue(time) Преобразует в дату символьное представление времени TimeValue("4:35:17PM") возвращает время 16:35:17
    Weekday(date, [firstdayofweek]) Возвращает день недели, соответствующий заданной дате Weekday(#7/25/2007#, 2) возвращает 3 (третий день недели - среда)
    Year(date) Из даты выделяет год Year(#7/24/2007#) возвращает 2007
    * Функции не имеют аргументов и могут записываться без скобок
    Предыдущее занятие Следующее занятие
    Вернуться к учебному плану
    ^