Vba excel. работа с текстом (функции)

Erase

Функция Erase используется для сброса значений массивов фиксированного размера и освобождения памяти динамических массивов.Он ведет себя в зависимости от типа массивов.

Синтаксис

• Фиксированный числовой массив, каждый элемент в массиве сбрасывается до нуля.
• Исправлен строковый массив, каждый элемент в массиве сбрасывается до нулевой длины «».
• Массив объектов, каждый элемент в массиве сбрасывается до специального значения .

пример

Добавьте кнопку и добавьте следующую функцию.

Private Sub Constant_demo_Click()
   Dim NumArray(3)
   NumArray(0) = "VBScript"
   NumArray(1) = 1.05
   NumArray(2) = 25
   NumArray(3) = #23/04/2013#
  
   Dim DynamicArray()
   ReDim DynamicArray(9)   ' Allocate storage space.
  
   Erase NumArray          ' Each element is reinitialized.
   Erase DynamicArray      ' Free memory used by array.
  
   ' All values would be erased.
   msgbox("The value at Zeroth index of NumArray is " & NumArray(0))
   msgbox("The value at First index of NumArray is " & NumArray(1))
   msgbox("The value at Second index of NumArray is " & NumArray(2))
   msgbox("The value at Third index of NumArray is " & NumArray(3))
End Sub

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

Функция, которая восстанавливает выделенную память для переменных массива.

Предыдущие статьи

• Функции даты-времени в VBA. Работа с датой и временем — 25/03/2018 17:33
• Работа со строками в VBA: InStr, LCASE, UCase, Left, Right, Mid, LTrim, RTrim, Trim, Len, Replace, Space, StrComp, String, StrReverse — 25/03/2018 15:57
• Работа с циклами в VBA:loop, for each, for next — 17/03/2018 18:40
• Решения, условия, алгоритмы if, then, switch в VBA Excel — 10/03/2018 21:52
• Константы в VBA Excel — Const. Как присвоить значение константам — 10/03/2018 07:53
• Поле ввода в VBA Excel — InputBox. Синтаксис, параметры и описание — 10/03/2018 07:03
• Окно сообщения в VBA Excel — MsgBox — 09/03/2018 06:34
• Простые Макросы Excel, написать шаг за шагом — 10/02/2018 20:07
• Переменные в VBA, значения и типы — 26/06/2017 06:37
• Программирование vba, макросы в Word и Excel — 19/12/2015 16:18

Примеры

Вывод прямых парных кавычек

Прямые парные кавычки в VBA Excel являются спецсимволами и вывести их, заключив в самих себя или в одинарные кавычки (апострофы), невозможно. Для этого подойдет функция Chr:

Смотрите интересное решение по выводу прямых кавычек с помощью прямых кавычек в первом комментарии.

Отображение слов наоборот

Преобразование слова «налим» в «Милан»:

или одной строкой:

Преобразование слова «лето» в «отель»:

или одной строкой:

Печатная машинка

Следующий код VBA Excel в замедленном режиме посимвольно печатает указанную строку на пользовательской форме, имитируя печатную машинку.

Для реализации этого примера понадобится пользовательская форма (UserForm1) с надписью (Label1) и кнопкой (CommandButton1):

Код имитации печатной машинки состоит из двух процедур, первая из которых замедляет выполнение второй, создавая паузу перед отображением очередного символа, что и создает эффект печатающей машинки:

Обе процедуры размещаются в модуле формы. Нажатие кнопки CommandButton1 запустит замедленную печать символов в поле надписи, имитируя печатную машинку.

Функция ВПР

Вертикальное первое равенство. Ищет совпадение по ключу в первом столбце определенного диапазона и возвращает значение из указанного столбца этого диапазона в совпавшей с ключом строке.

Синтаксис: =ВПР(ключ; диапазон; номер_столбца; ), где

