Vba instrrev описание: Excel все функции по работе с текстом полная инструкция на сайте ExcelPedia

VBA Excel. Функция InStr (синтаксис, параметры, примеры)

Определение первого вхождения одной строки в другую с помощью функции InStr из кода VBA Excel. Синтаксис функции, параметры, примеры использования.

Описание функции InStr

InStr – это функция, которая предназначена для определения номера позиции первого вхождения одной строки в другую. Она возвращает значение типа Variant (Long).

Функция InStr ищет первое вхождение одной строки в другую с начала исходной строки. Для поиска первого совпадения с конца исходной строки используется функция InStrRev.

Функция InStr часто незаменима при определении параметров функций Left, Mid и Right. Также ее можно использовать для определения наличия искомой подстроки в заданной строке.

Еще есть в VBA Excel функция InStrB, которая работает с байтовыми данными, содержащимися в строке. Она возвращает позицию байта, а не символа первого вхождения одной строки в другую.

Синтаксис, параметры, значения

Синтаксис функции InStr

Полный вариант:

Сокращенный вариант:

Чаще всего в VBA Excel используется сокращенный вариант функции со значениями необязательных параметров по умолчанию.

Параметры функции InStr

Параметр	Описание	Значение по умолчанию
start	Необязательный аргумент.* Числовое выражение, которое задает начальную позицию для поиска.	1
string1	Обязательный аргумент. Строковое выражение, в котором выполняется поиск.	–
string2	Обязательный аргумент. Искомое строковое выражение.	–
compare	Необязательный аргумент. Задает тип сравнения строк.	–1**

* Если задан аргумент compare, аргумент start является обязательным.
** Если аргумент compare не указан, используется значение инструкции Option Compare, заданное на уровне модуля. Если инструкция Option Compare в начале модуля отсутствует, используется ее значение по умолчанию – 0 (двоичное сравнение).

Если параметр start или параметр compare содержит значение NULL, возникает ошибка.

Значения аргумента «compare»

Константа	Значение	Описание
vbUseCompareOption	-1	Сравнение с помощью параметра инструкции Option Compare.
vbBinaryCompare	0	Двоичное (бинарное) сравнение.*
vbTextCompare	1	Текстовое сравнение.*
vbDatabaseCompare	2	Сравнение на основе сведений из базы данных. Только для Microsoft Access.

* При двоичном сравнении учитывается регистр букв, при текстовом – не учитывается.

Значения функции InStr

Примеры использования в VBA Excel

Пример 1

Самый простой пример:

Пример 2

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

Обратите внимание: несмотря на то, что начало поиска мы задали с 10 символа, номер позиции первого вхождения считается с начала строки, в которой выполняется поиск.

Пример 3

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

Результат 7 при побайтовом сравнении получен для кодировки, у которой один символ составляет 2 байта.

Функция InStr. Функции обработки строк в Excel (VBA)

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

Одно из ключевых понятий языка программирования VBA (а именно на нем осуществляется написание макросов) является функция. Это блок программы, который обрабатывает определенные данные и возвращает какой-то конкретный результат. 

Одной из таких функций является InStr. Давайте подробно рассмотрим ее основные аргументы, а также реальные примеры, описывающие ее использование в реальной жизни.

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

Итак, давайте разберем функцию InStr более подробно. 

Описание функции InStr

С помощью функции InStr пользователь может находить местонахождение первого вхождения какого-то текста в другой. Возвращаемый тип данных, осуществляемый этой функцией – Variant. 

Сфера применения этой функции очень широкая, и без нее невозможно обойтись при использовании других функций, таких как Left, Mid, Right. Кроме этого, она может применяться для поиска какого-то текста. 

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

Преимущества функции InStr следующие:

1. Возможность быстро обрабатывать огромные массивы данных буквально за несколько секунд. Когда человек осуществляет поиск вручную, только кажется, что все делает компьютер. НА самом деле, он выполняет лишь часть работы. Но все основное делается самим человеком. например, нужно вбить строку поиска, нажимать на стрелочки, чтобы искать определенное по счету вхождение и так далее. Это очень затратно. В случае же с функцией VBA InStr можно добиться этой цели с помощью макросов: с помощью формулы передать в функцию аргументы, а потом дальше использовать получившийся результат в другой функции. Таким образом автоматизируется огромное количество действий, которые при прочих равных пришлось бы выполнять вручную.
2. Экономия времени. Есть сотрудники, которые за счет макросов смогли значительно увеличить эффективность своей работы и фактически не работать большую часть времени. Все, что им потребовалось – один раз написать скрипт, а потом просто передавать ему нужные параметры. И функция InStr является важной составляющей этого процесса, поскольку со строками приходится иметь дело постоянно, если человек активно использует электронные таблицы.
3. Экономия ресурсов, в том числе, и интеллектуальных. Это тоже очень важный пункт. Ни для кого не секрет, что постоянное выполнение однотипных действий невероятно утомляет. Следовательно, функция InStr позволяет избавиться от бренной ноши постоянного поиска значений вручную, особенно если для этого используется формула.

Кстати! Использование функции InStr дает возможность значительно улучшить творческие способности!

Как? Очень просто. Исследователи доказали, что основной фактор, который мешает творческому процессу – это излишнее количество рутинных действий. В определенных пределах они помогают сосредоточиться (например, когда человек рисует что-то на автомате), но через некоторое время соответствующие нейронные связи начинают терять в скорости проведения нервных импульсов. Следовательно, на другие задачи уже сил не хватает. А творчество – это интеллектуально затратный процесс. 

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

А теперь давайте после лирического отступления все же перейдем к рассмотрению синтаксиса функции InStr.

Синтаксис функции InStr, параметры, значения

Синтаксис функции InStr

Если говорить об этой функции в общем, она записывается таким образом. 

InStr([начало], строка1, строка2, [сравнение])

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

InStr(строка1, строка2)

Как правило, именно сокращенный вариант используется на практике. Нельзя сказать, что в ней вообще нет первого и последнего аргумента, просто автоматически применяются те значения, которые определены как «по умолчанию».  

Параметры функции InStr

Давайте более подробно опишем, что означает каждый из аргументов:

1. Начало. Этот параметр не является обязательным. В него записывается число, которым записывается изначальная позиция, с которой начинается поиск. 
2. Строка1. Этот аргумент нужно не забывать указывать. Это непосредственно тот текст, в котором нужно искать.
3. Строка 2. Это то, что мы ищем.
4. Сравнение. С помощью этого аргумента пользователь может задать способ, которым будут анализироваться и сопоставляться строки. 

Важно учесть некоторые нюансы:

1. Если пользователь записал последнее значение, то первое вводить обязательно.
2. Если же параметр «Сравнение» в функции не прописан, то Эксель применяет значение по умолчанию (0 или другое при условии наличия инструкции Option Compare).
3. Если пользователь укажет значение NULL в необязательные аргументы, то формула выдаст ошибку. Это нужно держать в уме.

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

Значения аргумента «сравнение»

С помощью аргумента «сравнение» человек может задавать тип сравнения. Делается это через указание значения, выраженного в цифровом формате. Если в качестве значения этого аргумента используется 1, то параметр означает сравнивание двух показателей с использованием инструкции Option Compare.

Если этому аргументу присвоить значение 1, то функция осуществляет бинарное сравнение. Простыми словами, при этом типе осуществляется поиск значения с учетом регистра. В случае же с текстовым сравнением регистр не учитывается.

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

1. Позиция первого найденного соответствия. Если возвращается строка, аналогичная тому, что было обнаружено в самом первом случае, то поиск оказался успешным.  
2. 0. Это значение говорит о том, что поиск оказался неудачным. Простыми словами, не получилось в первой строке отыскать вторую. Также нередко можно встретить такую ошибку в ситуациях, когда при попытке поиска оказывается, что первая строка не содержит никаких значений или же значение, передающееся аргументу «начало», оказывается большим, чем длина первой строки. 
3. Такое же значение, которое передается аргументу «начало». Это говорит о том, что вторая строка не содержит никаких значений.
4. NULL. Эта проблема возникает в ситуациях, когда или строка 1, или строка 2 содержит аналогичную ошибку. Чаще всего эта проблема случается, если пользователь неправильно указал диапазон (например, указал неправильный оператор диапазона). Также если вводится несколько пересекающихся диапазонов, то эта ошибка может возникать, если человек неправильно применил оператор диапазона (коим служит символ пробела).

В последнем случае нужно удостовериться в том, что для указания диапазона был использован знак двоеточия. Например, если используется диапазон от А1 до А10, то нужно его записывать, как А1:А10.

Если же нужно указать несколько непересекающихся друг между другом наборов ячеек, то используется символ точки с запятой (некоторые говорят, что используется запятая, но это неправильно). Например, правильный ввод такой: А1:А10;С1:С10.

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

Ну и простой пример для наглядности. Предположим, у нас есть диапазоны a1: A5 C1: C3.

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

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

Примеры использования функции InStr в VBA Excel

Существует множество применений функции InStr при разработке макросов. Давайте разберем три простых примера, чтобы было более наглядно, как можно использовать ее в реальных (или воображаемых) условиях

Пример 1

Этот пример создан специально для новичков, поскольку демонстрирует чисто использование функции InStr. Следовательно, он является наиболее простым.

Sub Test1()

Dim x As Variant

x = InStr(“На горе Фернандо-По, где гуляет Гиппо-по”, “Фернандо”)

MsgBox x

‘Здесь x будет равен 9

End Sub

Пример 2

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

Sub Test2()

Dim x As Variant

x = InStr(10, “На горе Фернандо-По, где гуляет Гиппо-по”, “по”, 0)

MsgBox x

‘Здесь x будет равен 36 (поиск с учетом регистра символов)

x = InStr(10, “На горе Фернандо-По, где гуляет Гиппо-по”, “по”, 1)

MsgBox x

‘Здесь x будет равен 18 (поиск без учета регистра символов)

End Sub

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

Пример 3

А в этом случае осуществляется сравнение сравнений посимвольного и побайтового типов. Здесь также для большей наглядности мы использовали одинаковые строки и подстроки, которые будем искать. А вот и сам пример.

Sub Test 3()

Dim x As Variant

x = InStr(“На горе Фернандо-По, где гуляет Гиппо-по”, “гор”)

MsgBox x

‘Здесь x будет равен 4

x = InStr(“На горе Фернандо-По, где гуляет Гиппо-по”, “гор”)

MsgBox x

‘Здесь x будет равен 7

End Sub

Выводы

Таким образом, функция InStr в языке программирования VBA, который используется в макросах, имеет очень много применений. Она полезна, когда есть оромный массивы данных, который невозможно проанализировать вручную, а встроенные средства автоматизации типа поиска также отнимают много времени и сил.  

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

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

Оцените качество статьи. Нам важно ваше мнение:

VBA — Краткое руководство — CoderLessons.com

VBA означает V isual B asic для приложений A, языка программирования, управляемого событиями, от Microsoft, который в настоящее время преимущественно используется в офисных приложениях Microsoft, таких как MSExcel, MS-Word и MS-Access.

Он помогает техническим специалистам создавать специализированные приложения и решения для расширения возможностей этих приложений. Преимущество этого средства в том, что вам НЕ НУЖНО установить Visual Basic на наш ПК, однако установка Office неявно поможет в достижении этой цели.

Вы можете использовать VBA во всех офисных версиях, от MS-Office 97 до MS-Office 2013, а также с любой из последних доступных версий. Среди VBA Excel VBA является самым популярным. Преимущество использования VBA в том, что вы можете создавать очень мощные инструменты в MS Excel, используя линейное программирование.

Применение VBA

Вы можете задаться вопросом, зачем использовать VBA в Excel, поскольку MS-Excel сам по себе предоставляет множество встроенных функций. MS-Excel предоставляет только основные встроенные функции, которых может быть недостаточно для выполнения сложных вычислений. При таких обстоятельствах VBA становится наиболее очевидным решением.

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

Доступ к редактору VBA

В окне Excel нажмите «ALT + F11». Откроется окно VBA, как показано на следующем снимке экрана.

В этой главе вы узнаете, как написать простой макрос шаг за шагом.

Шаг 1. Сначала включите меню «Разработчик» в Excel 20XX. Чтобы сделать то же самое, нажмите Файл → Параметры.

Шаг 2 — Нажмите «Настроить ленту» и выберите «Разработчик». Нажмите «ОК».

Шаг 3 — Лента «Разработчик» появляется в строке меню.

Шаг 4 — Нажмите кнопку «Visual Basic», чтобы открыть редактор VBA.

Шаг 5 — Запустите сценарий, добавив кнопку. Нажмите Вставить → Выберите кнопку.

Шаг 6 — Выполните щелчок правой кнопкой мыши и выберите «Свойства».

Шаг 7 — Измените имя и подпись, как показано на следующем снимке экрана.

Шаг 8 — Теперь дважды нажмите кнопку, и схема подпрограммы отобразится, как показано на следующем снимке экрана.

Шаг 9 — Начните кодирование, просто добавив сообщение.

Private Sub say_helloworld_Click()
   MsgBox "Hi"
End Sub

Шаг 10 — Нажмите кнопку, чтобы выполнить подпроцедуру. Вывод подпроцедуры показан на следующем снимке экрана.

Примечание. В следующих главах мы продемонстрируем использование простой кнопки, как описано в шагах с 1 по 10. Следовательно, важно тщательно понять эту главу.

В этой главе вы познакомитесь с обычно используемой терминологией Excel VBA. Эти термины будут использоваться в следующих модулях, поэтому важно понимать каждый из них.

Модули

Модули — это область, где написан код. Это новая рабочая тетрадь, поэтому никаких модулей нет.

Чтобы вставить модуль, перейдите к Вставка → Модуль. Как только модуль вставлен, «module1» будет создан.

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

Процедура

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

Двумя основными типами процедур являются Sub и Function.

функция

Функция — это группа многократно используемого кода, которую можно вызывать в любом месте вашей программы. Это устраняет необходимость писать один и тот же код снова и снова. Это помогает программистам разделить большую программу на ряд небольших и управляемых функций.

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

Подпроцедур

Подпроцедуры работают аналогично функциям. Хотя подпроцедуры НЕ возвращают значение, функции могут возвращать или не возвращать значение. Подпроцедуры могут быть вызваны без ключевого слова вызова. Подпроцедуры всегда заключены в инструкции Sub и End Sub .

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

Он включает в себя информацию, такую ​​как разработанную, модифицированную, и может также включать встроенную логику. Комментарии игнорируются интерпретатором при исполнении.

Комментарии в VBA обозначаются двумя способами.

• Любое утверждение, начинающееся с одинарной кавычки (‘), рассматривается как комментарий. Ниже приведен пример.

Любое утверждение, начинающееся с одинарной кавычки (‘), рассматривается как комментарий. Ниже приведен пример.

' This Script is invoked after successful login 
' Written by : TutorialsPoint 
' Return Value : True / False

Любое утверждение, которое начинается с ключевого слова «REM». Ниже приведен пример.

REM This Script is written to Validate the Entered Input 
REM Modified by  : Tutorials point/user2

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

Синтаксис

MsgBox(prompt[,buttons][,title][,helpfile,context])

Описание параметра

• Подсказка — Обязательный параметр. Строка, которая отображается в виде сообщения в диалоговом окне. Максимальная длина подсказки составляет около 1024 символов. Если сообщение распространяется более чем на строку, то строки могут быть разделены с помощью символа возврата каретки (Chr (13)) или символа перевода строки (Chr (10)) между каждой строкой.

• Кнопки — необязательный параметр. Числовое выражение, определяющее тип отображаемых кнопок, стиль используемых значков, идентификатор кнопки по умолчанию и модальность окна сообщения. Если оставить пустым, значение по умолчанию для кнопок будет 0.

• Заголовок — необязательный параметр. Строковое выражение, отображаемое в строке заголовка диалогового окна. Если заголовок оставлен пустым, имя приложения помещается в строку заголовка.

• Файл справки — необязательный параметр. Строковое выражение, определяющее файл справки, который будет использоваться для предоставления контекстной справки для диалогового окна.

• Контекст — необязательный параметр. Числовое выражение, обозначающее номер контекста справки, назначенный автором справки для соответствующего раздела справки. Если предоставляется контекст, также должен быть предоставлен файл справки.

Подсказка — Обязательный параметр. Строка, которая отображается в виде сообщения в диалоговом окне. Максимальная длина подсказки составляет около 1024 символов. Если сообщение распространяется более чем на строку, то строки могут быть разделены с помощью символа возврата каретки (Chr (13)) или символа перевода строки (Chr (10)) между каждой строкой.

Кнопки — необязательный параметр. Числовое выражение, определяющее тип отображаемых кнопок, стиль используемых значков, идентификатор кнопки по умолчанию и модальность окна сообщения. Если оставить пустым, значение по умолчанию для кнопок будет 0.

Заголовок — необязательный параметр. Строковое выражение, отображаемое в строке заголовка диалогового окна. Если заголовок оставлен пустым, имя приложения помещается в строку заголовка.

Файл справки — необязательный параметр. Строковое выражение, определяющее файл справки, который будет использоваться для предоставления контекстной справки для диалогового окна.

Контекст — необязательный параметр. Числовое выражение, обозначающее номер контекста справки, назначенный автором справки для соответствующего раздела справки. Если предоставляется контекст, также должен быть предоставлен файл справки.

Параметр Buttons может принимать любое из следующих значений:

• 0 vbOKOnly — отображает только кнопку ОК.

• 1 vbOKCancel — отображает кнопки OK и Отмена.

• 2 vbAbortRetryIgnore — отображает кнопки отмены, повторной попытки и игнорирования.

• 3 vbYesNoCancel — отображает кнопки «Да», «Нет» и «Отмена».

• 4 vBYesNo — отображает кнопки «Да» и «Нет».

• 5 vbRetryCancel — отображает кнопки «Повторить» и «Отмена».

• 16 vbCritical — отображает значок критического сообщения.

• 32 vbQuestion — отображает значок запроса предупреждения.

• 48 vbExclamation — отображает значок предупреждающего сообщения.

• 64 vbInformation — отображает значок информационного сообщения.

• 0 vbDefaultButton1 — первая кнопка по умолчанию.

• 256 vbDefaultButton2 — вторая кнопка по умолчанию.

• 512 vbDefaultButton3 — третья кнопка по умолчанию.

• 768 vbDefaultButton4 — четвертая кнопка по умолчанию.

• 0 vbApplicationModal Модальное приложение — текущее приложение не будет работать, пока пользователь не ответит на окно сообщения.

• 4096 vbSystemModal Системный модал — Все приложения не будут работать, пока пользователь не ответит на окно сообщения.

0 vbOKOnly — отображает только кнопку ОК.

1 vbOKCancel — отображает кнопки OK и Отмена.

2 vbAbortRetryIgnore — отображает кнопки отмены, повторной попытки и игнорирования.

3 vbYesNoCancel — отображает кнопки «Да», «Нет» и «Отмена».

4 vBYesNo — отображает кнопки «Да» и «Нет».

5 vbRetryCancel — отображает кнопки «Повторить» и «Отмена».

16 vbCritical — отображает значок критического сообщения.

32 vbQuestion — отображает значок запроса предупреждения.

48 vbExclamation — отображает значок предупреждающего сообщения.

64 vbInformation — отображает значок информационного сообщения.

0 vbDefaultButton1 — первая кнопка по умолчанию.

256 vbDefaultButton2 — вторая кнопка по умолчанию.

512 vbDefaultButton3 — третья кнопка по умолчанию.

768 vbDefaultButton4 — четвертая кнопка по умолчанию.

0 vbApplicationModal Модальное приложение — текущее приложение не будет работать, пока пользователь не ответит на окно сообщения.

4096 vbSystemModal Системный модал — Все приложения не будут работать, пока пользователь не ответит на окно сообщения.

Вышеуказанные значения логически разделены на четыре группы: первая группа (от 0 до 5) указывает кнопки, отображаемые в окне сообщения. Вторая группа (16, 32, 48, 64) описывает стиль значка, который должен отображаться, третья группа (0, 256, 512, 768) указывает, какая кнопка должна быть по умолчанию, и четвертая группа (0, 4096). ) определяет модальность окна сообщения.

Возвращаемые значения

Функция MsgBox может возвращать одно из следующих значений, которое можно использовать для определения кнопки, которую пользователь нажал в окне сообщения.

• 1 — vbOK — нажата кнопка ОК
• 2 — vbCancel — Отмена была нажата
• 3 — vbAbort — нажата кнопка «Прервать»
• 4 — vbRetry — повторная попытка была нажата
• 5 — vbIgnore — щелкнули Ignore
• 6 — vbYes — Да нажали
• 7 — vbNo — нет кликали

пример

Function MessageBox_Demo() 
   'Message Box with just prompt message 
   MsgBox("Welcome")     
   
   'Message Box with title, yes no and cancel Butttons  
   int a = MsgBox("Do you like blue color?",3,"Choose options") 
   ' Assume that you press No Button  
   msgbox ("The Value of a is " & a) 
End Function

Выход

Шаг 1. Указанную выше функцию можно выполнить, нажав кнопку «Выполнить» в окне VBA или вызвав функцию из рабочего листа Excel, как показано на следующем снимке экрана.

Шаг 2 — Отображается окно простого сообщения с сообщением «Добро пожаловать» и кнопкой «ОК»

Шаг 3 — После нажатия OK, появляется еще одно диалоговое окно с сообщением вместе с кнопками «да, нет и отмена».

Шаг 4 — После нажатия кнопки «Нет» значение этой кнопки (7) сохраняется в виде целого числа и отображается пользователю в виде окна сообщения, как показано на следующем снимке экрана. Используя это значение, можно понять, на какую кнопку нажал пользователь.

Функция InputBox предлагает пользователям ввести значения. После ввода значений, если пользователь нажимает кнопку OK или нажимает клавишу ВВОД на клавиатуре, функция InputBox возвращает текст в текстовое поле. Если пользователь нажмет кнопку «Отмена», функция вернет пустую строку («»).

Синтаксис

InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile,context])

Описание параметра

• Подсказка — обязательный параметр. Строка, которая отображается в виде сообщения в диалоговом окне. Максимальная длина подсказки составляет около 1024 символов. Если сообщение распространяется более чем на строку, то строки могут быть разделены с помощью символа возврата каретки (Chr (13)) или символа перевода строки (Chr (10)) между каждой строкой.

• Заголовок — необязательный параметр. Строковое выражение, отображаемое в строке заголовка диалогового окна. Если заголовок оставлен пустым, имя приложения помещается в строку заголовка.

• По умолчанию — необязательный параметр. Текст по умолчанию в текстовом поле, который пользователь хотел бы отобразить.

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

• YPos — необязательный параметр. Положение оси Y представляет быстрое расстояние от левой стороны экрана по вертикали. Если оставить это поле пустым, поле ввода будет центрировано вертикально.

• Helpfile — необязательный параметр. Строковое выражение, определяющее файл справки, который будет использоваться для предоставления контекстно-зависимой справки для диалогового окна.

• контекст — необязательный параметр. Числовое выражение, обозначающее номер контекста справки, назначенный автором справки для соответствующего раздела справки. Если предоставляется контекст, также должен быть предоставлен файл справки.

Подсказка — обязательный параметр. Строка, которая отображается в виде сообщения в диалоговом окне. Максимальная длина подсказки составляет около 1024 символов. Если сообщение распространяется более чем на строку, то строки могут быть разделены с помощью символа возврата каретки (Chr (13)) или символа перевода строки (Chr (10)) между каждой строкой.

Заголовок — необязательный параметр. Строковое выражение, отображаемое в строке заголовка диалогового окна. Если заголовок оставлен пустым, имя приложения помещается в строку заголовка.

По умолчанию — необязательный параметр. Текст по умолчанию в текстовом поле, который пользователь хотел бы отобразить.

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

YPos — необязательный параметр. Положение оси Y представляет быстрое расстояние от левой стороны экрана по вертикали. Если оставить это поле пустым, поле ввода будет центрировано вертикально.

Helpfile — необязательный параметр. Строковое выражение, определяющее файл справки, который будет использоваться для предоставления контекстно-зависимой справки для диалогового окна.

контекст — необязательный параметр. Числовое выражение, обозначающее номер контекста справки, назначенный автором справки для соответствующего раздела справки. Если предоставляется контекст, также должен быть предоставлен файл справки.

пример

Давайте вычислим площадь прямоугольника путем получения значений от пользователя во время выполнения с помощью двух полей ввода (одно для длины и одно для ширины).

Function findArea() 
   Dim Length As Double 
   Dim Width As Double 
   
   Length = InputBox("Enter Length ", "Enter a Number") 
   Width = InputBox("Enter Width", "Enter a Number") 
   findArea = Length * Width 
End Function

Выход

Шаг 1 — Чтобы выполнить то же самое, позвоните, используя имя функции, и нажмите Enter, как показано на следующем снимке экрана.

Шаг 2 — После выполнения отображается первое поле ввода (длина). Введите значение в поле ввода.

Шаг 3 — После ввода первого значения отображается второе поле ввода (ширина).

Шаг 4 — После ввода второго числа нажмите кнопку ОК. Область отображается, как показано на следующем снимке экрана.

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

• Вы должны использовать букву в качестве первого символа.

• Вы не можете использовать пробел, точку (.), Восклицательный знак (!) Или символы @, &, $, # в имени.

• Длина имени не может превышать 255 символов.

• Вы не можете использовать зарезервированные ключевые слова Visual Basic в качестве имени переменной.

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

Вы не можете использовать пробел, точку (.), Восклицательный знак (!) Или символы @, &, $, # в имени.

Длина имени не может превышать 255 символов.

Вы не можете использовать зарезервированные ключевые слова Visual Basic в качестве имени переменной.

Синтаксис

В VBA вам необходимо объявить переменные перед их использованием.

Dim <<variable_name>> As <<variable_type>>

Типы данных

Существует много типов данных VBA, которые можно разделить на две основные категории, а именно числовые и нечисловые типы данных.

Числовые типы данных

В следующей таблице приведены числовые типы данных и допустимый диапазон значений.

-3.402823E + 38 до -1.401298E-45 для отрицательных значений

От 1.401298E-45 до 3.402823E + 38 для положительных значений.

-1,79769313486232e + 308 до -4,94065645841247E-324 для отрицательных значений

4.94065645841247E-324 до 1.79769313486232e + 308 для положительных значений.

+/- 79,228,162,514,264,337,593,543,950,335, если не используется десятичная дробь

+/- 7,9228162514264337593543950335 (28 знаков после запятой).

Нечисловые типы данных

В следующей таблице приведены нечисловые типы данных и допустимый диапазон значений.

пример

Давайте создадим кнопку и назовем ее «Variables_demo», чтобы продемонстрировать использование переменных.

Private Sub say_helloworld_Click()
   Dim password As String
   password = "Admin#1"

   Dim num As Integer
   num = 1234

   Dim BirthDay As Date
   BirthDay = DateValue("30 / 10 / 2020")

   MsgBox "Passowrd is " & password & Chr(10) & "Value of num is " &
      num & Chr(10) & "Value of Birthday is " & BirthDay
End Sub

Выход

После выполнения сценария выходные данные будут такими, как показано на следующем снимке экрана.

Константа — это именованная область памяти, используемая для хранения значения, которое НЕ МОЖЕТ быть изменено во время выполнения скрипта. Если пользователь пытается изменить значение константы, выполнение скрипта заканчивается ошибкой. Константы объявляются так же, как и переменные.

Ниже приведены правила именования констант.

• Вы должны использовать букву в качестве первого символа.

• Вы не можете использовать пробел, точку (.), Восклицательный знак (!) Или символы @, &, $, # в имени.

• Длина имени не может превышать 255 символов.

• Вы не можете использовать зарезервированные ключевые слова Visual Basic в качестве имени переменной.

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

Вы не можете использовать пробел, точку (.), Восклицательный знак (!) Или символы @, &, $, # в имени.

Длина имени не может превышать 255 символов.

Вы не можете использовать зарезервированные ключевые слова Visual Basic в качестве имени переменной.

Синтаксис

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

Const <<constant_name>> As <<constant_type>> = <<constant_value>>

пример

Давайте создадим кнопку «Constant_demo», чтобы продемонстрировать, как работать с константами.

Private Sub Constant_demo_Click() 
   Const MyInteger As Integer = 42 
   Const myDate As Date = #2/2/2020# 
   Const myDay As String = "Sunday" 
   
   MsgBox "Integer is " & MyInteger & Chr(10) & "myDate is " 
      & myDate & Chr(10) & "myDay is " & myDay  
End Sub

Выход

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

Оператор может быть определен с помощью простого выражения — 4 + 5 равно 9. Здесь 4 и 5 называются операндами, а + — операторами . VBA поддерживает следующие типы операторов —

• Арифметические Операторы
• Операторы сравнения
• Логические (или реляционные) операторы
• Операторы конкатенации

Арифматические операторы

Следующие арифметические операторы поддерживаются VBA.

Предположим, что переменная A содержит 5, а переменная B содержит 10, тогда —

Показать примеры

Операторы сравнения

В VBA поддерживаются следующие операторы сравнения.

Предположим, что переменная A содержит 10, а переменная B содержит 20, тогда —

Показать примеры

Логические операторы

Следующие логические операторы поддерживаются VBA.

Предположим, что переменная A содержит 10, а переменная B содержит 0, тогда —

Показать примеры

Операторы конкатенации

Следующие операторы конкатенации поддерживаются VBA.

Предположим, что переменная A содержит 5, а переменная B содержит 10, тогда —

Показать примеры

Допустим, переменная A = «Microsoft» и переменная B = «VBScript», тогда —

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

Принятие решений позволяет программистам контролировать ход выполнения скрипта или одного из его разделов. Выполнение регулируется одним или несколькими условными утверждениями.

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

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

Оператор if состоит из логического выражения, за которым следует один или несколько операторов.

Оператор if else состоит из логического выражения, за которым следует один или несколько операторов. Если условие истинно, выполняются операторы из операторов If . Если условие ложно, выполняется другая часть сценария.

Оператор if, сопровождаемый одним или несколькими операторами ElseIf , который состоит из логических выражений, а затем сопровождается необязательным оператором else , который выполняется, когда все условия становятся ложными.

Оператор if или elseif внутри другого оператора if или elseif .

Оператор switch позволяет проверять переменную на соответствие списку значений.

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

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

Оператор цикла позволяет нам выполнять оператор или группу операторов несколько раз. Ниже приводится общая форма оператора цикла в VBA.

VBA предоставляет следующие типы циклов для обработки требований циклов. Нажмите на следующие ссылки, чтобы проверить их детали.

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

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

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

Операторы do..While будут выполняться до тех пор, пока условие истинно. (Т. Е.) Цикл должен повторяться до тех пор, пока условие не станет ложным.

Операторы do..Until будут выполняться до тех пор, пока условие имеет значение False (т. Е.) Цикл должен повторяться до тех пор, пока условие не станет True.

Заявления о контроле цикла

Операторы управления циклом изменяют выполнение от его нормальной последовательности. Когда выполнение выходит из области видимости, все остальные операторы в цикле НЕ выполняются.

VBA поддерживает следующие операторы управления. Нажмите на следующие ссылки, чтобы проверить их детали.

Завершает оператор цикла For и передает выполнение в оператор, следующий сразу за циклом

Завершает оператор Do While и переносит выполнение в оператор сразу после цикла

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

Синтаксис

variablename = "string"

Примеры

str1 = "string"   ' Only Alphabets
str2 = "132.45"   ' Only Numbers
str3 = "!@#$;*"  ' Only Special Characters
Str4 = "Asc23@#"  ' Has all the above

Строковые функции

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

Возвращает первое вхождение указанной подстроки. Поиск происходит слева направо.

Возвращает первое вхождение указанной подстроки. Поиск происходит справа налево.

Возвращает нижний регистр указанной строки.

Возвращает верхний регистр указанной строки.

Возвращает определенное количество символов с левой стороны строки.

Возвращает определенное количество символов с правой стороны строки.

Возвращает определенное количество символов из строки на основе указанных параметров.

Возвращает строку после удаления пробелов в левой части указанной строки.

Возвращает строку после удаления пробелов с правой стороны указанной строки.

Возвращает строковое значение после удаления начальных и конечных пробелов.

Возвращает длину заданной строки.

Возвращает строку после замены строки другой строкой.

Заполняет строку указанным количеством пробелов.

Возвращает целочисленное значение после сравнения двух указанных строк.

Возвращает строку с указанным символом указанное количество раз.

Возвращает строку после изменения последовательности символов данной строки.

Функции даты и времени VBScript помогают разработчикам преобразовывать дату и время из одного формата в другой или выражать значение даты или времени в формате, соответствующем определенному условию.

Функции даты

Функция, которая возвращает текущую системную дату.

Функция, которая преобразует данный вход в дату.

Функция, которая возвращает дату, к которой был добавлен указанный интервал времени.

Функция, которая возвращает разницу между двумя периодами времени.

Функция, которая возвращает указанную часть заданного значения даты ввода.

Функция, которая возвращает действительную дату для данного года, месяца и даты.

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

Функция, которая возвращает логическое значение независимо от того, является ли предоставленный параметр датой.

Функция, которая возвращает целое число от 1 до 31, представляющее день указанной даты.

Функция, которая возвращает целое число от 1 до 12, представляющее месяц указанной даты.

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

Функция, которая возвращает название определенного месяца для указанной даты.

Функция, которая возвращает целое число (от 1 до 7), которое представляет день недели для указанного дня.

Функция, которая возвращает название дня недели для указанного дня.

Функции времени

Функция, которая возвращает текущую системную дату и время.

Функция, которая возвращает целое число от 0 до 23, представляющее часовую часть данного времени.

Функция, которая возвращает целое число от 0 до 59, которое представляет часть минут данного времени.

Функция, которая возвращает целое число от 0 до 59, которое представляет часть секунд данного времени.

Функция, которая возвращает текущее системное время.

Функция, которая возвращает количество секунд и миллисекунд с 12:00.

Функция, которая возвращает время для конкретного ввода часов, минут и секунд.

Функция, которая преобразует входную строку в формат времени.

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

Декларация массива

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

'Method 1 : Using Dim
Dim arr1()	'Without Size

'Method 2 : Mentioning the Size
Dim arr2(5)  'Declared with size of 5

'Method 3 : using 'Array' Parameter
Dim arr3
arr3 = Array("apple","Orange","Grapes")

• Хотя размер массива указан как 5, он может содержать 6 значений, так как индекс массива начинается с нуля.

• Индекс массива не может быть отрицательным.

• Массивы VBScript могут хранить переменные любого типа в массиве. Следовательно, массив может хранить целое число, строку или символы в одной переменной массива.

Хотя размер массива указан как 5, он может содержать 6 значений, так как индекс массива начинается с нуля.

Индекс массива не может быть отрицательным.

Массивы VBScript могут хранить переменные любого типа в массиве. Следовательно, массив может хранить целое число, строку или символы в одной переменной массива.

Присвоение значений массиву

Значения присваиваются массиву путем указания значения индекса массива для каждого из назначаемых значений. Это может быть строка.

пример

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

Private Sub Constant_demo_Click()
   Dim arr(5)
   arr(0) = "1"           'Number as String
   arr(1) = "VBScript"    'String
   arr(2) = 100 		     'Number
   arr(3) = 2.45 		     'Decimal Number
   arr(4) = #10/07/2013#  'Date
   arr(5) = #12.45 PM#    'Time
  
   msgbox("Value stored in Array index 0 : " & arr(0))
   msgbox("Value stored in Array index 1 : " & arr(1))
   msgbox("Value stored in Array index 2 : " & arr(2))
   msgbox("Value stored in Array index 3 : " & arr(3))
   msgbox("Value stored in Array index 4 : " & arr(4))
   msgbox("Value stored in Array index 5 : " & arr(5))
End Sub

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

Value stored in Array index 0 : 1
Value stored in Array index 1 : VBScript
Value stored in Array index 2 : 100
Value stored in Array index 3 : 2.45
Value stored in Array index 4 : 7/10/2013
Value stored in Array index 5 : 12:45:00 PM

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

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

пример

В следующем примере объявляется многомерный массив с 3 строками и 4 столбцами.

Private Sub Constant_demo_Click()
   Dim arr(2,3) as Variant	' Which has 3 rows and 4 columns
   arr(0,0) = "Apple" 
   arr(0,1) = "Orange"
   arr(0,2) = "Grapes"           
   arr(0,3) = "pineapple" 
   arr(1,0) = "cucumber"           
   arr(1,1) = "beans"           
   arr(1,2) = "carrot"           
   arr(1,3) = "tomato"           
   arr(2,0) = "potato"             
   arr(2,1) = "sandwitch"            
   arr(2,2) = "coffee"             
   arr(2,3) = "nuts"            
           
   msgbox("Value in Array index 0,1 : " &  arr(0,1))
   msgbox("Value in Array index 2,2 : " &  arr(2,2))
End Sub

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

Value stored in Array index : 0 , 1 : Orange
Value stored in Array index : 2 , 2 : coffee

ReDim Заявление

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

Синтаксис

ReDim [Preserve] varname(subscripts) [, varname(subscripts)]

Описание параметра

• Сохранять — необязательный параметр, используемый для сохранения данных в существующем массиве при изменении размера последнего измерения.

• Varname — обязательный параметр, который обозначает имя переменной, которое должно соответствовать стандартным соглашениям об именах переменных.

• Subscripts — обязательный параметр, который указывает размер массива.

Сохранять — необязательный параметр, используемый для сохранения данных в существующем массиве при изменении размера последнего измерения.

Varname — обязательный параметр, который обозначает имя переменной, которое должно соответствовать стандартным соглашениям об именах переменных.

Subscripts — обязательный параметр, который указывает размер массива.

пример

В следующем примере массив был переопределен, а затем значения сохранены при изменении существующего размера массива.

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

Private Sub Constant_demo_Click()
   Dim a() as variant
   i = 0
   redim a(5)
   a(0) = "XYZ"
   a(1) = 41.25
   a(2) = 22
  
   REDIM PRESERVE a(7)
   For i = 3 to 7
   a(i) = i
   Next
  
   'to Fetch the output
   For i = 0 to ubound(a)
      Msgbox a(i)
   Next
End Sub

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

XYZ
41.25
22
3
4
5
6
7

Методы массива

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

Функция, которая возвращает целое число, соответствующее наименьшему нижнему индексу из указанных массивов.

Функция, которая возвращает целое число, соответствующее наибольшему нижнему индексу заданных массивов.

Функция, которая возвращает массив, содержащий указанное количество значений. Разделить на основе разделителя.

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

Функция, которая возвращает нулевой массив, который содержит подмножество строкового массива на основе определенных критериев фильтра.

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

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

Функция — это группа многократно используемого кода, который можно вызывать в любом месте вашей программы. Это устраняет необходимость писать один и тот же код снова и снова. Это позволяет программистам разделить большую программу на ряд небольших и управляемых функций.

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

Определение функции

Функция VBA может иметь необязательный оператор возврата. Это необходимо, если вы хотите вернуть значение из функции.

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

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

Прежде чем использовать функцию, нам нужно определить эту конкретную функцию. Наиболее распространенный способ определения функции в VBA — использование ключевого слова Function , за которым следует уникальное имя функции, которое может содержать или не содержать список параметров и оператор с ключевым словом End Function , который указывает конец функции. Ниже приведен основной синтаксис.

Синтаксис

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

Function Functionname(parameter-list)
   statement 1
   statement 2
   statement 3
   .......
   statement n
End Function

пример

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

Function findArea(Length As Double, Optional Width As Variant)
   If IsMissing(Width) Then
      findArea = Length * Length
   Else
      findArea = Length * Width
   End If
End Function

Вызов функции

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

Вывод области, как показано ниже, будет отображаться пользователю.

Подпроцедуры похожи на функции, однако есть несколько отличий.

• Подпроцедуры НЕ возвращают значение, в то время как функции могут возвращать или не возвращать значение.

• Подпроцедуры МОГУТ быть вызваны без ключевого слова вызова.

• Подпроцедуры всегда заключены в инструкции Sub и End Sub.

Подпроцедуры НЕ возвращают значение, в то время как функции могут возвращать или не возвращать значение.

Подпроцедуры МОГУТ быть вызваны без ключевого слова вызова.

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

пример

Sub Area(x As Double, y As Double)
   MsgBox x * y
End Sub

Процедуры вызова

Чтобы вызвать процедуру где-нибудь в скрипте, вы можете сделать вызов из функции. Мы не сможем использовать тот же способ, что и для функции, так как подпроцедура НЕ возвратит значение.

Function findArea(Length As Double, Width As Variant)
   area Length, Width    ' To Calculate Area 'area' sub proc is called
End Function

Теперь вы сможете вызывать только функцию, но не подпроцедуру, как показано на следующем снимке экрана.

Площадь рассчитывается и отображается только в окне сообщения.

В ячейке результата отображается НОЛЬ, так как значение области НЕ возвращается из функции. Короче говоря, вы не можете сделать прямой вызов подпроцедуры из листа Excel.

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

• События рабочего листа
• Рабочая тетрадь События

События рабочего листа

События рабочего листа инициируются при изменении рабочего листа. Он создается путем щелчка правой кнопкой мыши на вкладке листа и выбора «просмотра кода», а затем вставки кода.

Пользователь может выбрать каждый из этих рабочих листов и выбрать «Рабочий лист» из выпадающего списка, чтобы получить список всех поддерживаемых событий рабочего листа.

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

Private Sub Worksheet_Activate() 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)    
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) 
Private Sub Worksheet_Calculate() 
Private Sub Worksheet_Change(ByVal Target As Range) 
Private Sub Worksheet_Deactivate() 
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

пример

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

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   MsgBox "Before Double Click"
End Sub

Выход

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

Рабочая тетрадь События

События рабочей книги инициируются, когда происходит изменение рабочей книги в целом. Мы можем добавить код для событий рабочей книги, выбрав «ThisWorkbook» и выбрав «рабочая книга» из выпадающего списка, как показано на следующем снимке экрана. Немедленно подпрограмма Workbook_open отображается пользователю, как показано на следующем снимке экрана.

Ниже приведены поддерживаемые события Workbook, которые могут быть добавлены пользователем.

Private Sub Workbook_AddinUninstall() 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
Private Sub Workbook_BeforePrint(Cancel As Boolean) 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
Private Sub Workbook_Deactivate() 
Private Sub Workbook_NewSheet(ByVal Sh As Object) 
Private Sub Workbook_Open() 
Private Sub Workbook_SheetActivate(ByVal Sh As Object) 
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) 
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) 
Private Sub Workbook_SheetCalculate(ByVal Sh As Object) 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) 
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink) 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) 
Private Sub Workbook_WindowActivate(ByVal Wn As Window) 
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window) 
Private Sub Workbook_WindowResize(ByVal Wn As Window)

пример

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

Private Sub Workbook_NewSheet(ByVal Sh As Object)
   MsgBox "New Sheet Created Successfully"
End Sub

Выход

После создания нового листа Excel пользователю отображается сообщение, как показано на следующем снимке экрана.

Существует три типа ошибок в программировании: (a) синтаксические ошибки, (b) ошибки времени выполнения и (c) логические ошибки.

Синтаксические ошибки

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

Function ErrorHanlding_Demo()
   dim x,y
   x = "Tutorialspoint"
   y = Ucase(x
End Function

Ошибки во время выполнения

Ошибки времени выполнения, также называемые исключениями, возникают во время выполнения после интерпретации.

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

Function ErrorHanlding_Demo1()
   Dim x,y
   x = 10
   y = 20
   z = fnadd(x,y)
   a = fnmultiply(x,y)
End Function

Function fnadd(x,y)
   fnadd = x + y
End Function

Логические ошибки

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

Вы не можете поймать эти ошибки, потому что это зависит от требований вашего бизнеса, какую логику вы хотите использовать в своей программе.

Например, деление числа на ноль или написанного скрипта, который входит в бесконечный цикл.

Err Object

Предположим, если у нас есть ошибка во время выполнения, то выполнение останавливается, отображая сообщение об ошибке. Как разработчик, если мы хотим зафиксировать ошибку, то используется Error Object.

пример

В следующем примере Err.Number дает номер ошибки, а Err.Description — описание ошибки.

Err.Raise 6   ' Raise an overflow error.
MsgBox "Error # " & CStr(Err.Number) & " " & Err.Description
Err.Clear   ' Clear the error.

Обработка ошибок

VBA включает подпрограмму обработки ошибок, а также может использоваться для отключения подпрограммы обработки ошибок. Без оператора On Error любая ошибка во время выполнения является фатальной: отображается сообщение об ошибке, и выполнение резко останавливается.

On Error { GoTo [ line | 0 | -1 ] | Resume Next }

Линия GoTo

Включает процедуру обработки ошибок, которая начинается со строки, указанной в требуемом аргументе строки. Указанная строка должна быть в той же процедуре, что и оператор On Error, иначе произойдет ошибка времени компиляции.

Перейти 0

Отключает включенный обработчик ошибок в текущей процедуре и сбрасывает его в Nothing.

GoTo -1

Отключает включенное исключение в текущей процедуре и сбрасывает его в Nothing.

Продолжить дальше

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

пример

Public Sub OnErrorDemo()
   On Error GoTo ErrorHandler   ' Enable error-handling routine.
   Dim x, y, z As Integer
   x = 50
   y = 0
   z = x / y   ' Divide by ZERO Error Raises
  
   ErrorHandler:    ' Error-handling routine.
   Select Case Err.Number   ' Evaluate error number.
      Case 10   ' Divide by zero error
         MsgBox ("You attempted to divide by zero!")
      Case Else
         MsgBox "UNKNOWN ERROR  - Error# " & Err.Number & " : " & Err.Description
   End Select
   Resume Next
End Sub

При программировании с использованием VBA есть несколько важных объектов, с которыми пользователь будет иметь дело.

• Объекты приложения
• Объекты рабочей книги
• Объекты рабочего листа
• Диапазон объектов

Объекты приложения

Объект Application состоит из следующих элементов:

• Общесоциальные настройки и опции.
• Методы, которые возвращают объекты верхнего уровня, такие как ActiveCell, ActiveSheet и т. Д.

пример

'Example 1 :
Set xlapp = CreateObject("Excel.Sheet") 
xlapp.Application.Workbooks.Open "C:\test.xls"

'Example 2 :
Application.Windows("test.xls").Activate

'Example 3:
Application.ActiveCell.Font.Bold = True

Объекты рабочей книги

Объект Workbook является членом коллекции Workbooks и содержит все объекты Workbook, открытые в настоящее время в Microsoft Excel.

пример

'Ex 1 : To close Workbooks
Workbooks.Close

'Ex 2 : To Add an Empty Work Book
Workbooks.Add

'Ex 3: To Open a Workbook
Workbooks.Open FileName:="Test.xls", ReadOnly:=True

'Ex : 4 - To Activate WorkBooks
Workbooks("Test.xls").Worksheets("Sheet1").Activate

Объекты рабочего листа

Объект Worksheet является членом коллекции Worksheets и содержит все объекты Worksheet в рабочей книге.

пример

'Ex 1 : To make it Invisible
Worksheets(1).Visible = False

'Ex 2 : To protect an WorkSheet
Worksheets("Sheet1").Protect password:=strPassword, scenarios:=True

Диапазон объектов

Объекты Range представляют ячейку, строку, столбец или выборку ячеек, содержащих один или несколько непрерывных блоков ячеек.

'Ex 1 : To Put a value in the cell A5
Worksheets("Sheet1").Range("A5").Value = "5235"

'Ex 2 : To put a value in range of Cells
Worksheets("Sheet1").Range("A1:A4").Value = 5

Вы также можете прочитать файл Excel и записать содержимое ячейки в текстовый файл, используя VBA. VBA позволяет пользователям работать с текстовыми файлами двумя способами:

• Объект файловой системы
• используя команду записи

Объект файловой системы (FSO)

Как следует из названия, FSO помогают разработчикам работать с дисками, папками и файлами. В этом разделе мы обсудим, как использовать FSO.

Привод

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

приводы

Диски это коллекция. Он предоставляет список дисков, подключенных к системе, физически или логически.

файл

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

файлы

Файлы это коллекция. Он предоставляет список всех файлов, содержащихся в папке.

скоросшиватель

Папка — это объект. Он предоставляет методы и свойства, которые позволяют разработчикам создавать, удалять или перемещать папки.

Папки

Папки это коллекция. Он предоставляет список всех папок в папке.

TextStream

TextStream — это объект. Это позволяет разработчикам читать и писать текстовые файлы.

Привод

Диск — это объект, который обеспечивает доступ к свойствам конкретного диска или общей сетевой папки. Следующие свойства поддерживаются объектом Drive —

• Доступное пространство
• DriveLetter
• Тип вождения
• Файловая система
• Свободное место
• Готов
• Дорожка
• Корневая папка
• Серийный номер
• ShareName
• Общий размер
• VolumeName

пример

Шаг 1. Прежде чем приступить к написанию сценариев с использованием FSO, мы должны включить Microsoft Scripting Runtime. Чтобы сделать то же самое, перейдите в Инструменты → Ссылки, как показано на следующем снимке экрана.

Шаг 2. Добавьте «Microsoft Scripting RunTime» и нажмите «ОК».

Шаг 3 — Добавьте данные, которые вы хотели бы записать в текстовый файл, и добавьте командную кнопку.

Шаг 4 — Теперь пришло время для сценария.

Private Sub fn_write_to_text_Click()
   Dim FilePath As String
   Dim CellData As String
   Dim LastCol As Long
   Dim LastRow As Long
  
   Dim fso As FileSystemObject
   Set fso = New FileSystemObject
   Dim stream As TextStream
  
   LastCol = ActiveSheet.UsedRange.Columns.Count
   LastRow = ActiveSheet.UsedRange.Rows.Count
    
   ' Create a TextStream.
   Set stream = fso.OpenTextFile("D:\Try\Support.log", ForWriting, True)
  
   CellData = ""
  
   For i = 1 To LastRow
      For j = 1 To LastCol
         CellData = Trim(ActiveCell(i, j).Value)
         stream.WriteLine "The Value at location (" & i & "," & j & ")" & CellData
      Next j
   Next i
  
   stream.Close
   MsgBox ("Job Done")
End Sub

Выход

При выполнении сценария убедитесь, что курсор помещен в первую ячейку рабочего листа. Файл Support.log создается, как показано на следующем снимке экрана в разделе «D: \ Try».

Содержимое файла показано на следующем снимке экрана.

Написать команду

В отличие от FSO, нам НЕ нужно добавлять ссылки, однако мы НЕ сможем работать с дисками, файлами и папками. Мы сможем просто добавить поток в текстовый файл.

пример

Private Sub fn_write_to_text_Click()
   Dim FilePath As String
   Dim CellData As String
   Dim LastCol As Long
   Dim LastRow As Long
  
   LastCol = ActiveSheet.UsedRange.Columns.Count
   LastRow = ActiveSheet.UsedRange.Rows.Count
    
   FilePath = "D:\Try\write.txt"
   Open FilePath For Output As #2
  
   CellData = ""
   For i = 1 To LastRow
      For j = 1 To LastCol
         CellData = "The Value at location (" & i & "," & j & ")" & Trim(ActiveCell(i, j).Value)
         Write #2, CellData
      Next j
   Next i
  
   Close #2
   MsgBox ("Job Done")
End Sub

Выход

После выполнения сценария файл «write.txt» создается в папке «D: \ Try», как показано на следующем снимке экрана.

Содержимое файла показано на следующем снимке экрана.

Используя VBA, вы можете создавать графики на основе определенных критериев. Давайте посмотрим на это на примере.

Шаг 1 — Введите данные, по которым должен быть сгенерирован график.

Шаг 2 — Создайте 3 кнопки — одну для создания гистограммы, другую для создания круговой диаграммы и другую для создания столбчатой ​​диаграммы.

Шаг 3 — Разработайте макрос, чтобы сгенерировать каждый из этих типов диаграмм.

' Procedure to Generate Pie Chart
Private Sub fn_generate_pie_graph_Click()
   Dim cht As ChartObject
   For Each cht In Worksheets(1).ChartObjects
      cht.Chart.Type = xlPie
   Next cht
End Sub

' Procedure to Generate Bar Graph
Private Sub fn_Generate_Bar_Graph_Click()
   Dim cht As ChartObject
   For Each cht In Worksheets(1).ChartObjects
      cht.Chart.Type = xlBar
   Next cht
End Sub

' Procedure to Generate Column Graph
Private Sub fn_generate_column_graph_Click()
   Dim cht As ChartObject
   For Each cht In Worksheets(1).ChartObjects
      cht.Chart.Type = xlColumn
   Next cht
End Sub

Шаг 4 — При нажатии на соответствующую кнопку создается диаграмма. В следующем выводе нажмите кнопку «Сгенерировать круговую диаграмму».

Форма пользователя — это настраиваемое диалоговое окно, которое делает ввод пользовательских данных более управляемым и более простым в использовании для пользователя. В этой главе вы научитесь разрабатывать простую форму и добавлять данные в Excel.

Шаг 1 — Перейдите к окну VBA, нажав Alt + F11 и перейдите к меню «Вставить» и выберите «Форма пользователя». После выбора форма пользователя отображается, как показано на следующем снимке экрана.

Шаг 2 — Разработка форм с использованием заданных элементов управления.

Шаг 3 — После добавления каждого элемента управления, элементы управления должны быть названы. Заголовок соответствует тому, что отображается в форме, а имя соответствует логическому имени, которое будет появляться при написании кода VBA для этого элемента.

Шаг 4 — Ниже приведены имена против каждого из добавленных элементов управления.

Шаг 5 — Добавьте код для события загрузки формы, выполнив щелчок правой кнопкой мыши на форме и выбрав «Просмотреть код».

Шаг 6 — Выберите «Userform» из выпадающего списка объектов и выберите метод «Initialize», как показано на следующем снимке экрана.

Шаг 7 — После загрузки формы убедитесь, что текстовые поля очищены, раскрывающиеся окна заполнены и переключатели сброшены.

Private Sub UserForm_Initialize()
   'Empty Emp ID Text box and Set the Cursor 
   txtempid.Value = ""
   txtempid.SetFocus
   
   'Empty all other text box fields
   txtfirstname.Value = ""
   txtlastname.Value = ""
   txtemailid.Value = ""
   
   'Clear All Date of Birth Related Fields
   cmbdate.Clear
   cmbmonth.Clear
   cmbyear.Clear
   
   'Fill Date Drop Down box - Takes 1 to 31
   With cmbdate
      .AddItem "1"
      .AddItem "2"
      .AddItem "3"
      .AddItem "4"
      .AddItem "5"
      .AddItem "6"
      .AddItem "7"
      .AddItem "8"
      .AddItem "9"
      .AddItem "10"
      .AddItem "11"
      .AddItem "12"
      .AddItem "13"
      .AddItem "14"
      .AddItem "15"
      .AddItem "16"
      .AddItem "17"
      .AddItem "18"
      .AddItem "19"
      .AddItem "20"
      .AddItem "21"
      .AddItem "22"
      .AddItem "23"
      .AddItem "24"
      .AddItem "25"
      .AddItem "26"
      .AddItem "27"
      .AddItem "28"
      .AddItem "29"
      .AddItem "30"
      .AddItem "31"
   End With
   
   'Fill Month Drop Down box - Takes Jan to Dec
   With cmbmonth
      .AddItem "JAN"
      .AddItem "FEB"
      .AddItem "MAR"
      .AddItem "APR"
      .AddItem "MAY"
      .AddItem "JUN"
      .AddItem "JUL"
      .AddItem "AUG"
      .AddItem "SEP"
      .AddItem "OCT"
      .AddItem "NOV"
      .AddItem "DEC"
   End With
   
   'Fill Year Drop Down box - Takes 1980 to 2014
   With cmbyear
      .AddItem "1980"
      .AddItem "1981"
      .AddItem "1982"
      .AddItem "1983"
      .AddItem "1984"
      .AddItem "1985"
      .AddItem "1986"
      .AddItem "1987"
      .AddItem "1988"
      .AddItem "1989"
      .AddItem "1990"
      .AddItem "1991"
      .AddItem "1992"
      .AddItem "1993"
      .AddItem "1994"
      .AddItem "1995"
      .AddItem "1996"
      .AddItem "1997"
      .AddItem "1998"
      .AddItem "1999"
      .AddItem "2000"
      .AddItem "2001"
      .AddItem "2002"
      .AddItem "2003"
      .AddItem "2004"
      .AddItem "2005"
      .AddItem "2006"
      .AddItem "2007"
      .AddItem "2008"
      .AddItem "2009"
      .AddItem "2010"
      .AddItem "2011"
      .AddItem "2012"
      .AddItem "2013"
      .AddItem "2014"
   End With
   
   'Reset Radio Button. Set it to False when form loads.
   radioyes.Value = False
   radiono.Value = False

End Sub

Шаг 8 — Теперь добавьте код в кнопку «Отправить». Нажав кнопку «Отправить», пользователь сможет добавить значения в таблицу.

Private Sub btnsubmit_Click()
   Dim emptyRow As Long
  
   'Make Sheet1 active
   Sheet1.Activate
  
   'Determine emptyRow
   emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
  
   'Transfer information
   Cells(emptyRow, 1).Value = txtempid.Value
   Cells(emptyRow, 2).Value = txtfirstname.Value
   Cells(emptyRow, 3).Value = txtlastname.Value
   Cells(emptyRow, 4).Value = cmbdate.Value & "/" & cmbmonth.Value & "/" & cmbyear.Value
   Cells(emptyRow, 5).Value = txtemailid.Value
  
   If radioyes.Value = True Then
      Cells(emptyRow, 6).Value = "Yes"
   Else
      Cells(emptyRow, 6).Value = "No"
   End If
End Sub

Шаг 9 — Добавьте метод, чтобы закрыть форму, когда пользователь нажимает кнопку Отмена.

Private Sub btncancel_Click()
   Unload Me
End Sub

Шаг 10 — Заполните форму, нажав кнопку «Выполнить». Введите значения в форму и нажмите кнопку «Отправить». Автоматически значения будут перетекать в лист, как показано на следующем снимке экрана.

VBScript: Строковые функции vbs | scriptcoding.ru

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

Строковые функции vbs

Asc(str) – С помощью этой строковой функции vbs можно получить ASCII-код первого символа в строке str. Параметр str может быть пробелом или содержать только один символ.

'**********************************
' Получение кода символа
' asc.vbs
'**********************************
dim a, b, c
 
a = asc(" ")
b = asc("string")
c = asc("A")
 
MsgBox a & vbCrLf & b & vbCrLf & c

Chr(code) – Данная vbs строковая функция позволяет получить символ, указав в качестве параметра его ASCII-код

'**********************************
' Получение символа по его коду
' chr.vbs
'**********************************
dim i, list
 
for i=100 to 120
list = list & chr(i) & vbCrLf
next
 
MsgBox list

InStr([start,] str1, str2 [, compare]) – позволяет определить позицию, с которой начинается искомая последовательность символов в заданной строчке.

Start – данный аргумент является числом, и задает позицию, начиная с которой надо начать поиск. Если аргумент отсутствует, то поиск производится с начала.
string1 – собственно, та строка, в которой надо произвести поиск.
string2 – тут мы задаем те символы, которые ищем.
compare – данный параметр может принимать значения vbBinaryCompare (учет регистра, значение 0) и vbTextCompare (без учета регистра, значение 1).

'**********************************
' Поиск в строке
' InStr.vbs
'**********************************
dim my_str, index_1, index_2, index_3
 
my_str = "это 3-й ПРИМЕР сценария на vbs"
index_1 = InStr(1,my_str,"пример",vbTextCompare)             '9
index_2 = InStr(3,my_str,"пример",vbBinaryCompare)          '0
index_3 = InStr(15,my_str,"пример",vbTextCompare)           '0

MsgBox index_1 & vbCrLf & index_2 & vbCrLf & index_3

InStrRev(strl, str2[, start [, compare]] ) – Аналогично работе предыдущей строковой vbs функции, но поиск идет в обратном порядке.

'**********************************
' Поиск в строке
' InStrRev.vbs
'**********************************
dim my_str, index_1, index_2, index_3
 
my_str = "это 3-й ПРИМЕР сценария на vbs"
index_1 = InStrRev(my_str,"пример",1,vbTextCompare)                   '0
index_2 = InStrRev(my_str,"пример",3,vbVinaryCompare)   '0
index_3 = InStrRev(my_str,"пример",15,vbTextCompare)     '9

MsgBox index_1 & vbCrLf & index_2 & vbCrLf & index_3

Join(list[, delim]) – позволяет объединить все значения элементов массива в одну строку. Тут list – одномерный массив, а delim – необязательный параметр, который задает символ разделитель.

'**********************************
' Работа с массивом
' Join.vbs
'**********************************
dim my_str, index_1, index_2, index_3, my_arr
 
my_arr = Array(1, "a","b","c",3)
my_str = Join(my_arr, "--")
 
MsgBox my_str

LCase(str) – Позволяет преобразовать все символы к нижнему регистру.

'**********************************
' Преобразование к нижнему регистру
' LCase.vbs
'**********************************
dim my_str, my_str_L
 
my_str = "ВЕРХНИЙ РЕГИСТР"
my_str_L = LCase(my_str)
 
MsgBox my_str_L

UCase(str) — Позволяет преобразовать все символы к верхнему регистру.

'**********************************
' Преобразование к верхнему регистру
' UCase.vbs
'**********************************
dim my_str, my_str_U
 
my_str = "нижний регистр"
my_str_U = UCase(my_str)
 
MsgBox my_str_U

Left(str, len) – Вернёт заданное количество (len) знаков с начала строки str.

'**********************************
' Вырезка слева
' left.vbs
'**********************************
dim my_str, my_str_result
 
my_str = "нижний регистр"
my_str_result = Left(my_str, 4)
 
MsgBox my_str_result

Right(str, len) — Вернёт заданное количество (len) знаков с конца строки str.

'**********************************
' Вырезка справа
' right.vbs
'**********************************
dim my_str, my_str_result
 
my_str = "нижний регистр"
my_str_result = Right(my_str, 4)
 
MsgBox my_str_result

Len(str) – Использование этой строковой функции vbs позволяет определить количество знаков в str.

'**********************************
' Длина строки
' len.vbs
'**********************************
dim my_str
 
my_str = "нижний регистр"
 
MsgBox len(my_str)       '14 знаков

LTrim(str) — удаление пробелов в начале.

RTrim(str) — удаление пробелов в конце.

Trim(str) – удаление пробелов в начале и в конце.

Скачать архив с примерами

'**********************************
' Удаление пробелов
' trim.vbs
'**********************************
dim my_str
 
my_str = " нижний регистр "
 
MsgBox LTrim(my_str) 
MsgBox RTrim(my_str) 
MsgBox Trim(my_str)

Space(x) — Просто формирует строчку из пробелов количеством x.

'**********************************
' Формирование пробелов
' space.vbs
'**********************************
dim my_str_1, my_str_2, my_space
 
my_str_1 = "первый"
my_str_2 = "второй"
my_space = space(10)
 
MsgBox my_str_1 & my_space & my_str_2

String(number, char) – Формирование строчки, которая состоит из заданного количества (number) определенных символов (char).

'**********************************
' Формирование строчки
' string.vbs
'**********************************
dim my_str
 
my_str = string(10,"a")
 
MsgBox my_str

Mid(str, start [, len]) – Формирование из строчки str подстрочки, которая будет начинаться с позиции start, параметр len позволяет указать длину. Строковые функции vbs.

'**********************************
' Извлечение подстроки
' mid.vbs
'**********************************
dim my_str
 
my_str = "первый второй и третий"
 
MsgBox mid(my_str, 4,8)

Replace(str, find, replacewith [, start[, count[, compare]]]) – Позволяет производит замену в строке.

str – начальная строка.
find – подстрока, которую мы ищем.
replacewith – подставляемое значение.
start – начало поиска.
count – количество замен, по умолчанию стоит -1, что говорит о полной замене.
compare – параметр принимает значения vbBinaryCompare (учет регистра, значение 0) и vbTextCompare (без учета регистра, значение 1).

'**********************************
' Замена
' replace.vbs
'**********************************
dim my_str
 
my_str = "первый четвертый и третий"
 
MsgBox replace(my_str, "четвертый", "второй")

Split(str[, delim[, count[, compare]]]) – Производит парсинг строки на подстроки и заполняет ими возвращаемый массив.

str – строка для обработки.
delim – разделитель (по умолчанию — пробел).
count – количество, по умолчанию – значение -1 (все элементы).
compare — параметр принимает значения vbBinaryCompare (учет регистра, значение 0) и vbTextCompare (без учета регистра, значение 1).

'**********************************
' Парсинг
' split.vbs
'**********************************
dim my_str, my_arr, index, list
 
my_str = "один, два, три, четыри, пять"
my_arr = split(my_str, ", ")
 
for each index in my_arr
     list = list & index & vbCrLf 
next
 
MsgBox list

StrComp(str1, str2[, compare]) – функция сравнения str1 строки и строки str2. Если они равны, возвращается 0, если str1<str2, то возвращается -1, если str1>str2 – возвращается 1. Параметр compare принимает значения vbBinaryCompare (учет регистра, значение 0) и vbTextCompare (без учёта регистра, значение 1). Строковые функции vbs.

'**********************************
' Сравнение
' StrComp.vbs
'**********************************
dim my_str_1, my_str_2, result
 
my_str_1 = "однажды"
my_str_2 = "однажды"
 
result = StrComp(my_str_1, my_str_2)
 
MsgBox result

Благодарю всех за внимание, надеюсь, материал статьи был для вас полезным.

НОУ ИНТУИТ | Основы офисного программирования и язык VBA

Форма обучения:

дистанционная

Стоимость самостоятельного обучения:

бесплатно

Доступ:

свободный

Документ об окончании:

Уровень:

Для всех

Длительность:

30:52:00

Выпускников:

312

Качество курса:

4.65 | 4.29

Курс адресован программистам, работающим в среде Office, и использующим язык VBA для настройки офисных документов. Хотя он написан применительно к среде Office 2000, он сохраняет свою актуальность и для современной офисной среды. Он содержит полное описание языка Visual Basic, начиная с описания переменных и объектов, операторов языка и процедур и заканчивая созданием классов. Подробно разбираются вопросы: создание семейства классов, наследование интерфейсов, реализация полиморфизма, создание классов с событиями, программирование Win API, функции обратного вызова, новые функции разбора строк и многое другое.

Курс построен нетрадиционно. Первые 6 лекций, адресованы программистам, уже знакомым с языком VBA и имеющим опыт работы в среде Office. Документы и проекты, классы и объекты, семейства классов, динамические классы, классы как обертка, программирование Win API – все эти темы подробно обсуждаются в этой части курса.

Дополнительные курсы

2 часа 30 минут

—

Введение

—

Начнем с объектов Office 2000

—

Документы и проекты

—

Типы данных и переменные

—

Классы и объекты

—

Семейство классов и наследование интерфейсов. Динамические классы

—

WinApi

—

Операторы

—

Операции и встроенные функции

—

Процедуры и функции

—

Отладка и оптимизация программ. Отладка

—

Проектирование интерфейса. Меню

—

Проектирование интерфейса. Диалоговые окна

—

Проектирование интерфейса. Объекты диалоговых окон

—

Файловый ввод-вывод.

—

Работа с текстом в vba

VBA — Краткое руководство — it-brain.online

VBA означает V isual B asic для приложений A, языка программирования, управляемого событиями, от Microsoft, который в настоящее время преимущественно используется в офисных приложениях Microsoft, таких как MSExcel, MS-Word и MS-Access.

Он помогает техническим специалистам создавать специализированные приложения и решения для расширения возможностей этих приложений. Преимущество этого средства в том, что вам НЕ НУЖНО установить Visual Basic на наш ПК, однако установка Office неявно поможет в достижении этой цели.

Вы можете использовать VBA во всех офисных версиях, от MS-Office 97 до MS-Office 2013, а также с любой из последних доступных версий. Среди VBA Excel VBA является самым популярным. Преимущество использования VBA в том, что вы можете создавать очень мощные инструменты в MS Excel, используя линейное программирование.

Применение VBA

Вы можете задаться вопросом, зачем использовать VBA в Excel, поскольку MS-Excel сам по себе предоставляет множество встроенных функций. MS-Excel предоставляет только основные встроенные функции, которых может быть недостаточно для выполнения сложных вычислений. При таких обстоятельствах VBA становится наиболее очевидным решением.

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

Доступ к редактору VBA

В окне Excel нажмите «ALT + F11». Откроется окно VBA, как показано на следующем снимке экрана.

В этой главе вы узнаете, как написать простой макрос шаг за шагом.

Шаг 1. Сначала включите меню «Разработчик» в Excel 20XX. Чтобы сделать то же самое, нажмите Файл → Параметры.

Шаг 2 — Нажмите «Настроить ленту» и выберите «Разработчик». Нажмите «ОК».

Шаг 3 — Лента «Разработчик» появляется в строке меню.

Шаг 4 — Нажмите кнопку «Visual Basic», чтобы открыть редактор VBA.

Шаг 5 — Запустите сценарий, добавив кнопку. Нажмите Вставить → Выберите кнопку.

Шаг 6 — Выполните щелчок правой кнопкой мыши и выберите «Свойства».

Шаг 7 — Измените имя и подпись, как показано на следующем снимке экрана.

Шаг 8 — Теперь дважды нажмите кнопку, и схема подпрограммы отобразится, как показано на следующем снимке экрана.

Шаг 9 — Начните кодирование, просто добавив сообщение.

Private Sub say_helloworld_Click()
   MsgBox "Hi"
End Sub

Шаг 10 — Нажмите кнопку, чтобы выполнить подпроцедуру. Вывод подпроцедуры показан на следующем снимке экрана.

Примечание. В следующих главах мы продемонстрируем использование простой кнопки, как описано в шагах с 1 по 10. Следовательно, важно тщательно понять эту главу.

В этой главе вы познакомитесь с обычно используемой терминологией Excel VBA. Эти термины будут использоваться в следующих модулях, поэтому важно понимать каждый из них.

Модули

Модули — это область, где написан код. Это новая рабочая тетрадь, поэтому никаких модулей нет.

Чтобы вставить модуль, перейдите к Вставка → Модуль. Как только модуль вставлен, «module1» будет создан.

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

Процедура

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

Двумя основными типами процедур являются Sub и Function.

функция

Функция — это группа многократно используемого кода, которую можно вызывать в любом месте вашей программы. Это избавляет от необходимости писать один и тот же код снова и снова. Это помогает программистам разделить большую программу на ряд небольших и управляемых функций.

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

Подпроцедур

Подпроцедуры работают аналогично функциям. Хотя подпроцедуры НЕ возвращают значение, функции могут возвращать или не возвращать значение. Подпроцедуры МОГУТ быть вызваны без ключевого слова вызова. Подпроцедуры всегда заключены в инструкции Sub и End Sub .

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

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

Комментарии в VBA обозначаются двумя способами.

• Любое утверждение, начинающееся с одинарной кавычки (‘), рассматривается как комментарий. Ниже приведен пример.

' This Script is invoked after successful login 
' Written by : TutorialsPoint 
' Return Value : True / False

REM This Script is written to Validate the Entered Input 
REM Modified by  : Tutorials point/user2

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

Синтаксис

MsgBox(prompt[,buttons][,title][,helpfile,context])

Описание параметра

• Подсказка — Обязательный параметр. Строка, которая отображается в виде сообщения в диалоговом окне. Максимальная длина подсказки составляет около 1024 символов. Если сообщение распространяется более чем на строку, то строки могут быть разделены с помощью символа возврата каретки (Chr (13)) или символа перевода строки (Chr (10)) между каждой строкой.

• Кнопки — необязательный параметр. Числовое выражение, указывающее тип отображаемых кнопок, стиль используемых значков, идентификатор кнопки по умолчанию и модальность окна сообщения. Если оставить пустым, значение по умолчанию для кнопок будет 0.

• Заголовок — необязательный параметр. Строковое выражение, отображаемое в строке заголовка диалогового окна. Если заголовок оставлен пустым, имя приложения помещается в строку заголовка.

• Файл справки — необязательный параметр. Строковое выражение, определяющее файл справки, который будет использоваться для предоставления контекстно-зависимой справки для диалогового окна.

• Контекст — необязательный параметр. Числовое выражение, обозначающее номер контекста справки, назначенный автором справки для соответствующего раздела справки. Если предоставляется контекст, также должен быть предоставлен файл справки.

Параметр Buttons может принимать любое из следующих значений:

• 0 vbOKOnly — отображает только кнопку ОК.

• 1 vbOKCancel — отображает кнопки OK и Отмена.

• 2 vbAbortRetryIgnore — отображает кнопки отмены, повторной попытки и игнорирования.

• 3 vbYesNoCancel — отображает кнопки «Да», «Нет» и «Отмена».

• 4 vBYesNo — отображает кнопки «Да» и «Нет».

• 5 vbRetryCancel — отображает кнопки «Повторить» и «Отмена».

• 16 vbCritical — отображает значок критического сообщения.

• 32 vbQuestion — отображает значок запроса предупреждения.

• 48 vbExclamation — отображает значок предупреждающего сообщения.

• 64 vbInformation — отображает значок информационного сообщения.

• 0 vbDefaultButton1 — первая кнопка по умолчанию.

• 256 vbDefaultButton2 — вторая кнопка по умолчанию.

• 512 vbDefaultButton3 — третья кнопка по умолчанию.

• 768 vbDefaultButton4 — четвертая кнопка по умолчанию.

• 0 vbApplicationModal Модальное приложение — текущее приложение не будет работать, пока пользователь не ответит на окно сообщения.

• 4096 vbSystemModal System модальный — все приложения не будут работать, пока пользователь не ответит на окно сообщения.

Вышеуказанные значения логически разделены на четыре группы: Первая группа (от 0 до 5) указывает кнопки, отображаемые в окне сообщения. Вторая группа (16, 32, 48, 64) описывает стиль отображаемого значка, третья группа (0, 256, 512, 768) указывает, какая кнопка должна быть по умолчанию, и четвертая группа (0, 4096). ) определяет модальность окна сообщения.

Возвращаемые значения

Функция MsgBox может возвращать одно из следующих значений, которое можно использовать для определения кнопки, которую пользователь нажал в окне сообщения.

• 1 — vbOK — нажата кнопка ОК
• 2 — vbCancel — Отмена была нажата
• 3 — vbAbort — нажата кнопка «Прервать»
• 4 — vbRetry — повторная попытка была нажата
• 5 — vbIgnore — щелкнули Ignore
• 6 — vbYes — Да нажали
• 7 — vbNo — нет кликали

пример

Function MessageBox_Demo() 
   'Message Box with just prompt message 
   MsgBox("Welcome")     
   
   'Message Box with title, yes no and cancel Butttons  
   int a = MsgBox("Do you like blue color?",3,"Choose options") 
   ' Assume that you press No Button  
   msgbox ("The Value of a is " & a) 
End Function

Выход

Шаг 1. Указанную выше функцию можно выполнить, нажав кнопку «Выполнить» в окне VBA или вызвав функцию из рабочей таблицы Excel, как показано на следующем снимке экрана.

Шаг 2 — Отображается окно простого сообщения с сообщением «Добро пожаловать» и кнопкой «ОК»

Шаг 3 — После нажатия OK, появляется еще одно диалоговое окно с сообщением вместе с кнопками «да, нет и отмена».

Шаг 4 — После нажатия кнопки «Нет» значение этой кнопки (7) сохраняется в виде целого числа и отображается в виде окна сообщения для пользователя, как показано на следующем снимке экрана. Используя это значение, можно понять, на какую кнопку нажал пользователь.

Функция InputBox предлагает пользователям вводить значения. После ввода значений, если пользователь нажимает кнопку ОК или нажимает клавишу ВВОД на клавиатуре, функция InputBox возвращает текст в текстовое поле. Если пользователь нажмет кнопку «Отмена», функция вернет пустую строку («»).

Синтаксис

InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile,context])

Описание параметра

• Подсказка — обязательный параметр. Строка, которая отображается в виде сообщения в диалоговом окне. Максимальная длина подсказки составляет около 1024 символов. Если сообщение распространяется более чем на строку, то строки могут быть разделены с помощью символа возврата каретки (Chr (13)) или символа перевода строки (Chr (10)) между каждой строкой.

• Заголовок — необязательный параметр. Строковое выражение, отображаемое в строке заголовка диалогового окна. Если заголовок оставлен пустым, имя приложения помещается в строку заголовка.

• По умолчанию — необязательный параметр. Текст по умолчанию в текстовом поле, который пользователь хотел бы отобразить.

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

• YPos — необязательный параметр. Положение оси Y представляет быстрое расстояние от левой стороны экрана по вертикали. Если оставить это поле пустым, поле ввода будет центрировано вертикально.

• Helpfile — необязательный параметр. Строковое выражение, определяющее файл справки, который будет использоваться для предоставления контекстной справки для диалогового окна.

• контекст — необязательный параметр. Числовое выражение, обозначающее номер контекста справки, назначенный автором справки для соответствующего раздела справки. Если предоставляется контекст, также должен быть предоставлен файл справки.

пример

Давайте вычислим площадь прямоугольника путем получения значений от пользователя во время выполнения с помощью двух полей ввода (одно для длины и одно для ширины).

Function findArea() 
   Dim Length As Double 
   Dim Width As Double 
   
   Length = InputBox("Enter Length ", "Enter a Number") 
   Width = InputBox("Enter Width", "Enter a Number") 
   findArea = Length * Width 
End Function

Выход

Шаг 1 — Чтобы выполнить то же самое, позвоните, используя имя функции, и нажмите Enter, как показано на следующем снимке экрана.

Шаг 2 — После выполнения отображается первое поле ввода (длина). Введите значение в поле ввода.

Шаг 3 — После ввода первого значения отображается второе поле ввода (ширина).

Шаг 4 — После ввода второго числа нажмите кнопку ОК. Область отображается, как показано на следующем снимке экрана.

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

• Вы должны использовать букву в качестве первого символа.

• Вы не можете использовать пробел, точку (.), Восклицательный знак (!) Или символы @, &, $, # в имени.

• Длина имени не может превышать 255 символов.

• Вы не можете использовать зарезервированные ключевые слова Visual Basic в качестве имени переменной.

Синтаксис

В VBA вам необходимо объявить переменные перед их использованием.

Dim <<variable_name>> As <<variable_type>>

Типы данных

Существует много типов данных VBA, которые можно разделить на две основные категории, а именно числовые и нечисловые типы данных.

Числовые типы данных

В следующей таблице приведены числовые типы данных и допустимый диапазон значений.

Нечисловые типы данных

В следующей таблице приведены нечисловые типы данных и допустимый диапазон значений.

пример

Давайте создадим кнопку и назовем ее «Variables_demo», чтобы продемонстрировать использование переменных.

Private Sub say_helloworld_Click()
   Dim password As String
   password = "Admin#1"

   Dim num As Integer
   num = 1234

   Dim BirthDay As Date
   BirthDay = DateValue("30 / 10 / 2020")

   MsgBox "Passowrd is " & password & Chr(10) & "Value of num is " &
      num & Chr(10) & "Value of Birthday is " & BirthDay
End Sub

Выход

После выполнения сценария выходные данные будут такими, как показано на следующем снимке экрана.

Константа — это именованная область памяти, используемая для хранения значения, которое НЕ МОЖЕТ быть изменено во время выполнения скрипта. Если пользователь пытается изменить значение константы, выполнение скрипта заканчивается ошибкой. Константы объявляются так же, как и переменные.

Ниже приведены правила именования констант.

• Вы должны использовать букву в качестве первого символа.

• Вы не можете использовать пробел, точку (.), Восклицательный знак (!) Или символы @, &, $, # в имени.

• Длина имени не может превышать 255 символов.

• Вы не можете использовать зарезервированные ключевые слова Visual Basic в качестве имени переменной.

Синтаксис

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

Const <<constant_name>> As <<constant_type>> = <<constant_value>>

пример

Давайте создадим кнопку «Constant_demo», чтобы продемонстрировать, как работать с константами.

Private Sub Constant_demo_Click() 
   Const MyInteger As Integer = 42 
   Const myDate As Date = #2/2/2020# 
   Const myDay As String = "Sunday" 
   
   MsgBox "Integer is " & MyInteger & Chr(10) & "myDate is " 
      & myDate & Chr(10) & "myDay is " & myDay  
End Sub

Выход

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

Оператор может быть определен с помощью простого выражения — 4 + 5 равно 9. Здесь 4 и 5 называются операндами, а + — операторами . VBA поддерживает следующие типы операторов —

• Арифметические Операторы
• Операторы сравнения
• Логические (или реляционные) операторы
• Операторы конкатенации

Арифматические операторы

Следующие арифметические операторы поддерживаются VBA.

Предположим, что переменная A содержит 5, а переменная B содержит 10, тогда —

Показать примеры

Операторы сравнения

В VBA поддерживаются следующие операторы сравнения.

Предположим, что переменная A содержит 10, а переменная B содержит 20, тогда —

Показать примеры

Логические операторы

Следующие логические операторы поддерживаются VBA.

Предположим, что переменная A содержит 10, а переменная B содержит 0, тогда —

Показать примеры

Операторы конкатенации

Следующие операторы конкатенации поддерживаются VBA.

Предположим, что переменная A содержит 5, а переменная B содержит 10, тогда —

Показать примеры

Предположим, переменная A = «Microsoft» и переменная B = «VBScript», тогда —

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

Принятие решений позволяет программистам контролировать ход выполнения скрипта или одного из его разделов. Выполнение регулируется одним или несколькими условными утверждениями.

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

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

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

Языки программирования предоставляют различные структуры управления, которые допускают более сложные пути выполнения.

Оператор цикла позволяет нам выполнять оператор или группу операторов несколько раз. Ниже приводится общая форма оператора цикла в VBA.

VBA предоставляет следующие типы циклов для обработки требований циклов. Нажмите на следующие ссылки, чтобы проверить их детали.

Заявления о контроле цикла

Операторы управления циклом изменяют выполнение от его нормальной последовательности. Когда выполнение выходит из области видимости, все остальные операторы в цикле НЕ выполняются.

VBA поддерживает следующие операторы управления. Нажмите на следующие ссылки, чтобы проверить их детали.

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

Синтаксис

variablename = "string"

Примеры

str1 = "string"   ' Only Alphabets
str2 = "132.45"   ' Only Numbers
str3 = "[email protected]#$;*"  ' Only Special Characters
Str4 = "[email protected]#"  ' Has all the above

Строковые функции

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

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

Функции даты

Функции времени

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

Декларация массива

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

'Method 1 : Using Dim
Dim arr1()	'Without Size

'Method 2 : Mentioning the Size
Dim arr2(5)  'Declared with size of 5

'Method 3 : using 'Array' Parameter
Dim arr3
arr3 = Array("apple","Orange","Grapes")

• Хотя размер массива указан как 5, он может содержать 6 значений, так как индекс массива начинается с нуля.

• Индекс массива не может быть отрицательным.

• Массивы VBScript могут хранить переменные любого типа в массиве. Следовательно, массив может хранить целое число, строку или символы в одной переменной массива.

Присвоение значений массиву

Значения присваиваются массиву путем указания значения индекса массива для каждого из назначаемых значений. Это может быть строка.

пример

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

Private Sub Constant_demo_Click()
   Dim arr(5)
   arr(0) = "1"           'Number as String
   arr(1) = "VBScript"    'String
   arr(2) = 100 		     'Number
   arr(3) = 2.45 		     'Decimal Number
   arr(4) = #10/07/2013#  'Date
   arr(5) = #12.45 PM#    'Time
  
   msgbox("Value stored in Array index 0 : " & arr(0))
   msgbox("Value stored in Array index 1 : " & arr(1))
   msgbox("Value stored in Array index 2 : " & arr(2))
   msgbox("Value stored in Array index 3 : " & arr(3))
   msgbox("Value stored in Array index 4 : " & arr(4))
   msgbox("Value stored in Array index 5 : " & arr(5))
End Sub

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

Value stored in Array index 0 : 1
Value stored in Array index 1 : VBScript
Value stored in Array index 2 : 100
Value stored in Array index 3 : 2.45
Value stored in Array index 4 : 7/10/2013
Value stored in Array index 5 : 12:45:00 PM

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

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

пример

В следующем примере объявляется многомерный массив с 3 строками и 4 столбцами.

Private Sub Constant_demo_Click()
   Dim arr(2,3) as Variant	' Which has 3 rows and 4 columns
   arr(0,0) = "Apple" 
   arr(0,1) = "Orange"
   arr(0,2) = "Grapes"           
   arr(0,3) = "pineapple" 
   arr(1,0) = "cucumber"           
   arr(1,1) = "beans"           
   arr(1,2) = "carrot"           
   arr(1,3) = "tomato"           
   arr(2,0) = "potato"             
   arr(2,1) = "sandwitch"            
   arr(2,2) = "coffee"             
   arr(2,3) = "nuts"            
           
   msgbox("Value in Array index 0,1 : " &  arr(0,1))
   msgbox("Value in Array index 2,2 : " &  arr(2,2))
End Sub

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

Value stored in Array index : 0 , 1 : Orange
Value stored in Array index : 2 , 2 : coffee

ReDim Заявление

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

Синтаксис

ReDim [Preserve] varname(subscripts) [, varname(subscripts)]

Описание параметра

• Сохранять — необязательный параметр, используемый для сохранения данных в существующем массиве при изменении размера последнего измерения.

• Varname — обязательный параметр, который обозначает имя переменной, которое должно соответствовать стандартным соглашениям об именах переменных.

• Subscripts — обязательный параметр, который указывает размер массива.

пример

В следующем примере массив был переопределен, а затем значения сохранены при изменении существующего размера массива.

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

Private Sub Constant_demo_Click()
   Dim a() as variant
   i = 0
   redim a(5)
   a(0) = "XYZ"
   a(1) = 41.25
   a(2) = 22
  
   REDIM PRESERVE a(7)
   For i = 3 to 7
   a(i) = i
   Next
  
   'to Fetch the output
   For i = 0 to ubound(a)
      Msgbox a(i)
   Next
End Sub

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

XYZ
41.25
22
3
4
5
6
7

Методы массива

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

Функция — это группа многократно используемого кода, который можно вызывать в любом месте вашей программы. Это избавляет от необходимости писать один и тот же код снова и снова. Это позволяет программистам разделить большую программу на ряд небольших и управляемых функций.

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

Определение функции

Функция VBA может иметь необязательный оператор возврата. Это необходимо, если вы хотите вернуть значение из функции.

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

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

Прежде чем использовать функцию, нам нужно определить эту конкретную функцию. Наиболее распространенный способ определения функции в VBA — использование ключевого слова Function , за которым следует уникальное имя функции, которое может содержать или не содержать список параметров и оператор с ключевым словом End Function , который указывает конец функции. Ниже приведен основной синтаксис.

Синтаксис

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

Function Functionname(parameter-list)
   statement 1
   statement 2
   statement 3
   .......
   statement n
End Function

пример

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

Function findArea(Length As Double, Optional Width As Variant)
   If IsMissing(Width) Then
      findArea = Length * Length
   Else
      findArea = Length * Width
   End If
End Function

Вызов функции

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

Вывод области, как показано ниже, будет отображаться пользователю.

Подпроцедуры похожи на функции, однако есть несколько отличий.

• Подпроцедуры НЕ возвращают значение, в то время как функции могут возвращать или не возвращать значение.

• Подпроцедуры МОГУТ быть вызваны без ключевого слова вызова.

• Подпроцедуры всегда заключены в инструкции Sub и End Sub.

пример

Sub Area(x As Double, y As Double)
   MsgBox x * y
End Sub

Процедуры вызова

Чтобы вызвать процедуру где-нибудь в скрипте, вы можете сделать вызов из функции. Мы не сможем использовать тот же способ, что и для функции, так как подпроцедура НЕ возвратит значение.

Function findArea(Length As Double, Width As Variant)
   area Length, Width    ' To Calculate Area 'area' sub proc is called
End Function

Теперь вы сможете вызывать только функцию, но не процедуру, как показано на следующем снимке экрана.

Площадь рассчитывается и отображается только в окне сообщения.

В ячейке результата отображается НОЛЬ, так как значение области НЕ возвращается из функции. Короче говоря, вы не можете сделать прямой вызов подпроцедуры из листа Excel.

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

• События рабочего листа
• Рабочая тетрадь События

События рабочего листа

События рабочего листа запускаются при изменении рабочего листа. Он создается путем щелчка правой кнопкой мыши на вкладке листа и выбора «просмотра кода», а затем вставки кода.

Пользователь может выбрать каждый из этих рабочих листов и выбрать «Рабочий лист» из выпадающего списка, чтобы получить список всех поддерживаемых событий рабочего листа.

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

Private Sub Worksheet_Activate() 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)    
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) 
Private Sub Worksheet_Calculate() 
Private Sub Worksheet_Change(ByVal Target As Range) 
Private Sub Worksheet_Deactivate() 
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

пример

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

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   MsgBox "Before Double Click"
End Sub

Выход

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

Рабочая тетрадь События

События рабочей книги инициируются, когда происходит изменение рабочей книги в целом. Мы можем добавить код для событий рабочей книги, выбрав «ThisWorkbook» и выбрав «рабочая книга» из выпадающего списка, как показано на следующем снимке экрана. Немедленно подпрограмма Workbook_open отображается пользователю, как показано на следующем снимке экрана.

Ниже приведены поддерживаемые события Workbook, которые могут быть добавлены пользователем.

Private Sub Workbook_AddinUninstall() 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
Private Sub Workbook_BeforePrint(Cancel As Boolean) 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
Private Sub Workbook_Deactivate() 
Private Sub Workbook_NewSheet(ByVal Sh As Object) 
Private Sub Workbook_Open() 
Private Sub Workbook_SheetActivate(ByVal Sh As Object) 
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) 
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) 
Private Sub Workbook_SheetCalculate(ByVal Sh As Object) 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) 
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink) 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) 
Private Sub Workbook_WindowActivate(ByVal Wn As Window) 
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window) 
Private Sub Workbook_WindowResize(ByVal Wn As Window)

пример

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

Private Sub Workbook_NewSheet(ByVal Sh As Object)
   MsgBox "New Sheet Created Successfully"
End Sub

Выход

После создания нового листа Excel пользователю отображается сообщение, как показано на следующем снимке экрана.

Существует три типа ошибок в программировании: (a) синтаксические ошибки, (b) ошибки времени выполнения и (c) логические ошибки.

Синтаксические ошибки

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

