Vba excel работа с массивами

VBA-Урок 13.2. Использование массивов (Arrays) (Продолжение)

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

Сохранение данных в двумерный массив:

‘Декларирование Dim array_example (10, 2) ’11 x 3 «определенный» массив ‘Сохранение данных в массиве For i = 0 To 10 array_example(i, 0) = Range(«A» & i + 2) array_example(i, 1) = Range(«B» & i + 2) array_example(i, 2) = Range(«C» & i + 2) Next

Далее есть несколько примеров работы с этими значениями:

MsgBox array_example (0, 0) ‘=> возвращает: 03.11.2026 MsgBox array_example (0, 1) ‘=> возвращает: 24 MsgBox array_example (9, 2) ‘=> возвращает: NO MsgBox array_example (10, 2) ‘=> возвращает: YES

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

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

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

last_row = Range(«A1»).End(xlDown).Row

Excel не принимает переменные в декларации.

Вместо этого, задекларируем динамический массив (используя пустые скобки), затем определим его размер используя Redim:

Dim array_example () ReDim array_example (last_row — 2, 2)

Используя следующую процедуру, вы можете сохранить все строки вашего набора данных (таблицы) в нашем массиве:

Dim array_example() ReDim array_example(last_row — 2, 2)

Ubound

В предыдущем примере, последний номер в нашем массиве был last_row — 2:

For i = 0 To last_row — 2

Другой способ, чтобы определить последний номер в нашем массиве, мог бы быть через использование Ubound :

For i = 0 To UBound (array_example)

Эта функция возвращает наибольший номер в массиве для выбранного измерения (первое измерение есть по-умолчанию).

Далее есть несколько примеров, которые прояснят это подробнее:

Sub example() Dim array_example(10, 2) MsgBox UBound (array_example) ‘=> возвращает: 10 MsgBox UBound (array_example, 1) ‘=> возвращает: 10 MsgBox UBound (array_example, 2) ‘=> возвращает : 2 End Sub

Сохранение данных в диапазоне элементов массива

Есть возможным заполнить массив значениями из диапазона ячеек на рабочем листе даже без использования цикла:

‘Декларирование Dim array_example (10, 2) ’11 x 3 «определенный» массив ‘Сохранение данных в массиве For i = 0 To 10 array_example(i, 0) = Range(«A» & i + 2) array_example(i, 1) = Range(«B» & i + 2) array_example(i, 2) = Range(«C» & i + 2) Next

Предыдущий код может быть эффективно заменен этим:

‘Декларирование Dim array_example () ‘Сохранение данных в массиве array_example = Range(«A2:C12»).Value

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

Если вы сохраните данные в вашем массиве таким способом, первый номер будет 1, а не 0, что может привести к недоразумению …

Далее в процессе увеличения кода, если вы решите сохранить только данные, которые соответствуют определенным критериям поиска в массиве (или проводить совсем другую операции), вам придется полностью переписать код, используя другую функцию цикла …

Но этот второй метод является весьма полезным, если вам нужно сохранить все содержимое большого набора данных, потому что это быстрее, чем циклом (экономит примерно 0,2 секунд на каждые 15 000 записей).

Массив (Array)

Но если вам нужно создать массив, который имеет «фиксированное» содержание.

VBA Excel. Массивы (одномерные, многомерные, динамические)

Одним из решений могло бы быть прописать значение строчка за строчкой:

Dim en(5) en(0) = «IF» en(1) = «VLOOKUP» en(2) = «SUM» en(3) = «COUNT» en(4) = «ISNUMBER» en(5) = «MID»

К счастью, вы можете упростить этот код, используя массив ( Array ):

en = Array («IF», «VLOOKUP», «SUM», «COUNT», «ISNUMBER», «MID»)

Вот демонстрация использования функции Replace (это поможет вам понять следующий пример):

Sub replace_example() Dim var_translate As String ‘Текстовая строчка для этого примера var_translate = «Hello World!» ‘Замена «World» на «you» в текстовой строке var_translate = Replace (var_translate, «World», «you») ‘Строка после замены MsgBox var_translate ‘=> возвращает «Hello you!» End Sub

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

Sub translate () ‘Упрощенный пример перевода формул с английского на французский Dim var_translate As String ‘Текстовая строчка для этого примера var_translate = «Formula to translate: SUM (IF (ISNUMBER (A1: E1), A1: E1,0))» ‘Два набора значений en = Array («IF», «VLOOKUP», «SUM», «COUNT», «ISNUMBER», «MID») fr = Array («SI», «RECHERCHEV», «SOMME», «NB», «ESTNUM», «STXT») ‘Замена» SI «на» IF «,» RECHERVEV «на» VLOOKUP «и т.д. For i = 0 To UBound (en) var_translate = Replace (var_translate, en (i), fr (i)) Next ‘Строка после замены MsgBox var_translate ‘=> возвращает «Formula to translate : SOMME(SI(ESTNUM(A1:E1),A1:E1,0))» End Sub

Разделение (Split)

Функция Split позволяет нам превратить символьную строку в массив.

Чтобы превратить строку в массив, сделайте следующее:

variable = «IF/VLOOKUP/SUM/COUNT/ISNUMBER/MID»

Используйте функцию Split и укажите разделитель:

en = Split (variable, «/»)

Массив en вернет следующие значения:

MsgBox en(0) ‘=> возвращает: IF MsgBox en (1) ‘=> возвращает: VLOOKUP MsgBox en (2) ‘=> возвращает: SUM MsgBox en (3) ‘=> возвращает: COUNT MsgBox en (4) ‘=> возвращает: ISNUMBER MsgBox en (5) ‘=> возвращает: MID

Следующие 3 массивы также вернут те же значения:

en = Array(«IF», «VLOOKUP», «SUM», «COUNT», «ISNUMBER», «MID») en = Split(«IF,VLOOKUP,SUM,COUNT,ISNUMBER,MID», «,») en = Split(«IF VLOOKUP SUM COUNT ISNUMBER MID», » «)

Следующий пример возвращает третье значение в строке:

MsgBox Split(«IF,VLOOKUP,SUM,COUNT,ISNUMBER,MID», «,»)(2) ‘=> возвращает : SUM

Обратной к Split является функция Join .

Эта функция собирает значение массива в строку.

MsgBox Join (Array (1, 2, 3, 4, 5), «») ‘=> возвращает: 12345

Статьи по теме:

  • VBA-Урок 13.1. Использование массивов (Arrays)
  • VBA-Урок 13.3. Использование массивов (Arrays) (Упражнения)
  • Массивы в Visual Basic

    Массивы VBA (ч.3)

    Функции LBound, UBound

    Функции LBound, UBound возвращают нижнее и верхнее граничные значения индексов статического или динамического массива.

    Синтаксис:

    LBound (array_Name [, dimension])
    UBound (array_Name [, dimension])

    array_Name — имя массива
    dimension — целое число (необязательный аргумент). Определяет измерение массива, для которого надо получить верхний или нижний предел. При отсутствии dimension возвращается предел для первого измерения массива.

    Нижеприведенный листинг демонстрирует использование функций LBound, UBound:

    Очистка и удаление массивов при помощи Erase

    Оператор Erase позволяет выполнять очистку для статических массивов и удаление — для динамических.

    Когда элементы массива заполнены, данные в массиве остаются до тех пор, пока пользователь не присвоит новые значения элементам массива или пока VBA не освободится от массива. Зачастую бывает, что в дальнейших вычислениях динамический массив ни при каких обстоятельствах использоваться не будет, поэтому нецелесообразно «держать» его в памяти компьютера, т.к. это может сказаться на скорости работы программы. Или же может понадобиться очистить все значения в статическом массиве, устанавливая числовые значения на 0, а строковые — на пустые строки. Это можно осуществить при помощи вложенных циклов — как было показано на предыдущем уроке. Но, можно сделать гораздо проще:

    Erase My_Array

    Данный оператор обнуляет (если массив статический) или очищает (если массив динамический) массив My_Array.

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

    Поведение оператора Erase для статических массивов зависит от конкретного типа элементов массива (см. таблицу):

    Тип статического массива Действие оператора Erase
    Любой числовой тип Устанавливает элементы массива на 0
    Любой строковый тип Устанавливает элементы массива на строку нулевой длины, а для строк фиксированной длины — как все символы пробела
    Тип Variant Устанавливает элементы массива на Empty
    Тип Object Устанавливает элементы массива на Nothing
    Любой пользовательский тип Устанавливает каждую переменную в пользовательском типе индивидуально: численные — на 0; строковые — на строки нулевой длины; Variant — Empty; Object — Nothing

    В начало страницы

    В начало страницы

    VBA-Урок 13.2. Использование массивов (Arrays) (Продолжение)

    Я работаю над добавлением Excel VBA, который обменивается объектами с COM-сервером, примерно так:

    Получение массивов работает хорошо: если массив содержит объекты, он работает так, как ожидалось, если массив пуст, то и все работает так, как ожидалось.

    Отправка массивов работает только с непустыми массивами, потому что я не могу и в том, что в массив попадают как VBA, так и COM-сервер: сбой в VBA и кто знает, что сбой сервера.

    Похоже, что оператор оставляет массив undefined/поврежден, а не пуст.

    EDIT (пояснение к комментарию ниже):

    Выполнение этого кода происходит сбой, поскольку он не может вычислить :

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

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

    arraysvba

    задан stenci 22 янв. '14 в 20:37

    источникподелиться

    Добавить комментарий

    Закрыть меню