Массивы в Excel VBA: типы и способы использования с примерами.

Автор: Шарлотта Миллер Шарлотта Миллер
Hours обновлено

⚡ Умное резюме

В Excel VBA массивы хранят группу связанных значений одного типа данных под одним именем, доступ к которым осуществляется по индексу. На этой странице рассматриваются статические и динамические массивы, одномерные, двумерные и многомерные массивы, синтаксис объявления и приведён пример загрузки напитков.

  • 📦 Концепция массива: Одно именованное место в памяти, содержащее множество значений одного типа данных, разделенных индексом.
  • 🎯 Преимущества: Массивы группируют связанные данные, уменьшают количество переменных и ускоряют извлечение и сортировку данных.
  • 📌 Статический массив: Объявляется с фиксированным размером, например, Dim ArrayMonth(12) As String, когда известно количество элементов.
  • 🔄 Динамический массив: Объявляется пустым с помощью Dim ArrayMonth(), а размер массива определяется позже с помощью ReDim, когда количество элементов неизвестно.
  • 📐 Габаритные размеры: Массив использует один, два или более индексов для моделирования отдельных списков, таблиц или многоуровневых данных.
  • 🧪 Рабочий пример: Кнопка команды загружает четыре названия напитков из массива в ячейки рабочего листа с помощью Sheet1.Cells.
Читать

Массивы Excel VBA

Что такое массив VBA?

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

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

В этом руководстве предполагается, что вы используете Microsoft Excel версия 2013. Знания по-прежнему применимы и к другим версиям Microsoft Эксель тоже.

Каковы преимущества массивов?

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

  1. Сгруппируйте логически связанные данные — допустим, вы хотите сохранить список студентов. Вы можете использовать одну переменную-массив, которая имеет отдельные местоположения для категорий учащихся, например детский сад, начальная, средняя, ​​старшая школа и т. д.
  2. Массивы упрощают написание поддерживаемого кода. Для одних и тех же логически связанных данных он позволяет определить одну переменную вместо определения нескольких переменных.
  3. Повышенная производительность — после определения массива становится быстрее извлекать, сортировать и изменять данные.

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

VBA поддерживает два типа массивов, а именно;

  • статический – Эти типы массивов имеют фиксированное заранее определенное количество элементов, которые можно хранить. Невозможно изменить размер типа данных статического массива. Это полезно, если вы хотите работать с известными объектами, такими как количество дней в неделе, пол и т. д.Для примера: Dim ArrayMonth(12) как строка
  • Dynamic – Эти типы массивов не имеют фиксированного заранее определенного количества элементов, которые можно хранить. Это полезно при работе с объектами, номер которых невозможно заранее определить.Для примера: Dim ArrayMonth() как вариант

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

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

Статические массивы

Синтаксис объявления СТАТИЧЕСКИЙ массивы выглядит следующим образом:

Dim arrayName (n) as datatype

ВОТ,

Code Экшн
Тип данных Dim arrayName (n)
  1. Он объявляет переменную массива с именем arrayName с размером n и типом данных. Размер относится к количеству элементов, которые может хранить массив.

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

Синтаксис объявления DYNAMIC массивы выглядит следующим образом:

Dim arrayName() as datatype
ReDim arrayName(4)

ВОТ,

Code Экшн
Тип данных Dim arrayName()
  1. Он объявляет переменную массива с именем arrayName без указания количества элементов.
Имя массива ReDim (4)
  1. Он определяет размер массива после его определения.

Размеры массива

Массив может быть одномерным, двухмерным или многомерным.

  • Одно измерение: в этом измерении массив использует только один индекс. Например, количество людей каждого возраста.
  • Два измерения: в этом измерении массив использует два индекса. Например, количество учеников в каждом классе. Требуется количество классов и количество учеников в каждом классе.
  • Многомерный: в этом измерении массив использует более двух индексов. Например, температура в дневное время. (30, 40, 20).

СТАТЬИ ПО ТЕМЕ

Как использовать массив в Excel VBA

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

  • Создать новый Microsoft Книгу Excel и сохраните ее как книгу Excel с поддержкой макросов (*.xlsm).
  • Добавление командной кнопки в книгу
  • Установите свойства имени и заголовка командной кнопки.
  • Программирование VBA, заполняющего лист Excel

Давайте выполним это упражнение шаг за шагом,

