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.
