How to use Regular Expressions with Excel VBA (Part 1)
Вставка
- Опубліковано 23 лип 2024
- 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(TheExcelVBAHandbook.com)
Webinar Archives - 60+ Hours of VBA training(excelmacromastery.com/excel-v...)
Related Links:
The Ultimate Guide to Excel VBA String Functions(excelmacromastery.com/vba-str...)
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(excelmacromastery.com/vba-str...)
Free Excel VBA Resources
Excel VBA Articles (excelmacromastery.com/vba-art...)
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.
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!
Glad you like it Mikhail.
Interesting Mikhail K! What do u use VBA too?
Great tutorial. Thank you, Paul!
Great video Paul, looking forward to the sequel!
Thanks Paul, brilliant explanation, looking forward to Part 2
Terrific, as usual. Thank you, Sir Paul!
Very useful. Thank you Paul.
Amazing
Again Thanks Paul for your clean speech, explanation, and demonstrations.
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!
Very helpful indeed; thank you!
Great video, again. I'm looking forward to Part 2.
Very very useful. Thanks very much Paul.
Great explanation！
What a great Tutorial Sir. I've learnt so much. I'm turning on another video. Thank you.
Awesome Tutorial very clean steps!
Great subject, your voice, no music and good pace: perfect VBA video. Thank you
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 :)
Glad you liked it:)
Awesome; thank you.
Your training is awesome
This was an excellent video. I've been following your UA-cam 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.
Thanks Jonathan. Glad you liked it.
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!
Excellent Rich.
I learnt a new library today... Thanks Paul for showering knowledge on us.
You're welcome Sunil.
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!
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 UA-cam (or the Internet) for stuff like this video to expand my knowledge/skill sets.
Very good explanation! Thanks for this I really need this tutorial. More power to you.
Glad it was helpful!
It is interesting. Thanks for the idea!
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!
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
Thanks Rico.
The techniques at your website "were" (probably) not available anywhere else - they are now, thanks to all your hard work and kindness!
Brilliant again Paul
Thanks Frik
Thank you very much Paul. Awesome tutorial.
Thanks Yasser.
@@Excelmacromastery Is there article related to this topic?
Not yet but I plan to create some in the near future.
@@Excelmacromastery Thank you very much.
nice sir 👍🏻👍🏻👍🏻💯💯💯
Nice one Paul 👍🏻
Thanks Graham
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?
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?
I'll be reviewing this a couple of times after "Midsomer Murders" is over. 😊 R.I.P. Sykes. 😢
before i learn it here, i thought Regex is very difficult, But after i learn your turtorial here, i found Regex very simple.....
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")'
That's Late Binding which is useful when releasing the software but the downside is that it doesnt' give access to Intellisense.
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
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.
Yes they can.
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 .... ??
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.
Hello paul,
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.
Once again, nice video. I want to ask. What IDE are you using? Yours looks different than mine in Excel 2016.
The VBA IDE hasn't changed in years. Paul has most likely customized his toolbars and views.
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.
👍
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.
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.
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 ??
Bump :) Do you plan to make video about that? :)
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
Hello , how to target special characters using this approach.. I means how we specify pattern for special characters.
I'll be covering regular expressions themselves in the next video.
Great, but why would you write \d+ in the pattern?
I only see VBScript 1.0 in my references. Anyone know how to get 5.5 to show up?
How to create a function based on=REGEXREPLACE(A1,"\D+","") regex
replace like Google sheets
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
I keep getting Method 'Range' of object' -Worksheet' failed or Global failed at "arr = shNames" even though code is the same.
WHY IS Microsoft VBScript Regular Expression 5.5 NOT IN MY REFERENCE LIBRARY PLEASE HELP
How to use Regular Expressions with Excel VBA (Part 2): ua-cam.com/video/MKDC03t407I/v-deo.html
Paul,,
Video 7:48, How do you delete the code?
Ctrl + Y deletes a line.
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?
@
hemant Kawalkar
Can you give more details and put some of the expected output?
@@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.
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?
A huge downside to RegEx is that code containing them is not maintainable. If you are working on throw away code they are fine.
Why is the code not maintainable??
@@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 ?
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.
I am pretty sure that it is common practice and makes sence. Dim ARR As ARRAY, Dim VAR As VARIANT, Dim COL As COLUMN
@@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.