Vba массив задать: Все про массивы в VBA читать в пошаговом руководстве по Excel

Примеры использования массивов в 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, т. е. через индекс элемента.

Массивы в Excel VBA — Блог: Управление проектами

28.11.201905.06.2020 planacademy

MS Excel,VBA

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

• Зачем нужны массивы
• Когда нужно применять массивы
• Типы массивов
• Использования многомерных массивов
• Объявление массивов
• Добавление значений
• Просмотр всех элементов
• Эффективный способ чтения диапазонов (Range) в массив

Задача	Статический массив	Динамический массив
Объявление	Dim arr(0 To 5) As Long	Dim arr() As Long Dim arr As Variant
Задать размер	см. выше	ReDim arr(0 To 5)As Variant
Получить размер(количество элементов)	см. функцию ArraySize .	см. функцию ArraySize
Увеличить размер (с сохранением данных)	Только динамический массив	ReDim Preserve arr(0 To 6)
Задать значение	arr(1) = 22	arr(1) = 22
Получить значение	total = arr(1)	total = arr(1)
Первый элемент	LBound(arr)	LBound(arr)
Последний элемент	Ubound(arr)	Ubound(arr)
Прочитать все элементы(1D)	For i = LBound(arr) To UBound(arr) Next i Or For i = LBound(arr,1) To UBound(arr,1) Next i	For i = LBound(arr) To UBound(arr) Next i Or For i = LBound(arr,1) To UBound(arr,1) Next i
Прочитать все элементы(2D)	For i = LBound(arr,1) To UBound(arr,1)   For j = LBound(arr,2) To UBound(arr,2)   Next j Next i	For i = LBound(arr,1) To UBound(arr,1)   For j = LBound(arr,2) To UBound(arr,2)   Next j Next i
Прочитать все элементы	Dim item As Variant For Each item In arr Next item	Dim item As Variant For Each item In arr Next item
Передать в процедуру	Sub MySub(ByRef arr() As String)	Sub MySub(ByRef arr() As String)
Вернуть из функции	Function GetArray() As Long()     Dim arr(0 To 5) As Long     GetArray = arr End Function	Function GetArray() As Long()     Dim arr() As Long     GetArray = arr End Function
Получить из функции	Только динамические массивы	Dim arr() As Long Arr = GetArray()
Стереть массив	Erase arr *сбрасывает все значения по умолчанию	Erase arr *удаляет массив
Строку в массив	Только динамические массивы	Dim arr As Variant arr = Split(“James:Earl:Jones”,”:”)
Массив в строку	Dim sName As String sName = Join(arr, “:”)	Dim sName As String sName = Join(arr, “:”)
Заполнить значениями	Только динамические массивы	Dim arr As Variant arr = Array(“Значение1”, “Значение2”, “Значение3”)
Диапазон в массив	Только динамические массивы	Dim arr As Variant arr = Range(“A1:D2”)
Массив в диапазон	также как в динамическом массиве	Dim arr As Variant Range(“A5:D6”) = arr

Типы массивов 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

Как сделать выполнение ваших макросов супер скоростным

