5 More things I wish I Knew When I Started Using Excel VBA

hello and welcome to this video on five things that I wish I had known when I started with Excel VBA the first five things video that I polished was very popular so I recommend that you check out that video if you find this one useful so in this video I will show you five techniques to rap beginner level to intermediate and the final one is for more advanced users so let's get started on the first one using the complete word so what is complete word and why do we need it well if we look in the menu under edit you can see that we have complete word at the bottom and the charcoal key is ctrl + space so what is this all about well if we type orgy here which is a range variable we add the dot what happens is that VBA gives us a list of options and we can use by typing in letters we can go down to the one that we want when we find the one that we want for example cells we can press tab our space to finish the differences space gives us a space and tab it just completes without the space to press tab can we get what we want but what complete war does is in the case that we're not looking for a property or method of an object so for example imagine we want to use something like the timer so we type T I and then we do control space and we get all the various options for time so we can just go down here and select it so this is very very convenient for seeing what type of things are available and also for selecting them very quickly now one way I find this very useful is if we have our variables like this suppose we have a customer name for example and we'll make that a string and the second one we have will say old customer name as a string now because there's nodding with see us so if we type see us like this when we press autocomplete word like control space it completes the full word and we can say equals here and then if we do all the new control space because there's nothing else with old it automatically finishes the word so using control space is a very very quick way of writing code so for example we can say things like this workbook we can finish that and we can say worksheets go down here and then we can put in the range or the name of whatever we want so you can see that complete word is very very useful so the second technique I use is the view definition from the menu now I have to say when I saw this first I really was underwhelmed and didn't really realize how useful it was so what we can do with definition is for example imagine we've code here and we're calling a soap perform calc and so we want to see exactly what the definition is well we can right click and select definition and it brings us to the definition of it now we can also search but sometimes when we search if there's lots of mentions of it in the code it can take a while to get there so just using definition is very very easy and it also means that we're actually looking at what VBA considers the definition we're not getting mixed up with another soap now if we want to go back we can just right click and we can just go to last position so the definition the shortcut key is shift F 2 and to go back it's control shift F 2 so to go to the last position now another way I find this useful is imagine we have like a soap and it's just got a lot of course so there's a lot of code here just imagine that and we see something like result way down here and we're wondering what it is we can just right click and just go to the definition and VBA will bring us to where it was declared and even if it's a global variable it'll bring us to the global place or if it was a parameter it'll bring us there so that's very very useful now the final way that I really like to use this is when we're trying to look at constants in VBA so if you look at VB Day it here you can see the Phoebe days is a constant so what happens in this code running this code and we're checking if this value is a days so if the type a value is a date so we run the code and let's put this in our watch window now it turns out that it's giving us the value back eight and VB date is seven so what we want to do is we want to figure out well what's the other value what is it what what type is that so what we can do is we can just click on VB days and do shift F to for definition and it brings us up all the members of VB vert type so basically all its siblings and then we can go down and we can find out what it is so if you look at the bottom in the gray pair you can see that it shows the value for example it says we beam all equals one so if we go down we can see that string equals eight so now we know what the value of the string is so this is very useful for any type of constant in VBA if we want to see all your valuables that are available now to close this window we can just click on the X in the top right or we can press ctrl + f4 so the next thing I want to look at is the address of the range and this is something I find very very useful anytime that I'm debugging code so imagine we've got some code like this and what our code is actually doing let's have a look at the Excel spreadsheet and what our code is actually doing is any item board that's greater than 45 it's writing out the first name here so if we look here the very first one we have should be Billie and the next one that we have should be Winifred but obviously we're getting the wrong values on the first name so clearly something is wrong with our range so the easiest way of whom B when something is wrong with our range the easiest way to figure out what the range is is to put a breakpoint here and then when we run the code we basically just grab the whole range so the range I've deliberately made it look complicated here with different numbers in it so rather than trying to calculate it out we basically just drop it into the watch window and let me bring off the watch window here and we put in address so the address of the range and this shows us exactly what the address is and we can see that the address is a 6 so if we go back and look in the code we'll see that a 6 is wrong it was a 4 that we wanted so we're two rows out and so if we look in the code again we will see that the offset value was wrong so it's so anytime that I have a problem with a range this is always the first thing that I do I drop it into the watch window and see if it's the right range now sometimes you're looking at the range and the range might be right but you're not a hundred percent sure that you're actually on the right worksheet now that's a second problem especially when active sheet or something like that might be used so what we can do to find a worksheet is add the range again and then instead of address we add the parent so parent gives us back the range worksheet and then we just simply ask for the name and now we can see that orders is the worksheet now following on for that sometimes you might want to even make sure that you have the right word book so the right workbook you've probably guessed what it's going to be its parent the parent of the worksheet is the workbook so its parent parent name and you can see I'll just make this watch window just a bit bigger you can see that we've got the workbook name so again just to go over them again the address gives us back the address of the range the parent dot name gives us back the worksheet and the parent parent name gives us back the workbook so this is a very good way of checking if your range is valid so years ago when I started working with VBA I thought that if you wanted to get a range from the user you had to be AC give the user a user forum with a text box and ask them to put in the range and then you have to verify it little did I know it was a much easier and more powerful way to get the range so what we can do is we simply use the input box from application and we set the type to 8 so let's go back to the spreadsheet and run the code we say a set color and then it says please enter range and the user can just select the range from the worksheet and then we click OK and you can see that it turned the range to orange so this is a very very useful way of getting the range from the user and it requires very little code now if you want to see more about the different types we can use on the input box you can check out my website so there's a free post on the VBA input box and if you just want to see two different types that it has you can just click on the input box type of parameter options and it shows you two different types that are available so we're on to the last technique now and this is a bit advanced but it's a really really good one imagine you have a workbook like this and you want to read data from the workbook so this is very commenting in VBA so one of the problems we often have is that we have to read lots of workbooks and we have to open and close them all and sometimes if the workbooks are on the server or something like that it can get quite slow so it'll be nice to actually read data from a workbook without having to actually open it and so it's possible to do that in VBA by using a do so what always is basically ActiveX database objects and we use this to connect to databases like Access or sequel server and read the data from them so we can do the same thing with a worksheet and what's more it's quite easy to do so let's take this file and we're going to close this file and then we're gonna read the data from it and we want to put the results right here so let's delete what's there and let's have a look at the code so you can see the code here and I'm just gonna tear all different things here so to get rid of it everything else now if you look at the code here what we can see is that basically the first thing we're getting the file name it's very straightforward we're just getting the file name from the current path so very simple now what we need to do next is we need to get the connection now this is the connection string so we do this with with if for it's a database or something so it looks quite complicated but actually you can just use everything here the only thing that you need to change yourself is the file name that's all you need you can just use the rest as it is after then we get the connection we need to have a query and so the query is like SQL and this means it's actually quite powerful so we can do many things with it so for example in this one we're going to group all the data by the first name and we're going to sum the amount so once we have the query and once we have the connection we basically just create a record set this is where the data goes and we basically just run the query on the record set using the connection and it places the value in our record set and then we can use copy from record set this is like a range function and it writes out all the data so if you think this is a bit complicated at first it's going to be the same pretty much every time the only difference is as I said is the file name and the query that you run so let's run the query so we can actually see it in action so the workbook is closed that's the key point here and we run the code and you see it brought everything back now let's try this again the beauty of sequel is we can do a lot of stuff with it the sequel queries so when this one we're gonna select everything from sales for the first equals Alan and that's one discord and you can see it brought back all the data so by using sequel we have a lot of power in our queries if you want to try the ATO code for yourself you can just get it from my website I'll leave the link in the description it's basically on the page excel vba copy the complete guide to copying data and you can just go down to the table of contents and it's using aerial and SQL you see an example of the code here and I've also included a lot of different ad or queries so you can try them all for yourself thanks for watching this video I hope you found it useful if you would like to get notified when my new videos are published then please click on the subscribe button and then click on the bell icon beside it now if you'd like some more free excel vba resources then check out my website Excel macro mastery comm there are major articles on all the major areas of Excel VBA each article has an easy-to-navigate table of contents as well as a quick guide that allows you to easily find the syntax you need and there's tons of coding examples that you can copy and use in your own macros you'll also find techniques that are not available anywhere else I also have a VBA tutorial and in this tutorial there's lots of activities and solutions so that you can try them all for yourself and it's all absolutely free so that's all for me and I hope to see you on my next video

Channel: Excel Macro Mastery

Views: 52,369

Rating: 4.9598799 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, visual basic, excel vba programming

Id: v-ETVJ83ivY

Length: 13min 30sec (810 seconds)

Published: Wed Aug 28 2019