So now that we have a good understanding of how to call subroutines and why we might call subroutines
we can take a look at passing arguments.
We've noticed that we've put an open and close parentheses after all of our subroutines these parentheses
are used to in case any arguments that pass into that subroutine in this case an argument is usually
a variable or a string or something that you want to use within that subroutine that might change as
an example.
Let's look at our move data to new location subroutine right here in this case.
We need to know what the texturing is that we are trying to move and where we are trying to move it
to.
So we might have a few inputs here.
So we'll go over to our parentheses and first is going to be our texturing.
Second will be our row number.
And third will be our column number.
So in the parentheses we're going to create variables to store this information and we'll say copy text
as string.
And this will be the the text that we're actually copying will say RONAN As Integer.
And so be the row number we want to move that data to.
And we'll also create called as integer which will be the column that we want to move that information
to.
Now I can use these variables that we just created copy text Ronan and call them anywhere within the
subroutine for moving data without having to explicitly declare them.
So this might look something like cells Ronan call them dot value equals copy text and this should set
my new location equal to my copied text value if we jump back up to our full code here we can go ahead
and fix up this move data to new location that we're calling out to feed our new location code the correct
data so to start we'll create a variable to pull the fruit that we are working with so up here we'll
go ahead and then a string and we'll just call this fruit
and then I'll go ahead and pull my first fruit value which is in row two column two so row two column
two value and we'll set fruit equal to this.
So now I know that I have my very first fruit value.
Now when I call my private subroutine down here for moving the data to a new location you'll notice
if we put the open and close parentheses at the end VBA is actually going to prompt us for some of the
inputs as we can see here.
And these are the same inputs that we just created in the actual subroutine
in this case.
There are some custom items we built into the routine that we're asking for.
First the copy text.
Second the new row number and third the new column number.
So we will fill this out for the text being fruit and let's say we want to copy this into column D so
we'll move down a few rows as well just as an example so we'll say row five and column for.
So now when our code hits this particular line this call are moving subroutine line here.
It's going to jump down to this actual private subroutine which we've written and we've told the subroutine
ahead of time.
These are the variables that we want to work with.
This copy text row number and column number.
So in this case our copy text will be whatever is stored in our variable fruit which we know we're pulling
directly from our file.
The row number will be five and the column number will be four.
So when we step into the subroutine and we actually look at this line of code this really stands for
cells row five column for dot value is going to be equal to whatever our fruit value is for simplicity's
sake I'll just ignore these other subroutines since this is a pretty simple piece of code that we're
working with.
And I will go ahead and make this the UK s for upper case of our copy text so we really don't need this
make data uppercase subroutine anymore.
It was just an example of how we might have broken this out and I'll go ahead and put the highlight
in there as well so we can take out our highlight new location which means I can also take these out
of my master code up at the top so now I've made it uppercase.
The last thing I could do is go ahead and highlight this yellow
so looking at the same cell that we just copy data into.
We're gonna look at the interior color of the cell.
We're going to set it equal to something like RG v 255 255 one hundred which should be some kind of
yellow.
We talked a little bit about that in a separate video.
So now we can go back up to our subroutine our main master code our public sub up at the top.
We could go ahead and pull our next fruit value.
Now we'll replace our old fruit value with the new fruit value that's in row three column to and then
again we will call our private sub to move the data to a new location and do all of the other work.
Again we'll be looking at the fruit variable this time we'll copy it into row six and we'll still be
in column four and then we'll pull our last fruit value
and again call the subroutine row seven column for this time.
So now we'll step through this so you can get an example of exactly how all this behaves.
But as a general summary This subroutine if we hadn't actually written it as a subroutine we would have
had to write each of these individual lines everywhere that you see this call subroutine call subroutine
call subroutine.
So we saved ourselves a little bit of coding even just by working out this very simple goal into having
a subroutine.
But obviously for more complex pieces of code having little subroutines that you can frequently call
on to do repetitive tasks is extremely extremely beneficial.
So now we can go ahead and step into this.
I will step into it by hitting eight but you can also use the debug menu had failed again.
We can see now I've pulled my first fruit value which is Apple and now I'm on the call line.
So if I press at 8:00 we should see it dumped down to our move data to new location private sub which
it does.
And now we're looking at this specific cell that we pointed our little private sub to we can see it
has successfully copied our information into this new cell it's successfully made it uppercase I'll
have it again and we can see that cell has turned yellow.
Now we're on this end of and when we end sub here it's not actually going to exit our code it's going
to jump back up into our master code and keep stepping through our data.
So now we're going to look at our second fruit which is a banana and I'll keep pressing f eight to walk
through this.
We're looking at our third fruit now and that should be everything and we can actually see I've made
a mistake in my code.
This third fruit came in as blank instead of as strawberry.
And the issue lies right here with pointing to sell 3 3 which would be Column C row 3 instead of pointing
to column B row 4.
So we'll fix that by making this for two.
And if I just hit play again law and clicked into my master code here we see it'll update correctly
to strawberry so in this video we talked about passing arguments and how to properly call out subroutines.
So now we should be able to better organize and structure our code in VBA.