2 семестр / Программирование на VBA

включать круглые скобки в объявление массива, независимо от того, определяется ли Subscripts).

При объявлении массивов следует помнить, что включение оператора Subscripts в объявление массива создает статический массив с фиксированным числом элементов, пропуск оператора Subscripts в объявлении массива создает динамический массив, а установка Option Base может повлиять на общее число элементов в массиве.

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

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

arrayName(validIndex1, [validlndex2]…)

здесь arrayName – имя массива; validlndexl – допустимое значение индекса для первого измерения массива; validlndex2 – допустимое значение индекса для второго измерения массива, если таковое имеется. Необходимо предоставлять

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

Задание 1. Напишите процедуру работы с одномерным массивом, которая принимает в диалоговом окне целые числа (для простоты без всякой проверки), заносит их в элементы массива. Затем значения элементов массива заносятся в строку и выводятся на экран в диалоговом окне оператора MsgBox.

Для этого:

ü введите процедуру (листинг 17):

Листинг 17 – Работа со статическим одномерным массивом

1Sub List6_17 ()

2Dim Int_Array(1 To 5) As Integer

3Dim str_msg As String

4Dim i As Integer

5

6str_msg = ""

7For i = 1 To 5

8Int_Array(i) = InputBox("Введите целое число для " _

9	& i & "-го элемента массива", "Ввод элементов массива")

10Next

11For i = 1 To 5

12str_msg = str_msg & Format(Int_Array(i), "@@@@@")

13Next

14

91

15MsgBox "Введено: " & str_msg, , "Вывод ранее введенного массива"

16End Sub

Встроке 2 объявляется одномерный массив Int_Array (размерностью 5 элементов) целых чисел; в строке 3 объявлена рабочая переменная str_msg для завершающего вывода данных из массива в диалоговом окне (строка 16). В первом цикле For…Next (строки 7–10) в массив Int_Array записываются значения из окна ввода. В строках 11–13 располагается второй цикл, который формирует строку из значений элементов массива для выдачи ее в диалоговом окне. Обратите внимание, как формируется значение аргумента Prompt функции

InputBox (рис. 21).

Рис. 21

Задание 2. Напишите процедуру работы с двумерным массивом, которая принимает в диалоговом окне целые числа (для простоты без всякой проверки), заносит их в элементы массива. Затем значения элементов массива заносятся в строку и выводятся на экран в диалоговом окне оператора MsgBox.

Для этого:

ü введите процедуру (листинг 18):

Листинг 18 – Работа со статическим двумерным массивом

1Sub List6_18 ()

2Dim Int_Array(1 To 3, 1 To 4) As Integer

3Dim str_msg As String

4Dim i As Integer

5Dim j As Integer

6

7str_msg = ""

8For i = 1 To 3

9For j = 1 To 4

10	Int_Array(i,j) = InputBox("Введите A(" & i & "," & j & ")", _
11	"Ввод элементов массива")

12Next j

13Next i

14For i = 1 To 3

15For j = 1 To 4

16	str_msg = str_msg & Format(Int_Array(i,j), "@@@@@")

17Next j

18str_msg = str_msg & Chr(13) 'перевод строки

92

19Next i

20MsgBox "Введено: " & Chr(13) & str_msg, , _

21"Вывод ранее введенного массива"

22End Sub

Для инициализации массива используется вложенный цикл в строках 8–13. Обратите внимание на строки 12, 13. Здесь, как ранее отмечалось, указывается, по какой переменной заканчивается цикл. Это необязательно, но лучше привыкнуть к такому стилю программирования с самого начала. В окне ввода теперь для указания, какой элемент вводится в конкретный момент времени, выводятся оба индекса. После заполнения строки, но перед изменением первого индекса к переменной str_msg добавляется символ перехода на другую строку (строка 18). В строках 14–19 располагается второй цикл, который формирует результат из значений элементов массива для выдачи ее в диалоговом окне (рис. 22).

Рис. 22

Свойства и методы объектов Range и Selection

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

Как и объекты реального мира, объекты VBA имеют различные присущие им качества или свойства (properties). Объекты в Excel имеют свойства, определяющие их вид и поведение: текст в Excel может отображаться или не отображаться полужирным шрифтом или курсивом, рабочий лист Excel может быть видимым или нет, строки в рабочем листе или таблице имеют свойство высоты, столбцы имеют свойство ширины и т.д. Чтобы узнать о текущем виде и поведении объекта, необходимо узнать о его свойствах. Некоторые свойства объекта можно изменять, некоторые – нет.

