7 Simple Practices for Writing Super-Readable VBA Code

here there and welcome to another excel vba video many people think that to be a good VBA programmer you have to write extremely complex locate code this is actually a myth a really good programmer they want to write code that's easy to read of course is readable then it's easier to understand it's easy to update but most of all it will significantly reduce the number of errors in your code and this is good because errors can waste a ton of your time so in this video I'm going to show you seven symbol put extremely effective ways for making your code readable so let's get started so the first thing we want to look at is declaring variables in VBA we don't actually have to declare variables and we can write code just like this now writing code without declaring variables it means we don't really know the type that we're dealing with and it can lead to other problems as we're about to see you can see here that we spell two total wrong in the second case we've put an all instead of an a the problem with this code is that it will actually run perfectly and by perfectly I mean without any errors being given but actually the value is wrong so it should be returning a total of a hundred but it returns a total of 50 instead now we use declare variables if we insists that are declared we must use option explicit so this phrase basically means that you have to declare your variables so we run a debug compile and this will tell us that we've got errors so we basically don't have total defined so we define total we declare it here and this basically tells us what exactly we're going to use it for which is a long integer now we use a debug compile again and des spots the second error because the second one hasn't been declared so if we don't insist on the current variables what it actually does is it VBA just creates a new variable and this time you see we got a hundred now if you want instead of type an option explicit every time we can basically just change one of the settings under tools options and that setting is require a variable declaration so if we check this every time we create a new module VBA will automatically add option explicit so let's just show you this insert a new module and you can see it's got option explicit at the start and now you know exactly why we use option explicit so this is something you'll often see in a VBA soap the top of the soap is basically used for a dumping ground for any variables that get declared now this is why it's kind of a safe thing to do you know the variables declared it actually makes the code very messy and it's not exactly clear where the very are being used so what I like to do is to clear the variables as I use them so let's look at a simple example of doing this so here I'm declaring range and I'm gonna use the range to get the current region so if anyone looks at this code and they see set range equals sheet data and so on they can just see the line before that I've just declared range so it's very obvious that range wasn't used anywhere else and it's very obvious that range has been used for the first time so this is the same weather far loop it just makes the code much more readable and much more obvious to what we're doing so we've declared I and now we've used a for loop and we've put I in the far loop so it's very very clear now if in the future I say okay I want to get rid of the code where I'm getting the current region I want to say use different code here then it's very easy to delete everything and I know that I'm not leaving the variable somewhere else I know that variable hasn't been used anywhere else because I've just declared it before that line so this is a very very simple practice but it's a very effective practice when it comes to making your code very readable so if you work as a professional programmer one thing you'll always do with your cord is indented because it makes it so much more readable so one indenting basically means is that you tab the code between different statements and for example here we're gonna highlight all the code which we use shift and tab this moves the code at tab to the left until it's all lined up and then we can easily highlight the code and just tab it all in one so we do this because we have solve an Enzo and when we get to other lines like the far loop we're going to tab all the code between far and next so we tab all the code like this and then we press the tab button and you can see that it tabs in the code now we do it again in the if statements and a statement basically that has an end like if end if select and select with and word and another thing you can see me down there is that I like to put in blank lines so this makes the code readable it's not on top of each other it's very clear here the flow of our code for loop if statements and then at the end we're using with so you can see if we compare this to the original code that we looked at you can see that it's by far more readable and it's very easy to debug the code and add a glance to see what the code is doing so you'll often see code like this when you're writing VBA so it has variable names that are not really that descriptive and of course tool debates all over the years about what is the right way what's a convention for a variable name what's the right way to name it and so on and actually the easiest thing to do it's just give it a descriptive name now you don't need the old days we used to do things like ell for long s per string you don't have to do that you can just use long descriptive names and then it's very clear what they're being used for obviously if something is customer name then it's going to be a string and if something is Torrent total amount it's gonna be some kind of number now there's no problem typing here because as I'm showing you on the screen here if you use control space it will help autocomplete the variables so you don't have to worry about the length of the names in case you're worried about it slowing down writing code so one small thing just to keep in mind is that we always use I and J when it comes to loops and that's just the convention and that's the way it's always done and the way it continues to be so you can just leave the far loops as I am J because that's just what people do so this technique I've shown very simple but so much debates over the years have come about how variables and everything should be declared so it's just important to get this right from the off so what we have in programming is the thing we call magic numbers and these are numbers that appear in our code that are not 0 or 1 when we say magic we mean it's like they have some magical value where we don't really know what it represents so what we want instead of her instead of having is no code we should have the numbers being read from a worksheet or if they're actually in the code they should be a constant or better still there should be an enum now you can see in this line that I've just inserted a snippet here you can see that 6i multiplied by 3 plus 4 very very difficult to understand what this code is doing and what about and if we decide to replace the four we're not quite sure if both of these fours are actually the same now whom use pay a special we have different options like paste all paste common space formats and all these are is basically text representations of a number and I'll show you what this means when we have a look at our in ohms so if we look at the in ohms up here so in ohm is short for a numerator now we've got data columns and for each one we've put in a number so basically same DC amount equals 1 DC check equals 2 and so on and the reason we do is that we can now put DC check DC results and all these in our code instead of the numbers so you can see here that it suddenly makes our code much more readable and what it actually does as well as that if we want to change say for example DC bonus say we want that column to know B column 6 we can just change it in one place and it changes everywhere but what's even more powerful is this if we set the values of the in ohms so they're all related to each other like this so we're seeing that check equals the column amount plus 1 we're saying that result equals the column check plus 1 and so on and the beauty of this is that if we decide to move data columns say we want to move our columns over to we can just change the first column and then all the code is updated to reflect this so this is a very simple but powerful method for making your code readable and also making it very very easy to update so this next one we're going to talk about is the most common one that I see in Excel VBA and it's really unique to this language it's where people refer to cells all the time in the code rather than using variables that a problem with this is it makes the code very difficult to read as you can see in this case because you've really where and also if we have them in variables like you can see here it makes the code just much more readable so let's have a look here we can put all the values into variables like a mount check result and bonus and then when we update the code you can see very clearly that the cold is much easier to read very easy to see exactly what's going on and that means we're going to have less errors in our code so I think this is of all of the ones that I'm showing you this is probably the biggest one for making your code readable and as I said it's very very unique to Excel VBA because we basically don't get ranges in cells in other programming languages so our last principle we're looking at is do not repeat so often you'll see people writing code like this so they have like your main code and then you want to send an email so they basically just put in all their email code afterwards and so they can have the email code all over their application and this is actually a bad idea what we should have is just email code in one place and then we call it from the other places so if you think about it in a real-world way it's like if we had a small town and we've got one post office we don't want to have post office all around our small town we just want to have one so when we send mail we go to one place and we basically send mail from there this makes it very very convenient in our code because as we'll see in a minute if we have it in many places we can run in to problems so the differences that we'll have in two different places so for example who were sending it to the subject and so on we basically make these the parameter of the soap and now we want to use we call send email and is based it sends it over to the email Department and then the email Department just looks after sending the code now why I said this was powerful was because imagine in the future we decide okay if Outlook doesn't exist what we want to do is we want to send it by Gmail so we can easily change the code in just one place here and we don't have to change it in loads of different places so we don't have to go and find everywhere that outlook was used we basically just changed code in one place and then we put in our gmail code like this now it tends to happen when people write code in all different places is that it tends to be a little different everywhere and then when we have to change it we run into lots of problems so that why it's much better to have just one place for one task so that's the seven principles for making our code readable now - you will find that they're quite simple to implement and that they don't take much time but the results that they have can be very very powerful indeed so I hope you enjoyed this video and I hope to see you on the next one 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 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: 65,973

Keywords: vba, excel, excel vba, microsoft excel, visual basic for applications, simple practice, vba code in excel, super readable, writing vba code, excel tricks, excel tips, excel macros, excel vba programming, vba excel

Id: QGFANQ4lPMA

Length: 13min 2sec (782 seconds)

Published: Fri Sep 13 2019