In this video we're gonna be talking about building custom functions in Excel and functions are similar
to the public subroutines and private subroutines that we've been writing in all of our other videos.
The primary difference is that a function will actually return value as the output of the function.
And this means we can call our functions in our actual cells on Excel rather than building a subroutine
that has to be triggered by a button or something like that.
So for example if we wanted to create a column over here that says hourly rate and then maybe we want
to have another column that says hours worked
and a third column that says total pay
and we'll go ahead and we'll just pick some numbers for these and we'll say this person worked only
eight hours.
This person worked 10 hours so they should probably get a little bit of overtime pay.
And this person worked 12 hours so they should definitely be getting overtime pay.
So it would be a little hard for us to right now just use regular functions here and function again
is something like If I typed some here.
This sum is one of excels internal functions that they already have written so we can build our own
function that will serve a purpose like some does but we can call it out with all of the custom code
in it to execute whatever we want it to execute.
So in this case it would be hard for us to determine what this person's total pay is compared to this
person because this person has worked two hours of overtime and maybe they get a little bit extra pay
for working that two hours of overtime.
So this wouldn't be as easy as just saying you know we're gonna take their hourly rate times their hours
worked because if we do this this person isn't getting any extra pay for their extra time that they've
worked.
So instead we're gonna build a custom function that will do this for us.
So we're gonna go over to the developer tab and open up our visual basic window.
Now if you don't have a module in your in your code already you can go to insert module or if you do
have a module you can double click on it or right click you code to open it up and you'll notice I'm
working in an excel file that we built previously when we were building forms and learning about forms.
You can just keep these three columns and create your own excel file if you don't want to have to work
from a pre-existing file on this will still work the same way.
So what we're going to type is function and notice we're using function instead of any sort of subroutine
because again we're interested in creating a custom function not a subroutine.
Then we'll type the name of the function.
So I'm just gonna call this full pay.
And now we're gonna create the inputs for this function.
So first I'm going to create a variable we'll say t as double and this will be how much they get paid
per hour.
And then I'm going to create another variable each as double which will be the number of hours that
they worked.
And now the key to remember about functions is this full pay is actually going to be a variable.
This is going to be the output of your function.
And so we need to tell the system what is for pay.
And in this case we're going to say for pay is also double.
And this just means no we're going to be doing math with this.
So we want to make sure we carry our decimals and all of that.
And that's why I'm using double and you can go back and check previous video where we talked about variable
types if you're not familiar with double versus string or anything like that.
Now the first thing we want to check we want to check if the number of hours that they worked which
will be h is greater than 8 and I'm going to go ahead and put my and if so I don't forget it later so
if they did work more than eight hours then we know they should get overtime pay so the first thing
we're gonna do is we're gonna figure out what their pay should have been for the regular eight hours
that they worked their first eight hours.
So we'll say full pay initially is just gonna be eight times the the hourly rate that they make which
is our variable.
So now I know I've accounted for their first eight hours of pay and I only want them to get overtime
pay for the extra hours that they worked.
So now I'm going to just that full pay value and I'm going to say OK I want you to keep that value that
we just calculated how much they would have paid for their regular eight hours.
And this time I'm going to add in their total number of hours that they worked.
Subtract out the initial eight hours.
So now I know I'm looking at only the extra hours that they worked that day.
I'm going to times that by their hourly pay.
And this time I'm going to give them a little bump and we'll use one point five.
Just as an example.
So they should be giving extra pay half of their hourly rate extra for each hour that they worked overtime.
Close out my parentheses and I'll hit enter.
Now I am going to use an LS here and that's because we also want to account for if they just worked
a regular eight hours so we'll see regular pay.
And this time our full pay would be just the regular pay rate times the number of hours that they worked
so all in all this function should look at how many hours they worked initially if they worked more
than eight hours we're gonna give them overtime pay.
So first we're gonna figure out what they should be paid for the first eight hours they worked then
we're gonna figure out what they should be paid for the extra hours that they worked and add that to
the original eight hours worth of pay.
If they didn't work more than eight hours so if they worked eight hours or less then we're just going
to take their pay rate and multiply it by the number of hours that they worked.
So now I'm going to go ahead and save this and go back to my cell file now in the total pay column here.
I'm going to type equals.
And then the name of our custom function which was full pay and we'll see it actually does pop up in
this function list so we can see Excel does recognize that we have created this custom function.
Now I'm going to go ahead and select the hourly rate and then the hours that they worked in that order
because that's the order that I built into that custom function and hit enter and we can see this person
only worked eight hours so they're not getting any overtime pay.
It's just ten times eight which is 80 but this person did work extra hours they worked two extra hours
so we will look at their hourly rate we can notice they're actually making a little bit more than just
10 times 15.
They're making extra pay to account for those extra hours that they worked and the same with this person.
And if I come back through and I change this to say 10 we can see their pay will adjust automatically.
So we've built this custom function now to go ahead and account for whether or not our employees worked
a regular eight hour day if they worked overtime and automatically calculate that for us.
And we can build these custom functions and take advantage of them to avoid having to write multiple
if statements or nested if statements into our formula box up here and avoid creating huge nasty formulas
that exist in the formula bar that are hard to keep track of.
Instead we can just create those custom functions in VBA and automatically run all those calculations
in the background.
So building custom functions is a super helpful tool.
You should definitely take advantage of custom functions to make sure your formulas aren't huge and
overwhelming and you can build in some error proofing into your custom functions as well.
So that's what we learned about in this video was building custom functions and how helpful they can
be.