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.com subject is the  Excel file we have the attachment and the body   says this is a test awesome those are just some  of the tasks that Excel VBA can automate for you   this video was very much simplified so there are  some best practices that we've skipped here but   it hopefully gives you a better idea for more on  automation check out this macros video over here   or take our VBA course over here hit the like and  that subscribe and I'll catch you in the next one

Channel: Kenji Explains

Views: 280,895

Keywords: VBA, Excel vba, visual basic for applications, learn vba, learn excel vba, master excel vba, excel vba for beginners, vba essentials, vba fundamentals, vba course, visual basic for applications course, vba tutorial, vba excel outlook, send an email from excel using vba, automate excel with vba, custom functions vba, sub procedures vba, learn vba as a beginner, microsoft office vba, visual basic for applications excel, vba in 15 minutes, vba in 10 minutes, sub, sub procedure

Id: YWJ1OIfQakQ

Length: 14min 1sec (841 seconds)

Published: Sun May 21 2023