Задачник VBA_ II

22

4. Функции VBA для работы с массивами

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

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

При формировании векторов День и N в задаче example_03 с помощью функции Array индексация начинается c нуля. Об этом необходимо помнить!

IsArray(Имя переменной) возвращает True (истина), если переменная содержит массив; в противном случае возвращается False (ложь). Функцию IsArray используют для проверки значений переменных типа вариант, содержащих массивы:

LBound(Имя_массива [, Размер]) и UBound(Имя_массива [, Размер])

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

23

Оператор Erase СписокМассивов позволяет выполнять очистку для статических массивов и удаление – для динамических.

Когда элементы массива заполнены, данные в массиве остаются до тех пор, пока пользователь не присвоит новые значения элементам массива или пока VBA не освободится от массива. Зачастую бывает, что в дальнейших вычислениях динамический массив ни при каких обстоятельствах использоваться не будет, поэтому нецелесообразно «держать» его в памяти компьютера, так как это может сказаться на скорости работы программы. Или же может понадобиться очистить все значения в статическом массиве, устанавливая числовые значения на 0, а строковые – на пустые строки. Это можно осуществить при помощи вложенных циклов, но можно сделать гораздо проще, используя оператор Erase:

Поведение оператора Erase для статических массивов зависит от конкретного типа элементов массива (табл. 2):

24

Таблица 2

Значения элементов массива после действия оператора Erase

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

5. Передача массивов в процедуры и функции

При программировании в VBA не очень часто приходится сталкиваться с массивами. Вместо них в объектных моделях приложений Office обычно используются коллекции. Коллекции – это специальные объекты, которые предназначены для хранения наборов одинаковых элементов. Например, в Excel – коллекции Workbooks (открытые книги) и Worksheets (листы в книге)

ит.п. Коллекции обычно удобнее, чем массивы: они изначально безразмерны

ив них предусмотрен стандартный набор свойств и методов (метод Add() для добавления нового элемента, свойство Count для получения информации о количестве элементов, метод Item() для получения ссылки на нужный элемент). Тем не менее, в VBA предусмотрены все возможности,

25

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

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

5.1. Способы передачи параметров для обычных переменных

Для обычных (скалярных) переменных примером такой функции может служить, в частности, простейшая функция сложения двух целых чисел:

Вызов ее может выглядеть так:

MsgBox(nsum(3, 2)).

В данном случае мы объявили оба параметра как обязательные, и поэтому попытка вызвать функцию без передачи ей какого-либо параметра (например, так: MsgBox (nsum(3))) приведет к ошибке "Argument not optional" – "Параметр не является необязательным". Чтобы можно было пропускать ка- кие-то параметры, эти параметры можно сделать необязательными. Для этой цели используется ключевое слово Optional:

Function nsum (N1 As Integer, Optional N2 As Integer).

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

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

Вызов функции с передачей параметров может выглядеть так: nResult = nsum (3, 2).

Однако здесь есть несколько моментов, которые необходимо рассмот-

реть.

26

В нашем примере мы передаем параметры по позиции, т. е. значение 3 присваивается первому параметру (N), а значение 2 – второму (N2). Однако параметры можно передавать и по имени:

nResult = nsum (N1 := 3, N2 := 2).

Обратите внимание, что, несмотря на то, что здесь выполняется вполне привычная операция – присвоение значений, оператор присвоения используется не совсем обычный – двоеточие со знаком равенства, как, например, в C++. При использовании знака равенства возникнет ошибка.

Конечно, вместо явной передачи значений (как у нас – 3 и 2) можно использовать переменные. Однако что произойдет с переменными после того, как они «побывают» в функции, если функция изменяет их значение? Останется ли это значение за пределами функции прежним или изменится?

Все зависит от того, как именно передаются параметры – по ссылке (по умолчанию, можно также использовать ключевое слово ByRef или по значению – нужно использовать ключевое слово ByVal).

Если параметры передаются по ссылке, то фактически в вызываемую процедуру передается ссылка на эту переменную в оперативной памяти. Если эту переменную в вызываемой процедуре изменить, то значение изменится и в вызывающей функции. Это – принятое в VBA поведение по умолчанию.

Если параметры передаются по значению, то фактически в оператив-

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

Продемонстрировать разницу можно на простом примере:

27

Если поменять строку объявления функции на следующую строку:

Function nsum(byVal nItem1 As Integer, nItem2 As Integer),

то сообщения по этой программе покажут, что значение переменной nP1 не изменится:

5.2. Особенности передачи массивов

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

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

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

Вот пример простой функции, которая находит сумму элементов целочисленного массива:

28

Вызов этой функции можно осуществить в следующей программе:

Функции LBound и UBound, используемые в программе fSum избавляют от необходимости передавать значения границ массива.

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

29

Эту процедуру можно вызывать несколько раз для разных массивов в одной программе. Следующий пример демонстрирует такую возможность для просмотра исходного массива СС и преобразованного массива DD. Однако, поскольку в программе example_10 реализуется передача размерностей через переменные, первая строка программы out_matrix должна быть переписана следующим образом:

Sub out_matrix(A( ) As Integer, ByVal l1 As Byte, ByVal u1 As Byte, _ ByVal l2 As Byte, ByVal u2 As Byte, Optional nameA As String).

6. Примеры решения типовых задач и задачи для самостоятельного решения

6.1. Примеры чтения программ обработке массивов

Пример 1. Прочесть программу и вычислить по ней результат. В диалоговые окна вводятся следующие числа: 3, 6, 3, 2.

30

Текст программы:

Решение.

1.В программе описаны три целочисленных переменных n, i, j, строковая переменная s и вещественная одинарной точности mes. Задан динамический целочисленный массив А.

2.Согласно условию задачи через диалоговое окно вводится значение переменной n = 3. Эта величина указывает реальный размер массива – 4 эле-

мента: A(0), A(1), A(2), A(3).

3.Открываем цикл по i:

i= 1. mes =”Ввести координату с номером 1” (поскольку для слияния стро-

ки используется знак «&», то автоматически числовое значение переменной

iконвертируется в строковое.

В диалоговом окне выдается сообщение mes и вводится A(1) = –6. Поскольку массив описан как целочисленный, происходит автоматическая конвертация текстового значения поля ввода в числовое.

Процедура повторяется для i = 2, i = 3, всякий раз выдавая соответст-

вующее сообщение: mes. А(2) =3, А(3) = –2.

4.В следующем цикле реализуется накопление суммы квадратов эле-

5.Вычисляем modul = 49 =7 .

6.В диалоговом окне формируется сообщение:

«Модуль заданного вектора = 7».

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