VBA RegEx - Як використовувати регулярний вираз Excel VBA?

• VBA RegEx

VBA RegEx

VBA RegEx «Регулярне вираження» дуже корисний у світі програмування, який може бути використаний для перевірки введення веб-сайту, пошуку шаблонів слів у великому рядку / тексті, для пошуку і заміни рядків.

Більшість із нас використовують ЗНАЙДЕННЯ, ЗАМОВЛЕННЯ, ЛІВО, ВПРАВО, а також функції MID для маніпулювання рядками в Microsoft Excel, чи не так? Однак у цих функцій є свої обмеження, які не можна широко використовувати. Знання про регулярні вирази VBA заощадить вам багато часу у вашому щоденному житті. Регулярні вирази коротко формуються як "RegEx" (або Regex) у VBA. У цьому підручнику ми розглянемо деякі RegEx з їх використанням.

Ви можете використовувати об'єкт VBA RegEx, який вже створений у бібліотеці регулярних виразів Microsoft VBScript. Ви можете переміститися по цій бібліотеці, натиснувши Інструменти - Довідка - Регулярні вирази Microsoft VBScript.

Ви також можете створити об’єкт RegEx за допомогою функції CreateObject у VBA. Це здається більш зручним способом, оскільки в цьому випадку вам не завжди потрібно посилатися на бібліотеку регулярних виразів VBScript.

Дивіться код нижче:

 Dim regex як набір об'єктів regex = CreateObject ("VBScript.RegExp") 

Цей код дозволить створити та отримати доступ до об’єктів RegEx у вашому макросі. Я особисто віддаю перевагу цьому способу над першим через його жорсткість.

Функція RegEx дозволяє маніпулювати наступним у довгій строці.

• Дикі картки типу *, ? тощо.
• Згруповані вирази разом із макетами.

RegEx має нижче параметри / властивості:

1. Шаблон - шаблон, визначений в RegEx, над яким потрібно робити операції.
2. IgnoreCase - ігноруйте регістри букв (Caps / Non-Caps однакові в рядку).
3. Глобальний - Дозволяє знаходити всі можливі збіги, якщо встановлено значення ІСТИНА. Якщо встановлено значення FALSE, воно знаходить лише першу відповідність.
4. MultiLine - Дозволяє відповідати шаблону в рядку, який поширюється по декількох рядках.

RegEx полегшує наступні три операції:

• Тест - Тестує, чи є згаданий шаблон у рядку чи ні. Якщо присутній повертає ПРАВИЛЬНО, інакше ЛЖЕ.
• Замінити - замінює початковий візерунок іншим.
• Виконати - Повертає всі відповідні шаблони пошуку.

Як користуватися Excel VBA RegEx?

Ми дізнаємось, як використовувати регулярний вираз Excel VBA за допомогою кількох прикладів.

Ви можете завантажити цей шаблон VBA RegEx Excel тут - Шаблон VBA RegEx Excel

Excel VBA RegEx - приклад №1

Тут ви перевірте, чи вказаний візерунок присутній у тексті за допомогою RegEx.Test.

Для використання VBA RegEx виконайте наведені нижче дії.

Крок 1: Визначте нову підпроцедуру для створення макросу.

Код:

 Sub RegEx_Ex1 () Кінець Sub 

Крок 2: Визначте дві змінні RegEx як об'єкт, який можна використовувати для створення об’єкта RegEx, а Str як рядок.

Код:

 Sub RegEx_Ex1 () Dim RegEx як об’єкт, Str як String End Sub 

Крок 3: Створіть об’єкт RegEx за допомогою функції CreateObject.

Код:

 Sub RegEx_Ex1 () Dim RegEx як об’єкт, Str як String Set RegEx = CreateObject ("VBScript.RegExp") Кінець Sub 

Крок 4: Додайте шаблон для тестування за допомогою функції RegEx.

Код:

 Sub RegEx_Ex1 () Dim RegEx як об'єкт, Str як String Set RegEx = CreateObject ("VBScript.RegExp") з RegEx .Pattern = "(0-9) +" End with End Sub 

Крок 5: Визначте рядок, в межах якого нам потрібно перевірити заданий шаблон.

Код:

 Sub RegEx_Ex1 () Dim RegEx як об'єкт, Str як String Set RegEx = CreateObject ("VBScript.RegExp") з RegEx .Pattern = "(0-9) +" End with Str = "Мій номер велосипеда MH-12 PP- 6145 "Кінець підп 

Крок 6: Використовуйте RegEx.Test, щоб перевірити, чи є даний візерунок у змінній з іменем Str. Також використовуйте Debug.Print, щоб надрукувати результат (True або False) на найближчому вікні.

Код:

 Sub RegEx_Ex1 () Dim RegEx як об'єкт, Str як String Set RegEx = CreateObject ("VBScript.RegExp") з RegEx .Pattern = "(0-9) +" End with Str = "Мій номер велосипеда MH-12 PP- 6145 "Debug.Print RegEx.Test (Str) Кінець Sub 

Крок 7: Натисніть кнопку F5 або кнопку Run, щоб запустити код і побачити вихід. (Натисніть CTRL + G, щоб відкрити негайне вікно)

Тут ми створили об’єкт RegEx. Тоді за допомогою VBA RegEx ми перевірили, чи є даний рядок ("(0-9) +"), який включає всі числа / комбінацію чисел від 0 до 9, у рядку, визначеному за допомогою RegEx.Test ( Вул). Вихід "True" під безпосереднім вікном показує, що шаблон "(0-9) +" присутній у заданому рядку.

Excel VBA RegEx - приклад №2

Тут ви побачите, як ви можете замінити один рядок іншим за допомогою RegEx.Replace.

Для використання VBA RegEx виконайте наведені нижче дії.

Крок 1: Визначте новий підрядчик для створення макросу.

Код:

 Sub RegEx_Ex2 () Кінець Sub 

Крок 2: Те саме, що і в першому прикладі, визначте дві змінні RegEx як об'єкт, який може містити значення об'єкта RegEx і Str, який буде містити значення рядка, яке ви визначите.

Код:

 Sub RegEx_Ex2 () Dim RegEx як об’єкт, Str як String End Sub 

Крок 3: Визначте об'єкт RegEx та встановіть його на змінну RegEx за допомогою функції CreateObject.

Код:

 Sub RegEx_Ex2 () Dim RegEx як об'єкт, Str як String Set RegEx = CreateObject ("VBScript.RegExp") Кінець Sub 

Крок 4: Вставте шаблон, який ви хочете замінити за допомогою функції .Pattern.

Код:

 Sub RegEx_Ex2 () Dim RegEx як об’єкт, Str як String Set RegEx = CreateObject ("VBScript.RegExp") з RegEx .Pattern = "123" Кінець кінцем Sub 

Крок 5: Використовуйте .GLOBAL = ІСТИНА, яка, якщо TRUE дозволяє замінити всі відповідні шаблони в заданому рядку. Якщо FALSE замінює лише перший узор відповідності.

Код:

 Sub RegEx_Ex2 () Dim RegEx як об’єкт, Str як String Set RegEx = CreateObject ("VBScript.RegExp") з RegEx .Pattern = "123" .Global = True 'Якщо FALSE, замінює лише першу відповідну рядок' End with End Sub 

Крок 6: Після закінчення "С" визначте рядок, з яким потрібно відповідати, і замініть шаблон.

Код:

 Sub RegEx_Ex2 () Dim RegEx як об'єкт, Str як String Set RegEx = CreateObject ("VBScript.RegExp") з RegEx .Pattern = "123" .Global = True 'Якщо FALSE, замінює лише перший відповідний рядок' End with str = "123-654-000-APY-123-XYZ-888" Кінець Sub 

Крок 6: Використовуйте RegEx.Replace, щоб замінити значення шаблону в заданому рядку іншим текстом.

Код:

 Sub RegEx_Ex2 () Dim RegEx як об'єкт, Str як String Set RegEx = CreateObject ("VBScript.RegExp") з RegEx .Pattern = "123" .Global = True 'Якщо FALSE, замінює лише перший відповідний рядок' End with str = "123-654-000-APY-123-XYZ-888" Debug.Print RegEx.Replace (Str, "Замінено") Кінець Sub 

Тут ми хотіли, щоб шаблон "123" замінили на якийсь інший шматок рядка. RegEx.Replace дозволяє замінити значення шаблону на шматок рядка (ми визначили "Замінено"). Будь ласка, подивіться також .Global = ІСТИНА. Яка умовна заява. Якщо Global = TRUE, функція Replace замінює всі відповідні шаблони іншим рядком. Якщо GLOBAL = FALSE, функція Replace замінює лише перший узор відповідності, а решта інші нехтують.

Крок 7: Натисніть кнопку Run або F5, щоб запустити цей код і побачити вихід у вікні негайного виводу.

Excel VBA RegEx - приклад №3

Тут ви побачите, як ви можете знайти всі відповідні шаблони в рядку за допомогою RegEx.Execute.

Для використання Excel VBA RegEx виконайте наведені нижче дії.

Крок 1: Визначте нову під-процедуру для додавання макросу.

Код:

 Sub RegEx_Ex3 () Закінчення Sub 

Крок 2: Виконайте кроки, такі ж, як і в попередніх двох прикладах, щоб визначити регулярний вираз і змінну, яка може містити значення рядка.

Код:

 Sub RegEx_Ex3 () Dim RegEx як об'єкт, Str як String Set RegEx = CreateObject ("VBScript.RegExp") Кінець Sub 

Крок 3: Вставте шаблон, який ви хотіли б виконати і зіставити в заданий рядок.

Код:

 Sub RegEx_Ex3 () Dim RegEx як об’єкт, Str як String Set RegEx = CreateObject ("VBScript.RegExp") з RegEx .Pattern = "123-XYZ" End with End Sub 

Крок 4: Використовуйте .GLOBAL = ІСТИНА, щоб усі збіги були захоплені у заданому рядку.

Код:

 Sub RegEx_Ex3 () Dim RegEx як об'єкт, Str як String Set RegEx = CreateObject ("VBScript.RegExp") з RegEx .Pattern = "123-XYZ" .Global = True End with End Sub 

Крок 5: Визначте рядок після End with, під яким потрібно використовувати Execute та знайти всі відповідні шаблони.

Код:

 Sub RegEx_Ex3 () Dim RegEx як об'єкт, Str як String Set RegEx = CreateObject ("VBScript.RegExp") з RegEx .Pattern = "123-XYZ" .Global = True End With Str = "123-XYZ-326-ABC- 983-670-PQR-123-XYZ "Кінець Підд 

Крок 6: Тепер використовуйте Execute на змінній з іменем string, щоб дізнатися всі збіги, пов'язані з заданим шаблоном.

Код:

 Sub RegEx_Ex3 () Dim RegEx як об'єкт, Str як String Set RegEx = CreateObject ("VBScript.RegExp") з RegEx .Pattern = "123-XYZ" .Global = True End With Str = "123-XYZ-326-ABC- 983-670-PQR-123-XYZ "Встановити відповідність = RegEx.Execute (Str) End Sub 

Крок 7: Використовуйте для циклу для друку значення всіх узгоджених шаблонів у безпосередньому вікні виводу.

Код:

 Sub RegEx_Ex3 () Dim RegEx як об'єкт, Str як String Set RegEx = CreateObject ("VBScript.RegExp") з RegEx .Pattern = "123-XYZ" .Global = True End With Str = "123-XYZ-326-ABC- 983-670-PQR-123-XYZ "Встановити матчі = RegEx.Execute (Str) на кожен матч у матчах Debug.Print Match.Value Наступний матч Кінець Sub 

Крок 8: Запустіть цей код, натиснувши F5 або запустіть кнопку і побачте вихід під вікном негайного виходу.

Цей код друкує всю кількість збігів для визначеного шаблону.

Що потрібно пам’ятати

• Регулярне вираження VBA можна використовувати лише для з'ясування рядків. Очевидно, що для числових значень нам не потрібна така функція. Всі числові значення, розглянуті в кожному з трьох вищевказаних прикладів, насправді визначаються як рядок.
• Excel VBA RegEx повинен використовуватися для обробки рядків, коли є великі дані. Не використання VBA RegEx знижує ефективність програми.
• VBA RegEx виходить з ладу, коли є сценарії, коли шаблон повторюється n кількість разів або нескінченний час для цього.

Рекомендовані статті

Це було керівництвом до VBA RegEx. Тут ми обговорили, як використовувати Excel VBA RegEx разом з деякими практичними прикладами та шаблоном Excel, який можна завантажити. Ви також можете ознайомитися з іншими запропонованими нами статтями -

1. Як використовувати заміну VBA в Excel?
2. Використання вставки коментаря в Excel
3. Створення VBA TIMER в Excel
4. Вкладена формула IF в Excel