Vba — arrays

Ключевые слова для работы с текстом

Пример применения формулы массива

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

Товарный чек

Как бы мы решали данную задачу стандартным образом?

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

Подсчет суммы заказа

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

Расчет общей суммы покупки

Ту же самую задачу можно решить с помощью простейшей формулы массива.

Нам нужно получить сумму, поэтому воспользуемся соответствующей функцией СУММ. А вот суммировать мы должны произведение цены товара на его количество, что мы и сделаем – выбираем диапазон значений из столица B и умножаем его на аналогичный диапазон значений столбца C.

Формула массива

Если сейчас нажать Enter, то появится ошибка.

Ошибка при нажатии Enter

Так как мы в формуле использовали диапазоны (массивы) данных, то и формула должна быть формулой массива. Для этого нужно нажать сочетание клавиш Ctrl + Shift + Enterи получим результат.

Формула массива

Что произошло и как работает формула массива? Это важно понять, так как в дальнейшем можно будет применять формулы массива для решения намного более заковыристых задач…

Итак, при вычислении формулы массива Excel произвел попарное умножение значений диапазонов B2:B5 и C2:C5. В результате получился массив значений который был просуммирован соответствующей функцией. Мы получили только одно значение итоговой суммы без вспомогательных вычислений.

Обратите внимание на формулу в строке формул. Она заключена в фигурные скобки, которые указывают нам, что мы имеем дело с формулой массива

Скобки формулы массива

Это не текстовые скобки, то есть нельзя их ввести с клавиатуры, чтобы сделать формулу формулой массива. Они появляются автоматически при нажатии сочетания клавиш Ctrl + Shift + Enter. Если после создания формулы массива вам необходимо ее отредактировать, то в конце необходимо вновь нажать сочетание клавиш, а не просто клавишу Enter.

Excel vba массив

Об’явление (описание) массива :

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

прим. * На самом деле существует функция Array , которая позволяет создавать массив в ходе выполнения программы, без предварительного описания.

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

Об’явление одномерного фиксированного массива :

Dim iString(3) As String

Об’явление двухмерного фиксированного массива :

Dim iData(5, 2) As Double

Об’явление динамического массива :

Dim iWhatIs() As Integer

При об’явлении (описании) динамического массива его размер не указывается. В процессе выполнения программы его размер может изменяться, причём неоднократно. Поэтому динамический массив применяют, если предполагается, что размер массива не будет постоянным.

ReDim – инструкция, которая позволяет изменять динамический массив.

Dim iWhatIs() As Integer

Примечание : Обратите внимание на то, что элементы массива, которые были созданы ранее, после использования ReDim не сохраняются. ReDim Preserve – инструкция, которая позволяет изменять динамический массив, с сохранением всех элементов массива

ReDim Preserve – инструкция, которая позволяет изменять динамический массив, с сохранением всех элементов массива.

ReDim Preserve iWhatIs(2) iWhatIs(0) = 1 iWhatIs(1) = 2 iWhatIs(2) = 3

ReDim Preserve iWhatIs(3) iWhatIs(3) = 4

Номер первого элемента и границы массива :

Пример фиксированного массива, где нумерация элементов начинается с 1

Dim iString(3) As String

Dim iData(5, 2) As Double

Примечание : Инструкцию Option Base необходимо расположить в самом начале модуля VBA.

Пример об’явления фиксированных массивов с явным описанием нижней границы :

Dim iString(1 To 3) As String

Dim iData(1 To 5, 1 To 2) As Double

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

Dim iString(5 To 10)

Dim iData(1 To 5, 2 To 5)

Макрофункции для работы с массивом :

Array – функция позволяет создавать массив в ходе выполнения программы, без предварительного описания.

iArray = Array(“ivan”, “john”, “maurizio”)

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

IsArray – функция проверяет, является ли переменная массивом. IsArray имеет всего один аргумент (переменную) и в зависимости от результатов проверки возвращает True – если переменная является массивом, или False – если нет.

Пример проверки динамического массива :