Объекты реального мира почти всегда имеют тот тип присущего им поведения или действия, который они могут выполнить. Объекты VBA также имеют поведение и возможности, называемые методами (methods). Методы изменяют значения свойств объектов; методы выполняют также действия с данными (или над данными), сохраняемыми объектом. Методы во многом похожи на процедуры VBA, с которыми вы уже знакомы, но связаны с объектом; к методам объекта можно обратиться, только используя объект. Это может казаться сложным, но на деле все проще. Для вызова метода объекта (функции обработки данных объекта) указывается не только наименование метода, но и объект (имя),

93

которому принадлежит метод.

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

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

Object.identifier

Object – любая допустимая ссылка на объект. Объектные ссылки создаются

заданием переменной для ссылки на объект или использованием методов или свойств объектов, возвращающих объектную ссылку. Identifier – любое допустимое имя свойства или метода; VBA отображает сообщение о runtime- ошибке при попытке использовать свойства или методы, которые не являются в действительности частью указанного объекта.

Объект Worksheet представляет собой рабочий лист. Объект Worksheet можно получить, используя свойства ActiveSheet или Worksheets объекта Workbook. Класс Worksheets содержит все рабочие листы рабочей книги. Свойство Cells класса Worksheets возвращает объект Range, содержащий все ячейки указанного рабочего листа. Свойство Range класса Worksheets возвращает ссылку на указанный диапазон ячеек. Например, ActiveSheet.Range("B1").

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

ячейки или диапазона с помощью метода Select либо результатом применения свойства Selection. К нему применимы свойства и методы объекта Range.

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

Если используется свойство Range, то в качестве аргумента указывается любая допустимая в MS Excel ссылка в формате А1. Например:

Worksheets("Лист1").Range("A5") = 15 'Ячейке А5 листа Лист1 'присвоить значение 15

Worksheets("Лист1").Range("A2:C3") = 15 'Ячейкам А2:C3 листа Лист1 'присвоить значение 15

Temp = Worksheets("Лист1").Range("A2") 'Переменной Temp 'присвоить содержимое 'ячейки A2 листа Лист1

Свойство Cells используется для доступа к отдельной ячейке. В качестве аргументов указываются номер строки и столбца. Например:

94

Worksheets("Лист1").Cells(6, 1) = 15 'Ячейке А6 листа Лист1 'присвоить значение 15

Temp = Worksheets("Лист1").Cells(2, 3) 'Переменной Temp 'присвоить содержимое 'ячейки C2 листа Лист1

Можно также использовать свойство Cells для альтернативного указания диапазона. Например, Range("A2:C3") и Range(Cells(2,1), Cells(3,3)) определяют один и тот же диапазон.

Ссылка на объекты с помощью With…End With

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

– структуру With…End With, позволяющую ссылаться на свойства или методы, которые принадлежат одному и тому же объекту, без задания всей объектной ссылки каждый раз. Структура With…End With имеет следующий синтаксис:

With Object

'операторы, использующие свойства и методы Object

End With

Object – это любая допустимая объектная ссылка.

Задание 3. Напишите процедуру работы с двумерным массивом, которая принимает из ячеек рабочего листа целые числа (для простоты без всякой проверки), заносит квадраты этих чисел в элементы массива. Затем значения элементов массива выводятся в ячейки рабочего листа Excel.

Для этого:

üвведите в ячейки A1:D4 листа Лист1 целые числа;

üвведите процедуру (листинг 19):

Листинг 19 – Использование свойства Cells и Range

1Sub List6_19 ()

2Dim Int_Array(1 To 3, 1 To 4) As Integer

3Dim i As Integer

4Dim j As Integer

5

6For i = 1 To 3

7For j = 1 To 4

8	Int_Array(i,j) = Worksheets("Лист1").Cells(i, j) 'Ввод элементов
9	'массива с листа Лист1 Excel (номера строк – от 1 до 3,
10	'номера столбцов – от 1 до 4)
11	Int_Array(i,j) = (Int_Array(i,j))^2

12Next j

13Next i