• ключ – обязательный аргумент. Искомое значение, для которого необходимо вернуть значение.
• диапазон – обязательный аргумент. Таблица, в которой необходимо найти значение по ключу. Первый столбец таблицы (диапазона) должен содержать значение совпадающее с ключом, иначе будет возвращена ошибка #Н/Д.
• номер_столбца – обязательный аргумент. Порядковый номер столбца в указанном диапазоне из которого необходимо возвратить значение в случае совпадения ключа.
• интервальный_просмотр – необязательный аргумент. Логическое значение указывающее тип просмотра:
  • ЛОЖЬ – функция ищет точное совпадение по первому столбцу таблицы. Если возможно несколько совпадений, то возвращено будет самое первое. Если совпадение не найдено, то функция возвращает ошибку #Н/Д.
  • ИСТИНА – функция ищет приблизительное совпадение. Является значением по умолчанию. Приблизительное совпадение означает, если не было найдено ни одного совпадения, то функция вернет значение предыдущего ключа. При этом предыдущим будет считаться тот ключ, который идет перед искомым согласно сортировке от меньшего к большему либо от А до Я. Поэтому, перед применением функции с данным интервальным просмотром, предварительно отсортируйте первый столбец таблицы по возрастанию, так как, если это не сделать, функция может вернуть неправильный результат. Когда найдено несколько совпадений, возвращается последнее из них.

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

Пример использования:

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

Для цены необходимо использовать функцию ВПР с точным совпадением (интервальный просмотр ЛОЖЬ), так как данный параметр определен для всех товаров и не предусматривает использование цены другого товара, если вдруг она по случайности еще не определена.

Для определения расположения товара используется ВПР с приблизительным совпадением (интервальный просмотр ИСТИНА), так как распределение товара осуществляется по категориям. Из-за того, что наименование товара состоит из названия категории плюс дополнительный текст, по условиям сортировки от А до Я наименования товаров будут идти сразу после наименования категории, поэтому когда в таблице не обнаруживается совпадений с ключом подтягивается первое значение сверху.

В принципе, данный подход устраивал бы, если для товаров, для которых отсутствует категория, не подтягивалось расположение

Обратите внимание на товар «Лук Подмосковье». Для него определено расположение «Стелаж №2», хотя в первой таблице нет категории «Лук». Это происходит все по тем же причинам, когда функцией не обнаруживается равенств, то она определяет для значения значение меньшего самого близкого по сортировке ключа, а для «Лук Подмосковье» это категория «Картофель»

Это происходит все по тем же причинам, когда функцией не обнаруживается равенств, то она определяет для значения значение меньшего самого близкого по сортировке ключа, а для «Лук Подмосковье» это категория «Картофель».

Он подобного эффекта можно избавиться путем определения категории из наименования товара используя текстовые функции ЛЕВСИМВ(C11;ПОИСК(» «;C11)-1), которые вернут все символы до первого пробела, а также изменить интервальный просмотр на точный.

Помимо всего описанного, функция ВПР позволяет применять для текстовых значений подстановочные символы – * (звездочка – любое количество любых символов) и ? (один любой символ). Например, для искомого значения «*» & «иван» & «*» могут подойти строки Иван, Иванов, диван и т.д.

Также данная функция может искать значения в массивах – =ВПР(1;{2;»Два»:1;»Один»};2;ЛОЖЬ) – результат выполнения строка «Два».

Метод WorksheetFunction.Match

WorksheetFunction.Match – это метод VBA Excel, который возвращает относительное положение элемента в массиве (диапазоне), соответствующее его порядковому номеру в массиве (диапазоне). Метод соответствует функции рабочего листа =ПОИСКПОЗ (поиск позиции).

Обратите внимание на то, что

• поиск позиции в массиве возможен, если он одномерный или двумерный, но с набором элементов только по одному измерению, например: myArray(8, 0) или myArray(1 To 1, 1 To 20);
• поиск позиции в диапазоне рабочего листа возможен, если он содержит только одну строку или один столбец;
• нумерация относительного положения элемента в массиве начинается с единицы, независимо от заданной индексации массива.

Синтаксис метода WorksheetFunction.Match в VBA Excel:

Параметры

Описание параметров метода WorksheetFunction.Match:

В параметре Arg1 можно использовать знаки подстановки для шаблонов, те же, что и для методов Find и Replace.

Значения параметра Arg3, задающие тип сопоставления:

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

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

В VBA есть два типа массивов:

1. Статический — размер массива задается в
   операторе Dim и не может изменяться.
2. Динамический — размер массива не указан в
   выражении Dim. Это устанавливается позже с помощью оператора ReDim

' Статический массив

' Магазины 7 длинных - от 0 до 6
Dim arrLong(0 To 6) As Long

' Магазины 7 длинных - от 0 до 6
Dim arrLong(6) As String

Динамический массив дает нам гораздо больше гибкости. Мы
можем установить размер во время выполнения кода.

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

' Динамический массив

' Объявите переменную
Dim arrLong() As Long

' Установить размер
ReDim arrLong(0 To 6) As Long

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

