1 00:00:00,850 --> 00:00:06,070 So we covered tables in a previous video and why they are important to use in excel. 2 00:00:06,070 --> 00:00:11,560 So in this video we're going to talk specifically about the use of tables within Visual Basic. 3 00:00:11,560 --> 00:00:16,990 So on this table interaction sheet I have here my example workbook I've created this basic table and 4 00:00:16,990 --> 00:00:18,000 it's pretty straightforward. 5 00:00:18,010 --> 00:00:22,660 As a few names their favorite color their favorite fruit and the quantity of that fruit that they have. 6 00:00:22,720 --> 00:00:29,150 And if I click into this table and go to the table design tab up here and my ribbon we can see that 7 00:00:29,150 --> 00:00:30,620 I've given this table a name. 8 00:00:30,620 --> 00:00:36,290 In this case example table and this name is really important for identifying the table in VBA. 9 00:00:36,320 --> 00:00:41,030 So if you're going to follow along with your own table make sure that you note your tables name. 10 00:00:41,030 --> 00:00:46,790 So far as our object hierarchy is concerned our tables fall under our worksheet object so we'll need 11 00:00:46,790 --> 00:00:52,340 to track this hierarchy through application identifier workbook specify a worksheet and then we can 12 00:00:52,340 --> 00:00:58,610 use the list objects property of worksheets to find our table and the list objects method is how we 13 00:00:58,610 --> 00:01:03,320 would reference any of the objects that live on a particular worksheet and tables are identified as 14 00:01:03,350 --> 00:01:04,670 objects within VBA. 15 00:01:04,670 --> 00:01:06,450 So that's what we're gonna use. 16 00:01:06,470 --> 00:01:12,880 So if we go ahead and open up our VBA window by going to developer and then Visual Basic we can go ahead 17 00:01:12,880 --> 00:01:20,120 and insert a new module and let's give ourselves some space here we'll go ahead and we'll create one 18 00:01:20,720 --> 00:01:28,050 Public Sub and we'll just call this new table code something like that give ourselves some room. 19 00:01:28,070 --> 00:01:35,330 So now we're gonna go ahead and type here worksheets and then table interactions because that's the 20 00:01:35,330 --> 00:01:37,890 name of the sheet that we want to look at. 21 00:01:37,970 --> 00:01:44,860 Then we use the period to move a step further into our object hierarchy list objects and then we'll 22 00:01:44,860 --> 00:01:49,810 give it the name of our table which is example table or you can use whatever the table name is that 23 00:01:49,810 --> 00:01:51,260 you're referencing. 24 00:01:51,310 --> 00:01:56,110 Now we're going to put a period again to move again one step further into the object hierarchy. 25 00:01:56,170 --> 00:02:00,600 This time we'll say range select so range. 26 00:02:00,610 --> 00:02:05,860 In this case is going to select the entire range of the table and select just means we're going to highlight 27 00:02:06,130 --> 00:02:09,270 that particular range and so we'll step through this line by line. 28 00:02:09,400 --> 00:02:16,470 I'm just using select as a visual for what this particular range property here is going to represent. 29 00:02:16,510 --> 00:02:24,550 So we'll go ahead make another line per table interactions worksheet we'll look at the objects on that 30 00:02:24,550 --> 00:02:30,430 worksheet and reference our example table and this time instead of saying range we're going to say data 31 00:02:30,460 --> 00:02:38,740 body range dot select and this time data body range will only select the data of the table it will leave 32 00:02:38,740 --> 00:02:41,110 out things like the header or the total row. 33 00:02:41,440 --> 00:02:46,240 So if we wanted to reference specifically the header row we can do that as well. 34 00:02:46,270 --> 00:02:53,840 So again we'll follow through the same hierarchy through list objects name our table and this time we'll 35 00:02:53,840 --> 00:02:58,430 say header row range select. 36 00:02:58,430 --> 00:03:04,270 And lastly if we wanted to represent our totals row we could do that as well. 37 00:03:04,430 --> 00:03:12,740 Similar to how we've done everything else reference our sheet reference our table and then totals row 38 00:03:13,670 --> 00:03:17,900 range dot select. 39 00:03:18,120 --> 00:03:23,400 So if we break this down I've specified our worksheet to be the table interactions worksheet and we're 40 00:03:23,400 --> 00:03:27,330 letting VBA assume our Excel application and our example workbook. 41 00:03:27,330 --> 00:03:31,560 But if you wanted to be safe or if you had multiple workbooks open at the same time you might want to 42 00:03:31,560 --> 00:03:37,200 specify the name of your workbook specifically then we tell VBA to look at all of the objects that are 43 00:03:37,200 --> 00:03:41,730 going to be on that sheet and identify the one that's called example table which is what we've named 44 00:03:41,730 --> 00:03:46,490 our table where they're going to say the range of that table where the data body range of the head arranged 45 00:03:46,560 --> 00:03:52,500 the total row range whatever it is that we want to look at and what we've told VBA here to just select 46 00:03:52,530 --> 00:03:54,570 just highlight that data for us. 47 00:03:54,570 --> 00:03:59,610 So what I'm going to do is make each of these half screen and then I'm going to step through this code 48 00:03:59,610 --> 00:04:05,550 line by line and the way we do that is with the FAA key on your keyboard or use debug and step into 49 00:04:07,390 --> 00:04:09,030 now what effort. 50 00:04:09,180 --> 00:04:15,180 Now after we had a bit more time we should see VBA run this particular line of code and we can see now 51 00:04:15,180 --> 00:04:21,090 VBA has selected this entire table range it includes this hetero includes all of the data in the table 52 00:04:21,090 --> 00:04:26,340 and it includes this totals row down here if we had at eight one more time we should see it changed 53 00:04:26,340 --> 00:04:32,280 to only select the data body here and we can see it's done that we've only got the data of this table 54 00:04:32,280 --> 00:04:37,230 we don't have the totals row and we don't have the hetero this time we should see only the header rugged 55 00:04:37,230 --> 00:04:44,500 selected and next we should see only the totals we get selected and then hit eight one more time to 56 00:04:44,500 --> 00:04:51,370 end your subroutine so we can reference these different parts of the table and there are different scenarios 57 00:04:51,370 --> 00:04:55,870 where you might want only the header row maybe you're making the font size bigger you might want only 58 00:04:55,870 --> 00:05:01,480 the total row maybe you're changing whether it's bold or regular text or you might only want to know 59 00:05:01,480 --> 00:05:04,210 the data itself and where that data lives. 60 00:05:04,210 --> 00:05:09,540 So there's different scenarios where you might use range versus data body range versus hetero or totals 61 00:05:09,540 --> 00:05:13,120 row range but it's good to know how to use all of them. 62 00:05:13,180 --> 00:05:17,590 Now the main thing that we want to do with tables is deal with the tables rows or columns. 63 00:05:17,590 --> 00:05:22,870 And mainly we want to add delete or count those rows or columns similar to how we can reference a row 64 00:05:22,870 --> 00:05:25,100 or column on the worksheet by using a number. 65 00:05:25,240 --> 00:05:29,740 We can do the same thing with tables but it's important to note that the number we reference will be 66 00:05:29,740 --> 00:05:32,620 the row number relative to the table itself. 67 00:05:32,920 --> 00:05:39,670 So if we look at this name here Emma and we look at this row on the worksheet it's row five. 68 00:05:39,830 --> 00:05:41,960 We can see five is highlighted here. 69 00:05:41,960 --> 00:05:44,290 This is row five on our full worksheet. 70 00:05:44,390 --> 00:05:50,180 But with respect to the table it's only going to be row three because it's the third row in one two 71 00:05:50,330 --> 00:05:57,030 third row so when we identify this row we need to make sure it's properly tracked through our object 72 00:05:57,030 --> 00:06:04,360 hierarchy and then is relative to the table and not to the entire worksheet so we're gonna give ourselves 73 00:06:04,360 --> 00:06:07,200 some more room in our VBA code. 74 00:06:07,300 --> 00:06:14,560 And next we're going to create a table variable just like we've done before with our worksheets and 75 00:06:14,560 --> 00:06:19,510 workbooks that can be really tedious to try to type out this worksheets table interactions that list 76 00:06:19,510 --> 00:06:24,100 objects example table every time when we know we're going to continue to reference this table over and 77 00:06:24,100 --> 00:06:24,730 over. 78 00:06:24,730 --> 00:06:29,680 So what I'm going to do is create a variable here I've called the example table Little E X TB l here 79 00:06:30,340 --> 00:06:37,120 and I'm going to create this as a list object so just like we did with the workbooks and worksheets 80 00:06:37,210 --> 00:06:42,520 we're gonna use the set commands since this isn't something like an integer or string that's really 81 00:06:42,520 --> 00:06:46,840 storing a variable rather it's an object and we're storing that object path. 82 00:06:47,020 --> 00:06:56,920 So we're going to set example table equal to worksheets table interactions that list objects example 83 00:06:56,920 --> 00:07:05,040 table so now rather than having to type this out every single time I can just use X L to represent my 84 00:07:05,040 --> 00:07:06,020 particular table. 85 00:07:06,030 --> 00:07:10,740 And if you were referencing the application or your workbook as well you could also put that at the 86 00:07:10,740 --> 00:07:16,650 front of this line here we got ourselves a little bit more room again and this time we're gonna learn 87 00:07:16,650 --> 00:07:19,950 how to add and delete columns and rows. 88 00:07:19,950 --> 00:07:25,200 So I'm going to type our variable that we're using to represent our table and then I'm going to type 89 00:07:25,260 --> 00:07:28,750 list rows that add. 90 00:07:29,400 --> 00:07:35,730 Now the list rows property here means that we're dealing with these individual rows of our table and 91 00:07:35,910 --> 00:07:42,120 specifically VBA knows to list these out by one two three four five and six. 92 00:07:42,150 --> 00:07:47,400 So in this case we told it we're not necessarily referencing any specific row but we do want to add 93 00:07:47,430 --> 00:07:48,600 a new row. 94 00:07:48,600 --> 00:07:56,880 We can do the same thing with our columns in this case list columns instead of list rows and add. 95 00:07:56,900 --> 00:07:58,630 And again this represents the same thing. 96 00:07:58,640 --> 00:08:05,060 If we go to our table lists columns will recognize and VBA This is column 1 2 3 and 4 in this case we're 97 00:08:05,060 --> 00:08:07,330 not referencing any specific column. 98 00:08:07,340 --> 00:08:12,890 Rather we are trying to add a new one the next thing we can do is delete. 99 00:08:13,490 --> 00:08:17,630 So we'll just delete those same rows and columns that we just added. 100 00:08:17,630 --> 00:08:22,860 So in this case I am going to specify a specific row so many use my open parentheses. 101 00:08:23,000 --> 00:08:27,170 I'm going to feed it the number of the row that I want to work with and then close parentheses. 102 00:08:27,170 --> 00:08:33,350 So in this case we've got one two three four five six rows so that row that I just added up here should 103 00:08:33,350 --> 00:08:41,010 be row 7 and this time I'm going to use that delete instead have got add I'll do the same thing with 104 00:08:41,010 --> 00:08:45,700 the columns and in this case it should be column 5. 105 00:08:46,660 --> 00:08:50,430 And that's because we have 1 2 3 4 columns already. 106 00:08:50,530 --> 00:08:53,520 So we'll be adding this fifth column. 107 00:08:54,580 --> 00:08:58,840 And the last thing we'll look at is counting the rows and columns which is probably the thing you'll 108 00:08:58,840 --> 00:09:01,690 do most often with tables in VBA. 109 00:09:01,690 --> 00:09:09,310 So we'll create two integers will create an integer C for our columns and an integer R for our rows 110 00:09:10,420 --> 00:09:20,930 and then we're going to use C equals the set value of c example table less columns and instead of add 111 00:09:20,930 --> 00:09:22,610 or delete we're gonna say count. 112 00:09:22,700 --> 00:09:27,740 So again we haven't really specified a particular column that we want to look at because really we want 113 00:09:27,740 --> 00:09:32,360 to look at all of the columns and in this case we want to count them and we'll do the same thing with 114 00:09:32,360 --> 00:09:36,100 our rows as the variable are. 115 00:09:36,560 --> 00:09:39,080 So we'll use list rows that count. 116 00:09:39,350 --> 00:09:50,250 And the last thing we'll do is create a little message box down here that says rows and are and BBC 117 00:09:50,250 --> 00:10:00,690 are all left for New Line and then we'll say columns and C and we've built message boxes in the past 118 00:10:01,200 --> 00:10:03,020 that we've kind of explain some of this. 119 00:10:03,240 --> 00:10:08,340 If you want more information on this particular code or why we're using these Ampersand that'll be in 120 00:10:08,340 --> 00:10:10,110 the string manipulation video. 121 00:10:10,230 --> 00:10:16,800 But to walk through this really quickly we're the the signs here combine different text string. 122 00:10:16,800 --> 00:10:20,330 So in this case we have a texturing here that we specified as rows. 123 00:10:20,400 --> 00:10:23,710 We know it's text because it's surrounded in these quotations here. 124 00:10:23,940 --> 00:10:28,170 And we've told it we want you to combine that text with whatever stored in this particular variable 125 00:10:28,230 --> 00:10:31,720 in this case the number of rows that we have that we use the. 126 00:10:31,730 --> 00:10:35,240 And sine again to tell VBA we're not finished building this full stream. 127 00:10:35,250 --> 00:10:36,480 We want to keep going. 128 00:10:36,630 --> 00:10:38,870 BBC are Al F is a new line. 129 00:10:38,880 --> 00:10:44,260 It's a code that just means like hitting enter on your keyboard just providing an extra space. 130 00:10:44,280 --> 00:10:49,170 So on a new line we're going to combine that with the text columns which again we've surrounded includes 131 00:10:49,170 --> 00:10:51,370 here to represent that it is text. 132 00:10:51,480 --> 00:10:57,210 And the last thing we'll do is combine that with the C variable here which is the number of columns 133 00:10:57,210 --> 00:10:59,580 that we had in our table. 134 00:10:59,820 --> 00:11:01,360 So we're gonna go ahead and run this. 135 00:11:01,380 --> 00:11:06,210 And again I'm going to do this line by lines that we can get a feel for what each of these lines in 136 00:11:06,210 --> 00:11:12,430 the code does to our table so I'm going to click anywhere in my routine here and I'm going to click 137 00:11:12,760 --> 00:11:15,910 f eight on my keyboard. 138 00:11:15,950 --> 00:11:21,290 So again we should see this select the entire range of our table then we should see it select only the 139 00:11:21,290 --> 00:11:31,140 data in our table moving to the header row of our table and moving to the totals row in our table so 140 00:11:31,170 --> 00:11:37,620 we've just set the variable for X TTL so we don't want to continue to type out that particular object 141 00:11:37,620 --> 00:11:38,840 path every time. 142 00:11:38,850 --> 00:11:43,130 So this time we should see a row get added and we can see here. 143 00:11:43,130 --> 00:11:47,850 I do actually have a new row in my particular table that I wanted to look at. 144 00:11:47,880 --> 00:11:52,050 Next we should see a new column get added and we can see. 145 00:11:52,050 --> 00:11:54,470 I do indeed have a new column over here. 146 00:11:54,480 --> 00:11:59,760 Now we're gonna go ahead and delete this row that we just added and we can see that has disappeared. 147 00:11:59,820 --> 00:12:05,300 And next we're going to delete the column that we just added and that's gone as well now. 148 00:12:05,340 --> 00:12:11,400 So the last thing we'll do is count the number of columns count the number of rows and then display 149 00:12:11,400 --> 00:12:18,230 that in a message box so we should expect somewhere around six rows and four columns and it looks like 150 00:12:18,230 --> 00:12:25,010 that's what we've got six rows four columns and we'll click OK and then hit F eight one more time to 151 00:12:25,250 --> 00:12:32,580 end your subroutine in this video we talked about tables and how we can interact with them in VBA we 152 00:12:32,590 --> 00:12:37,590 covered the different ranges of the table we might want to use as well as the basics of adding deleting 153 00:12:37,660 --> 00:12:43,300 and counting the number of rows and columns in the table and a previous video we talked about why tables 154 00:12:43,300 --> 00:12:49,330 are important to use in excel so it's equally important to know how to reference them in VBA using the 155 00:12:49,330 --> 00:12:51,100 methods we covered in this video.