Если ваши макросы работают очень медленно, этот метод может оказаться очень полезным. Особенно если вы имеете дело с большими объемами данных.

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

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

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

    Например, следующий код будет намного быстрее, чем код ниже

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("h2").CurrentRegion.ClearContents
    Dim rowCount as Long, columnCount as Long
    rowCount  - UBound(arr,1)
    columnCount = UBound(arr,2)
    
    shData.Range("h2".Resize(rowCount, columnCount).Value = arr
End Sub

VBA массивы

Использование массивов (VBA) | Microsoft Узнайте

Редактировать

Твиттер

LinkedIn

Фейсбук

Электронная почта

• Статья
• 22. 01.2022
• 2 минуты на чтение

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

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

 Dim curExpense(364) As Currency
 

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

 Sub FillArray ()
 Dim curExpense(364) в качестве валюты
 Dim intI как целое число
 Для intI = от 0 до 364
 curExpense(intI) = 20
 Следующий
Конец сабвуфера
 

Изменение нижней границы

Используйте оператор Option Base в верхней части модуля, чтобы изменить индекс по умолчанию первого элемента с 0 на 1. В следующем примере оператор Option Base изменяет индекс для первый элемент, а оператор Dim объявляет переменную массива с 365 элементами.

 Опция База 1
Dim curExpense(365) в качестве валюты
 

Можно также явно задать нижнюю границу массива с помощью предложения To , как показано в следующем примере.

 Dim curExpense(от 1 до 365) как валюта
Dim strWeekday (от 7 до 13) как строка
 

Сохранение значений Variant в массивах

Существует два способа создания массивов значений Variant . Один из способов — объявить массив данных типа Variant, как показано в следующем примере:

 Dim varData(3) как вариант
varData(0) = "Клаудия Бендель"
varData(1) = "Кленовый бульвар, 4242"
varData(2) = 38
varData(3) = Формат("09-06-1952", "Общая дата")
 

Другой способ — присвоить массив, возвращаемый функцией Array , переменной Variant , как показано в следующем примере.

 Dim varData как вариант
varData = Array("Рон Бендель", "Кленовый бульвар, 4242", 38, _
Формат("09-06-1952", "Общая дата"))
 

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

 MsgBox "Данные для " & varData(0) & " были записаны."
 

Использование многомерных массивов

В Visual Basic можно объявлять массивы, содержащие до 60 измерений. Например, следующий оператор объявляет двумерный массив 5 на 10.

 Dim sngMulti (от 1 до 5, от 1 до 10) как одиночный
 

Если вы думаете о массиве как о матрице, первый аргумент представляет строки, а второй аргумент представляет столбцы.

Используйте вложенные операторы For…Next для обработки многомерных массивов. Следующая процедура заполняет двумерный массив значениями Single .

 Sub FillArrayMulti()
 Dim intI как целое число, intJ как целое число
 Dim sngMulti (от 1 до 5, от 1 до 10) как одиночный
 
 ' Заполнить массив значениями.
 Для intI = от 1 до 5
 Для intJ = от 1 до 10
 sngMulti(intI, intJ) = intI * intJ
 Debug.Print sngMulti(intI, intJ)
 Следующий интервал
 Следующий между
Конец сабвуфера
 

См. также

• Концептуальные темы Visual Basic

Поддержка и отзывы

У вас есть вопросы или отзывы об Office VBA или этой документации? См. раздел Поддержка и отзывы Office VBA, чтобы узнать, как вы можете получить поддержку и оставить отзыв.

Excel VBA — Создание массива — 3 способа

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

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

Первый способ создания массива самый простой, мы просто используем функцию Array() в VBA:

Подпрограмма CreateArrayTest()

'первый способ добавить массив

myarray = Массив ("красный", "зеленый", "синий")

мой второй массив = Массив (10, 20, 30)

'вывести значения из массивов

MsgBox myarray(0) & " " & mysecondarray(2)

Концевой переходник

Это самый простой способ создать переменную массива в VBA и макросах для Excel.

Переменные массива: myarray и мой второй массив . Эти массивы являются просто переменными, которые используют функцию Array() для преобразования в массивы. Обратите внимание, что переменные даже не объявлены в начале макроса (хотя это хорошая практика, здесь это не требуется).

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

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

Вы также заметите строку вывода:

MsgBox myarray(0) & " " & mysecondarray(2)

Это показывает вам две важные вещи.

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

Второе, на что следует обратить внимание, это то, что по умолчанию индексы начинаются с 0 (ноль), а НЕ с цифры 1.

Таким образом, вывод функции MsgBox выше будет red 30 .

 Второй способ создания массива — явно объявить нужные значения индекса:

.

Подпрограмма CreateArrayTest2()

'второй способ добавления массива

'используя приведенный ниже метод, мы должны объявить, что эти переменные будут массивами

Dim myarray(2) Как вариант

Dim mysecondarray(2) Как вариант

мой массив (0) = "красный"

myarray(1) = "зеленый"

myarray(2) = "синий"

мой второй массив (0) = 10

мой вторичный массив (1) = 20

мой второй массив (2) = 30

'вывести значения из массивов

MsgBox myarray(2) & " " & mysecondarray(0)

Концевой переходник

Вы заметите два больших изменения в этом коде. Во-первых, мы должны объявить переменные как массивы:

Dim myarray(2) Как вариант

Dim mysecondarray(2) Как вариант

Это то же самое, что и объявление обычного типа переменной в макросе, за исключением того, что мы должны сообщить Excel, что это переменная массива. Это делается, но включает (2) после каждой переменной.

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

ПОМНИТЕ: индексы или слоты переменных массива начинаются с 0 (нуля), а НЕ с 1.

Вместо двойки можно поставить любую цифру; вы можете поставить 50, если хотите сохранить 51 значение, или 1, если хотите сохранить 2 значения. Это немного сбивает с толку, и я покажу вам, как решить эту проблему в следующем примере.

Но теперь, когда вы знаете, как создать переменную массива, давайте поместим в нее данные.

Это приводит нас к этим строкам макроса:

мой массив (0) = "красный"

myarray(1) = "зеленый"

myarray(2) = "синий"

мой второй массив (0) = 10

мой вторичный массив (1) = 20

мой второй массив (2) = 30

На этот раз мы не используем функцию Array(). Вместо этого мы отдельно размещаем каждый элемент внутри массива, используя желаемый номер индекса.

Мы хотим, чтобы текст зеленого цвета сохранялся в myarray по индексу 1 , поэтому мы используем эту строку:

myarray(1) = "зеленый"

После этого мы выводим данные в окно сообщения, как в предыдущем примере макроса.

Третий способ создать массив в VBA — объявить начальный и конечный индексы для каждого массива:

Подпрограмма CreateArrayTest3()

'третий способ добавления массива

'заставить счетчик массива начинаться с 1 вместо нуля

Dim myarray (от 1 до 3) Как вариант

'второй способ добавления массива

мой массив (1) = "красный"

myarray(2) = "зеленый"

myarray(3) = "синий"

'вывести значение из массива

MsgBox myarray(3)

Концевой переходник

Обратите внимание, объявление переменной массива здесь выглядит иначе, чем в предыдущем примере.