4.5 Using Complex Macros with Real-Life Problems Part 2
8 hours 33 minutes
so in a continuance off.
with the examples,
we will go
to another example
the second part of this lesson.
And here we have the famous $250 cookie recipe. I don't believe nobody has heard the Reed. So there is a popular
ah story. It's one of the first spoofs on chain letters on the Internet.
It was actually there before the Internet. While
people are still exchanging,
uh, males, why are different kinds of means by bulletin boards?
And basically, somebody invented the story that people went toe. Um ah, department store went to get eat something. They found some very nice cookies, asked for a recipe,
and they told him that because 2 50 So they
paid with the card and they got the bill. Later, when they looked at it, they realized the cost of recipes $250 instead of $2. 50 cents.
Anyway, this is very nice
cookie recipe. I have tried it. So it makes really great cookies, and it's publicly available on the Internet, and I have used it as a example in this,
listen, so we want to convert.
metric, the imperial units, the metric if needed.
we want to do it so that when we
converted to metric, we can convert it back to Imperial if needed. So let's say that we have, ah, standardized way off writing
recipes in Excel, and we keep them in one file,
and we just want
the way to do it.
the way the recipe structured these, we have the recipe name.
Then we have a line with ingredients. Amount unit
had had pet inc Then we have,
uh, these ingredients,
and when they finish, we have a single cell with instructions, and then we have instructions.
Ah, separated in easy, uh,
perform herbal actions,
uh, be the serial number.
So we want D's
to be converted to these. And we have a comparison chart, and these are the units were goingto change with our macro. So we have ounces. It goes to grams, we have cups, it goes two liters,
we have pounds, it goes to kilograms, and we have pints. If they're there, they go to leaders. So the idea is that we have,
uh, in both ways were a simple,
uh, change. So if something is given in grams.
It should be translated tow ounces,
but it's something is given kilograms, then it's probably a lot of its show, so we should convert it to pounce.
Also with cups they go Two liters and pints day also goto literature. This It's a slightly problematic think and now we have a conversion rate, which is from this to this. So
if we have
five ounces, then we multiply with 28.3 and we get grams. But if we get grams, we just
divide the amount. For example, we have 200 grams of chocolate. We divided 28.3 and we get ounces,
which is a little bit over six ounces. So
this is a comparison chart. We don't need to hide it. It's just there, and we want to do this calculation
now. First thing we're going to do is we're going toe insert the shape which is going to be a
round the direct and do,
and we're just going toe put here
we're going to change the text size
so it fits
You're going to put it in the middle.
Uh, we can change the
and we go here
and then we go home and every click here is Well, so we sent through it on the
work took Alexis is well,
and we just place it over here,
and when we finish everything, we're going to assign macro to it.
first of all,
we have to decide
what to do when we click this. We have to check if these units are metric or imperial.
And I have already
made that macro
and we're going to go through it. It's called Decide
and let's edit it.
And at this point, this macro is just
displaying message boxes, imperial or metric, And what is doing? So we have ah
for going through the
And, uh, we have Ah,
variable court, we call it in. I called it Imperial
and its default setting to false, and we're checking if this is
imperial or not. So we're going to have a do loop until
and we see So we set eye to three. Why?
Because the first
line off ingredients always start on in the third row
and then we check if range, see and I So first pass it's C three equals
ounce then Imperial is true. If not, we check for other imperial measurements. So
and again it's true.
If it's not, we check if
there is found there.
Well, I see three
just the check. So see, trees this
then if it's not bound, we check for point. Because we have these four units there.
And then again it's true. If not,
we close and structure. We have four ends.
I equals eye plus one. So remove one row lower
and we go loop until range or off A and I equals instruction. So we started at three
and the end of this check. If nothing happened, where it happened,
we increase this too
And then we look, if their instructions Why? Because we can have between I don't know, two and 22 or 25 ingredients in this list. So we have to do the do look because we don't know how many lines we have arose. We have before we reach to instructions
Why we do that for every line because we can have
things like eggs
and, uh, I don't know.
oranges or lemons,
which don't have units because it's just the two or three or five of them.
So the recipe might not contain
Probably there is going to be something like,
uh, some measurements, but it may be all in
teaspoons and tablespoons. So then we don't need any conversion.
Uh, but this is it. So
look at here
and we display
message books, Imperial or metric.
And if we start this macro
we go to decide,
we get information that this is obviously imperial metric.
So I have already created that macro man, Let's stop here and look at it in the next video, which will continue this lesson.