Большая разница между Dim и ReDim
заключается в том, что мы можем использовать переменную в выражении ReDim. В операторе Dim размер должен быть
постоянным значением.

Sub IspSet()

    ' Объявите переменную
    Dim arrLong() As Long
    
    ' Спросите пользователя о размере
    Dim size As Long
    size = InputBox("Пожалуйста, введите размер массива.", Default:=1)
    
    ' Установите размер на основе пользовательского ввода
    ReDim arrLong(0 To size) As Long

End Sub

На самом деле мы можем использовать оператор Redim без
предварительного использования оператора Dim.

В первом примере вы можете видеть, что мы используем Dim:

Sub IspDimReDim()

    ' Использование Dim
    Dim arr() As String

    ReDim arr(1 To 5) As String
    
    arr(1) = "Яблоко"
    arr(5) = "Апельсин"
    
End Sub

Во втором примере мы не используем Dim:

Sub IspTolkoReDim ()

    ' Использование только ReDim
    ReDim arr(1 To 5) As String
    
    arr(1) = "Яблоко"
    arr(5) = "Апельсин"
    
End Sub

Преимущество состоит в том, что вам не нужно тусклое
утверждение. Недостатком является то, что это может сбить с толку тех, кто
читает ваш код. В любом случае это не имеет большого значения.

Вы можете использовать ключевое слово Preserve с ReDim для сохранения существующих данных при изменении размера массива. Вы можете прочитать больше об этом .

Вы можете найти все, что вам нужно знать о массивах в VBA здесь.

ReDim с Preserve

Если мы используем ReDim для существующего массива, то массив и его содержимое будут удалены.

В следующем примере второй оператор ReDim создаст совершенно новый массив. Исходный массив и его содержимое будут удалены.

Sub UsingRedim()

    Dim arr() As String
    
    ' Установить массив в слоты от 0 до 2
    ReDim arr(0 To 2)
    arr(0) = "Яблоко"
    
    ' Массив с яблоком теперь удален
    ReDim arr(0 To 3)

End Sub

Если мы хотим расширить размер массива без потери содержимого, мы можем использовать ключевое слово Preserve.

Когда мы используем Redim Preserve, новый массив должен начинаться с того же начального размера, например мы не можем сохранить от (0 до 2) до (от 1 до 3) или до (от 2 до 10), поскольку они являются различными начальными размерами.

В следующем коде мы создаем массив с использованием ReDim, а затем заполняем массив типами фруктов.

Затем мы используем Preserve для увеличения размера массива, чтобы не потерять оригинальное содержимое.

Sub UsingRedimPreserve()

    Dim arr() As String
    
    ' Установить массив в слоты от 0 до 1
    ReDim arr(0 To 2)
    arr(0) = "Яблоко"
    arr(1) = "Апельсин"
    arr(2) = "Груша"
    
    ' Изменение размера и сохранение исходного содержимого
    ReDim Preserve arr(0 To 5)

End Sub

Из приведенных ниже снимков экрана видно, что исходное содержимое массива было «сохранено».

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

Использование Preserve с 2-мерными массивами

Preserve работает только с верхней границей массива.

Например, если у вас есть двумерный массив, вы можете сохранить только второе измерение, как показано в следующем примере:

Sub Preserve2D()

    Dim arr() As Long
    
    ' Установите начальный размер
    ReDim arr(1 To 2, 1 To 5)
    
    ' Изменить размер верхнего измерения
    ReDim Preserve arr(1 To 2, 1 To 10)

End Sub

Если мы попытаемся использовать Preserve на нижней границе, мы получим ошибку «Индекс вне диапазона».

В следующем коде мы используем Preserve для первого измерения. Запуск этого кода приведет к ошибке «Индекс вне диапазона»:

Sub Preserve2DError()

    Dim arr() As Long
    
    ' Установите начальный размер
    ReDim arr(1 To 2, 1 To 5)
    
    ' Ошибка «Вне диапазона»
    ReDim Preserve arr(1 To 5, 1 To 5)

End Sub

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

Применяются те же правила сохранения. Мы можем использовать Preserve только на верхней границе, как показано в следующем примере:

Sub Preserve2DRange()

    Dim arr As Variant
    
    ' Назначить диапазон массиву
    arr = Sheet1.Range("A1:A5").Value
    
    ' Preserve будет работать только на верхней границе
    ReDim Preserve arr(1 To 5, 1 To 7)