ReDim Preserve iArray(1 To 3) iArray(1) = “ivan” iArray(2) = “john” iArray(3) = “maurizio”

ReDim Preserve iArray(1 To 4) iArray(4) = “it’s very good man’s”

If IsArray(iArray) = True Then MsgBox “iArray – это массив”, , “” Else MsgBox “iArray – это не массив”, , “” End If

Пример проверки массива, созданного с помощью функции Array :

iArray = Array(“ivan”, “john”, “maurizio”)

If IsArray(iArray) = True Then MsgBox “iArray – это массив”, , “” Else MsgBox “iArray – это не массив”, , “” End If

LBound – функция определит нижнюю границу массива. UBound – функция определит верхнюю границу массива.

Dim iData(5 To 15, 1 To 100) As Double iLBound = LBound(iData) iUBound = UBound(iData)

MsgBox “Нижняя граница массива : ” & iLBound & Chr(10) _ & “Верхняя граница массива : ” & iUBound, , “”

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

Массивы, на которые мы смотрели до сих пор, были одномерными. Это означает, что массивы представляют собой один список элементов.

Двумерный массив — это список списков. Если вы думаете об одной строке электронной таблицы как об одном измерении, то более одного столбца является двухмерным. На самом деле электронная таблица является эквивалентом двумерного массива. Он имеет два измерения — строки и столбцы.

Следует отметить одну маленькую вещь: Excel обрабатывает одномерный массив как строку, если вы записываете его в электронную таблицу. Другими словами, массив arr (от 1 до 5) эквивалентен arr (от 1 до 1, от 1 до 5) при записи значений в электронную таблицу.

На следующем рисунке показаны две группы данных. Первый — это одномерный массив, а второй — двухмерный.

Чтобы получить доступ к элементу в первом наборе данных (одномерном), все, что вам нужно сделать, это дать строку, например. 1,2, 3 или 4.

Для второго набора данных (двумерного) вам нужно указать строку И столбец. Таким образом, вы можете думать, что 1-мерное — это несколько столбцов, а одна строка и двухмерное — это несколько строк и несколько столбцов.

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

Вы объявляете двумерный массив следующим образом:

Dim ArrayMarks(0 To 2, 0 To 3) As Long

В следующем примере создается случайное значение для каждого элемента в массиве и печатается значение в Immediate Window.

Public Sub TwoDimArray()

    ' Объявить двумерный массив
    Dim arrMarks(0 To 3, 0 To 2) As String

    ' Заполните массив текстом, состоящим из значений i и j
    Dim i As Long, j As Long
    For i = LBound(arrMarks) To UBound(arrMarks)
        For j = LBound(arrMarks, 2) To UBound(arrMarks, 2)
            arrMarks(i, j) = CStr(i) & ":" & CStr(j)
        Next j
    Next i

    ' Вывести значения в массиве в Immediate Window
    Debug.Print "i", "j", "Знаечние"
    For i = LBound(arrMarks) To UBound(arrMarks)
        For j = LBound(arrMarks, 2) To UBound(arrMarks, 2)
            Debug.Print i, j, arrMarks(i, j)
        Next j
    Next i

End Sub

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

Результат примера выглядит следующим образом:

Этот макрос работает следующим образом:

• Входит в цикл i
• i установлен на 0
• цикл Enters j
• j установлен на 0
• j установлен в 1
• j установлен на 2
• Выход из цикла j
• i установлен в 1
• j установлен на 0
• j установлен в 1
• j установлен на 2
• И так до тех пор, пока i = 3 и j = 2

Заметьте, что LBound и UBound имеют второй аргумент 2. Это указывает, что это верхняя или нижняя граница второго измерения. Это начальное и конечное местоположение для j. Значение по умолчанию 1, поэтому нам не нужно указывать его для цикла i.

Использование цикла For Each

