5 Killer Excel VBA Tips Everyone Should Know

hello and welcome in this video we're going to look at five killer tips that everyone should know in Excel VBA now these tips are very useful and I use them all the time myself now if you like this video please click on the like button so let's get started with the very first tip so the first tip we're going to look at is a very simple one but it's very useful and I think it's a tip that most people don't know about so imagine we want to search for the world collection we click on collection and we do ctrl F and that appears in the dialog and then we can set whatever settings we want so for example we can set it to current project and then we click on find next and we keep clicking on find next as it cycles through all the different instance of the keyword collection until it reaches the end and then we get this message now what we can actually do is if we click on collection we can press ctrl F tree and it will automatically search for the word collection and after that we can just hit F tree and it will keep cycling true and keep finding the next one and this is all based on our current selection so in other words whatever the settings are in the find dialog these are the ones that control F tree follows so if we change this to current procedure and then let's just do a ctrl F tree again you can see that it never leaves the write data function because it's going on the settings in the find dialog so this is the first tip it's a very simple one but it's one that I use all the time so tip number two involves copying modules so Cobian modules is quite easy but there's simple ways to do that most people don't know about now imagine we have a module called error handling and we want to copy this somewhere else or we want to back it up so we can easily do this by right-clicking and selecting export file and then when we select export file we just simply select where we want to do it now I have it in the code repository here and we we simply click on save and then we have our own version of error handling now if we want to add error hanging to a new project so let's imagine we have a new workbook what we can simply do is right-click and select import file and then select error handling and you can see when we go down to the modules you can see that it now has error handling so let's look at another way of importing the file let's remove error handling like this we remove it we say we don't want to back it up and then we go to the folder so we open Windows Explorer and we find where the file is and we can easily add this to our book 1 by simply dragging the file in to book 1 and you can see we double-click we now have the error handler module in book 1 so it's a really handy way of doing it now let's remove it and let me show you the final method to do it so imagine we have the two workbooks open and error handling module is in one and we want to create a copy in the other one so we don't have to actually export the file what we can do is just drag it from here and drop it into book 1 and now we have a copy of error handling in book 1 so this is a very easy way of copying modules between different workbooks either we have the modules open and we just copy between or we can just export the file and import the file now exporting the file is very useful if you want to keep a backup of it and if we want to keep different versions so that's tip 2 how to copy modules so one thing that confused me a lot when I started with Excel VBA was how to figure out the events so VBA has worksheet events and workbook events like when something changes on a worksheet so I couldn't find an easy way to see a list of the available events and then when I wanted to create an event I had to like google and find the event somewhere and copy that syntax but then one day I found a very easy way to create events and the way we do it is we select the sheet that we want the worksheet event far so we double click on it and then on the left at the top where it says general we select worksheet now when we select worksheet it automatically puts in a selection change event now if there's no event here basically VBA can keep it open so this is kind of a strange quirk of VBA that I don't know what the reason for it is so we can create our own event and we just selected from the window here and let's say we want to do a change event and we select change and you can see that it appears now we can get rid of this one because there's at least one event here and you can see we now have our worksheet change event so let's test it out we'll just have a message box from what worksheet changed does is when a cell changes this event kicks off so we can just say target let's do target which is the cells that change and the address so when we change the value in a cell this will give us a message box with the address of that cell let's do alt F 11 and let's do two and we hit enter and you can see that it displays b2 so now let's do C 2 and you can see that it says C 2 was the one that changed now let's delete what's in both of these and you can see that it selects B 2 and C 2 so you can see it's a very simple way that we can select the events on any of the worksheets and you can see all the ones that are there so all the ones that are available are in this list so it makes it very very easy and as I said when you select one it basically just creates the event for you now the same thing happens for the workbook if you want to workbook event we basically double click in this workbook and we select workbook now again it automatically generates an event so again we can look in the right hand side drop down and you can see all the different things available you can see like workbook open window activate window deactivate all different things she'd activate sheet before delete and so on and you can select any of these to use with the workbook so let's try one let's try the activate because it's quite a simple one to use and when the workbook is activated what we will do is we'll do a message box and the message box will basically say activate it and what we'll do is we'll put down the name of the workbook which is this workbook and name so let's create a second workbook so we just open a new one and then let's go back to the previous workbook and you can see that it said activated book 1 because we activated this workbook so you can see this is a very simple way of finding all the different events that are available and adding the events to your workbook or to your sheet and as I said this wasn't obvious to me when I started with Excel VBA and took me a while to find out so that is tip number 3 so this tip is more advanced as it has to do with error handling and this is one very useful thing and you'll see it if you look in my error handling selection of videos now I really liked this little tip so that's why I've took it out and I'm gonna show it in this video imagine you have your code and has error handling and somebody is reporting an error so you run the code and it basically tells you ok does an error somewhere in weed data now if you had line numbers I might even be more accurate but what you want to do is stop for the errors because the error could be in a loop so even if you have the line number it might not stop the first time it could be like the tenth time or something so what you want to do is kind of go to when the error occurs so how you can do this is by basically just turning off the error handling code and this is very simple to do you just go to tools you go to options and you go to general and the default of error trapping is break on unhandled errors but we want to break on all errors which basically is saying ignore error handling so we click OK we run the code and you can see it found the error we select debug and it stops exactly where the error happens you can see for this error it's happening where is tree so what's happening on the third loop but it could happen if you did a lot of data could be like under 1,000 on a 10,000 it could be way down so this is very useful to get you straight to the error it was very important that once you finish and you have the error solved that you turn the error handling back on tip 5 is again a bit advanced but I think it's very very useful in certain situations and is definitely worth knowing so imagine you give a user a big application and within that application you've got lots of kind of business logic and you have lots of message boxes saying to the user what you've selected given them certain information now when you run the code in test mode you want to add more stuff to it then you don't want to have to spend your time turning off these message boxes in other words you want to keep having to click OK okay so what we can do is we can turn them off like this we can say if and we say say for example debugging equals zero and I'll show you what that means in a minute then so if there's no debugging what we want to do is just show the message box and again let's put a second one here so anytime the cord runs at the moment it just runs as normal it just displays the message boxes just just like this as normal but if we get the code and then we say let's do some debugging and I don't want all these message boxes poppin off what we can do is do tools and we do the project properties so in the conditional compilation arguments what we add is debugging and we say debugging equals one so debugging if the Boggan doesn't exist it equals zero when we run the code now the code just steps over this and finishes it doesn't do any of the messagebox stuff but if we had something outside for example if we had a message box outside let's just say we have a message box here and we run it again you can see that it will run that message box so this is very useful when you're testing the code and you want to take some of the stuff out that isn't necessary while debugging now one thing you might notice here putting if debugging equals zero everywhere in your cord is quite annoying so what we can do instead is create a soul but like this and what this so basically does is it's exactly the same as message box it's just passing on the parameters but the only difference is that what it does is it has this line once inside so what we can do instead of this is we can do my message box and we can get rid of all these other instance of this so that we only have it in one place and then if we run the cord again you can see it goes in and this should be if debugging equals naught and so we run the cord we step in and you see it doesn't it doesn't go to the message box because debugging is set on if we want to turn debugging off we can just simply go to the properties and then we either delete debugging from here or we set it to zero so if it's not here then is the same as it's set to zero so we run this code again and you can see that it turns off so as I said this one's a little advanced but it can be very very useful in situations where you're dealing with code and you're trying to develop the code already does a lot of and you kind of want to skip that stuff when you're testing it or when you're developing so those are the five killer excel vba tips I hope you enjoyed them and I hope you'll find them useful now if you enjoyed this video then please click on the like button and if you want to hear about more of my upcoming videos then please click on the subscribe button in the description below the video I give away a free VBA vitals cheat sheet so make sure to get your hands on that now if any questions comments or queries please add them below the video see you next time

Channel: Excel Macro Mastery

Views: 81,672

Keywords: excel, microsoft excel (software), vba, excel vba, visual basic for applications, excel vba tips and tricks, excel vba tutorial, quick search shortcut, copying modules in excel, add event, turn off error handling, turn off code section

Id: 1UqfJ5DqGa4

Length: 12min 55sec (775 seconds)

Published: Sat Jan 04 2020