I'm going to show you how to say to create user-defined functions and how to
save them so that you can use them on every sheet so I have a couple of very
simple things here I've got the part number I'm part number the price and the
cost and so I've created a user-defined function and I go equals and I called my
function profit and so then I can click on that and it'll put it in there and
then just to show what what it's going to do what I it has here is that you
wants the sale price and then it wants the cost and there you go that it
figures out the profit so what is basically doing is it's multiplied is it
subtracting the cost multiplied by an overhead and taking it from the sale
price and so that's our profit now another example of a user-defined
function is so the sale date was the first of November so let's see when
those bills do so let's go equals due date and there you go again and the only
thing I need to put in there is the date that was sold on then I press ENTER
there you have the due date for this for this bill now let's let's go look at the
code writing functions are pretty simple you name it public function and then you
name whatever you're going to call it and you pull it up it's going to be
profit and then you define what the variables are so the sale price is an
integer cost as an integer and on the result of this is going to be an integer
that's important as well so profit equals the sale price minus the cost
times one point zero seven and that's how we calculate the profit now the
other function that I did was due date so public function due date purchase
purchase as I didn't say purchase date I said purchase as date and the result is
going to be a date so due date
clothes purchase plus 30 so we're just adding 30 days onto it so that looks
fine but then you know you go and you have an image sheet you need to work on
so let's go control and open up another worksheet and let's go let's go this is
the cost 88 for the sale prices that and this is for 4 and then you go equals and
then profit and it's going what it had it hasn't been it hasn't been saved
so now let's let's save it so that it can be used every place so let's delete
this now if we take what we've got here in our so what I'm going to do is I'm
going to take this Code here that's in the module in the workbook that I'm in and
I'm going to copy it and then I'm going to go to my personal and then I'm going
to open up a new module within my personal workbook and then I pasted it
in here so that looks like it should work so now let's go back to our second
sheet and it still doesn't it still doesn't work out you're not you're not
going to go profit and it's not going to bring anything up and you get still get
nothing so to run this from our personal book we're gonna have to type in equals
and we're going to have to type in personal dot XLS B and then we're going
to put an exclamation mark and then we're going to write profit and then
we're going to put an opening bracket and then we're going to put the sale
price and then when we put a comma and then we're going to print the cost and
then we're going to close it then we're gonna press ENTER so there it's
calculated for you but that's not very very efficient or anything else to
access this macro so let's show you how you can set it up so that works
automatically so what I've done now is I've gone back to my Visual Basic screen
here clicked on my VBA project my personal
workbook and then on the bottom on the properties box it allows me to rename it
and I'm going to rename it such as something so that it sticks out a little
bit so I am going to name it BH personal
so now I have the BH personal so now you click on one of the other books not the
one that you've just created the BH is up here so I'm just going to click on
project will be be a project book one and I'm going to tools and references
and then we have BH personal here and so I'm going to say ok and now you can see
that it has added the references so now you go back to your sheet and you've
let's put in 800 and let's put in 550 and then if you go equals and profit
it's not going to come up automatically anymore but we know that it's it's the
sale price and comma and then it's the cost here and then we close the bracket
in it and calculates it for you now when one more time if I go control
new it's not going to work unless I go back up to the Developer tab and go to
visual basic and now I'm on the next book here and so then I just have to go
up to tools and I go references and I once once more click the bhc personal
and say ok and then your if you have a long list of functions that you can use
these functions in every sheet so every sheet that you want to use the functions
on you must go and and put the reference to the personal workbook on it so that's
how you create automatic functions that you can use on every sheet please
subscribe