In this video we're going to talk a little bit more about subroutines at this point.
We've already covered how to create a subroutine and the difference between private and public subs.
But just as a quick reminder if we create a private subroutine that means that our subroutine exists
only within the specific module it lives in.
So we had multiple modules in our code like I have here with first module and second module and we created
a private sub in this first module here.
We could only reference the private subroutine from within the first module itself if we tried to call
our private subroutine out in our second module.
The code would fail to find the routine public subroutines.
On the other hand can be called from anywhere in any of your modules or directly from the Excel macros
button.
Let's talk a little bit about organizing our code.
We typically try to break up our code into bite sized pieces that make sense.
We don't want to have super lengthy blocks of code that go on for pages and pages.
And one big reason for that is that having smaller pieces of code makes it much much easier to debug
and define the cause of any errors you might be getting.
What that means is that at the beginning of your project you typically want to lay out all of the steps
you think it will take for you to accomplish your goal.
Let's say we have a simple list here and column B simple list of our favorite fruit.
And we want to copy that data into column D change it all to uppercase and highlight it yellow.
Now for something this simple you'd probably be able to do all of this in one subroutine but the steps
for that subroutine would be first identify the data that we want to copy second.
Change the data to be uppercase third move that data to the new location and last highlight the new
location so if we were to break this into different subroutines it might look something like this.
We'll start by opening up one of my modules here.
Now I would start with the public subroutine and this will be the main subroutine that we're going to
work out of so I'll call this the master code.
This is the master code so all of the smaller subroutines are going to roll up into this main subroutine.
So we only have to call it out once but really we'll be able to work with all of the different subroutines
that we've built from this single set of code here.
So we might have a few different private smaller subroutines so we'll see private sub and we'll call
this one make data uppercase so maybe in this sub this is where we would actually go in and take the
data that we're looking at in column B and turn it into uppercase theta.
We might have another private sub we'll call this one move data to new location and just like the name
sounds we might individually move each piece of data to the new location in that specific subroutine.
And lastly we might have another private sub that says highlight new location.
So all of these private subroutines that we just created have a specific purpose for us.
They all correspond with one of these steps that we've already laid out over here of what we need to
do in order to accomplish our goal.
So we can go ahead and in our main routine up at the top.
We'll give ourselves some space and we'll type call and then the name of each subroutine like so
and lastly highlight new location.
So now when we run our full code or our master code this public sub here and we hit these call lines
VBA is actually going to jump down to the corresponding subroutine and run that piece of code before
returning to our main routine up here breaking your code into smaller pieces is great for debugging
but it also helps for when we have very repetitive tasks.
So we have a whole lot of different information that we want to make uppercase and we want to highlight
like we do in this in this column B over here instead of typing that piece of code over and over and
over again in our main subroutine we can break that out into its own subroutine and simply call that
subroutine instead of manually typing out all of that code.
And this means we need to be able to pass some variables between our subroutines so that they can communicate
with each other and that's what we're gonna be looking at doing next.