Vba динамические массивы: Все про массивы в VBA читать в пошаговом руководстве по Excel

Содержание

VBA — SBP-Program

Язык VBA
Объявление переменных в VBA
Константы в VBA
Массивы в VBA
Многомерные массивы в VBA
Динамические массивы в VBA
Процедуры в VBA
Функции в VBA
Первая программа на VBA
Использование функций в VBA

]]>

Язык VBA

Язык VBA достаточно простой. Ниже приводится краткий курс языка VBA. Аббревиатура VBA означает Visual Basic for Applications, т.е. Visual Basic для приложений.

Наверх

Объявление переменных в VBA

Пример объявления локальной переменной в VBA:

Dim intVar As Integer

здесь объявлена локальная, т.е. видимая в пределах функции, переменная типа Integer.

Пример объявления глобальной переменной в VBA:

Public intVar As Integer

такая перемнная доступна из своего и других модулей.

Пример объявления переменной, видимой только в своём модуле:

Private intVar As Integer

Пример объявления статической переменной в VBA:

Dim Static intVar As Integer

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

Особенность объявления переменных в VBA состоит в том, что переменную можно и не объявлять, но использовать. Тип таких переменных — Variant. Но рекомендуется объявлять переменные, указывать их тип, а не использовать тип Variant.

Наверх

Константы в VBA

Константы в VBA объявляют с помощью Const:

Const intValue As Integer = 1234

Константам значение присваивается при их объявлении.

Наверх

Массивы в VBA

Пример объявления массива в VBA:

Dim intArray(0 To 5) As Integer

объявлен массив типа Integer, индексы элементов изменяются от нуля до пяти.

Наверх

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

Пример двумерного массива в VBA:

Dim intArray(0 To 5, 0 To 2) As Integer

Наверх

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

Динамические массивы могут изменять свой размер. Объявление динамического массива:

Dim intArray() As Integer

здесь размер массива не указан. Но перед использованием такого массива следует объявить его размер:

ReDim intArray(5)

Можно и изменить его размер после предыдущего определения:

ReDim intArray(15)

Наверх

Процедуры в VBA

Пример объявления процедуры в VBA:

Public Sub aProcedure(ByVal intA As Integer, ByRef lngSqr As Long)
&nbsp&nbsp&nbsp&nbsplngSqr = intA * intA
End Sub

В этом примере объявлена процедура, видимая из всех модулей, на это указывает слово Public, принимающая два параметра: intA по значению, на это указывает ключевое слово ByVal, и параметр lngSqr по ссылке, на это указывает ключевое слово ByRef. Ключевое слово Sub говорит, что объявлена процедура.

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

Наверх

Функции в VBA

Функции отличаются от процедур тем, что возвращают значение. Пример функции в VBA:

Public Function lngFunc(ByVal intA As Integer) As Long
&nbsp&nbsp&nbsp&nbsplngFunc = intA * intA
End Sub

Для задания функции используем ключевое слово Function, после списка аргументов указываем тип возвращаемого значения. Строка

lngFunc = intA * intA

возвращает значение из данной функции, слева — имя функции, в данном случае оно играет роль return.

Наверх

Первая программа на VBA

Откройте Excel, в строке меню окна Excel выберете Сервис — > Макрос — > Редактор Visual Basic. Откроется окно редактора, оно называется Microsoft Visual Basic. В окне Project – VBA Project кликаем дважды по Лист1. Вводим код:

Public Sub aMessage()
&nbsp&nbsp&nbsp&nbspMsgBox («It is my first VBA program.»)
End Sub

Далее Debug — > Compile VBA Project. Если нет ошибок, то запускаем нашу программу: Run — > Run Sub/UserForm.

Видим такую картину:

Если у вас так получилось, то это значит, что самое трудное позади и очень скоро вы освоите VBA окончательно.

Наверх

Использование функций в VBA

Сделаем программу с использованием функций. Напомню, что функция — это процедура, которая возвращает значение. Пусть наша программа возводит целое число в квадрат. Код на VBA:

Public Function lngSqr(ByVal intVal As Integer) As Long
&nbsp&nbsp&nbsp&nbsp lngSqr = intVal * intVal
End Sub

Public Sub calculation()
&nbsp&nbsp&nbsp&nbsp lngSqr = intVal * intVal
&nbsp&nbsp&nbsp&nbsp Dim intVar As Integer
&nbsp&nbsp&nbsp&nbsp Dim lngResult As Long
&nbsp&nbsp&nbsp&nbsp intVar = 5
&nbsp&nbsp&nbsp&nbsp lngResult = lngSqr(intVar)
&nbsp&nbsp&nbsp&nbsp MsgBox («The result is » & lngResult)
End Sub

Получаем:

Программирование

]]>

Наверх

НОУ ИНТУИТ | Лекция | Массивы, циклы, принятие решений

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

07-05-Динамические массивы.docm — пример к п. 7.7.

Когда вы используете массив, не всегда известно заранее, сколько элементов он будет иметь. В такой ситуации можно объявить массив, который содержит заведомо больше элементов, чем может понадобиться, но это приведет к нерациональному использованию системных ресурсов. Что же делать? Динамические массивы — вот достойный ответ на этот вопрос.

Решим задачу. Программа просит пользователя ввести количество сотрудников, которое сохраняет в переменной ArraySize, а потом создает массив, одна из размерностей которого равняется ArraySize.

Чтобы воспользоваться динамическим массивом, сначала нужно объявить пустой массив, например, командой Dim MyArray(), а потом задать размерность массива командой ReDim (листинге 7.7.)

Dim MyArray()
	ArraySize = InputBox("Введите количество сотрудников")
	ReDim MyArray(1 To ArraySize, 1 To 2)

Листинг
7.7.
Работа с динамическим массивом

intuit.ru/2010/edi»>В итоге, если на вопрос программы о количестве сотрудников мы ввели число 15, будет создан двумерный массив размерностью 15х2.

Если в программе возникла ситуация, когда последней размерности объявленного и заполненного массива не хватает для хранения данных, вы можете увеличить его командой ReDim с ключевым словом Preserve. Благодаря ему данные, внесенные ранее в массив, будут сохранены. Например, для добавления двух дополнительных столбцов в динамический массив из листинга 7.7. нужно использовать такую команду:

ReDim Preserve MyArray(1 To ArraySize, 1 To 4)

7.8. Дополнительные команды работы с массивами

07-06-Дополнительные команды.docm — пример к п. 7.8.

Для работы с массивами вы можете использовать еще некоторые команды.

Array (Список аргументов)- позволяет быстро заполнять массив. Например, в листинге 7.8. массив MyArray заполняется числами 1, 2, 6, 9 и 19, после чего первый элемент массива выводится в окне сообщения.

Dim MyArray 
MyArray = Array(1, 2, 6, 9, 19) 
MsgBox MyArray(0)

Листинг
7.8.
Работа с оператором Array

IsArray (Имя переменной) — возвращает True если переменная является массивом. Например, в листинге 7.9. мы объявляем две переменные — одну из них как массив, вторую — как обычную переменную. Далее мы используем оператор IsArray для проверки того, является ли переменная массивом. После чего программа выводит соответствующее сообщение. Здесь мы использовали оператор сравнения If, подробности о котором мы рассмотрим ниже.

Dim MyArray(10)
Dim MyArr
If IsArray(MyArray) Then _
MsgBox ("Переменная MyArray - массив") _
Else MsgBox ("Переменная MyArray - не массив")
If IsArray(MyArr) Then _
MsgBox ("Переменная MyArr - массив") _
Else MsgBox ("Переменна MyArr - не массив")

Листинг
7. 9.
Работа с функцией IsArray

LBound (Имя Массива, Размерность) — возвращает нижнюю границу для указанной размерности массива.

UBound (Имя Массива, Размерность) — возвращает верхнюю границу для указанной размерности массива.

Рассмотрим пример. Создадим динамический двумерный массив, размерности которого заданы с помощью генератора случайных чисел. После этого с помощью операторов LBound и UBound узнаем размерности массива и выведем их в окнах сообщений. Далее — используем двойной цикл для заполнения массива случайными числами (листинг 7.10.)

Dim MyArray()
    ReDim MyArray(Int(Rnd * 5 + 5), Int(Rnd * 5 + 5))
    MsgBox ("Двумерный массив MyArray:" + Chr(13) + _
    "Первая размерность:" + _
    Str(LBound(MyArray, 1)) + " -" + _
    Str(UBound(MyArray, 1)) + Chr(13) + _
    "Вторая размерность:" + _
    Str(LBound(MyArray, 2)) + " -" + _
    Str(UBound(MyArray, 2)))
    For i = LBound(MyArray, 1) To UBound(MyArray, 1)
        For j = LBound(MyArray, 2) To UBound(MyArray, 2)
            MyArray(i, j) = Int(Rnd * 100)
        Next j
    Next i

