Массивы в Excel VBA

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

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

Задача	Статический массив	Динамический массив
Объявление	Dim arr(0 To 5) As Long	Dim arr() As Long Dim arr As Variant
Задать размер	см. выше	ReDim arr(0 To 5)As Variant
Получить размер(количество элементов)	см. функцию ArraySize .	см. функцию ArraySize
Увеличить размер (с сохранением данных)	Только динамический массив	ReDim Preserve arr(0 To 6)
Задать значение	arr(1) = 22	arr(1) = 22
Получить значение	total = arr(1)	total = arr(1)
Первый элемент	LBound(arr)	LBound(arr)
Последний элемент	Ubound(arr)	Ubound(arr)
Прочитать все элементы(1D)	For i = LBound(arr) To UBound(arr) Next i Or For i = LBound(arr,1) To UBound(arr,1) Next i	For i = LBound(arr) To UBound(arr) Next i Or For i = LBound(arr,1) To UBound(arr,1) Next i
Прочитать все элементы(2D)	For i = LBound(arr,1) To UBound(arr,1)   For j = LBound(arr,2) To UBound(arr,2)   Next j Next i	For i = LBound(arr,1) To UBound(arr,1)   For j = LBound(arr,2) To UBound(arr,2)   Next j Next i
Прочитать все элементы	Dim item As Variant For Each item In arr Next item	Dim item As Variant For Each item In arr Next item
Передать в процедуру	Sub MySub(ByRef arr() As String)	Sub MySub(ByRef arr() As String)
Вернуть из функции	Function GetArray() As Long()     Dim arr(0 To 5) As Long     GetArray = arr End Function	Function GetArray() As Long()     Dim arr() As Long     GetArray = arr End Function
Получить из функции	Только динамические массивы	Dim arr() As Long Arr = GetArray()
Стереть массив	Erase arr *сбрасывает все значения по умолчанию	Erase arr *удаляет массив
Строку в массив	Только динамические массивы	Dim arr As Variant arr = Split(“James:Earl:Jones”,”:”)
Массив в строку	Dim sName As String sName = Join(arr, “:”)	Dim sName As String sName = Join(arr, “:”)
Заполнить значениями	Только динамические массивы	Dim arr As Variant arr = Array(“Значение1”, “Значение2”, “Значение3”)
Диапазон в массив	Только динамические массивы	Dim arr As Variant arr = Range(“A1:D2”)
Массив в диапазон	также как в динамическом массиве	Dim arr As Variant Range(“A5:D6”) = arr

Типы массивов VBA

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

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

Статический массив объявляется следующим образом

Public Sub DecArrayStatic()

    ' создаем массив с элементами 0,1,2,3
    Dim arrMarks1(0 To 3) As Long

    ' массив с элементами 0,1,2,3
    Dim arrMarks2(3) As Long

    ' массив с элементами 1,2,3,4,5
    Dim arrMarks3(1 To 5) As Long

    ' массив с элементами 2,3,4 ' редко используется
    Dim arrMarks4(2 To 4) As Long

End Sub

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

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

Динамические массивы не имеют таких проблем. Размер массивы не указывается при объявлении. Поэтому его размер может расти и уменьшаться по мере необходимости

Public Sub DecArrayDynamic()

    ' Объявление динамического массива
    Dim arrMarks() As Long

    ' Устанавливаем размер массива 
    ReDim arrMarks(0 To 5)

End Sub

Динамический массив не создается, пока вы не используете оператор ReDim. Преимущество в том, что вы можете подождать, пока не узнаете количество элементов, прежде чем устанавливать размер массива. Со статическим массивом вы должны указать размер заранее.

Чтение из диапазона ячеек в массив

VBA имеет очень эффективный способ чтения из диапазона ячеек в массив и наоборот

Public Sub ReadToArray()

    ' Объявляем динамический массив
    Dim StudentMarks As Variant

    ' Считываем данные в массив из первой строки
    StudentMarks = Range("A1:Z1").Value

    ' Записываем значения назад в третью строку
    Range("A3:Z3").Value = StudentMarks

End Sub

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

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

Public Sub ReadAndDisplay()

    ' Получаем диапазон
    Dim rg As Range
    Set rg = ThisWorkbook.Worksheets("Sheet1").Range("C3:E6")

    ' Создаем динамический массив
    Dim StudentMarks As Variant

    ' Записываем данные в массив 
    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

Как сделать выполнение ваших макросов супер скоростным

Если ваши макросы работают очень медленно, этот метод может оказаться очень полезным. Особенно если вы имеете дело с большими объемами данных.

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

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

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

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

Public Sub ReadToArray()

    ' Считываем данные в массив
    Dim StudentMarks  As Variant
    StudentMarks = Range("A1:Z20000").Value

    Dim i As Long
    For i = LBound(StudentMarks) To UBound(StudentMarks)
        ' Update marks here
        StudentMarks(i, 1) = StudentMarks(i, 1) * 2
        '...
    Next i

    ' Записываем новые значения на лист
    Range("A1:Z20000").Value = StudentMarks

End Sub

Sub UsingCellsToUpdate()
    
    Dim c As Variant
    For Each c In Range("A1:Z20000")
        c.Value = ' Обновляем значения 
    Next c
    
End Sub

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

' Ассоциирование диапазона- это быстро
Range("A1:A10").Value = Range("B1:B10").Value

' Копировать вставить - медленно
Range("B1:B1").Copy Destination:=Range("A1:A10")

Пример с динамическим диапазоном

Sub ReadingRange()
    
    Dim arr As Variant
    arr = shData.Range("A1").CurrentRegion

    Dim i as long
    For i = LBound(arr,1) + 1 to UBound(arr,1)
      arr(i,5) = arr(i,5) - 100
    next i

    shData.Range("H1").CurrentRegion.ClearContents
    Dim rowCount as Long, columnCount as Long
    rowCount  - UBound(arr,1)
    columnCount = UBound(arr,2)
    
    shData.Range("H1".Resize(rowCount, columnCount).Value = arr

End Sub