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

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

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

Содержание

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

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

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

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

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

1 2	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 переносятся в массив, обрабатываются и, после обработки, вставляются обратно в ту же или другую таблицу на том же или другом рабочем листе.

Обмен значениями между двумя диапазонами

Обмен значениями можно осуществить в VBA Excel не только между массивом и диапазоном, но и между двумя диапазонами одинаковой размерности:

1	Range("B2:D6") = Range("G7:I11").Value

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

Если диапазон ячеек, принимающий значения, по размеру меньше диапазона-источника, то он будет заполнен полностью:

1	Range("B2:D6") = Range("G5:L13").Value

Если принимающий диапазон ячеек по размеру больше передающего, то часть его будет заполнена значениями диапазона-источника, а остальные ячейки — значениями #Н/Д:

1	Range("B2:D6") = Range("G7:H9").Value

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

Эти примеры составлены так, чтобы вам не пришлось совершать лишних действий, просто скопируйте их в свой модуль любой книги 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)

Лист2.Range("A1:C5") = a

End Sub

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

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

Этот пример показывает, как в VBA Excel можно скопировать значения из одного массива в другой:

Sub Test4()

Dim arr1, arr2

arr1 = Range("G7:I11")

arr2 = arr1

Range("B2:D6") = arr2

End Sub

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

83 комментария для “VBA Excel. Диапазон ячеек и массив (обмен значениями)”

Артем 15.08.2018 в 08:35

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

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

1
2
3
4
5

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

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

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

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

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

Евгений.
Дано: массив (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 (например)?
Спасибо!

Евгений 07.12.2018 в 13:01

Василий,
для быстрого выведения информации на рабочий лист 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

'Заполняем двумерный массив информацией из Период2

For i1 = 1 To 3

For i2 = 1 To 3

a2(i1, i2) = a1(2, i1, i2)

'Вставляем информацию на активный лист

Range("A1:C3") = a2

End Sub

Катерина 16.12.2018 в 17:01

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

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

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

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

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

Татьяна 14.10.2021 в 18:33

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

Евгений 14.10.2021 в 19:26

Здравствуйте, Татьяна!
Обратиться к отдельному элементу массива, чтобы скопировать его значение в ячейку, можно по его индексам:

Sub Test4()

Dim a(1, 2) As Variant

a(0, 0) = "телепузик"

a(0, 1) = "журналист"

a(0, 2) = "ящерица"

a(1, 0) = "короед"

a(1, 1) = "утенок"

a(1, 2) = "шмель"

Range("A1") = a(0, 2) 'ящерица

Range("A2") = a(1, 1) 'утенок

End Sub

1. Татьяна 14.10.2021 в 20:07
  
  Евгений, спасибо за быстрый ответ. Но я заметила, что это будет работать только при двумерном массиве. А если массив одномерный (взят диапазон столбца), то не получается у меня
2. 1. Евгений 14.10.2021 в 20:53
    
    Если массив одномерный, заполненный циклом по одному элементу, тогда значения копируются так:
    
    1
    2
    
    Range("A1") = a(0)
    Range("A2") = a(1)
    
    Но если массив заполнен одной строкой, путем присвоения ему значений диапазона (пусть даже части одного столбца), в этом случае массив автоматически преобразуется в двумерный с индексацией измерений, начиная с единицы.

Ольга 02.02.2019 в 20:05

Евгений, спасибо большое, очень хорошее и полное объяснение! Только остался еще вопрос — чтобы рабоать с динамическим массивом после копирования из диапазона ячеек, его надо переопределить с preserve?
Евгений 03.02.2019 в 15:05

Привет, Ольга!

1) Вы можете переопределить массив с оператором Preserve, только учитывайте, что таким способом можно переопределить только последнее измерение массива. Если условно сопоставить двумерный массив с диапазоном ячеек, то расширение возможно только путем «добавления столбцов», количеству которых соответствует значение второго измерения.

