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

Содержание

Использование двумерных массивов в VBA на уроках информатики

VBA — универсальный язык программирования. С помощью его можно создавать полноценные приложения на Visual Basic, поскольку эти языки — близкие родственники. Создавать программы на нем можно очень быстро и легко, не нужно заботиться об установке и настройке среды программирования и наличии нужных библиотек на компьютере пользователя — MS Office есть практически на любом компьютере. Рассмотрим пример создания программы с использованием двумерных массивов.

Удобство среды VBA заключается в том, что она внедрена в пакет прикладных программ Microsoft Office и, соответственно, является доступной практически на любом ПК, не требует установки дополнительного программного обеспечения.

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

Массив — набор однотипных переменных, объединенных одним именем и доступных через это имя и порядковый номер переменной в наборе. Организуем в электронных таблицах Excel двумерный массив А, состоящий из 20 х 10 = 200 элементов. Для этого в Excel создадим поле, в котором определим элементы массива целыми случайными числами от 1 до 100.

Рис. 1. Поле двумерного массива в Excel

Перейдём во вкладку Разработчик → Visual Basic. Затем вкладка Insert → Module. Откроется окно для создания программного кода.

Рис. 2. Окно для создания программного кода

Sub Двумерный_Массив()

Dim A(20, 10) As Integer

For i = 1 To 20 ‘Число строк в массиве

For j = 1 To 10 ‘Число столбцов в массиве

A(i, j) = Int(Rnd * 100 + 1) ‘Задание массива целыми числами от 1 до 100

Cells(i, j) = A(i, j)

Next j

Next i

При выполнении программы на активном листе Excel образуется следующее поле:

Рис. 3. Заполнение двумерного массива в Excel

Определим переменные для нахождения максимального, минимального, среднего значения в таблице, кроме того, вычислим сумму и размах таблицы. Все перечисленные переменные целые, кроме действительного среднего значения. Присвоим им соответствующие типы данных: Dim Max, Min, Сумма, Размах As Integer, Среднее As Single.

Используя принцип математической индукции, найдём наибольшее и наименьшее значения таблицы: If A(i, j) >Max Then Max = A(i, j)

If A(i, j)

Для вычисления суммы: Сумма = Сумма + A(i, j), среднего значения: Среднее = Сумма / 200, размаха таблицы: Размах = Max — Min. Результат программы будет следующий:

Sub Двумерный_Массив()

Dim A(20, 10) As Integer

Dim Max, Min, Сумма, Размах As Integer, Среднее As Single

Max = 0 ‘Начальное значение Максимального элемента в массиве

Min = 100 ‘Начальное значение Минимального элемента в массиве

Сумма = 0

For i = 1 To 20 ‘Число строк в массиве

For j = 1 To 10 ‘Число столбцов в массиве

A(i, j) = Int(Rnd * 100 + 1) ‘Задание массива целыми числами от 1 до 100

Cells(i, j) = A(i, j)

If A(i, j) >= Max Then Max = A(i, j) ‘Вычисление Максимального элемента в массиве

If A(i, j)

Сумма = Сумма + A(i, j) ‘Вычисление Суммы

Next j

Next i

Среднее = Сумма / 200 ‘Вычисление Среднего значения

Размах = Max — Min

Range(«A22″). Value = «Max =»

Range(«A23″).Value = «Min =»

Range(«A24″).Value = «Сумма =»

Range(«A25″).Value = «Среднее =»

Range(«A26″).Value = «Размах =»

Range(«B22″).Value = Max

Range(«B23″).Value = Min

Range(«B24″).Value = Сумма

Range(«B25″).Value = Среднее

Range(«B26″).Value = Размах

End Sub

Для создания копии таблицы, сдвинем её на 11 позиций вправо.

‘Создание копии таблицы

For i = 1 To 20

For j = 1 To 10

Cells(i, j + 11) = A(i, j)

Next j

Next i

Наглядно видно, что перед нами копия таблицы.

Рис. 4. Копия таблицы

С этой таблицей мы может выполнять какие-либо действия, например: Заменим все числа кратные 2 на 2, кратные 3 на 3, кратные 5 на 5, остальные на «*». Произведём подсчёт таких чисел. Для этого добавим строки:

‘Обработка таблицы

Dim Кратные2, Кратные3, Кратные5, Звезд As Integer

Кратные2 = 0

Кратные3 = 0

Кратные5 = 0

Звезд = 0

For i = 1 To 20

For j = 1 To 10

If A(i, j) \ 2 = A(i, j) / 2 Then Cells(i, j + 22) = 2

If A(i, j) \ 3 = A(i, j) / 3 Then Cells(i, j + 22) = 3

If A(i, j) \ 5 = A(i, j) / 5 Then Cells(i, j + 22) = 5

If A(i, j) \ 2 A(i, j) / 2 And A(i, j) \ 3 A(i, j) / 3 And A(i, j) \ 5 A(i, j) / 25 Then Cells(i, j + 22) = «*»

