1 00:00:00,960 --> 00:00:06,080 In this video we'll be looking at the worksheet object and how we can use it in our code. 2 00:00:06,080 --> 00:00:10,640 Like with workbooks there are a few different ways we can reference worksheets in Excel and versus by 3 00:00:10,640 --> 00:00:15,680 referencing the number of the worksheet which is helpful for looping through all of the sheets or if 4 00:00:15,680 --> 00:00:20,540 we don't know the sheet names but generally speaking it's best practice to reference the worksheet directly 5 00:00:20,540 --> 00:00:23,690 using its name which is the second reference method. 6 00:00:23,690 --> 00:00:29,960 The third method is referencing the currently active sheet similar to how we can use active workbook. 7 00:00:30,200 --> 00:00:35,690 The last reference method which is plain sheets can be used to refer to any sheet type in Excel not 8 00:00:35,690 --> 00:00:41,150 just worksheets worksheets or what Excel defaults to when you add new sheets is what you're used to 9 00:00:41,150 --> 00:00:46,010 seeing these sheets with lots of cells that we can enter data into a chart sheet is the second most 10 00:00:46,010 --> 00:00:50,660 common sheet type and it contains a chart but none of the data it's different than embedding a chart 11 00:00:50,690 --> 00:00:53,030 onto your worksheet like most people do. 12 00:00:53,060 --> 00:00:57,130 You can also have dialog and macro sheets but these are almost never used. 13 00:00:57,170 --> 00:01:03,080 You can see these options by right clicking on your sheet and selecting insert and we can see here. 14 00:01:03,170 --> 00:01:07,020 We have a couple of different options worksheet chart macro dialog. 15 00:01:07,190 --> 00:01:13,340 If we insert a chart sheet just to see we can see this is a completely blank sheet. 16 00:01:13,340 --> 00:01:17,890 There are no there's no data there's no cells or anything it is just this chart. 17 00:01:18,170 --> 00:01:25,440 So we'll go ahead and delete this and go back to our worksheet here. 18 00:01:25,500 --> 00:01:29,400 Now we can activate and count worksheets just like we can with workbooks. 19 00:01:29,430 --> 00:01:33,290 We can also protect or protect individual sheets like we could with workbooks. 20 00:01:33,300 --> 00:01:36,810 And that's a super helpful tool to use with VBA. 21 00:01:36,810 --> 00:01:41,850 You can add sheets you can delete sheets you can copy and paste them all of which is again useful to 22 00:01:41,850 --> 00:01:43,300 do in VBA. 23 00:01:43,350 --> 00:01:47,120 And again you can see the full list of interactions you can use with worksheets. 24 00:01:47,130 --> 00:01:52,770 If you go to Microsoft's help site or look up the worksheet object in the object library. 25 00:01:52,770 --> 00:01:57,910 These are the most common properties that you'll probably interact with using the worksheet object. 26 00:01:58,050 --> 00:02:02,070 First is the sheet name which is pretty obvious it gets you the name of the sheet. 27 00:02:02,070 --> 00:02:06,720 The second is the list objects property and this will let you reference objects such as tables that 28 00:02:06,720 --> 00:02:08,360 might appear on the sheet. 29 00:02:08,430 --> 00:02:13,980 The row property you can use to delete insert or account rows on the sheet in the columns property lets 30 00:02:13,980 --> 00:02:19,500 you delete insert or count the columns on the sheet all of which is really helpful to do so now that 31 00:02:19,500 --> 00:02:22,340 we understand a little bit about how the worksheet object works. 32 00:02:22,350 --> 00:02:27,690 We can actually go and apply some of these actions and interactions in VBA. 33 00:02:27,930 --> 00:02:34,560 So I'll go to my developer tab click Visual Basic to open up my visual basic window and you can see 34 00:02:34,560 --> 00:02:39,750 here I've already inserted a module in this case it's module 2 because I had an existing module already 35 00:02:40,220 --> 00:02:44,310 and I've gone ahead and I've written some code so I'll give you a second to pause and copy this code 36 00:02:44,310 --> 00:02:49,840 if you want to and that should have been enough time so now I'm going to walk through this line by line. 37 00:02:49,870 --> 00:02:55,720 So the first thing I did was create an integer variable C and I'm going to use C to store the count 38 00:02:55,750 --> 00:02:56,550 of my worksheet. 39 00:02:56,560 --> 00:03:01,270 So I would expect the system to count however many worksheets I have in my active workbook which should 40 00:03:01,270 --> 00:03:07,210 be this example workbook it's the only workbook I have open and it should return that C as a message 41 00:03:07,210 --> 00:03:15,720 box so I would expect this to read in this case 3 because I only have three sheets in my workbook. 42 00:03:15,890 --> 00:03:18,920 Now I'm going to use the same trick I used in the workbook video. 43 00:03:18,990 --> 00:03:25,560 We can actually create a worksheet variable type which I've called W us and census and object type variable 44 00:03:25,570 --> 00:03:30,430 we need to use set when we decide what this variable stands for. 45 00:03:30,450 --> 00:03:36,090 And in this case I've gone ahead and I've specified my workbook so we know even if I have other workbooks 46 00:03:36,090 --> 00:03:44,880 open my w s variable here is always going to represent this workbooks and then in this worksheet so 47 00:03:44,880 --> 00:03:48,380 in this case I've told it to look at the worksheet objects. 48 00:03:48,380 --> 00:03:55,880 So if I go into my file here we could see worksheet objects is the name of this particular sheet so 49 00:03:55,890 --> 00:04:00,570 if I come back into my VBA we can see that matches up with the name that I've inserted here. 50 00:04:00,570 --> 00:04:04,760 So every time I type w us that's the sheet it's going to be referring to. 51 00:04:04,770 --> 00:04:11,190 So the first thing I did was protect this W.S. sheet and I inserted a password here and if I hit space 52 00:04:11,220 --> 00:04:13,020 we can take a look at this really quick. 53 00:04:13,050 --> 00:04:18,450 So the password I've given it is just quote password it's a simple texturing there's nothing fancy about 54 00:04:18,450 --> 00:04:19,120 it. 55 00:04:19,230 --> 00:04:25,770 All of these other inputs here they are all true and false inputs and they're all pretty straightforward. 56 00:04:25,770 --> 00:04:32,850 For example if we put false for allow the leading rows the user wouldn't be able to delete rows when 57 00:04:32,970 --> 00:04:35,730 this sheet is password protected. 58 00:04:35,790 --> 00:04:41,270 But if you put true then the user would be allowed to delete rows even if they weren't allowed to delete 59 00:04:41,270 --> 00:04:46,060 the columns or to format the columns or anything like that. 60 00:04:46,230 --> 00:04:52,890 One interesting property here is the user interface input and what this means is that if we put true 61 00:04:52,890 --> 00:04:58,680 for this then it applies only to the user interface meaning it doesn't apply to our VBA code. 62 00:04:58,680 --> 00:05:04,650 So for our macro essentially the workbook would still or the worksheet would still be unprotected. 63 00:05:04,770 --> 00:05:07,600 But for the user it would be a protected worksheet. 64 00:05:07,600 --> 00:05:11,190 That's a particularly useful property that you might want to pay attention to. 65 00:05:11,250 --> 00:05:16,430 In this case I'm going to let all those default whatever they are usually in Excel whatever my Excel 66 00:05:16,440 --> 00:05:18,570 usually defaults them to. 67 00:05:18,720 --> 00:05:22,140 And I'm just going to apply a password to it which I've just typed. 68 00:05:22,140 --> 00:05:29,440 Password for now I'm going to create a second worksheet and so I've gone ahead and I've created a second 69 00:05:29,440 --> 00:05:32,040 worksheet variable which I'm calling W.S. 2. 70 00:05:32,050 --> 00:05:36,640 And then I've set that worksheet variable equal to again the same workbook that I want to be in. 71 00:05:36,640 --> 00:05:42,290 This only workbook that I have open and so I've used dot sheets dot add. 72 00:05:42,340 --> 00:05:48,280 Now when you add sheet store workbook you should use these sheets type reference instead of the worksheets 73 00:05:48,280 --> 00:05:48,970 reference. 74 00:05:49,120 --> 00:05:54,310 And that's because we can actually specify if we wanted to add a charge sheet or work cheat sheet or 75 00:05:54,310 --> 00:05:58,210 a macro sheet or a dialog sheet or whatever type of sheet we want to add. 76 00:05:58,330 --> 00:06:02,170 If we don't specify at all it's just going to default to a worksheet which is fine in the scenario. 77 00:06:02,170 --> 00:06:03,640 So we'll let it default. 78 00:06:03,640 --> 00:06:10,390 Now I put a W S activate so again when we add this sheet the sheet that we've just added will automatically 79 00:06:10,390 --> 00:06:10,870 activate. 80 00:06:11,200 --> 00:06:17,620 So with W.S. activate which we should see our screen bounced back to this original worksheet that we 81 00:06:17,620 --> 00:06:21,530 were working in this worksheet objects sheet and then us to activate. 82 00:06:21,530 --> 00:06:28,180 So we should see everything bounce back into our W.S. to sheet which is that new sheet now just to give 83 00:06:28,180 --> 00:06:29,320 a quick example. 84 00:06:30,010 --> 00:06:34,800 I went ahead and created two values that should show up on our new sheet here. 85 00:06:34,840 --> 00:06:36,720 The first is in row two column two. 86 00:06:37,000 --> 00:06:39,060 And it should read test. 87 00:06:39,190 --> 00:06:43,400 The next is in row three column three and it should read test two. 88 00:06:43,420 --> 00:06:48,820 Now I created those values those cell values on the sheet so that we can see the effects of deleting 89 00:06:48,820 --> 00:06:50,270 a row and deleting a column. 90 00:06:50,710 --> 00:06:56,570 So this line will delete row two off of our worksheet to here which is the one we added. 91 00:06:56,740 --> 00:06:59,400 And this will delete column three. 92 00:06:59,440 --> 00:07:05,510 So we should see both of these values ultimately disappear as this row and this column are deleted. 93 00:07:05,590 --> 00:07:10,440 The last thing I'm going to do down here I'm going to create a name variable which I'm going to pull 94 00:07:10,450 --> 00:07:15,490 as the name property of the worksheet and then I'm going to display that name in a message box. 95 00:07:15,490 --> 00:07:18,030 So we'll know exactly what this sheet is called. 96 00:07:18,370 --> 00:07:23,880 The very last thing I'll do is copy the worksheet and if I hit space here we can see what these inputs 97 00:07:23,880 --> 00:07:24,090 are. 98 00:07:24,510 --> 00:07:29,190 So after you copy a worksheet you can decide to tell the system where to paste that worksheet so we 99 00:07:29,190 --> 00:07:32,580 can either put it before something or we can put it after something. 100 00:07:32,640 --> 00:07:38,040 In this case I've told it to go ahead and put this before my original worksheet which was this worksheet 101 00:07:38,070 --> 00:07:46,190 objects sheet file here so we're gonna go ahead and delete this sheet that we had originally added it'll 102 00:07:46,210 --> 00:07:49,170 probably be named something like she or sheet too. 103 00:07:49,280 --> 00:07:54,940 And it's this original sheet that we added up here but we're going to leave this copy sheet which will 104 00:07:54,940 --> 00:07:59,170 be like sheet one copy or sheet to copy and we're going to leave that in our file. 105 00:07:59,170 --> 00:08:03,970 So we should still see the copy chief but we shouldn't see the original sheet that we had added when 106 00:08:03,970 --> 00:08:04,920 we're done. 107 00:08:04,960 --> 00:08:08,560 So now we're gonna go ahead and run this code and test it out make sure it's behaving properly and we're 108 00:08:08,560 --> 00:08:14,210 gonna do this line by line so we can see what each of these individual statements does in VBA. 109 00:08:14,280 --> 00:08:20,260 We're going to do this by using f eight on our keyboard or if you go up to debug you can you step into 110 00:08:20,260 --> 00:08:24,010 right here and we can see FHA is the associated keyboard shortcut. 111 00:08:24,040 --> 00:08:29,590 So I'll go in and click on this to start the macro and now I'll step through this with the FHA. 112 00:08:29,950 --> 00:08:35,770 So the first thing that this line does is count our worksheets and then provide that in a message box. 113 00:08:35,770 --> 00:08:39,430 So when I hit that date I should see a message box which I do. 114 00:08:39,430 --> 00:08:43,440 And it tells me I have three worksheets which is what we expect we have three worksheets down here. 115 00:08:43,450 --> 00:08:47,860 So we know that our worksheet count function is literally just counting the number of worksheets we 116 00:08:47,860 --> 00:08:49,680 have in this particular workbook. 117 00:08:49,810 --> 00:08:52,330 So I'll click OK. 118 00:08:52,380 --> 00:08:56,160 Now we're gonna go ahead and set up this little shortcut that we have all referencing our worksheet 119 00:08:56,190 --> 00:09:02,140 objects of sheet that is in our workbook and we're gonna go ahead and password protect that sheet. 120 00:09:02,160 --> 00:09:07,770 Now when I come back to this original the worksheet object sheet later I should have to on password 121 00:09:07,770 --> 00:09:10,430 protected to change anything. 122 00:09:10,490 --> 00:09:12,710 The next thing we're going to do is add a sheet. 123 00:09:12,710 --> 00:09:18,980 And at this point I will make these half windows so we can see the direct impact of this VBA code. 124 00:09:19,310 --> 00:09:24,230 So make sure you click back into your VBA code if you just have your windows to make sure that when 125 00:09:24,230 --> 00:09:26,640 you hit at 8 it is responding in VBA. 126 00:09:27,080 --> 00:09:32,540 So we'll make sure we're clicked into this and hit it way and we can see we have actually added a new 127 00:09:32,540 --> 00:09:35,950 sheet here in this case it's called sheet 1. 128 00:09:35,960 --> 00:09:41,080 Now when I hit F eight you'll notice that right now my sheet 1 which is the sheet I just added my W.S. 129 00:09:41,090 --> 00:09:43,600 to is what's active right now. 130 00:09:43,610 --> 00:09:49,130 So when I hit F eight off of this line we should see my original sheet that I identified up here activate 131 00:09:49,160 --> 00:09:51,770 instead which it does. 132 00:09:51,770 --> 00:09:56,070 We can see we've jumped back to our worksheet objects which is what we would expect when I hit update 133 00:09:56,100 --> 00:09:56,640 again. 134 00:09:56,690 --> 00:10:02,740 We should jump back to that sheet we just added so that's the difference between activating your worksheets 135 00:10:03,180 --> 00:10:06,130 that's the same as when we were activating workbooks before. 136 00:10:06,130 --> 00:10:10,440 Same as when we manually click around on the sheets down here. 137 00:10:10,570 --> 00:10:14,350 It just changes what sheet is active right now. 138 00:10:14,380 --> 00:10:20,740 So I've gone ahead and have added some this these two test strings right here so we can see the effect 139 00:10:20,740 --> 00:10:26,560 of the deleting of rows and columns so we'll go ahead and hit edit here and we see our test value shows 140 00:10:26,560 --> 00:10:32,980 up and Selby to update again test two shows up and cell C 3 which is what we expect. 141 00:10:33,310 --> 00:10:38,680 So now we're going to go ahead and delete Row 2 which means this entire row that contains this test 142 00:10:38,740 --> 00:10:42,790 right here should disappear that's about how we expected. 143 00:10:43,350 --> 00:10:47,610 And now we're going to delete all of column three which is this column see right here. 144 00:10:47,610 --> 00:10:49,430 So this test too should disappear. 145 00:10:50,720 --> 00:10:56,960 So now we know that the deleting of columns and rows behaves how we expected. 146 00:10:56,960 --> 00:11:00,830 Now we're gonna go ahead and identify the name of the sheet which we can see down here should be sheet 147 00:11:00,830 --> 00:11:01,340 1. 148 00:11:01,340 --> 00:11:07,680 So I'll hit a date and then update again to see what shows up in this message box and it is Sheet 1. 149 00:11:08,010 --> 00:11:13,080 So we know this is how the Name property of our sheet behaves it's behaving as expected. 150 00:11:13,140 --> 00:11:14,880 So click OK. 151 00:11:14,910 --> 00:11:19,470 Now this line should copy the sheet one that we added in here originally. 152 00:11:19,470 --> 00:11:21,180 So this sheet that I have active right now. 153 00:11:21,600 --> 00:11:26,600 And it should make a copy of this before my NWS which was this original worksheet. 154 00:11:26,610 --> 00:11:28,860 This original worksheet objects here. 155 00:11:28,860 --> 00:11:32,680 So we should see a second sheet show up like right here in our tabs. 156 00:11:32,850 --> 00:11:38,220 And it should be called sheet one copy or something like that so we'll hit at 8:00 and we can see we 157 00:11:38,220 --> 00:11:42,320 have Sheet 1 2 which is essentially the same thing as sheet one copy. 158 00:11:42,360 --> 00:11:45,120 This is just a copy of the sheet one file. 159 00:11:45,120 --> 00:11:53,270 So now when I hit at 8:00 one more time we should see this original sheet one that we have here deleted. 160 00:11:53,320 --> 00:11:59,440 Now we did talk a little bit before in a previous video about how we can actually turn off these dialog 161 00:11:59,440 --> 00:12:00,310 boxes. 162 00:12:00,310 --> 00:12:05,980 So if you're doing a lot of deleting sheets or saving or things that might trigger these dialog boxes 163 00:12:05,980 --> 00:12:10,720 you can actually turn them off and we went over that in our dialogue video a while back. 164 00:12:10,720 --> 00:12:15,700 But for now we'll just go ahead and approve this request say delete and we can see it. 165 00:12:15,700 --> 00:12:18,430 It did actually delete that sheet when we had done there. 166 00:12:18,640 --> 00:12:25,760 And now this is the last step here I think one more time and we've completed our subroutine so now that 167 00:12:25,760 --> 00:12:31,730 we have a general understanding of the worksheet object we kind of went through how to apply all of 168 00:12:31,730 --> 00:12:35,780 these different properties and different interactions that we covered here and now we have a pretty 169 00:12:35,780 --> 00:12:40,100 good understanding of what the worksheet object is and how to use it in VBA.