2) Если вам нужно добавить «строки», вы можете присвоить массиву значения диапазона на нужное количество строк больше, чтобы использовать их для промежуточных расчетов. Например, вместо диапазона A1:C3 с нужными значениями можно присвоить массиву значения диапазона A1:C4 и элементы ИмяМассива(4, 1), ИмяМассива(4, 2), ИмяМассива(4, 3) использовать для промежуточных расчетов.

3) Для промежуточных расчетов можно объявить отдельный массив.
1. Ольга 16.12.2019 в 01:49
  
  Евгений, благодарю от души! Удовольствие Вас читать, настолько все полно и понятно описано)
2. Евгений 16.12.2019 в 02:15
  
  Спасибо, Ольга, за добрые слова и столь высокую оценку!
ASD 23.02.2019 в 00:45

а как в строку через запятую это написать

1
2
3
4
5

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

Евгений 23.02.2019 в 14:11

Привет, ASD!
В этом случае не обойтись без цикла:

Sub Test2()

Dim a As Variant, b As Variant, c As String

a = Range("A1:E1")

For Each b In a

c = c & "," & b

c = Mid(c, 2)

Range("A2") = c

MsgBox c

End Sub

Руслан 27.02.2019 в 14:27

Добрый день.
Есть массив A(1,3,5)

Как в вывести в ячейки на лист1 не только элементы этого массива, а еще и индексы.
То есть должно получиться так:
Строка с индексами: 1 2 3
Строка с элементами:1 3 5

Евгений 02.03.2019 в 12:55

Добрый день, Руслан!
Вывести индексы в ячейки можно с помощью циклов For…Next:

Sub Test()

Dim a(1, 3, 5) As Variant, i1 As Long, _

i2 As Long, i3 As Long, n As Long

For i1 = 0 To 1

For i2 = 0 To 3

For i3 = 0 To 5

n = n + 1

Cells(1, n) = i1 & " " & i2 & " " & i3

Cells(2, n) = a(i1, i2, i3)

End Sub

1 12.02.2020 в 11:26

Здравствуйте!
Пытаюсь приделать пользовательский формат данных к массиву

Option Explicit

Type пользовательский_формат_данных

Длина As Integer

Ширина As Integer

End Type

Sub Test4()

Dim i As Integer

Dim a(1 to 16) As пользовательский_формат_данных

For i = 1 To 16

a(i).Длина = Cells(i + 1, 1).Value

a(i).Ширина = Cells(i + 1, 2).Value

Next i

For i = 1 To 16

Debug.Print "Длина " & i + 1 & " " & a(i).Длина

Debug.Print "Ширина " & i + 1 & " " & a(i).Ширина

Next i

End Sub

всё работает.

Но если я использую динамический массив

1	Dim a() As пользовательский_формат_данных

то при этом происходит ошибка (не удаётся найти элемент в массиве?)
почему это происходит?
Спасибо!

1. Евгений 12.02.2020 в 20:08
  
  Любому динамическому массиву перед его заполнением по отдельным элементам необходимо задать размерность с помощью ключевого слова ReDim.
Анна 07.05.2020 в 14:04

Для заданной матрицы из 4 столбцов и 10 строк вычислить количество элементов, у которых значение больше площади заданного треугольника (а, b заданы). Расположить матрицу, начиная с ячейки C1. Диапазон элементов [-13;15]. Как решить такую задачу?

Евгений 08.05.2020 в 12:02

Привет, Анна!
Решение может быть таким:

Sub ObkhodDiapazona()

Dim a, b, s, myCell, n

'Вводим параметры треугольника

a = val(InputBox("Введите основание треугольника:"))

b = val(InputBox("Введите высоту треугольника:"))

'Вычисляем площадь треугольника

s = (a * b) / 2

'Находим элементы, у которых значение больше площади треугольника

For Each myCell In Range("C1:F10")

If myCell.Value > s Then n = n + 1

'Отображаем количество найденных элементов

MsgBox n, , "Количество найденных по условию элементов:"

End Sub

Анастасия 02.06.2020 в 21:24

Здравствуйте! Как можно перезаписать массив, без выбранного столбца или строки?
1. Анастасия 02.06.2020 в 21:27
  
  Забыла указать, что перезапись идёт в другой массив
