The tips about referencing tables in the get range section is hugely helpful. I've got a project where I need to pull data from a certain column of a vendor-supplied spreadsheet, but some months one column gets removed, a column might get added, or the columns have been reordered for some reason so I never know which column I should be looking at (I can't just say "look at column G" because it won't always be that same column) and I have some unnecessarily complex code in my macro to find the right column - with this I can just convert it to a table and reference the header name, which is so much simpler and more flexible. I can use the array tip for reading data too to speed it up since it's often a lot of lines (sometimes 50K+). I actually think I have a few macros that I can take advantage of the array tip to read data faster for - definitely a good Monday morning project. :)
I've always been using the Split function because its easy to handle with, now I'm using arrays and class modules that I learned from the previous videos of yours. Thanks for the hints Paul.
Hi, nice tips for intermediate users. For the pros: As performance is always an issue with VBA, working in memory with arrays instead of Excel objects which are visible (e.g. cells) is way faster. Instead of writing each single array value in a cell just dump the array in a defined range the size of the array. This is x times faster as it is one single line. When manipulating large tables for future use, try creating an "Entry" class with the fields as attributes and put the instances in an array. This allows you to address everything easily and is extremely fast as VBA handels objects quite well. Best alternative, however, put it in a database and connect with ADODB to the DB using SQL. When considering Access as the DB alternative, connecting to Access via a Excel file with VBA, this is even faster than executing the code in Access.
I love the split function in VBA, I use it all the time, especially for parsing range addresses. A tip for others is that you can add a second set of brackets to return the split number, e.g. Split (s, ":")(1) to return the second item.
Great work as always! I must admit I didn't know about checking properites in the watch window, so big plus to you! I have been using these tips for some time yet, but I saw them for the first time on your channel long ago :) I agree they were gamechangers.
A lot of your videos have changed my coding to the better... both to the eye and to the runtime stability. I've been doing this for almost 20, years but somewhere the learning curve phased out and I started to "do with what I had", which meant long detours instead of direct functions, ex nested loops and Mid to find strings instead of simple Instr (and really dangerous Resume Nexts just to get to End)... not to mention 'time'. Your quote "What people often do when they start VBA because they don't know any better", from your 1000x faster video, made me laugh... because I knew you were talking about me. I haven't "just started", but there's certainly a lot I should know better after 15+ years. Your videos have given me much and I'm sure that your coming ones will.
@@Excelmacromastery I'm not the only one benefitting from your videos. So far at least 3 of the tools I've done for people at work have been updated since I stumbled on your collection of uploads. :)
Thank you for another valuable video. I remember reading (rightly or wrongly) that Excel and VBA are in different memory processes, which creates a performance hit each time you go between them, so I always minimise traffic between VBA and the worksheet, reading all the sheet data that I need into arrays at the start, doing all the work in memory, then writing results back in as few statements as possible, ideally one figure or table. The other performance hack I think everyone should know is dictionaries.
That's a very efficient way to do it Dermot. Make sure to check out my Arrays Class that adds a ton of functionality to arrays(th-cam.com/video/ZYxa-Q-nSX0/w-d-xo.html).
I didn't know about the watch and the error-break hacks. These are really helpful in debugging, which often takes longer than the coding itself. I hope you have a video or article that focuses on debugging tricks (not the textbook methods) to quickly nail down problems.
تمثل اللغة تحدي جدي بالنسبة لي لكي اتابع فيديوهاتك ، فانا ابذل جهدا مضاعفا لكي احصل على الفائدة القصوى ، و مع ذلك استمر في متابعة منشوراتك لاني اعلم انك الافضل . شكرا بول .
Whow! Once again: I learned a lot! The only thing I already knew about was the timing advantage of arrays over ranges. And I knew about list objects. But all other topics: well worth the watch! :-)
I do a lot of work with HMI displays that utilize DHTML and scripting, the split function is invaluable for building strings that can be passed to the execute command or eval function.
Nice video. I regularly follow your video. Your teaching is so excellent. Please make a video- Excel data send through what's app. Data may be jpg, pdf, text accordingly.
Thank you very much for your videos and instructions and tips. Not sure why, but the shortcuts to select regions, Ctrl * gives me a "Cannot show outline symbols" message. and the Ctrl Shift * gives me a "Printer Installer Client Console".
As always, amazingly well explained! Edit: I'll use your Code but I need to understand the Magie, accordingly I'll write it by myself! Thanks alt sir!😉🤟😎
@@Excelmacromastery please excuse the auto-cerrecture- mistakes! Thanks for all your content! I enjoy every single minute!👍👍👍🤟😎 BR from Germany! Cheers!🍻
Great Video A remark: In the Use Range macro, you should save the last row of the range in a variable, and then put: For i = 2 to LastRow. I think it would be faster. Greetings from Bolivia.
Great video, as always! Thanks very much for posting. When using LBound(arr) and UBound(arr) in the For loop header, does VBA reevaluate those functions at each pass through the loop or does it optimize performance by evaluating them only once? Generally, when I am looping through an array and I know the lower and upper bounds, I store those in variables such as inLBound and intUBound and use those variables in the construction of the For loop. Thank youl
Hi Paul can you please make a video on to update pivot table when user changes particular cells. Example I want to create a p& l iin excel with pivot table. I want to update it based on the financial period i.e from and to date. Dont want to do that with slicers but want to do it with excel vba. Thank you
Hi Paul I have got a data in a serious of columns and row which change every time. What I want to achieve is I want to add each columns and if the result is different from zero then. In short if you could help me with the code that can loop across the column and sum it. In an if and then Many thanks
Thank you for another fantastic video, I didn't know you could do that with the watch window! With the code Sheet1.range"A1:C1").value = split(s,":") (11:19) does this need the array to work or is the array element of this redundant?
Thanks. Split will convert the string to an array and that get's written directly to the worksheet. You can store it in an array if you need to perform so extra steps.
So if I have used the current region, an array can I compare each element from sheet 1 to mapping column name ( Order of columns can be different) from sheet 2 to highlight mismatches both ways. I am using power query using multiple join methods as separate queries.
The Split() approach is good. Extremely useful for doing what the name suggests, not just for debugging but also for merging and separating multiple data in a return value string, when you cannot be bothered doing something nicer. The Parent thing is also very nice to keep in mind. It could easily happen that all you have is a worksheet refeference, but actually you need to save the workbook. You could overhaul stuff and make that reference available or just go with Worksheet.Parent.Save. Also very nifty hack inside the hack to adjust the expression in the watch window. CurrentRegion is dangerous unless you know that your data will not contain empty rows or columns. Do use it if you can be certain of that but otherwise my feeling is that it has the potential to be one of those PITA sources of errors that is just not particularly obvious unless you're actively looking for it. Finding end points the traditional way seems safer to me. Using Excel tables is nice, if that is how the data is formatted, but keep in mind that Excel table columns cannot have the same name. And I have seen many, many data ranges in Excel that would then suddenly get renamed if you just force it to be an Excel table, so I prefer simply writing a bit more code over using Excel table specific code. The next hack, the array thing, is in my view not optimally described. The hack is not simply that data is read faster from arrays than from ranges or cells in the workbook. This part is trivial and fairly obvious. Reading from an array does not require any interfacing between VBA and the Excel spreadsheet layer. Reading from specific cells obviously must require that. The real hack here is in loading the entire data range into an array in a single read, which is such a monumental time saver when working data. You can do it with the CurrentRegion or just by using Range(cells(1,1), cells(eRow, eCol)), and now any single cell access will happen at array speed instead of spreadsheet speed. Lastly, the "halt on all errors" approach to stopping error handling from running away from the error. That is a good tool to remember, but of course the example code used was a bit on the rubbish side. One should aspire to write code that doesn't crash and burn just just because a data range had a string where a numerical value was expected, and if crashing is the right thing to do then a proper crash message should be composed. In a perfect world, there would even be enough time for that. One last thing to comment, please do not use these very relaxed and casual variable names that Paul is using. Just calling an array "arr" and declaring it as standard variant is like naming your kid "person". It is not a big deal in small examples but if you have 30+ variables with all kinds of junk in them then having half-decent naming of variables becomes nice. It is a bit more typing, but if you ever run into a legacy project with a few thousand lines of code then you'll hero-worship the predecessor that took that took the time to do that. Conversely, imagine having that legacy project but everything is a mix of a, b, c, aa, ab, ba, and so forth? And everything is just variant all over the place.
Great hints! I was kinda lucky I knew them before, because our coach is awesome. But his availability is limited. I just learned about the split from him last month and it made my jaw drop. Speed of arrays is, I think, well known if you code a bit longer. But yeah, that was something I learned from him as well. DataBodyRange I've learned last month by myself (digging). I generally prefer to work on tables, because of power query and quality of life in general. CurrentRange is useful, but I somehow end up using UsedRange, because it also resets the scroll bar. It's useful in cases when you use same workbook during the month and with each day there's more and more data and then on the new month the sheet is barely filled. Without UsedRange the vertical scroll would be small like there was the data from whole month. The minus, or sometimes advantage, of UsedRange is that it would grab the range of all of the data, even the data after empty column. So for example cannot be used when next to the table there are supporting cells, such as =TODAY() and =MAX(ItemsDateColumn) - to get today's date and report's date. So both are useful in their own way. PS Due to the fact you do very great job at explaining things I have few recommendations: I am currently learning conversion from arrays to collections and from collections to arrays to print the results. It's so not intuitive to me and I still mostly copy the code and rewrite it for my own purposes. I could use some more hints. Especially adding new items to existing collections while in the loop. Probably that's because I still don't understand "New" before variable type. Too much knowledge in short time :D Eh... I've just checked your playlist... sorry. You have it all. So I better jump into it. Also Enums and Type various use cases would be awesome, too. I found that usage of Type makes code very clean in some cases - when you can group some parameters (strings, integers etc) into groups. I cannot find many use cases for Enums though :( but it's also not intuitive for me as most things I would like to put in Enums are strings with spaces and slashes, and that doesn't work.
Thanks very much for your feedback. If you're not sure about enums then checkout the Range.Paste function. The parameters like xlPasteValues, xlPasteAll etc. are enums. There are actually lots of them within VBA.
@@Excelmacromastery oh I understand the existing ones, which are built into excel. I kind of don't understand what and when is worth to put into Enum which is created by myself in Class module. I have already watched your 3 videos on classes and code architecture since yesterday. Such a dense but great stuff. Thank you!
Hi! Error 50290 worked around? I can recreate the error by opening a drop down list and while it is open, click on a macro that takes me to a different sheet like shData.Select shData.Range("A1").Select Works unless dropdown list in cell is open. 50290 Error occurs. Any workarounds?
To make shape macro not bug out when drop down list is open, I effectively coded my own double-click for macro shapes in vba. Never found a single video to do so. Static, if Long = 1 then subtract 1, Mytime = hour(time) * 60 + minute(time) * 60 + second(time) exit sub elseif long = 0 then add1, mytime2 = same formula as mytime, Timediff = mytime2 -mytime end if If my time greater than 1 then exit sub Elseif timediff less or = To 1 then Call sub end if. That's my solution.
I had a tool that takes templated ranges from worksheets, with place locators in it and creates text based ladder logic. I would go through many thousands of lines of tag information and replace the items from the tag worksheet into the placeholders in the templates, and then write the templated ranges out to a new workbook that could then be copied into the PLC program. . The original tool I built would run over night to complete, assuming we didn’t hit an error. After seeing your videos I converted all the code to use arrays instead, and some of your other optimization techniques, and it would complete the exact same task in a few minutes at most. I just never thought to look for a better way, and what you don’t know, you don’t think to look for it. Thanks again so much for your content Paul.
That's great to hear. It's very true what you say. If you are new to VBA you may have no idea that you can use arrays to write your code much faster. Most online examples use range so you think that is the proper way.
People say VBA is dead. But it will be here as long as there is Excel. Hahaha. Btw, instead of doing loop, what's your thoughts on range.formula then insert excel formula using any worksheet.function? I used if to automate my vlookup function. Hahahaha
The CONTENT of what you covered is Excellent, but THE SPEED in which you covered the material was UNREALISTIC for anyone to learn/understand. You both speak and move the mouse around @ 100 mph.
Your videos are always great, however you should consider ditching this Windows 98 mouse you use and take a more silent one. Yout keys are silent but the mouse click is pretty annoying...
Paul, you really need to include some info on what references you are including (and those that cannot be included because of conflicts). Initially the code would not compile. Sorry if i missed it somewhere.
Call me a dreamer, but one day I'm hoping that a worksheet will have a row zero for column headings. Preferably optional by worksheet. Maybe even negative numbered rows above that for the rubbish that idiot users put above actual data that should be in a header.
@Phoenixspin I do not mean to be unkind. Try doing other things for a few months, then maybe come back to VBA later. Or maybe never. Your path will present itself at some point.
![일상 속 기분 좋음 [Proto disco meme]](http://i.ytimg.com/vi/DwmEIaZ7XGQ/mqdefault.jpg)
Let me know in the comments if you plan to use these hacks in your own code. Make sure to download the source code from the link in the description.
The tips about referencing tables in the get range section is hugely helpful. I've got a project where I need to pull data from a certain column of a vendor-supplied spreadsheet, but some months one column gets removed, a column might get added, or the columns have been reordered for some reason so I never know which column I should be looking at (I can't just say "look at column G" because it won't always be that same column) and I have some unnecessarily complex code in my macro to find the right column - with this I can just convert it to a table and reference the header name, which is so much simpler and more flexible. I can use the array tip for reading data too to speed it up since it's often a lot of lines (sometimes 50K+). I actually think I have a few macros that I can take advantage of the array tip to read data faster for - definitely a good Monday morning project. :)
I've always been using the Split function because its easy to handle with, now I'm using arrays and class modules that I learned from the previous videos of yours. Thanks for the hints Paul.
Hi, nice tips for intermediate users.
For the pros:
As performance is always an issue with VBA, working in memory with arrays instead of Excel objects which are visible (e.g. cells) is way faster. Instead of writing each single array value in a cell just dump the array in a defined range the size of the array. This is x times faster as it is one single line. When manipulating large tables for future use, try creating an "Entry" class with the fields as attributes and put the instances in an array. This allows you to address everything easily and is extremely fast as VBA handels objects quite well. Best alternative, however, put it in a database and connect with ADODB to the DB using SQL. When considering Access as the DB alternative, connecting to Access via a Excel file with VBA, this is even faster than executing the code in Access.
Hey paul, i got a promotion by studiyng with your videos!! Thank you!!
I love the split function in VBA, I use it all the time, especially for parsing range addresses. A tip for others is that you can add a second set of brackets to return the split number, e.g. Split (s, ":")(1) to return the second item.
I use it along with FullName or Path to navigate between folders
I like how you use the watch window. I never thought of adding properties to it like the address query you added. Thanks
Thanks Seamus.
Great work as always! I must admit I didn't know about checking properites in the watch window, so big plus to you! I have been using these tips for some time yet, but I saw them for the first time on your channel long ago :) I agree they were gamechangers.
ty.
these are always super helpfull when comming to a new syntax etc ^^
A lot of your videos have changed my coding to the better... both to the eye and to the runtime stability. I've been doing this for almost 20, years but somewhere the learning curve phased out and I started to "do with what I had", which meant long detours instead of direct functions, ex nested loops and Mid to find strings instead of simple Instr (and really dangerous Resume Nexts just to get to End)... not to mention 'time'. Your quote "What people often do when they start VBA because they don't know any better", from your 1000x faster video, made me laugh... because I knew you were talking about me. I haven't "just started", but there's certainly a lot I should know better after 15+ years. Your videos have given me much and I'm sure that your coming ones will.
Glad you have found the videos useful.
@@Excelmacromastery I'm not the only one benefitting from your videos. So far at least 3 of the tools I've done for people at work have been updated since I stumbled on your collection of uploads. :)
Thank you for another valuable video.
I remember reading (rightly or wrongly) that Excel and VBA are in different memory processes, which creates a performance hit each time you go between them, so I always minimise traffic between VBA and the worksheet, reading all the sheet data that I need into arrays at the start, doing all the work in memory, then writing results back in as few statements as possible, ideally one figure or table.
The other performance hack I think everyone should know is dictionaries.
That's a very efficient way to do it Dermot. Make sure to check out my Arrays Class that adds a ton of functionality to arrays(th-cam.com/video/ZYxa-Q-nSX0/w-d-xo.html).
I didn't know about the watch and the error-break hacks. These are really helpful in debugging, which often takes longer than the coding itself. I hope you have a video or article that focuses on debugging tricks (not the textbook methods) to quickly nail down problems.
faster than a speeding bullet! Thank goodness for the pause & rewind buttons...
My thoughts exactly!
Yes. Try this: click on the gear (settings button) beneath the video, and change the playback speed to .75. It helped me a lot.
@@houstonvanhoy2198 hmmm, 25% better!
Paul, I like the pace of your tutorials … even though I occasionally adjust the playback speed as suggested by others.
When you can watch without pause and rewind you are a true Jedi Master😉
Thanks Paul. Love the watch window edit. I had no idea you could add .address to something while in the watch window.
Glad to help!
Wow, awesome video!!! Thank you so much for sharing these cool tips!!!
What's that clsTimer you are using? Is it that one using the QueryPerformanceCounter and double integer values?
تمثل اللغة تحدي جدي بالنسبة لي لكي اتابع فيديوهاتك ، فانا ابذل جهدا مضاعفا لكي احصل على الفائدة القصوى ، و مع ذلك استمر في متابعة منشوراتك لاني اعلم انك الافضل .
شكرا بول .
Hi Paul.. than you very much for all your lessons about vba excel. With all of those i can make my pos application much much more faster.
Excellent Budi
Very insightful video 📹 Thank u for your hard work
Whow! Once again: I learned a lot! The only thing I already knew about was the timing advantage of arrays over ranges. And I knew about list objects. But all other topics: well worth the watch! :-)
Glad it was helpful!
That error handling hack would have saved me some grief in the past for sure.
I liked the turn off error handling idea, but would it not be 'safer' to before the , the code will stop at i just befofre the issue row.
I do a lot of work with HMI displays that utilize DHTML and scripting, the split function is invaluable for building strings that can be passed to the execute command or eval function.
Thanks for the feedback.
Hi Excel Mastery :) I`m not very advance in VBA. Could you tell me how I can use your examples from Immediate window in normal worksheet ? thx
😁
Hi Paul. Your teaching is nice. I learn some new things on watching each of your videos.
Glad you like them!
Nice video. I regularly follow your video. Your teaching is so excellent.
Please make a video- Excel data send through what's app. Data may be jpg, pdf, text accordingly.
Thank you very much for your videos and instructions and tips. Not sure why, but the shortcuts to select regions, Ctrl * gives me a "Cannot show outline symbols" message. and the Ctrl Shift * gives me a "Printer Installer Client Console".
As always, amazingly well explained!
Edit:
I'll use your Code but I need to understand the Magie, accordingly I'll write it by myself! Thanks alt sir!😉🤟😎
You're welcome John.
@@Excelmacromastery please excuse the auto-cerrecture- mistakes! Thanks for all your content! I enjoy every single minute!👍👍👍🤟😎 BR from Germany! Cheers!🍻
Great Video
A remark:
In the Use Range macro, you should save the last row of the range in a variable, and then put: For i = 2 to LastRow. I think it would be faster.
Greetings from Bolivia.
Hi Jose, I haven't tested it but I don't know if it would really make much difference to speed. VBA might optimise the For loop for this.
Thank you for this great video. Well explained and useful
You're very welcome Jan! Thanks for your ongoing support.
Great video, as always! Thanks very much for posting.
When using LBound(arr) and UBound(arr) in the For loop header, does VBA reevaluate those functions at each pass through the loop or does it optimize performance by evaluating them only once? Generally, when I am looping through an array and I know the lower and upper bounds, I store those in variables such as inLBound and intUBound and use those variables in the construction of the For loop. Thank youl
Thanks for everything.
Hi Paul can you please make a video on to update pivot table when user changes particular cells. Example I want to create a p& l iin excel with pivot table. I want to update it based on the financial period i.e from and to date. Dont want to do that with slicers but want to do it with excel vba. Thank you
Hai Paul. Can you please explain how to fill a form in a website form using vba excel? Thank you
Ive learned so much with your vids.... Thanks!
Glad to hear it Roger!
Harika yöntemler. Teşekkür ederim. İyi Çalışmalar..
Hi. In the last section you show error handling and debugging. How do you get the error handling to give the location of bad data?
Hi Paul. As always, great tips! Thanks for sharing :)) Thumbs up!!
Thanks for watching Wayne.
Thank you Paul. Once again great video!!! :)
00:00 - Introduction
00:19 - Manipulating Text Hack
02:32 - Debug Properties Hack
04:30 - Get Range Hack
07:58 - Read Data Faster Hack
09:43 - Find Error Fast Hack
@planxlsm
Very useful. Thank you.
Nice Video. Thank you.
Hi Paul I have got a data in a serious of columns and row which change every time. What I want to achieve is I want to add each columns and if the result is different from zero then.
In short if you could help me with the code that can loop across the column and sum it. In an if and then
Many thanks
Thank you for another fantastic video, I didn't know you could do that with the watch window! With the code Sheet1.range"A1:C1").value = split(s,":") (11:19) does this need the array to work or is the array element of this redundant?
Thanks. Split will convert the string to an array and that get's written directly to the worksheet. You can store it in an array if you need to perform so extra steps.
So if I have used the current region, an array can I compare each element from sheet 1 to mapping column name ( Order of columns can be different) from sheet 2 to highlight mismatches both ways. I am using power query using multiple join methods as separate queries.
For the split I use
For each item in split(string, "~")
.......
Next Item
I already knew these.
Great tips, super useful. I will use this for sure !
Thanks Matthieu.
Thanks, this is was very useful
The Split() approach is good. Extremely useful for doing what the name suggests, not just for debugging but also for merging and separating multiple data in a return value string, when you cannot be bothered doing something nicer.
The Parent thing is also very nice to keep in mind. It could easily happen that all you have is a worksheet refeference, but actually you need to save the workbook. You could overhaul stuff and make that reference available or just go with Worksheet.Parent.Save. Also very nifty hack inside the hack to adjust the expression in the watch window.
CurrentRegion is dangerous unless you know that your data will not contain empty rows or columns. Do use it if you can be certain of that but otherwise my feeling is that it has the potential to be one of those PITA sources of errors that is just not particularly obvious unless you're actively looking for it. Finding end points the traditional way seems safer to me.
Using Excel tables is nice, if that is how the data is formatted, but keep in mind that Excel table columns cannot have the same name. And I have seen many, many data ranges in Excel that would then suddenly get renamed if you just force it to be an Excel table, so I prefer simply writing a bit more code over using Excel table specific code.
The next hack, the array thing, is in my view not optimally described. The hack is not simply that data is read faster from arrays than from ranges or cells in the workbook. This part is trivial and fairly obvious. Reading from an array does not require any interfacing between VBA and the Excel spreadsheet layer. Reading from specific cells obviously must require that. The real hack here is in loading the entire data range into an array in a single read, which is such a monumental time saver when working data. You can do it with the CurrentRegion or just by using Range(cells(1,1), cells(eRow, eCol)), and now any single cell access will happen at array speed instead of spreadsheet speed.
Lastly, the "halt on all errors" approach to stopping error handling from running away from the error. That is a good tool to remember, but of course the example code used was a bit on the rubbish side. One should aspire to write code that doesn't crash and burn just just because a data range had a string where a numerical value was expected, and if crashing is the right thing to do then a proper crash message should be composed. In a perfect world, there would even be enough time for that.
One last thing to comment, please do not use these very relaxed and casual variable names that Paul is using. Just calling an array "arr" and declaring it as standard variant is like naming your kid "person". It is not a big deal in small examples but if you have 30+ variables with all kinds of junk in them then having half-decent naming of variables becomes nice. It is a bit more typing, but if you ever run into a legacy project with a few thousand lines of code then you'll hero-worship the predecessor that took that took the time to do that. Conversely, imagine having that legacy project but everything is a mix of a, b, c, aa, ab, ba, and so forth? And everything is just variant all over the place.
Great hints! I was kinda lucky I knew them before, because our coach is awesome. But his availability is limited.
I just learned about the split from him last month and it made my jaw drop.
Speed of arrays is, I think, well known if you code a bit longer. But yeah, that was something I learned from him as well.
DataBodyRange I've learned last month by myself (digging).
I generally prefer to work on tables, because of power query and quality of life in general.
CurrentRange is useful, but I somehow end up using UsedRange, because it also resets the scroll bar.
It's useful in cases when you use same workbook during the month and with each day there's more and more data and then on the new month the sheet is barely filled. Without UsedRange the vertical scroll would be small like there was the data from whole month.
The minus, or sometimes advantage, of UsedRange is that it would grab the range of all of the data, even the data after empty column. So for example cannot be used when next to the table there are supporting cells, such as =TODAY() and =MAX(ItemsDateColumn) - to get today's date and report's date.
So both are useful in their own way.
PS Due to the fact you do very great job at explaining things I have few recommendations:
I am currently learning conversion from arrays to collections and from collections to arrays to print the results.
It's so not intuitive to me and I still mostly copy the code and rewrite it for my own purposes.
I could use some more hints. Especially adding new items to existing collections while in the loop.
Probably that's because I still don't understand "New" before variable type. Too much knowledge in short time :D
Eh... I've just checked your playlist... sorry. You have it all. So I better jump into it.
Also Enums and Type various use cases would be awesome, too.
I found that usage of Type makes code very clean in some cases - when you can group some parameters (strings, integers etc) into groups.
I cannot find many use cases for Enums though :( but it's also not intuitive for me as most things I would like to put in Enums are strings with spaces and slashes, and that doesn't work.
Thanks very much for your feedback. If you're not sure about enums then checkout the Range.Paste function. The parameters like xlPasteValues, xlPasteAll etc. are enums. There are actually lots of them within VBA.
@@Excelmacromastery oh I understand the existing ones, which are built into excel. I kind of don't understand what and when is worth to put into Enum which is created by myself in Class module.
I have already watched your 3 videos on classes and code architecture since yesterday. Such a dense but great stuff. Thank you!
Final tip about turning error handling back on:
Can this be done within VBA?
Excellent tips!
Glad it was helpful!
Very interesting ton link table and VBA. 🙂
Hi sir,, how to implement mouse click and drag during running simulation using vba?
Very nice video sir ! Thank you
Amazing content, thank you!
Glad you enjoyed it!
Awesome sir, thanks for knowledge update
Always welcome
SIR Paul!!!
Hi! Error 50290 worked around? I can recreate the error by opening a drop down list and while it is open, click on a macro that takes me to a different sheet like shData.Select shData.Range("A1").Select
Works unless dropdown list in cell is open. 50290 Error occurs.
Any workarounds?
To make shape macro not bug out when drop down list is open, I effectively coded my own double-click for macro shapes in vba. Never found a single video to do so. Static, if Long = 1 then subtract 1, Mytime = hour(time) * 60 + minute(time) * 60 + second(time) exit sub elseif long = 0 then add1, mytime2 = same formula as mytime,
Timediff = mytime2 -mytime end if
If my time greater than 1 then exit sub
Elseif timediff less or = To 1 then
Call sub end if.
That's my solution.
Thanks
Genious! That is so helpful
I had a tool that takes templated ranges from worksheets, with place locators in it and creates text based ladder logic. I would go through many thousands of lines of tag information and replace the items from the tag worksheet into the placeholders in the templates, and then write the templated ranges out to a new workbook that could then be copied into the PLC program. . The original tool I built would run over night to complete, assuming we didn’t hit an error. After seeing your videos I converted all the code to use arrays instead, and some of your other optimization techniques, and it would complete the exact same task in a few minutes at most. I just never thought to look for a better way, and what you don’t know, you don’t think to look for it. Thanks again so much for your content Paul.
That's great to hear. It's very true what you say. If you are new to VBA you may have no idea that you can use arrays to write your code much faster. Most online examples use range so you think that is the proper way.
Great video. We want more hacks! :D
I'll see what I can do:-)
People say VBA is dead. But it will be here as long as there is Excel. Hahaha.
Btw, instead of doing loop, what's your thoughts on range.formula then insert excel formula using any worksheet.function? I used if to automate my vlookup function. Hahahaha
The CONTENT of what you covered is Excellent, but THE SPEED in which you covered the material was UNREALISTIC for anyone to learn/understand. You both speak and move the mouse around @ 100 mph.
Congratulations and many thanks. I am looking forward for your new video.
Thank you! 😃
Your videos are always great, however you should consider ditching this Windows 98 mouse you use and take a more silent one. Yout keys are silent but the mouse click is pretty annoying...
lol - That's a sound effect not a real mouse.
array is much faster than range.
Paul, you really need to include some info on what references you are including (and those that cannot be included because of conflicts). Initially the code would not compile. Sorry if i missed it somewhere.
The code should work as is. What error did you have?
6. hack VBA = macro parameters
7. hack VBA = click and hide / view
Call me a dreamer, but one day I'm hoping that a worksheet will have a row zero for column headings. Preferably optional by worksheet. Maybe even negative numbered rows above that for the rubbish that idiot users put above actual data that should be in a header.
I hate VBA. I've never understood it and I'm not even motivated to watch this video. Sorry.
@Phoenixspin
I do not mean to be unkind. Try doing other things for a few months, then maybe come back to VBA later. Or maybe never. Your path will present itself at some point.