Передача массива в функцию vba excel
Объявление одномерных (линейных) статических массивов в VBA Excel:
В первом случае публичный массив содержит 10 элементов от 0 до 9 (нижний индекс по умолчанию — 0, верхний индекс — 9), а во втором случае локальный массив содержит 9 элементов от 1 до 9.
По умолчанию VBA Excel считает в массивах нижним индексом нуль, но, при желании, можно сделать нижним индексом по умолчанию единицу, добавив в самом начале модуля объявление «Option Base 1». Вместо верхнего индекса можно использовать переменную.
Многомерные массивы
Объявление многомерных статических массивов в VBA Excel аналогично объявлению одномерных массивов, но с добавлением размерностей дополнительных измерений через запятую:
Третий массив состоит из 10000 элементов — 10×10×10×10.
Динамические массивы
Динамические массивы в VBA Excel, в отличие от статических, объявляются без указания размерности:
Такие массивы используются, когда заранее неизвестна размерность, которая определяется в процессе выполнения программы. Когда нужная размерность массива становится известна, она в VBA Excel переопределяется с помощью оператора ReDim:
Переопределять размерность динамических массивов в процессе работы программы можно неоднократно, как по количеству измерений, так и по количеству элементов в измерении.
При переопределении размерности динамических массивов в VBA Excel теряются значения их элементов. Чтобы сохранить значения, используйте оператор Preserve:
Обратите внимание!Переопределить с оператором Preserve можно только последнюю размерность динамического массива. Это недоработка разработчиков, которая сохранилась и в VBA Excel 2016. Без оператора Preserve можно переопределить все размерности.
Максимальный размер
Размер массива – это произведение длин всех его измерений. Он представляет собой общее количество элементов, содержащихся в данный момент в массиве.
По информации с сайта разработчиков, максимальный размер массивов зависит от операционной системы и доступного объема памяти. Использование массивов, размер которых превышает объем доступной оперативной памяти компьютера, приводит к снижению скорости, поскольку системе необходимо выполнять запись данных и чтение с диска.
Использование массивов
Приведу два примера, где не обойтись без массивов.
1. Как известно, функция Split возвращает одномерный массив подстрок, извлеченных из первоначальной строки с разделителями. Эти данные присваиваются заранее объявленному строковому (As String) одномерному динамическому массиву. Размерность устанавливается автоматически в зависимости от количества подстрок.
2. Данные в массивах обрабатываются значительно быстрее, чем в ячейках рабочего листа. Построчную обработку информации в таблице Excel можно наблюдать визуально по мерцаниям экрана, если его обновление (Application.ScreenUpdating) не отключено. Чтобы ускорить работу кода, можно значения из диапазона ячеек предварительно загрузить в динамический массив с помощью оператора присваивания (=). Размерность массива установится автоматически. После обработки данных в массиве кодом VBA полученные результаты выгружаются обратно на рабочий лист Excel. Обратите внимание, что загрузить значения в диапазон ячеек рабочего листа через оператор присваивания (=) можно только из двумерного массива.
Функции Array, LBound, UBound
Функция Array
Функция Array возвращает массив элементов типа Variant из первоначального списка элементов, перечисленных через запятую. Нумерация элементов в массиве начинается с нуля. Обратиться к элементу массива можно, указав в скобках его номер (индекс).
Создание функций с бесконечным количеством аргументов
В предыдущих примерах мы создавали функции, в которых в качестве аргументов выступало одно значение. Но есть функции, у которых в качестве переменной выступает массив значений. Примерами таких функций встроенных в Excel служат функции СУММ, ВПР, МАКС, СРЗНАЧ и так далее. Давайте теперь создадим свою похожую функцию.
Далее приведен код функции, которая суммирует все четные значения.
В первой строке данной функции можно увидеть, что переменной Диапазон мы присвоили тип Range (Диапазон As Range). Таким образом, в качестве аргумента функции мы можем использовать диапазон ячеек. Применим эту функцию на рабочем листе Excel.
Как видно из рисунка, мы не перечисляли каждое значение, а использовали диапазон ячеек A2:A11.
Теперь немного разберем код. Мы использовали цикл For Each - Next. Его задача пройтись по каждой ячейке нашего диапазона. Предварительно мы объявили еще одну переменную r типа Range. Она будет хранить значение каждой ячейки.
Далее идет проверка значения ячейки и если оно кратно 2, то мы суммируем результат во временную переменную S.
В конце, после цикла, мы присваиваем функции СуммаЧетных значение просуммированных элементов S.
Создание функций с неопределенным количеством аргументов
Предыдущая функция имеет недостаток - если вы попытаетесь использовать в качестве переменной несмежный диапазон ячеек, то получите ошибку. Иногда требуется более универсальная функция, так как на старте мы можем не знать количество аргументов, которые будут использоваться. Например, всем известная функция СУММ может принимать неограниченное количество аргументов =СУММ( A1 ; B1:B5 ;1;2;3), функция все рано вернет верный результат.
Вы можете создать свою аналогичную функцию в VBA, указав к последнему (или единственному) аргументу ключевое слово ParamArray.
ParamArray - данный модификатор применяется только к последнему аргументу. Аргумент с данным модификатором всегда должен иметь тип данных Variant и всегда является необязательным, ключевое слов Optional не указывается дополнительно
Теперь давайте создадим функцию, которая может принимать произвольное количество аргументов/ А в качестве результата возвращать всю туже сумму нечетных значений.
Данная функция может принимать любое количество аргументов, однако аргументы не могут быть многомерные, это означает, что мы не можем указывать диапазоны, а должны перечислить все значения или все ячейки. Как это показано на рисунке далее.
Что же делать, если необходимо использовать различные диапазоны ячеек, а не перечислять каждый элемент отдельно. Рассмотрим следующий код функции, который реализует эту возможность.
У нас добавился еще один цикл For Each - Next. Т.е. сначала мы проходим по всем аргументам (диапазонам ячеек), а после проходим по всем ячейкам этих диапазонов.
Этот код тоже далек от идеала: если мы будем использовать вместо диапазонов значения, то получим ошибку. Поэтому если необходимо создать идеальную функцию, например как СУММ (она может обработать любые типы аргументов), то придется потрудится и дополнительно проверять тип аргументов. Но об это уже поговорим в другой раз. Перейдем к созданию еще одного типа функций.
Создание функции, возвращающих массив значений
Надеюсь вы знаете, как использовать формулы массивов в Excel. Эти функции вводятся на рабочий лист Excel с помощью сочетания клавиш Ctrl + Shift + Enter. Сейчас мы создадим функцию, которая возвратит массив значений. Например, выведет нам список всех месяцев.
Если мы введем функцию в одну ячейку, то увидим, что результатом будет только Январь. Чтобы понять как работает функция необходимо выделить 12 ячеек по горизонтали, ввести =СписокМесяцев() и нажать сочетание клавиш Ctrl + Shift + Enter.
Если необходимо вывести список месяцев по вертикали, то можно дополнительно использовать функцию =ТРАНСП(СписокМесяцев()).
Возможно покажется, что функции, возвращающие массив достаточно бесполезны. Однако это не так, да они реже используются на рабочем листе Excel, но я надеюсь что они будут незаменимой вещаю в ваших будущих программах на VBA.
Как правило, процедуру нельзя вызывать с дополнительными аргументами, чем указано в объявлении процедуры. Если требуется неопределенное число аргументов, можно объявить массив параметров, который позволяет процедуре принимать массив значений для параметра. При определении процедуры вам не нужно было узнать число элементов в массиве параметров. Размер массива определяется отдельно при каждом вызове процедуры.
Объявление ParamArray
Используйте ключевое слово ParamArray для обозначения массива параметров в списке параметров. Применяются следующие правила.
Процедура может определять только один массив параметров и должен быть последним параметром в определении процедуры.
Массив параметров должен передаваться по значению. Рекомендуется явно включать в определение процедуры ключевое слово ByVal .
Массив параметров является автоматически необязательным. Его значением по умолчанию является пустой одномерный массив типа элемента массива параметров.
Должны быть необходимы все параметры, предшествующие массиву параметров. Массив параметров должен быть единственным необязательным параметром.
Вызов метода ParamArray
При вызове процедуры, определяющей массив параметров, можно указать аргумент одним из следующих способов:
Нет, то есть можно опустить аргумент ParamArray . В этом случае в процедуру передается пустой массив. При явном передаче ключевого слова Nothing в процедуру передается пустой массив, который может привести к NullReferenceException, если вызываемая процедура не проверяет это условие.
Список произвольного числа аргументов, разделенных запятыми. Тип данных каждого аргумента должен быть неявно преобразован в ParamArray тип элемента.
Массив с тем же типом элемента, что и у типа элемента массива параметров.
Во всех случаях код в процедуре обрабатывает массив параметров как одномерный массив с элементами того же типа данных, что и ParamArray тип данных.
Всякий раз при работе с массивом, который может быть неограниченным большим, существует риск перегрузки внутренней емкости приложения. Если вы принимаете массив параметров, следует проверить размер массива, которому был передан вызывающий код. Выполните соответствующие действия, если оно слишком велико для вашего приложения. Дополнительные сведения см. в статье Arrays (C++/CLI and C++/CX) (Массивы (C++/CLI и C++/CX)).
Пример
В следующем примере определяется и вызывается функция calcSum . ParamArray Модификатор для параметра args позволяет функции принимать переменное число аргументов.
В следующем примере определяется процедура с массивом параметров и выводятся значения всех элементов массива, переданных в массив параметров.
Массивы очень упрощают процесс программирования. Без них практически невозможно написать универсальную программу. Например, представьте себе, что вы манипулируете информацией о квартирах жилого дома. Вы объявляете переменные 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]]])
expression - строковое выражение, содержащая подстроки и разделители. Обязательный параметр.
delimiter - текстовый разделитель. Необязательный параметр. Если опущен, то предполагается, что разделителем является символ пробела.
limit - количество подстрок, которое необходимо вернуть. -1 или отсутствие параметра означает, что вернуть надо все подстроки.
compare - константа, указывающая тип сравнения для символов разделителей. 1 - текстовое сравнение (без учёта регистра), 0 - бинарное сравнение (с учётом регистра).
Результат выглядит так:
Если вы в качестве разделителя укажете пустую строку, то на выходе получите массив, состоящий из одного элемента. Кстати, 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. Конкретно по массивам там огромное количество готовых подпрограмм для работы с ними, исходные коды, снабженные подробнейшими комментариями, продвинутые объяснения для копающих в глубину. Без преувеличения великолепный ресурс!
Читайте также: