Массивы в Excel VBA
В данной статье подробно рассматриваются массивы на языке программирования Excel VBA. Рассматриваются различные вопросы и схемы применения, такие как:
Типы массивов VBA
В VBA есть два типа массивов:
Статический – массив фиксированного размера.
Динамический – массив, в котором размер задается во время выполнения.
Разница между этими массивами в основном в том, как они создаются. Доступ к значениям в обоих типах массивов абсолютно одинаков. В следующих разделах мы рассмотрим оба типа.
Объявление массива
Статический массив объявляется следующим образом
Public Sub DecArrayStatic() ' создаем массив с элементами 0,1,2,3 Dim arrMarks1(0 To 3) As Long ' массив с элементами 0,1,2,3 Dim arrMarks2(3) As Long ' массив с элементами 1,2,3,4,5 Dim arrMarks3(1 To 5) As Long ' массив с элементами 2,3,4 ' редко используется Dim arrMarks4(2 To 4) As Long End Sub
Как видите, при объявлении статического массива указывается его размер. Но проблема в том, что вы никогда не можете заранее знать, какой размер вам нужен. Каждый раз при запуске макроса у вас могут быть разные требования к размеру массива
Если вы не используете все элементы массива, ресурсы памяти расходуются в пустую. Если нужно добавить больше элементов можно использовать метод ReDim, но по сути это создает новый статический массив
Динамические массивы не имеют таких проблем. Размер массивы не указывается при объявлении. Поэтому его размер может расти и уменьшаться по мере необходимости
Public Sub DecArrayDynamic() ' Объявление динамического массива Dim arrMarks() As Long ' Устанавливаем размер массива ReDim arrMarks(0 To 5) End Sub
Динамический массив не создается, пока вы не используете оператор ReDim. Преимущество в том, что вы можете подождать, пока не узнаете количество элементов, прежде чем устанавливать размер массива. Со статическим массивом вы должны указать размер заранее.
Чтение из диапазона ячеек в массив
VBA имеет очень эффективный способ чтения из диапазона ячеек в массив и наоборот
Public Sub ReadToArray() ' Объявляем динамический массив Dim StudentMarks As Variant ' Считываем данные в массив из первой строки StudentMarks = Range("A1:Z1").Value ' Записываем значения назад в третью строку Range("A3:Z3").Value = StudentMarks End Sub
Динамический массив, созданный в этом примере, будет двухмерным массивом. Как видите, мы можем прочитать весь диапазон ячеек в массив всего за одну строку.
В следующем примере будут считаны данные из диапазона C3: E6 листа Sheet1 и отображены в окне Immediate Window.
Public Sub ReadAndDisplay() ' Получаем диапазон Dim rg As Range Set rg = ThisWorkbook.Worksheets("Sheet1").Range("C3:E6") ' Создаем динамический массив Dim StudentMarks As Variant ' Записываем данные в массив StudentMarks = rg.Value ' Печатаем данные из массива Debug.Print "i", "j", "Value" Dim i As Long, j As Long For i = LBound(StudentMarks) To UBound(StudentMarks) For j = LBound(StudentMarks, 2) To UBound(StudentMarks, 2) Debug.Print i, j, StudentMarks(i, j) Next j Next i End Sub
Как сделать выполнение ваших макросов супер скоростным
Если ваши макросы работают очень медленно, этот метод может оказаться очень полезным. Особенно если вы имеете дело с большими объемами данных.
Обновление значений в массивах происходит гораздо быстрее, чем обновление значений в ячейках.
В последнем разделе вы увидели, как мы можем легко читать из группы ячеек в массив и наоборот. Если мы обновляем много значений, мы можем сделать следующее
- Копируем данные из ячеек в массив.
- Изменяем данные в массиве.
- Копируем обновленные данные из массива обратно в ячейки.
Например, следующий код будет намного быстрее, чем код ниже
Public Sub ReadToArray() ' Считываем данные в массив Dim StudentMarks As Variant StudentMarks = Range("A1:Z20000").Value Dim i As Long For i = LBound(StudentMarks) To UBound(StudentMarks) ' Update marks here StudentMarks(i, 1) = StudentMarks(i, 1) * 2 '. Next i ' Записываем новые значения на лист Range("A1:Z20000").Value = StudentMarks End Sub
Sub UsingCellsToUpdate() Dim c As Variant For Each c In Range("A1:Z20000") c.Value = ' Обновляем значения Next c End Sub
Назначение из одного множества ячеек в другой также намного быстрее, чем с помощью копирования и вставки
' Ассоциирование диапазона- это быстро Range("A1:A10").Value = Range("B1:B10").Value ' Копировать вставить - медленно Range("B1:B1").Copy Destination:=Range("A1:A10")
Пример с динамическим диапазоном
Sub ReadingRange() Dim arr As Variant arr = shData.Range("A1").CurrentRegion Dim i as long For i = LBound(arr,1) + 1 to UBound(arr,1) arr(i,5) = arr(i,5) - 100 next i shData.Range("H1").CurrentRegion.ClearContents Dim rowCount as Long, columnCount as Long rowCount - UBound(arr,1) columnCount = UBound(arr,2) shData.Range("H1".Resize(rowCount, columnCount).Value = arr End Sub
Массивы в VBA
Массивы используются в большинстве языков программирования и VBA, как язык высокого уровня, тоже не мог обойтись без них. Данный материал расскажет о создании массивов, их применении, а так же вы здесь найдете примеры использования массивов. Для начала разберем синтаксис определения массивов.
Объявление массивов
Пример 1. Создание (объявление) одномерного массива выполняется, так:
Dim Arr1(10) As Integer Dim Arr2(5 To 10) As String Dim Arr3() As Long
В данном примере объявляются: одномерный массив Arr1, содержащий ячейки с 0-й до 10-й типа Integer, массив Arr2, содержащий ячейки с 5-й до 10-й типа String и динамический массив Arr3.
Прежде чем использовать динамический массив, его необходимо «инициализировать», т.е. выделить для него необходимую память. Менять размер динамического массива можно много раз в программе, даже в цикле, если хотите. Правда учитывайте, что выделение памяти для массива — ресурсно-затратная операция, поэтому не стоит менять размер массива очень часто, это сильно замедлит программу.
Пример 2. Инициализация динамического массива и изменение его размеров
Dim Arr3() As Long ReDim Preserve Arr3(10) ReDim Preserve Arr3(20)
В данном примере мы сначала с помощью ReDim задали размер динамического массива в 11 элементов (c 0-го по 10-й), а затем снова увеличили размер до 21-го элемента. Кроме того, использовали ключевое слово Preserve — означающее, что нужно сохранить уже имеющиеся элементы с их значениями (без этого ключевого слова массив обнуляется).
Примечание: изменение размеров функцией ReDim возможно только для динамических массивов. Если размер массива был изначально задан, то его размер уже не будет меняться функцией.
Пример 3. Объявление многомерного массива
Dim Arr4(10, 10) As Integer Dim Arr5(5 To 10, 15 To 20, 30) As String
Arr4 — двумерных массив 11х11 элементов, а массив Arr5 — трехмерный.
Пример 4. Создание массива массивов
В следующем примере массив Arr2 будет содержать элементы другого массива Arr1
Dim Arr1 As Variant Dim Arr2(10) As Variant Arr1 = Array(10, 20, 30) Arr2(0) = Arr1 For i = LBound(Arr2(0)) To UBound(Arr2(0)) MsgBox Arr2(0)(i) ' Выведет последовательно 10, 20 и 30 Next i
Определение нижней и верхней границы массива
Чтобы узнать какой самый наименьший индекс у массива и какой самый максимальный индекс массива, нужно использовать функции LBound для определения нижней границы и UBound для определения верхней границы.
Пример 5. Определение границ массива
Dim Arr1(2 To 15) As Integer MsgBox LBound(Arr1) ' Выведет: 2 MsgBox UBound(Arr1) ' Выведет: 15 Dim Arr2() As Integer ReDim Arr2(8) MsgBox LBound(Arr2) ' Выведет: 0 MsgBox UBound(Arr2) ' Выведет: 8
Чтобы определить границы многомерных массивов, нужно просто использовать второй параметр функций UBound и LBound.
Dim Arr(1 To 10, 5 To 20) As Integer MsgBox LBound(Arr, 2) ' Выведет: 5 MsgBox UBound(Arr, 2) ' Выведет: 20
Задание нижней границы по-умолчанию
Иногда бывает очень не удобно, что VBA начинает нумерацию элементов массивов с нуля (0), это часто может привести к путанице и усложнению кода программы. Для решения этой проблемы есть специальный оператор Option Base , аргумент которого может быть 0 или 1. Указав значение 1, индексация массивов будет начинаться с 1, а не с 0.
Пример 6. Указание нижней границы по-умолчанию.
Option Base 1 Sub Test() Dim Arr1(10) As Integer MsgBox LBound(Arr1) End Sub
В данном примере я намеренно использовал процедуру, чтобы показать, что Option Base нужно применять не внутри процедур и функций, а в разделе «Declarations». В результате выполнения процедуры Test будет отображено сообщение с индексом нижней границы массива, т.е. «1».
Примечание: Оператор Option Base так же влияет на функцию Array и не влияет на функцию Split (будут рассмотрены ниже), что означает, что при задании «Option Base 1», функция Array вернет массив с индексацией с 1-цы, а функция Split вернет массив с индексацией с 0.
Запись данных в массивы
Для сохранения каких-либо значений в массив, можно воспользоваться несколькими способами. С помощью примеров, покажу эти способы.
Пример 7. Запись данных в массив в цикле.
Dim Arr(10) As Integer For i = 0 To 10 Arr(i) = i * 2 Next i
Пример 8. Запись заранее известных данных с помощью Array
Dim Arr() Arr = Array("красный", "зеленый", "синий") MsgBox Arr(2)
Функция Array возвращает массив из перечисленных данных типа Variant. При этом, массив, который будет содержать значения, должен быть динамическим, т.е. без указания его размеров и границ.
Пример 9. Получение массива из строки с разделителями
Dim Arr() As String Arr = Split("красный;зеленый;синий", ";") MsgBox Arr(2)
Обход элементов массива
Обычно, массивы используются для хранения большого кол-ва данных, а не 1-2 значений, поэтому чтобы получить все эелементы и использовать их для чего-то, обычно используют циклы. Наиболее удобны в этом плане циклы For и For Each .
Пример 10. Обход элементов массива циклом For.
Dim Sum As Integer Dim Arr() Arr = Array(10, 20, 30) For i = 0 To 2 Sum = Sum + Arr(i) Next i MsgBox Sum
Пример 11. Обход элементов массива циклом For Each.
Dim Sum As Integer Dim Val As Variant Dim Arr() Arr = Array(10, 20, 30) For Each Val In Arr Sum = Sum + Val Next MsgBox Sum
Иногда, бывает необходимость работы с массивом внутри других типов циклов, но получение значение элемента, всё-равно в них будет таким же, как и в цикле For, т.е. через индекс элемента.
Как узнать размерность массива vba
Здравствуйте всем.
Что-то заинтересовал меня такой вопрос:
массив Arr(4,6,3,9, .. и так n значений)
Возможно ли определить это самое n?
То, что n не более 60 я знаю.
Чисто ради любопытства.
Пользователь
Сообщений: 3672 Регистрация: 23.12.2012
11.05.2011 22:00:28
Sub test()
a = Array(4, 6, 3, 9, 8)
n = UBound(a) — LBound(a) + 1
MsgBox n
End Sub
Пользователь
Сообщений: 3103 Регистрация: 22.12.2012
Excel,MSSQL,Oracle,Qlik
11.05.2011 22:07:35
Что-то заинтересовал меня такой вопрос:
массив Arr(4,6,3,9, .. и так n значений)
Возможно ли определить это самое n?
То, что n не более 60 я знаю.
Чисто ради любопытства.
Sub test()
Dim Arr(4, 6, 3, 9)
For i = 1 To 60
on error resume next
tmp= UBound(Arr, i)
if err.number<>0 then exit for :msgbox i
Next
End Sub
Пользователь
Сообщений: 10514 Регистрация: 21.12.2012
11.05.2011 22:08:00
Нет, это мы определим «длину» каждой размерности. А я хотел узнать, сколько всего этих размерностей.
зы. неясно наверно выражаюсь, после праздников.
Arr(1 to 5, 4 to 7, 2 to 9, 5 to 7)
Как узнать, что массив четырехмерный.
Пользователь
Сообщений: 10514 Регистрация: 21.12.2012
11.05.2011 22:10:54
Пока сочинял для EducatedFool, появилось решение от R Dmitry.
Спасибо, в принципе так устроит.
Пользователь
Сообщений: 3103 Регистрация: 22.12.2012
Excel,MSSQL,Oracle,Qlik
11.05.2011 22:14:18
Sub test()
Dim Arr(4, 6, 3, 9)
For i = 1 To 60
On Error Resume Next
tmp = UBound(Arr, i)
If Err.Number > 0 Then MsgBox i — 1: Exit For:
Next
End Sub
Михаил так правильней , а то дописал код в форум не проверив 🙂
Пользователь
Сообщений: 10514 Регистрация: 21.12.2012
11.05.2011 22:16:36
Не, Дим, попробовал ради интереса — ничего не выдает. 🙁
Ну попробую на этой основе что нибудь придумать.
Пользователь
Сообщений: 10514 Регистрация: 21.12.2012
11.05.2011 22:19:22
Да, второй вариант то что надо. Спасибо!
Пользователь
Сообщений: 3103 Регистрация: 22.12.2012
Excel,MSSQL,Oracle,Qlik
11.05.2011 22:20:34
Ну попробую на этой основе что нибудь придумать.
Михаил, я исправил ошибку, у меня работает. Просто писал прямо в форум.
Пользователь
Сообщений: 10514 Регистрация: 21.12.2012
11.05.2011 22:20:59
Правда чуть изменить пришлось
Dim Arr(4, 6, 3, 9, 8), i, tmp
🙂
Пользователь
Сообщений: 11312 Регистрация: 01.01.1970
11.05.2011 23:47:01
Ну попробую на этой основе что нибудь придумать.
Ну вот, ещё одного формулиста потеряли 🙂
Планета становиться VB-образной 🙂
Пользователь
Сообщений: 3139 Регистрация: 23.12.2012
12.05.2011 00:22:48
И это логично 🙂
Зачем пытаться выжать из «Запорожца» 150 км/ч, если можно пересесть на «Мерседес».
Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
12.05.2011 10:17:29
Не обижать «Запорожец»! 🙂
Вопрос армянскому радио:
— Может ли «Запорожец» развить скорость 200 км/ч?
— Может. Если сбросить его с горы Арарат.
Пользователь
Сообщений: 10514 Регистрация: 21.12.2012
12.05.2011 12:13:53
ЗапорожецЪ — моя любимая машина. Я себе в 1985 новый сделал, 965 модели («горбатый»).
По проходимости, особенно если в нем пять здоровых мужиков едут — любой джип и рядом не стоял. 🙂
Пользователь
Сообщений: 7207 Регистрация: 21.12.2012
12.05.2011 12:19:41
Подтверждаю! 🙂
Пользователь
Сообщений: 5192 Регистрация: 16.01.2013
12.05.2011 15:42:48
вот вам мерседец 🙂
Declare Function VarPtrArray Lib «msvbvm60.dll» Alias «VarPtr» (Var() As Any) As Long
Declare Sub GetMem4 Lib «msvbvm60.dll» (ByVal SrcPointer As Long, ByVal DstPointer As Long)
Sub t()
Dim ar(1, 2, 3, 4, 5, 6, 7)
Dim p&, dm As Integer
GetMem4 VarPtrArray(ar()), VarPtr(p)
GetMem4 p, VarPtr(dm)
msgbox «вы объявили «& p «измерений»
End Sub
Живи и дай жить..
Пользователь
Сообщений: 5192 Регистрация: 16.01.2013
12.05.2011 15:47:24
а точнее( а главное безопаснее):
Option Explicit
Option Base 0
Declare Function VarPtrArray Lib «msvbvm60.dll» Alias «VarPtr» (Var() As Any) As Long
Declare Sub GetMem4 Lib «msvbvm60.dll» (ByVal SrcPointer As Long, ByVal DstPointer As Long)
Sub t()
Dim ar(1, 2, 3, 4, 5, 6, 7)
Dim p&, dm(1) As Integer
GetMem4 VarPtrArray(ar()), VarPtr(p)
GetMem4 p, VarPtr(dm(0))
MsgBox «вы объявили » & dm(0) & » измерений»
End Sub
Живи и дай жить..
09.09.2011 18:40:19
Option Explicit
Option Base 0
Declare Function VarPtrArray Lib «msvbvm60.dll» Alias «VarPtr» (Var() As Any) As Long
Declare Sub GetMem4 Lib «msvbvm60.dll» (ByVal SrcPointer As Long, ByVal DstPointer As Long)
Sub t()
Dim ar(1, 2, 3, 4, 5, 6, 7)
Dim p&, dm(1) As Integer
GetMem4 VarPtrArray(ar()), VarPtr(p)
GetMem4 p, VarPtr(dm(0))
MsgBox «вы объявили » & dm(0) & » измерений»
End Sub
А вот как определить размерность 2-мерного массива ?
DIM Mas(1 TO 7, 1 TO 10)’ ?
Как узнать размерность массива vba
Массив представляет собой набор данных одного типа. Например, объявим массив элементов типа Integer:
Dim nums(5) As Integer nums(0) = 0 nums(1) = 1 nums(2) = 2 nums(3) = 3 Console.WriteLine(nums(3))
Здесь мы объявили массив из 6 элементов типа Integer. По умолчанию всем шести элементам в массиве присваивается 0. Затем первым четырем элементам массива мы присваиваем некоторые значения. Обратите внимание, что индексация в массиве начинается с нуля. При этом мы не можем выйти за рамки установленной длины массива в 6 элементов. А следующий код вызовет исключение ArrayIndexOutOfRange, поскольку восьмого элемента в массиве не существует, в нем определено только 6 элементов:
Dim nums(5) As Integer nums(7) = 7
В вышеприведенном примере мы уже неявно инициализировали члены массива, задав для не него размер в шесть элементов. Однако можно объявить массив, а количество элементов указать потом:
Dim nums() As Integer
В таком случае нам его еще предстоит инициализировать. Мы это можем сделать так:
Dim nums() As Integer nums = New Integer(5) <> nums(0) = 0 nums(1) = 1 nums(2) = 2 nums(3) = 3
В данном примере мы c помощью ключевого слова New указываем, что хотим создать новый объект. Также указываем размер массива. А фигурные скобки служат для инициализации массива. Однако нам необязательно присваивать все значения массива после объявления. Мы можем все сделать уже при объявлении массива:
Dim nums2 As Integer() = New Integer(5)
Таким образом, все элементы массива у нас уже будут иметь значение. При этом мы можем использовать сокращенный синтаксис при инициализации массива без ключевого слова New, либо не задавать явным образом размер массива:
‘Не указываем размер массива Dim nums1 As Integer() = New Integer() ‘Сокращенный синтаксис инициализации Dim nums2 As Integer() =
В первой главе мы уже говорили, что тип переменной может выводиться компилятором автоматически. То же самое применимо и к массиву. Например:
Dim nums =
Кроме размера массив характеризуется таким понятием как размерность (dimension). В предыдущих примерах мы использовали одномерные массивы. Но массивы бывают и многомерными. Например:
'Одномерный массив Dim nums1 As Integer() = 'Двухмерный массив Dim nums2 As Integer(,) = , > Console.WriteLine(nums2(1, 1))
Здесь мы создали двухмерный массив, который можно представить в виде таблицы: