Размер массива excel vba: VBA Excel. Массивы (одномерные, многомерные, динамические)
Excel. Определить размер массива после функции Split в VBA « Марк и Марта.Ру. Записки отца-программиста
Excel. Определить размер массива после функции Split в VBA
Excel и VBA не являются моими коньками, хотя и мой уровень владения ими не ниже среднего. Но прибегаю к ним достаточно редко. Потому и наступаю на грабли. Недавно столкнулся с тем, что в VBA не смог определить размер массива строк после преобразования строки в массив строк функцией Split.
Перевести длинную строчку в массив можно функцией Split.
Dim txt As String txt = "Перевести длинную строчку в массив можно функцией Split" Dim textArray() As String textArray = Split(txt)
Согласно документации, размер массива можно определить функцией Length. Но в случае с массивом, полученным с помощью функции Split, этого сделать нельзя. На строке «textArray.Length» будет сообщение об ошибке «Compile error: Invalid qualifier».
Compile error: Invalid qualifier
Найти длину массива можно через поиск наименьшего и наибольшего доступных индексов для указанного измерения массива: LBound и UBound.
Dim arrayStart As Integer Dim arrayEnd As Integer arrayStart = LBound(textArray) arrayEnd = UBound(textArray) MsgBox (arrayEnd - arrayStart)
Отмечу, что функция Split возвращает массив с нулевым основанием (т.е. первый элемент имеет индекс 0), потому в этом случае можно ограничиться определением только UBound(textArray).
Массивы в Visual Basic for Application
Массивы в Visual Basic for Application – это структуры, которые обычно хранят наборы взаимосвязанных переменных одного типа. Доступ к записям массива осуществляется по их числовому индексу.
Например, есть команда из 20 человек, имена которых нужно сохранить для дальнейшего использования в коде VBA. Можно было бы просто объявить 20 переменных для хранения каждого имени, вот так:
Dim Team_Member1 As String Dim Team_Member2 As String ... Dim Team_Member20 As String
Но можно использовать гораздо более простой и организованный способ – сохранить список имён членов команды в массиве из 20 переменных типа String:
Dim Team_Members(1 To 20) As String
В строке, показанной выше, мы объявили массив. Теперь запишем значение в каждый из его элементов, вот таким образом:
Team_Members(1) = "John Smith"
Дополнительное преимущество хранения данных в массиве, по сравнению с использованием отдельных переменных, становится очевидным, когда возникает необходимость выполнить одно и то же действие над каждым элементом массива. Если бы имена членов команды были сохранены в 20 отдельных переменных, то потребовалось бы каждый раз записывать 20 строк кода, чтобы выполнить одинаковое действие с каждым из них. Однако, если имена сохранены в массиве, то выполнить нужное действие с каждым из них можно при помощи простого цикла.
Как это работает продемонстрировано ниже на примере кода, который выводит имена каждого члена команды последовательно в ячейки столбца A активного рабочего листа Excel.
For i = 1 To 20 Cells(i,1).Value = Team_Members(i) Next i
Очевидно, что работа с массивом, в котором сохранено 20 имён, значительно менее громоздка и более аккуратна, чем использование 20 отдельных переменных. А что, если этих имён не 20, а 1000? А если, вдобавок, требуется сохранить отдельно фамилии и отчества?! Ясно, что вскоре станет совершенно невозможно справиться с таким объёмом данных в коде VBA без помощи массива.
Многомерные массивы в Excel Visual Basic
Массивы Visual Basic, о которых шла речь выше, считаются одномерными. Это означает, что они хранят простой список имён. Однако, массивы могут иметь множество измерений. Например, двумерный массив можно сравнить с сеткой значений.
Предположим, что нужно сохранить ежедневные показатели продаж за январь для 5 разных команд. Для этого потребуется двумерный массив, состоящий из 5 наборов показателей для 31 дня. Объявим массив таким образом:
Dim Jan_Sales_Figures(1 To 31, 1 To 5) As Currency
Для того, чтобы получить доступ к элементам массива Jan_Sales_Figures, нужно использовать два индекса, указывающих день месяца и номер команды. Например, адрес элемента, содержащего показатели продаж для 2-ой команды за 15-ое января будет записан так:
Таким же образом можно объявить массив с 3-мя и более измерениями – достаточно добавить дополнительные измерения в объявление массива и использовать дополнительные индексы для ссылки на элементы этого массива.
Объявление массивов в Excel Visual Basic
Ранее в этой статье мы уже рассмотрели несколько примеров объявления массивов в VBA, но эта тема заслуживает более подробного рассмотрения. Как было показано, одномерный массив можно объявить вот так:
Dim Team_Members(1 To 20) As String
Такое объявление сообщает компилятору VBA, что массив Team_Members состоит из 20 переменных, к которым можно обратиться по индексам от 1 до 20. Однако, нам может прийти в голову пронумеровать наши переменные массива числами от 0 до 19, и в таком случае массив должен быть объявлен вот так:
Dim Team_Members(0 To 19) As String
На самом деле, по умолчанию нумерация элементов массива начинается с 0, и в объявлении массива начальный индекс может быть не указан вовсе, вот так:
Dim Team_Members(19) As String
Такую запись компилятор VBA расценит, как объявление массива из 20 элементов с индексами от 0 до 19.
Эти же правила действуют при объявлении многомерных массивов Visual Basic. Как уже было показано в одном из примеров, при объявлении двумерного массива индексы его измерений разделяются запятой:
Dim Jan_Sales_Figures(1 To 31, 1 To 5) As Currency
Однако, если не указывать начальный индекс для обоих измерений массива и объявить его вот так:
Dim Jan_Sales_Figures(31, 5) As Currency
то эта запись будет расценена, как двумерный массив, первое измерение которого содержит 32 элемента с индексами от 0 до 31, а второе измерение массива содержит 6 элементов с индексами от 0 до 5.
Динамические массивы
Все массивы в рассмотренных выше примерах имеют фиксированное количество измерений. Однако, во многих случаях мы не знаем заранее, какого размера должен быть наш массив. Можно выйти из ситуации, объявив огромный массив, размер которого будет наверняка больше, чем нужно для нашей задачи. Но такое решение потребует много лишней памяти и может замедлить работу программы. Есть решение лучше. Мы можем использовать динамический массив – это такой массив, размер которого может быть установлен и изменён сколько угодно раз в процессе выполнения макроса.
Динамический массив объявляется с пустыми скобками, вот так:
Dim Team_Members() As String
Далее нужно будет объявить измерение массива в процессе выполнения кода при помощи выражения ReDim:
ReDim Team_Members(1 To 20)
И если в процессе выполнения кода потребуется снова изменить размер массива, то можно использовать выражение ReDim снова:
If Team_Size > 20 Then ReDim Team_Members(1 To Team_Size) End If
Нужно иметь в виду, что изменение размера динамического массива таким способом приведёт к потере всех значений, хранившихся в массиве. Для того, чтобы сохранить данные, уже находящиеся в массиве, нужно использовать ключевое слово Preserve, как показано ниже:
If Team_Size > 20 Then ReDim Preserve Team_Members(1 To Team_Size) End If
К сожалению, ключевое слово Preserve можно использовать только, чтобы изменить верхнюю границу измерения массива. Нижнюю границу массива таким образом изменить нельзя. Кроме того, если массив имеет несколько измерений, то, используя ключевое слово Preserve, можно изменить размер только последнего измерения массива.
Оцените качество статьи. Нам важно ваше мнение:
VBA — Динамические массивы (изменение размера массива и динамическая обработка)
пример
Динамические массивы
Добавление и уменьшение переменных в массиве динамически является огромным преимуществом, когда информация, которую вы обрабатываете, не имеет определенного количества переменных.
Добавление значений динамически
Вы можете просто изменить размер массива с помощью ReDim
, это изменит размер массива, но если вы сохраните информацию, уже сохраненную в массиве, вам понадобится часть Preserve
.
В приведенном ниже примере мы создаем массив и увеличиваем его на еще одну переменную в каждой итерации, сохраняя значения уже в массиве.
Dim Dynamic_array As Variant
' first we set Dynamic_array as variant
For n = 1 To 100
If IsEmpty(Dynamic_array) Then
'isempty() will check if we need to add the first value to the array or subsequent ones
ReDim Dynamic_array(0)
'ReDim Dynamic_array(0) will resize the array to one variable only
Dynamic_array(0) = n
Else
ReDim Preserve Dynamic_array(0 To UBound(Dynamic_array) + 1)
'in the line above we resize the array from variable 0 to the UBound() = last variable, plus one effectivelly increeasing the size of the array by one
Dynamic_array(UBound(Dynamic_array)) = n
'attribute a value to the last variable of Dynamic_array
End If
Next
Удаление значений динамически
Мы можем использовать ту же логику для уменьшения массива. В этом примере значение «последний» будет удалено из массива.
Dim Dynamic_array As Variant
Dynamic_array = Array("first", "middle", "last")
ReDim Preserve Dynamic_array(0 To UBound(Dynamic_array) - 1)
' Resize Preserve while dropping the last value
Сброс массива и повторное использование динамически
Мы также можем повторно использовать массивы, которые мы создаем, чтобы не иметь много памяти, что замедлит работу. Это полезно для массивов разных размеров. Один фрагмент кода можно использовать повторно использовать массив в ReDim
массив обратно (0)
, приписывать одной переменной в массив и снова свободно увеличивать массив.
В нижеприведенном фрагменте я создаю массив со значениями от 1 до 40, пуст массив и пополняем массив значениями от 40 до 100, все это выполняется динамически.
Dim Dynamic_array As Variant
For n = 1 To 100
If IsEmpty(Dynamic_array) Then
ReDim Dynamic_array(0)
Dynamic_array(0) = n
ElseIf Dynamic_array(0) = "" Then
'if first variant is empty ( = "") then give it the value of n
Dynamic_array(0) = n
Else
ReDim Preserve Dynamic_array(0 To UBound(Dynamic_array) + 1)
Dynamic_array(UBound(Dynamic_array)) = n
End If
If n = 40 Then
ReDim Dynamic_array(0)
'Resizing the array back to one variable without Preserving,
'leaving the first value of the array empty
End If
Next
VBA — Многомерные массивы | vba Tutorial
пример
Многомерные массивы
Как видно из названия, многомерные массивы представляют собой массивы, которые содержат более одного измерения, обычно два или три, но могут иметь до 32 измерений.
Массив массива работает как матрица с различными уровнями, например, сравнение между одним, двумя и тремя измерениями.
Одно измерение — ваш типичный массив, он выглядит как список элементов.
Dim 1D(3) as Variant
*1D - Visually*
(0)
(1)
(2)
Два измерения будут выглядеть как сетка Sudoku или лист Excel, при инициализации массива вы определяете, сколько строк и столбцов будет иметь массив.
Dim 2D(3,3) as Variant
'this would result in a 3x3 grid
*2D - Visually*
(0,0) (0,1) (0,2)
(1,0) (1,1) (1,2)
(2,0) (2,1) (2,2)
Три измерения начнут выглядеть как кубик Рубика, при инициализации массива вы будете определять строки и столбцы, а также уровни / глубины, которые будет иметь массив.
Dim 3D(3,3,2) as Variant
'this would result in a 3x3x3 grid
*3D - Visually*
1st layer 2nd layer 3rd layer
front middle back
(0,0,0) (0,0,1) (0,0,2) ¦ (1,0,0) (1,0,1) (1,0,2) ¦ (2,0,0) (2,0,1) (2,0,2)
(0,1,0) (0,1,1) (0,1,2) ¦ (1,1,0) (1,1,1) (1,1,2) ¦ (2,1,0) (2,1,1) (2,1,2)
(0,2,0) (0,2,1) (0,2,2) ¦ (1,2,0) (1,2,1) (1,2,2) ¦ (2,2,0) (2,2,1) (2,2,2)
Дальнейшие измерения можно рассматривать как умножение 3D, поэтому 4D (1,3,3,3) будет представлять собой два боковых боковых 3D-массива.
Двухмерный массив
Создание
Пример ниже будет компиляцией списка сотрудников, каждый сотрудник будет иметь набор информации в списке (имя, фамилия, адрес, электронная почта, телефон …), пример, по существу, будет храниться в массиве ( сотрудник, информация), являющееся (0,0), является первым именем первого сотрудника.
Dim Bosses As Variant
'set bosses as Variant, so we can input any data type we want
Bosses = [{"Jonh","Snow","President";"Ygritte","Wild","Vice-President"}]
'initialise a 2D array directly by filling it with information, the redult wil be a array(1,2) size 2x3 = 6 elements
Dim Employees As Variant
'initialize your Employees array as variant
'initialize and ReDim the Employee array so it is a dynamic array instead of a static one, hence treated differently by the VBA Compiler
ReDim Employees(100, 5)
'declaring an 2D array that can store 100 employees with 6 elements of information each, but starts empty
'the array size is 101 x 6 and contains 606 elements
For employee = 0 To UBound(Employees, 1)
'for each employee/row in the array, UBound for 2D arrays, which will get the last element on the array
'needs two parameters 1st the array you which to check and 2nd the dimension, in this case 1 = employee and 2 = information
For information_e = 0 To UBound(Employees, 2)
'for each information element/column in the array
Employees(employee, information_e) = InformationNeeded ' InformationNeeded would be the data to fill the array
'iterating the full array will allow for direct attribution of information into the element coordinates
Next
Next
Изменение размера
Изменение размера или ReDim Preserve
Multi-Array, как и норма для массива One-Dimension, приведет к ошибке, вместо этого информация должна быть перенесена в массив Temporary с тем же размером, что и оригинал, плюс число добавляемых строк / столбцов. В приведенном ниже примере мы увидим, как инициализировать Temp Array, передать информацию из исходного массива, заполнить оставшиеся пустые элементы и заменить массив temp на исходный массив.
Dim TempEmp As Variant
'initialise your temp array as variant
ReDim TempEmp(UBound(Employees, 1) + 1, UBound(Employees, 2))
'ReDim/Resize Temp array as a 2D array with size UBound(Employees)+1 = (last element in Employees 1st dimension) + 1,
'the 2nd dimension remains the same as the original array. we effectively add 1 row in the Employee array
'transfer
For emp = LBound(Employees, 1) To UBound(Employees, 1)
For info = LBound(Employees, 2) To UBound(Employees, 2)
'to transfer Employees into TempEmp we iterate both arrays and fill TempEmp with the corresponding element value in Employees
TempEmp(emp, info) = Employees(emp, info)
Next
Next
'fill remaining
'after the transfers the Temp array still has unused elements at the end, being that it was increased
'to fill the remaining elements iterate from the last "row" with values to the last row in the array
'in this case the last row in Temp will be the size of the Employees array rows + 1, as the last row of Employees array is already filled in the TempArray
For emp = UBound(Employees, 1) + 1 To UBound(TempEmp, 1)
For info = LBound(TempEmp, 2) To UBound(TempEmp, 2)
TempEmp(emp, info) = InformationNeeded & "NewRow"
Next
Next
'erase Employees, attribute Temp array to Employees and erase Temp array
Erase Employees
Employees = TempEmp
Erase TempEmp
Изменение значений элементов
Изменение / изменение значений в определенном элементе может быть выполнено путем простого вызова координаты для изменения и предоставления ей нового значения: Employees(0, 0) = "NewValue"
В качестве альтернативы, итерация по координатам использует условия для соответствия значениям, соответствующим требуемым параметрам:
For emp = 0 To UBound(Employees)
If Employees(emp, 0) = "Gloria" And Employees(emp, 1) = "Stephan" Then
'if value found
Employees(emp, 1) = "Married, Last Name Change"
Exit For
'don't iterate through a full array unless necessary
End If
Next
чтение
Доступ к элементам в массиве может выполняться с помощью вложенного цикла (итерация каждого элемента), цикла и координат (итерации строк и доступа к столбцам напрямую) или прямого доступа к обеим координатам.
'nested loop, will iterate through all elements
For emp = LBound(Employees, 1) To UBound(Employees, 1)
For info = LBound(Employees, 2) To UBound(Employees, 2)
Debug.Print Employees(emp, info)
Next
Next
'loop and coordinate, iteration through all rows and in each row accessing all columns directly
For emp = LBound(Employees, 1) To UBound(Employees, 1)
Debug.Print Employees(emp, 0)
Debug.Print Employees(emp, 1)
Debug.Print Employees(emp, 2)
Debug.Print Employees(emp, 3)
Debug.Print Employees(emp, 4)
Debug.Print Employees(emp, 5)
Next
'directly accessing element with coordinates
Debug.Print Employees(5, 5)
Помните , всегда удобно хранить карту массива при использовании многомерных массивов, они могут легко стать путаницей.
Трехмерный массив
Для 3D-массива мы будем использовать ту же предпосылку, что и 2D-массив, с добавлением не только хранения Employee и Information, но и построения, в котором они работают.
В трехмерном массиве будут присутствовать сотрудники (могут рассматриваться как строки), информация (столбцы) и здание, которые можно рассматривать как разные листы на документе excel, они имеют одинаковый размер между ними, но каждый лист имеет различный набор информации в своих ячейках / элементах. 3D-массив будет содержать n число 2D-массивов.
Создание
3D-массив нуждается в трех координатах для инициализации Dim 3Darray(2,5,5) As Variant
первой координатой массива будет количество строений / таблиц (разные наборы строк и столбцов), вторая координата будет определять строки и третью Столбцы. В результате Dim
выше будет создан трехмерный массив с 108 элементами ( 3*6*6
), эффективно имеющий 3 разных набора 2D-массивов.
Dim ThreeDArray As Variant
'initialise your ThreeDArray array as variant
ReDim ThreeDArray(1, 50, 5)
'declaring an 3D array that can store two sets of 51 employees with 6 elements of information each, but starts empty
'the array size is 2 x 51 x 6 and contains 612 elements
For building = 0 To UBound(ThreeDArray, 1)
'for each building/set in the array
For employee = 0 To UBound(ThreeDArray, 2)
'for each employee/row in the array
For information_e = 0 To UBound(ThreeDArray, 3)
'for each information element/column in the array
ThreeDArray(building, employee, information_e) = InformationNeeded ' InformationNeeded would be the data to fill the array
'iterating the full array will allow for direct attribution of information into the element coordinates
Next
Next
Next
Изменение размера
Изменение размера 3D-массива аналогично изменению размера 2D, сначала создайте временный массив с тем же размером оригинала, добавляя его в координату параметра для увеличения, первая координата увеличит количество наборов в массиве, второе и третьи координаты увеличат количество строк или столбцов в каждом наборе.
Пример ниже увеличивает количество строк в каждом наборе на единицу и заполняет те недавно добавленные элементы новой информацией.
Dim TempEmp As Variant
'initialise your temp array as variant
ReDim TempEmp(UBound(ThreeDArray, 1), UBound(ThreeDArray, 2) + 1, UBound(ThreeDArray, 3))
'ReDim/Resize Temp array as a 3D array with size UBound(ThreeDArray)+1 = (last element in Employees 2nd dimension) + 1,
'the other dimension remains the same as the original array. we effectively add 1 row in the for each set of the 3D array
'transfer
For building = LBound(ThreeDArray, 1) To UBound(ThreeDArray, 1)
For emp = LBound(ThreeDArray, 2) To UBound(ThreeDArray, 2)
For info = LBound(ThreeDArray, 3) To UBound(ThreeDArray, 3)
'to transfer ThreeDArray into TempEmp by iterating all sets in the 3D array and fill TempEmp with the corresponding element value in each set of each row
TempEmp(building, emp, info) = ThreeDArray(building, emp, info)
Next
Next
Next
'fill remaining
'to fill the remaining elements we need to iterate from the last "row" with values to the last row in the array in each set, remember that the first empty element is the original array Ubound() plus 1
For building = LBound(TempEmp, 1) To UBound(TempEmp, 1)
For emp = UBound(ThreeDArray, 2) + 1 To UBound(TempEmp, 2)
For info = LBound(TempEmp, 3) To UBound(TempEmp, 3)
TempEmp(building, emp, info) = InformationNeeded & "NewRow"
Next
Next
Next
'erase Employees, attribute Temp array to Employees and erase Temp array
Erase ThreeDArray
ThreeDArray = TempEmp
Erase TempEmp
Изменение значений элементов и чтение
Чтение и изменение элементов в 3D-массиве может быть выполнено аналогично тому, как мы делаем 2D-массив, просто отрегулируйте дополнительный уровень в петлях и координатах.
Do
' using Do ... While for early exit
For building = 0 To UBound(ThreeDArray, 1)
For emp = 0 To UBound(ThreeDArray, 2)
If ThreeDArray(building, emp, 0) = "Gloria" And ThreeDArray(building, emp, 1) = "Stephan" Then
'if value found
ThreeDArray(building, emp, 1) = "Married, Last Name Change"
Exit Do
'don't iterate through all the array unless necessary
End If
Next
Next
Loop While False
'nested loop, will iterate through all elements
For building = LBound(ThreeDArray, 1) To UBound(ThreeDArray, 1)
For emp = LBound(ThreeDArray, 2) To UBound(ThreeDArray, 2)
For info = LBound(ThreeDArray, 3) To UBound(ThreeDArray, 3)
Debug.Print ThreeDArray(building, emp, info)
Next
Next
Next
'loop and coordinate, will iterate through all set of rows and ask for the row plus the value we choose for the columns
For building = LBound(ThreeDArray, 1) To UBound(ThreeDArray, 1)
For emp = LBound(ThreeDArray, 2) To UBound(ThreeDArray, 2)
Debug.Print ThreeDArray(building, emp, 0)
Debug.Print ThreeDArray(building, emp, 1)
Debug.Print ThreeDArray(building, emp, 2)
Debug.Print ThreeDArray(building, emp, 3)
Debug.Print ThreeDArray(building, emp, 4)
Debug.Print ThreeDArray(building, emp, 5)
Next
Next
'directly accessing element with coordinates
Debug.Print Employees(0, 5, 5)
Данные массива. VBA для чайников
Данные массива
При работе с массивами нужно помнить следующее.
* Можно создавать массивы данных любых типов. VBA с успехом хранит в массивах строки, даты, денежные значения и данные любых числовых типов.
* В одном массиве могут храниться данные только одного типа. Нельзя создать массив с раздельными ячейками для хранения и данных типа Date и данных типа String.
Правда, второе ограничение очень легко преодолеть. Как вы уже знаете, тип данных Variant допускает хранение данных любого из допустимых в VBA типов, и массивы данных типа Variant тоже вполне допустимы. Здесь следует иметь в виду, что информация, хранимая в виде данных типа Variant, занимает существенно больше места в памяти, чем та же информация в виде данных более определенного типа; для массивов произвольной длины это может потребовать дополнительно очень значительного расхода памяти. Но необходимость поместить в один массив неопределенной длины набор данных различных типов возникает крайне редко.
С другой стороны, довольно часто необходимо работать с наборами родственных в некотором смысле элементов разного типа. Именно такие наборы составляют структуру типичной базы данных. В базе данных для хранения адресов, например, каждая запись представляет собой некоторый набор элементов информации (имя и адрес), относящихся к одному объекту (человеку). Для подобных данных как раз и предлагается создавать пользовательские типы данных- как это сделать, вы узнаете чуть позже, в разделе «Определение своих собственных типов данных»).
Поделитесь на страничке
Следующая глава >
VBA Excel. Переменная диапазона ячеек (As Range)
Присвоение диапазона ячеек объектной переменной в VBA Excel. Адресация ячеек в переменной диапазона и работа с ними. Определение размера диапазона. Примеры.
Присвоение диапазона ячеек переменной
Чтобы переменной присвоить диапазон ячеек, она должна быть объявлена как Variant, Object или Range:
Dim myRange1 As Variant Dim myRange2 As Object Dim myRange3 As Range |
Чтобы было понятнее, для чего переменная создана, объявляйте ее как Range.
Присваивается переменной диапазон ячеек с помощью оператора Set:
Set myRange1 = Range(«B5:E16») Set myRange2 = Range(Cells(3, 4), Cells(26, 18)) Set myRange3 = Selection |
В выражении Range(Cells(3, 4), Cells(26, 18)) вместо чисел можно использовать переменные.
Для присвоения диапазона ячеек переменной можно использовать встроенное диалоговое окно Application.InputBox, которое позволяет выбрать диапазон на рабочем листе для дальнейшей работы с ним.
Адресация ячеек в диапазоне
К ячейкам присвоенного диапазона можно обращаться по их индексам, а также по индексам строк и столбцов, на пересечении которых они находятся.
Индексация ячеек в присвоенном диапазоне осуществляется слева направо и сверху вниз, например, для диапазона размерностью 5х5:
1 | 2 | 3 | 4 | 5 |
6 | 7 | 8 | 9 | 10 |
11 | 12 | 13 | 14 | 15 |
16 | 17 | 18 | 19 | 20 |
21 | 22 | 23 | 24 | 25 |
Индексация строк и столбцов начинается с левой верхней ячейки. В диапазоне этого примера содержится 5 строк и 5 столбцов. На пересечении 2 строки и 4 столбца находится ячейка с индексом 9. Обратиться к ней можно так:
‘обращение по индексам строки и столбца myRange.Cells(2, 4) ‘обращение по индексу ячейки myRange.Cells(9) |
Обращаться в переменной диапазона можно не только к отдельным ячейкам, но и к части диапазона (поддиапазону), присвоенного переменной, например,
обращение к первой строке присвоенного диапазона размерностью 5х5:
myRange.Range(«A1:E1») ‘или myRange.Range(Cells(1, 1), Cells(1, 5)) |
и обращение к первому столбцу присвоенного диапазона размерностью 5х5:
myRange.Range(«A1:A5») ‘или myRange.Range(Cells(1, 1), Cells(5, 1)) |
Работа с диапазоном в переменной
Работать с диапазоном в переменной можно точно также, как и с диапазоном на рабочем листе. Все свойства и методы объекта Range действительны и для диапазона, присвоенного переменной. При обращении к ячейке без указания свойства по умолчанию возвращается ее значение. Строки
MsgBox myRange.Cells(6) MsgBox myRange.Cells(6).Value |
равнозначны. В обоих случаях информационное сообщение MsgBox выведет значение ячейки с индексом 6.
Важно: если вы планируете работать только со значениями, используйте переменные массивов, код в них работает значительно быстрее.
Преимущество работы с диапазоном ячеек в объектной переменной заключается в том, что все изменения, внесенные в переменной, применяются к диапазону (который присвоен переменной) на рабочем листе.
Пример 1 – работа со значениями
Скопируйте процедуру в программный модуль и запустите ее выполнение.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | Sub Test1() ‘Объявляем переменную Dim myRange As Range ‘Присваиваем диапазон ячеек Set myRange = Range(«C6:E8») ‘Заполняем первую строку ‘Присваиваем значение первой ячейке myRange.Cells(1, 1) = 5 ‘Присваиваем значение второй ячейке myRange.Cells(1, 2) = 10 ‘Присваиваем третьей ячейке ‘значение выражения myRange.Cells(1, 3) = myRange.Cells(1, 1) _ * myRange.Cells(1, 2) ‘Заполняем вторую строку myRange.Cells(2, 1) = 20 myRange.Cells(2, 2) = 25 myRange.Cells(2, 3) = myRange.Cells(2, 1) _ + myRange.Cells(2, 2) ‘Заполняем третью строку myRange.Cells(3, 1) = «VBA» myRange.Cells(3, 2) = «Excel» myRange.Cells(3, 3) = myRange.Cells(3, 1) _ & » » & myRange.Cells(3, 2) End Sub |
Обратите внимание, что ячейки диапазона на рабочем листе заполнились так же, как и ячейки в переменной диапазона, что доказывает их непосредственную связь между собой.
Пример 2 – работа с форматами
Продолжаем работу с тем же диапазоном рабочего листа «C6:E8»:
Sub Test2() ‘Объявляем переменную Dim myRange As Range ‘Присваиваем диапазон ячеек Set myRange = Range(«C6:E8») ‘Первую строку выделяем жирным шрифтом myRange.Range(«A1:C1»).Font.Bold = True ‘Вторую строку выделяем фоном myRange.Range(«A2:C2»).Interior.Color = vbGreen ‘Третьей строке добавляем границы myRange.Range(«A3:C3»).Borders.LineStyle = True End Sub |
Опять же, обратите внимание, что все изменения форматов в присвоенном диапазоне отобразились на рабочем листе, несмотря на то, что мы непосредственно с ячейками рабочего листа не работали.
Пример 3 – копирование и вставка диапазона из переменной
Значения ячеек диапазона, присвоенного переменной, передаются в другой диапазон рабочего листа с помощью оператора присваивания.
Скопировать и вставить диапазон полностью со значениями и форматами можно при помощи метода Copy, указав место вставки (ячейку) на рабочем листе.
В примере используется тот же диапазон, что и в первых двух, так как он уже заполнен значениями и форматами.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | Sub Test3() ‘Объявляем переменную Dim myRange As Range ‘Присваиваем диапазон ячеек Set myRange = Range(«C6:E8») ‘Присваиваем ячейкам рабочего листа ‘значения ячеек переменной диапазона Range(«A1:C3») = myRange.Value MsgBox «Пауза» ‘Копирование диапазона переменной ‘и вставка его на рабочий лист ‘с указанием начальной ячейки myRange.Copy Range(«E1») MsgBox «Пауза» ‘Копируем и вставляем часть ‘диапазона из переменной myRange.Range(«A2:C2»).Copy Range(«E11») End Sub |
Информационное окно MsgBox добавлено, чтобы вы могли увидеть работу процедуры поэтапно, если решите проверить ее в своей книге Excel.
Размер диапазона в переменной
При получении диапазона с помощью метода Application.InputBox и присвоении его переменной диапазона, бывает полезно узнать его размерность. Это можно сделать следующим образом:
Sub Test4() ‘Объявляем переменную Dim myRange As Range ‘Присваиваем диапазон ячеек Set myRange = Application.InputBox(«Выберите диапазон ячеек:», , , , , , , 8) ‘Узнаем количество строк и столбцов MsgBox «Количество строк = » & myRange.Rows.Count _ & vbNewLine & «Количество столбцов = » & myRange.Columns.Count End Sub |
Запустите процедуру, выберите на рабочем листе Excel любой диапазон и нажмите кнопку «OK». Информационное сообщение выведет количество строк и столбцов в диапазоне, присвоенном переменной myRange.
Все про массивы в VBA
Все про массивы в VBA
Автор Дмитрий Якушев На чтение24 мин. Просмотров2.7k.
Дональд Кнут
Список настолько же силен, как и его самое слабое звено
В следующей таблице приведена краткая справка по использованию массивов в VBA. Оставшаяся часть статьи содержит наиболее полное руководство, которое вы найдете о массивах VBA.
Содержание
- Краткое руководство по массивам VBA
- Введение
- Быстрые заметки
- Что такое массивы и зачем они нужны?
- Типы массивов VBA
- Объявление массива
- Присвоение значений массиву
- Использование функций Array и Split
- Использование циклов с массивами
- Использование цикла For Each
- Использование Erase
- ReDim с Preserve
- Использование Preserve с 2-мерными массивами
- Сортировка массива
- Передача массива в Sub или функцию
- Возвращение массива из функции
- Двумерные массивы
- Использование цикла For Each
- Чтение из диапазона ячеек в массив
- Как заставить ваши макросы работать на суперскорости
- Заключение
Краткое руководство по массивам VBA
Задача | Статический | Динамический |
Объявление | Dim arr(0 To 5) As | Dim arr() As Long |
Установить размер | См. «Объявление» | ReDim arr(0 To 5)As |
Увеличить размер | Только | 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) | For i = LBound(arr) To UBound(arr) |
Читать все записи (2D) | For i = LBound(arr,1) To UBound(arr,1) | For i = LBound(arr,1) To UBound(arr,1) |
Читать все записи | Dim item As Variant | Dim item As Variant |
Перейти на Sub | Sub MySub(ByRef arr() As String) | Sub MySub(ByRef arr() As String) |
Возврат из функции | Function GetArray() | Function GetArray() |
Получить от | Только | Dim arr() As Long |
Стереть массив | Erase arr | Erase arr |
Строка в массив | Только | Dim arr As Variant |
Массив в строку | Dim sName As String | Dim sName As String |
Заполните | Только | Dim arr As Variant |
Диапазон в массив | Только | Dim arr As Variant |
Массив в диапазон | Так же, как в | Dim arr As Variant |
Введение
В этой статье подробно рассматриваются массивы на языке программирования Excel VBA. Она охватывает важные моменты, такие как:
- Зачем вам массивы
- Когда вы должны их использовать
- Два типа массивов
- Использование более одного измерения
- Объявление массивов
- Добавление значений
- Просмотр всех предметов
- Супер эффективный способ чтения Range в масси
В первом разделе мы рассмотрим, что такое массивы и зачем они нужны. Вы можете не понимать часть кода в первом разделе. Это отлично. Я буду разбивать на простые термины в следующих разделах статьи.
Быстрые заметки
Иногда коллекции лучше, чем массивы. Вы можете прочитать о коллекциях здесь.
Массивы и циклы идут рука об руку. Наиболее распространенными циклами, которые вы используете с массивами, являются циклы For и For Each.
Что такое массивы и зачем они нужны?
Массив VBA — это тип переменной. Используется для хранения списков данных одного типа. Примером может быть сохранение списка стран или списка итогов за неделю.
В VBA обычная переменная может хранить только одно значение за раз.
В следующем примере показана переменная, используемая для хранения оценок ученика.
1 2 3 | ‘ Может хранить только 1 значение за раз Dim Student1 As Integer Student1 = 55 |
Если мы хотим сохранить оценки другого ученика, нам нужно создать вторую переменную.
В следующем примере у нас есть оценки пяти студентов
Мы собираемся прочитать эти отметки и записать их в Immediate Window.
Примечание. Функция Debug.Print записывает значения в Immediate Window. Для просмотра этого окна выберите View-> Immediate Window из меню (сочетание клавиш Ctrl + G).
Как видите в следующем примере, мы пишем один и тот же код пять раз — по одному для каждого учащегося.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | Public Sub StudentMarks()
With ThisWorkbook.Worksheets(«Лист1»)
‘ Объявите переменную для каждого студента Dim Student1 As Integer Dim Student2 As Integer Dim Student3 As Integer Dim Student4 As Integer Dim Student5 As Integer
‘ Читайте оценки студентов из ячейки Student1 = .Range(«C2»).Offset(1) Student2 = .Range(«C2»).Offset(2) Student3 = .Range(«C2»).Offset(3) Student4 = .Range(«C2»).Offset(4) Student5 = .Range(«C2»).Offset(5)
‘ Печать студенческих оценок Debug.Print «Оценки студентов» Debug.Print Student1 Debug.Print Student2 Debug.Print Student3 Debug.Print Student4 Debug.Print Student5
End With
End Sub |
Ниже приведен вывод из примера
Проблема с использованием одной переменной для каждого учащегося заключается в том, что вам необходимо добавить код для каждого учащегося. Поэтому, если в приведенном выше примере у вас будет тысяча студентов, вам понадобится три тысячи строк кода!
К счастью, у нас есть массивы, чтобы сделать нашу жизнь проще. Массивы позволяют нам хранить список элементов данных в одной структуре.
Следующий код показывает приведенный выше пример с использованием массива.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | Public Sub StudentMarksArr()
With ThisWorkbook.Worksheets(«Лист1»)
‘ Объявите массив для хранения оценок для 5 студентов Dim Students(1 To 5) As Integer
‘ Читайте оценки учеников из ячеек C3: C7 в массив Dim i As Integer For i = 1 To 5 Students(i) = .Range(«C2»).Offset(i) Next i
‘ Распечатывать оценки студентов из массива Debug.Print «Оценки студентов» For i = LBound(Students) To UBound(Students) Debug.Print Students(i) Next i
End With
End Sub |
Преимущество этого кода в том, что он будет работать для любого количества студентов. Если нам нужно изменить этот код для работы с 1000 студентами, нам нужно всего лишь изменить (от 1 до 5) на (от 1 до 1000) в декларации. В предыдущем примере нам нужно было добавить примерно пять тысяч строк кода.
Давайте проведем быстрое сравнение переменных и массивов. Сначала мы сравним декларацию.
1 2 3 4 5 6 7 | ‘ Переменная Dim Student As Integer Dim Country As String
‘ Массив Dim Students(1 To 3) As Integer Dim Countries(1 To 3) As String |
Далее мы сравниваем присвоение значения
1 2 3 4 5 | ‘ присвоить значение переменной Student1 = .Cells(1, 1)
‘ присваивать значение первому элементу в массиве Students(1) = .Cells(1, 1) |
Наконец, мы смотрим на запись значений
1 2 3 4 5 | ‘ Вывести значение переменной Debug.Print Student1
‘ Вывести значение первого студента в массиве Debug.Print Students(1) |
Как видите, использование переменных и массивов очень похоже.
Важным является тот факт, что массивы используют индекс (также называемый нижним индексом) для доступа к каждому элементу. Это означает, что мы можем легко получить доступ ко всем элементам в массиве, используя цикл For.
Теперь, когда у вас есть представление о том, почему массивы полезны, давайте пройдемся по ним шаг за шагом.
Типы массивов VBA
В VBA есть два типа массивов:
- Статический — массив фиксированного размера.
- Динамический — массив, в котором размер задается во время выполнения
Разница между этими массивами в основном в том, как они создаются. Доступ к значениям в обоих типах массивов абсолютно одинаков. В следующих разделах мы рассмотрим оба типа.
Объявление массива
Статический массив объявляется следующим образом
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | Public Sub DecArrayStatic()
‘ Создать массив с местоположениями 0,1,2,3 Dim arrMarks1(0 To 3) As Long
‘ По умолчанию от 0 до 3, то есть местоположения 0,1,2,3 Dim arrMarks2(3) As Long
‘ Создать массив с местоположениями 1,2,3,4,5 Dim arrMarks1(1 To 5) As Long
‘ Создать массив с местоположениями 2,3,4 ‘Это редко используется Dim arrMarks3(2 To 4) As Long
End Sub |
Как видите, размер указывается при объявлении статического массива. Проблема в том, что вы никогда не можете быть заранее уверены, какой размер вам нужен. Каждый раз, когда вы запускаете макрос, у вас могут быть разные требования к размеру.
Если вы не используете все расположения массива, ресурсы тратятся впустую. Если вам нужно больше мест, вы можете использовать ReDim, но это по сути создает новый статический массив.
Динамический массив не имеет таких проблем. Вы не указываете размер, когда объявляете. Поэтому вы можете расти и уменьшаться по мере необходимости.
1 2 3 4 5 6 7 8 9 | Public Sub DecArrayDynamic()
‘ Объявить динамический массив Dim arrMarks() As Long
‘ Установите размер массива, когда вы будете готовы ReDim arrMarks(0 To 5)
End Sub |
Динамический массив не выделяется, пока вы не используете оператор ReDim. Преимущество в том, что вы можете подождать, пока не узнаете количество элементов, прежде чем устанавливать размер массива. Со статическим массивом вы должны указать размер заранее.
Привожу пример. Представьте, что вы читали листы студенческих оценок. С помощью динамического массива вы можете сосчитать студентов на рабочем листе и установить массив для этого размера. Со статическим массивом вы должны установить размер максимально возможного числа студентов.
Присвоение значений массиву
Чтобы присвоить значения массиву, вы используете номер местоположения. Вы присваиваете значение для обоих типов массивов одинаково.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | Public Sub AssignValue()
‘ Объявить массив с местоположениями 0,1,2,3 Dim arrMarks(0 To 3) As Long
‘ Установите значение позиции 0 arrMarks(0) = 5
‘ становите значение позиции 3 arrMarks(3) = 46
‘ Это ошибка, так как нет местоположения 4 arrMarks(4) = 99
End Sub |
Номер места называется индексом. Последняя строка в примере выдаст ошибку «Индекс вне диапазона», так как в примере массива нет местоположения 4.
Использование функций Array и Split
Вы можете использовать функцию Array для заполнения массива списком элементов. Вы должны объявить массив как тип Variant. Следующий код показывает, как использовать эту функцию.
1 2 3 4 5 | Dim arr1 As Variant arr1 = Array(«Апельсин», «Персик»,»Груша»)
Dim arr2 As Variant arr2 = Array(5, 6, 7, 8, 12) |
Массив, созданный функцией Array, начнется с нулевого индекса, если вы не используете Option Base 1 в верхней части вашего модуля. Затем он начнется с первого индекса. В программировании, как правило, считается плохой практикой иметь ваши реальные данные в коде. Однако иногда это полезно, когда вам нужно быстро протестировать некоторый код. Функция Split используется для разделения строки на массив на основе разделителя. Разделитель — это символ, такой как запятая или пробел, который разделяет элементы.
Следующий код разделит строку на массив из трех элементов.
1 2 3 4 5 | Dim s As String s = «Красный,Желтый,Зеленый,Синий»
Dim arr() As String arr = Split(s, «,») |
Функция Split обычно используется, когда вы читаете из файла, разделенного запятыми, или из другого источника, который предоставляет список элементов, разделенных одним и тем же символом.
Использование циклов с массивами
Использование цикла For обеспечивает быстрый доступ ко всем элементам массива. Вот где сила использования массивов становится очевидной. Мы можем читать массивы с десятью значениями или десятью тысячами значений, используя те же несколько строк кода. В VBA есть две функции: LBound и UBound. Эти функции возвращают самый маленький и самый большой индекс в массиве. В массиве arrMarks (от 0 до 3) LBound вернет 0, а UBound вернет 3.
В следующем примере случайные числа присваиваются массиву с помощью цикла. Затем он печатает эти числа, используя второй цикл.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | Public Sub ArrayLoops()
‘ Объявить массив Dim arrMarks(0 To 5) As Long
‘ Заполните массив случайными числами Dim i As Long For i = LBound(arrMarks) To UBound(arrMarks) arrMarks(i) = 5 * Rnd Next i
‘ Распечатайте значения в массиве Debug.Print «Место нахождения», «Значение» For i = LBound(arrMarks) To UBound(arrMarks) Debug.Print i, arrMarks(i) Next i
End Sub |
Функции LBound и UBound очень полезны. Их использование означает, что наши циклы будут работать правильно с любым размером массива. Реальное преимущество заключается в том, что если размер массива изменяется, нам не нужно менять код для печати значений. Цикл будет работать для массива любого размера, пока вы используете эти функции.
Использование цикла For Each
Вы можете использовать цикл For Each с массивами. Важно помнить, что он доступен только для чтения. Это означает, что вы не можете изменить значение в массиве.
В следующем коде значение метки изменяется, но оно не меняет значение в массиве.
1 2 3 4 | For Each mark In arrMarks ‘ Не изменит значение массива mark = 5 * Rnd Next mark |
Цикл For Each отлично подходит для чтения массива. Как видите, лучше писать специально для двумерного массива.
1 2 3 4 | Dim mark As Variant For Each mark In arrMarks Debug.Print mark Next mark |
Использование Erase
Функция Erase может использоваться для массивов, но она работает по-разному в зависимости от типа массива.
Для статического массива функция Erase сбрасывает все значения по умолчанию. Если массив состоит из целых чисел, то все значения устанавливаются в ноль. Если массив состоит из строк, то все строки устанавливаются в «» и так далее.
Для динамического массива функция удаления стирает память. То есть он удаляет массив. Если вы хотите использовать его снова, вы должны использовать ReDim для выделения памяти.
Давайте рассмотрим пример статического массива. Этот пример аналогичен примеру ArrayLoops в последнем разделе с одним отличием — мы используем Erase после установки значений. Когда значение будет распечатано, все они будут равны нулю.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | Public Sub EraseStatic()
‘ Объявить массив Dim arrMarks(0 To 3) As Long
‘ Заполните массив случайными числами Dim i As Long For i = LBound(arrMarks) To UBound(arrMarks) arrMarks(i) = 5 * Rnd Next i
‘ ВСЕ ЗНАЧЕНИЯ УСТАНОВЛЕНЫ НА НОЛЬ Erase arrMarks
‘ Распечатайте значения — там все теперь ноль Debug.Print «Место нахождения», «Значение» For i = LBound(arrMarks) To UBound(arrMarks) Debug.Print i, arrMarks(i) Next i
End Sub |
Теперь мы попробуем тот же пример с динамикой. После того, как мы используем Erase, все места в массиве были удалены. Нам нужно использовать ReDim, если мы хотим использовать массив снова.
Если мы попытаемся получить доступ к членам этого массива, мы получим ошибку «Индекс вне диапазона».
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Public Sub EraseDynamic()
‘ Объявить массив Dim arrMarks() As Long ReDim arrMarks(0 To 3)
‘ Заполните массив случайными числами Dim i As Long For i = LBound(arrMarks) To UBound(arrMarks) arrMarks(i) = 5 * Rnd Next i
‘ arrMarks теперь освобожден. Места не существуют. Erase arrMarks
End Sub |
ReDim с Preserve
Если мы используем ReDim для существующего массива, то массив и его содержимое будут удалены.
В следующем примере второй оператор ReDim создаст совершенно новый массив. Исходный массив и его содержимое будут удалены.
1 2 3 4 5 6 7 8 9 10 11 12 | Sub UsingRedim()
Dim arr() As String
‘ Установить массив в слоты от 0 до 2 ReDim arr(0 To 2) arr(0) = «Яблоко»
‘ Массив с яблоком теперь удален ReDim arr(0 To 3)
End Sub |
Если мы хотим расширить размер массива без потери содержимого, мы можем использовать ключевое слово Preserve.
Когда мы используем Redim Preserve, новый массив должен начинаться с того же начального размера, например
Мы не можем сохранить от (0 до 2) до (от 1 до 3) или до (от 2 до 10), поскольку они являются различными начальными размерами.
В следующем коде мы создаем массив с использованием ReDim, а затем заполняем массив типами фруктов.
Затем мы используем Preserve для увеличения размера массива, чтобы не потерять оригинальное содержимое.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | Sub UsingRedimPreserve()
Dim arr() As String
‘ Установить массив в слоты от 0 до 1 ReDim arr(0 To 2) arr(0) = «Яблоко» arr(1) = «Апельсин» arr(2) = «Груша»
‘ Изменение размера и сохранение исходного содержимого ReDim Preserve arr(0 To 5)
End Sub |
Из приведенных ниже снимков экрана видно, что исходное содержимое массива было «сохранено».
Предостережение: в большинстве случаев вам не нужно изменять размер массива, как мы делали в этом разделе. Если вы изменяете размер массива несколько раз, то вам захочется рассмотреть возможность использования коллекции.
Использование Preserve с 2-мерными массивами
Preserve работает только с верхней границей массива.
Например, если у вас есть двумерный массив, вы можете сохранить только второе измерение, как показано в следующем примере:
1 2 3 4 5 6 7 8 9 10 11 | Sub Preserve2D()
Dim arr() As Long
‘ Установите начальный размер ReDim arr(1 To 2, 1 To 5)
‘ Изменить размер верхнего измерения ReDim Preserve arr(1 To 2, 1 To 10)
End Sub |
Если мы попытаемся использовать Preserve на нижней границе, мы получим ошибку «Индекс вне диапазона».
В следующем коде мы используем Preserve для первого измерения. Запуск этого кода приведет к ошибке «Индекс вне диапазона»:
1 2 3 4 5 6 7 8 9 10 11 | Sub Preserve2DError()
Dim arr() As Long
‘ Установите начальный размер ReDim arr(1 To 2, 1 To 5)
‘ Ошибка «Вне диапазона» ReDim Preserve arr(1 To 5, 1 To 5)
End Sub |
Когда мы читаем из диапазона в массив, он автоматически создает двумерный массив, даже если у нас есть только один столбец.
Применяются те же правила сохранения. Мы можем использовать Preserve только на верхней границе, как показано в следующем примере:
1 2 3 4 5 6 7 8 9 10 11 | Sub Preserve2DRange()
Dim arr As Variant
‘ Назначить диапазон массиву arr = Sheet1.Range(«A1:A5»).Value
‘ Preserve будет работать только на верхней границе ReDim Preserve arr(1 To 5, 1 To 7)
End Sub |
Сортировка массива
В VBA нет функции для сортировки массива. Мы можем отсортировать ячейки листа, но это медленно, если данных много.
Функция быстрой сортировки ниже может использоваться для сортировки массива.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | Sub QuickSort(arr As Variant, first As Long, last As Long)
Dim vCentreVal As Variant, vTemp As Variant
Dim lTempLow As Long Dim lTempHi As Long lTempLow = first lTempHi = last
vCentreVal = arr((first + last) \ 2) Do While lTempLow <= lTempHi
Do While arr(lTempLow) < vCentreVal And lTempLow < last lTempLow = lTempLow + 1 Loop
Do While vCentreVal < arr(lTempHi) And lTempHi > first lTempHi = lTempHi — 1 Loop
If lTempLow <= lTempHi Then
‘ Поменять значения vTemp = arr(lTempLow)
arr(lTempLow) = arr(lTempHi) arr(lTempHi) = vTemp
‘ Перейти к следующим позициям lTempLow = lTempLow + 1 lTempHi = lTempHi — 1
End If
Loop
If first < lTempHi Then QuickSort arr, first, lTempHi If lTempLow < last Then QuickSort arr, lTempLow, last
End Sub |
Вы можете использовать эту функцию так:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Sub TestSort()
‘ Создать временный массив Dim arr() As Variant arr = Array(«Банан», «Дыня», «Персик», «Слива», «Яблоко»)
‘ Сортировать массив QuickSort arr, LBound(arr), UBound(arr)
‘ Печать массива в Immediate Window(Ctrl + G) Dim i As Long For i = LBound(arr) To UBound(arr) Debug.Print arr(i) Next i
End Sub |
Передача массива в Sub или функцию
Иногда вам нужно будет передать массив в процедуру. Вы объявляете параметр, используя круглые скобки, аналогично тому, как вы объявляете динамический массив.
Переход к процедуре с использованием ByRef означает, что вы передаете ссылку на массив. Таким образом, если вы измените массив в процедуре, он будет изменен, когда вы вернетесь.
Примечание. Когда вы используете массив в качестве параметра, он не может использовать ByVal, он должен использовать ByRef. Вы можете передать массив с помощью ByVal, сделав параметр вариантом.
1 2 3 4 5 6 7 8 9 10 11 | ‘ Передает массив в функцию Public Sub PassToProc() Dim arr(0 To 5) As String |
Динамический массив в Excel VBA
Если размер вашего массива увеличивается, и вы не хотите фиксировать размер массива, вы можете использовать ключевое слово ReDim . Excel VBA затем автоматически изменяет размер массива.
Добавьте числа в столбец A.
Поместите командную кнопку на лист и добавьте следующие строки кода:
1. Сначала мы объявляем массив с именем numbers. Также объявите две переменные типа Integer.Один именованный размер и один — i.
Тусклые числа () как целое, размер как целое, i как целое
Примечание: у массива еще нет размера. числа, размер и i здесь выбираются случайным образом, вы можете использовать любые имена. Не забудьте ссылаться на эти имена в остальной части кода.
2. Затем мы определяем размер массива и сохраняем его в переменной size. Для этого вы можете использовать функцию рабочего листа CountA. Добавьте следующую строку кода:
size = WorksheetFunction.CountA (Рабочие листы (1). Столбцы (1))
3. Теперь мы знаем размер массива и можем изменить его размер. Добавьте следующую строку кода:
Номера ReDim (размер)
4. Затем мы инициализируем каждый элемент массива. Используем петлю.
Для i = 1 К размеру
числа (i) = Ячейки (i, 1). Значение
Далее i
5. Мы отображаем последний элемент массива с помощью MsgBox.
Номера MsgBox (размер)
6. Закройте редактор Visual Basic и нажмите кнопку команды на листе.
Результат:
7. Теперь, чтобы ясно понять, почему это называется динамическим массивом, добавьте число в столбец A.
8. Еще раз нажмите командную кнопку.
Заключение: Excel VBA автоматически изменил размер этого динамического массива.
9. Когда вы используете ключевое слово ReDim, вы стираете все существующие данные, хранящиеся в данный момент в массиве. Например, добавьте следующие строки кода к ранее созданному коду:
номера ReDim (3)
Номера MsgBox (1)
Результат:
Массив пуст.
10. Если вы хотите сохранить данные в существующем массиве при его изменении размеров, используйте ключевое слово Preserve.
Номера ReDim Preserve (3)
Номера MsgBox (1)
Результат:
.
Функция массива VBA — создание массивов VBA из значений
Загрузка …
Функция массива VBA в Excel создает массив VBA Variant из списка значений, разделенных запятыми. Если значения не указаны, создается массив VBA нулевой длины.
Синтаксис функции массива VBA
Синтаксис функции массива в VBA:
, где Value может быть любым типом переменной или константой, то есть типом Variant (любым типом). Из-за этого вы можете инициировать массив VBA, используя вышеуказанную функцию, даже с переменными разных типов, см. Ниже пример массива, инициированного с числами, строками и датами:
Советы и хитрости
Получить длину массива VBA
Чтобы увидеть больше советов и трюков с массивами VBA, см. Мой учебник или несколько основных советов по использованию.
Dim arr как вариант
arr = Массив (10, 20, 30)
‘Получить размер массива
Debug.Print UBound (arr) — LBound (arr) + 1 ‘Результат: 4
Dim arr As Variant arr = Array (10, 20, 30) ‘Получить размер массива Debug.Print UBound (arr) — LBound (arr) + 1′ Result: 4 |
Как расширить массив VBA
Ниже приведен пример создания массива с помощью функции массива VBA с последующим расширением его по элементам с сохранением предыдущих элементов.
Dim arr как вариант
arr = Массив (10, 20, 30)
Отладка.Печать arr (2) ’30
‘Изменить размер массива и сохранить
ReDim Preserve обр. (3)
Debug.Print arr (3) ‘Результат: пусто
arr (3) = 4 ‘Установить в 4
Debug.Print arr (3) ‘Результат: 4
Dim arr As Variant arr = Array (10, 20, 30) Debug.Print arr (2) ’30 ‘ Изменить размер массива и сохранить ReDim Preserve arr (3) Отладка.Печать arr (3) ‘Результат: пусто arr (3) = 4′ Установить 4 Debug.Print arr (3) ‘Результат: 4 |
Примеры
Ниже приведены несколько полезных примеров использования функции:
VBA Массив из строковых значений
Dim arr как вариант
arr = Array («Том», «Майкл», «Джон»)
‘Распечатать все элементы массива
Debug.Print arr (0) ‘Результат: Том
Debug.Print arr (1) ‘Результат: Майкл
Debug.Print arr (2) ‘Результат: Джон
‘Получить длину массива
Отлаживать.Печать UBound (arr) — LBound (arr) + 1 ‘Результат: 3
Dim arr As Variant arr = Array («Tom», «Michael», «John») ‘Распечатать все элементы массива Debug.Print arr (0)’ Результат: Tom Debug.Print arr (1) ‘Результат: Michael Debug.Print arr (2)’ Результат: John ‘Получить длину массива Debug.Print UBound (arr) — LBound (arr) + 1′ Result : 3 |
Приведенный выше пример можно упростить с помощью функции VBA Split, исключив дополнительные скобки:
Dim arr как вариант
arr = Split («Том, Майкл, Джон»)
Отлаживать.Печать arr (0) ‘Результат: Tom
Dim arr As Variant arr = Split («Tom, Michael, John») Debug.Print arr (0) ‘Результат: Tom |
Разделитель даже лучше для струнных, так как вы можете использовать другой разделитель:
Dim arr как вариант
arr = Split («Том; Майкл; Джон», «;»)
Debug.Print arr (0) ‘Результат: Tom
Dim arr As Variant arr = Split («Том; Майкл; Джон», «;») Отладка.Печать arr (0) ‘Результат: Tom |
VBA Массив или числа
Ниже простой пример массива чисел:
Dim arr как вариант
arr = Массив (10, 20, 30)
Debug.Print arr (0) ‘Результат: 20
Dim arr As Variant arr = Array (10, 20, 30) Debug.Print arr (0) ‘Результат: 20 |
Массив или массивы VBA
Аналогичным образом вы можете использовать эту функцию для создания других типов массивов, даже массива массивов VBA i.е. 2-мерный массив:
Dim arr как вариант
arr = Массив (Массив (10, 20, 30), Массив (40, 50, 60))
Debug.Print arr (0) (1) ‘Результат: 20
Debug.Print arr (1) (0) ‘Результат: 40
Dim arr As Variant arr = Array (Array (10, 20, 30), Array (40, 50, 60)) Debug.Print arr (0) (1) ‘Result: 20 Debug.Print arr (1) (0) ‘Результат: 40 |
.