В таблице ниже приведен краткий справочник по использованию массивов в VBA. В статье я попытался перейти к заголовку самого подробного руководства, которое вы найдете по массивам VBA.
Краткое руководство по массивам VBA
| Задача | Статический множество |
Динамический множество |
| Объявление | Тусклый обр (от 0 до 5) Как Длинный |
Dim arr() As Long Тусклый шрам как вариант |
| Установить размер | Тусклый обр (от 0 до 5) Как Длинный |
Изменить обр (от 0 до 5) как Вариант |
| Приблизить (сохранять существующий данные) |
Только динамичный |
Рубец ReDim Preserve (от 0 до 6) |
| Установить ценности |
обр (1) = 22 | обр (1) = 22 |
| Несколько значений | всего = обр (1) | всего = обр (1) |
| Первая позиция | LBound(шрам) | LBound(шрам) |
| Последняя позиция | Ubound (шрам) | Ubound (шрам) |
| Читать все записи (1D) | Для i = LBound(arr) To UBound(arr) Далее в Или Для i = LBound(arr,1) To UBound(arr,1) Далее в |
Для i = LBound(arr) To UBound(arr) Далее в Или Для i = LBound(arr,1) To UBound(arr,1) Далее в |
|
Читать все записи (2D) |
Для i = LBound(arr,1) To UBound(arr,1) Для j = LBound(arr,2) To UBound(arr,2) Следующий j Далее в |
Для i = LBound(arr,1) To UBound(arr,1) Для j = LBound(arr,2) To UBound(arr,2) Следующий j Далее в |
|
Прочитать все записи |
Затемнение элемента как вариант Для каждого элемента в обр Следующий элемент |
Затемнение элемента как вариант Для каждого элемента в обр Следующий элемент |
| Перейти к сабвуферу | Sub MySub (ByRef arr () как строка) | Sub MySub (ByRef arr () как строка) |
| Возврат из функции | Функция ПолучитьМассив() Пока() Тусклый обр (от 0 до 5) Как Длинный GetArray = обр Выход из функции |
Функция ПолучитьМассив() Пока() Dim arr() As Long GetArray = обр Выход из функции |
| Получить от функции |
Только динамичный |
Dim arr() As Long Арр = ПолучитьМассив() |
| Удалить массив | Стереть шрамы * Сбросить все ценности по умолчанию |
Стереть шрамы *Удаляет массив |
| Строка в массив | Только динамичный |
Тусклый шрам как вариант arr = Split("Джеймс:Эрл:Джонс»,»:») |
| Массив в строку | Dim sName как строка sName = Присоединиться (обр, «:») |
Dim sName как строка sName = Присоединиться (обр, «:») |
| Входить ценности |
Только динамичный |
Тусклый шрам как вариант arr = Array("Джон", "Хейзел", "Фред») |
| Диапазон массива | Только динамичный |
Тусклый шрам как вариант обр = Диапазон("A1:D2») |
| Массив в диапазоне | Так же, как в динамичный |
Тусклый шрам как вариант Диапазон("A5:D6") = обр |
Введение
В этой статье подробно рассматриваются массивы в языке программирования Excel VBA. Он охватывает такие важные моменты, как:
- Зачем вам массивы
- Когда вы должны их использовать
- Два типа массивов
- Использует более одного измерения
- Объявление массива
- Добавление значений
- Посмотреть все товары
- Суперэффективный способ чтения диапазона в массив
В первой части мы рассмотрим, что такое массивы и зачем они нужны. Вы можете не понимать часть кода в первой части. Это отлично. Я разобью его простыми словами в следующих разделах статьи.
Быстрые заметки
Иногда коллекции лучше, чем массивы. О коллекциях можно прочитать здесь.
Матрицы и петли идут рука об руку. Наиболее распространенными циклами, которые вы используете с массивами, являются циклы For i и For Every.
Что такое массивы и зачем они нужны?
Массив VBA — это переменный тип. Используется для хранения списков данных одного типа. Примером может быть хранение списка стран или списка недельных итогов.
В VBA обычная переменная может хранить только одно значение за раз.
В следующем примере показана переменная, используемая для хранения оценок учащегося.
' Одновременно может храниться только одно значение Dim Student1 As Integer Student1 = 55
Если мы хотим сохранить оценки другого ученика, нам нужно создать еще одну переменную.
В следующем примере у нас есть оценки пяти учеников
Мы прочитаем эти теги и запишем их в ближайшее окно.
Примечание. Функция Debug.Print записывает значения в ближайшее окно. Чтобы увидеть это окно, выберите в меню View->Immediate Window (сочетание клавиш Ctrl + G).
Как вы можете видеть в следующем примере, мы пишем один и тот же код пять раз, по одному для каждого ученика.
Public Sub StudentMarks() With ThisWorkbook.Worksheets("Sheet1") ' Объявить переменную для каждого учащегося Dim Student1 As Integer Dim Student2 As Integer Dim Student3 As Integer Dim Student4 As Integer Dim Student5 As Integer ' Читать оценки учащегося из ячейки Student1 = . Диапазон ("C2"). Смещение (1) Студент2 = .Range ("C2"). Смещение (2) Студент3 = .Range ("C2"). Смещение (3) Студент4 = .Range ("C2"). Смещение (4) Student5 = .Range("C2").Offset(5) ' Вывести оценки учащихся Debug.Print "Оценки учащихся" Debug.Print Student1 Debug.Print Student2 Debug.Print Student3 Debug.Print Student4 Debug.Print Student5 End With Конец сабвуфера
Ниже приведен вывод из примера
Проблема с использованием одной переменной для каждого учащегося заключается в том, что вам нужно добавить код для каждого учащегося. Итак, если в приведенном выше примере у вас есть тысяча студентов, вам потребуется три тысячи строк кода!
К счастью, у нас есть матрицы, облегчающие нашу жизнь. Массивы позволяют нам хранить список элементов данных в одной структуре.
Следующий код демонстрирует приведенный выше пример с использованием массива.
Public Sub StudentMarksArr() With ThisWorkbook.Worksheets("Sheet1") ' Объявить массив для хранения оценок для 5 учащихся Dim Student(1 To 5) As Integer ' Считать оценки учащихся из ячеек C3: C7 в массив Dim i As Integer For i = от 1 до 5 студентов(i) = .Range("C2").Offset(i) Next i ' Студенты(i) Next i End With End Sub
Преимущество этого кода в том, что он будет работать для любого количества студентов. Если нам нужно изменить этот код для работы с 1000 студентов, нам просто нужно изменить (от 1 до 5) на (от 1 до 1000) в объявлении. В предыдущем примере нам нужно было добавить около пяти тысяч строк кода.
Проведем быстрое сравнение переменных и массивов. Во-первых, мы сравним процесс объявления.
' Объявить переменные Dim Student As Integer Dim Country As String ' Объявить массивы Dim Student (от 1 до 3) As Integer Dim Country (от 1 до 3) As String
Затем мы сравниваем присвоение значения
' присваиваем значение переменной Student1 = .Cells(1, 1) ' присваиваем значение первому элементу массива Student(1) = .Cells(1, 1)
Наконец, мы смотрим на значения записи
' Вывести значение переменной Debug.Print Student1 ' Вывести значение первого студента в массиве Debug.Print Student(1)
Как видите, использование переменных и массивов очень похоже.
Важно то, что массивы используют индекс (также называемый индексом) для доступа к каждому элементу. Это означает, что мы можем легко получить доступ ко всем элементам массива с помощью цикла For.
Теперь, когда у вас есть представление о том, чем полезны массивы, давайте рассмотрим их шаг за шагом.
Типы массивов VBA
В VBA есть два типа массивов:
- Статический - массив фиксированного размера.
- Динамический — массив, размер которого задается во время выполнения
Разница между этими массивами в основном заключается в том, как они создаются. Доступ к значениям в обоих типах массивов абсолютно одинаков. В следующих разделах мы рассмотрим оба типа.
Объявление массива
Статический массив объявляется так
Public Sub DecArrayStatic() 'Создать массив с ячейками 0,1,2,3 Dim arrMarks1 (от 0 до 3) As Long ' По умолчанию от 0 до 3, т.е с ячейками 0,1,2,3 Dim arrMarks2(3) As Long ' Создать массив с ячейками 1,2,3,4,5 Dim arrMarks1(от 1 до 5) As Long 'Создать массив с ячейками 2,3,4' Это редко используется Dim arrMarks3(от 2 до 4) As Long End Sub
Как видите, размер указывается при объявлении статического массива. Проблема в том, что вы никогда не можете быть уверены, какой размер вам нужен. Каждый раз, когда вы запускаете макрос, у вас могут быть разные требования к размеру.
Если вы не используете все местоположения массива, ресурсы тратятся впустую. Если вам нужно больше места, вы можете использовать ReDim, но это по существу создает новый статический массив.
Динамический массив не имеет этих проблем. Вы не указываете размер при объявлении. Таким образом, вы можете увеличивать и уменьшать масштаб по мере необходимости.
Public Sub DecArrayDynamic() ' Объявить динамический массив Dim arrMarks() As Long ' Установить размер массива, когда он будет готов ReDim arrMarks(0 To 5) End Sub
Динамический массив не выделяется, пока вы не используете оператор ReDim. Преимущество состоит в том, что вы можете подождать, пока не узнаете количество элементов, прежде чем указывать размер массива. Для статического массива необходимо заранее указать размер.
Присвоение значений массиву
Чтобы присвоить значения массиву, вы используете номер позиции (пересечение строки и столбца). Вы присваиваете значение обоим типам массивов одинаково.
Public Sub AssignValue() ' Объявить массив с позициями 0,1,2,3 Dim arrMarks(0 to 3) As Long ' Установить значение позиции равным 0 arrMarks(0) = 5 ' Установить значение позиции равным 3 arrMarks(3)) = 46 'Эта ошибка как отсутствие местоположения 4 arrMarks(4) = 99 End Sub
Номер места называется индексом. Последняя строка в примере выдаст ошибку «Индекс вне диапазона», потому что в примере с массивом нет позиции 4.
Использование функций Array и Split
Вы можете использовать функцию Array для заполнения массива списком элементов. Вы должны объявить массив как вариантный тип. Следующий код показывает, как использовать эту функцию.
Dim arr1 As Variant arr1 = Array("Апельсин", "Персик","Груша") Dim arr2 As Variant arr2 = Array(5, 6, 7, 8, 12)
Массив, созданный функцией Array, будет начинаться с нулевого индекса, если вы не используете Option Base 1 в верхней части модуля. Затем он начнется с первого индекса. В программировании обычно считается плохой практикой иметь ваши реальные данные в вашем коде. Однако иногда это полезно, когда нужно быстро протестировать какой-то код. Функция разделения используется для разделения строки на массив на основе разделителя. Разделитель — это символ, например запятая или пробел, разделяющий элементы.
Следующий код разделит строку на массив из трех элементов.
Dim s As String s = "Red, Yellow, Green, Blue" Dim arr() As String arr = Split(s, ",")
Функция разделения обычно используется при чтении из файла cvs или txt, разделенного запятыми, или из другого источника, который предоставляет список элементов, разделенных одним и тем же символом.
Использование циклов с массивами
использование цикла For обеспечивает быстрый доступ ко всем элементам массива. Именно здесь становится очевидной мощь использования массивов. Мы можем читать массивы из десяти значений или десяти тысяч значений, используя одни и те же несколько строк кода. В VBA есть две функции: LBound и UBound. Эти функции возвращают наименьший и наибольший индекс в массиве. В массиве arrMarks (от 0 до 3) LBound вернет 0, а UBound вернет 3.
В следующем примере случайные числа назначаются массиву с помощью цикла. Затем он печатает эти числа, используя второй цикл.
Public Sub ArrayLoops() ' Объявить массив Dim arrMarks (от 0 до 5) As Long ' Заполнить массив случайными числами Dim i As Long For i = LBound(arrMarks) To UBound(arrMarks) arrMarks(i) = 5 * Rnd Next i ' Распечатать значения в массиве Debug.Print "Location", "Value" For i = LBound(arrMarks) To UBound(arrMarks) Debug.Print i, arrMarks(i) Next i End Sub
Функции LBound и UBound очень полезны. Их использование означает, что наши циклы будут корректно работать с любым размером массива. Реальное преимущество заключается в том, что если размер массива изменится, нам не нужно менять код для вывода значений. Цикл будет работать для диапазона любого размера, пока вы используете эти функции.
Используйте один для каждой петли
Вы можете использовать цикл For Each с массивами. Важно помнить, что он доступен только для чтения. Это означает, что вы не можете изменить значение в массиве.
Следующий код изменяет значение метки, но не изменяет значение в массиве.
Для каждой метки в arrMarks ' Не изменится значение массива метка = 5 * Rnd Следующая метка
Цикл For Every отлично подходит для чтения массива. Как видите, лучше писать специально для двумерного массива.
Затемнить метку как вариант для каждой метки В arrMarks Debug.Print метка Следующая метка
Использование Erase
Функцию удаления можно использовать для массивов, но она работает по-разному в зависимости от типа массива.
Для статического массива функция Clear сбрасывает все значения по умолчанию. Если массив состоит из целых чисел, все значения устанавливаются равными нулю. Если массив состоит из строк, всем строкам присваивается значение "" и так далее.
Для динамического массива функция удаления очищает память. То есть снимает матрицу. Если вы хотите использовать его снова, вам нужно использовать ReDim для выделения памяти.
Давайте рассмотрим пример статического массива. Этот пример похож на пример ArrayLoops в предыдущем разделе с одним отличием — мы используем Erase после установки значений. Когда значение будет напечатано, все будет равно нулю.
Public Sub EraseStatic() ' Объявить массив Dim arrMarks (от 0 до 3) As Long ' Заполнить массив случайными числами Dim i As Long For i = LBound(arrMarks) To UBound(arrMarks) arrMarks(i) = 5 * Rnd Next i ' ВСЕ ЗНАЧЕНИЯ УСТАНОВЛЕНЫ НА NULL Удалить arrMarks ' Значения печати - теперь все равно null Debug.Print "Location", "Value" For i = LBound(arrMarks) To UBound(arrMarks) Debug.Print i, arrMarks(i) Next i Конец сабвуфера
Теперь попробуем тот же пример с динамикой. После того, как мы используем Erase, все места в массиве удаляются. Нам нужно использовать ReDim, если мы хотим снова использовать массив.
Если мы попытаемся получить доступ к членам этого массива, мы получим ошибку «Индекс вне диапазона».
Public Sub EraseDynamic() ' Объявить массив Dim arrMarks() As Long ReDim arrMarks(0 To 3) ' Заполнить массив случайными числами Dim i As Long For i = LBound(arrMarks) To UBound(arrMarks) arrMarks(i) = 5 * Rnd Next i 'arrMarks теперь выпущен. Мест не существует. Удалить arrMarks End Sub
ReDim с Preserve
Если мы используем ReDim для существующего массива, массив и его содержимое будут удалены.
В следующем примере второй оператор ReDim создаст совершенно новый массив. Исходный массив и его содержимое будут удалены.
Sub UsingRedim() Dim arr() As String ' Установить массив в слоты от 0 до 2 ReDim arr(0 To 2) arr(0) = "Apple" ' Массив с яблоком теперь удален ReDim arr(0 To 3) End Sub
Если мы хотим увеличить размер массива без потери его содержимого, мы можем использовать ключевое слово Preserve.
Когда мы используем Redim Preserve, новый массив должен начинаться с того же начального размера, например, мы не можем сохранить от (0 до 2) до (от 1 до 3) или до (от 2 до 10), поскольку они имеют разные начальные размеры.
В следующем коде мы создаем массив с помощью ReDim, а затем заполняем массив типами фруктов.
Затем мы используем Preserve для увеличения размера массива, чтобы не потерять исходное содержимое.
Sub UsingRedimPreserve() Dim arr() As String ' Установить массив в слот от 0 до 1 ReDim arr(0 to 2) arr(0) = "Apple" arr(1) = "Orange" arr(2) = "Pear" ' Измените размер и сохраните исходный контент ReDim Keep arr(0 To 5) End Sub
Из приведенных ниже скриншотов видно, что исходное содержимое матрицы «сохранилось».
Предупреждение: в большинстве случаев вам не нужно изменять размер массива, как мы это делали в этом разделе. Если вы изменяете размер массива несколько раз, рассмотрите возможность использования коллекции.
Использование Preserve с 2D-матрицами
Preserve работает только в верхней части массива.
Например, если у вас есть двумерный массив, вы можете сохранить только второе измерение, как показано в следующем примере:
Sub Preserve2D() Dim arr() As Long ' Установить начальный размер ReDim arr(1 до 2, 1 до 5) ' Измените размер верхнего измерения ReDim Preserve arr(1 до 2, 1 до 10) End Sub
Если мы попытаемся использовать Preserve на нижней границе, мы получим ошибку «Index out of range».
В следующем коде мы используем Preserve для первого измерения. Выполнение этого кода приведет к ошибке «Индекс вне диапазона»:
Sub Preserve2DError() Dim arr() As Long 'Установить начальный размер ReDim arr(1–2, 1–5) 'Ошибка вне диапазона ReDim Preserve arr(1–5, 1–5) End Sub
Когда мы читаем из диапазона в массив, он автоматически создает двумерный массив, даже если у нас есть только один столбец.
Применяются те же правила консервации. Мы можем использовать Preserve только на верхнем краю, как показано в следующем примере:
Sub Preserve2DRange() Dim arr As Variant ' Назначить диапазон массиву arr = Sheet1.Range("A1:A5"). Значение 'Preserve будет работать только на верхнем пределе ReDim Preserve arr(1 To 5, 1 To 7) End Sub
Сортировка массива
В VBA нет функции для сортировки массива. Мы можем сортировать ячейки на листе, но это медленно, если данных много.
Приведенную ниже функцию быстрой сортировки можно использовать для сортировки массива.
Sub QuickSort(arr As Variant, first As Long, last As Long) Dim vCentreVal As Variant, vTemp As Variant Dim lTempLow As Long Dim lTempHi As Long lTempLow = first lTempHi = last vCentreVal = arr((first + last) / 2) Do Пока lTempLow first lTempHi = lTempHi = lTempHi - 1 Затем lTempHi - 1 vTemp = arr lTempLow) arr(lTempHi) = arr(lTempHi) arr(lTempHi) = vTemp ' Перейти к следующим позициям lTempLow = lTempLow + 1 lTempHi = lTempHi - 1 End If Loop If first Вы можете использовать эту функцию следующим образом: Sub TestSort() 'Создать временный массив Dim arr() As Variant arr = Array("Банан", "Дыня", "Персик", "Слива", "Яблоко") ' Сортировать массив QuickSort arr, LBound(arr), UBound (arr) ' Печать массива в немедленном окне (Ctrl + G) Dim i As Long For i = LBound (arr) To UBound (arr) Debug.Print arr (i) Next i End Sub
Передача массива в Sub или функцию
Иногда вам нужно передать массив процедуре. Вы объявляете параметр, используя круглые скобки, так же, как вы объявляете динамический массив.
переход к процедуре с ByRef означает передачу ссылки на массив. Таким образом, если вы измените массив в процедуре, он будет изменен, когда вы вернетесь.
Примечание. Когда вы используете массив в качестве параметра, он не может использовать ByVal, он должен использовать ByRef. Вы можете передать массив с помощью ByVal, сделав параметр Variant.
' Передать массив в общедоступную подфункцию PassToProc() Dim arr(0 To 5) As String ' Передать массив в функцию UseArray arr End Sub Public Function UseArray(ByRef arr() As String) ' Использовать массив Debug.Print UBound(arr) End функция
Возвращение массива из функции
Важно помнить следующее. Если вы хотите изменить существующий массив в процедуре, вы должны передать его в качестве параметра с помощью ByRef (см последний раздел). Вам не нужно возвращать массив из процедуры.
Основной причиной возврата массива является использование процедуры для создания нового массива. В этом случае вы присваиваете возвращаемый массив массиву в вызывающей программе. Этот массив уже не может быть выделен. Другими словами, вы должны использовать динамический массив, который не распределен.
Следующие примеры показывают это:
Public Sub TestArray() ' Объявить динамический массив - нераспределенный Dim arr() As String ' Возвратить новый массив arr = GetArray End Sub Public Function GetArray() As String() ' Создать и выделить новый массив Dim arr(0 To 5) As string ' Возвращаемый массив GetArray = arr End Function
Двумерные массивы
Массивы, которые мы рассматривали до сих пор, были одномерными. Это означает, что массивы представляют собой один список элементов.
Двумерный массив представляет собой список списков. Если вы думаете об одной строке в электронной таблице как об одном измерении, более одного столбца являются двумерными. Фактически электронная таблица эквивалентна двумерному массиву. Он имеет два измерения — строки и столбцы.
Небольшое замечание: Excel обрабатывает одномерный массив как строку, если вы записываете его в электронную таблицу. Другими словами, массив arr(1 to 5) эквивалентен arr(1 to 1, 1 to 5) при записи значений в электронную таблицу.
На следующем рисунке показаны две группы данных. Первый представляет собой одномерный массив, а второй — двумерный массив.
Чтобы получить доступ к элементу в первом наборе данных (одномерном), все, что вам нужно сделать, это указать строку, например. 1, 2, 3 или 4.
Для второго набора данных (двумерного) необходимо указать строку И столбец. Таким образом, вы можете думать об 1-мерном как о нескольких столбцах и одной строке, а о 2-мерном как о нескольких строках и нескольких столбцах.
Примечание. Массив может иметь более двух измерений. Это редко необходимо. Если вы решаете проблему с трехмерным массивом, вероятно, есть лучший способ сделать это.
Вы объявляете двумерный массив следующим образом:
Dim ArrayMarks (от 0 до 2, от 0 до 3) As Long
В следующем примере создается случайное значение для каждого элемента массива и печатается значение в непосредственном окне.
Public Sub TwoDimArray() ' Объявить двумерный массив Dim arrMarks(0 To 3, 0 To 2) As String ' Заполнить массив значениями i и j Dim i As Long, j As Long For i = LBound(arrMarks) To UBound (arrMarks) For j = LBound(arrMarks, 2) To UBound(arrMarks, 2) arrMarks(i, j) = CStr(i) & ":" & CStr(j) Next j Next i ' Вывести значения в массиве в Immediate Window Debug.Print "i", "j", "Value" For i = LBound(arrMarks) To UBound(arrMarks) For j = LBound(arrMarks, 2) To UBound(arrMarks, 2) Debug. Вывести i, j, arrMarks(i, j) Next j Next i End Sub
Вы можете видеть, что мы используем второй цикл For внутри первого цикла для доступа ко всем элементам.
Результат примера выглядит следующим образом:
Этот макрос работает так:
- Входит в I цикл
- я установлен на 0
- петля Enter j
- j установлен на 0
- j установлен в 1
- j установлен на 2
- Закончить петлю j
- я настроен на 1
- j установлен на 0
- j установлен в 1
- j установлен на 2
- И так далее, пока i = 3 и j = 2
Обратите внимание, что LBound и UBound имеют второй аргумент, равный 2. Это указывает, что это верхняя или нижняя граница второго измерения. Это начальное и конечное местоположение j. Значение по умолчанию равно 1, поэтому нам не нужно указывать его для i-loop.
Используйте один для каждой петли
использование for each лучше всего использовать при чтении из массива.
Возьмем приведенный выше код, который печатает двумерный массив.
'для цикла требуется два цикла отладки. Выведите «i», «j», «Value» For i = LBound(arrMarks) To UBound(arrMarks) For j = LBound(arrMarks, 2) To UBound(arrMarks, 2) Debug . Вывести i, j, arrMarks(i, j) Next j Next i
Теперь давайте перепишем его, используя цикл For Each. Как видите, нам нужен только один цикл, поэтому написать его намного проще:
' Использование For Each требует только одного цикла Debug.Print "Value" Dim mark As Variant For Each mark In arrMarks Debug.Print mark Next mark
использование цикла For Each дает нам массив только в одном порядке, от LBound до UBound. В большинстве случаев это все, что вам нужно.
Чтение из диапазона ячеек в массив
Если вы читали мою статью о ячейках и диапазонах, то знаете, что VBA имеет чрезвычайно эффективный способ чтения из массива ячеек в массив и наоборот.
Public Sub ReadToArray() ' Объявить динамический массив Dim StudentMarks As Variant ' Считать значения в массив из первой строки StudentMarks = Range("A1:Z1"). Значение 'Записать значения обратно в третью строку Range("A3:Z3"). Значение = StudentMarks End Sub
Динамический массив, созданный в этом примере, будет двумерным массивом. Как видите, мы можем прочитать целую строку ячеек в массив всего за одну строку.
В следующем примере приведенный ниже образец данных об ученике считывается из листа C3:E6 Sheet1 и печатается в текущем окне.
Public Sub ReadAndDisplay() ' Получить диапазон Dim rg As Range Set rg = ThisWorkbook.Worksheets("Sheet1").Range("C3:E6") ' Создать динамический массив Dim StudentMarks As Variant ' Считать значения в массив с листа 1 StudentMarks = rg.Value ' Вывести значения массива Debug.Print "i", "j", "Value" Dim i As Long, j As Long For i = LBound(StudentMarks) To UBound(StudentMarks) For j = LBound(StudentMarks , 2) To UBound(StudentMarks, 2) Debug.Print i, j, StudentMarks(i, j) Next j Next i End Sub
Как видите, первое измерение (доступное через i) массива — это строка, а второе — столбец. Чтобы продемонстрировать это, давайте посмотрим на значение 44 в E4 данных примера. Это значение находится в строке 2 в столбце 3 наших данных. Вы можете видеть, что 44 хранится в массиве StudentMarks(2,3).
Как заставить ваши макросы работать на суперскорости
Если ваши макросы очень медленные, этот раздел будет очень полезен. Особенно, если вы имеете дело с большими объемами данных. В VBA это держится в секрете.
Обновление значений в массивах экспоненциально быстрее, чем обновление значений в ячейках.
В предыдущей части вы увидели, как мы можем легко читать из группы ячеек в массив и наоборот. Если мы обновляем много значений, мы можем сделать следующее
- Копировать данные из ячеек в массив.
- Измените данные в массиве.
- Скопируйте обновленные данные из массива обратно в ячейки.
Например, следующий код будет намного быстрее, чем приведенный ниже код:
Public Sub ReadToArray() 'Чтение значений в массив из первой строки Dim StudentMarks As Variant StudentMarks = Range("A1:Z20000"). Value Dim i As Long For i = LBound(StudentMarks) To UBound(StudentMarks) ' Обновить отметки здесь StudentMarks (i, 1) = StudentMarks(i, 1) * 2 '... Next i ' Записать новые значения обратно на лист Range("A1:Z20000"). Значение = StudentMarks End Sub Sub UsingCellsToUpdate() Dim c As Variant For Each ci range («A1: Z20000») c.Value = ' Обновить значения здесь Next c End Sub
кроме того, сопоставление одного набора ячеек с другим выполняется намного быстрее, чем копирование и вставка.
' Место назначения — более быстрый диапазон ("A1: A10"). Значение = диапазон ("B1: B10"). Значение ' Копировать и вставить - более медленный диапазон ("B1: B1"). Место назначения: = диапазон ("A1: A10")
Заключение
Ниже приводится краткое изложение основных моментов этой статьи.
- Массивы — это эффективный способ хранения списка элементов одного типа.
- Вы можете получить доступ к элементу массива напрямую, используя его номер местоположения, известный как его индекс.
- Распространенная ошибка «Индекс вне диапазона» вызвана доступом к несуществующему местоположению.
- Существует два типа массивов: статические и динамические.
- Статический используется, когда размер массива всегда одинаков.
- Динамические массивы позволяют определить размер массива во время выполнения.
- LBound и UBound обеспечивают безопасный способ поиска наименьшего и наибольшего индексов в массиве.
- Основная матрица одномерная. Существуют также многомерные массивы.
- Чтобы просто передать массив процедуре, используйте ByRef. Вы делаете это так: ByRef arr() как long.
- Вы можете вернуть массив из функции, но массив, которому он назначен, не обязательно должен быть выделен в данный момент.
- Электронная таблица с ее строками и столбцами по существу представляет собой двумерную матрицу.
- Вы можете напрямую читать из области листа двумерный массив всего одной строкой кода.
- Вы также можете записать из 2D-массива в диапазон всего одной строкой кода.