Использование For Each лучше использовать при чтении из массива.Давайте возьмем код сверху, который выписывает двумерный массив.

 ' Для цикла For необходимо два цикла
    Debug.Print "i", "j", "Значение"
    For i = LBound(arrMarks) To UBound(arrMarks)
        For j = LBound(arrMarks, 2) To UBound(arrMarks, 2)
            Debug.Print i, j, arrMarks(i, j)
        Next j
    Next i

Теперь давайте перепишем его, используя цикл For Each. Как видите, нам нужен только один цикл, и поэтому гораздо проще написать:

 ' Использование For Each требует только одного цикла
    Debug.Print "Значение"
    Dim mark As Variant
    For Each mark In arrMarks
        Debug.Print mark
    Next mark

Использование цикла For Each дает нам массив только в одном порядке — от LBound до UBound. В большинстве случаев это все, что вам нужно.

Объявление массива

Статический массив объявляется следующим образом

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

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

Динамические массивы не имеют таких проблем. Размер массивы не указывается при объявлении. Поэтому его размер может расти и уменьшаться по мере необходимости

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

Синтаксис, параметры, значения

Синтаксис функции InStr

Полный вариант:

Сокращенный вариант:

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

Параметры функции InStr

* Если задан аргумент compare, аргумент start является обязательным.
** Если аргумент compare не указан, используется значение инструкции Option Compare, заданное на уровне модуля. Если инструкция Option Compare в начале модуля отсутствует, используется ее значение по умолчанию – 0 (двоичное сравнение).

Если параметр start или параметр compare содержит значение NULL, возникает ошибка.

Значения аргумента «compare»

* При двоичном сравнении учитывается регистр букв, при текстовом – не учитывается.

Значения функции InStr

Функции для работы со ссылками и массивами

В Excel имеется ряд функций для обработки ссылок и массивов: вычисление номеров строки или столбца таблицы по имени ссылки, определение количества столбцов (строк) ссылки или массива, выбор значения по номеру индекса и т. д.

Таблица 4.10. Функции для работы со ссылками и массивами.

Чтение из диапазона ячеек в массив

Если вы читали мою статью о ячейках и диапазонах, то вы знаете, что VBA имеет чрезвычайно эффективный способ чтения из диапазона ячеек в массив и наоборот.

Public Sub ReadToArray()

    ' Объявить динамический массив
    Dim StudentMarks As Variant

    ' Считать значения в массив из первой строки
    StudentMarks = Range("A1:Z1").Value

    ' Запишите значения обратно в третий ряд
    Range("A3:Z3").Value = StudentMarks

End Sub

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

В следующем примере будут считаны примеры данных студента ниже из C3: E6 Лист1 и распечатаны в Immediate Window.

Public Sub ReadAndDisplay()

    ' Получить диапазон
    Dim rg As Range
    Set rg = ThisWorkbook.Worksheets("Лист1").Range("C3:E6")

    ' Создать динамический массив
    Dim StudentMarks As Variant

    ' Считать значения в массив из листа 1
    StudentMarks = rg.Value

    ' Вывести значения массива
    Debug.Print "i", "j", "Значение"
    Dim i As Long, j As Long
    For i = LBound(StudentMarks) To UBound(StudentMarks)
        For j = LBound(StudentMarks, 2) To UBound(StudentMarks, 2)
            Debug.Print i, j, StudentMarks(i, j)
        Next j
    Next i

End Sub

Как видите, первое измерение (доступное через i) массива — это строка, а второе — столбец. Чтобы продемонстрировать это, взглянем на значение 44 в Е4 данных образца. Это значение находится в строке 2 столбца 3 наших данных. Вы можете видеть, что 44 хранится в массиве в StudentMarks (2,3).

Передача массивов в подпрограммы

Массивы удобнее всего передавать в подпрограммы в виде параметра типа Variant .

Обратите внимание, что функции GetResult в качестве параметра передаются массивы. При чём, в первом случае это массив с типом элементов Long , а во втором — String

