Vba в excel диапазон в массив
Обновлено: 04.07.2024
В данной статье подробно рассматриваются массивы на языке программирования Excel VBA. Рассматриваются различные вопросы и схемы применения, такие как:
Типы массивов VBA
Объявление массива
Статический массив объявляется следующим образом
Как видите, при объявлении статического массива указывается его размер. Но проблема в том, что вы никогда не можете заранее знать, какой размер вам нужен. Каждый раз при запуске макроса у вас могут быть разные требования к размеру массива
Если вы не используете все элементы массива, ресурсы памяти расходуются в пустую. Если нужно добавить больше элементов можно использовать метод ReDim, но по сути это создает новый статический массив
Динамические массивы не имеют таких проблем. Размер массивы не указывается при объявлении. Поэтому его размер может расти и уменьшаться по мере необходимости
Динамический массив не создается, пока вы не используете оператор ReDim. Преимущество в том, что вы можете подождать, пока не узнаете количество элементов, прежде чем устанавливать размер массива. Со статическим массивом вы должны указать размер заранее.
Чтение из диапазона ячеек в массив
VBA имеет очень эффективный способ чтения из диапазона ячеек в массив и наоборот
Динамический массив, созданный в этом примере, будет двухмерным массивом. Как видите, мы можем прочитать весь диапазон ячеек в массив всего за одну строку.
В следующем примере будут считаны данные из диапазона C3: E6 листа Sheet1 и отображены в окне Immediate Window.
Как сделать выполнение ваших макросов супер скоростным
Если ваши макросы работают очень медленно, этот метод может оказаться очень полезным. Особенно если вы имеете дело с большими объемами данных.
Обновление значений в массивах происходит гораздо быстрее, чем обновление значений в ячейках.
В последнем разделе вы увидели, как мы можем легко читать из группы ячеек в массив и наоборот. Если мы обновляем много значений, мы можем сделать следующее
- Копируем данные из ячеек в массив.
- Изменяем данные в массиве.
- Копируем обновленные данные из массива обратно в ячейки.
Например, следующий код будет намного быстрее, чем код ниже
Назначение из одного множества ячеек в другой также намного быстрее, чем с помощью копирования и вставки
У меня, казалось бы, основная проблема, но я не могу найти ресурсов для ее решения.
Проще говоря, я просто хочу загрузить содержимое диапазона ячеек (все один столбец) в массив.
Я могу добиться этого с помощью
Но по какой-то причине я не могу создать массив при таком выражении:
Мой реальный диапазон намного длиннее (и может отличаться по длине), поэтому я не хочу, чтобы мне приходилось индивидуально перечислять ячейки таким образом. Может ли кто-нибудь сказать мне, как правильно загрузить весь диапазон в массив?
С буквенным кодом:
Вернуть 0, тогда как в первом они возвращают 1.
Просто определите переменную как вариант и сделайте их равными:
Нет необходимости в команде «Массив».
Эта функция возвращает массив независимо от размера диапазона.
Диапазоны вернут массив, если диапазон не составляет только 1 ячейку, а затем вместо этого он вернет одно значение. Эта функция превратит одно значение в массив (на основе 1, такой же, как массив, возвращаемый диапазонами)
Этот ответ улучшает предыдущие ответы, поскольку он вернет массив из диапазона независимо от размера. Это также более эффективно, чем другие ответы, поскольку, если возможно, он вернет массив, сгенерированный диапазоном. Работает с одномерными и многомерными массивами
Функция работает, пытаясь найти верхние границы массива. Если это не удается, тогда это должно быть одно значение, поэтому мы создадим массив и присвоим ему значение.
Если мы сделаем это так:
Новый массив будет двухмерным. С чем не всегда как-то комфортно работать:
Чтобы избавиться от двух измерений, при преобразовании одного столбца в массив мы можем использовать встроенную функцию Excel «Транспонировать». С его помощью данные становятся в одном измерении:
Если у нас есть данные в строке, однократное транспонирование не сработает. Нам нужно использовать функцию транспонирования дважды:
Добавляя к ответу @Vityata, ниже приведена функция, которую я использую для преобразования вектора строки / столбца в одномерный массив:
В дополнение к предлагаемым решениям и в случае, если у вас есть диапазон от 1D до 1D, я предпочитаю обрабатывать его с помощью функции, как показано ниже. Причина проста: если по какой-либо причине ваш диапазон уменьшен до диапазона 1 элемента, насколько я знаю, команда Range (). Value не вернет массив вариантов, а будет только вариант, и вы не сможете назначить вариант в массив вариантов (объявленный ранее).
Мне пришлось преобразовать диапазон переменного размера в двойной массив, и когда диапазон был размером в 1 ячейку, я не смог использовать такую конструкцию, как range (). Value, поэтому я выполняю функцию, как показано ниже.
Чтобы скопировать значения из диапазона ячеек в массив, необходимо объявить переменную универсального типа (As Variant) и присвоить ей значения диапазона ячеек с помощью оператора присваивания (=):
VBA Excel автоматически преобразует объявленную переменную в двумерный массив, соответствующий размерности диапазона ячеек, в нашем случае в массив — a(1 To 3, 1 To 3), и заполняет его значениями. Нумерация измерений массивов, созданных таким образом, начинается с единицы (1).
Можно, в этом случае, объявить сразу динамический массив, чтобы изначально указать, что эта переменная будет массивом. Так как свойством диапазона ячеек по-умолчанию в VBA Excel является значение (Value), его можно в коде явно не указывать, но, при желании, можно и указать. Получится такая конструкция, аналогичная первой:
Копирование значений из массива в диапазон ячеек
Значения в диапазон ячеек добавляются из массива с помощью оператора присваивания (=):
Обратите внимание, что вставить значения в диапазон ячеек можно только из двумерного массива. Размерность такого массива может начинаться с нуля (0). Количество элементов в измерениях массива должно совпадать с количеством строк и столбцов в диапазоне ячеек. Если вам нужно вставить значения в одну строку или в один столбец, укажите размерность единственной строки или единственного столбца как (0) или (1 To 1), если вы хотите использовать нумерацию измерений своего массива с единицы. Например, для записи десяти значений из массива в одну строку можно объявить такой массив — massiv(9, 0), или в один столбец — massiv(0, 9).
Для вставки значений в диапазон ячеек из массива идеально подойдет массив, созданный для копирования в него значений из диапазона. В этом случае, данные с рабочего листа Excel переносятся в массив, обрабатываются и, после обработки, вставляются обратно в ту же или другую таблицу на том же или другом рабочем листе.
Простейшие примеры обмена значениями
Эти примеры составлены так, чтобы вам не пришлось совершать лишних действий, просто скопируйте их в свой модуль любой книги Excel с поддержкой макросов и запустите по-очереди на выполнение.
Пример 1
Заполнение двумерного массива значениями и и их присвоение диапазону ячеек на рабочем листе 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]]])
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. Конкретно по массивам там огромное количество готовых подпрограмм для работы с ними, исходные коды, снабженные подробнейшими комментариями, продвинутые объяснения для копающих в глубину. Без преувеличения великолепный ресурс!
Читайте также: