VBA-Урок 13.2. Использование массивов (Arrays) (Продолжение)

Для того, чтобы сохранить более одного столбца данных, нам необходима другая размерность массива. например:

Сохранение данных в двумерный массив:

'Декларирование
Dim array_example (10, 2) '11 x 3 "определенный" массив

'Сохранение данных в массиве
For i = 0 To 10
     array_example(i, 0) = Range("A" & i + 2)
     array_example(i, 1) = Range("B" & i + 2)
     array_example(i, 2) = Range("C" & i + 2)
Next

Далее есть несколько примеров работы с этими значениями:

MsgBox array_example (0, 0) '=> возвращает: 03.11.2026
MsgBox array_example (0, 1) '=> возвращает: 24
MsgBox array_example (9, 2) '=> возвращает: NO
MsgBox array_example (10, 2) '=> возвращает: YES

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

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

Чтобы узнать номер строки последней непустой ячейки, или другими словами, последнюю строку нашей базы данных, мы используем следующую формулу:

last_row = Range("A1").End(xlDown).Row

Excel не принимает переменные в декларации.

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

Dim array_example ()
ReDim array_example (last_row - 2, 2)

Используя следующую процедуру, вы можете сохранить все строки вашего набора данных (таблицы) в нашем массиве:

Dim array_example()
ReDim array_example(last_row - 2, 2)

Ubound

В предыдущем примере, последний номер в нашем массиве был last_row - 2:

For i = 0 To last_row - 2

Другой способ, чтобы определить последний номер в нашем массиве, мог бы быть через использование Ubound :

For i = 0 To UBound (array_example)

Эта функция возвращает наибольший номер в массиве для выбранного измерения (первое измерение есть по-умолчанию).

Далее есть несколько примеров, которые прояснят это подробнее:

Sub example()
     Dim array_example(10, 2)
     
     MsgBox UBound (array_example) '=> возвращает: 10
     MsgBox UBound (array_example, 1) '=> возвращает: 10
     MsgBox UBound (array_example, 2) '=> возвращает : 2
End Sub

Сохранение данных в диапазоне элементов массива

Есть возможным заполнить массив значениями из диапазона ячеек на рабочем листе даже без использования цикла:

'Декларирование
Dim array_example (10, 2) '11 x 3 "определенный" массив

'Сохранение данных в массиве
For i = 0 To 10
     array_example(i, 0) = Range("A" & i + 2)
     array_example(i, 1) = Range("B" & i + 2)
     array_example(i, 2) = Range("C" & i + 2)
Next

Предыдущий код может быть эффективно заменен этим:

'Декларирование
Dim array_example ()

'Сохранение данных в массиве
array_example = Range("A2:C12").Value

Хотя второй метод кажется более привлекательным, чем первый, будьте осторожны, что в большинстве случаев он может вам стоить больше времени на выполнение, чем первый.

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

Далее в процессе увеличения кода, если вы решите сохранить только данные, которые соответствуют определенным критериям поиска в массиве (или проводить совсем другую операции), вам придется полностью переписать код, используя другую функцию цикла ...

Но этот второй метод является весьма полезным, если вам нужно сохранить все содержимое большого набора данных, потому что это быстрее, чем циклом (экономит примерно 0,2 секунд на каждые 15 000 записей).

Массив (Array)

Но если вам нужно создать массив, который имеет "фиксированное" содержание.

Одним из решений могло бы быть прописать значение строчка за строчкой:

Dim en(5)

en(0) = "IF"
en(1) = "VLOOKUP"
en(2) = "SUM"
en(3) = "COUNT"
en(4) = "ISNUMBER"
en(5) = "MID"

К счастью, вы можете упростить этот код, используя массив ( Array ):

en = Array ("IF", "VLOOKUP", "SUM", "COUNT", "ISNUMBER", "MID")

Вот демонстрация использования функции Replace (это поможет вам понять следующий пример):

Sub replace_example()
     Dim var_translate As String

     'Текстовая строчка для этого примера
     var_translate = "Hello World!"
    
     'Замена "World" на "you" в текстовой строке
     var_translate = Replace (var_translate, "World", "you")

     'Строка после замены
     MsgBox var_translate '=> возвращает "Hello you!"
End Sub

Теперь, если мы хотим заменить ряд значений другим набором данных, использование массивов и Array функции будет чрезвычайно полезным:

Sub translate () 'Упрощенный пример перевода формул с английского на французский
     Dim var_translate As String

     'Текстовая строчка для этого примера
     var_translate = "Formula to translate: SUM (IF (ISNUMBER (A1: E1), A1: E1,0))"
     
     'Два набора значений
     en = Array ("IF", "VLOOKUP", "SUM", "COUNT", "ISNUMBER", "MID")
     fr = Array ("SI", "RECHERCHEV", "SOMME", "NB", "ESTNUM", "STXT")
     
     'Замена" SI "на" IF "," RECHERVEV "на" VLOOKUP "и т.д.
     For i = 0 To UBound (en)
         var_translate = Replace (var_translate, en (i), fr (i))
     Next

     'Строка после замены
     MsgBox var_translate '=> возвращает "Formula to translate : SOMME(SI(ESTNUM(A1:E1),A1:E1,0))"
End Sub

Разделение (Split)

Функция Split позволяет нам превратить символьную строку в массив.

Чтобы превратить строку в массив, сделайте следующее:

variable = "IF/VLOOKUP/SUM/COUNT/ISNUMBER/MID"

Используйте функцию Split и укажите разделитель:

en = Split (variable, "/")

Массив en вернет следующие значения:

MsgBox en(0)  '=> возвращает: IF
MsgBox en (1) '=> возвращает: VLOOKUP
MsgBox en (2) '=> возвращает: SUM
MsgBox en (3) '=> возвращает: COUNT
MsgBox en (4) '=> возвращает: ISNUMBER
MsgBox en (5) '=> возвращает: MID

Следующие 3 массивы также вернут те же значения:

en = Array("IF", "VLOOKUP", "SUM", "COUNT", "ISNUMBER", "MID")
en = Split("IF,VLOOKUP,SUM,COUNT,ISNUMBER,MID", ",")
en = Split("IF VLOOKUP SUM COUNT ISNUMBER MID", " ")

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

MsgBox Split("IF,VLOOKUP,SUM,COUNT,ISNUMBER,MID", ",")(2) '=> возвращает : SUM

Обратной к Split является функция Join .

Эта функция собирает значение массива в строку.

MsgBox Join (Array (1, 2, 3, 4, 5), "") '=> возвращает: 12345

Статьи по теме: