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!
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!
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 :)
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!
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.
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. 👍🏻
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
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.
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?
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!
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?
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")'
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.
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.
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 .... ??
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.
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
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.
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
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 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.
@@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.
@@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.
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?
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!
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:)
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!
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.
Thanks Paul, brilliant explanation, looking forward to Part 2
Terrific, as usual. Thank you, Sir Paul!
The techniques at your website "were" (probably) not available anywhere else - they are now, thanks to all your hard work and kindness!
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. 👍🏻
Awesome Tutorial very clean steps!
What a great Tutorial Sir. I've learnt so much. I'm turning on another video. Thank you.
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.
Great explanation！
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.
Your training is awesome
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.
Great tutorial. Thank you, Paul!
Very good explanation! Thanks for this I really need this tutorial. More power to you.
Glad it was helpful!
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?
Amazing
Again Thanks Paul for your clean speech, explanation, and demonstrations.
Great video Paul, looking forward to the sequel!
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.
Brilliant again Paul
Thanks Frik
Great video, again. I'm looking forward to Part 2.
Very helpful indeed; thank you!
Thanks! Although I did not understand what is difference between 'like' clause and 'regex' syntax. Is the 'like' clause a limited subset of 'regex'?
Awesome; thank you.
before i learn it here, i thought Regex is very difficult, But after i learn your turtorial here, i found Regex very simple.....
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?
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.
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.
nice sir 👍🏻👍🏻👍🏻💯💯💯
It is interesting. Thanks for the idea!
I'll be reviewing this a couple of times after "Midsomer Murders" is over. 😊 R.I.P. Sykes. 😢
Nice one Paul 👍🏻
Thanks Graham
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.
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.
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.
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
How to use Regular Expressions with Excel VBA (Part 2): ua-cam.com/video/MKDC03t407I/v-deo.html
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.
Hello paul,
Great, but why would you write \d+ in the pattern?
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 only see VBScript 1.0 in my references. Anyone know how to get 5.5 to show up?
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
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.
How to create a function based on=REGEXREPLACE(A1,"\D+","") regex
replace like Google sheets
👍
Paul,,
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.
Very useful. Thank you Paul.
Very very useful. Thanks very much Paul.