Перейти к содержимому

VBA Excel. Диапазон ячеек и массив (обмен значениями)

Копирование значений из диапазона ячеек в массив и обратно с помощью VBA Excel. Простейшие примеры обмена значениями между диапазоном и массивом.

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

Как известно, VBA обрабатывает информацию в массивах значительно быстрее, чем в ячейках рабочего листа Excel. Поэтому, при работе с большими объемами данных, удобнее использовать массивы, чем наблюдать во время выполнения кода за мерцанием изображения на экране или просто смотреть в неизменную картинку, если обновление экрана отключено (Application.ScreenUpdating = False). Здесь обмен значениями между массивом и диапазоном ячеек будет вполне уместен.

Копирование значений из диапазона ячеек в массив

Чтобы скопировать значения из диапазона ячеек в массив, необходимо объявить переменную универсального типа (As Variant) и присвоить ей значения диапазона ячеек с помощью оператора присваивания (=):

Dim a As Variant
a = Range("A1:C3")

VBA Excel автоматически преобразует объявленную переменную в двумерный массив, соответствующий размерности диапазона ячеек, в нашем случае в массив — a(1 To 3, 1 To 3), и заполняет его значениями. Нумерация измерений массивов, созданных таким образом, начинается с единицы (1).

Можно, в этом случае, объявить сразу динамический массив, чтобы изначально указать, что эта переменная будет массивом. Так как свойством диапазона ячеек по-умолчанию в VBA Excel является значение (Value), его можно в коде явно не указывать, но, при желании, можно и указать. Получится такая конструкция, аналогичная первой:

Dim a() As Variant
a = Range("A1:C3").Value

Стоит отметить, что для копирования значений из диапазона ячеек в массив можно использовать только обычную переменную или динамический массив универсального типа (Variant). VBA Excel автоматически преобразовывает их в двумерный массив. Если объявить двумерный массив с указанной заранее размерностью, использовать его не получится, будет сгенерирована ошибка с сообщением: Can't assign to array (Нельзя назначать массив).

Копирование значений из массива в диапазон ячеек

Значения в диапазон ячеек добавляются из массива с помощью оператора присваивания (=):

Range("A6:F15") = a
'или
Range("A6:F15").Value = a
'где a - переменная двумерного массива

Обратите внимание, что вставить значения в диапазон ячеек можно только из двумерного массива. Размерность такого массива может начинаться с нуля (0). Количество элементов в измерениях массива должно совпадать с количеством строк и столбцов в диапазоне ячеек. Если вам нужно вставить значения в одну строку или в один столбец, укажите размерность единственной строки или единственного столбца как (0) или (1 To 1), если вы хотите использовать нумерацию измерений своего массива с единицы. Например, для записи десяти значений из массива в одну строку можно объявить такой массив — massiv(9, 0), или в один столбец — massiv(0, 9).

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

Простейшие примеры обмена значениями

Эти примеры составлены так, чтобы вам не пришлось совершать лишних действий, просто скопируйте их в свой модуль любой книги Excel с поддержкой макросов и запустите по-очереди на выполнение.

Пример 1

Заполнение двумерного массива значениями и и их присвоение диапазону ячеек на рабочем листе Excel:

Sub Test1()
Dim a(2, 2) As Variant
a(0, 0) = "телепузик"
a(0, 1) = "журналист"
a(0, 2) = "ящерица"
a(1, 0) = "короед"
a(1, 1) = "утенок"
a(1, 2) = "шмель"
a(2, 0) = 200
a(2, 1) = 300
a(2, 2) = 400
Range("A1:C3").Value = a
End Sub

В данном случае переменная массива не обязательно должна быть универсального типа (As Variant), например, если бы в нее записывались только текстовые данные, ее можно было бы объявить как строковую (As String), и все бы работало.

Пример 2

