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