1
00:00:00,890 --> 00:00:05,570
In this video we're going to talk a little bit more about subroutines at this point.

2
00:00:05,570 --> 00:00:10,300
We've already covered how to create a subroutine and the difference between private and public subs.

3
00:00:10,340 --> 00:00:16,040
But just as a quick reminder if we create a private subroutine that means that our subroutine exists

4
00:00:16,280 --> 00:00:19,300
only within the specific module it lives in.

5
00:00:19,430 --> 00:00:25,370
So we had multiple modules in our code like I have here with first module and second module and we created

6
00:00:25,370 --> 00:00:28,050
a private sub in this first module here.

7
00:00:28,190 --> 00:00:34,700
We could only reference the private subroutine from within the first module itself if we tried to call

8
00:00:34,700 --> 00:00:37,520
our private subroutine out in our second module.

9
00:00:37,520 --> 00:00:41,480
The code would fail to find the routine public subroutines.

10
00:00:41,480 --> 00:00:47,450
On the other hand can be called from anywhere in any of your modules or directly from the Excel macros

11
00:00:47,450 --> 00:00:48,650
button.

12
00:00:48,680 --> 00:00:51,740
Let's talk a little bit about organizing our code.

13
00:00:51,740 --> 00:00:56,290
We typically try to break up our code into bite sized pieces that make sense.

14
00:00:56,360 --> 00:01:01,160
We don't want to have super lengthy blocks of code that go on for pages and pages.

15
00:01:01,160 --> 00:01:06,980
And one big reason for that is that having smaller pieces of code makes it much much easier to debug

16
00:01:07,310 --> 00:01:10,580
and define the cause of any errors you might be getting.

17
00:01:10,580 --> 00:01:15,620
What that means is that at the beginning of your project you typically want to lay out all of the steps

18
00:01:15,620 --> 00:01:18,680
you think it will take for you to accomplish your goal.

19
00:01:18,680 --> 00:01:23,990
Let's say we have a simple list here and column B simple list of our favorite fruit.

20
00:01:23,990 --> 00:01:30,230
And we want to copy that data into column D change it all to uppercase and highlight it yellow.

21
00:01:30,230 --> 00:01:35,180
Now for something this simple you'd probably be able to do all of this in one subroutine but the steps

22
00:01:35,180 --> 00:01:41,520
for that subroutine would be first identify the data that we want to copy second.

23
00:01:41,620 --> 00:01:51,660
Change the data to be uppercase third move that data to the new location and last highlight the new

24
00:01:51,660 --> 00:01:59,570
location so if we were to break this into different subroutines it might look something like this.

25
00:01:59,600 --> 00:02:02,200
We'll start by opening up one of my modules here.

26
00:02:02,290 --> 00:02:07,120
Now I would start with the public subroutine and this will be the main subroutine that we're going to

27
00:02:07,120 --> 00:02:10,620
work out of so I'll call this the master code.

28
00:02:10,640 --> 00:02:17,020
This is the master code so all of the smaller subroutines are going to roll up into this main subroutine.

29
00:02:17,060 --> 00:02:21,620
So we only have to call it out once but really we'll be able to work with all of the different subroutines

30
00:02:21,620 --> 00:02:25,010
that we've built from this single set of code here.

31
00:02:25,010 --> 00:02:31,160
So we might have a few different private smaller subroutines so we'll see private sub and we'll call

32
00:02:31,160 --> 00:02:38,150
this one make data uppercase so maybe in this sub this is where we would actually go in and take the

33
00:02:38,150 --> 00:02:42,080
data that we're looking at in column B and turn it into uppercase theta.

34
00:02:42,140 --> 00:02:51,560
We might have another private sub we'll call this one move data to new location and just like the name

35
00:02:51,560 --> 00:02:56,930
sounds we might individually move each piece of data to the new location in that specific subroutine.

36
00:02:57,530 --> 00:03:04,140
And lastly we might have another private sub that says highlight new location.

37
00:03:04,460 --> 00:03:10,190
So all of these private subroutines that we just created have a specific purpose for us.

38
00:03:10,220 --> 00:03:15,170
They all correspond with one of these steps that we've already laid out over here of what we need to

39
00:03:15,170 --> 00:03:17,400
do in order to accomplish our goal.

40
00:03:17,780 --> 00:03:20,370
So we can go ahead and in our main routine up at the top.

41
00:03:20,510 --> 00:03:27,860
We'll give ourselves some space and we'll type call and then the name of each subroutine like so

42
00:03:31,920 --> 00:03:37,040
and lastly highlight new location.

43
00:03:37,260 --> 00:03:43,080
So now when we run our full code or our master code this public sub here and we hit these call lines

44
00:03:43,470 --> 00:03:48,930
VBA is actually going to jump down to the corresponding subroutine and run that piece of code before

45
00:03:48,930 --> 00:03:55,950
returning to our main routine up here breaking your code into smaller pieces is great for debugging

46
00:03:56,250 --> 00:03:59,720
but it also helps for when we have very repetitive tasks.

47
00:03:59,850 --> 00:04:03,600
So we have a whole lot of different information that we want to make uppercase and we want to highlight

48
00:04:03,600 --> 00:04:09,090
like we do in this in this column B over here instead of typing that piece of code over and over and

49
00:04:09,120 --> 00:04:14,940
over again in our main subroutine we can break that out into its own subroutine and simply call that

50
00:04:14,940 --> 00:04:18,440
subroutine instead of manually typing out all of that code.

51
00:04:18,630 --> 00:04:23,880
And this means we need to be able to pass some variables between our subroutines so that they can communicate

52
00:04:23,910 --> 00:04:27,320
with each other and that's what we're gonna be looking at doing next.