Шаг 1. Создайте новую книгу.

  1. Открыто Microsoft Excel
  2. Сохраните новую книгу как VBA Arrays.xlsm.

Шаг 2 – Добавьте командную кнопку

Примечание: В этом разделе предполагается, что вы знакомы с процессом создания интерфейса в Excel. Если вы не знакомы, прочитайте руководство Управление формой VBA Excel и управление ActiveX. Он покажет вам, как создать интерфейс.

  1. Добавляем командную кнопку на лист

Используйте массив в Excel VBA

  1. Установите для свойства name значение cmdLoadBeverages.
  2. Установите для свойства caption значение «Загрузить напитки».

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

Используйте массив в Excel VBA

Шаг 3 – Сохраните файл

  1. Нажмите кнопку «Сохранить как»
  2. Выберите книгу Excel с поддержкой макросов (*.xlsm), как показано на рисунке ниже.

Используйте массив в Excel VBA

Шаг 4 – Напишите код

Теперь мы напишем код для нашего приложения.

  1. Щелкните правой кнопкой мыши кнопку «Загрузить напитки» и выберите код просмотра.
  2. Добавьте следующий код в событие щелчка cmdLoadBeverages.
Private Sub cmdLoadBeverages_Click()
    Dim Drinks(1 To 4) As String

    Drinks(1) = "Pepsi"
    Drinks(2) = "Coke"
    Drinks(3) = "Fanta"
    Drinks(4) = "Juice"

    Sheet1.Cells(1, 1).Value = "My Favorite Beverages"
    Sheet1.Cells(2, 1).Value = Drinks(1)
    Sheet1.Cells(3, 1).Value = Drinks(2)
    Sheet1.Cells(4, 1).Value = Drinks(3)
    Sheet1.Cells(5, 1).Value = Drinks(4)
End Sub

ВОТ,

Code Экшн
Тусклые напитки (от 1 до 4) в виде строки
  • Он объявляет переменную массива Drinks. Первый индекс массива равен 1, а последний индекс массива равен 4.
Напитки(1) = «Пепси»
  • Присваивает значение Pepsi первому элементу массива. Другой аналогичный код делает то же самое для других элементов массива.
Sheet1.Cells(1, 1).Value = “Мои любимые напитки”
  • Записывает значение «Мои любимые напитки» в адрес ячейки A1. Sheet1 ссылается на лист, а Cells(1,1) ссылается на строку номер 1 и столбец 1 (B).
Sheet1.Cells(2, 1).Значение = Напитки(1)
  • Записывает значение элемента массива с индексом 1 во вторую строку столбца 1.

Тестирование нашего приложения

После добавления кода последний шаг — запустить кнопку и убедиться, что массив заполняет лист. Выберите вкладку «Разработчик» и убедитесь, что кнопка «Режим проектирования» выключена. Индикатор этого выключения будет белым, а не цветным (зеленоватым) фоном. (См. изображение ниже)

Тестирование приложения в Excel VBA

Нажмите кнопку «Загрузить напитки».

Вы получите следующие результаты

Тестирование приложения в Excel VBA

Загрузите Excel, содержащий приведенный выше код.

Скачайте указанный выше файл Excel. Code

СТАТЬИ ПО ТЕМЕ

Часто задаваемые вопросы (FAQ)

Статический массив имеет фиксированный размер, заданный при объявлении, например, Dim Arr(12). Динамический массив объявляется пустым с помощью Dim Arr() и изменяется в размере позже с помощью ReDim, если количество элементов заранее неизвестно.

ReDim устанавливает или изменяет размер динамического массива после его объявления. Добавление ключевого слова Preserve, как в ReDim Preserve Arr(10), сохраняет существующие значения при изменении размера; без Preserve все сохраненные значения очищаются.

Используйте функции LBound и UBound. Функция LBound возвращает наименьший индекс, а функция UBound — наибольший индекс. Количество элементов равно UBound(Arr) минус LBound(Arr) плюс один, что работает как для статических, так и для динамических массивов.

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

Да. Искусственный интеллект считывает ошибку «Индекс выходит за пределы диапазона» и код, затем указывает на индекс, выходящий за границы массива, и предлагает решение, например, корректировку размера ReDim или счетчика цикла. Разработчик применяет его и повторно тестирует.

Подведем итог этой публикации следующим образом: