Remkomplekty.ru

IT Новости из мира ПК
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vba excel redim

Vba excel redim

Применяется на уровне процедуры для перераспределения памяти для переменных динамического массива.

Параметры
Preserve
Необязательный. Ключевое слово, используемое для сохранения данных в существующем массиве при изменении значения последней размерности.
имяПеременной
Обязательный. Имя переменной, удовлетворяющее стандартным правилам именования переменных.
индексы
Обязательный. Размерности переменной массива; допускается описание до 60 размерностей. Аргумент индексы использует следующий синтаксис:
Если нижний индекс не задан явно, нижняя граница массива определяется инструкцией Option Base. Если отсутствует инструкция Option Base, нижняя граница массива равняется нулю.
тип
Необязательный. Тип данных переменной; поддерживаются типы Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (в настоящее время не поддерживается), Date, String (для строк переменной длины), String * длина (для строк фиксированной длины), Object, Variant, определяемый пользователем тип или объектный тип. Для каждой описываемой переменной следует использовать отдельное предложение As тип. Для значения типа Variant, содержащего массив, тип описывает тип данных каждого элемента массива, но не заменяет тип Variant на какой-либо другой тип.

Замечания
Инструкция ReDim используется для задания или изменения размера динамического массива, который уже был формально описан с помощью инструкции Private, Public или Dim с пустыми скобками (без индексов размерностей).
Имеется возможность повторно использовать инструкцию ReDim для изменения числа элементов и размерностей массива. Однако не допускается описание массива с одним типом данных и использование инструкции ReDim для последующего изменения типа данных этого массива, если массив не содержится в переменной типа Variant. Тип элементов массива, содержащегося в переменной типа Variant, может быть изменен с помощью предложения As тип, если ранее не было использовано ключевое слово Preserve, запрещающее изменение типа данных.
При использовании ключевого слова Preserve имеется возможность изменить значение только последней размерности массива и не допускается изменение числа размерностей. Например, если массив имеет только одну размерность, имеется возможность изменить эту размерность, так как она является последней и единственной размерностью. Однако, если массив имеет две или более размерности, возможно изменение значения только последней размерности, сохраняя при этом содержимое массива. В следующем примере показывается, как можно увеличить значение последней размерности динамического массива без уничтожения данных, содержащихся в этом массиве.

Кроме того, при использовании ключевого слова Preserve допускается изменение размера массива только за счет изменения верхней границы индекс; попытка изменить нижнюю границу приведет к ошибке.
Если уменьшить размер массива, данные из удаленных элементов будут потеряны. При передаче массива в процедуру по ссылке нельзя изменять размеры массива в процедуре.
При инициализации переменных числовая переменная получает значение 0, строка переменной длины получает значение пустой строки («»), а строка фиксированной длины заполняется нулями. Переменные типа Variant получают при инициализации значение Empty. Каждый элемент переменной типа, определяемого пользователем, при инициализации получает значение, которые он получил бы, если бы являлся одиночной переменной. Переменной со ссылкой на объект перед ее использованием необходимо присвоить существующий объект с помощью инструкции Set. До присвоения объекта описанная объектная переменная имеет специальное значение Nothing, которое указывает, что она не содержит ссылку на какой-либо определенный экземпляр объекта.
Инструкция ReDim действует как инструкция описания, даже если описываемая в ней переменная не существует на уровне модуля или на уровне процедуры. При создании в дальнейшем переменной с тем же именем, возможно, в более широкой области определения, инструкция ReDim будет ссылаться на эту последнюю переменную и не обязательно вызовет ошибку компиляции, даже при действующей инструкции Option Explicit. Во избежание подобных конфликтов не следует использовать инструкцию ReDim как инструкцию описания. Пользуйтесь ей только для изменения размеров массивов.
Для изменения размера массива, содержащегося в переменной типа Variant, нужно явно описать переменную типа Variant перед попыткой изменения размера.

Пример
В данном примере инструкция ReDim используется для выделения и повторного выделения памяти для динамических массивов. Предполагается, что Option Base равняется 1.
Следующая инструкция изменяет размер массива и обнуляет его элементы.
Следующая инструкция изменяет размер массива, но сохраняет существующие элементы.

Vba excel redim

Массивы очень упрощают процесс программирования. Без них практически невозможно написать универсальную программу. Например, представьте себе, что вы манипулируете информацией о квартирах жилого дома. Вы объявляете переменные 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 .

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

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

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

Читать еще:  Vba excel подключение библиотек

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. Конкретно по массивам там огромное количество готовых подпрограмм для работы с ними, исходные коды, снабженные подробнейшими комментариями, продвинутые объяснения для копающих в глубину. Без преувеличения великолепный ресурс!

ШКОЛА ПРОГРАММИРОВАНИЯ

Вы здесь

Сообщение об ошибке

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

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

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

Читать еще:  Excel cells select

Объявление статических массивов в 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 integer
Dim 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=4
Redim MyDynMas (i)

x = i +13
Redim MyDynMas (x)

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

Redim Preserve MyDynMas (x)

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

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

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

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

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

Option Explicit
Option Base 1

Sub StatMas()
Dim Mas(3) As Integer
Dim Matr(3, 4) As Integer
Dim Msg As String
Dim 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) = 7
Matr(2, 1) = 12: Matr(2, 2) = 10: Matr(2, 3) = 2: Matr(2, 4) = 9
Matr(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 i
MsgBox Msg
End Sub

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

Sub DynMasTest()
Dim Msg As String
Dim i, j, col, row As Integer
Dim 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 i
MsgBox Msg
End 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

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

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

Массивы – это множества однотипных элементов, имеющих одно имя и отличающиеся друг от друга индексами. Они могут быть одномерными (линейными), многомерными и динамическими. Массивы в 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:

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

С помощью оператора ReDim невозможно изменить обычный массив, объявленный с заранее заданной размерностью. Попытка переопределить размерность такого массива вызовет ошибку компиляции с сообщением: Array already dimensioned (Массив уже измерен).

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

Обратите внимание!
Переопределить с оператором Preserve можно только последнюю размерность динамического массива. Это недоработка разработчиков, которая сохранилась и в VBA Excel 2016. Без оператора Preserve можно переопределить все размерности.

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

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

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

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

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

Функция Array

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

Dynamic Array with ReDim Preserve VBA

The VBA Tutorials Blog

Introduction to ReDim and ReDim Preserve

You can make a dynamic array while maintaining the values currently stored in the array by using the ReDim Preserve VBA statement. This tutorial will introduce you to dynamic arrays and it will show you how to make dynamic arrays with 2D and multidimensional arrays — which is harder than you’d think!

Example — ReDim Preserve

1D ReDim Preserve VBA Example

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit to supplement this tutorial. Grab it below and you’ll be writing macros so much faster than you are right now.

Читать еще:  Библиотеки vba excel

When you run this macro, the values stored in MyArray(0) and MyArray(1) are retained:


Array Values are Preserved

If you delete the Preserve keyword and try to run the same macro, the values stored in MyArray(0) and MyArray(1) are lost! Check it out:


Same Macro without the Preserve Keyword

Tutorial — ReDim and ReDim Preserve

Dynamic Array Basics

To make an array a dynamic array, you declare it with a regular Dim statement, but you don’t give it a size. All that means is you give your variable name with an empty set of parentheses afterward, like this:

If you were to put a number in the parantheses, it will be a static array.

To give your array a new size, you would use a ReDim statement, instead of another Dim statement.

This will clear out any values you previously stored in your array, MyArray . We demonstrated this with the second message box in our earlier example.

If you want to resize your array while remembering all the elements in the array, you must use the Preserve keyword.

We demonstrated this in the first message box of our 1-D example

ReDim Limitations

There are a several rules you must follow when ReDimming a dynamic array, but I’ll present the 3 major (read: 3 most annoying) limitations of the ReDim statement.

Limitation 1: Can only ReDim Preserve Last Dimension

The ReDim statement is used to resize an array, but it clears ALL the values stored in your array unless you use the optional Preserve keyword with ReDim, like we did in the example above. The primary limitation of the ReDim Preserve statement is it can only change the upper bound of the last dimension of a multidimensional array, including simple 2D arrays in VBA. You don’t have to worry about this if you strictly use 1D arrays.

Running this macro will cause an error because you tried preserving the array while re-dimensionalizing the FIRST dimension; you tried changing it from a 1 to a 2. Unless you get creative (like I’ll show you below), you can only redim the last dimension of an array.


Redim Preserve Multidimensional Array Runtime Error

Limitation 2: Cannot ReDim Static Arrays

Another limitation of ReDim — not exclusive to the Preserve keyword — is that you can’t resize an array that was statically defined. In other words, if you Dim a variable with specific dimensions, you’ll get an “Array Already Dimensioned” error when you try to ReDim it. Take the following example, for instance.

Because you defined your array at the top with a value of 2, you’ll get an error when you try to change the size with the ReDim statement:


Already Dimensioned VBA Redim Static Array

Limitation 3: Cannot use ReDim to Change Data Types

A third limitation of the ReDim statement is that you can’t use it to change your VBA data type. Doing so will create another error.

This example tries to change the data type from an Integer to a Double. When you run this macro, you’ll get a compilation error declaring you can’t change data types of array elements:


ReDim Cannot Change VBA Array Data Type

ReDim Preserve Multidimensional Array

Let’s be real. This is what you came for. If you read ReDim Limitation 1, you know the Preserve keyword only works if you’re redimming the last dimension of an array. You can ReDim the last dimension easily, like we do in this example:

ReDim the Last Dimension of 2D array

To drive the message home, take a look at a real-life example. I’ll show you a macro that stores the array index positions in the 2D array of size (1,3). After it does this, the Array is resized to (1,5) and I’ll prove to you that the values previously stored in the array are retained.

Here’s the proof


ReDim Preserve 2D Array Last Dimension

The elements that were added show up as empty strings. That’s easy. The challenging part is using ReDim Preserve when you want to ReDim the first dimension of your 2D array.

ReDim Any Dimension of 2D array

Because you can only ReDim Preserve the last dimension of an array, you have to get creative if you want to resize the first dimension of an array while preserving the values. I’ll demonstrate one way to do this with a 2D array.

A lot of people like to use the Tranpose function to fix the “Subscript out of range” run-time error message I showed you in ReDim Limitation 1, but I prefer to use a custom user-defined function, called ReDimPreserve . I like this function because it lets me re-dimensionalize either the first dimension, the last dimension, or BOTH dimensions of my 2D array at the same time. That’s pretty awesome!

Note: For this function to work, your array MUST be defined as a Variant. Alternatively, you can modify the TempArray variable in the ReDimPreserve function to declare the variables as whatever data type you want, like a string. You’ll get a pretty warning message if your array isn’t a variant data type.

I can’t take full credit for this solution. I found a similar function a while back and modified it for my needs. Why reinvent the wheel!

This code example has two different procedures. The second one (the ReDimPreserve function) is the one you most likely want to copy and paste in your own macro. The first procedure (the ReDimPreserve2D_AnyDimension subroutine) simply demonstrates how to call the ReDimPreserve function to change the size of an array.

To use the custom ReDimPreserve function, you pass it three arguments:

  1. Your array
  2. The new size you want your first dimension to be
  3. The new size you want your last dimension to be

That’s what I did in my ReDimPreserve2D_AnyDimension subroutine. If you don’t want to change the size of an element, simply pass it the same value of your previous array!

Each element will be preserved in your newly ReDimmed array. Check it out:


ReDim Preserve any dimension of a 2D Array

You can see how I changed the size of my array from (1,3) to (2,4). The new array elements show up as “Empty” in the resized array.

If you’d like to ReDim Preserve a multidimensional array larger than two-dimensions, your best bet is to construct your array in such a way that only the number of elements in the last dimension will need to be preserved. Otherwise, it can get pretty complicated.

We introduced some pretty advanced redim preserve macros in this tutorial. If you want to become a VBA array expert, you’ll need to grab a copy of our comprehensive VBA Arrays Cheat Sheet with over 20 pre-built macros and dozens of tips designed to make it easy for you to handle arrays.

If you haven’t already done so, join our VBA Insiders using the form below. After that, share this article on social media and follow me on Twitter for even more great VBA content.

Oh, and if you have a question, post it in our VBA Q&A community.

The best free VBA training on the web
I see people struggling with Excel every day and I want to help. That’s why I’m giving away my 90-days to Master VBA eCourse and my entire personal macro library for free.

Over 2 million people use our VBA tutorials each year to help automate their work. Are you ready to reclaim your time, too? Grab our VBA Cheat Sheets and you’ll be writing macros like a professional. With over 180 tips and 135 macro examples, they include everything you need to know to become a great VBA programmer.

Ссылка на основную публикацию
Adblock
detector