Learn Excel VBA to Automate Anything

in this video we'll learn the basics of VBA which  stands for Visual Basic for applications and it's a programming language built into Microsoft  Office apps like Excel PowerPoint and Outlook VBA is most useful to automate tasks and we'll  go over three different scenarios of this first creating a custom Excel formula to calculate  discount amounts secondly creating a message box to clear all the values in a data set and finally  learning how to send an email from Excel in just one click so let's get into it over here you can  see the Excel file we're working with which you can download for free in the video description  so you can see that we have all of these orders for different color products and the quantity the  price and the total amounts from here to activate VBA you're gonna have to go to the developer tab  if in case you don't have this tab just go on any of these Tabs go to right click here and then go  to customize the ribbon from here you're going to want to find the developer it should be down  towards the bottom in case that's unticked make sure you tick it and just hit on OK awesome  now you should have it over here and to edit the Visual Basic we need to go to the Visual  Basic editor so this figure right over here within this to the left hand side you're going to  have all of the different projects so these are all of the Excel files that you might have open  and the respective sheets and then right in the center is where you would add all of your code but  first we need to create a new module so we'll just go to right click here on sheet1 say insert module  the reason we want to insert the module like this is so if sheet1 gets deleted we're not going to  lose all of the code that we have first up here we want to create a custom function so much like  Excel has say the equals sum function you can also customize a function to fit your specific needs so  in our case if we want to find the discount let's suppose that we only want discounts if whoever is  purchasing or there is more than 25 cars then we want a discount amount of 20 percent you can see  here I have this as a split screen so we can see what's going on both on the Excel file and on  the Visual Basic editor so let's go ahead and type function because that's what we want here  and let's call this one something like discount then we need to put it in parenthesis here and  within it we want to have all of our inputs so in our case we have two inputs for it we need to  know the price and we need to know the quantity so we're gonna go quantity comma price and those  are our two inputs just hit enter there and you'll notice that it says end function that's fine that  should come by default within it we want to make a condition where you only apply a discount if  it's greater than 25. so we'll do if the quantity is greater than 25 then hit enter here we want  that discount to be equals to the quantity multiplied by the price multiplied by 0. 2 that  0.

2 is at 20 percent now in the scenario that it doesn't fit the criteria meaning if the  quantity is less than 25 we need to put an else then we want a discount to be equals to zero  as we shouldn't have a discount in there then we need to close out this if statement so we'll  do end if and then everything else is okay in there once we help you with the function we just  need to try and test it over here under the Excel file so we'll go to equals then we just need to  type the name of the function which is discount hit the top key there and so we need two  requirements the quantity and the price in case you don't remember what these requirements  are you can just hit Ctrl shift a and you can see that they're both gonna start showing  so first is a quantity comma and then that second one instead of the price we should have  this figure which is the price for us just hit enter there now in this case it's giving us zero  and that's because the quantity is less than 25 but if we drag this down by double clicking  you can see whenever it's above that 25 it should be applying that 20 discount that's the  idea with custom functions in VBA as you can imagine you can really personalize this to fit  just about anything now suppose we get this data every week and so once we send out all of the  invoices we need to clear all of this data now we would like to automate that process for this  let's open up VBA Again by hitting the alt f11 and within this we want to create a sub which is  short for a sub procedure and it's basically a set of actions that you want VBA to take so we'll  do a sub space and we'll call this one say clear content again open parenthesis and this time  we can just close them and hit enter you can see it should create a dividing line between the  function and the sub below so here we basically want to delete everything but just the data right  we don't really want to delete the header or this row as well with the header rows so we really want  to start a row six to do this we can just type rows and then in parenthesis here and quotations  we're gonna start at row six and then we want that to go down all the way to the end so we're gonna  put this sign over here close out the quotations and then an ampersand and now we need to count  how many rows down this goes so we'll do rows dot count hit the top key there close the parenthesis  and then after this dot we need to tell it what we want it to do so we wanted to clear all of the  contents so that's what we'll say clear contents make sure it has an s in the end there now to run  it we can't simply go ahead and go to equals and then clear contents as that's simply not gonna  show up and the reason for that is because it's no longer a function instead we have it as a sub  procedure as you can tell by the sub here so to run it we just need to go click inside of that  area and then just go to run sub so just hit on play there and you'll see how it deletes all of  the content but it Remains the Same up top as we would like it to now there is one problem here  and it's that if you do this as a mistake you can't go back anymore so that's quite problematic  that's why it might make sense to have a message box to confirm that we do indeed want to clear  the content so for this we'll go back to the Visual Basic editor and just at the top over here  we want to set the answer equals to a message box and then here under the prompt in our case we  wanted to ask are you sure you want to clear this content so you want to put in quotations  here and let's say something like confirm you want to clear question mark close the  quotations hit the comma there and now we need to have some kind of an input box that  says yes or no so we're gonna do VB yes no and you should find that one hit the top key and  close the parenthesis there now to run this let me just randomly put some data here I'm just going  to put a few numbers and now if I hit on play you'll see that we get this pop-up that says  confirm you want to clear now suppose I say no you'll notice that it still clears the data that's  because we don't really have a condition here that tells it what to do if yes or what to do if  no so we need to add some part that says that something like if answer equals VB yes then we  still wanted to do this so we want it to clear else so if it says no Then we just want to exit  the sub meaning we don't want it to do anything then we need to close out this  if statement so we go to end if and finally we can close out the sub so now if I  just add a value over here then I go hit on play confirm you want to clear let's say I  say no you'll notice that it's going to remain there however if I hit on play again  and I say confirm you want to clear it yes then it's gonna clear that data one final touch  here could be to add a button so let's suppose I go to the second sheet which is actually just  a duplicate and from here I'm gonna go over to developer under insert we want to insert  a button so what this button is going to do is it's going to allow us to clear all the data  just by clicking on it once so we'll assign it to this clear content macro name hit on OK there  and now let's rename this to something like clear so let's try that out just click on clear once you  will confirm you want to clear let's say we go for yes and you'll see that it clears everything if  you're liking this VBA content and you want to learn more you can consider taking our Excel  VBA and macros for business automation course from automating data analysis tasks to financial  reporting and spreadsheet formatting learning VBA will be a game changer for your productivity  in the course you learn fundamentals such as object properties methods and variables once  you get a feel for the basics we'll introduce more Dynamic features such as conditional  statements looping functions and data arrays with this knowledge you'll be able to perform  a whole range of common spreadsheet tasks such as Auto generating pivot tables formatting  charts building interactive info boxes and more finally you'll have two extensive case studies  to apply the concepts you've learned the first one will focus on automating a billing summary  report for pwc's Consulting team and the second case study will focus on building an automated  profit and loss statement for Mercedes-Benz so if you're interested in taking the course we're  currently offering a 20 discount just for the next seven days so make sure you check that out in  the description below finally let's say we're happy with this Excel file and we want to send  it to the rest of our team we can also automate that in VBA so it sends a subject a body and the  attached Excel file in just one click this part is slightly more advanced so not everything might  make sense but it's still useful for you to be able to see what it's capable of so we'll create  a new sub down over here let's call this one sub send email open parenthesis and  close and just hit enter there and I'm going to fast forward how I write the code  here and then we're going to look at it together so this is the sub procedure we're gonna need  and I know it looks a bit daunting so let's go over some of the steps and these first two lines  up over here the dim means the dimension and here we're declaring the variable meaning we're telling  VBA what kind of a data type this is so as object it could be as an integer meaning it's just a  number it could be as a date but in this case we're going for object then right below that  you can see that we're setting these to these two names so the L top is equals to basically  we want it to be an Outlook as the application and then we want it to be an email within our  look then we have the without mail area which is basically what's going to be inside of the  email and for the two here we want it to equals to whoever we want to send it to so in quotations  that would be let's say to hello gmail. com closer quotations there for a subject again  equals to Let's suppose we call this Excel file and close those quotations then for the body  this is the actual text inside of the email in quotations again we can put something  like this is a test closer quotations and for the attachments we want to add an  attachment dot add then what we want to add is a current Excel file we're working on so  we can just type something like this workbook dot full name finally this display part is  to display the email before it sends you can switch it switch it to just send in case you  wanted to send it directly and finally these two nothing parts are basically to clear the memory  so it's just a best practice thing awesome now that we're ready we can just hit on play there  and you'll notice that it creates a brand new email sending to hello gmail.