2. Евгений 02.06.2020 в 21:46
  
  Здравствуйте, Анастасия!
  Передать значения из одного массива в другой без использования диапазона ячеек можно с помощью циклов For… Next.
Игорь 30.01.2021 в 10:41

Подскажите, пожалуйста, как можно сформированный масив сохранить в txt файл а потом выгрузить?
1. Евгений 30.01.2021 в 23:28
  
  Привет, Игорь!
  Ответ не простой, у меня целая статья получилась.
Александр 09.02.2021 в 12:45

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

Евгений 09.02.2021 в 13:17

Добрый день! Лучше использовать или один массив на всю таблицу, или по отдельному массиву на каждую строку или столбец.

Александр 09.02.2021 в 13:29

А если заранее не известно число строк, как создавать имена таких массивов с индексами, чтобы потом можно было сослаться на один из массивов и его элемент?

Евгений 09.02.2021 в 14:04

Dim stroki, stolbzy

'количество строк

stroki = Range("A1").CurrentRegion.Rows.Count

'количество столбцов

stolbzy = Range("A1").CurrentRegion.Columns.Count

Range(«A1») – верхняя левая ячейка таблицы.

1. А 09.02.2021 в 17:07
  
  Но это я должен выгружать значения в таблицу. А нет ли какой-то структуры, чтобы сразу можно было сделать массив с разным количеством элементов в каждой строке?
  В Dictionary нельзя каждому ключу задать несколько Item?
Евгений 09.02.2021 в 18:10

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

Dictionary допускает только уникальные ключи, но вы можете использовать такую индексацию: для первой строки – 1_1, 1_2 …, для второй – 2_1, 2_2 … и т.д.
1. Алёна 09.04.2023 в 14:07
  
  Здравствуйте, Евгений, у меня к вам небольшой вопрос: у меня есть двумерный массив четных и нечетных чисел, как из этого массива вычленить четные числа и нечетные числа и добавить их в пустой массив для четных чисел и нечетных чисел соответственно?
  Sub mas1() Dim a() As Integer Dim chet() As Integer Dim nechet() As Integer Dim m As Integer Dim n As Integer Dim i As Integer Dim j As Integer Dim l As Integer Dim b As Integer Dim sumchet As Integer Dim countchet As Integer Dim sumnechet As Integer Dim countnechet As Integer Worksheets("Лист1").Select Cells.Clear count = 0 m = InputBox("Введите M") n = InputBox("Введите N") ReDim a(1 To m, 1 To n) ReDim chet(1 To j, 1 To i) ReDim nechet(1 To j, 1 To i) For j = 1 To m For i = 1 To n a(j, i) = Int(Rnd() * 100) - 50 Cells(j + 5, i + 5) = a(j, i) Next i Next j If a(j, i) Mod 2 = 0 Then Cells(i, 1).Value = chet(j, i) End If If a(j, i) Mod 2 0 Then Cells(i, 4).Value = nechet(j, i) End If For l = 1 To n For b = 1 To m If a(b, l) Mod 2 = 0 Then countchet = countchet + 1 sumchet = sumchet + a(b, l) Cells(31, 1) = ("Количество четных чисел: ") & (countchet) Cells(32, 1) = ("Сумма четных чисел массива: ") & (sumchet) End If Next b Next l For l = 1 To n For b = 1 To m If a(b, l) Mod 2 0 Then countnechet = countnechet + 1 sumnechet = sumnechet + a(b, l) Cells(31, 5) = ("Количество нечетных чисел: ") & (countnechet) Cells(32, 5) = ("Сумма нечетных чисел массива: ") & (sumnechet) End If Next b Next l If sumchet > sumnechet Then Cells(36, 3).Value = "Сумма четных чисел больше." ElseIf sumchet countnechet Then Cells(34, 3).Value = "Количество четных чисел больше." ElseIf countchet < countnechet Then Cells(34, 3).Value = "Количество нечетных чисел больше." Else Cells(34, 3).Value = "Количество четных и нечетных чисел равна." End If End Sub

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