Function ErrorHanlding_Demo()
   dim x,y
   x = "Tutorialspoint"
   y = Ucase(x
End Function

Ошибки во время выполнения

Ошибки во время выполнения, также называемые исключениями, возникают во время выполнения после интерпретации.

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

Function ErrorHanlding_Demo1()
   Dim x,y
   x = 10
   y = 20
   z = fnadd(x,y)
   a = fnmultiply(x,y)
End Function

Function fnadd(x,y)
   fnadd = x + y
End Function

Логические ошибки

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

Вы не можете поймать эти ошибки, потому что это зависит от требований вашего бизнеса, какую логику вы хотите использовать в своей программе.

Например, деление числа на ноль или написанный скрипт, который входит в бесконечный цикл.

Err Object

Предположим, если у нас есть ошибка во время выполнения, тогда выполнение останавливается, отображая сообщение об ошибке. Как разработчик, если мы хотим зафиксировать ошибку, то используется Error Object.

пример

В следующем примере Err.Number дает номер ошибки, а Err.Description — описание ошибки.

Err.Raise 6   ' Raise an overflow error.
MsgBox "Error # " & CStr(Err.Number) & " " & Err.Description
Err.Clear   ' Clear the error.

Обработка ошибок

VBA включает подпрограмму обработки ошибок, а также может использоваться для отключения подпрограммы обработки ошибок. Без оператора On Error любая ошибка во время выполнения является фатальной: отображается сообщение об ошибке, и выполнение резко останавливается.

On Error { GoTo [ line | 0 | -1 ] | Resume Next }

пример

Public Sub OnErrorDemo()
   On Error GoTo ErrorHandler   ' Enable error-handling routine.
   Dim x, y, z As Integer
   x = 50
   y = 0
   z = x / y   ' Divide by ZERO Error Raises
  
   ErrorHandler:    ' Error-handling routine.
   Select Case Err.Number   ' Evaluate error number.
      Case 10   ' Divide by zero error
         MsgBox ("You attempted to divide by zero!")
      Case Else
         MsgBox "UNKNOWN ERROR  - Error# " & Err.Number & " : " & Err.Description
   End Select
   Resume Next
End Sub

При программировании с использованием VBA есть несколько важных объектов, с которыми пользователь будет иметь дело.

• Объекты приложения
• Объекты рабочей книги
• Объекты рабочего листа
• Диапазон объектов

Объекты приложения

Объект Application состоит из следующих элементов:

• Общие для приложения настройки и параметры.
• Методы, которые возвращают объекты верхнего уровня, такие как ActiveCell, ActiveSheet и т. Д.

пример

'Example 1 :
Set xlapp = CreateObject("Excel.Sheet") 
xlapp.Application.Workbooks.Open "C:\test.xls"

'Example 2 :
Application.Windows("test.xls").Activate

'Example 3:
Application.ActiveCell.Font.Bold = True

Объекты рабочей книги

Объект Workbook является членом коллекции Workbooks и содержит все объекты Workbook, открытые в настоящее время в Microsoft Excel.

пример

'Ex 1 : To close Workbooks
Workbooks.Close

'Ex 2 : To Add an Empty Work Book
Workbooks.Add

'Ex 3: To Open a Workbook
Workbooks.Open FileName:="Test.xls", ReadOnly:=True

'Ex : 4 - To Activate WorkBooks
Workbooks("Test.xls").Worksheets("Sheet1").Activate

Объекты рабочего листа

Объект Worksheet является членом коллекции Worksheets и содержит все объекты Worksheet в рабочей книге.

пример

'Ex 1 : To make it Invisible
Worksheets(1).Visible = False

'Ex 2 : To protect an WorkSheet
Worksheets("Sheet1").Protect password:=strPassword, scenarios:=True

Диапазон объектов

Объекты Range представляют ячейку, строку, столбец или выборку ячеек, содержащих один или несколько непрерывных блоков ячеек.

'Ex 1 : To Put a value in the cell A5
Worksheets("Sheet1").Range("A5").Value = "5235"

'Ex 2 : To put a value in range of Cells
Worksheets("Sheet1").Range("A1:A4").Value = 5

Вы также можете прочитать файл Excel и записать содержимое ячейки в текстовый файл с помощью VBA. VBA позволяет пользователям работать с текстовыми файлами двумя способами:

• Объект файловой системы
• используя команду записи

Объект файловой системы (FSO)

Как следует из названия, FSO помогают разработчикам работать с дисками, папками и файлами. В этом разделе мы обсудим, как использовать FSO.

Водить машину

Диск — это объект, который обеспечивает доступ к свойствам определенного диска или общей сетевой папки. Следующие свойства поддерживаются объектом Drive —

• Доступное пространство
• DriveLetter
• Тип вождения
• Файловая система
• Свободное место
• Готово
• Путь
• Корневая папка
• Серийный номер
• ShareName
• Общий размер
• VolumeName

пример

Шаг 1. Прежде чем приступить к написанию сценариев с использованием FSO, мы должны включить Microsoft Scripting Runtime. Чтобы сделать то же самое, перейдите в Инструменты → Ссылки, как показано на следующем снимке экрана.

Шаг 2. Добавьте «Microsoft Scripting RunTime» и нажмите «ОК».

Шаг 3 — Добавьте данные, которые вы хотели бы записать в текстовый файл и добавьте командную кнопку.

Шаг 4 — Теперь пришло время написать сценарий.

Private Sub fn_write_to_text_Click()
   Dim FilePath As String
   Dim CellData As String
   Dim LastCol As Long
   Dim LastRow As Long
  
   Dim fso As FileSystemObject
   Set fso = New FileSystemObject
   Dim stream As TextStream
  
   LastCol = ActiveSheet.UsedRange.Columns.Count
   LastRow = ActiveSheet.UsedRange.Rows.Count
    
   ' Create a TextStream.
   Set stream = fso.OpenTextFile("D:\Try\Support.log", ForWriting, True)
  
   CellData = ""
  
   For i = 1 To LastRow
      For j = 1 To LastCol
         CellData = Trim(ActiveCell(i, j).Value)
         stream.WriteLine "The Value at location (" & i & "," & j & ")" & CellData
      Next j
   Next i
  
   stream.Close
   MsgBox ("Job Done")
End Sub

Выход

При выполнении сценария убедитесь, что курсор помещен в первую ячейку рабочего листа. Файл Support.log создается, как показано на следующем снимке экрана в разделе «D: \ Try».

Содержимое файла показано на следующем снимке экрана.

Написать команду

В отличие от FSO, нам НЕ нужно добавлять ссылки, однако мы НЕ сможем работать с дисками, файлами и папками. Мы сможем просто добавить поток в текстовый файл.

пример

Private Sub fn_write_to_text_Click()
   Dim FilePath As String
   Dim CellData As String
   Dim LastCol As Long
   Dim LastRow As Long
  
   LastCol = ActiveSheet.UsedRange.Columns.Count
   LastRow = ActiveSheet.UsedRange.Rows.Count
    
   FilePath = "D:\Try\write.txt"
   Open FilePath For Output As #2
  
   CellData = ""
   For i = 1 To LastRow
      For j = 1 To LastCol
         CellData = "The Value at location (" & i & "," & j & ")" & Trim(ActiveCell(i, j).Value)
         Write #2, CellData
      Next j
   Next i
  
   Close #2
   MsgBox ("Job Done")
End Sub

Выход

После выполнения сценария файл «write.txt» создается в папке «D: \ Try», как показано на следующем снимке экрана.

Содержимое файла показано на следующем снимке экрана.

Используя VBA, вы можете создавать графики на основе определенных критериев. Давайте посмотрим на это на примере.

Шаг 1 — Введите данные, по которым должен быть сгенерирован график.

Шаг 2. Создайте 3 кнопки — одну для создания гистограммы, другую для создания круговой диаграммы и другую для создания столбчатой диаграммы.

Шаг 3 — Разработайте макрос, чтобы сгенерировать каждый из этих типов диаграмм.

' Procedure to Generate Pie Chart
Private Sub fn_generate_pie_graph_Click()
   Dim cht As ChartObject
   For Each cht In Worksheets(1).ChartObjects
      cht.Chart.Type = xlPie
   Next cht
End Sub

' Procedure to Generate Bar Graph
Private Sub fn_Generate_Bar_Graph_Click()
   Dim cht As ChartObject
   For Each cht In Worksheets(1).ChartObjects
      cht.Chart.Type = xlBar
   Next cht
End Sub

' Procedure to Generate Column Graph
Private Sub fn_generate_column_graph_Click()
   Dim cht As ChartObject
   For Each cht In Worksheets(1).ChartObjects
      cht.Chart.Type = xlColumn
   Next cht
End Sub

Шаг 4 — При нажатии на соответствующую кнопку создается диаграмма. В следующем выводе нажмите на кнопку «Сгенерировать круговую диаграмму».

Форма пользователя — это настраиваемое диалоговое окно, которое делает ввод пользовательских данных более управляемым и более простым в использовании для пользователя. В этой главе вы научитесь разрабатывать простую форму и добавлять данные в Excel.

Шаг 1 — Перейдите к окну VBA, нажав Alt + F11 и перейдите к меню «Вставить» и выберите «Форма пользователя». После выбора форма пользователя отображается, как показано на следующем снимке экрана.

Шаг 2 — Разработка форм с использованием заданных элементов управления.

Шаг 3 — После добавления каждого элемента управления, элементы управления должны быть названы. Заголовок соответствует тому, что отображается в форме, а имя соответствует логическому имени, которое будет появляться при написании кода VBA для этого элемента.

Шаг 4 — Ниже приведены имена против каждого из добавленных элементов управления.

Шаг 5 — Добавьте код для события загрузки формы, выполнив щелчок правой кнопкой мыши на форме и выбрав «Просмотреть код».

Шаг 6 — Выберите «Userform» из выпадающего списка объектов и выберите метод «Initialize», как показано на следующем снимке экрана.

Шаг 7 — После загрузки формы убедитесь, что текстовые поля очищены, раскрывающиеся окна заполнены и переключатели сброшены.

Private Sub UserForm_Initialize()
   'Empty Emp ID Text box and Set the Cursor 
   txtempid.Value = ""
   txtempid.SetFocus
   
   'Empty all other text box fields
   txtfirstname.Value = ""
   txtlastname.Value = ""
   txtemailid.Value = ""
   
   'Clear All Date of Birth Related Fields
   cmbdate.Clear
   cmbmonth.Clear
   cmbyear.Clear
   
   'Fill Date Drop Down box - Takes 1 to 31
   With cmbdate
      .AddItem "1"
      .AddItem "2"
      .AddItem "3"
      .AddItem "4"
      .AddItem "5"
      .AddItem "6"
      .AddItem "7"
      .AddItem "8"
      .AddItem "9"
      .AddItem "10"
      .AddItem "11"
      .AddItem "12"
      .AddItem "13"
      .AddItem "14"
      .AddItem "15"
      .AddItem "16"
      .AddItem "17"
      .AddItem "18"
      .AddItem "19"
      .AddItem "20"
      .AddItem "21"
      .AddItem "22"
      .AddItem "23"
      .AddItem "24"
      .AddItem "25"
      .AddItem "26"
      .AddItem "27"
      .AddItem "28"
      .AddItem "29"
      .AddItem "30"
      .AddItem "31"
   End With
   
   'Fill Month Drop Down box - Takes Jan to Dec
   With cmbmonth
      .AddItem "JAN"
      .AddItem "FEB"
      .AddItem "MAR"
      .AddItem "APR"
      .AddItem "MAY"
      .AddItem "JUN"
      .AddItem "JUL"
      .AddItem "AUG"
      .AddItem "SEP"
      .AddItem "OCT"
      .AddItem "NOV"
      .AddItem "DEC"
   End With
   
   'Fill Year Drop Down box - Takes 1980 to 2014
   With cmbyear
      .AddItem "1980"
      .AddItem "1981"
      .AddItem "1982"
      .AddItem "1983"
      .AddItem "1984"
      .AddItem "1985"
      .AddItem "1986"
      .AddItem "1987"
      .AddItem "1988"
      .AddItem "1989"
      .AddItem "1990"
      .AddItem "1991"
      .AddItem "1992"
      .AddItem "1993"
      .AddItem "1994"
      .AddItem "1995"
      .AddItem "1996"
      .AddItem "1997"
      .AddItem "1998"
      .AddItem "1999"
      .AddItem "2000"
      .AddItem "2001"
      .AddItem "2002"
      .AddItem "2003"
      .AddItem "2004"
      .AddItem "2005"
      .AddItem "2006"
      .AddItem "2007"
      .AddItem "2008"
      .AddItem "2009"
      .AddItem "2010"
      .AddItem "2011"
      .AddItem "2012"
      .AddItem "2013"
      .AddItem "2014"
   End With
   
   'Reset Radio Button. Set it to False when form loads.
   radioyes.Value = False
   radiono.Value = False

End Sub

Шаг 8 — Теперь добавьте код в кнопку «Отправить». Нажав кнопку «Отправить», пользователь сможет добавить значения в таблицу.

Private Sub btnsubmit_Click()
   Dim emptyRow As Long
  
   'Make Sheet1 active
   Sheet1.Activate
  
   'Determine emptyRow
   emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
  
   'Transfer information
   Cells(emptyRow, 1).Value = txtempid.Value
   Cells(emptyRow, 2).Value = txtfirstname.Value
   Cells(emptyRow, 3).Value = txtlastname.Value
   Cells(emptyRow, 4).Value = cmbdate.Value & "/" & cmbmonth.Value & "/" & cmbyear.Value
   Cells(emptyRow, 5).Value = txtemailid.Value
  
   If radioyes.Value = True Then
      Cells(emptyRow, 6).Value = "Yes"
   Else
      Cells(emptyRow, 6).Value = "No"
   End If
End Sub

Шаг 9 — Добавьте метод, чтобы закрыть форму, когда пользователь нажимает кнопку Отмена.

Private Sub btncancel_Click()
   Unload Me
End Sub

Шаг 10 — Заполните форму, нажав кнопку «Выполнить». Введите значения в форму и нажмите кнопку «Отправить». Автоматически значения будут перетекать в лист, как показано на следующем снимке экрана.

Функция VBA InStrRev — поиск текста справа

В этом руководстве будет показано, как использовать функцию InStrRev VBA для поиска текста справа.

Функция InStrRev

Функция VBA InStrRev ищет позицию подстроки внутри строки. Он возвращает отсчет позиции слева направо, но выполняет поиск от конца строки к началу.

VBA InStrRev Найти подстроку

Функцию VBA InStrRev можно использовать для поиска позиции подстроки внутри строки.

 Sub InStrRevExample_1 ()
MsgBox InStrRev ("ABCABC", "C") 'Результат: 6
MsgBox InStrRev ("ABCABC", "BC") 'Результат: 5
MsgBox InStrRev ("La La Land", "L") 'Результат: 7
MsgBox InStrRev ("La La Land", "La") 'Результат: 7
MsgBox InStrRev ("La La Land", "La") 'Результат: 4
MsgBox InStrRev ("La La Land", "M") 'Результат: 0
Конец подписки
 

AutoMacro — Генератор кода VBA

Исходное положение VBA InStrRev

VBA InStrRev Функция может начать поиск подстроки с назначенной позиции, считая слева направо.

 Sub InStrRevExample_2 ()
MsgBox InStrRev ("La La Land", "L") 'Результат: 7
MsgBox InStrRev ("La La Land", "L", 8) 'Результат: 7
MsgBox InStrRev ("La La Land", "L", 7) 'Результат: 7
MsgBox InStrRev ("La La Land", "L", 6) 'Результат: 4
MsgBox InStrRev ("La La Land", "L", 4) 'Результат: 4
MsgBox InStrRev ("La La Land", "L", 3) 'Результат: 1
Конец подписки
 

VBA InStrRev Чувствительность к регистру

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

 Подложка InStrRevExample_3 ()
'InstrRev по умолчанию чувствителен к регистру
MsgBox InStrRev ("La La Land", "L") 'Результат: 7
MsgBox InStrRev ("La La Land", "l") 'Результат: 0

InstrRev может выполнять поиск без учета регистра
MsgBox InStrRev ("La La Land", "L", -1, vbTextCompare) 'Результат: 7
MsgBox InStrRev ("La La Land", "l", -1, vbTextCompare) 'Результат: 7
Конец подписки
 

VBA InStrRev Последнее пространство

Функция VBA InStrRev может найти позицию последнего пробела в строке.

 Sub InStrRevExample_4 ()
MsgBox InStrRev ("Ла-ла-Лэнд", "")
Результат: 6
MsgBox InStrRev ("Леонардо да Винчи", "")
Результат: 12
MsgBox InStrRev («Да пребудет с тобой Сила», «»)
Результат: 22

Конец подписки
 

VBA InStrRev От второго до последнего места

Функция VBA InStrRev может найти положение предпоследнего пробела в строке. Мы можем использовать функцию VBA InStrRev, чтобы найти последний пробел в строке, а затем мы можем снова использовать InStrRev с новой начальной точкой, чтобы найти позицию от второго до последнего пробела

 Sub InStrRevExample_5 ()
Dim LastPos как целое число

LastPos = InStrRev («Да пребудет с тобой Сила», «»)
Результат MsgBox LastPos: 22

Dim SecondLastPos как целое число

SecondLastPos = InStrRev («Да пребудет с тобой Сила», «», LastPos - 1)
Результат MsgBox SecondLastPos: 17
'InStrRev начал поиск места незадолго до последнего места
'Он найдет предпоследний пробел, потому что ищет справа налево
Конец подписки
 

VBA InStrRev Имя файла возврата

Функция VBA InStrRev может использоваться для простого возврата имени файла из строки, содержащей путь и имя файла.Мы будем использовать функцию InStrRev с функциями VBA Len и VBA Right.

Функция VBA Len возвращает длину строки:

 MsgBox Len ("XBCX") 'Результат: 4 

Правая функция VBA возвращает n символов с конца строки:

 MsgBox Right ("ABCDE", 2) 'Результат: DE 

Мы можем использовать функцию InStrRev, чтобы найти последнее вхождение символа «\» в имени пути, и использовать функцию Len для вычисления длины имени файла. Затем право может извлечь имя файла.

 Sub InStrRevExample_4 ()
Dim PathEx как строка
PathEx = "C: \ MyFiles \ Other \ UsefulFile.pdf"

Dim FilenameEx как строка


FilenameEx = Right (PathEx, Len (PathEx) - InStrRev (PathEx, "\"))
MsgBox FilenameEx 'Результат: UsefulFile.pdf

'Давайте разберем этот код
MsgBox Len (PathEx)
'Это длина имени пути (31)


MsgBox InStrRev (PathEx, "\")
'Это позиция последнего \ в строке

MsgBox Len (PathEx) - InStrRev (PathEx, "\")
'Это длина имени файла (14)
'Разница между полной длиной и положением последнего \ в строке
'(31-27 = 14)

'Теперь мы знаем длину имени файла и, конечно же, в конце
'Правая функция идеально подходит для этой ситуации
FilenameEx = Right (PathEx, Len (PathEx) - InStrRev (PathEx, "\"))

Концевой переводник 

Функция InStrRev (Visual Basic для приложений)

• 13.12.2018
• 2 минуты на чтение

В этой статье

Возвращает позицию вхождения одной строки в другую, начиная с конца строки.

Синтаксис

InstrRev ( stringcheck , stringmatch , [ start , [ compare ]])

Синтаксис функции InstrRev имеет следующие именованные аргументы:

Настройки

Аргумент compare может иметь следующие значения:

Возвращаемые значения

InStrRev возвращает следующие значения:

Примечания

Обратите внимание, что синтаксис функции InstrRev отличается от синтаксиса функции Instr .

InstrRev не найдет экземпляр stringmatch , если только позиция конечного символа stringmatch меньше или равна start .

См. Также

Поддержка и отзывы

У вас есть вопросы или отзывы об Office VBA или этой документации? См. Раздел Поддержка и отзывы Office VBA, чтобы узнать, как получить поддержку и оставить отзыв.

Как использовать функцию INSTRREV (VBA)

В этом руководстве Excel объясняется, как использовать функцию INSTRREV в Excel с синтаксисом и примерами.

Описание

Функция Microsoft Excel INSTRREV возвращает позицию первого вхождения строки в другую строку, начиная с конца строки. Это похоже на функцию INSTR, которая возвращает позицию первого вхождения, начиная с начала строки.

Функция INSTRREV — это встроенная функция Excel, относящаяся к категории String / Text Function . Его можно использовать как функцию VBA (VBA) в Excel.В качестве функции VBA вы можете использовать эту функцию в коде макроса, который вводится через редактор Microsoft Visual Basic Editor.

Синтаксис

Синтаксис функции INSTRREV в Microsoft Excel:

 InStrRev (строка, подстрока [, начало [, сравнить]]) 

Параметры или аргументы

строка

Строка для поиска.

подстрока

Подстрока, которую вы хотите найти.

начало

Необязательно.Это начальная позиция для поиска. Если этот параметр опущен, поиск начнется с позиции -1, которая является позицией последнего символа.

сравнить

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

Константа VBA	Значение	Пояснение
vbUseCompareOption	–1	Использует вариант сравнения
vbBinaryСравнить	0	Двоичное сравнение
vbTextСравнить	1	Текстовое сравнение

Возвращает

Функция INSTRREV возвращает числовое значение.
Если строка2 не найдена в пределах string_being_searched , функция INSTRREV вернет 0.
Если string_being_searched имеет нулевую длину, функция INSTRREV вернет 0.
Если string_being_searched имеет значение null, функция INSTRREV вернет значение null. ноль.
Если start имеет значение null, функция INSTRREV вернет #Error.

Относится к

• Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Пример (как функция VBA)

Функцию INSTRREV можно использовать только в коде VBA в Microsoft Excel.

Давайте посмотрим на некоторые примеры функции INSTRREV в Excel и узнаем, как использовать функцию INSTRREV в коде Excel VBA:

 InStrRev («алфавит», «а»)
  Результат:  5

InStrRev («алфавит», «а», -1)
  Результат:  5

InStrRev («алфавит», «а», 1)
  Результат:  1

InStrRev («алфавит», «а», 2)
  Результат:  1

InStrRev («алфавит», «а», 3)
  Результат:  1

InStrRev («алфавит», «а», 4)
  Результат:  1

InStrRev («алфавит», «а», 5)
  Результат:  5

InStrRev («алфавит», «а», 6)
  Результат:  5

InStrRev («алфавит», «а», 7)
  Результат:  5

InStrRev («алфавит», «а», 8)
  Результат:  5

InStrRev («алфавит», «а», 9)
  Результат:  0 

Например:

 Dim LP Position As Integer (Тусклое положение LP как целое число)

LPosition = InStrRev ("алфавит", "а") 

В этом примере переменная LPosition теперь будет содержать значение 5.

VBA InStr и InStrRev — Как использовать

Как следует из названия Функция InStr VBA ищет подстроку, встроенную в другую строку, а затем возвращает ее начальную позицию.

Если не удается найти строку внутри родительской строки, возвращается 0. Это очень важная функция среди строковых функций в VBA. Вероятно, это лучшая функция для выполнения операций поиска строк в VBA.

В этом посте я объясню функции InStr и InStrRev в VBA на примерах:

Синтаксис функции VBA Instr:

Синтаксис Instr следующий:

Instr ([начало], parent_string, substring, [сравнить])

Здесь «начало» относится к позиции, с которой должен начинаться поиск.Это необязательный параметр, и если он не указан, то поиск строки начинается с 1 ^st позиции внутри «parent_string»

«parent_string» — это строка, внутри которой должна быть найдена «подстрока».

«подстрока» — это строка, которая должна быть найдена внутри «родительской_строки».

«Сравнить» определяет алгоритм сравнения, с помощью которого должен производиться поиск. Это также необязательный параметр. В расширенных случаях параметр сравнения может иметь любое из следующих значений:

Несколько важных моментов о функции Instr:

• Если любая из строк, например, parent_string или substring, Null .Тогда инструкция VBA Instr возвращает Null и, следовательно, может привести к поломке кода. Итак, перед передачей какого-либо значения в функцию Instr проверьте, действительно ли значение. Также убедитесь, что вы выполнили правильную обработку ошибок в коде.
• Возвращает параметр «начало», если параметр «подстрока» пуст.
• Возвращает 0, если «подстрока» отсутствует внутри «родительской_строки».
• Если значение параметра start больше, чем длина parent_string, то функция Instr также возвращает 0.

Примеры функции Instr:

Теперь давайте рассмотрим несколько примеров функции VBA Instr:

1. InStr («Excel-Trick», «Trick») вернет результат 7 в виде строки «Trick» начинается с седьмой позиции внутри «parent_string», то есть «Excel-Trick».

2. InStr ("Excel-Trick", "c") вернет 3, поскольку символ «c» первым идет на третьей позиции внутри «parent_string».

3. InStr (4, "Excel-Trick", "c") приведет к 10, поскольку здесь мы начинаем поиск «c» с 4 ^{-го символа} , и, следовательно, Instr дает нам позицию вторая буква «c» (Excel-Tri c k) в строке parent_string.

4. InStr («Аллигатор», «z») приведет к 0, поскольку в родительской строке нет символа «z».

5. InStr (1, «Аллигатор», «а») приведет к 6, поскольку строчный символ «а» присутствует в позиции 6 внутри слова «Аллигатор».

6. InStr (1, «Аллигатор», «a», vbTextCompare) приведет к 1, поскольку здесь мы использовали «vbTextCompare», который игнорирует разницу между символами верхнего и нижнего регистра.

Использование vbTextCompare — не лучший способ:

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

Например:

InStr (1, «Ætna», «et», vbTextCompare) приводит к 1, потому что символ «Æ» — латинский символ, обозначающий «et». Следовательно, vbTextCompare рассматривает «Æ» как «et», такое поведение vbTextCompare вводит в заблуждение.

Итак, в случаях, когда вы имеете дело с символами без учета регистра, как в примере 6, лучшим способом будет:

InStr (LCase ("Alligator"), LCase ("a"))

Здесь Функция LCase преобразует строки в символы нижнего регистра, а результат инструкции InStr — в 1.

Функция обратного поиска строки в VBA: InStrRev

Функция InStrRev аналогична функции VBA InStr с той лишь разницей, что она начинает поиск «подстроки» внутри «родительской_строки» от конца до начала.

Синтаксис InStrRev следующий:

InStrRev (родительская_строка, подстрока, [начало], [сравнение])

Здесь также «родительская_строка» относится к строке, в которой «подстрока» должна быть найдена. .

«подстрока» — это строка, в которой выполняется поиск внутри «родительской_строки».

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

Параметр «сравнить» относится к типу сравнения, например: vbBinaryCompare, vbTextCompare и

vbDatabaseCompare.

Примечание: Обратите внимание на разницу в месте параметров функций InStr и InStrRev.

Пример функции InStrRev:

InStrRev («aligator», «a») приводит к 5. Функция InStrRev начинает поиск символа «a» внутри «parent_string» в обратном направлении и при обнаружении первого «a» ”(В обратном направлении) он проверяет свое положение в прямом направлении и выдает его в качестве результата.

Практическое использование InStrRev может заключаться в нахождении последнего индекса символа внутри строки.

Образец макроса, имеющий как VBA InStr, так и VBA InStrRev:

Ниже приводится очевидный макрос, в котором я использовал функции InStr и InStrRev:

 Sub SearchStrings () Start_pos = InStr ("aesthesia", " ") Last_pos = InStrRev (" aesthesia "," a ") MsgBox" InStr Output = "& Start_pos & vbNewLine &" InStrRev Output = "& Last_posEnd Sub 

Итак, это все о функциях InStr и InStrRev в VBA.Сообщите нам, если у вас есть какие-либо сомнения или вопросы, связанные с этой темой.

VBA: функция InStrRev — ссылка VBA

Функция VBA InStrRev возвращает позицию индекса первого вхождения строки в предоставленной строке , если смотреть с правого конца строки. Возвращаемый результат — размещение строки, смотрящей спереди.

'InStrRev
Debug.Print InStrRev ("abcdefabc", "a") 'Результат: 7
'Потому что он обнаружил, что это первая буква "а" как третья с конца строки.'Результат - положение второй буквы «а», смотрящей спереди строки.

'InStr
Debug.Print InStr ("abcdefabc", "a") 'Результат: 1
'Потому что он обнаружил, что это первая буква "а" как первая от начала строки.
 

Используйте InStr для возврата позиции индекса первого вхождения, если смотреть с начала конца строки (слева).

Синтаксис VBA InStrRev

Синтаксис функции InStrRev в VBA:

 InStrRev (строка, подстрока [, начало [, сравнить]]) 

Параметры

строка
Строка для поиска.

подстрока
Подстрока, которую вы хотите найти.

начало
Необязательно. Начальная позиция индекса для функции InStr. Если он не указан, поиск начнется с позиции индекса -1, которая является последней позицией индекса символа в строке (гипотетически — длина строки + 1).

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

Прочие примечания

Функция InStrRev вернет 0, если подстрока не найдена в строке .

Позиции символов строки в VBA индексируются, начиная с 1. См. Примеры ниже.

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

Функцию InStrRev можно использовать только в коде VBA. Давайте посмотрим на некоторые примеры функций VBA InStrRev:

 InStrRev «Привет, мир!», «Мир»
Результат: 7

InStrRev "Вау, вау, вау", "Вау"
Результат: 11

InStrRev «Привет!», «Там», -1, vbTextCompare
Результат: 4

InStrRev «Привет!», «Там», -1, vbBinaryCompare
Результат: 0

InStr 1, «Привет!», «Там», -1, vbBinaryCompare)
Результат: 4
 

Функция VBA InStrRev | Объяснение с примерами синтаксиса

Обзор функции VBA InStrRev:

Функция VBA InStrRev относится к категории текстовых / строковых функций в VBA.Это встроенная функция в MS Office Excel. Он возвращает позицию подстроки в строке, выполняя поиск справа налево. У него есть два обязательных параметра и два необязательных параметра. Если подстрока не найдена, функция возвращает значение «0». Если параметр Start равен Null, функция возвращает ошибку. Эта функция может использоваться как функция VBA и не может использоваться как функция листа Excel. Функцию InStrRev можно использовать либо в процедуре, либо в функции в окне редактора VBA в Excel.Мы можем использовать эту функцию VBA InStrRev любое количество раз в любом количестве процедур или функций. В следующем разделе мы узнаем, каков синтаксис и параметры функции InStrRev, где мы можем использовать эту функцию InStrRev и примеры в реальном времени в VBA.

Содержание:

Синтаксис функции VBA InStrRev

Синтаксис функции VBA InStrRev:

  InStrRev (StringCheck, StringMatch, [Начало], [Сравнить]) 
 

Примечание. Эта функция InStrRev возвращает значение.

Параметры или аргументы

Эта функция имеет один обязательный параметр и три дополнительных параметра для функции InStrRev.
Где
StringCheck: Выражение является обязательным аргументом. Он представляет собой строку, в которой вы хотите выполнить поиск.
StringMatch: Выражение является обязательным аргументом. Он представляет собой подстроку, которую вы хотите найти.
Начало: FirstDayOfWeek — необязательный аргумент. Он представляет собой начальную позицию, с которой вы хотите начать поиск.Значение этого параметра по умолчанию — «-1». Если вы проигнорируете этот аргумент, он будет считать значение по умолчанию.
Сравните: FirstWeekOfYear — необязательный аргумент. Он представляет собой тип сравнения при выполнении поиска. Сравнение по умолчанию — vbBinaryCompare. Вот сравнительная таблица.

Где мы можем применить или использовать функцию VBA InStrRev?

Мы можем использовать эту функцию VBA InStrRev в MS Office 365, MS Excel 2016, MS Excel 2013, 2011, Excel 2010, Excel 2007, Excel 2003, Excel 2016 для Mac, Excel 2011 для Mac, Excel Online, Excel для iPhone, Excel для iPad, Excel для планшетов с Android и Excel для мобильных устройств с Android.

Пример 1. Найдите последнее вхождение слова «help» в строке «VBA Help help»

Вот простой пример функции VBA InStrRev.В приведенном ниже примере макроса используется функция InStrRev и выполняется поиск указанной позиции подстроки внутри данной строки, начиная с позиции -1.

'Искать последнее вхождение слова "help" в строке "VBA Help help"
Sub VBA_InStrRev_Function_Ex1 ()
    
    'Объявление переменной
    Dim iPosition As Integer
    Тусклое слово как строка
    
    sWord = "Справка по VBA"
    
    iPosition = InStrRev (sWord, «помощь»)
    'или же
    Позиция = InStrRev (sWord, «справка», -1)
        
    'Отображение выходного сообщения
    MsgBox "Положение слова 'help' в строке:" & iPosition, vbInformation, "VBA InStrRev Function"
    
Конец подписки
 

Вывод: Вот снимок экрана первого примера вывода.

Пример 2: Поиск последнего появления пустого места в строке «Справка по VBA»

Вот еще один пример функции VBA InStrRev. В приведенном ниже примере макроса используется функция InStrRev и выполняется поиск позиции пустого места в заданной строке, начиная с позиции 3

'Поиск последнего появления пустого места в строке «Справка по VBA»
Sub VBA_InStrRev_Function_Ex2 ()
    
    'Объявление переменной
    Dim iPosition As Integer
    Тусклое слово как строка
    
    sWord = "Справка по VBA"
    
    iPosition = InStrRev (sWord, "")
       
    'Отображение выходного сообщения
    MsgBox "Последнее появление позиции пустого места:" & iPosition, vbInformation, "VBA InStrRev Function"
    
Конец подписки
 

Вывод: Вот скриншот второго примера вывода.

Инструкции по использованию макросов

Вот пошаговые инструкции по использованию функции VBA InStrRev в Excel VBA.

• Откройте книгу Excel.
• Нажмите Alt + F11, чтобы открыть окно редактора VBA.
• Перейдите в меню «Вставка» и щелкните модуль из доступных опций.
• Скопируйте указанные выше макросы в редактор VBA.
• Выберите любую процедуру из приведенных выше кодов и нажмите «Выполнить» или используйте сочетание клавиш «F5».

Список функций VBA:

Щелкните ссылку ниже, чтобы вернуться к просмотру всего списка функций VBA.
Список функций VBA

VBA InStrRev | Как использовать функцию Excel VBA InStrRev?

Excel VBA INSTRREV

VBA INSTRREV Функция, как означает ‘In String Reverse’ , возвращает позицию первого вхождения строки поиска (подстроки) в другой строке, начиная с конца строки (справа налево) из которого мы ищем строку с возможностью поиска.

INSTRREV Функция начинает поиск доступной для поиска строки с конца строки, в которой нам нужно найти, но считает позицию с начала. Существует еще одна функция VBA INSTR (обозначает ‘In String’ ), которая также ищет строку в другой строке и возвращает позицию, но эта функция начинает поиск с начала строки, из которой мы ищем доступная для поиска строка.

INSTRREV и INSTR , оба являются встроенной функцией String / Text VBA в MS Excel.Мы можем использовать их при написании любого макроса в редакторе Microsoft Visual Basic.

Синтаксис

Как видно на изображении выше, есть 2 обязательных и 2 необязательных аргумента.

• StringCheck As String: Это обязательный аргумент. Нам нужно указать строковое выражение, в котором выполняется поиск.
• StringMatch As String: Этот аргумент также является обязательным. Нам нужно указать строковое выражение, которое ищется.
• Начать как длинное = -1: Это необязательный аргумент. Указываем числовое выражение. По умолчанию принимает значение -1, что означает, что поиск начинается с позиции последнего символа. Если мы укажем любое положительное значение, например 80, поиск начнется с конца строки в этих 80 символах слева.
• Сравнить как VbCompareMethod = vbBinaryCompare As Long: Этот аргумент является необязательным.

Мы можем указать следующие значения для этого аргумента.

Возвращаемые значения

1. INSTRREV функция возвращает 0, если проверка строки имеет нулевую длину или совпадение строки не найдено или «начало» аргумента > длина совпадения строки .
2. Эта функция возвращает «Null» , если проверка строки или совпадение строки равно «Null».
3. Если строка соответствует нулевой длины, функция возвращается к start .
4. Если совпадение строки найдено в проверке строки , , тогда функция возвращает позицию, в которой найдено совпадение.

Как использовать функцию VBA INSTRREV?

Предположим, у нас есть данные по названиям фильмов и их режиссерам. Мы хотим разделить имена директоров.

У нас есть данные в 1201 строке. Если мы сделаем эту задачу вручную, это займет много времени.

Чтобы сделать то же самое, мы будем использовать код VBA.Шаги:

• Нам нужно нажать на команду «Visual Basic» , доступную в группе «Код» на вкладке «Разработчик» , или мы можем нажать Alt + F11 , чтобы открыть редактор Visual Basic.

• Мы вставим модуль , используя меню «Вставить» .

• Мы создадим подпрограмму с именем «SplittingNames».

• Нам нужно 6 переменных — одна для хранения значений ячеек, которыми мы будем манипулировать.Второй — для хранения позиции первого пробела в строке, третий — для хранения позиции последнего пробела в строке, четвертый — для хранения номера последней строки, пятый и шестой — для строки и столбца, которые мы будем использовать для печати значений в соседних клетки.

• Чтобы узнать последнюю использованную строку на листе, нам нужно использовать следующий код.

Этот код сначала выберет ячейку B1, а затем выберет последнюю использованную ячейку в том же столбце, а затем мы присвоим номер строки ячейки переменной «LastRow».

• Теперь, чтобы управлять всеми ячейками в столбце B, мы запустим цикл «for» .

• Мы будем сохранять значения ячеек столбца B от строки 2 до строки 1201 одну за другой в переменной «s», чтобы управлять ими.

• Нам нужно установить значение переменной «Столбец» на 3, так как нам нужно записать имена разбиения в C (3 ^ряд Столбец) и столбец вперед.

• Если строка состоит только из одного слова, что означает, что в строке нет пробела, тогда нам нужна сама строка в качестве вывода.Для этого мы укажем условие, используя ‘If and Else statement’ со знаком звездочки (обозначающим один или несколько символов) следующим образом:

• Если в строке есть пробел, мы хотим разделить строку. Чтобы сделать то же самое, мы использовали функции INSTR и INSTRREV для определения первой и последней позиции пробела, соответственно. Это поможет нам найти первое и последнее слово в строке соответственно.

Функция INSTR принимает следующий аргумент:

Подробности аргумента

• Начало: С какой позиции начать.
• String1: Нам нужно указать строковое выражение, в котором выполняется поиск.
• String2: Нам нужно указать строковое выражение, которое ищется.

По сравнению с VbCompareMethod: Указание метода сравнения. По умолчанию это двоичное сравнение.

• Нам нужно использовать функцию VBA LEFT для извлечения левых символов из строки. Мы использовали ‘Last Space-1’ , чтобы получить левые символы перед последним пробелом.

Нам нужно использовать функции RIGHT и LEN для извлечения правильных символов из строки после первого пробела.

Макрос написан. Теперь нам просто нужно запустить макрос с помощью клавиши F5 .

Код:

 Sub SplittingNames ()

Dim s As String
Dim FirstSpace как долго
Dim LastSPace как долго
Dim LastRow As Long
Тусклый ряд по всей длине
Тусклый столбец по длине

  Лист1.Диапазон ("B1"). Выберите
  Selection.End (xlDown) .Select
  LastRow = ActiveCell.Row

Для строки = 2 до последней строки
  s = Sheet1.Cells (Row, 2) .Value
  Столбец = 3
   Если s Like "* *" Тогда
      FirstSpace = InStr (1, s, "")
      LastSPace = InStrRev (s, "")
      Sheet1.Cells (строка, столбец) .Value = Left (s, LastSPace - 1)
      Sheet1.Cells (Row, Column + 1) .Value = Right (s, Len (s) - FirstSpace).
   Еще
      Sheet1.Cells (строка, столбец) .Value = s
   Конец, если
Следующий

Концевой переводник 

Теперь у нас есть результат.

Рекомендуемые статьи

Это руководство по VBA INSTRREV. Здесь мы обсуждаем, как использовать функцию VBA INSTRREV вместе с примерами и загружаемым шаблоном Excel. Ниже приведены несколько полезных статей в Excel, связанных с VBA —

• 3 курса
• 12 практических проектов
• 43+ часов
• Полный пожизненный доступ
• Свидетельство о завершении