Массивы vba примеры программ

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

Содержание

Одномерные массивы

Объявление одномерных (линейных) статических массивов в VBA Excel:

Public Massiv1 ( 9 ) As Integer
Dim Massiv2 ( 1 To 9 ) As String

В первом случае публичный массив содержит 10 элементов от 0 до 9 (нижний индекс по умолчанию — 0, верхний индекс — 9), а во втором случае локальный массив содержит 9 элементов от 1 до 9. По умолчанию VBA Excel считает в массивах нижним индексом нуль, но, при желании, можно сделать нижним индексом по умолчанию единицу, добавив в самом начале модуля объявление «Option Base 1».

Многомерные массивы

‘Массив двухмерный
Public Massiv1 ( 3 , 6 ) As Integer
‘Массив трехмерный
Dim Massiv2 ( 1 To 6 , 1 To 8 , 1 To 5 ) As String
‘Массив четырехмерный
Dim Massiv3 ( 9 , 9 , 9 , 9 ) As Date
Третий массив состоит из 10000 элементов — 10×10×10×10.

Динамические массивы

Public Massiv1 ( ) As Integer
Dim Massiv2 ( ) As String
Public Massiv1 ( ) As Integer
Dim Massiv2 ( ) As String
ReDim Massiv1 ( 1 To 20 )
ReDim Massiv2 ( 3 , 5 , 4 )
Dim Massiv1 ( ) as Variant , x As Integer

Arrays VBA — Одна переменная для множества значений! — (Серия VBA 12)

ReDim Massiv1 ( 1 To x )
Dim Massiv1 ( ) As String
ReDim Massiv1 ( 5 , 2 , 3 )
ReDim Preserve Massiv1 ( 5 , 2 , 7 )

Обратите внимание!
Переопределить с оператором Preserve можно только последнюю размерность динамического массива. Это недоработка разработчиков, которая сохранилась и в VBA Excel 2016. Без оператора Preserve можно переопределить все размерности.

Максимальный размер

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

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

Приведу два примера, где не обойтись без массивов. 1. Как известно, функция Split возвращает одномерный массив подстрок, извлеченных из первоначальной строки с разделителями. Эти данные присваиваются заранее объявленному строковому (As String) одномерному динамическому массиву. Размерность устанавливается автоматически в зависимости от количества подстрок.

2. Данные в массивах обрабатываются значительно быстрее, чем в ячейках рабочего листа. Построчную обработку информации в таблице Excel можно наблюдать визуально по мерцаниям экрана, если его обновление (Application.ScreenUpdating) не отключено. Чтобы ускорить работу кода, можно значения из диапазона ячеек предварительно загрузить в динамический массив с помощью оператора присваивания (=).

Excel VBA. Массивы (урок 17)

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

Источник: vremya-ne-zhdet.ru

Массивы в VBA. Урок №14

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

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

Содержание урока:

1. Одномерные массивы в VBA
2. Объявление массивов в VBA
3. Оператор Option Base в VBA
4. Пример одномерного массива в VBA
5. Многомерные массивы в VBA
6. Объявление многомерных массивов
7. Пример многомерного массива в VBA
8. Определение границ массива
9. Статические массивы
10. Динамические массивы
11. Очистка и удаление массивов

Одномерные массивы в VBA

Например, у нас есть список дней недели. Это может быть массив, потому что элементы этого списка (дни недели) имеют одинаковый тип. И вот если мы дадим этому списку имя, например Week, то мы и получим массив.

Каждый день недели в этом списке – это элемент массива. При этом, каждый день недели имеет свой собственный порядковый номер (так называемый индекс).

1. Sunday;
2. Monday;
3. Tuesday;
4. Wednesday;
5. Thursday;
6. Friday;
7. Saturday.

Обратите внимание, что нумерация в массивах начинается не с единицы, а с нуля.

И вот, если мы хотим обратиться к какому-то из этих дней недели, например к дню, который у нас находится под индексом 1 (Monday), то нам достаточно указать имя массива и индекс 1 – Week(1).

Объявление массивов в VBA

Sub Массивы() Dim Week Week = Array(«Sunday», «Monday», «Tuesday», «Wednesday», «Thursday», «Friday», «Saturday») MsgBox Week(1) End Sub

Массив из примера выше, который представляет собой простой список данных, называется одномерным. Заключенное в скобки число, стоящее рядом с именем массива, называется индексом элемента данного массива.

Как я уже сказал, в языке VBA, нумерация элементов в массиве начинается с нуля. Но можно изменить начальный индекс элементов массива с помощью оператора Option Base. Этот оператор может иметь два значения – 0, или 1. Но поскольку по умолчанию нумерация элементов массива начинается с нуля, то Option Base 0 не указывают.

Оператор Option Base указывают в модуле только один раз, и он обязательно должен предшествовать объявлению массивов.

Оператор Option Base в VBA

Option Base 1 Sub Массивы() Dim Week Week = Array(«Sunday», «Monday», «Tuesday», «Wednesday», «Thursday», «Friday», «Saturday») MsgBox Week(1) End Sub

Пример одномерного массива в VBA

Для закрепления темы, давайте рассмотрим ещё один пример работы с одномерными массивами в VBA Excel.

Например, у нас есть книга Excel с одним листом, который мы переименуем в Arrays (что в переводе с английского означает массивы) и заполним первый столбец этого листа днями недели:

1. Sunday;
2. Monday;
3. Tuesday;
4. Wednesday;
5. Thursday;
6. Friday;
7. Saturday.

Рисунок 1. Одномерные массивы в VBA Excel

Этот список является одномерным массивом и обратиться в элементам этого массива мы можем следующим образом:

Sub oneDimArrs() Dim oneDimArr(1 To 7) As String oneDimArr(1) = Worksheets(«Arrays»).Cells(1, 1) oneDimArr(2) = Worksheets(«Arrays»).Cells(2, 1) oneDimArr(3) = Worksheets(«Arrays»).Cells(3, 1) oneDimArr(4) = Worksheets(«Arrays»).Cells(4, 1) oneDimArr(5) = Worksheets(«Arrays»).Cells(5, 1) oneDimArr(6) = Worksheets(«Arrays»).Cells(6, 1) oneDimArr(7) = Worksheets(«Arrays»).Cells(7, 1) MsgBox oneDimArr(2) End Sub

Многомерные массивы в VBA

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

Например, Dim Matrix (1 to 3, 1 to 3) – это матрица размером 3х3 элементов, состоящая из трёх строк и трёх столбцов.

В Visual Basic For Applications допускается объявлять массивы с максимум 60 размерностями. Но, как показывает практика, в программировании чаще всего используются одно- и двухмерные массивы.

Объявление многомерных массивов

Sub MultidimensionalArrays() ‘Массив двумерный Dim arr1(3, 6) As Integer ‘Массив трехмерный Dim arr2(1 To 6, 1 To 8, 1 To 5) As String ‘Массив четырехмерный Dim arr3(9, 9, 9, 9) As Date End Sub

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

Давайте рассмотрим пример двумерного массива в VBA Excel. Создадим книгу Excel и переименуем Лист1 в Arrays. Далее заполним первые три строки и колонки буквами английского алфавита: A, B, C, D, E, F, G, H, I.

Рисунок 2. Многомерные массивы в VBA Excel

Теперь давайте обратимся к элементам данного массива и выведем какой-нибудь из них на экран в сообщении.

Sub MultidimensionalArrays() Dim twoDimArr(1 To 3, 1 To 3) As String ‘объявляем двумерный массив, группа значений в котором идентифицируется по двум признакам Rows — Columns (Строки — Колонки) twoDimArr(1, 1) = Worksheets(«Arrays»).Cells(1, 1) twoDimArr(1, 2) = Worksheets(«Arrays»).Cells(1, 2) twoDimArr(1, 3) = Worksheets(«Arrays»).Cells(1, 3) twoDimArr(2, 1) = Worksheets(«Arrays»).Cells(2, 1) twoDimArr(2, 2) = Worksheets(«Arrays»).Cells(2, 2) twoDimArr(2, 3) = Worksheets(«Arrays»).Cells(2, 3) twoDimArr(3, 1) = Worksheets(«Arrays»).Cells(3, 1) twoDimArr(3, 2) = Worksheets(«Arrays»).Cells(3, 2) twoDimArr(3, 3) = Worksheets(«Arrays»).Cells(3, 3) MsgBox twoDimArr(2, 2) End Sub

Пример выше является достаточно громоздким и его можно использовать только тогда, когда у нас небольшое количество элементов (до 3-ох, 4-ох). А вот если у нас большое количество элементов, то для сохранения своего времени, лучше написать код, который пройдётся по указанным нами ячейкам и сохранит их значение в массив.

Sub twoDimArrays() Dim twoDimArr(1 To 3, 1 To 3) As String Dim cellChecked As Range For Each cellChecked In Worksheets(«Arrays»).Range(«A1:C3») twoDimArr(cellChecked.Row, cellChecked.Column) = cellChecked Next cellChecked MsgBox twoDimArr(2, 2) End Sub

В данном примере, мы с помощью цикла проходимся по ячейкам от A1 до C3 (слева направо) и выводим на экран сообщение в котором хранится значение строки 2, столбца 2.

Согласитесь, такая запись кода значительно проще и занимает меньше места.

Определение границ массива

Иногда, возникает необходимость определить нижнюю и верхнюю границы индекса массива. Для этого в VBA предусмотрены 2 функции: LBound и UBound.

Первая возвращает нижнюю границу индекса, вторая — верхнюю.

Синтаксис

LBound(arrayname, [ dimension ]) UBound(arrayname, [ dimension ])