If A(i, j) \ 2 = A(i, j) / 2 Then Кратные2 = Кратные2 + 1 ‘Подсчёт количества чисел кратных 2

If A(i, j) \ 3 = A(i, j) / 3 Then Кратные3 = Кратные3 + 1 ‘Подсчёт количества чисел кратных 3

If A(i, j) \ 5 = A(i, j) / 5 Then Кратные5 = Кратные5 + 1 ‘Подсчёт количества чисел кратных 5

If Cells(i, j + 22) = «*» Then Звезд = Звезд + 1 ‘Подсчёт количества «*»

Next j

Next i

Range(«D22″). Value = «Таблица»

Range(«O22″).Value = «Копия Таблицы»

Range(«Z22″).Value = «Обработанная таблица»

Range(«W22″).Value = «Кратные 2″ ‘Вывод результатов

Range(«W23″).Value = «Кратные 3″

Range(«W24″).Value = «Кратные 5″

Range(«W25″).Value = «Кол-во *»

Range(«X22″).Value = Кратные2

Range(«X23″).Value = Кратные3

Range(«X24″).Value = Кратные5

Range(«X25″).Value = Звезд

Рис. 5. Обработанная таблица

Используя офисное программирование можно сформировать у студентов комплекс знаний, умений и навыков, необходимых для решения профессиональных задач в области автоматизации работы с офисным пакетом Microsoft Office.

Литература:

И. Г. Фризен. Офисное программирование: Учебное пособие / М. Издательско-торговая корпорация «Дашков и К», 2013 г.
Языки управления приложениями: Учебно-методическое пособие. — М.: Издательский отдел факультета ВМиК МГУ имени М. В. Ломоносова № 05899; 2015 г.
https://studfiles.net/preview/2897110/
https://www.intuit.ru/studies/courses/23/23/info

Основные термины (генерируются автоматически): VBA, массив, двумерный массив, Задание массива, Максимальный элемент, Начальное значение, программный код, размах таблицы, Число столбцов, Число строк.

Урок 8 по VBA — Массивы

Автор Coding На чтение 6 мин Просмотров 180 Опубликовано 20.10.2013

Всем привет, в этой статье мы рассмотрим массивы VBA языка. Но сначала немного теории… Как мы уже знаем, данные удобно хранить в переменных, но это не всегда эффективно, например: есть десять значений (номера телефонов), объявлять для каждого значения переменную довольно громоздко, да и объем кода увеличивается, более эффективно объявить одну структуру и сохранить в ней все данные, к которые можно будет обратиться по порядковому номеру (индексу). Такая структура и будет называться массивом.

VBA массивы могут быть как одномерными (список), так и многомерными:

VBA одномерный массив

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

Dim MyArr(2) As Integer

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

Dim MyArr(2)

Для примера, создадим форму с темя текстовыми метками (объект Label) и одной кнопкой и попишем в редакторе кода:

Private Sub CommandButton1_Click()
Dim MyArr(2) As Integer
    MyArr(0) = 10
    MyArr(1) = 20
    MyArr(2) = 30
    Label1.Caption = "Значение: " & MyArr(0)
    Label2.Caption = "Значение: " & MyArr(1)
    Label3. Caption = "Значение: " & MyArr(2)
End Sub
 
Private Sub UserForm_Initialize()
    Label1.Caption = ""
    Label1.FontSize = 15
    Label1.ForeColor = vbBlue
 
    Label2.Caption = ""
    Label2.FontSize = 15
    Label2.ForeColor = vbRed
 
    Label3.Caption = ""
    Label3.FontSize = 15
    Label3.ForeColor = vbGreen
    CommandButton1.Caption = "Значения"
End Sub

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

Private Sub CommandButton1_Click()
Dim MyArr(2)
    MyArr(0) = "Строка"
    MyArr(1) = #12/30/1986#
    MyArr(2) = 124.55
    Label1.Caption = "Значение: " & MyArr(0)
    Label2.Caption = "Значение: " & MyArr(1)
    Label3. Caption = "Значение: " & MyArr(2)
End Sub

В данном случае мы присвоили значения разных типов (строка, дата и вещественное число), при этом, сам тип данных для массива VBA мы не указывали, иначе, произошла бы ошибка Type mismatch (несоответствие типов).

VBA многомерный и двумерный массив

Помимо одномерных, можно объявлять и многомерные массивы до 60 измерений, например:

Private Sub CommandButton1_Click()
Dim MyArr(0 To 1, 0 To 3) As Integer
    MyArr(0, 0) = 10
    MyArr(0, 1) = 11
    MyArr(0, 2) = 12
    MyArr(0, 3) = 13
 
    MyArr(1, 0) = 20
    MyArr(1, 1) = 21
    MyArr(1, 2) = 22
    MyArr(1, 3) = 23
    Label1.Caption = MyArr(0, 0) & MyArr(0, 1) & MyArr(0, 2) & MyArr(0, 3)
    Label2.Caption = MyArr(1, 0) & MyArr(1, 1) & MyArr(1, 2) & MyArr(1, 3)
End Sub

Строка Dim MyArr(0 To 1, 0 To 3) As Integer говорит, что мы объявили двумерный VBA массив размером два на четыре, при этом, мы сразу указали нумерацию, для сравнения:

Dim MyArr(2 To 3, 3 To 5) As Integer
    MyArr(2, 3) = 10
    MyArr(2, 4) = 11
    MyArr(2, 5) = 12
 
    MyArr(3, 3) = 20
    MyArr(3, 4) = 21
    MyArr(3, 5) = 22
    Label1. Caption = MyArr(2, 3) & MyArr(2, 3) & MyArr(2, 5)
    Label2.Caption = MyArr(3, 3) & MyArr(3, 4) & MyArr(3, 5)

Хотя можно прописать и так MyArr(2,3), в этом случае нумерация будет идти от нуля автоматически.

Динамические массивы VBA – довольно часто может возникнуть ситуация, когда точно количество элементов массива не известно, или же его размер надо изменить. При объявлении динамического массива его размер не указывается:

Dim MyArr ()

Для изменения размера используется ключевое слово ReDim:

ReDim MyArr(6)

ReDim автоматически удаляет все предыдущие значения, что бы они сохранились (например, предыдущий размер составлял 4 элемента и хранил данные, а теперь надо расширить размер до 6 элементов, сохранив предыдущие значения), используется ключевое слово Preserve:

ReDimPreserve MyArr(10)

Оператор Option Base 1 – данные оператор позволяет указать, что бы нумерация элементов начиналась не с нуля, а с единицы.

Некоторые функции – язык VBA предоставляет в распоряжение пользователя функции для работы с массивами, например:

Array() – данная функция VBA позволяет сразу задавать значения одномерного массива:

Dim MyArr
MyArr = Array(10, 22, 100, 5)

Видим, что сначала надо объявить переменную, а уже потом использовать функцию для формирования одномерного VBA массива.

Erase – очищает содержимое:

Erase MyArr

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

Есть и другие функции для работы с массивами VBA, плюс, можно создавать вложенные массивы, более детально можете почитать в статье VBScript массивы (просто структура языков практически одинакова).

массивов VBA | Как использовать массивы Excel VBA с примерами?

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

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

Давайте разберемся, как использовать массивы Excel VBA и их типы на нескольких примерах.

Вы можете скачать этот шаблон Excel для массивов VBA здесь — Шаблон Excel для массивов VBA

Пример №1

Взгляните на приведенный ниже пример. X — это переменная, которая содержит целочисленный тип данных.

Код:

 Sub Array_Example1()
Dim x как целое число

х = 1

End Sub

Теперь присвойте значение 1 объявленной переменной х.

Вставим значение 1 в ячейку A1.

Код:

 Sub Array_Example1()
Dim x как целое число

х = 1

Диапазон ("A1"). Значение = х
End Sub

Значение x равно 1, а в диапазоне A1 значение будет вставлено как значение x, т.е. значение x равно 1. Теперь запустите код с помощью клавиши F5 или вручную, чтобы увидеть результаты.

В приведенном выше примере x содержит только одну переменную; вот и все. Но если я хочу вставить 5 последовательных чисел с помощью одной переменной, мне нужно использовать переменную массива типов, которая может содержать множество значений переменных в одном имени переменной.

Пример #2

Теперь взгляните на приведенный ниже пример. Имя переменной — x, а тип данных — LONG. Но при объявлении самой переменной я раскрыл скобки и упомянул от 1 до 5. Это означает, что переменная x будет содержать 5 различных типов значений.

Код:

 Sub Array_Example()

Dim x (от 1 до 5) As Long, i As Integer

End Sub

После этого я присвоил значения каждой переменной. X(1) = 20 означает, что первая переменная должна быть равна 20. X(2) = 25 означает, что вторая переменная должна быть равна 25 и так далее.

Код:

 Sub Array_Example()

    Dim x(1–5) As Long, i As Integer

    х(1) = 20
    х(2) = 25
    х (3) = 44
    х(4) = 78
    х(5) = 96

End Sub

Позже я объявил еще одну переменную с именем «I», это второй тип переменной и содержит тип данных integer .

На следующем шаге я применил циклы FOR для вставки назначенных чисел в массив в первом столбце. Я установил значение переменной i к 1, и я дал указание циклу выполняться от 1 до 5 раз. Когда цикл запускается в первый раз, значение i будет равно 1. CELLS (I,1).value = x(i) это означает, что в первый раз i равно 1, т.е. CELLS(1,1) .value = x(1), в первом столбце первой строки (ячейка A1) значение будет первым значением массива ( x(1) ), т.е. 20.

