Перейти к содержимому
Наше приложение «Дешевые авиабилеты» в AppGallery >>

VBA Excel. Массивы (одномерные, многомерные, динамические)

    Массивы в VBA Excel: одномерные, многомерные и динамические. Объявление и использование массивов. Операторы Public, Dim и ReDim. Функции Array, LBound, UBound.

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

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

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

    В первом случае публичный массив содержит 10 элементов от 0 до 9 (нижний индекс по умолчанию — 0, верхний индекс — 9), а во втором случае локальный массив содержит 9 элементов от 1 до 9.

    По умолчанию VBA Excel считает в массивах нижним индексом нуль, но, при желании, можно сделать нижним индексом по умолчанию единицу, добавив в самом начале модуля объявление «Option Base 1».

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

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

    Третий массив состоит из 10000 элементов — 10×10×10×10.

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

    Динамические массивы в VBA Excel, в отличие от статических, объявляются без указания размерности:

    Такие массивы используются, когда заранее неизвестна размерность, которая определяется в процессе выполнения программы. Когда нужная размерность массива становится известна, она в VBA Excel переопределяется с помощью оператора ReDim:

    При переопределении размерности массива вместо верхнего индекса можно использовать переменную:

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

    С помощью оператора ReDim невозможно изменить обычный массив, объявленный с заранее заданной размерностью. Попытка переопределить размерность такого массива вызовет ошибку компиляции с сообщением: Array already dimensioned (Массив уже измерен).

    При переопределении размерности динамических массивов в VBA Excel теряются значения их элементов. Чтобы сохранить значения, используйте оператор Preserve:

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

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

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

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

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

    Приведу два примера, где не обойтись без массивов.

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

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

    Функции Array, LBound, UBound

    Функция Array

    Функция Array возвращает массив элементов типа Variant из первоначального списка элементов, перечисленных через запятую. Нумерация элементов в массиве начинается с нуля. Обратиться к элементу массива можно, указав в скобках его номер (индекс).

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

    Функция LBound

    Функция LBound возвращает значение типа Long, равное наименьшему (нижнему) доступному индексу в указанном измерении массива.
    Синтаксис:
    LBound (arrayname[, dimension])

    • arrayname — это имя переменной массива, является обязательным аргументом;
    • dimension — это номер измерения массива, необязательный аргумент, по умолчанию принимает значение 1.

    Наименьший индекс по-умолчанию может быть равен 0 или 1 в зависимости от настроек оператора Option Base. Нижняя граница архива, полученного с помощью функции Array, всегда равна 0.

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

    Функция UBound

    Функция UBound возвращает значение типа Long, равное наибольшему (верхнему) доступному индексу в указанном измерении массива.
    Синтаксис:
    UBound( arrayname[, dimension])

    • arrayname — это имя переменной массива, является обязательным аргументом;
    • dimension — это номер измерения массива, необязательный аргумент, по умолчанию принимает значение 1.

    Функция UBound используется вместе с функцией LBound для определения размера массива.

    Скопируйте код в модуль VBA Excel и запустите его на выполнение. Информационное сообщение MsgBox покажет значения наименьшего и наибольшего индекса переменной массива a.

    Обход массива циклом

    Обход одномерного массива циклом For… Next, в котором для определения границ массива используются функции UBound и LBound:

    Результат работы цикла вы увидите в окне Immediate.

    Очистка (обнуление) массивов

    Первый способ

    Очистить любой массив, статический или динамический, без использования цикла можно с помощью оператора Erase. Термин «обнуление» можно применить только к массиву числового типа.

    Обратите внимание, что оба массива при таком способе очистки будут возвращены в исходное состояние, которое они имели сразу после объявления:

    • статический Massiv1 сохранит размерность (4, 3);
    • динамический Massiv2 не сохранит размерность ().

    Второй способ

    Динамический массив можно очистить (обнулить) без использования цикла с помощью оператора ReDim. Просто переопределите его с той же размерностью.


    Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

    40 комментариев для “VBA Excel. Массивы (одномерные, многомерные, динамические)”

    1. Это все очень интересно и здорово, но хотелось бы еще узнать о заполнении массивов.
      Например один цикл поможет заполнить одномерный массив. Два цикла двумерный. Но это в случае когда значения рассчитываются.
      А если у меня есть двумерный массив с данными (постоянными) и мне надо его «ввести» в программу — как быть? Самый простой пример — календарь. В строках первый элемент месяц, второй элемент 1 число, третий элемент день недели, четвертое — последний день месяца, пятый — день недели последнего числа месяца.
      То есть на входе у меня 12 строк, 5 столбцов, массив 5х12.
      Теперь мне надо его заполнить, 60 значений. Цикл не подойдет, надо каждое вносить отдельно.
      Чисто теоретически можно представить массив как одномерный массив строк, каждый элемент которого состоит из одномерного массива столбцов. А вот как это присвоение оформить в ВБА?

    2. Увы, не могу. Дело в том, что :
      1. Я пишу не в Екселе, а в Автокаде — там нет ячеек.
      2. Мои данные это по сути константы, мне придется их заносить в ячейки (если делать нечто подобное в Екселе), а затем считывать программно. При этом с листа (из ячеек) их надо прятать, что бы случайно никто не изменил. Поэтому данные необходимо вводить в теле программы в массив и далее с ними работать. В то же время необходимо сделать так, что бы при желании было легко добавить еще одну строку с данными.

    3. Евгений

      Артем, я правильно понял, что вы вносите все данные в массив вручную и хотите сделать автоматическое добавление «пустых строк» в массив под новые данные?

    4. Нет, вручную вношу до 50 «строк», вручную добавляю (при необходимости, а необходимость возникает не часто) по 1-15 строк.
      В общем на одном форуме подсказали несколько вариантов. Один из наиболее подходящих (для меня) оказался массив массивов. То есть создается массив, каждый элемент которого это тоже массив, содержащий наименование и далее параметры.
      При необходимости просто добавляю элемент массива (строку) с наименованием и параметрами и все.
      А вот как правильно обратится к каждому элементу массива и как работать с массивом массивов — вот это хорошо бы у вас в статье отобразить.

    5. Евгений

      Иван, размерность массива (количество измерений) вы можете узнать с помощью пользовательской функции KolichestvoIzmereniy.

    6. to Артем:
      если массив в автокаде надо заполнять какими-то постоянными данными, то почему бы не хранить эти данные в отдельном файле (напр. txt или dbf) и считывать данные оттуда.

    7. Msgbox не переваривает «;» когда ввожу &amp он пилит её на & amp и никак не учитывает, но стоит добавить в конец «;» чтобы получилось & всю строку выделяет красным!

      1. Евгений

        Привет, Павел!
        Это защита WordPress заменила амперсанд & на его html-код. Исправил.

    8. А как с помощью Array инициализировать, например двумерный массив.
      В одном пособии увидел такой код.

      Но VBA выводит ошибку

      1. Евгений

        Андрей, у меня ваш способ записи двумерного массива в переменную работает:

        Нумерация в измерениях массива начинается с единицы.

    9. Владимир

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

      Бывают случаи, что данных нет и строка остается пустой. Далее идет такой код :
      arrNew = Split(str, "|") , т.е. массив заполняется подстроками строки «str»
      Так вот, после наполнения массива пустой строкой, Ubound(arrNew) = -1, а Lbound(arrNew) = 0.
      Я не понимаю, как такое возможно, ведь по сути массив пустой?

      1. Евгений

        Привет, Владимир!
        Функция Split всегда возвращает массив. Массив arrNew(0 to -1) — это и есть пустой массив, без элементов. Если было бы arrNew(0 to 0) — это уже был бы массив с одним элементом.

        1. Владимир

          Добрый день, Евгений.
          У меня были такие подозрения, но нигде не мог получить этому подтверждения. В справке по Split есть указание о возвращении пустого массива, но ничего не сказано, про его размерность. В Ubound также нет на это ссылок. Сам я — глубокий любитель в VBA. Поэтому разбираюсь со всеми задачами с помощью Уокенбаха и Интернета. Спасибо за разъяснения.

    10. как и какую указать переменную для массива, если его размерность задается пользователем

      машина ругается:
      Constant expression required

      1. P.S.
        оператора ReDim — не решает проблему

        Array already dimensioned

        1. Евгений

          Добрый вечер!
          У вас чередуются символы «c» в переменной «qc», то кириллицей, то латиницей. Проверяйте — ReDim работает:

          1. всем и всегда указывал на кириллицу и сам же лохонулся …

            подскажите еще один вопросик

            Как вывести на Лист RND-массив в размере qr*qc ?

            1. Евгений

              Нужно заполнить массив случайными числами и вставить на лист:

    11. Владимир

      Как вытащить массив из ComboBox? Имеется большой список организаций и хочу сделать автопоиск по ComboBox.
      Но для этого нужно считать все данные в обычный массив. Вылетает ошибка: Выход за пределы диапазона ИЛИ несоответствие типов.

      1. Евгений

        Добрый день, Владимир!
        А ComboBoxSpisokKontragent — это имя листа или элемента управления ComboBox?

        1. Владимир

          Это сам ComboBox. Список этот зависимый, поэтому не могу использовать диапазон с листа. Нужно именно из Бокса вытащить массив. Вот код заполнения Бокса

          Сейчас всё работает, но очень большой список, нужен автопоиск.

          1. Евгений

            Попробуйте так:

            Будет создан двухмерный массив: первое измерение — строки, второе — столбец. Ваш список: Spisok(1, 1), Spisok(2, 1), Spisok(3, 1) и т.д.

            1. Владимир

              Спасибо. Как я сказал, список зависимый и формируется из разных диапазонов по выбору OptionBatton. Поэтому завел глобальную переменную для сохранения пути диапазона и всё заработало!

        2. Здравствуйте.
          В начале статьи есть фраза:
          "Вместо верхнего индекса можно использовать переменную".
          Не могли бы Вы привести примерчик, а то у меня что-то не выходит. Заранее спасибо!

          1. Евгений

            Добрый день, Сергей!

            1. Спасибо за скорый ответ.
              Но по контексту привлекший меня фразы, я подумал, что можно с помощью переменной задать верхнюю границу в Dim, т.е. для статического массива. Если такое невозможно, то, наверно, стоит какой-то поправить эту фразу, дабы не вводить в заблуждение таких неофитов, как я. Спасибо.

              1. Евгений

                Спасибо, Сергей, за важное уточнение. Перенес эту информацию в параграф "Динамические массивы".

    12. Массивы. Нумерация элементов.
      С одномерным массивом всё понятно …
      1-ый, 2-ой, 3-ий … N-ый элемент
      как найти 12-ый элемент в многомерном массиве
      напр. mass(7, 9) (массив начинается на А1)
      нумерация слева — направо или сверху вниз ?

      1. Евгений

        Первое измерение двумерного массива — строка, второе — столбец.

        В диапазоне нумерация ячеек идет слева-направо построчно. В массивах такой нумерации нет.

        Но посчитать, как в диапазоне, мы можем:

        12/9=1 — одна целая строка, остаток — 3, который переносится на вторую строку. Получаем результат: 12-ый элемент находится на пересечении второй строки и третьего столбца, что соответствует элементу массива — mass(2, 3).

    13. Евгений

      Помогите, плиз. На присвоении d mas2(i) становится двумерным и выдает ошибку диапазона. Как получить доступ к значению?

      1. Евгений Ф

        Здравствуйте, Евгений!
        Массив становится двумерным при присвоении ему значений диапазона в этой строке:

        Нумерация строк начинается с единицы, столбец один — единица.

    14. Алексей

      Евгений, здравствуйте!
      Подскажите пожалуйста, как можно отсортировать (соединить) два массива с данными (данные могут повторяться в массивах, массивы могут иметь разную длину) так, чтобы был массив со всеми данными, но данные не повторялись. Данные могут быть как числовыми, так и строковыми.
      ‘ Задаем значения для первого массива
      arr1 = Range("B3:B12") '347 349 т527 427 ц467 678 345 u234 235 346
      ‘Задаем значения для второго массива
      arr2 = Range("D3:D12") '370 869 317 н862 590 347 615 ц467 427 345 u234 346

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

      1. Евгений

        Здравствуйте, Алексей!
        Это можно сделать с помощью отбора уникальных значений из массивов в коллекцию и последующего копирования полученных данных из коллекции в новый массив:

        Данные в примере копируются в одномерный массив, если нужен — двумерный, можно переопределить размерность нового массива так:

        Заполняем его:

        1. Алексей

          Евгений! Спасибо Вам большое за помощь!

    Обсуждение закрыто.