14Worksheets("Лист1").Range("A5") = "Результат:"

15For i = 1 To 3

95

16	For j = 1 To 4
17	Worksheets("Лист1").Cells(i + 5, j) = Int_Array(i,j) 'Вывод массива
18	'на Лист1 в диапазон A6:D8

19Next j

20Next i

21End Sub

Использование ReDim с динамическими массивами

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

Динамические массивы создаются с помощью оператора Dim, затем их размер устанавливается с помощью оператора ReDim во время выполнения процедуры. Оператор ReDim имеет следующий синтаксис:

ReDim [Preserve] varname(subscripts) [As type] [, varname(subscripts) _ [As type]]

необязательное ключевое слово Preserve, как предполагает его имя, приводит к тому, что VBA сохраняет данные в имеющемся массиве, когда изменяется размер массива с помощью ReDim; varname – имя существующего массива; subscripts – это измерения массива (синтаксис для оператора subscripts в операторе ReDim такой же, как для оператора Dim); type – любой тип VBA или определенный пользователем тип. Необходимо использовать отдельный оператор As type для каждого массива, который вы определяете.

Далее следуют допустимые примеры объявлений динамических массивов и возможные операторы ReDim, используемые с этими динамическими массивами:

Dim aMonth() As String 'объявляет динамический массив aMonth ReDim aMonth(1 To 30) 'изменяет размер массива до 30 элементов ReDim aMonth(31) ' изменяет размер массива до 31 элемента

ReDim Preserve aMonth(1 То 31) 'изменяет размер массива до 31 элемента, 'сохраняя содержимое

Dim Table() As Integer 'объявляет динамический массив ReDim Table(3, 15) 'делает массив двумерным

ReDim Table(4, 20) 'изменяет размер двумерного массива

ReDim Preserve Table(4, 25) 'только изменяет последний размер массива Dim Mas As Variant 'объявляет переменную типа Variant

ReDim Mas(20) As Integer 'создает массив 20 целых в Variant

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

96

Variant для сохранения динамического массива (использование переменной типа

Variant для сохранения динамического массива дает возможность изменять размер массива с помощью ReDim и изменять тип данных массива).

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

Variant или сами элементы массива не имеют тип Variant. Если динамический массив сохраняется в переменной типа Variant, можно изменять тип данных, используя оператор As type в операторе ReDim.

Упражнение 1

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

Функции LBound и UBound

Для контроля за размерами как статических, так и динамических массивов

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

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

VBA имеет пару функций, которые освобождают пользователя от необходимости вручную отслеживать верхний и нижний пределы массива — функции LBound и UBound. Эти функции возвращают нижнее и верхнее граничные значения индексов статического или динамического массива. Функции LBound и UBound имеют следующий синтаксис:

LBound(arrayName [, dimension])

UBound(arrayName [, dimension])

функция LBound возвращает первый индекс массива, представленного с помощью arrayName. Функция UBound возвращает наибольший индекс массива, представленного с помощью arrayName. Можно использовать эти две функции как со статическими, так и с динамическими массивами. Аргумент dimension представляет целое число, определяющее измерение массива, для которого необходимо получить нижний или верхний предел. Если опустить dimension, VBA возвращает предел для первого измерения массива.

Использование Erase для очистки или удаления массивов

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

97

очищать все элементы массива (устанавливая числовые значения на 0, строковые значения на пустые строки), в основном переустанавливая массив в то же самое состояние, какое он имел, когда VBA создавал его в оперативной памяти. В случае динамических массивов Erase позволяет полностью удалять массив и его содержимое из оперативной памяти. Оператор Erase имеет следующий синтаксис:

Erase array1 [, array2, …]

здесь array1 и array2 представляют любое допустимое имя массива VBA. Можно перечислить столько массивов в операторе Erase, сколько хотите, отделяя каждое имя массива запятой.

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

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

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

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

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

[ByVal | ByRef] arrayname() As type

ключевое слово ByVal указывает VBA передать аргумент-массив по значению, а ключевое слово ByRef указывает VBA, что следует передавать аргумент-массив по ссылке (см. часть 7). Если ByVal и ByRef пропущены, VBA передает аргумент- массив по ссылке. В этой синтаксической конструкции аггаупате() представляет аргумент-массив; можно использовать любой допустимый идентификатор VBA в качестве имени аргумента-массива. Необходимо всегда включать пустые круглые скобки после arrayname; круглые скобки указывают VBA, что этот аргумент является массивом. Параметр type представляет любой допустимый тип VBA или определенный пользователем тип.

Если вы помните, передача аргументов по значению (с помощью ключевого слова ByVal) приводит к тому, что VBA передает копию данных функции или процедуре. Не передавайте массивы по значению, если в этом нет особой

98

необходимости — передача больших массивов по значению может быстро исчерпать ресурсы памяти вашего компьютера, приводя к runtime-ошибкам из-за нехватки памяти.

Контрольные вопросы

1.Что такое массив?

2.Как описывать массивы с помощью инструкции Dim?

3.Как использовать массивы в процедурах VBA?

4.Как менять размеры массивов с помощью инструкции ReDim?

5.Как удалять и очищать массивы с помощью инструкции Erase?

Часть 7. СОЗДАНИЕ	И	ИСПОЛЬЗОВАНИЕ	ФУНКЦИЙ
И ФУНКЦИЙ-ПРОЦЕДУР

Из этой части вы узнаете, как создавать собственные пользовательские функции, как сделать так, чтобы ваши собственные функции были доступны рабочим листам Excel.

Лабораторная работа № 7. Функции-процедуры	и	определенные
пользователем функции
Цель занятия: Уметь создавать функции-процедуры и		определенные
пользователем функции.
Материалы к занятию: MS Excel 2003.

Создание функций и функций-процедур

Функция-процедура (или функция) – это особый вид процедуры VBA,

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

С «точки зрения» Excel используемые им функции-процедуры VBA

являются определенными пользователем функциями (user-defined functions). Этот термин позволяет отличать функции, написанные пользователем, от встроенных функций Excel. Хотя все определенные пользователем функции являются также функциями-процедурами, не все функции-процедуры отвечают требованиям определенной пользователем функции.

Функции-процедуры с некоторыми ограничениями на их действия называются определенными пользователем функциями (или сокращенно UDF – user-defined functions), и только их может использовать Excel в формуле ячейки рабочего листа.

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

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

99

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

Кроме того, UDF не может устанавливать свойства объекта или использовать методы объекта; в большинстве случаев установка свойств объекта или использование его методов приводят к изменениям в среде Excel (объекты, методы и свойства описываются в части 6). Определенная пользователем функция

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

Следует помнить о том, что ни VBA, ни Excel не отображают сообщение об ошибке, если в качестве UDF используется функция, в которой нарушены правила для определенных пользователем функций. Такая функция просто не может возвращать результат. Например, при попытке вставить значение в рабочий лист Excel, используя функцию-процедуру, нарушающую правила для определенных пользователем функций, ячейка с такой функцией отображает Excel-сообщение об ошибке #VALUE!, обозначающее, что функция или формула для этой ячейки не может возвращать допустимый результат.

Функция-процедура – это наиболее общий термин для создаваемых пользователем функций; термин «определенная пользователем функция» описывает определенный тип функции-процедуры, который может использовать

Excel.

Функция-процедура имеет следующий синтаксис:

Function Name ([Arglist]) [As Type] ' VBA Statements

[Name = expression] End Function

Каждая функция-процедура начинается ключевым словом Function, за которым следует имя функции, Name представляет имя, выбранное для этой функции. При написании имен функций необходимо соблюдать те же правила, что и при написании имен других идентификаторов в VBA: они должны начинаться с буквы, не могут содержать пробелов или каких-либо символов арифметических, логических операторов или операторов отношения и не могут дублировать ключевые слова VBA. После имени функции следует список ее аргументов, который заключается в круглые скобки. Здесь Arglist представляет список аргументов функции и является необязательным. Туре – любой тип возвращаемого значения функции. Если только не определяется иначе, результат, который возвращает функция-процедура, имеет тип Variant. Необязательный элемент синтаксиса Name = expression представляет присваивание функции (function assignment), которое указывает VBA, какое значение должна возвращать функция. Хотя эта часть функции является необязательной, следует всегда включать оператор присваивания в функции-процедуры. Наконец, объявление функции заканчивается ключевыми словами End Function.

Даже если функция не имеет аргументов, как VBA-функции Now, Date и Time, в объявлении функции необходимо использовать круглые скобки. Как вы

100