1 00:00:00,700 --> 00:00:05,680 So now that we have a good understanding of how to call subroutines and why we might call subroutines 2 00:00:05,740 --> 00:00:08,490 we can take a look at passing arguments. 3 00:00:08,650 --> 00:00:15,100 We've noticed that we've put an open and close parentheses after all of our subroutines these parentheses 4 00:00:15,100 --> 00:00:21,580 are used to in case any arguments that pass into that subroutine in this case an argument is usually 5 00:00:21,610 --> 00:00:27,250 a variable or a string or something that you want to use within that subroutine that might change as 6 00:00:27,250 --> 00:00:27,820 an example. 7 00:00:27,820 --> 00:00:33,460 Let's look at our move data to new location subroutine right here in this case. 8 00:00:33,490 --> 00:00:38,530 We need to know what the texturing is that we are trying to move and where we are trying to move it 9 00:00:38,590 --> 00:00:39,030 to. 10 00:00:39,430 --> 00:00:41,760 So we might have a few inputs here. 11 00:00:42,150 --> 00:00:47,760 So we'll go over to our parentheses and first is going to be our texturing. 12 00:00:47,800 --> 00:00:49,300 Second will be our row number. 13 00:00:49,330 --> 00:00:51,200 And third will be our column number. 14 00:00:51,190 --> 00:00:56,860 So in the parentheses we're going to create variables to store this information and we'll say copy text 15 00:00:57,310 --> 00:00:58,360 as string. 16 00:00:58,420 --> 00:01:03,990 And this will be the the text that we're actually copying will say RONAN As Integer. 17 00:01:04,330 --> 00:01:07,430 And so be the row number we want to move that data to. 18 00:01:07,720 --> 00:01:13,210 And we'll also create called as integer which will be the column that we want to move that information 19 00:01:13,210 --> 00:01:14,010 to. 20 00:01:14,020 --> 00:01:20,080 Now I can use these variables that we just created copy text Ronan and call them anywhere within the 21 00:01:20,080 --> 00:01:24,520 subroutine for moving data without having to explicitly declare them. 22 00:01:24,520 --> 00:01:35,110 So this might look something like cells Ronan call them dot value equals copy text and this should set 23 00:01:35,110 --> 00:01:43,200 my new location equal to my copied text value if we jump back up to our full code here we can go ahead 24 00:01:43,200 --> 00:01:48,780 and fix up this move data to new location that we're calling out to feed our new location code the correct 25 00:01:48,810 --> 00:01:57,510 data so to start we'll create a variable to pull the fruit that we are working with so up here we'll 26 00:01:57,510 --> 00:02:00,990 go ahead and then a string and we'll just call this fruit 27 00:02:03,840 --> 00:02:10,200 and then I'll go ahead and pull my first fruit value which is in row two column two so row two column 28 00:02:10,200 --> 00:02:15,990 two value and we'll set fruit equal to this. 29 00:02:16,070 --> 00:02:19,700 So now I know that I have my very first fruit value. 30 00:02:19,700 --> 00:02:26,120 Now when I call my private subroutine down here for moving the data to a new location you'll notice 31 00:02:26,120 --> 00:02:31,760 if we put the open and close parentheses at the end VBA is actually going to prompt us for some of the 32 00:02:31,760 --> 00:02:33,610 inputs as we can see here. 33 00:02:33,600 --> 00:02:37,160 And these are the same inputs that we just created in the actual subroutine 34 00:02:39,930 --> 00:02:40,570 in this case. 35 00:02:40,590 --> 00:02:43,890 There are some custom items we built into the routine that we're asking for. 36 00:02:43,890 --> 00:02:45,450 First the copy text. 37 00:02:45,450 --> 00:02:48,760 Second the new row number and third the new column number. 38 00:02:48,810 --> 00:02:56,660 So we will fill this out for the text being fruit and let's say we want to copy this into column D so 39 00:02:57,050 --> 00:03:02,980 we'll move down a few rows as well just as an example so we'll say row five and column for. 40 00:03:03,070 --> 00:03:09,480 So now when our code hits this particular line this call are moving subroutine line here. 41 00:03:09,670 --> 00:03:15,160 It's going to jump down to this actual private subroutine which we've written and we've told the subroutine 42 00:03:15,160 --> 00:03:16,010 ahead of time. 43 00:03:16,030 --> 00:03:17,980 These are the variables that we want to work with. 44 00:03:17,980 --> 00:03:21,070 This copy text row number and column number. 45 00:03:21,070 --> 00:03:26,260 So in this case our copy text will be whatever is stored in our variable fruit which we know we're pulling 46 00:03:26,260 --> 00:03:27,820 directly from our file. 47 00:03:28,000 --> 00:03:32,200 The row number will be five and the column number will be four. 48 00:03:32,200 --> 00:03:38,200 So when we step into the subroutine and we actually look at this line of code this really stands for 49 00:03:38,200 --> 00:03:47,420 cells row five column for dot value is going to be equal to whatever our fruit value is for simplicity's 50 00:03:47,420 --> 00:03:52,340 sake I'll just ignore these other subroutines since this is a pretty simple piece of code that we're 51 00:03:52,340 --> 00:03:53,510 working with. 52 00:03:53,510 --> 00:04:03,180 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 53 00:04:03,360 --> 00:04:05,310 make data uppercase subroutine anymore. 54 00:04:05,310 --> 00:04:10,050 It was just an example of how we might have broken this out and I'll go ahead and put the highlight 55 00:04:10,050 --> 00:04:15,180 in there as well so we can take out our highlight new location which means I can also take these out 56 00:04:15,180 --> 00:04:21,280 of my master code up at the top so now I've made it uppercase. 57 00:04:21,390 --> 00:04:24,300 The last thing I could do is go ahead and highlight this yellow 58 00:04:29,470 --> 00:04:32,630 so looking at the same cell that we just copy data into. 59 00:04:32,630 --> 00:04:36,770 We're gonna look at the interior color of the cell. 60 00:04:36,770 --> 00:04:43,820 We're going to set it equal to something like RG v 255 255 one hundred which should be some kind of 61 00:04:43,820 --> 00:04:45,200 yellow. 62 00:04:45,280 --> 00:04:48,580 We talked a little bit about that in a separate video. 63 00:04:48,580 --> 00:04:54,560 So now we can go back up to our subroutine our main master code our public sub up at the top. 64 00:04:54,610 --> 00:04:57,840 We could go ahead and pull our next fruit value. 65 00:04:57,850 --> 00:05:04,810 Now we'll replace our old fruit value with the new fruit value that's in row three column to and then 66 00:05:04,810 --> 00:05:12,720 again we will call our private sub to move the data to a new location and do all of the other work. 67 00:05:12,850 --> 00:05:18,280 Again we'll be looking at the fruit variable this time we'll copy it into row six and we'll still be 68 00:05:18,280 --> 00:05:21,970 in column four and then we'll pull our last fruit value 69 00:05:25,250 --> 00:05:32,800 and again call the subroutine row seven column for this time. 70 00:05:32,850 --> 00:05:36,980 So now we'll step through this so you can get an example of exactly how all this behaves. 71 00:05:37,140 --> 00:05:43,410 But as a general summary This subroutine if we hadn't actually written it as a subroutine we would have 72 00:05:43,410 --> 00:05:48,660 had to write each of these individual lines everywhere that you see this call subroutine call subroutine 73 00:05:48,660 --> 00:05:49,920 call subroutine. 74 00:05:49,920 --> 00:05:55,980 So we saved ourselves a little bit of coding even just by working out this very simple goal into having 75 00:05:55,980 --> 00:05:56,600 a subroutine. 76 00:05:56,610 --> 00:06:01,650 But obviously for more complex pieces of code having little subroutines that you can frequently call 77 00:06:01,650 --> 00:06:06,480 on to do repetitive tasks is extremely extremely beneficial. 78 00:06:06,490 --> 00:06:09,370 So now we can go ahead and step into this. 79 00:06:09,510 --> 00:06:16,170 I will step into it by hitting eight but you can also use the debug menu had failed again. 80 00:06:16,180 --> 00:06:21,580 We can see now I've pulled my first fruit value which is Apple and now I'm on the call line. 81 00:06:21,580 --> 00:06:28,510 So if I press at 8:00 we should see it dumped down to our move data to new location private sub which 82 00:06:28,510 --> 00:06:29,920 it does. 83 00:06:29,920 --> 00:06:36,640 And now we're looking at this specific cell that we pointed our little private sub to we can see it 84 00:06:36,640 --> 00:06:41,770 has successfully copied our information into this new cell it's successfully made it uppercase I'll 85 00:06:41,770 --> 00:06:45,970 have it again and we can see that cell has turned yellow. 86 00:06:45,970 --> 00:06:51,250 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 87 00:06:51,250 --> 00:06:55,570 to jump back up into our master code and keep stepping through our data. 88 00:06:55,570 --> 00:07:02,620 So now we're going to look at our second fruit which is a banana and I'll keep pressing f eight to walk 89 00:07:02,620 --> 00:07:03,130 through this. 90 00:07:03,130 --> 00:07:08,630 We're looking at our third fruit now and that should be everything and we can actually see I've made 91 00:07:08,630 --> 00:07:10,090 a mistake in my code. 92 00:07:10,190 --> 00:07:13,610 This third fruit came in as blank instead of as strawberry. 93 00:07:13,610 --> 00:07:21,320 And the issue lies right here with pointing to sell 3 3 which would be Column C row 3 instead of pointing 94 00:07:21,320 --> 00:07:23,540 to column B row 4. 95 00:07:23,570 --> 00:07:27,550 So we'll fix that by making this for two. 96 00:07:27,690 --> 00:07:32,910 And if I just hit play again law and clicked into my master code here we see it'll update correctly 97 00:07:32,910 --> 00:07:40,640 to strawberry so in this video we talked about passing arguments and how to properly call out subroutines. 98 00:07:40,640 --> 00:07:45,290 So now we should be able to better organize and structure our code in VBA.