Когда цикл запускается во второй раз i value становится равным 2, т. е. CELLS (2, 1).value = x (2), во второй строке первого столбца (A2) значением будет второй массив ( x(2) ) значение, т. е. 25.

Код:

 Sub Array_Example()

Dim x (от 1 до 5) As Long, i As Integer

х(1) = 20
х(2) = 25
х (3) = 44
х(4) = 78
х(5) = 96

Для i = от 1 до 5

Ячейки (я, 1). Значение = х (я)

Далее я

End Sub

Таким образом, когда циклы продолжают работать, значения будут продолжать меняться. Когда циклы запускаются в третий раз, значение ячейки A3 будет равно 44, при четвертом запуске цикла значение ячейки A4 будет 78, когда циклы запускаются в последний или пятый раз, значение ячейки A5 будет равно 96.

После запуска кода с помощью клавиши F5 или вручную мы получим результаты, как показано ниже.

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

Массивы имеют разные типы в VBA. В Excel доступно пять типов массивов.

Статический массив
Динамический массив
Одномерный массив
Двумерный массив
Многомерный массив

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

В этом типе массива длина массива заранее определена и остается постоянной.

Код:

 Sub Static_Example()

Dim ArrayType (от 1 до 3) как целое число

Тип массива (1) = 0
Тип массива (2) = 1
Тип массива (3) = 2

Ячейки (1, 1). Значение = ArrayType (1)
Ячейки (1, 2). Значение = ArrayType (2)
Ячейки (1, 3). Значение = ArrayType (3)

End Sub

В приведенном выше коде длина ArrayType определяется заранее как от 1 до 3, а тип данных — Integer.

После запуска кода с помощью клавиши F5 или вручную мы получим результаты, как показано ниже.

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

В этом типе массива длина массива заранее не определена заранее.

Код:

 Sub Dynamic_Example()

Dim ArrayType() как вариант

ReDim ArrayType(3)

ArrayType(1) = "Мое имя"
ArrayType(2) = "есть"
ArrayType(3) = "Excel"

Ячейки (1, 1). Значение = ArrayType (1)
Ячейки (1, 2). Значение = ArrayType (2)
Ячейки (1, 3). Значение = ArrayType (3)

Конец суб

В этом типе массива данные являются Variant, и длина здесь не определяется. После объявления переменной я присвоил длину массива с помощью функции ReDim . Этот массив будет вставлять такие значения, как ячейка A1 = мое имя, ячейка B1 = is Cell C1 = Excel.

Одномерный массив

В этом типе массива длина определяется, но в одном измерении это работает.

Код:

 Sub One_Dimensional()

Dim OneDimension(1–3) в виде строки

OneDimension(1) = 40
OneDimension(2) = 50
OneDimension(3) = 15

Конец суб

Показать эти значения в окне сообщений VBA.

Код:

 Sub One_Dimensional()

Dim OneDimension(1–3) в виде строки

OneDimension(1) = 40
OneDimension(2) = 50
OneDimension(3) = 15

MsgBox OneDimension(1) & "," & OneDimension(2) & "," & OneDimension(3)

End Sub

Запустив этот код с помощью клавиши F5 или вручную, мы получим следующий результат.

Двумерный массив

В этом типе массива длина определяется в двух измерениях, и это работает.

Код:

 Sub Two_Dimensional()

Dim TwoDimension(1–2, 1–2) As Long
Dim i как целое число
Dim j как целое число

Два измерения (1, 2) = 40
Два измерения (2, 1) = 50
Два измерения (1, 1) = 15
Два измерения (2, 2) = 10

End Sub

Теперь, чтобы сохранить эти значения в ячейках ниже кода.

Код:

 Sub Two_Dimensional()

Dim TwoDimension (от 1 до 2, от 1 до 2) As Long
Dim i как целое число
Dim j как целое число

Два измерения (1, 2) = 40
Два измерения (2, 1) = 50
Два измерения (1, 1) = 15
Два измерения (2, 2) = 10

Для i = от 1 до 2
    Для j = от 1 до 2
        Ячейки (i, j) = TwoDimension (i, j)
    Следующий j
Далее я

End Sub

Здесь будут храниться данные, как показано ниже.

Многомерный массив

В этом типе массива длина определяется, но в многомерном массиве это работает.

Код:

 Sub Multi_Dimensional()

Dim TwoDimension (от 1 до 3, от 1 до 2) As Long
Dim i как целое число
Dim j как целое число

Многомерность (1, 1) = 15
Многомерность (1, 2) = 40
Многомерность (2, 1) = 50
Многомерность (2, 2) = 10
Многомерность (3, 1) = 98
Многомерность (3, 2) = 54

Если вы посмотрите на приведенный выше код, во-первых, я объявил массив как от 1 до 3, а затем от 1 до 2. Это означает, что когда я пишу массив, во-первых, я могу использовать только числа от 1 до 3, но во втором пробеле я могу использовать только от 1 до 2, а не от 1 до 3.

