Передача массива в функцию vba excel
Как известно, VBA обрабатывает информацию в массивах значительно быстрее, чем в ячейках рабочего листа Excel. Поэтому, при работе с большими объемами данных, удобнее использовать массивы, чем наблюдать во время выполнения кода за мерцанием изображения на экране или просто смотреть в неизменную картинку, если обновление экрана отключено (Application.ScreenUpdating = False). Здесь обмен значениями между массивом и диапазоном ячеек будет вполне уместен.
Копирование значений из диапазона ячеек в массив
Чтобы скопировать значения из диапазона ячеек в массив, необходимо объявить переменную универсального типа (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 переносятся в массив, обрабатываются и, после обработки, вставляются обратно в ту же или другую таблицу на том же или другом рабочем листе.
Обмен значениями между двумя диапазонами
Обмен значениями можно осуществить в VBA Excel не только между массивом и диапазоном, но и между двумя диапазонами одинаковой размерности:
Как правило, процедуру нельзя вызывать с дополнительными аргументами, чем указано в объявлении процедуры. Если требуется неопределенное число аргументов, можно объявить массив параметров, который позволяет процедуре принимать массив значений для параметра. При определении процедуры вам не нужно было узнать число элементов в массиве параметров. Размер массива определяется отдельно при каждом вызове процедуры.
Объявление ParamArray
Используйте ключевое слово ParamArray для обозначения массива параметров в списке параметров. Применяются следующие правила.
Процедура может определять только один массив параметров и должен быть последним параметром в определении процедуры.
Массив параметров должен передаваться по значению. Рекомендуется явно включать в определение процедуры ключевое слово ByVal .
Массив параметров является автоматически необязательным. Его значением по умолчанию является пустой одномерный массив типа элемента массива параметров.
Должны быть необходимы все параметры, предшествующие массиву параметров. Массив параметров должен быть единственным необязательным параметром.
Вызов метода ParamArray
При вызове процедуры, определяющей массив параметров, можно указать аргумент одним из следующих способов:
Нет, то есть можно опустить аргумент ParamArray . В этом случае в процедуру передается пустой массив. При явном передаче ключевого слова Nothing в процедуру передается пустой массив, который может привести к NullReferenceException, если вызываемая процедура не проверяет это условие.
Список произвольного числа аргументов, разделенных запятыми. Тип данных каждого аргумента должен быть неявно преобразован в ParamArray тип элемента.
Массив с тем же типом элемента, что и у типа элемента массива параметров.
Во всех случаях код в процедуре обрабатывает массив параметров как одномерный массив с элементами того же типа данных, что ParamArray и тип данных.
Всякий раз при работе с массивом, который может быть неограниченным большим, существует риск перегрузки внутренней емкости приложения. Если вы принимаете массив параметров, следует проверить размер массива, которому был передан вызывающий код. Выполните соответствующие действия, если оно слишком велико для вашего приложения. Дополнительные сведения см. в статье Arrays (C++/CLI and C++/CX) (Массивы (C++/CLI и C++/CX)).
Пример
В следующем примере определяется и вызывается функция calcSum . ParamArray Модификатор для параметра args позволяет функции принимать переменное число аргументов.
В следующем примере определяется процедура с массивом параметров и выводятся значения всех элементов массива, переданных в массив параметров.
Можно объявить массив для работы с набором значений одного типа данных. Массив представляет собой одиночную переменную с множеством ячеек для хранения значений, тогда как типовая переменная имеет только одну ячейку хранилища, в которой может храниться только одно значение. Следует обращаться к массиву как одному целому, когда необходимо сослаться на все значения, хранящиеся в нем, или можно обращаться к отдельным элементам.
Например, чтобы хранить ежедневные расходы по каждому дню года, можно объявить одну переменную массива с 365 элементами, а не объявлять 365 переменных. Каждый элемент массива содержит одно значение. В следующем заявлении объявляется переменная массива с 365 элементами. По умолчанию массив индексируется, начиная с нуля, так что верхняя граница массива равна 364, а не 365.
Чтобы установить значение отдельного элемента, указывается индекс этого элемента. В следующем примере каждому элементу массива присваивается начальное значение, равное 20.
Изменение нижней границы
Используйте заявление Option Base в верхней части модуля, чтобы изменить индекс по умолчанию первого элемента с 0 на 1. В следующем примере в заявлении Option Base изменяется индекс для первого элемента, а в заявлении Dim объявляется переменная массива с 365 элементами.
Можно также в явном виде установить нижнюю границу массива, используя выражение To, как показано в следующем примере.
Хранение значений Variant в массивах
Существует два способа создания массивов значений Variant . Один способ — объявление массива с типом данных Variant, как показано в следующем примере:
Другой способ — присвоение массива, возвращаемого функцией Array, переменной Variant, как показано в следующем примере.
Независимо от способа создания массива элементы в массиве значений Variant идентифицируют по индексу. Например, в любом из предыдущих примеров может быть добавлен следующий оператор.
Использование многомерных массивов
В Visual Basic допускается объявлять массивы с 60 размерностями, максимум. Например, следующий оператор объявляет 2-мерный массив 5 на 10.
Если рассматривать массив как матрицу, первый аргумент представляет строки, а второй аргумент представляет столбцы.
Использование вложенных для. Следующие утверждения для обработки многомерных массивов. Следующая процедура позволяет заполнить двумерный массив значениями Single.
См. также
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Вы можете скопировать массив VBA в массив того же типа, используя оператор = . Массивы должны быть одного типа, иначе код будет генерировать ошибку компиляции «Can not assign to array».
Исходный массив может быть фиксированным или динамическим, но целевой массив должен быть динамическим. Попытка скопировать в фиксированный массив вызовет ошибку компиляции «Can not assign to array». Любые существующие данные в принимающем массиве теряются, а его границы и размеры изменяются так же, как исходный массив.
Как только копия сделана, два массива являются отдельными в памяти, то есть две переменные не являются ссылками на одни и те же базовые данные, поэтому изменения, внесенные в один массив, не отображаются в другом.
Копирование массивов объектов
С массивами объектов копируются ссылки на эти объекты, а не сами объекты. Если изменение в объекте в одном массиве будет также изменено в другом массиве - они оба ссылаются на один и тот же объект. Однако установка элемента в другой объект в одном массиве не приведет его к этому объекту в другом массиве.
Варианты, содержащие массив
Вы также можете скопировать массив в переменную варианта и из нее. При копировании из варианта он должен содержать массив того же типа, что и принимающий массив, иначе он будет вызывать ошибку «Ошибка несоответствия типа».
Возвращаемые массивы из функций
Функция в нормальном модуле (но не модуле класса) может возвращать массив, помещая () после типа данных.
Результат функции можно затем поместить в динамический массив того же типа или варианта. К элементам также можно получить доступ, используя второй набор скобок, однако это вызовет функцию каждый раз, поэтому лучше сохранить результаты в новом массиве, если вы планируете использовать их более одного раза
Обратите внимание, что возвращаемое на самом деле является копией массива внутри функции, а не ссылкой. Поэтому, если функция возвращает содержимое массива Static, его данные не могут быть изменены процедурой вызова.
Вывод массива через выходной аргумент
Обычно это хорошая практика кодирования для аргументов процедуры как входных данных и для вывода через возвращаемое значение. Однако ограничения VBA иногда требуют, чтобы процедура выводила данные через аргумент ByRef .
Вывод в фиксированный массив
Вывод массива из метода класса
Выходной аргумент также может использоваться для вывода массива из метода / процедуры в модуле класса
Передача массивов на прохождение
Массивы могут передаваться в процедуры, помещая () после имени переменной массива.
Массивы должны передаваться по ссылке. Если не указан какой-либо передающий механизм, например myFunction(arr()) , то VBA будет считать ByRef по умолчанию, однако хорошая практика кодирования делает его явным. Попытка передать массив по значению, например myFunction(ByVal arr()) приведет к ошибке компиляции «Array argument должно быть ByRef» (или ошибке компиляции «Синтаксическая ошибка», если Auto Syntax Check не проверена в параметрах VBE) ,
Передача по ссылке означает, что любые изменения в массиве будут сохранены в процессе вызова.
Если вы хотите избежать изменения исходного массива, будьте осторожны, чтобы написать функцию, чтобы она не меняла никаких элементов.
Альтернативно создайте рабочую копию массива и работайте с копией.
report this ad
Массивы – это множества однотипных элементов, имеющих одно имя и отличающиеся друг от друга индексами. Они могут быть одномерными (линейными), многомерными и динамическими. Массивы в VBA Excel, как и другие переменные, объявляются с помощью операторов Dim и Public. Для изменения размерности динамических массивов используется оператор ReDim. Массивы с заранее объявленной размерностью называют статическими.
Одномерные массивы
Объявление одномерных (линейных) статических массивов в 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 из первоначального списка элементов, перечисленных через запятую. Нумерация элементов в массиве начинается с нуля. Обратиться к элементу массива можно, указав в скобках его номер (индекс).
Читайте также: