ГлавнаяОптимизацияVba оптимизация работы с массивом

VBA Excel. Массивы (одномерные, многомерные, динамические). Vba оптимизация работы с массивом


performance - Оптимизация цикла VBA, сохраняя диапазон в виде массива?

Хотя Р.Лерут очень близок, есть несколько вещей, которые необходимо разработать.

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

Это снижение производительности, как правило, справедливо для любых операций Range, и снижение производительности напрямую связано с размером диапазона. Таким образом, работа на 100 ячеек намного быстрее, чем работа на 1 000 000 ячеек.

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

Чтобы создать массив, мы используем тип Variant. Имейте в виду, что Variant может быть любым, поэтому вам нужно быть осторожным. Общее соглашение - использовать Dim Foo as Variant() но тогда любому аргументу, который принимает или возвращает Variant() должен быть предоставлен Variant() а не Variant (незначительная разница, огромное влияние на код). Из-за этого я склонен использовать Dim Foo as Variant.

Затем мы можем назначить значения из диапазона обратно в массив. Foo = Range("A1:B2") функционально эквивалентен Foo = Range("A1:B2").Value, я настоятельно рекомендую полную квалификацию. Таким образом, я не полагаюсь на неявные свойства, насколько могу (.Value - это неявное свойство Range).

Поэтому наш код должен быть:

Dim Foo as Variant Foo = SomeRange.Value

Где Foo - ваша переменная, а SomeRange заменяется вашим диапазоном.

Пока функция Interpolate принимает array, это не должно вызывать никаких проблем. Если функция Interpolate не принимает array, вам может потребоваться найти другое обходное решение (или написать свое собственное).

Для вывода массива нам просто нужно создать диапазон того же размера, что и наш массив. Есть разные способы сделать это. Я предпочитаю этот метод:

SomeRange.Resize(UBound(SomeArray, 1) - LBound(SomeArray, 1) + 1, Ubound(SomeArray, 2) - LBound(SomeArray, 2) + 1)

Все это занимает некоторый диапазон (должна быть одна ячейка) и изменяет размеры, которые варьируются по количеству столбцов в массиве и количеству строк в массиве. Я использую (Ubound - Lbound) + 1 так как для массива, основанного на 0, это приведет к возврату Ubound + 1 и для массива на основе 1 он вернет Ubound. Это делает вещи намного проще, чем создание блоков If для той же цели.

Последнее, что нужно сделать во всем этом, - это то, что ваша переменная Range полностью соответствует требованиям. Обратите внимание, что Range("A1:B2").Value функционально эквивалентно ActiveSheet.Range("A1:B2").Value но опять же, полагаясь на неявные вызовы, быстро вводит ошибки. Выкачайте их как можно больше. Если вам нужен ActiveSheet используйте его. В противном случае создайте переменную Worksheet и укажите эту переменную на правильный лист.

И если вы должны использовать ActiveSheet тогда Dim Foo as Worksheet: Set Foo = ActiveSheet намного лучше, чем просто использовать ActiveSheet (поскольку ActiveSheet будет вообще изменяться, когда вам действительно это не нужно, и тогда все сломается.

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

qaru.site

Ускоряем работу VBA в Excel / Хабр

Предисловие
Так уж сложилось, что на сегодняшний день много кому приходится работать(писать макросы) на VBA в Excel. Некоторые макросы содержат сотни строк кода, которые приходится выполнять каждый день (неделю, месяц, квартал и так далее) и, при этом, они занимают изрядное количество времени. Вроде бы и и процесс автоматизирован и человеческого вмешательства не нужно, но время, занимаемое выполнением макроса, может охватывать десятки минут, а то и несколько часов. Время, как говориться, — деньги и в этом посте я постараюсь значительно ускорить время выполнения Вашего макроса и, возможно, это положительно скажется на ваших делах, а в итоге и деньгах.
Перед началом работы
Перед тем, как перейти прямо к сути, я хотел бы обратить внимание на пост: Несколько советов по работе с VBA в Excel. В частности, в блоке “Ускорение работы макросов” есть полезные примеры кода, которые стоит использовать вместе с моими советами по ускорению работы, для достижения максимального результата.
Ускоряем работу макроса
Итак, к сути… Для того что бы реально ускорить работу VBA в Ecxel нужно понимать, что обращение к ячейке на листе — занимает значительно время. Если Вы хотите записать в ячейку одно значение, то это не займет значительного времени, но если Вам потребуется записать(прочитать, обратиться) к тысячам ячеек, то это потребует гораздо большего времени. Что же делать в таких случаях? На помощь приходят массивы. Массивы хранятся в памяти, а операции в памяти VBA выполняет в сотни, а то и в тысячи раз быстрее. Поэтому, если у Вас в данных тысячи, сотни тысяч значений, то время выполнения макроса может занимать от нескольких минут до нескольких часов, а если эти данные перенести в массив, то выполнение макроса может сократиться до нескольких секунд (минут).

Я наведу пример кода и в комментариях объясню что к чему, так будет яснее. К тому же, могут пригодиться некоторые строки кода, не относящееся прямо к процессу ускорения.

Пример
Предположим, что у нас есть данные на “Лист1” (“Sheet1”). Данные содержаться в 50 колонках (колонки содержат названия) и 10 000 строк. К примеру, нам нужно в последнюю колонку внести значение, которое равно значению во второй колонке, деленное на значение в третьей колонке (начиная со 2-й строки, так как первая содержит заглавие). Потом мы возьмем первые 10 колонок и скопируем их на “Лист2” (“Sheet2”), для дальнейшей обработки (для других потребностей). Пусть пример и банальный, но, как мне кажется, он может отобразить всю суть данного поста.'Для явной инициализации переменных, включаем эту опцию 'Это поможет избежать многих ошибок Option Explicit Sub Test() 'К листам будем обращаться через переменные Dim Sheet1_WS, Sheet2_WS As Worksheet 'Переменная для прохождения срок на листе (в массиве) Dim i As Long 'Массив, в котором будут храниться наши данные Dim R_data As Variant 'Переменные последней строки и колонки Dim FinalRow, FinalColumn As Long 'Можно инициализировать лист не по названию, а по порядковому номеру 'Set Sheet1_WS = Application.ThisWorkbook.Worksheet("Sheet1") Set Sheet1_WS = Application.ThisWorkbook.Sheets(1) Set Sheet2_WS = Application.ThisWorkbook.Sheets(2) 'Поиск последней не пустой строки в первой колонке 'Нужно, что бы данные не были отфильтрованы, иначе последняя строка будет последней строкой в фильтре 'Также в последней строке, в первой колонке, не должно быть пустой ячейки. Конечно, если в этой строке вообще есть данные. Иначе последней строкой будет последняя не пустая ячейка. FinalRow = Sheet1_WS.Cells(Rows.Count, 1).End(xlUp).Row '=10 000 'Поиск последней не пустой колонки в первой строке FinalColumn = Sheet1_WS.Cells(1, Columns.Count).End(xlToLeft).Column '=50 'Присваиваем массиву диапазон данных на Листе 1 R_data = Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, FinalColumn)) For i = 2 To FinalRow 'Выполняем нужные нам операции с данными. 'Проверяем, что бы не было деления на ноль. 'Предполагается, что в колонке 2 и 3 стоят числовые данные 'Иначе потребуется обработка ошибок If R_data(i, 3) <> 0 Then R_data(i, FinalColumn) = R_data(i, 2) / R_data(i, 3) End If Next i 'Копируем данные из массива обратно на Лист1 'Перед этим очищаем данные на листе (если есть форматирование или формулы, то лучше Sheet1_WS.Cells.ClearContents) Sheet1_WS.Cells.Delete Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, FinalColumn)) = R_data 'Копируем данные на Лист2, копируем первые 10 колонок. Sheet2_WS.Range(Sheet2_WS.Cells(1, 1), Sheet2_WS.Cells(FinalRow, 10)) = R_data 'Закрываем книгу и сохраняем её Workbooks(Application.ThisWorkbook.Name).Close SaveChanges:=True End Sub

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

Dim R_new() As Variant ............................................ ' Явно указываем размер массива ReDim R_new(1 To FinalRow, 1 To 50) As Variant ........................................... Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, 50)) = R_new()
Заключение
Большинство операций над данными можно выполнять в массиве, при этом, отображать на лист только результат. Иногда целесообразным бывает показать результат на лист, потом выполнить некоторые действия (например, сортировку) и снова загрузить данные в массив.

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

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

Спасибо за внимание. Удачных разработок.

habr.com

Массивы в VBA - Макросы и программы VBA - Excel - Каталог статей

Содержание:

Зачем нужны массивы

Массивы очень упрощают процесс программирования. Без них практически невозможно написать универсальную программу. Например, представьте себе, что вы манипулируете информацией о квартирах жилого дома. Вы объявляете переменные K1 - для первой квартиры, K2 - для второй и так далее. K1=54 будет означать, что площадь первой квартиры 54 кв.м., К2=72 и т.д. Теперь представим, что нам надо подсчитать общую площадь всех квартир в доме. Очевидно, что это что-то типа Total_S = K1+K2+...+Kn. В одном доме у нас 36 квартир, а в другом 144. Представляете бредовость процесса подобного программирования? Если в первом случае я должен буду использовать 36 отдельных переменных для вычисления общей площади, то для второго дома уже 144. Очень быстро вы придёте к мысли, что вам нужна переменная, состоящая из нумерованных ячеек. Тогда обретают смысл все те операторы циклов, входящие в состав любого языка программирования. Но об этом чуть позже...

▲ вверх

Что такое массив

Массив - переменная, состоящая из некоторого количества однотипных элементов. У массива, как и у любой другой переменной, есть имя. А доступ к конкретному элементу массива осуществляется через указание в скобках после имени его индекса. Например, A(5) означает, что я обращаюсь к элементу с индексом 5 массива, имеющего имя A.

▲ вверх

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

Массивы в VBA и во многих других языках программирования делятся на 2 класса:

▲ вверх

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

Объявление фиксированных массивов

Рекомендация: при объявлении массивов VBA я советую вам давать всем именам префикс "arr". Я сторонник венгерской нотации.

Как мы видим, тут объявлено 2 одномерных массива arrTemp и arrTest. Одномерные массивы в программировании также часто называют векторами. Типом элементов первого массива является Long, второго массива - String. В этом типе синтаксиса в скобках указан максимальный индекс (верхняя граница ) элемента массива. А что насчёт минимального индекса (нижней границы) массива? По-умолчанию минимальным индексом является ноль. В данном случае стандартное поведение интерпретатора языка VBA можно изменить при помощи оператора option base {0|1}. Option base 1 заставляет VBA считать нижней границей массива - единицу, а не ноль.

Таким образом, по-умолчанию массив arrTemp имеет 11 элементов - от 0 до 10. Но, если в начало модуля, в котором этот массив объявляется, вставить оператор Option Base 1, то массив arrTemp будет иметь 10 элементов - от 1 до 10.

Помимо вышеуказанного вы вправе использовать следующий синтаксис, который НЕ зависит от option base {0|1}:

таким образом вы в явном виде указываете и нижнюю, и верхнюю границы. Как видите, нижняя граница совершенно не обязательно должна начинаться с 0 или 1. Более того, индексы границ могут принимать и отрицательные значения, главное чтобы нижняя была меньше верхней.

Помимо одномерных массивов, можно объявлять и массивы с размерностью больше единицы.

arrMulti - двумерный массив, а arrData3 - трёхмерный. Первый содержит 11*31=341 элемент, второй - 2*3*10=60 элементов. Теоретически допускается объявлять до 60 размерностей массива.

Какие типы данных могут стать элементами массива? Тут всё, как в шутке про фамилию еврея, - абсолютно любой тип данных годится на роль элемента массива, включая объектные типы, User Data Type, другие массивы (через тип Variant). Если вы не указываете при объявлении тип данных массива, то предполагается, что этим типом является тип Variant.

▲ вверх

Объявление динамических массивов

Динамические массивы объявляться так:

Однако, использовать их после такого объявления пока ещё нельзя. Необходимо выделить память под массив. Особенность работы с динамическим массивом как раз состоит в том, что программист отвечает за его своевременное расширение (усечение) в памяти. Для этого существует специальный оператор, который имеет следующий синтаксис:

    ReDim [Preserve] varname(subscripts) [As Type]

Например:

После этого оператора, вы можете использовать элементы массива arrOpen с 0-го по 5-й. Всё, что мы говорили про оператор option base и нижнюю границу, верно и для динамических массивов. Предположим, что вы сохранили информацию в элементах 0-5 и у вас поспела новая порция информации для элементов 6-11. Чтобы разместить в данном массиве новые элементы и не потерять старые, вы должны сделать следующее:

то есть мы тут увеличиваем верхнюю границу массива и используем ключевое слово Preserve, чтобы во время этой операции не потерять текущее содержимое arrOpen, так как в противном случае (без слова Preserve) массив будет расширен, а память заполнена нулями. Вы также вправе вообще не декларировать массив оператором Dim, а сделать это впервые через ReDim и там же указать лип элементов. Но, если вы в первом ReDim (или Dim) указали определенный тип элементов, то в последующих операторах ReDim этот тип переопределён быть не может - возникнет ошибка на этапе компиляции проекта.

▲ вверх

Изменение элементов массива

Пора бы нам уже научиться пользоваться нашими массивами - то есть записывать информацию в их элементы и считывать её оттуда. Это довольно просто:

Как и с обычными переменными запись информации в элемент массива происходит через оператор присваивания (=), но указанием индекса элемента массива.

▲ вверх

Чтение элементов массива

▲ вверх

Определение границ массива

В подпрограммах часто приходится иметь дело с массивами, которые переданы вам в качестве параметра (как это сделать показано ниже), поэтому в этом случае очень актуален вопрос определения нижней и верхней границ индекса массива. Для этого в языке предусмотрены 2 функции: LBound и UBound. Первая возвращает нижнюю границу индекса, вторая - верхнюю.

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

Для одномерных массивов параметр dimension можно не указывать. Для многомерных массивов его указывать необходимо. Кстати, это означает, что, если вы точно не знаете, с каким массивом имеете дело, но необходимо узнать его первую размерность, то лучше использовать вариант UBound(arrTemp,1), а не UBound(arrTemp), так как последний вариант вызовет ошибку, если массив окажется многомерным.

Если вы ошибётесь с указанием правильного индекса массива, то возникнет ошибка периода исполнения с кодом 9. Эта же ошибка возникнет, если вы в функции LBound / UBound укажете несуществующую размерность массива (например, 3 для двумерного массива).

▲ вверх

Перебор элементов массива

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

Наиболее удобный оператор цикла для перебора элементов массива - это безусловно For ... Next.

так же есть способ не заботиться об определении нижней и верхней границ, если алгоритм не требует от нас знания текущего индекса массива:

Вы, конечно, можете перебирать массив и в других типах циклов Do ... Loop, но, право, смысла и удобства в этом не много. По крайней мере я не сталкивался, кажется, с ситуациями, когда для перебора массива цикл For не подошёл.

▲ вверх

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

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

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

▲ вверх

Массив с элементами типа массив

Продемонстрируем, как можно хранить в качестве элементов массива другие массивы.

Результат отладочной печати:

▲ вверх

Функция Array

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

    Array( arglist )

Вызов функции без параметров приведёт к возврату массива нулевой длинны. При этом будет наблюдаться интересный эффект LBound вернёт вам 0, а UBound вернёт -1, то есть верхняя граница окажется меньше нижней границы.

▲ вверх

Функция Split

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

    Split(expression[, delimiter[, limit[, compare]]])

Результат выглядит так:

Если вы в качестве разделителя укажете пустую строку, то на выходе получите массив, состоящий из одного элемента. Кстати, split всегда возвращает массив с нулевой нижней границей вне всякой зависимости от наличия option base 1.

▲ вверх

Нюансы работы с динамическими массивами

Неинициализированный массив

У динамического массива есть такое промежуточное состояние, когда он уже объявлен, но ещё не содержит никаких элементов.

То есть у переменной динамического массива есть такое состояние, когда мы не можем воспользоваться вспомогательными функциями LBound / UBound для определения его (массива) статуса. Это особенно надо учитывать, когда вы пишите подпрограммы, работающие с массивами. Прежде чем работать (перебирать) массив необходимо убедиться, что он проинициализирован, в противном случае программа вылетит с ошибкой 9.

Для этого я предлагаю пользоваться функцией подобной нижеописанной IsNotEmptyArray:

▲ вверх

Расширение массива

Как правило, расширять динамический массив приходится в цикле. Возможны 2 стратегии: расширение на 1 элемент, как только в этом есть необходимость (назовём это эластичным расширением), и расширение авансом, когда вы увеличиваете верхнюю границу скачками, скажем сразу на 100 элементов. Реализовав оба варианта, я для себя сделал вывод, что авансовое расширение получилось и компактнее, и работает быстрее, так как операция расширения, вообще говоря, затратна и, чем реже вызывается, тем лучше.

Результат:

Авансовый метод вышел даже компактнее

▲ вверх

Удаление массива

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

▲ вверх

Получение массива на основе диапазона Excel

Самый эффективный по скорости способ получить содержимое диапазона Excel для манипулирования в VBA - это скопировать его в массив с элементами Variant. Делается так:

Даже, если вы передаёте в массив столбец или строку, получаемый массив всегда будет иметь 2 измерения. Измерение 1 отвечает за строки, измерение 2 - за столбцы. То есть ячейка C5 будет в элементе arrTemp(5,3). Нижняя граница таких массивов всегда будет начинаться с единицы.

▲ вверх

Дополнительные источники

В качестве источника дополнительной информации по массивам я могу порекомендовать замечательный, исчерпывающий ресурс Чарльза Пирсона (Charles H. Pearson). Его сайт следует штудировать всем, кто серьёзно осваивает VBA. Конкретно по массивам там огромное количество готовых подпрограмм для работы с ними, исходные коды, снабженные подробнейшими комментариями, продвинутые объяснения для копающих в глубину. Без преувеличения великолепный ресурс!

▲ вверх

Читайте также:

perfect-excel.ru

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

Вход/Регистрация

Главная » Функции VBA » 28 Апрель 2011       Дмитрий       93885 просмотров Статья помогла? Сделай твит, поделись ссылкой с друзьями!

Юридическая информация

Так же с этой статьей читают:

Наши партнеры

Спасибо за сообщение

Ваше сообщение было получено и отправлено администратору.

www.excel-vba.ru

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

Массивы в VBA Excel: одномерные, многомерные и динамические. Объявление и использование массивов. Операторы Public, Dim и ReDim. Функции Array, LBound, UBound.

Массивы - это множества однотипных элементов, имеющих одно имя и отличающиеся друг от друга индексами. Они могут быть одномерными (линейными), многомерными и динамическими. Массивы в VBA Excel, как и другие переменные, объявляются с помощью операторов Dim и Public. Для изменения размерности массивов используется оператор ReDim.

  1. Одномерные массивы
  2. Многомерные массивы
  3. Динамические массивы
  4. Использование массивов
  5. Функции Array, LBound, UBound

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

Объявление одномерных (линейных) массивов в VBA Excel:

Public Massiv1(9) As Integer Dim Massiv2(1 To 9) As String

В первом случае публичный массив содержит 10 элементов от 0 до 9 (нижний индекс по умолчанию - 0, верхний индекс - 9), а во втором случае локальный массив содержит 9 элементов от 1 до 9.

По умолчанию VBA Excel считает в массивах нижним индексом нуль, но, при желании, можно сделать нижним индексом по умолчанию единицу, добавив в самом начале модуля объявление «Option Base 1». Вместо верхнего индекса можно использовать переменную.

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

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

'Массив двухмерный Public Massiv1(3, 6) As Integer 'Массив трехмерный Dim Massiv2(1 To 6, 1 To 8, 1 To 5) As String 'Массив четырехмерный Dim Massiv3(9, 9, 9, 9) As Date

Третий массив состоит из 10000 элементов - 10×10×10×10.

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

Динамические массивы в VBA Excel объявляются без указания размерности:

Public Massiv1() As Integer Dim Massiv2() As String

Такие массивы используются, когда заранее неизвестна размерность, которая определяется в процессе выполнения программы. Когда нужная размерность массива становится известна, она в VBA Excel переопределяется с помощью оператора ReDim:

Public Massiv1() As Integer Dim Massiv2() As String ReDim Massiv1(1 To 20) ReDim Massiv2(3, 5, 4)

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

При переопределении размерности массивов в VBA Excel теряются значения их элементов. Чтобы сохранить значения, используйте оператор Preserve:

Dim Massiv1(1 To 9, 1 To 9) As String ReDim Preserve Massiv1(1 To 25, 1 To 35)

Однако, если размерность массива в VBA Excel уменьшить, то значения, естественно, сохранятся не все.

Использование массивов

Приведу два примера, где не обойтись без массивов.

1. Как известно, функция Split возвращает одномерный массив подстрок, извлеченных из первоначальной строки с разделителями. Эти данные присваиваются заранее объявленному строковому (As String) одномерному динамическому массиву. Размерность устанавливается автоматически в зависимости от количества подстрок.

2. Данные в массивах обрабатываются значительно быстрее, чем в ячейках рабочего листа. Построчную обработку информации в таблице Excel можно наблюдать визуально по мерцаниям экрана, если его обновление (Application.ScreenUpdating) не отключено. Чтобы ускорить работу кода, можно значения из диапазона ячеек предварительно загрузить в динамический массив с помощью оператора присваивания (=). Размерность массива установится автоматически. После обработки данных в массиве кодом VBA полученные результаты выгружаются обратно на рабочий лист Excel. Обратите внимание, что загрузить значения в диапазон ячеек рабочего листа через оператор присваивания (=) можно только из двумерного массива.

Функции Array, LBound, UBound

Функция Array

Функция Array возвращает массив элементов типа Variant из первоначального списка элементов, перечисленных через запятую. Нумерация элементов в массиве начинается с нуля. Обратиться к элементу массива можно, указав в скобках его номер (индекс).

Sub Test1() Dim a() As Variant a = Array("text", 25, "solo", 35.62, "stop") MsgBox a(0) & vbNewLine & a(1) & vbNewLine _ & a(2) & vbNewLine & a(3) & vbNewLine & a(4) End Sub

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

Функция LBound

Функция LBound возвращает значение типа Long, равное наименьшему (нижнему) доступному индексу в указанном измерении массива.Синтаксис:LBound (arrayname[, dimension])

Наименьший индекс по-умолчанию может быть равен 0 или 1 в зависимости от настроек оператора Option Base. Нижняя граница архива, полученного с помощью функции Array, всегда равна 0.

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

Функция UBound

Функция UBound возвращает значение типа Long, равное наибольшему (верхнему) доступному индексу в указанном измерении массива.Синтаксис:UBound( arrayname[, dimension])

Функция UBound используется вместе с функцией LBound для определения размера массива.

Sub Test2() Dim a(-2 To 53) As String MsgBox "Наименьший индекс = " & LBound(a) & _ vbNewLine & "Наибольший индекс = " & UBound(a) End Sub

Скопируйте код в модуль VBA Excel и запустите его на выполнение. Информационное сообщение MsgBox покажет значения наименьшего и наибольшего индекса переменной массива a.

vremya-ne-zhdet.ru

Примеры использования массивов в VBA

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

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

Пример 1. Создание (объявление) одномерного массива выполняется, так:

Dim Arr1(10) As Integer Dim Arr2(5 To 10) As String Dim Arr3() As Long

В данном примере объявляются: одномерный массив Arr1, содержащий ячейки с 0-й до 10-й типа Integer, массив Arr2, содержащий ячейки с 5-й до 10-й типа String и динамический массив Arr3.

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

Пример 2. Инициализация динамического массива и изменение его размеров

Dim Arr3() As Long ReDim Preserve Arr3(10) ReDim Preserve Arr3(20)

В данном примере мы сначала с помощью ReDim задали размер динамического массива в 11 элементов (c 0-го по 10-й), а затем снова увеличили размер до 21-го элемента. Кроме того, использовали ключевое слово Preserve - означающее, что нужно сохранить уже имеющиеся элементы с их значениями (без этого ключевого слова массив обнуляется).

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

Пример 3. Объявление многомерного массива

Dim Arr4(10, 10) As Integer Dim Arr5(5 To 10, 15 To 20, 30) As String

Arr4 - двумерных массив 11х11 элементов, а массив Arr5 - трехмерный.

Пример 4. Создание массива массивов

В следующем примере массив Arr2 будет содержать элементы другого массива Arr1

Dim Arr1 As Variant Dim Arr2(10) As Variant Arr1 = Array(10, 20, 30) Arr2(0) = Arr1 For i = LBound(Arr2(0)) To UBound(Arr2(0)) MsgBox Arr2(0)(i) ' Выведет последовательно 10, 20 и 30 Next i

Определение нижней и верхней границы массива

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

Пример 5. Определение границ массива

Dim Arr1(2 To 15) As Integer MsgBox LBound(Arr1) ' Выведет: 2 MsgBox UBound(Arr1) ' Выведет: 15 Dim Arr2() As Integer ReDim Arr2(8) MsgBox LBound(Arr2) ' Выведет: 0 MsgBox UBound(Arr2) ' Выведет: 8

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

Dim Arr(1 To 10, 5 To 20) As Integer MsgBox LBound(Arr, 2) ' Выведет: 5 MsgBox UBound(Arr, 2) ' Выведет: 20

Задание нижней границы по-умолчанию

Иногда бывает очень не удобно, что VBA начинает нумерацию элементов массивов с нуля (0), это часто может привести к путанице и усложнению кода программы. Для решения этой проблемы есть специальный оператор Option Base, аргумент которого может быть 0 или 1. Указав значение 1, индексация массивов будет начинаться с 1, а не с 0.

Пример 6. Указание нижней границы по-умолчанию.

Option Base 1 Sub Test() Dim Arr1(10) As Integer MsgBox LBound(Arr1) End Sub

В данном примере я намеренно использовал процедуру, чтобы показать, что Option Base нужно применять не внутри процедур и функций, а в разделе "Declarations". В результате выполнения процедуры Test будет отображено сообщение с индексом нижней границы массива, т.е. "1".

Примечание: Оператор Option Base так же влияет на функцию Array и не влияет на функцию Split (будут рассмотрены ниже), что означает, что при задании "Option Base 1", функция Array вернет массив с индексацией с 1-цы, а функция Split вернет массив с индексацией с 0.

Запись данных в массивы

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

Пример 7. Запись данных в массив в цикле.

Dim Arr(10) As Integer For i = 0 To 10 Arr(i) = i * 2 Next i

Пример 8. Запись заранее известных данных с помощью Array

Dim Arr() Arr = Array("красный", "зеленый", "синий") MsgBox Arr(2)

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

Пример 9. Получение массива из строки с разделителями

Dim Arr() As String Arr = Split("красный;зеленый;синий", ";") MsgBox Arr(2)

Обход элементов массива

Обычно, массивы используются для хранения большого кол-ва данных, а не 1-2 значений, поэтому чтобы получить все эелементы и использовать их для чего-то, обычно используют циклы. Наиболее удобны в этом плане циклы For и For Each.

Пример 10. Обход элементов массива циклом For.

Dim Sum As Integer Dim Arr() Arr = Array(10, 20, 30) For i = 0 To 2 Sum = Sum + Arr(i) Next i MsgBox Sum

Пример 11. Обход элементов массива циклом For Each.

Dim Sum As Integer Dim Val As Variant Dim Arr() Arr = Array(10, 20, 30) For Each Val In Arr Sum = Sum + Val Next MsgBox Sum

Иногда, бывает необходимость работы с массивом внутри других типов циклов, но получение значение элемента, всё-равно в них будет таким же, как и в цикле For, т.е. через индекс элемента.

www.codernotes.ru

Статические и динамические массивы в VBA

Что такое массив? 

Массив это набор элементов одинакового типа, которые имеют общее имя. А обращение к этим элементам происходит по их индексу. Проще говоря, это множество переменных собранных в одну группу по определенному типу. Для более ясного понимания возьмем, например, жемчужные бусы, одни черного цвета, другие белого. Так вот, бусы черного и белого цвета это массивы жемчужин, сгруппированные по цвету, черный и белый, а жемчужины являются элементами этих массивов.Массивы бывают одномерными (линейными), те же вышеупомянутые бусы и многомерными - двумерные(матрицы, 2D), трехмерные и т.д. Статические - кол-во элементов массива объявляется на этапе разработки и не изменяется в процессе выполнения программы, и динамические - число элементов и размерность изменяется в процессе работы программы.

Объявление статических массивов в VBA

Массив в VB объявляется как обычная переменная, после ключевого слова Dim или Public, с той лишь разницей, что после имени переменной (перед ключевым словом "As") должны быть скобки.Dim Mas () as integer

Статические массивы объявляются с указанием верхней и нижней границы:Dim Mas (2 to 50) as integer

Объявить массив также можно указав только кол-во элементов:Dim Mas (50) as integer

В этом случае нижняя граница будет установлена по умолчанию 0. Если Вы хотите что бы первым индексом во всех массивах была 1, то Вам необходимо в начале модуля (перед первой процедурой) указать следующее:Option Base 1

Для объявления многомерных массивов используется запись следующего вида:Dim Matrix (1 to 10, 1 to 10) as integerDim V3D (10, 10,10) as integer 'трехмерный массивDim V4D (10, 10, 25, 5) as integer 'четырехмерный массиви т.д.

Объявление динамических массивов в VBA

Динамический массив - не имеет предопределенного  кол-ва элементов и определяется без указания в скобках границ:Dim MyDynMas () as integer

Но динамический массив можно будет использовать только после программного определения границ, после ключевого слова ReDim:Dim MyDynMas () as integer'некоторый код или расчет границi=4Redim MyDynMas (i)

x = i +13Redim MyDynMas (x)

после переопределения кол-ва элементов массива, все элементы обнуляются. Для того чтобы сохранить значения элементов массива, после ReDim необходимо добавить ключевое слово Preserve:

Redim Preserve MyDynMas (x)

Так же возможно переопределить размерность массивов, например с одномерного на двух-трех-мерный:Dim MyDynMas () as integer'некоторый код или расчет границi=4Redim MyDynMas (i)

x = i +13Redim MyDynMas (i, x)

но учтите, что при переопределении размерности массива, использование ключевого слова Preserve для сохранения значений элементов - невозможно!

Рассмотрим примеры по работе со статическими и динамическими массивами.

Пример 1:Дан одномерный массив с тремя элементами и матрица 3х4 (3 строки, 4 столбца). Необходимо умножить каждый элемент одномерного массива со всеми элементами строки матрицы, т.е. 1-й элемент умножаем со всеми элементами первой строки матрицы, 2-й со второй строкой и т.д. Результат вывести в виде матрицы в сообщении.

Option ExplicitOption Base 1

Sub StatMas()Dim Mas(3) As IntegerDim Matr(3, 4) As IntegerDim Msg As StringDim i, j As Integer

'заполняем масивыMas(1) = 2: Mas(2) = 4: Mas(3) = 6

Matr(1, 1) = 4: Matr(1, 2) = 5: Matr(1, 3) = 1: Matr(1, 4) = 7Matr(2, 1) = 12: Matr(2, 2) = 10: Matr(2, 3) = 2: Matr(2, 4) = 9Matr(3, 1) = 24: Matr(3, 2) = 11: Matr(3, 3) = 6: Matr(3, 4) = 3

Msg = "Результат:" & Chr(13)For i = 1 To 3  For j = 1 To 4     Matr(i, j) = Matr(i, j) * Mas(i)     'добавляем результат в строку сообщения, резделитель TAB (chr(9))     Msg = Msg & CStr(Matr(i, j)) & Chr(9)  Next j  'добавляем перенос на новую строку chr(13)  Msg = Msg & Chr(13)Next iMsgBox MsgEnd Sub

Пример 2:Двухмерный массив не известной длины заполняется следующим образом Mas(i,j) = i * j. Размеры массива вводятся пользователем с помощью формы InputBox. Результат вывести в сообщении.

Sub DynMasTest()Dim Msg As StringDim i, j, col, row As IntegerDim DynMas() As Long

Msg = "Результат:" & Chr(13)

col = InputBox("Введите кол-во столбцов массива", _                "Пример создания динамического массива", 2)row = InputBox("Введите кол-во строк массива", _                "Пример создания динамического массива", 2)                ReDim Preserve DynMas(col, row)

For i = 1 To col  For j = 1 To row    DynMas(i, j) = i * j    Msg = Msg & CStr(DynMas(i, j)) & Chr(9)  Next j  Msg = Msg & Chr(13)Next iMsgBox MsgEnd Sub

И напоследок, для того чтобы узнать (получить) длину массива воспользуйтесь функцией  UBound(). Запись типа UBound(Mas) вернет кол-во элементов одномерного массива. Если же необходимо узнать длину многомерного массива, то функция примет вид UBound(Matr, 2) – вернет кол-во элементов второго измерения, UBound(Matr, 3) – третьего и т.д. Запись же UBound(Matr, 1)  равносильна UBound(Mas).

Пример:

Sub GetLengthMas()Dim Matr(3, 4, 5) As Integer

MsgBox "Размер массива: " & UBound(Matr, 1) & "x" & UBound(Matr, 2) & "x" & UBound(Matr, 3) _       & Chr(13) & "Всего элементов: " & UBound(Matr, 1) * UBound(Matr, 2) * UBound(Matr, 3)End Sub

 

Прикрепленный файл: work_mass_vba.zip

 

 

www.programm-school.ru

Prostoy-Site | Все права защищены © 2018 | Карта сайта