5 Things I wish I knew When I started using Excel VBA

hello and welcome to this Excel macro mastery video I've been writing cold for a long time one thing I always try to do when I use a new language or a new technology is to find the most practical and efficient techniques that I can use now these techniques are often little-known secrets that only a small percentage of users know and it take a lot of effort to find out so this video I'm going to show you five things I wish I knew when I started using Excel VBA so one thing I really wished I'd known when I started with VBA was how to use the code name of the worksheet so let's take a look at the code that we have here what we're doing is simply reading a value from the worksheet to a variable so reading the value 3/8 4/8 thumb cell d2 I want to write it out to our immediate window and to do this we get the worksheet by using this workbook which is a conte workbook and then we use the worksheets collection and we basically see the name data give us back the worksheet called data so let's run the code you can see that it printed out 3/8 4/8 to the immediate window so the problem with this code is that if the user comes along and changes the name of the worksheet to cold breaks so let's have a look at this we rename data the data old and now we're going to run the code again and you can see we've suddenly got a subscript out of range so the way around this is that we instead of using this workbook worksheets collection we use the code name so what is the code name well the code name if you look in the project window if it's not visible just select view project Explorer or ctrl R you can see that each worksheet has two names a name in parenthesis and a name to the left now renamed in the left is what we call the code name and what we do with the code name is so if we double click on the worksheet you can see the code name and what we can do is just change it here in the properties window now if the properties window is invisible press f4 our view properties window so instead of using discord what we can actually use is data and we can use it just like this now let's run the code again and you can see that it worked fine it doesn't care about the name of the worksheet because it's referencing the code name so let's change it just to show exactly let's rename it again we'll say data one two tree and then we'll run the code again and you can see that it run fine so this is one really great advantage of known about the code name in excel at PPA okay so onto number two which is the current region so we've got data in a worksheet like this a lot of people try to get the last row then the starting role and then they try and reach you to range and this can become very messy but VBA has a built-in function and it's so useful for getting the data so how it works is like this if we click on anything here and we press the key control Asterix normally on a keyboard is control shift in it and you can see that as highlights everything so this is called the current region so anytime we click on anything control shift and it will highlight at all and what it's doing is its highlighting all adjacent data so if you've got a data that isn't beside this if there's a blank row or one column in between it doesn't bring it back so it's very useful for kind of well-formulated data so how we use it is very simple we can just use sheet data like this range and we can say any cell within us so say a1 and that current region and that will bring us back that range now we're going to store this in a range so we do dim range as range and then we set the range to equal the result of current region and then we're going to display the address in a message box so that's going on discord and you can see that he addresses a 1 to T 15 so you can see this is a very useful way of getting back all the data without having to build up the range by finding the last row and the last column when I started in VBA I actually taught this was the call that you needed to read from a range to an array you actually had to get the range then you had to create the array make the array the same size as the range and then read to every item in the range and copy it individually to the array later I found out you actually don't need all this code at all all you actually need is first of all to declare the array as a variant and this means that VBA will change it at runtime when it decides what it's going to be and the second thing is then just to simply assign it to our range which we can do like this now because it's not a range we basically set it to the value and then we can just delete all this code now let's run the code and let's look in our watch window so our watch window allows us to view what's in variable and you can see the array so let's look at the first one you can see that just like the data it has day sales person quantity sales and let's look in the second one and you see the second row has the day at the first order 7 2019 and gazed and the value 3/8 4/8 so you can see this is quite amazing that in just one line we can copy the entire range and what's more is that if you want to change the array and copy it somewhere else we can actually copy it back to the worksheet also just using one line so for example if you wanted to put it back to H we will do H 1 and then we do ijk which would be the fourth column 15 and then we do value equals and we do array and it's quite simple so let's just move it over here so we can see it actually happening so then we run the code and you can see that it rolled out all the values in just one line so you can see this is very very useful thing to do in VBA so one very useful thing in VBA is the split function and this is how useful it is so imagine we've got names like this so the names come in three parts and we want to break them up into each of the parents now if the names are all a fixed size then we could use left right and mid functions but because it's not we need to use in string as well and the core can get very tricky so let's have a look at the code that we've used for this and you can see the code here so you can see we get the name first of all and then we read the first name we do so by using in strings so in string finds the first space and we basically calculate the left from there now to get the last name it's a bit trickier we use in string reverse which searches for the last space but then getting the middle name you can see gets very messy because we need to know the starting position and the last position and this gets even more messy if we had four or five or six different parents to our string so first of all we run this code just to see how it works so let's look at our immediate window whipping it up here and I just hear that data from a previous run and then just wonder code you can see that it that it works but as I said it's quite messy and it tends to get Messier now what we can actually do with the code is we can use split so if we use play we don't need all this end start first kind of positions we can just do an array like this as a variant it'll be common array and then we just say array equals and we're gonna split it by spaces so it uses space as the delimiter so we use split and now we've got an array so the first name in the array is guess what it's a raid 0 the middle name is now array 1 don't need any of these calculations and of course the last name is array 2 so again let's run this code and see exactly what happens so we'll clear our previous data from the worksheet our from the media we know I should say and then run the code and you can see that it worked just as perfect without any complicated calculations and even if we have 4 or 5 you can see that the code isn't any more complicated so this is the split function which is brilliant for manipulating strings so one very useful function in VBA or a useful tool is probably a better description is the debug compiler so first of all let's look at the syntax checker so imagine we write incorrect syntax like this and we move to the next line you can see that the syntax checker tells us that it's incorrect but the problem with the syntax checker is that it only finds errors on one line or error in the syntax but imagine that we have a for loop and we don't have the next now this is incorrect but it won't be found by the syntax checker so what we do is we use debug compile and debug compile finds errors in our code so these are errors that happen over multiple lines so another error could be that we're not passing enough parameters and we do a debug compile and you can see argument not optional now it finds many types of errors like this when it no longer finds any errors it nothing will happen which kind of is a bit confusing but in the old days when computers were slower this bit up here was actually like a progress bar and you see this if you have a lot of called in your application and you're on a slow computer you can actually see the progress bar but nowadays because computers are so fast it actually happened so quick you don't see it so debug compile and nothing seemed to happen we go back we see that debug compile it's grayed out and what this means is that there was no errors the last time I ran now if you want that enabled we just press Enter on any line so the reason that debug compile is better than running to find errors because if we run for example so let's just change this let's just say we changed the name of this and then we run the code and you can see that it finds the earth just the same as debug compile but the big problem with this is that it only finds errors in the code that you reach when you're running so if you don't happen to go to a sub that has an error it won't find that error and the problem is if you leave your code and come back in a week it looks like your code suddenly has errors and it didn't before so debug compile basically gets rid of that because it checks to all the code so for example if we go to split here for example and we just put in an if statement by mistake and then someone distracts us and then we leave the if statement here and we go back to our code if we do a debug compile tells us straight away that there's a problem so debug compile is the fifth and another very important thing I wish I'd known when I started with VBA so hope you enjoyed this video and I think if you use these five different techniques that I've shown you that you'll find them very very useful they're simple to apply and you can see that they're quite effective now if you enjoyed this video please click on the subscribe button to get notified of upcoming videos you can also add any comments queries or questions below and if you'd like some more details or to see some articles on VBA then please go to my website Excel macro mastery com

Channel: Excel Macro Mastery

Views: 260,330

Rating: 4.9607897 out of 5

Keywords: excel vba, vba excel, excel tips and tricks, visual basic for applications, excel tips and tricks you must know, vba, microsoft excel, excel, 5 Things I wish I knew When I started using Excel VBA, excel macros, excel online course, excel vba tutorial for beginners

Id: DxIzTKgchJ8

Length: 12min 44sec (764 seconds)

Published: Mon Aug 19 2019