Листинг
7. 10.
Работа с функциями LBound и UBound

В нашем случае команда LBound для обеих размерностей массива возвращает 0 так как по умолчанию нумерация элементов массива начинается с 0. А вот Ubound возвращает границу каждой из размерностей, которая установлена случайным образом с помощью оператора ReDim. На рис. 7.1. вы можете видеть окно сообщения c информацией о границах массива.

Рис.
7.1.
Сообщение о размерностях массива

Erase Имя_массива — очистить массив. Элементы обычных массивов, содержащих числовые данные, обнуляются. Если мы применим команду Erase к массиву строк — каждый его элемент будет хранить строку нулевой длины («»). Применяя команду Erase к динамическому массиву, мы очищаем память, выделенную этому массиву командой ReDim. Причем, для повторного использования динамического массива, придется снова устанавливать его размерности. Если команда Erase применяется к объектному массиву, в каждый его элемент записывается специальное значение Nothing, которое означает пустую ссылку на объект.

Теперь, когда мы обсудили циклы For-Next и работу с массивами, поговорим о других типах циклов.

7.9. Цикл с предусловием

07-07-Цикл с предусловием.docm — пример к п. 7.9.

Как вы уже знаете, цикл с предусловием While — Wend выполняется до тех пор, пока условие, указанное на входе, верно.

В листинге 7.11. представлено решение такой задачи: выводить на экран случайные числа от 0 до 20 до тех пор, пока не будет выведено число больше 10.

А = 1 
    While А < 10 
        А = Int(Rnd() * 20)
        MsgBox А 
    Wend

Листинг
7.11.
Вывод случайных чисел в цикле While — Wend

intuit.ru/2010/edi»>Сначала мы приравниваем переменной А число 1. Проверка при входе в цикл находит, что А меньше 10 и запускает первый проход. Переменной А приравнивается целое случайное число, это число выводится в окне сообщения. Дальше следует новая проверка — если A все еще меньше 10 — все повторяется снова. Если A больше или равно 10 — число не выводится, после чего выполнение цикла прекращается.

Теперь рассмотрим цикл с постусловием.

7.10. Цикл с постусловием

07-08-Цикл с постусловием.docm — пример к п. 7.10.

Цикл Do-Loop While выполняется до тех пор, пока значение на выходе из цикла не верно. Подобные циклы используют, например, для проверки правильности ввода каких-либо данных пользователем. Если данные введены неверно — цикл выполняется снова.

Аналогично действует цикл Do-Loop Until — он будет выполняться до тех пор, пока условие цикла неверно (то есть равно False ).

В листинге 7.12. вы можете найти пример такого цикла. Здесь пользователю предлагается ввести какое-нибудь число. Если введено не число (то есть функция IsNumeric возвратит False ), программа выведет окно ввода снова.

Dim var_A
    Do
        var_A = InputBox("Введите число")
    Loop Until IsNumeric(var_A)

Листинг
7.12.
Проверка ввода в цикле с постусловием

7.11. Принятие решений: If-Then-Else

07-09-If-Then-Else.docm — пример к п. 7.11.

Программы на VBA умеют принимать решения — для этого существуют операторы условного перехода. Они объединены в конструкцию If — Then — Else.

В этой конструкции могут быть использованы следующие операторы сравнения (табл. 7.4.)

Таблица
7. 4.
Операторы сравнения
Оператор	Описание
=	Равно
<>	Не равно
>	Больше
<	Меньше
>=	Больше или равно
<=	Меньше или равно
Like	Сравнение строки с шаблоном
Is	Сравнение объектов

intuit.ru/2010/edi»>Напишем простую программу (листинг 7.13.), которая спрашивает у пользователя его возраст. Если введенный возраст меньше 18 — программа должна вывести надпись «Вам менее 18 лет», если больше или равен 18 — надпись «Вам 18 или больше».

a = InputBox("Введите ваш возраст")
If a < 18 Then MsgBox ("Вам меньше 18")
If a >= 18 Then MsgBox ("Вам 18 или больше")

Листинг
7.13.
Оператор If — Then

Здесь представлен оператор в простейшем виде — проверка условия и выполнение однострочной команды. А что если нужно выполнить не одну команду, а несколько? Для этого служит команда End If (листинг 7.14.)

a = InputBox("Введите ваш возраст")
If a < 18 Then
    MsgBox ("Вам меньше 18")
    MsgBox ("Вам не следует смотреть этот фильм")
End If
If a >= 18 Then MsgBox ("Добро пожаловать")

Листинг
7. 14.
Использование команды End If

Здесь программа выводит два сообщения, если пользователю меньше 18 лет.

Оператор может иметь вид If — Then — Else (листинг 7.15). Благодаря ему два оператора If — Then из листинга 7.13. можно объединить в один.

a = InputBox("Введите ваш возраст")
If a < 18 Then MsgBox ("Вам меньше 18") _
Else MsgBox ("Вам больше 18")

Листинг
7.15.
Использование команды Else

Применение команды Else так же позволяет исполнять многострочные команды. Например, вот так — листинг 7.16.

a = InputBox("Введите ваш возраст")
If a < 18 Then
    MsgBox ("Вам меньше 18")
    MsgBox ("Вам не следует смотреть этот фильм")
Else
    MsgBox ("Вам больше 18")
    MsgBox ("Добро пожаловать")
End If

Листинг
7. 16.
Выполнение многострочных команд

В операторе If возможно использование сложных условий. Например, вы просите пользователя ввести имя и пароль. Если они соответствуют данным, хранящимся в системе, программа выводит приветствие, иначе — сообщает о том, что пользователь ввел неправильные сведения. Очевидно, что нам нужно проверить два условия, причем важно, чтобы и то и другое выполнялось. Для этого можно воспользоваться логическим оператором And, который трактуется как «И». Конструкция с And выглядит так — листинг 7.17.

UserName = InputBox("Введите имя пользователя")
UserPass = InputBox("Введите ваш пароль")
If UserName = "Александр" And UserPass = "12345" Then
    MsgBox ("Добро пожаловать в систему")
Else
    MsgBox ("Неверное имя пользователя или пароль")
End If

Листинг
7.17.
Использование And

intuit.ru/2010/edi»>Словесно вышеприведенную конструкцию можно описать так: » Если имя равно Александр и пароль равен 12345…».

Логический оператор Or (переводится как «Или») позволяет принять положительное решение, если выполняется хотя бы одно из условий. Например, нам нужно узнать имя пользователя, и если оно соответствует одному из имен, зарегистрированных в системе, вывести приветствие, иначе — вывести информацию об отсутствии в системе такого пользователя — листинг. 7.18.

UserName = InputBox("Введите имя пользователя")
If UserName = "Александр" Or UserName = "Сергей" Or _
UserName = "Николай" Then
    MsgBox ("Добро пожаловать в систему, " & UserName)
Else
    MsgBox ("В системе нет такого пользователя!")
End If

Листинг
7.18.
Использование Or

And и Or можно использовать вместе. Оператор Not (Не) позволяет задавать условия с отрицанием. Например, вы можете пропустить в систему всех пользователей кроме пользователя с именем «Владимир».

Для проверки дополнительных условий можно использовать оператор If — Then — Else.

Выше, в табл. 7.4., есть пара необычных операторов — Like для сравнения строк с шаблоном, и Is — для сравнения объектных переменных.

Заполнение динамических массивов VBA — Stack Overflow

Подход на основе классов с BetterArray
В 2013 году Иоаннис заметил, что вы можете создать класс для управления изменением размера массива по частям. Реализация со многими другими функциями — сортировка, нарезка, фильтрация и преобразование, и это лишь некоторые из них — теперь можно найти здесь: https://senipah.github.io/VBA-Better-Array/ (я не подключен к проект). На странице свойства емкости объясняется внутренний процесс удвоения. В общем:

Класс BetterArray, хранящийся в одном файле .cls, можно легко
импортируется в любой новый или существующий проект VBA. Написано чисто
VBA и не использует никаких внешних зависимостей. Соответственно должно работать
в любом приложении, которое поддерживает сценарии VBA как в Windows, так и в
Операционные системы Mac.

Другими словами, вы просто загружаете модуль класса (один файл .cls, ссылка здесь), перетаскиваете его в свой проект ^* , и оттуда он доступен как коллекция или любой другой объект для создания. Здесь я использую его для получения содержимого текущего каталога:

 Sub DemoBetterArray()
    Dim ba As BetterArray, tempDir As String, basicArray As Variant
    Установите ba = новый BetterArray
    временный каталог = каталог ("")
    Делать, пока tempDir <> ""
        ba.Push tempDir 'Я не устанавливаю границ, но добавляю элемент
        временный каталог = каталог()
    Петля
    basicArray = ba.Items 'Просмотр результатов в традиционном массиве
    
Конец сабвуфера

В этом примере вы можете сделать то же самое с ArrayList , обычно доступным в Windows через . NET (но, по-видимому, устаревшим в .NET). См. резюме. В любом случае, между этими объектами, которые вы можете исследовать, есть существенные различия. Для сложения 1 000 000 целых чисел я обнаружил, что BetterArray работает в несколько раз быстрее, чем ArrayList.

Совет по использованию документации BetterArray: хотя страница с примерами в настоящее время пуста, страницы с методами (перечисленные здесь) содержат много полезных примеров того, что может делать класс, помимо эффективного расширения.

Расширение посредством обработки ошибок
Еще одна возможность, которая еще не обсуждалась, — использование обработки ошибок. Подход продемонстрирован Брюсом МакКинни в Hardcore Visual Basic, 2-е издание (1997). Функция ниже использует эту идею.

 Sub VectorFill (исходный вектор как вариант, индекс как длинный, значение как вариант)
'Заполняет массив 1d по указанному индексу и при необходимости увеличивает UBound (удваивая его).
'Обрежьте ненужное пространство отдельно с помощью ReDim Preserve. 
    Const resizeMultiplier As Integer = 2
    
    'С этим оператором ошибка выхода за границы вызовет изменение размера
    При ошибке GoTo ErrorHandling
    исходный вектор (индекс) = значение
    Выйти из подпрограммы
    
'ErrorHandling используется для изменения размера массива по частям
Обработка ошибок:
    newBound = (UBound(sourceVector) + 1) * resizeMultiplier '+1 помогает с начальным 0
    ReDim Preserve исходный вектор (newBound)
    Возобновить 'Новое место доступно, поэтому вернитесь и повторите попытку
Конец сабвуфера

Приведенную выше функцию можно использовать следующим образом:

 Sub DemoVectorFill()
    Dim dirContents() как вариант, я как длинный
    ReDim dirContents(0)
    dirContent = Каталог("")
    Делать, пока dirContent <> ""
        VectorFill dirContents, i, dirContent
        dirContent = Каталог
        я = я + 1
    Петля
    ReDim Preserve dirContents(i - 1)
Конец сабвуфера

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

^{^* Если вы попытаетесь скопировать и вставить код BetterArray в модуль класса, это не совсем сработает. Модули класса имеют некоторый код, который скрыт в редакторе VBA и не может быть скопирован с помощью копирования и вставки. Есть два варианта: перетащить файл .cls на панель «Проект» или использовать File —> Import File .}

Excel VBA — Как добавить формулу динамического массива

Задай вопрос

спросил
2 года, 8 месяцев назад

Изменено
2 года, 7 месяцев назад

Просмотрено
6к раз

Я добавляю формулу на рабочий лист через VBA, которая должна быть:

 = UNIQUE (IF (TableA [ColumnA] = A1, TableA [ColumnB], ""))

Это использует новую функцию SPILL в Excel, чтобы дать мне список значений столбца B, где связанное значение в столбце A соответствует значению в ячейке A. Я также применяю функцию UNIQUE для удаления любых множественных пробелов («») Результаты.

Это отлично работает, если я вручную ввожу формулу в Excel, однако при использовании VBA для добавления формулы Excel добавляет символы @ в формулу и заставляет ее отображать #ЗНАЧ!.

Строка VBA, используемая для добавления формулы:

 =Cells(x,y).Formula = "=UNIQUE(IF(TableA[ColumnA]=A1,TableA[ColumnB],""""))"

Результат в Excel:

 =@UNIQUE(IF(TableA[@[ColumnA]]=A1,TableA[ColumnB],""))

Что происходит и что я пропустил?

Заранее спасибо!

excel
vba
excel-формула
динамические массивы

Хороший вопрос, я его просмотрел…

_{Короче говоря:}

Используйте =Cells(x,y).Formula2 вместо

4 =Cells(x,y). Формула

_{Объяснение:}

@ , которое отображается, называется неявным оператором пересечения. Из документов MS:

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

Но почему он появляется в вашем новом Excel O365? Ну, Range.Formula использует IIE (неявное пересечение), таким образом добавляя @ , чтобы в основном отменить функциональность вашего динамического массива. UNIQUE — это новая функция динамического массива. Таким образом, чтобы записать это в коде, вы должны использовать свойство Range.Formula2 (или Range.Formula2R1C1 , если вы используете нотацию R1C1 ). Эти свойства используют AE (оценка массива) и теперь используются по умолчанию.

Вот информативный документ от MS по этому вопросу, который объясняет разницу между Formula и Formula2 более подробно.