No video :(

How to use Regular Expressions with Excel VBA (Part 1)

Подписаться 85 тыс.

Просмотров 49 тыс.

50% 1

How to use Regular Expressions with Excel VBA (Part 1)
In this video I am covering Regular Expressions. These are a very powerful feature used in most programming languages.
Don't worry if you've never heard of them. In this video I will show you why you need Regular Expressions and how important they are.
FREE CHEAT SHEET: Get the free cheat sheet on VBA arrays here: (bit.ly/2MXsnz9)
Excel VBA Training
The Excel VBA Handbook Course(TheExcelVBAHan...)
Webinar Archives - 60+ Hours of VBA training(excelmacromast...)
Related Links:
The Ultimate Guide to Excel VBA String Functions(excelmacromast...)
The VBA Like Operator(bit.ly/342HAnR)
The Instr Function(bit.ly/2p1k4ZA)
Related Webinar Replays(VBA Vault Members only):
How to use VBA String Functions(excelmacromast...)
Free Excel VBA Resources
Excel VBA Articles (excelmacromast...)
Useful VBA Shortcut Keys:
View the Immediate Window: Ctrl + G
Tab: To move lines of code to the right(Indent).
Shift + Tab: To move lines of code to the left(Outdent).
Shift + F2: Get the definition of the item under the cursor.
Ctrl + Shift + F2: Go to the last cursor position.
Ctrl + Space: AutoComplete Word.
Alt + F11: Switch between Excel and the VBA Editor.
Ctrl + G: view the Immediate Window
Ctrl + R: View the Project Explorer Window.
Ctrl + Shift + 8(or Ctrl + *): Get the current region on a worksheet.
F4: View the Properties Window.
F5: Run the code from the current sub.
F8: Step into the code.
F9(or click left margin): Add a breakpoint to pause the code.

Опубликовано:

18 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 94

@mikhailk5690 4 года назад

This was a great help, thank you for this video! I am using VBA for 15 years but every time I find something new for me. I truly enjoy watching your tutorials!

@Excelmacromastery 4 года назад

Glad you like it Mikhail.

@henrikijonkoping4694 3 года назад

Interesting Mikhail K! What do u use VBA too?

@szilagyigabor3013 4 года назад

For Each text In arr If regEx.Test(text) = True Then Set mc = regEx.Execute(text) For I = 0 To mc.Count shNamesCells(row, I + 2).Value = mc(I) Next I End If Next text If you have names with multiple elements. Great coding!

@terrycline8689 4 года назад

You have a knack for covering topics that I am truly "fuzzy" with. In this case, I have tinkered with "Like" but it never quite gave me what I thought it would, and I really didn't understand why (or, how/which tools I should be using). I do now. Also REALLY enjoy when you show how a single line of code can do what many more might do. That is, I could have accomplished several things you showed here, but in about 5x the number of lines. And this is why I don't/can't write code for a living :)

@Excelmacromastery 4 года назад

Glad you liked it:)

@nelsonrioux5555 4 года назад

Great subject, your voice, no music and good pace: perfect VBA video. Thank you

@GarryI449 4 года назад

This stuff is amazing. I used to have a function that did a ton of search and replace on a massive list to generate logic for automation systems. It did the job, but literally took over-night to complete, and while it was operating, you couldn't use the PC. With a few tweaks after watching your RUN-1000 TIMES FASTER video, it completes now in the time it takes to get a coffee. I Have some more optimizing to do yet but thanks so much for this stuff, and your website!

@LosoIAm 2 года назад

Yes, the "Run-1000 Times Faster" video is a game-changer (being an Access/VBA veteran but Excel/VBA newbie). Note: I have read that Excel loads a workbook into RAM and this could cause performance issues. Sadly, I don't work with Excel; I am just trawling RU-vid (or the Internet) for stuff like this video to expand my knowledge/skill sets.

@LosoIAm 2 года назад

