Все про массивы в VBA

Дональд Кнут

Список настолько силен, насколько сильно его самое слабое звено

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

Краткое руководство по массивам VBA

Домашнее задание	Статический повышение квалификации	Динамический повышение квалификации
Объявление	Dim arr (от 0 до 5) как Длина	Тусклый обр() Всегда Тусклый обр как вариант
Установить размер	Dim arr (от 0 до 5) как Длина	ReDim arr (от 0 до 5) как Вариант
Становимся ближе (спасти существующий данные)	Только динамичный	ReDim Сохранить обр (от 0 до 6)
Установить на ПК значения	матрица (1) = 22	матрица (1) = 22
Получить значения	всего = массив (1)	всего = массив (1)
Первая позиция	LBound (обр)	LBound (обр)
Последняя позиция	Вверх (вверх)	Вверх (вверх)
Чтение всех регистров (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 () как строка)
Возврат функции	Функция ПолучитьМассив()  AsLength()     Dim arr (от 0 до 5) как Длина     GetArray = обр Конечная функция	Функция ПолучитьМассив()  AsLength()     Тусклый обр() Всегда     GetArray = обр Конечная функция
Прибыть из функции	Только динамичный	Тусклый обр() Всегда  Арр = ПолучитьМассив()
Удалить массив	Удалить верх * Сбросить все значения по по умолчанию	Удалить верх * Удалить массив
Строка в массив	Только динамичный	Тусклый обр как вариант arr = Split(«Джеймс:Эрл:Джонс»,»:»)
Массив в строку	Dim sName как строка sName = Присоединиться (обр, «:»)	Dim sName как строка sName = Присоединиться (обр, «:»)
Заполнить значения	Только динамичный	Тусклый обр как вариант arr = Array(«Джон», «Хейзел», «Фред»)
Ранг в массив	Только динамичный	Тусклый обр как вариант массив = Диапазон(«A1:D2»)
Массив в диапазон	Как в динамичный	Тусклый обр как вариант Диапазон («A5: D6») = массив

Введение

В этой статье подробно рассматриваются массивы в языке программирования Excel VBA. Он охватывает такие важные моменты, как:

• Зачем тебе исправления
• Когда вы должны их использовать
• Два типа аранжировок
• Использование более чем одного измерения
• Объявление массива
• Добавление значений
• Посмотреть все статьи
• Суперэффективный способ чтения диапазона в массив

В первом разделе мы увидим, что такое массивы и зачем они нужны. Вы можете не понимать часть кода в первом разделе. Это хорошо. Я разобью это на простые термины в следующих разделах статьи.

Быстрые заметки

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

Массивы и циклы идут рука об руку. Наиболее распространенными циклами, которые вы используете с массивами, являются циклы For i и For Each.

Что такое массивы и зачем они нужны?

Массив VBA — это тип переменной. Он используется для хранения списков данных одного типа. Примером может служить сохранение списка стран или списка итогов за неделю.

В VBA обычная переменная может хранить только одно значение за раз.

В следующем примере показана переменная, используемая для хранения оценок учащегося.

‘ Одновременно может храниться только одно значение Dim Student1 As Integer Student1 = 55

Если мы хотим сохранить оценки другого ученика, нам нужно создать вторую переменную.

В следующем примере у нас есть оценки пяти учеников

 

 

Мы прочитаем эти метки и запишем их в Immediate Window.

Примечание. Функция Debug.Print записывает значения в Immediate Window. Чтобы увидеть это окно, выберите в меню 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 = .Ранг(«C2»).Смещение(2) Студент3 = .Ранг(«C2»).Смещение(3) Студент4 = .Ранг(«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 Целое число для 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 есть два типа массивов:

1. Статический: массив фиксированного размера.
2. Динамический: массив, размер которого устанавливается во время выполнения

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

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

Статический массив объявляется так

Public Sub DecArrayStatic() ‘Создать массив с ячейками 0,1,2,3 Dim arrMarks1(0 to 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 для заполнения массива списком элементов. Вы должны объявить массив как тип Variant. Следующий код показывает, как использовать эту функцию.

Dim arr1 As Variant arr1 = Array(«Апельсин», «Персик»,»Груша») Dim arr2 As Variant arr2 = Array(5, 6, 7, 8, 12)

 

 

Массив, созданный функцией Array, будет начинаться с нулевого индекса, если вы не используете опцию Base 1 в верхней части вашего модуля. Затем он начнется с первого индекса. В программировании обычно считается плохой практикой иметь ваши фактические данные в вашем коде. Однако иногда это полезно, когда вам нужно быстро протестировать какой-то код. Функция Split используется для разделения строки на массив на основе разделителя. Разделитель — это символ, например запятая или пробел, разделяющий элементы.

Следующий код разделит строку на массив из трех элементов.

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

Вы можете использовать цикл For Each с массивами. Важно помнить, что он доступен только для чтения. Это означает, что вы не можете изменить значение в массиве.

В следующем коде значение метки изменяется, но значение массива не изменяется.

Для каждой метки в arrMarks ‘ Не изменится значение метки массива = 5 * Rnd Следующая метка

Цикл For Each отлично подходит для чтения массива. Как видите, лучше писать специально для двумерного массива.

Затемнить метку как вариант для каждой метки в arrMarks Debug.Print метка Следующая метка

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

Функцию 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 ‘ ВСЕ ЗНАЧЕНИЯ УСТАНОВЛЕНЫ НА НОЛЬ Очистить arrMarks ‘Значения печати: теперь все нулевые Debug.Print «Location», «Value» For i = LBound(arrMarks) To UBound(arrMarks) Debug.Print i, arrMarks(i) Next i Finish sub

Теперь попробуем тот же пример с динамикой. После использования 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 до 2) arr(0) = «Apple» ‘ Теперь удален массив с яблоком ReDim arr(0 до 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) = «Gear» ‘ Измените размер и сохраните исходный контент ReDim Preserve arr(0 To 5) End Sub

На скриншотах ниже видно, что исходное содержимое массива «сохранилось».

 

 

 

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

Использование Preserve с 2D-массивами

Сохранение работает только в верхней части массива.

Например, если у вас есть двумерный массив, вы можете сохранить только второе измерение, как показано в следующем примере:

Sub Preserve2D() Dim arr() As Long ‘ Установить начальный размер ReDim arr(1 To 2, 1 To 5) ‘ Измените размер верхнего измерения ReDim Preserve arr(1 To 2, 1 To 10) End Sub

Если мы попытаемся использовать функцию «Сохранить нижнюю границу», мы получим ошибку «Индекс вне диапазона».

В приведенном ниже коде мы используем Preserve для первого измерения. Запуск этого кода приведет к ошибке «Индекс вне диапазона»:

Sub Preserve2DError() Dim arr() As Long ‘ Установить начальный размер ReDim arr(1 до 2, 1 до 5) ‘ Ошибка вне диапазона ReDim Preserve arr(1 до 5, 1 до 5) End Sub

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

Применяются те же правила хранения. Мы можем использовать только Keep on Top Edge, как показано в следующем примере:

Sub Preserve2DRange() Dim arr As Variant ‘ Назначить диапазон массиву arr = Sheet1.Range(«A1:A5»).Value ‘ 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 — 1 Loop If lTempLow Вы можете использовать эту функцию следующим образом: Sub TestSort() ‘Создать временный массив Dim arr() As Variant arr = Array(«Банан», «Дыня», «Персик», «Слива», «Яблоко») ‘ Сортировать массив QuickSort arr, LBound(arr), UBound (arr) ‘ Печать массива в ближайшее окно (Ctrl + G) Dim i While i = LBound (arr) To UBound (arr) Debug.Print arr (i) Next i End Sub

Передача массива в Sub или функцию

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

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

   

Примечание. Когда вы используете массив в качестве параметра, вы не можете использовать ByVal, вы должны использовать ByRef. Вы можете передать массив с помощью ByVal, сделав параметр вариантом.

‘ Передать массив в общедоступную подфункцию PassToProc() Dim arr(0 To 5) As String ‘ Передать массив в UseArray arr End Sub Public Function UseArray(ByRef arr() As String) ‘ Использовать массив Debug.Print UBound(arr) End function

Возвращение массива из функции

Важно помнить следующее. Если вы хотите изменить существующий массив в процедуре, вы должны передать его в качестве параметра с помощью 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.

Для второго (двумерного) набора данных необходимо указать строку И столбец. Таким образом, вы можете думать об одном измерении как о нескольких столбцах, а об одной строке и двух измерениях как о нескольких строках и нескольких столбцах.

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

Вы объявляете двумерный массив следующим образом:

Dim ArrayMarks (от 0 до 2, от 0 до 3), а

В следующем примере для каждого элемента массива создается случайное значение, которое печатается в окне Immediate.

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) A UBound (arrMarks) For j = LBound(arrMarks, 2) A UBound(arrMarks, 2) arrMarks(i, j) = CStr(i) & «:» & CStr(j) Next j Next i ‘ Вывести значения в массив в непосредственном окне Debug . Выведите «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 Finish 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.

