Обучающее видео различных тематик бесплатно!

Массивы VBA

Сказки на ночь

Массивы VBA

  

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

Объявление массива
Чтобы объявить массив, укажите число его элементов в скобках после имени переменной, как показано ниже:

Dim myArray(2)

Приведенный выше код создает массив myArray, состоящий из трех элементов. Да-да, именно из трех, потому что по умолчанию первый элемент массива имеет индекс 0:
myArray(0) = 10
myArray(1) = 20
myArray(2) =30

Чтобы назначить первому элементу массива индекс 1, воспользуйтесь выражением Option Base 1. Выражение Option Base размешается в разделе объявлений модуля:
Option Base 1
Dim myArray(2)

Теперь массив myArray будет состоять из двух элементов. При создании массива можно задать как верхнюю, так и нижнюю его границу:
Dim myArray(1 to 10)
Dim BigArray(100 to 200)

Узнать верхнюю и нижнюю границу массива можно с помощью функций UBound и LBound, соответственно. Выражение Dim myArray (2) определяет
только верхнюю границу массива, в то время как выражение Dim myArray (1 to 10) — как верхнюю (10), так и нижнюю (1) границы.
Многомерные массивы
Рассмотренные выше массивы являются одномерными массивами, положение элементов которых определяется всего одним числом. Одномерный массив можно представить в виде строки данных, единственной значимой координатой которой является номер столбца. Ниже приведен пример обращения
ко второму элементу одномерного массива (Option Base 0):
myArray(1)

Во многих ситуациях для представления данных используются многомерные массивы, положение элементов которых определяется несколькими числами
(например, двумерный массив можно сравнить с набором строк, значимыми координатами которого являются номер столбца и номер строки).
На заметку
Двумерный массив часто называют матрицей, что по своей сути близко к понятию электронной таблицы. Действительно, объект Cells определяет положение элемента таблицы, т.е. номер его строки и столбца. Ниже приведен пример создания двумерного массива, состоящего из 10 строк и 20 столбцов:
Dim myArray{1 to 10,  1 to 20)

Следующий код заполняет элементы первой строки массива myArray:
myArray(1, 1) = 10
myArray(1,  2) = 20

Аналогичным образом заполняются элементы второй строки массива:
myArray(2, 1) = 20
myArray(2,  2) = 40

Конечно же, существуют и более эффективные способы заполнения массива, которые рассматриваются далее в этой главе.
Заполнение массива
Самый простой способ заполнения массива был продемонстрирован в предыдущем разделе. Существуют гораздо более эффективные методы выполнения аналогичной операции, один из которых приведен ниже:
Option Base 1
Sub ColumnHeaders()
Dim myArray As Variant
Dim myCount As Integer
' Заполнить массив.
myArray = Array("Имя",   "Адрес",   "Телефон",   "Адрес эл. почты")
' Скопировать содержимое массива на рабочий лист.
With Worksheets("Заполнение массива")
For myCount = 1 То UBound(myArray)
.Cells(l, myCount).Value = myArray(myCount)
Next myCount
End With
End Sub

Результат выполнения макроса ColumnHeaders показан на рис. 17.1. Обратите внимание, что в приведенном выше коде для создания массива была использована переменная myArray типа Variant, которая может хранить информацию любого рода. Переменная myArray принимает свойства массива после присвоения ей соответствующего значения:
myArray = Array("Имя",   "Адрес",   "Телефон",   "Адрес эл. почты")

Ниже приведен пример заполнения массива данными, взятыми с рабочего листа Excel (см. рис. 17.2):
Dim myArray As Variant
myArray = Worksheets("Sheetl").Range("B2:C17")

Массивы VBA

Рис. 17.1. Пример использования массива для создания строки заголовков

Массивы VBA

Рис. 17.2. Массив можно заполнить данными, взятыми с рабочего листа Excel