Используя Цикл, мы можем вставлять значения в ячейки. Я использовал два цикла для многомерного массива.

Код:

 Sub Multi_Dimensional()

Dim TwoDimension (от 1 до 3, от 1 до 2) As Long
Dim i как целое число
Dim j как целое число

Многомерность (1, 1) = 15
Многомерность (1, 2) = 40
Многомерность (2, 1) = 50
Многомерность (2, 2) = 10
Многомерность (3, 1) = 98
Многомерность (3, 2) = 54

Для i = от 1 до 3
    Для j = от 1 до 2
        Ячейки (i, j) = MultiDimension (i, j)
    Следующий j
Далее я

End Sub

После запуска кода с помощью клавиши F5 или вручную мы получим результаты, как показано ниже.

Что следует помнить

Массив будет считать значения с нуля, а не с 1.
Массив (0, 0) означает первый столбец первой строки.
Этот файл макроса Excel необходимо сохранить как книгу с поддержкой макросов.
В случае динамического массива нам нужно присвоить значение массива с помощью функции REDIM в VBA.

Работа с массивами переменных (VBA)

Автор: Оскар Кронквист Последнее обновление статьи: 08 февраля 2023 г.

Зачем нужны массивы? Excel гораздо быстрее выполняет задачи с массивами, чем со значениями на листе. Массивы существуют в оперативной памяти вашего компьютера, а рабочие книги — на жестком диске.

Работа с данными из оперативной памяти вашего компьютера выполняется намного быстрее.

Что на этой странице

Как задать размер переменной массива и объявить тип данных
Как изменить размер переменной массива
n-мерные массивы
Одномерные массивы
2-мерные массивы
Трехмерные массивы
Нахождение начального и конечного порядкового номера массива
Одномерные массивы
2-мерные массивы
Трехмерные массивы
Как работать с массивом
Как очистить массив
Как скопировать значения массива на лист
Макро
Пользовательская функция
Как транспонировать массив
Получить данные из рабочего листа в переменную массива
Копирование данных из таблицы Excel
Как скопировать массив
Как скопировать столбец в новый массив
Как скопировать строку в новый массив
Как передать массив в макрос
Как передать массив в функцию
Как определить количество измерений в массиве
Как разделить текстовую строку

1.

Объявить переменную-массив

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

 Подмакрос1()
Dim pets() как строка
Конец сабвуфера

Вы также можете указать, сколько значений вы хотите сохранить в массиве. Макрос ниже объявляет переменную pets как массив строк, он может хранить 6 значений. от 0 до 5.

 Субмакрос1()
Dim pets(5) Как String
Конец сабвуфера

Если вы хотите начать с 1 вместо 0, вы тоже можете это сделать. Эта переменная может хранить 5 значений, от 1 до 5.

 Sub Macro1()
Dim pets(от 1 до 5) As String
Конец сабвуфера

В начало

2. Изменение размера переменной-массива

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

Подмакрос1()
Dim pets(1 to 2) As String

домашние животные (1) = "собака"
Msgbox Ubound (домашние животные)

ReDim питомцы (от 1 до 3)

Msgbox pets(1) & " " & Ubound(pets)

Конец макроса

Обратите внимание, что при этом удаляются все предыдущие сохраненные значения массива.

Вернуться к началу

3. n-мерные массивы

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

3.1 Одномерные массивы

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

 Подмакрос1()
Dim pets(1 to 2) As String
домашние животные (1) = "собака"
домашние животные (2) = "кошка"
Конец сабвуфера

В этом макросе можно сохранить 2 значения питомцев (от 1 до 2) . Номер индекса в переменной pets указывает Excel, где сохранить значение в массиве. pets( index )

Вы также можете брать значения с рабочего листа и сохранять их в массив.

 Подмакрос1()
Dim pets(1 to 2) As String

домашние животные (1) = Рабочие листы ("Лист1"). Диапазон ("A1"). Значение
домашние животные (2) = Рабочие листы ("Лист1"). Диапазон ("A2"). Значение
Конец сабвуфера

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

 Подмакрос1()
Dim pets(от 1 до 100) As String
Для i = от 1 до 100
домашние животные (i) = рабочие листы («Лист1»). Диапазон («A» и i). значение
Далее я
Конец сабвуфера

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

В начало

3.2 Двумерные массивы

Этот макрос показывает, как заполнить двумерный массив. Первый аргумент — это номер строки, а второй — номер столбца, pets( строка , столбец )

 Sub Macro1()
Тусклые питомцы (от 1 до 2, от 1 до 2)

домашние животные (1,1) = "собака"
домашние животные (1,2) = 5
домашние животные (2,1) = "кошка"
домашние животные (2,2) = 9
Конец сабвуфера

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

Вернуться к началу

3.3 Трехмерные массивы

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

 Подмакрос1()
Тусклые питомцы (от 1 до 2, от 1 до 2, от 1 до 2)