Использование цикла For Each

Использование For Each лучше всего использовать при чтении из массива.

Возьмем приведенный выше код, который записывает двумерный массив.

‘ Цикл For требует двух циклов отладки. Выведите «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 как вариант ‘ Считать значения в массиве из первой строки StudentMarks = Range(«A1:Z1»).Value ‘ Записать значения обратно в третью строку Range(«A3:Z3 «).Value = StudentMarks End Sub

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

В следующем примере приведенный ниже образец данных об учащемся считывается из листа C3:E6 Sheet1 и печатается в окне Immediate.

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) For 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 это держится в секрете.

Обновление значений в массивах экспоненциально быстрее, чем обновление значений в ячейках.

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

1. Скопируйте данные из ячеек в массив.
2. Измените данные в массиве.
3. Скопируйте обновленные данные из массива в ячейки.

Например, следующий код будет намного быстрее, чем следующий код:

Public Sub ReadToArray() ‘ Считайте значения в массив из первой строки здесь StudentMarks(i, 1) = StudentMarks(i, 1) * 2 ‘… Next i’ Запишите новые значения обратно на лист Диапазон(«A1 : Z20000»). Значение = StudentMarks End Sub Sub UsingCellsToUpdate() Dim c как вариант для каждого c в диапазоне («A1: Z20000») c.Value = ‘Обновить значения здесь Next c End Sub

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

‘ Место назначения — Самый быстрый диапазон («A1: A10»). Значение = Диапазон («B1: B10»). Значение ‘ Копировать и вставить — Самый медленный диапазон («B1: B1»). Место назначения: = Диапазон («A1: A10»)

Заключение

Ниже приводится краткое изложение основных моментов этой статьи.

1. Массивы — это эффективный способ хранения списка элементов одного типа.
2. Вы можете получить доступ к элементу массива напрямую, используя номер позиции, известный как индекс.
3. Распространенная ошибка «Индекс вне диапазона» возникает при доступе к несуществующему местоположению.
4. Существует два типа массивов: статические и динамические.
5. Статический используется, когда размер массива всегда одинаков.
6. Динамические массивы позволяют определить размер массива во время выполнения.
7. LBound и UBound обеспечивают безопасный способ поиска самых маленьких и самых больших подписок в массиве.
8. Основной массив одномерный. Существуют также многомерные массивы.
9. Чтобы передать в процедуру только массив, используйте ByRef. Вы делаете это так: ByRef arr() всегда.
10. Вы можете вернуть массив из функции, но массив, которому он назначен, не должен быть назначен на этом этапе.
11. Рабочий лист с его строками и столбцами по существу представляет собой двумерный массив.
12. Вы можете напрямую читать из диапазона листов в двумерный массив всего одной строкой кода.
13. Вы также можете записать из 2D-массива в диапазон всего одной строкой кода.