End Sub

Пример фильтрации

Фильтрация списка в столбце «A» по словам, начинающимся с буквы «К», и загрузка результатов в столбец «B»:

Пример кода VBA Excel с функцией Filter:

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

Erase

Функция Erase используется для сброса значений массивов фиксированного размера и освобождения памяти динамических массивов.Он ведет себя в зависимости от типа массивов.

Синтаксис Erase ArrayName

• Фиксированный числовой массив, каждый элемент в массиве сбрасывается до нуля.
• Исправлен строковый массив, каждый элемент в массиве сбрасывается до нулевой длины «».
• Массив объектов, каждый элемент в массиве сбрасывается до специального значения Nothing .

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете вышеуказанную функцию, она производит следующий вывод.

The value at Zeroth index of NumArray is The value at First index of NumArray is The value at Second index of NumArray is The value at Third index of NumArray is

Функция, которая восстанавливает выделенную память для переменных массива.

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

Динамические массивы в VBA Excel, в отличие от статических, объявляются без указания размерности:

Такие массивы используются, когда заранее неизвестна размерность, которая определяется в процессе выполнения программы. Когда нужная размерность массива становится известна, она в VBA Excel переопределяется с помощью оператора ReDim:

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

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

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

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

Без оператора Preserve можно переопределить все размерности.

Чтение из диапазона ячеек в массив

Если вы читали мою статью о ячейках и диапазонах, то вы знаете, что VBA имеет чрезвычайно эффективный способ чтения из диапазона ячеек в массив и наоборот.

Public Sub ReadToArray()

    ' Объявить динамический массив
    Dim StudentMarks As Variant

    ' Считать значения в массив из первой строки
    StudentMarks = Range("A1:Z1").Value

    ' Запишите значения обратно в третий ряд
    Range("A3:Z3").Value = StudentMarks

End Sub

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

В следующем примере будут считаны примеры данных студента ниже из C3: E6 Лист1 и распечатаны в Immediate Window.

Public Sub ReadAndDisplay()

    ' Получить диапазон
    Dim rg As Range
    Set rg = ThisWorkbook.Worksheets("Лист1").Range("C3:E6")

    ' Создать динамический массив
    Dim StudentMarks As Variant

    ' Считать значения в массив из листа 1
    StudentMarks = rg.Value

    ' Вывести значения массива
    Debug.Print "i", "j", "Значение"
    Dim i As Long, j As Long
    For i = LBound(StudentMarks) To UBound(StudentMarks)
        For j = LBound(StudentMarks, 2) To UBound(StudentMarks, 2)
            Debug.Print i, j, StudentMarks(i, j)
        Next j
    Next i

End Sub

Как видите, первое измерение (доступное через i) массива — это строка, а второе — столбец. Чтобы продемонстрировать это, взглянем на значение 44 в Е4 данных образца. Это значение находится в строке 2 столбца 3 наших данных. Вы можете видеть, что 44 хранится в массиве в StudentMarks (2,3).

Loop Through Array

There are two ways to loop through an array.  The first loops through the integers corresponding to the number positions of the array. If you know the array size you can specify it directly:

However, if you don’t know the array size (if the array is dynamic), you can use the LBound and UBound functions from the previous section:

For Each Array Loop

The second method is with a . This loops through each item in the array:

The For Each Array Loop will work with multi-dimensional arrays in addition to one-dimensional arrays.

Loop Through 2D Array

You can also use the UBound and LBound functions to loop through a multi-dimensional array as well. In this example we will loop through a 2D array.  Notice that the UBound and LBound Functions allow you to specify which dimension of the array to find the upper and lower bounds (1 for first dimension, 2 for second dimension).

Excel vba массив

Об’явление (описание) массива :

Прежде чем использовать массив его необходимо описать прим. * Так как массив является переменной, то действия с массивом во многом похожи на действия с обычными переменными. В массивах используются те же типы данных, что и в обычных переменных, причём массив должен содержать только однотипные данные, однако его ограничение можно обойти, если создать массив типа Variant.

прим. * На самом деле существует функция Array , которая позволяет создавать массив в ходе выполнения программы, без предварительного описания.

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

Об’явление одномерного фиксированного массива :

Dim iString(3) As String

Об’явление двухмерного фиксированного массива :

Dim iData(5, 2) As Double

Об’явление динамического массива :

Dim iWhatIs() As Integer

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

ReDim – инструкция, которая позволяет изменять динамический массив.