• arrayname – имя массива. Это обязательный аргумент.
• dimension – размер. Это необязательный аргумент. Используйте 1 для первого измерения, 2 для второго и так далее. Если измерение опущено, предполагается 1.

Для одномерных массивов, dimension указывать не обязательно. А вот если массив окажется многомерным, и вы не укажете dimension, то возникнет ошибка.

Поэтому, если вы точно не знаете, с каким массивом имеете дело, но необходимо узнать его первую размерность, то лучше использовать вариант UBound(arrTemp,1), а не UBound(arrTemp). Вариант UBound(arrTemp) вызовет ошибку, если массив окажется многомерным.

Пример определения границ массива

Sub testArrs() Dim arrStart(0 To 3) ‘одномерный массив MsgBox LBound(arrStart) ‘определяем нижнюю границу индекса (результат будет 0) MsgBox UBound(arrStart) ‘определяем верхнюю границу индекса (результат будет 3) End Sub

Если вы ошибётесь с указанием правильного индекса массива, то возникнет ошибка периода исполнения Run-time error ‘9’: Subscript out of range. Эта же ошибка возникнет, если вы в функции LBound / UBound укажете несуществующую размерность массива (например, 3 для двумерного массива).

Статические массивы в VBA

Язык VBA поддерживает два типа массивов – статические и динамические.

Статическими называют такие массивы, размерность которых была указана непосредственно при их объявлении. В этом случае размер массива остается фиксированным на протяжении всего выполнения программы. Для объявления массивов используются те же операторы Dim и Public, что и при объявлении обычной переменной, причем с теми же правилами определения их области действия. Объявить массив фиксированного размера можно, указав в скобках после его имени конкретные значения для каждого его измерения.

‘Одномерный массив состоящий из 31 элемента Dim Arr1(30) As Integer ‘Двумерный массив из 11 строк по 21 элементу в каждой строке Dim Arr2(10, 20) As Integer

В этом примере первый оператор объявляет одномерный массив Arr1 из 31 целого числа. Поскольку мы уже знаем, что в языке VBA по умолчанию нумерация индексов начинается с нуля, то элементы массива Arr1 будут пронумерованы от 0 до 30. Второй оператор объявляет двухмерный массив Arr2, который будет содержать 11 строк по 21 целому числу, причем индексы элементов этого массива будут изменяться следующим образом: первый – от 0 до 10, а второй – от 0 до 20.

Динамические массивы в VBA

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

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

Dim Arr1 () As String

Объявлять динамический массив целесообразно в следующих случаях:

1. когда требуемый размер массива неизвестен до момента выполнения программы;
2. если заранее известно, что в ходе выполнения программы размер массива будет меняться;
3. если при выполнении программы после завершения использования массива необходимо освободить занимаемую им память.

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

Dim Arr1() Dim Arr2() Dim Arr3() … ReDim Arr1(5) ‘1 измерение, 6 элементов ReDim Arr2(5 To 11) ‘1 измерение, 7 элементов ReDim Arr3(1 To 6, 1 To 10) ‘2 измерения, 60 элементов

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

Dim Arr1() As Single … ReDim Arr1(1 To 10, 1 To 20) … ReDim Preserve Arr1(1 To 10, 1 To 50)

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

Следует отметить, что при использовании ключевого слова Preserve имеет место несколько ограничений:

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

Очистка и удаление массивов при помощи Erase

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

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

Или же может понадобиться очистить все значения в статическом массиве, устанавливая числовые значения на 0, а строковые — на пустые строки.

Вот для этого и существует оператор Erase.

Erase My_Array

Чтобы вновь использовать удаленный динамический массив, его следует переопределить с помощью оператора ReDim.

Для статических массивов действия оператора Erase зависит от конкретного типа элементов данного массива.

1. Для любого числового типа оператор Erase записывает во все элементы массива значения нуль.
2. Для строкового типа с переменной длиной строки оператор Erase помещает во все элементы массива строку нулевой длинны, а строки фиксированной длинны полностью заполняются символами пробелов.
3. Для типа Variant оператор Erase устанавливает все элементы массива на Empty.
4. Для типа Object оператор Erase устанавливает элементы массива на Nothing.
5. Для пользовательского типа оператор Erase устанавливает числовые типы на 0, строковые – на строки нулевой длины, Variant – на Empty, a Object – на Nothing.

Вас может заинтересовать

• Объекты в VBA. Урок №40
• Ошибки выполнения процедур и функций VBA. Урок №39
• Процедуры типа Function. Урок №38
• Функции в VBA и их аргументы. Урок №37
• Способы вызова процедур в VBA. Урок №36
• Способы передачи аргументов в процедуру. Урок №35
• Передача аргументов при вызове процедуры в VBA. Урок №34
• Аргументы процедур в VBA. Урок №33
• Процедуры в VBA: Основные понятия. Урок №32
• Оператор Do…Loop в VBA. Урок №31

Источник: d-nik.site