домашние животные (1,1,1) = "собака"
домашние животные (1,2,1) = 5
домашние животные (2,1,1) = "кошка"
домашние животные (2,2,1) = 9
домашние животные (1,1,2) = "змея"
домашние животные (1,2,2) = 3
домашние животные (2,1,2) = "рыба"
домашние животные (2,2,2) = 4
Конец сабвуфера

В начало

4. Размер массива. Определение начального и конечного порядкового номера массива

Каждый элемент или значение имеет порядковый номер, указывающий его положение в массиве. У массивов всегда есть нижний и верхний пределы, оператор Lbound возвращает нижний предел, а оператор Ubound возвращает верхний предел. Эти функции очень полезны. Если нижний предел равен 1, а верхний предел равен 5, массив содержит 5 значений.

В начало

4.1 Одномерный массив

Следующий макрос использует операторы LBound и Ubound для определения размера переменной массива.

 Подмакрос1()
Тусклые домашние животные (от 1 до 10)
Msgbox "LBound: " & LBound (домашние животные) & " Ubound: " & Ubound (домашние животные)
Конец сабвуфера

В начало

4.2 Двумерный массив

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

 Подмакрос1()
Тусклые домашние животные (от 1 до 5, от 1 до 3)
MsgBox "Нижний предел 1-dim: " & LBound(pets, 1) & " Верхний предел 1-dim:" & UBound(pets, 1) & " Нижний предел 2-dim: " & LBound(pets, 2) & " Верхний предел 2-dim: " & UBound(домашние животные, 2)
Конец сабвуфера

Вернуться к началу

4. 3 Трехмерный массив

 Sub Macro1()
Тусклые питомцы (от 1 до 5, от 1 до 3, от 1 до 2)
MsgBox "Нижний предел 1-dim: " & LBound(pets, 1) & " Верхний предел 1-dim:" & UBound(pets, 1) & " Нижний предел 2-dim: " & LBound(pets, 2) & " Верхний предел 2-мерный: " & UBound(домашние животные, 2) & " Нижний предел 3-мерный: " & LBound(домашние животные, 3) & " Верхний предел 3-мерный: " & UBound(домашние животные, 3)
Конец сабвуфера

Вернуться к началу

5. Работа с массивом

Вы можете изменять размеры массива, сохраняя при этом все значения. Но вам нужно объявить переменную массива с пустыми скобками, иначе вы получите эту ошибку компиляции: «Массив уже имеет размеры»

 Sub Macro1()
Dim pets() как строка
ReDim домашние животные (от 1 до 5)
домашние животные (2) = "собака"
ReDim Preserve pets(1 To UBound(pets) + 1)
MsgBox "Lbound(домашние животные): " & LBound(домашние животные) & " Ubound(домашние животные): " & UBound(домашние животные) & "домашние животные(2): " & домашние животные(2)
Конец сабвуфера

Вы можете изменить размер только последнего измерения, следующий массив имеет 2 измерения, границы от 1 до 5 и от 1 до 2.

 Sub Macro1()
Dim pets() как строка
ReDim домашние животные (от 1 до 5, от 1 до 2)
ReDim Preserve pets(1 to Ubound(pets,1) , 1 To UBound(pets,2) + 1)
MsgBox "Lbound(домашние животные,1): " & LBound(домашние животные,1) & " Ubound(домашние животные,1): " & UBound(домашние животные,1) & vbNewLine & "Lbound(домашние животные,2): " & LBound(домашние животные ,2) & " Ubound(домашние животные,2): " & UBound(домашние животные,2)
Конец сабвуфера

Оператор ReDim Preserve изменяет последнее измерение двумерного массива в макросе выше. Границы теперь от 1 до 5 и от 1 до 3, это изображение показывает вам это.

Excel очень медленный, когда дело доходит до манипулирования массивами, лучше попытаться свести к минимуму количество раз, когда вы используете ReDim Preserve, угадывая, сколько элементов вам нужно.

В начало

6. Очистить массив

Вы можете использовать оператор ReDim для очистки всех сохраненных значений массива.

Подмакрос1()
Dim pets(1 to 2) As String

домашние животные (1) = "собака"
Msgbox Ubound (домашние животные)

ReDim питомцы (от 1 до 3)

Msgbox pets(1) & " " & Ubound(pets)

Конец макроса

Вы также можете использовать функцию стирания

Подмакрос1()

Dim pets(1 to 2) As String

домашние животные (1) = "кошка"

Стереть домашних животных

Конец сабвуфера

В начало

7.

Перенос значений массива на рабочий лист

7.1 Макрос

Этот макрос возвращает массив на рабочий лист Sheet1

Подмакрос1()
Dim pets(1 to 2) As String

домашние животные (1) = "собака"
домашние животные (2) = "кошка"

Рабочие листы("Лист1").Range("A1:B1") = домашние животные

Конец макроса