Следующий макрос заполняет массив данными, взятыми с рабочего листа Excel через одну строку:
Sub EveryOtherRow()
Dim myArray(1 To 8,  1 To 2)
Dim i As Integer,  j As Integer, myCount As Integer
'  Заполнить массив данными, взятыми
'   с рабочего листа Excel через одну строку.
For i = 1 То 8
For j  = 1 To 2
myArray(i,  j)  = _
Worksheets("Через один").Cells(i * 2,  j + 1).Value
Next j
Next i
'  Скопировать  содержимое массива на рабочий лист.
For myCount = LBound(myArray)  To UBound(myArray)
Worksheets("Через один").Cells(myCount * 2,  4)  = _
WorksheetFunction.Sum(myArray(myCount,  1), myArray(myCount, 2
Next myCount
End Sub

Результат выполнения макроса EveryOtherRow показан на рис. 1
Массивы VBA

Рис. 17.3. Макрос EveryOtherRow подсчитывает сумму данных, взятых с рабочего листа Excel через одну строку

Манипулирование элементами массива
Одно из основных предназначений массива заключается в манипулировании его элементами. Следующий макрос вычисляет наибольший элемент массива, заполненного данными, взятыми с рабочего листа Excel.
Sub QuickFillMax()
Dim myArray As Variant
myArray = Worksheets("Через один").Range("B2:C17")
MsgBox  "Максимальное  целое число в диапазоне В2:С17 равно  " _
& WorksheetFunction.Max(myArray)
End Sub

Результат выполнения макроса QuickFillMax показан на рис. 17.4. Зачастую результат манипулирования элементами массива помещается на рабочий лист Excel. Следующий макрос вычисляет среднее значение каждой строки массива и помещает полученный результат на рабочий лист.
Массивы VBA

Рис. 17.4. Макрос QuickFillMax вычисляет наибольшее значение в диапазоне В2 :С17

Sub QuickFillAverage()
Dim myArray As Variant
Dim myCount As Integer
myArray = Worksheets("Через один").Range("B2:C17")
For myCount = LBound(myArray)  To UBound(myArray)
Worksheets("Через один").Cells(myCount +1,  5).Value = _
WorksheetFunction.Average(myArray(myCount,  1), myArray(myCount,  2))
Next myCount
End Sub

На заметку
При помещении данных на рабочий лист Excel в качестве адреса строки используется значение myCount + 1. Это обусловлено тем, что нижняя граница массива (LBound) равна 1 (Option Base l), а данные на рабочем листе размещаются начиная со второй строки.

Результат выполнения макроса QuickFillAverage показан на рис. 17.5.
Еще одно преимущество массивов
Неужели все преимущество массивов сводится к упрощению манипулирования данными рабочего листа? Конечно же, нет. Основная выгода, получаемая от использования массивов, заключается в существенном повышении скорости выполнения программного кода.
Массивы VBA

Рис. 17.5. Макрос QuickFillAverage вычисляет среднее значение строк массива и помещает полученные результаты на рабочий лист Excel

Следующий макрос вычисляет среднее значение столбцов В и С без использования массива.
Sub SlowAverage()
Dim myCount As Integer,  LastRow As Integer
LastRow = Worksheets("Через один").Range("A65536").End(xlUp).Row
For myCount = 2 To LastRow
With Worksheets("Через один")
.Cells(myCount,  5).Value = _
WorksheetFunction.Average(.Cells(myCount,  2),   .Cells(myCount, 3))
End With
Next myCount
End Sub

Макрос SlowAverage обрабатывает каждую строку в области данных рабочего листа, вычисляя среднее столбцов В и С и помещая полученный результат в столбец Е. Не проще ли работать с данными как с единым целым, обращаясь к ячейкам рабочего листа только для заполнения массива и помещения на рабочий лист полученного результата? При заполнении массива рекомендуется использовать именованный диапазон ячеек, так как это позволяет создавать более универсальный код. Ниже приведены примеры заполнения массива с помощью обычного и именованного диапазона ячеек.
myArray = Range("B2:С17")
myArray = Range("myData")

Совет
Присвоение переменной типа Variant содержимого столбца рабочего листа Excel приведет к созданию двумерного массива, для обращения к элементам которого необходимо указывать номер строки и номер столбца. Чтобы упростить работу с подобным массивом, транспонируйте столбец с помощью функции Transpose, превратив его тем самым в строку. Присвоение переменной типа Variant содержимого строки рабочего листа Excel приведет к созданию одномерного массива, что наглядно демонстрирует следующий код.
Sub TransposeArray()
Dim myArray As Variant
myArray = WorksheetFunction.Transpose(Range("myTran"))
'  Узнать значение 5-го элемента массива.
MsgBox "5-й элемент массива равен  "  & myArray(5)
End Sub

Результат выполнения макроса TransposeArray показан на рис. 17.6.
Массивы VBA

Рис. 17.6. Чтобы создать одномерный массив, преобразуйте столбец в строку с помощью функции Transpose
 
Уважаемый посетитель, Вы зашли на сайт как незарегистрированный пользователь. Мы рекомендуем Вам зарегистрироваться либо войти на сайт под своим именем.


Дата: 18 марта 2014   |   Опубликовал: Admin   |   Просмотров: 3202   |   Комментарии (0)

Уважаемый , статьи ниже возможно будут интересны вам:

Посетители, находящиеся в группе Гости, не могут оставлять комментарии к данной публикации.