Объявление обычной переменной универсального типа, присвоение ей значений из диапазона ячеек «A1:C3», записанных кодом первого примера, и вставка этих значений из полученного двумерного массива в диапазон «D10:F12»:

Sub Test2()
Dim a As Variant
a = Range("A1:C3")
Range("D10:F12") = a
End Sub

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

Пример 3

Допустим, на рабочем листе «Лист1» в ячейках «A1:A5» записано количество какого-то товара, а в ячейках «B1:B5» — его цена. Необходимо к этой информации добавить сумму каждого товара, умножив количество на цену, и перенести данные на «Лист2».

Sub Test3()
Dim a As Variant, i As Long
  a = Лист1.Range("A1:C5")
    For i = 1 To 5
      a(i, 3) = a(i, 1) _
      * a(i, 2)
    Next
  Лист2.Range("A1:C5") = a
End Sub

Массив создан сразу с размерностью 5x3 с элементами под суммы. Даже если на первом листе в ячейках «C1:C5» есть какие-то значения, в массиве они будут перезаписаны результатами вычислений.

VBA Excel. Диапазон ячеек и массив (обмен значениями): 9 комментариев

  1. Артем

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

  2. Евгений (автор статьи)

    Артем, двумерные массивы в VBA Excel используются без ограничений, как и массивы с другим количеством измерений. Тесты показали, что значения ячеек невозможно присвоить заранее объявленному двумерному массиву (естественно с размерностью, соответствующей диапазону ячеек):

    Sub Primer()
    Dim a(1 To 3, 1 To 3) As Variant
    a = Range(Cells(1, 1), Cells(3, 3))
    MsgBox a(1, 1)
    End Sub
    

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

  3. Артем

    Честно говоря выше так и не понял фразу даже после корректировки, а вот в комментарии вы все описали ясно и понятно. Спасибо!

  4. Нурислам

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

  5. Евгений (автор статьи)

    Нурислам, в качестве ответа я добавил в статью «Пример 3».

  6. Василий

    Евгений.
    Дано: массив (1 to 5, 1 to 10, 1 to 3)
    по порядку:
    1-5 периоды (пусть 1, 2, 3, 4, 5)
    1-10 - Наименование товара (10 вариантов)
    1-3 - цена в разных магазинах (цена1, цена2, цена3).
    Пусть массив заполнен полностью.
    Вопрос:
    Как быстро вывести на экран (лист) данные массива относящиеся к периоду 2 (например)?
    Спасибо!

  7. Евгений (автор статьи)

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

    Sub Primer()
    Dim a1(1 To 3, 1 To 3, 1 To 3) As Variant, a2(1 To 3, 1 To 3) As Variant, _
    i1 As Integer, i2 As Integer, i3 As Integer
    'Заполняем трехмерный массив контрольной информацией
    For i1 = 1 To 3
      For i2 = 1 To 3
        For i3 = 1 To 3
          a1(i1, i2, i3) = "Период" & i1 & " Товар" & i2 & " Цена" & i3
        Next
      Next
    Next
    'Заполняем двумерный массив информацией из Период2
    For i1 = 1 To 3
      For i2 = 1 To 3
        a2(i1, i2) = a1(2, i1, i2)
      Next
    Next
    'Вставляем информацию на активный лист
    Range("A1:C3") = a2
    End Sub
    
  8. Катерина

    Добрый день, Евгений!

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

  9. Евгений (автор статьи)

    Здравствуйте, Катерина!

    Если динамическая таблица расположена в левом верхнем углу рабочего листа, используйте при вставке данных обычную адресацию рабочего листа вида Range(Cells(1, 1), Cells(2, 2)).

    Если динамическая таблица расположена не в левом верхнем углу рабочего листа, использование адресации умной таблицы вида Range("Таблица1").Range(Cells(1, 1), Cells(2, 2)) будет оправдано тем, что не придется изменять код при удалении строк выше таблицы и столбцов левее.

Добавить комментарий

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