Как видите, он возвращает эти значения по горизонтали. Также можно возвращать значения по вертикали, см. «Транспонирование» далее в этом посте.

Наверх

7.2 Пользовательская функция

Следующая пользовательская функция (udf) возвращает значения массива, но ее необходимо ввести как формулу массива.

Функция ArrTest
Dim pets(1 to 2) As String

домашние животные (1) = "собака"
домашние животные (2) = "кошка"

ArrTest = домашние животные

Конец макроса

Вот шаги по вводу формулы массива:

Выберите ячейку A1:B1
Тип =ArrTest()
Нажмите и удерживайте CTRL + SHIFT
Нажмите Enter

Если вы введете udf правильно, строка формул покажет {=ArrTest()}

Если вы не введете этот udf как формулу массива, может отображаться первое значение в массиве во всех ячейках, строка формул покажет =ArrTest()

В начало

8.

Транспонирование массива

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

Подмакрос1()
Dim pets(1 to 2) As String

домашние животные (1) = "собака"
домашние животные (2) = "кошка"

Рабочие листы("Лист1").Range("A1:A2") = Application.Transpose(домашние животные)

Конец сабвуфера

Если ваш массив больше 65536, оператор транспонирования вернет ошибку, этот макрос не будет работать.

 Подмакрос1()
Dim pets(от 1 до 65537) как строка
Дим я как сингл
Для я = 1 до 65537
домашние животные (я) = я
Далее я
Рабочие листы («Лист1»). Диапазон («A1: A65537») = Приложение. Транспонировать (домашние животные)
Конец сабвуфера

Оператор Transpose возвращает ошибку времени выполнения «13»: несоответствие типов.

Однако существует обходной путь для преодоления этого предела массива. Следующий макрос возвращает массив по вертикали в диапазон ячеек A1:A65537, а Transpose даже не используется. Недостатком является то, что вы не можете изменить размер первого измерения с помощью ReDim Preserve.

 Подмакрос1()
Dim pets(от 1 до 65537, от 1 до 1) как одиночные

Для я = 1 до 65537
домашние животные (я, 1) = я
Далее я

Рабочие листы("Лист1").Range("A1:A65537") = домашние животные

Конец сабвуфера

Имейте в виду, что если вы используете транспонирование с массивом больше 65536 в Excel 2013 или 2016, ошибка не отображается.

В начало

9. Извлечение данных из рабочего листа

Этот макрос извлекает 65537 значений из диапазона ячеек A1:A65537 и делает это очень быстро.

Подмакрос1()
Dim pets() как вариант

домашние животные = Рабочие листы ("Лист1"). Диапазон ("A1: A65537"). Значение

MsgBox "LBound(домашние животные,1):" & LBound(домашние животные, 1) & " Ubound(домашние животные,1): " & UBound(домашние животные, 1) & vbNewLine & "LBound(домашние животные,2): " & LBound(домашние животные , 2) & " UBound(домашние животные,2): " & UBound(домашние животные, 2)

Конец сабвуфера

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

В начало

9.1 Копирование данных из таблицы Excel

Вы также можете копировать значения из таблицы Excel в массив.

Подмакрос1()
Dim pets() как вариант

домашние животные = Диапазон ("Таблица1"). Значение

MsgBox "LBound(домашние животные,1):" & LBound(домашние животные, 1) & " Ubound(домашние животные,1): " & UBound(домашние животные, 1) & vbNewLine & "LBound(домашние животные,2): " & LBound(домашние животные , 2) & " UBound(домашние животные,2): " & UBound(домашние животные, 2)

Конец сабвуфера