За счёт того, что внутри функции используются переменные типа Variant , то сначала функция нам возвращает сумму элементов массива arrIntegers , а во втором результат сложения (конкатенации) строк массива arrStrings . Кроме того, параметр parArray не описан как массив ( parArray As Variant ), но мы внутри функции GetResult ведём себя с ним, как с массивом ( For Each Element In parArray )! Это возможно, так как переменные типа Variant умеют определять, что им присваивается и вести себя далее в соответствии с тем, что они содержат. Если переменной parArray присвоили массив (через вызов функции — строки 17 и 18), то она себя будет вести как массив.

Array Methods

There are various inbuilt functions within VBScript which help the developers to handle arrays effectively. All the methods that are used in conjunction with arrays are listed below. Please click on the method name to know about it in detail.

Previous Page
Print Page

Next Page  

Erase

Функция Erase используется для сброса значений массивов фиксированного размера и освобождения памяти динамических массивов.Он ведет себя в зависимости от типа массивов.

Синтаксис

• Фиксированный числовой массив, каждый элемент в массиве сбрасывается до нуля.
• Исправлен строковый массив, каждый элемент в массиве сбрасывается до нулевой длины «».
• Массив объектов, каждый элемент в массиве сбрасывается до специального значения .

пример

Добавьте кнопку и добавьте следующую функцию.

Private Sub Constant_demo_Click()
   Dim NumArray(3)
   NumArray(0) = "VBScript"
   NumArray(1) = 1.05
   NumArray(2) = 25
   NumArray(3) = #23/04/2013#
  
   Dim DynamicArray()
   ReDim DynamicArray(9)   ' Allocate storage space.
  
   Erase NumArray          ' Each element is reinitialized.
   Erase DynamicArray      ' Free memory used by array.
  
   ' All values would be erased.
   msgbox("The value at Zeroth index of NumArray is " & NumArray(0))
   msgbox("The value at First index of NumArray is " & NumArray(1))
   msgbox("The value at Second index of NumArray is " & NumArray(2))
   msgbox("The value at Third index of NumArray is " & NumArray(3))
End Sub

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Функция, которая восстанавливает выделенную память для переменных массива.

Предыдущие статьи

• Функции даты-времени в VBA. Работа с датой и временем — 25/03/2018 17:33
• Работа со строками в VBA: InStr, LCASE, UCase, Left, Right, Mid, LTrim, RTrim, Trim, Len, Replace, Space, StrComp, String, StrReverse — 25/03/2018 15:57
• Работа с циклами в VBA:loop, for each, for next — 17/03/2018 18:40
• Решения, условия, алгоритмы if, then, switch в VBA Excel — 10/03/2018 21:52
• Константы в VBA Excel — Const. Как присвоить значение константам — 10/03/2018 07:53
• Поле ввода в VBA Excel — InputBox. Синтаксис, параметры и описание — 10/03/2018 07:03
• Окно сообщения в VBA Excel — MsgBox — 09/03/2018 06:34
• Простые Макросы Excel, написать шаг за шагом — 10/02/2018 20:07
• Переменные в VBA, значения и типы — 26/06/2017 06:37
• Программирование vba, макросы в Word и Excel — 19/12/2015 16:18

Функции для работы с текстом

Основные функции для работы с текстом в VBA Excel:

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

Set Array Values

Setting array values is very easy.

With a static array, you must define each position of the array, one at a time:

With a Variant Array you can define the entire array with one line (only practical for small arrays):

If you attempt to define a value for an array location that does not exist, you will receive a Subscript Out of Range error:

In the ‘Assign Range to Array’ section Below we’ll show you how to use a loop to quickly assign large numbers of values to arrays.

Get Array Value

You can fetch array values the same way.  In the example below we will write array values to cells:

Похожие записи:

Не получается зарегистрироваться в хамачи. как зарегистрироваться в hamachi, если возникает ошибка Как подключить наушники с микрофоном к компьютеру? Как: как включить или отключить автозаполнение в excel — 2021 Решение проблем с обнаружением принтера canon Как получить почтовый ящик анонимно без регистрации Долго открывается или тормозит excel? ничего страшного