Dim iWhatIs() As Integer

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

ReDim Preserve – инструкция, которая позволяет изменять динамический массив, с сохранением всех элементов массива.

ReDim Preserve iWhatIs(2) iWhatIs(0) = 1 iWhatIs(1) = 2 iWhatIs(2) = 3

ReDim Preserve iWhatIs(3) iWhatIs(3) = 4

Номер первого элемента и границы массива :

Пример фиксированного массива, где нумерация элементов начинается с 1

Dim iString(3) As String

Dim iData(5, 2) As Double

Примечание : Инструкцию Option Base необходимо расположить в самом начале модуля VBA.

Пример об’явления фиксированных массивов с явным описанием нижней границы :

Dim iString(1 To 3) As String

Dim iData(1 To 5, 1 To 2) As Double

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

Dim iString(5 To 10)

Dim iData(1 To 5, 2 To 5)

Макрофункции для работы с массивом :

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

iArray = Array(“ivan”, “john”, “maurizio”)

Erase – функция используется для удаления данных, хранимых в элементах массива. Массив фиксированного размера очищается полностью, но память, отведённая под его хранение остаётся за ним. Динамический же массив уничтожается полностью.

IsArray – функция проверяет, является ли переменная массивом. IsArray имеет всего один аргумент (переменную) и в зависимости от результатов проверки возвращает True – если переменная является массивом, или False – если нет.

Пример проверки динамического массива :

ReDim Preserve iArray(1 To 3) iArray(1) = “ivan” iArray(2) = “john” iArray(3) = “maurizio”

ReDim Preserve iArray(1 To 4) iArray(4) = “it’s very good man’s”

If IsArray(iArray) = True Then MsgBox “iArray – это массив”, , “” Else MsgBox “iArray – это не массив”, , “” End If

Пример проверки массива, созданного с помощью функции Array :

iArray = Array(“ivan”, “john”, “maurizio”)

If IsArray(iArray) = True Then MsgBox “iArray – это массив”, , “” Else MsgBox “iArray – это не массив”, , “” End If

LBound – функция определит нижнюю границу массива. UBound – функция определит верхнюю границу массива.

Dim iData(5 To 15, 1 To 100) As Double iLBound = LBound(iData) iUBound = UBound(iData)

MsgBox “Нижняя граница массива : ” & iLBound & Chr(10) _ & “Верхняя граница массива : ” & iUBound, , “”

Формат VBA Dim Statement

Формат выражения Dim показан ниже.

' 1. BASIC VARIABLE
' Объявление основной переменной
Dim  As 

' Объявление фиксированной строки
Dim  As String * 

' 2. VARIANT
Dim  As Variant
Dim 

' 3. OBJECT
' Объявление объекта
Dim  As 

' Объявление и создание объекта
Dim  As New 

' Объявление объекта с использованием позднего связывания
Dim  As Object

' 4. ARRAY
' Объявление статического массива
Dim (first To last) As 

' Объявление динамического массива
Dim () As 
Ниже приведены примеры использования различных форматов.
Sub Primeri()

    ' 1. BASIC VARIABLE
    ' Объявление основной переменной
    Dim name As String
    Dim count As Long
    Dim amount As Currency
    Dim eventdate As Date
    
    ' Объявление фиксированной строки
    Dim userid As String * 8
    
    ' 2. VARIANT
    Dim var As Variant
    Dim var
    
    ' 3. OBJECT
    ' Объявление объекта
    Dim sh As Worksheet
    Dim wk As Workbook
    Dim rg As Range
    
    ' Объявление и создание объекта
    Dim coll1 As New Collection
    Dim o1 As New Class1
    
    ' Объявление объекта - создайте объект ниже, используя Set
    Dim coll2 As Collection
    Dim o2 As Class1
    
    Set coll2 = New Collection
    Set o2 = New Class1
    
    ' 	Объявление и присвоение с использованием позднего связывания
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' 4. ARRAY
    ' Объявление статического массива
    Dim arrScores(1 To 5) As Long
    Dim arrCountries(0 To 9) As String
    
    ' Объявление динамического массива - установите размер ниже, используя ReDim
    Dim arrMarks() As Long
    Dim arrNames() As String
    
    ReDim arrMarks(1 To 10) As Long
    ReDim arrNames(1 To 10) As String

End Sub

Мы рассмотрим эти различные типы операторов Dim в следующих
разделах.

От: admin

Эта тема закрыта для публикации ответов.