Range(«Table1»).Value возвращает все значения, кроме заголовков, используйте Table1[#All], если вам нужны все значения. Если вы хотите скопировать только столбец «Имя» в массив, используйте Таблица1[Имя].

В начало

10. Копирование массива

Чтобы скопировать весь массив, просто используйте знак равенства.

Подмакрос1()
Тусклые питомцы (от 1 до 2) как вариант
Тусклые собаки () как вариант

домашние животные (1) = "Лабрадор-ретривер"
домашние животные (2) = "Золотистый ретривер"

собаки = домашние животные

Собаки MsgBox(1) и vbNewLine и собаки(2)
Конец сабвуфера

В начало

10. 1 Копирование столбца в новый массив

Оператор Application.Index позволяет копировать раздел массива.

собаки = Application.Index(pets, 0, 1) копирует столбец 1 из массива pets в новый массив с именемdogs.

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

Подмакрос1()
Тусклые питомцы (от 1 до 2, от 1 до 2) как вариант
Тусклые собаки () как вариант

pets(1, 1) = "Лабрадор-ретривер"
домашние животные (2, 1) = "Золотистый ретривер"
домашние животные (1, 2) = "Боксер"
домашние животные (2, 2) = "Бигль"
собаки = Application.Index (домашние животные, 0, 1)

MsgBox собаки (1, 1) и vbNewLine и собаки (2, 1)
Конец сабвуфера

В начало

10.2 Копирование строки в новый массив

При копировании строки (горизонтальные значения) из массива создается новый массив только с одним измерением.

 Подмакрос1()
Тусклые питомцы (от 1 до 2, от 1 до 2) как вариант
Тусклые собаки () как вариант

pets(1, 1) = "Лабрадор-ретривер"
домашние животные (2, 1) = "Золотистый ретривер"
домашние животные (1, 2) = "Боксер"
домашние животные (2, 2) = "Бигль"
собаки = Application.Index (домашние животные, 2, 0)

Собаки MsgBox(1) и vbNewLine и собаки(2)

Конец сабвуфера

Как вы могли догадаться, возникла проблема с функцией ИНДЕКС. Он не может обрабатывать массивы больше 65536 значений, и Microsoft, похоже, ничего не делает с этим, ошибка существует с Excel 2007.

В начало

11. Отправить массив в макрос

Macro2 создает и заполняет новый массив, а затем запускает Macro1, используя массив в качестве аргумента.

 Sub Macro1 (val () как строка)

MsgBox значение(1) vbNewLine значение(2)
Конец сабвуфера

Подмакрос2()
Тусклые домашние животные (от 1 до 2) в виде строки
домашние животные (1) = "Лабрадор-ретривер"
домашние животные (2) = "Золотистый ретривер"
Вызов макроса1(домашние животные)
Конец сабвуфера

В начало

12.

Передача массива в функцию

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

Функция Func1(val() как вариант)
Func1 = Application.Transpose(val)
Конечная функция

Функция Func2(rng As Range)
Dim temp () как вариант
Dim temp1() как вариант

темп = rng.Value
Func2 = Func1 (темп.)
Конечная функция

Вы также можете отправить массив из макроса в udf.

В начало

13. Определение количества измерений в массиве

Пользовательская функция CountDim подсчитывает количество измерений в массиве.

Функция CountDim(val() как вариант)
Дим я как сингл

При ошибке Возобновить Далее
Для я = 1 до 60000
chk = LBound(val, i)
Если Err <> 0, то выйти в течение
Далее я
При ошибке Перейти к 0
CountDim = я - 1
Конечная функция

Функциональный тест()
Dim arr (от 1 до 10, от 1 до 5, от 1 до 3, от 1 до 5, от 1 до 3)
Тест = CountDim (обр)
Конечная функция

Функциональный тест возвращает 5, в этом массиве пять измерений:

В начало

14.

Разделить текстовую строку

Функция разделения позволяет разделить текстовую строку с помощью разделителя. Он возвращает одномерный массив (от 0 до n), что делает его интересным в данном контексте.

Функция CountWords(rng As Range)
Текст = Разделить (rng, "")
Количество слов = UBound(текст) + 1
Конечная функция

Наверх

Категория массивов

Руководство для начинающих по формулам массива Excel
Формулы массива позволяют выполнять расширенные вычисления, которые невозможно выполнить с помощью обычных формул.

Как вводить формулы массива в объединенные ячейки
Вы когда-нибудь пытались ввести формулу массива в объединенные ячейки? Тогда вы знакомы с этим сообщением об ошибке: […]

Более 1300 формул Excel

Категории Excel

Домашняя страница

Последние обновленные статьи.

Функции Excel

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

Формулы Excel

Более 1300 формул, организованных в подкатегории.

Таблицы Excel

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

Расширенный фильтр

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

Проверка данных

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

Раскрывающийся список

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

Именованные диапазоны

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

Excel Solver

Excel Solver — это бесплатная надстройка, которая использует целевые ячейки, ограничения, основанные на формулах на листе, для выполнения анализа «что, если» и других проблем принятия решений, таких как перестановки и комбинации.

Диаграммы

Функция Excel, позволяющая визуализировать данные в виде графика.

Условное форматирование

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

Сводные таблицы

Позволяет быстро суммировать большие объемы данных в очень удобной для пользователя форме. Эта мощная функция Excel позволяет эффективно анализировать, упорядочивать и классифицировать важные данные.

imacrosoft.ru

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

Использование двумерных массивов в VBA на уроках информатики

Урок 8 по VBA — Массивы

VBA одномерный массив

VBA многомерный и двумерный массив

массивов VBA | Как использовать массивы Excel VBA с примерами?

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

Пример №1

Пример #2

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

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

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

Одномерный массив

Двумерный массив

Многомерный массив

Что следует помнить

Рекомендуемые статьи

Работа с массивами переменных (VBA)

Что на этой странице

1.

2. Изменение размера переменной-массива

3. n-мерные массивы

4. Размер массива. Определение начального и конечного порядкового номера массива

5. Работа с массивом

6. Очистить массив

7.

8.

9. Извлечение данных из рабочего листа

10. Копирование массива

11. Отправить массив в макрос

12.

13. Определение количества измерений в массиве

14.

Категория массивов

Категории Excel