If this video had been available when I first started my journey in VBA (back in 2001), I would have been a Jedi Master in VBA by now. I had lots of energy (and esp. the passion and patience) back then! I now call myself (or sometimes cheekily sign) "VBA Worrier" - not a typo, by the way. Awesome video, many thanks!

@glennlangford5897 4 года назад

Thanks Paul, brilliant explanation, looking forward to Part 2

@philipmcdonnell7168 4 года назад

Normally, even if I’m stood on a stepladder, how I can use your training flies way over my head. This time, I can see possibilities! Not quite nailed where but the concept is definitely lurking at the back of my mind. I’m looking forward to the next instalment. 👍🏻

@houstonvanhoy2198 2 года назад

Terrific, as usual. Thank you, Sir Paul!

@LosoIAm 2 года назад

The techniques at your website "were" (probably) not available anywhere else - they are now, thanks to all your hard work and kindness!

@RJYL 2 года назад

Great explanation！

@ricos1497 4 года назад

Great tutorial. I love regex, but for some reason I always forget to utilise it. This is great timing, I'll break it out again on my next project to keep the syntax in my head

@Excelmacromastery 4 года назад

Thanks Rico.

@LearningSpanishforbeginn-nu2jm Год назад

Excellent video. Very very helpful with using Regex inside of Excel. Can you tell me how you would go about setting up an array for RegEx.Pattern so that I could write a script that will search through a text file and extract out multiple items and save them in various cells?

@a_pear 4 года назад

This was an excellent video. I've been following your RU-vid series pretty well and am happy to hear your website is such a good resource as well. I've been spreading the good word about you around the office with my Junior OR staff, but probably need to put some of these lessons into a brown bag to get their code skills sharpened up. Anywho, excellent video once again, keep up the good work.

@Excelmacromastery 4 года назад

Thanks Jonathan. Glad you liked it.

@maciejdolinski2603 4 года назад

Great tutorial. Thank you, Paul!

@houstonvanhoy7767 3 года назад

How to use Regular Expressions with Excel VBA (Part 2): ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-MKDC03t407I.html

@ventjemazzel8822 3 года назад

Awesome; thank you.

@baruchpale 4 года назад

Very useful. Thank you Paul.

@davidunger7425 4 года назад

Great video Paul, looking forward to the sequel!

@hadibq 2 года назад

Awesome Tutorial very clean steps!

@michalroesler 4 года назад

What a great Tutorial Sir. I've learnt so much. I'm turning on another video. Thank you.

@iincitr 4 года назад

Amazing Again Thanks Paul for your clean speech, explanation, and demonstrations.

@rakeshkumarmadella7955 4 года назад

Your training is awesome

@joaocustodio2094 4 года назад

Very very useful. Thanks very much Paul.

@KhalilYasser 4 года назад

Thank you very much Paul. Awesome tutorial.

@Excelmacromastery 4 года назад

Thanks Yasser.

@KhalilYasser 4 года назад

@@Excelmacromastery Is there article related to this topic?

@Excelmacromastery 4 года назад

Not yet but I plan to create some in the near future.

@KhalilYasser 4 года назад

@@Excelmacromastery Thank you very much.

@dildokafir5058 4 года назад

I learnt a new library today... Thanks Paul for showering knowledge on us.

@Excelmacromastery 4 года назад

You're welcome Sunil.

@richrobins5711 4 года назад

Thank you Paul! I used it in a project I had that was counting occurrences of several strings in a file! I was doing it that it had to use several passes on the file but with this I can do it all in 1 pass!

@Excelmacromastery 4 года назад

Excellent Rich.

@frenzyyyy7186 4 года назад

Very good explanation! Thanks for this I really need this tutorial. More power to you.

@Excelmacromastery 3 года назад

Glad it was helpful!

@myessaytutor 4 года назад

Great video, again. I'm looking forward to Part 2.

@itlife4555 2 года назад

before i learn it here, i thought Regex is very difficult, But after i learn your turtorial here, i found Regex very simple.....

@alreadydeadfunk 3 года назад

In case no one says in the comments below, you could also assign the object to vbscript.regexp using CreateObject function when declaring the object variable which allows you to use the same Regular Expression library. To be more precise, type in something like this 'Dim regexp As Object: Set regexp = CreateObject("vbscript.regexp")'

@Excelmacromastery 3 года назад

That's Late Binding which is useful when releasing the software but the downside is that it doesnt' give access to Intellisense.

@frikduplessis3869 4 года назад

Brilliant again Paul

@Excelmacromastery 4 года назад

Thanks Frik

@ventjemazzel8822 4 года назад

Very helpful indeed; thank you!

@vijaysahal4556 3 года назад

nice sir 👍🏻👍🏻👍🏻💯💯💯

@LosoIAm 2 года назад

Great video - THANK YOU! A small note: although the dim statements do not get executed in the loop and moving them to above the loop will produce the same result as the original code. Question: do you know if declaring the variables inside the loop will cause the code to run slower?

@houstonvanhoy7767 3 года назад

I'll be reviewing this a couple of times after "Midsomer Murders" is over. 😊 R.I.P. Sykes. 😢

@ExcelVisual 4 года назад

It is interesting. Thanks for the idea!

@rakeshkumarmadella7955 4 года назад

Hello paul,

@rrrprogram8667 4 года назад

Oh awesomeee Paul... But this content is not available in ur website right ???... can you make a video .. of half n hour may be...to elaborate on the mostly used algorithms in Regex .... ??

@Excelmacromastery 4 года назад

It's not on the website yet but I hope to add an article about this in the future. The next videos covers the basics of using RegEx expressions.

@grahamparker7729 4 года назад

Nice one Paul 👍🏻

@Excelmacromastery 4 года назад

Thanks Graham

@apurvaumang3581 4 года назад

I have a string which looks something like this 10x10 Hot Dog Bags, but the problem with the string is, it can sometimes have X in blocks sometimes small. It can have spaces between the X and numbers. Also it can have the numbers after the Alphabets in the string like Manchurian Candidate Sleeve Bag 19X 18. In addition to this the numbers are sometimes not evenly placed (i.e. trailing whitespaces and lastly it could look like this 10" X 9" Megadeath Sauce packet bag. How to build a pattern for this and loop through all records to get only the numbers i.e 10 in one column in excel and 9 in the other and so on.

@PerFeldvoss 2 года назад

Great, but why would you write \d+ in the pattern?

@rakeshkumarmadella7955 4 года назад

How r ? actually I want to purchase your handbook.. but I am from India it's 350dollars.. in India rs 21,000 it's a very huge amount and I can't afford is there any other alternative where I can purchase ur handbook

@johnnyc873 4 года назад

Nice explanation Paul. I was aware of them but never knew what they could be used for, they look perfect for parsing pathnames into the folders. Can they be used for control characters? We have a system that randomly inserts ascii 8 into fields in data dumps as separators and it would be handy to put the data into a collection using the ascii 8 as a delimiter

@serbach 4 года назад

Johnny c, I think you mean ASCII 9 which is the TAB character. ASCII 8 is the BACKSPACE. Regular Expressions can search and replace tabs easily. The shorthand way of representing a TAB in a regular expression is \t. I use it all the time. By the way, the absolute best reference work for learning regular expressions is the one compiled by Jan Goyvaerts. Just search for his name on Google and you'll find his book and on-line tutorial.

@Excelmacromastery 4 года назад

Yes they can.

@rrrprogram4704 4 года назад

Hi paul.... if the macro code gets bigger and bigger .. how to you manage. Do u use GIT ???? is it even possible to use GIT with macro code ??

@ukaszbrzozowski2377 4 года назад

Bump :) Do you plan to make video about that? :)

@Excelmacromastery 4 года назад

You can use any version control software with the code including Git. The problem with VBA is that you have to export the modules from Excel to files first. You can use third party software to export all your files in one go or you can write your own code to do it. It's a bit messy to say the least. -Paul

@jgirard1970 4 года назад

Once again, nice video. I want to ask. What IDE are you using? Yours looks different than mine in Excel 2016.

@michaelk8642 4 года назад

The VBA IDE hasn't changed in years. Paul has most likely customized his toolbars and views.

@Excelmacromastery 4 года назад

Yes - it's 2016 but as Michael said the IDE hasn't changed in years. It probably looks different because the Immediate window is floating and the project and property windows are closed.

@harrysherman8166 3 года назад

WHY IS Microsoft VBScript Regular Expression 5.5 NOT IN MY REFERENCE LIBRARY PLEASE HELP

@not.an.operator 3 года назад

I only see VBScript 1.0 in my references. Anyone know how to get 5.5 to show up?

@tobiewaldeck7105 4 года назад

I keep getting Method 'Range' of object' -Worksheet' failed or Global failed at "arr = shNames" even though code is the same.

@hemant0088 4 года назад

Hi, you have given great demonstration about Reg Ex in VBA. However, I am trying to find how to connect AS400 terminals using Excel VBA. Could you please assist me?

@KhalilYasser 4 года назад

@ hemant Kawalkar Can you give more details and put some of the expected output?

@hemant0088 4 года назад

@@KhalilYasser AS400 usually comes only in company's. I can give you few screenshots or what it is ? So far I know we need to use some dll. But I don't know which one or where to start.

@kingpin11111 4 года назад

At 12:37 you write "\d+" into your pattern to search for all the numbers. Can anyone please tell me, what does the "\d+" actually stands for? Is it some built-in expression in VBA for numbers? If so, then where can I found it in VBA help documentation (which topic name?). Are there other similar expressions? I have never seen that before. Thanks.

@Excelmacromastery 4 года назад

Regular Expressions are not part of VBA. They are a separate library that is used by many languages. You can google Regular Expressions tutorial to find out how they work.

@irajatsolanki 4 года назад

How to create a function based on=REGEXREPLACE(A1,"\D+","") regex replace like Google sheets

@dildokafir5058 4 года назад

Hello , how to target special characters using this approach.. I means how we specify pattern for special characters.

@Excelmacromastery 4 года назад

I'll be covering regular expressions themselves in the next video.

@cocococo8147 4 года назад

Video 7:48, How do you delete the code?

@Excelmacromastery 4 года назад

Ctrl + Y deletes a line.

@debjitsarkar2651 4 года назад

Sir please tell me "WHY SAME VBA CORD RUN FIRST SOME TIME & SLOW SOME TIME.SAME CORD IN SAME EXCEL FILE .PLEASE REPLY.HOW TO SOLVE THIS PROBLEM?

@joaocustodio2094 4 года назад

Paul,,

@tughanozsezer9332 4 года назад

👍

@rheitzman 4 года назад

A huge downside to RegEx is that code containing them is not maintainable. If you are working on throw away code they are fine.

@Excelmacromastery 4 года назад

Why is the code not maintainable??

@felixrode1290 4 года назад

@@Excelmacromastery I was wondering that too. Great video Paul, very useful not just for Excel but great potential for MS Access too. As a general rule I would use late binding to ensure the highest level of compatibility - any thoughts on that ?

@donepearce 4 года назад

Please try to kill some confusion. Please don't choose variable names that are almost identical to the related function name. Go for something totally different, so don't call your array ARR, call it John, shoelace or Buffalo. You remind me a little of my maths teacher at school. He would illustrate a problem and end up with an equation, but he always chose his values so almost everything cancelled top and bottom. What remained was great for him, but explained nothing about the process.

@nordicdust 4 года назад

I am pretty sure that it is common practice and makes sence. Dim ARR As ARRAY, Dim VAR As VARIANT, Dim COL As COLUMN

@donepearce 4 года назад

@@nordicdust One man's common sense is another man's failure of imagination. Variable names should always be about the content of the variable, never about the structure of the programming language.