So we covered tables in a previous video and why they are important to use in excel.
So in this video we're going to talk specifically about the use of tables within Visual Basic.
So on this table interaction sheet I have here my example workbook I've created this basic table and
it's pretty straightforward.
As a few names their favorite color their favorite fruit and the quantity of that fruit that they have.
And if I click into this table and go to the table design tab up here and my ribbon we can see that
I've given this table a name.
In this case example table and this name is really important for identifying the table in VBA.
So if you're going to follow along with your own table make sure that you note your tables name.
So far as our object hierarchy is concerned our tables fall under our worksheet object so we'll need
to track this hierarchy through application identifier workbook specify a worksheet and then we can
use the list objects property of worksheets to find our table and the list objects method is how we
would reference any of the objects that live on a particular worksheet and tables are identified as
objects within VBA.
So that's what we're gonna use.
So if we go ahead and open up our VBA window by going to developer and then Visual Basic we can go ahead
and insert a new module and let's give ourselves some space here we'll go ahead and we'll create one
Public Sub and we'll just call this new table code something like that give ourselves some room.
So now we're gonna go ahead and type here worksheets and then table interactions because that's the
name of the sheet that we want to look at.
Then we use the period to move a step further into our object hierarchy list objects and then we'll
give it the name of our table which is example table or you can use whatever the table name is that
you're referencing.
Now we're going to put a period again to move again one step further into the object hierarchy.
This time we'll say range select so range.
In this case is going to select the entire range of the table and select just means we're going to highlight
that particular range and so we'll step through this line by line.
I'm just using select as a visual for what this particular range property here is going to represent.
So we'll go ahead make another line per table interactions worksheet we'll look at the objects on that
worksheet and reference our example table and this time instead of saying range we're going to say data
body range dot select and this time data body range will only select the data of the table it will leave
out things like the header or the total row.
So if we wanted to reference specifically the header row we can do that as well.
So again we'll follow through the same hierarchy through list objects name our table and this time we'll
say header row range select.
And lastly if we wanted to represent our totals row we could do that as well.
Similar to how we've done everything else reference our sheet reference our table and then totals row
range dot select.
So if we break this down I've specified our worksheet to be the table interactions worksheet and we're
letting VBA assume our Excel application and our example workbook.
But if you wanted to be safe or if you had multiple workbooks open at the same time you might want to
specify the name of your workbook specifically then we tell VBA to look at all of the objects that are
going to be on that sheet and identify the one that's called example table which is what we've named
our table where they're going to say the range of that table where the data body range of the head arranged
the total row range whatever it is that we want to look at and what we've told VBA here to just select
just highlight that data for us.
So what I'm going to do is make each of these half screen and then I'm going to step through this code
line by line and the way we do that is with the FAA key on your keyboard or use debug and step into
now what effort.
Now after we had a bit more time we should see VBA run this particular line of code and we can see now
VBA has selected this entire table range it includes this hetero includes all of the data in the table
and it includes this totals row down here if we had at eight one more time we should see it changed
to only select the data body here and we can see it's done that we've only got the data of this table
we don't have the totals row and we don't have the hetero this time we should see only the header rugged
selected and next we should see only the totals we get selected and then hit eight one more time to
end your subroutine so we can reference these different parts of the table and there are different scenarios
where you might want only the header row maybe you're making the font size bigger you might want only
the total row maybe you're changing whether it's bold or regular text or you might only want to know
the data itself and where that data lives.
So there's different scenarios where you might use range versus data body range versus hetero or totals
row range but it's good to know how to use all of them.
Now the main thing that we want to do with tables is deal with the tables rows or columns.
And mainly we want to add delete or count those rows or columns similar to how we can reference a row
or column on the worksheet by using a number.
We can do the same thing with tables but it's important to note that the number we reference will be
the row number relative to the table itself.
So if we look at this name here Emma and we look at this row on the worksheet it's row five.
We can see five is highlighted here.
This is row five on our full worksheet.
But with respect to the table it's only going to be row three because it's the third row in one two
third row so when we identify this row we need to make sure it's properly tracked through our object
hierarchy and then is relative to the table and not to the entire worksheet so we're gonna give ourselves
some more room in our VBA code.
And next we're going to create a table variable just like we've done before with our worksheets and
workbooks that can be really tedious to try to type out this worksheets table interactions that list
objects example table every time when we know we're going to continue to reference this table over and
over.
So what I'm going to do is create a variable here I've called the example table Little E X TB l here
and I'm going to create this as a list object so just like we did with the workbooks and worksheets
we're gonna use the set commands since this isn't something like an integer or string that's really
storing a variable rather it's an object and we're storing that object path.
So we're going to set example table equal to worksheets table interactions that list objects example
table so now rather than having to type this out every single time I can just use X L to represent my
particular table.
And if you were referencing the application or your workbook as well you could also put that at the
front of this line here we got ourselves a little bit more room again and this time we're gonna learn
how to add and delete columns and rows.
So I'm going to type our variable that we're using to represent our table and then I'm going to type
list rows that add.
Now the list rows property here means that we're dealing with these individual rows of our table and
specifically VBA knows to list these out by one two three four five and six.
So in this case we told it we're not necessarily referencing any specific row but we do want to add
a new row.
We can do the same thing with our columns in this case list columns instead of list rows and add.
And again this represents the same thing.
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
not referencing any specific column.
Rather we are trying to add a new one the next thing we can do is delete.
So we'll just delete those same rows and columns that we just added.
So in this case I am going to specify a specific row so many use my open parentheses.
I'm going to feed it the number of the row that I want to work with and then close parentheses.
So in this case we've got one two three four five six rows so that row that I just added up here should
be row 7 and this time I'm going to use that delete instead have got add I'll do the same thing with
the columns and in this case it should be column 5.
And that's because we have 1 2 3 4 columns already.
So we'll be adding this fifth column.
And the last thing we'll look at is counting the rows and columns which is probably the thing you'll
do most often with tables in VBA.
So we'll create two integers will create an integer C for our columns and an integer R for our rows
and then we're going to use C equals the set value of c example table less columns and instead of add
or delete we're gonna say count.
So again we haven't really specified a particular column that we want to look at because really we want
to look at all of the columns and in this case we want to count them and we'll do the same thing with
our rows as the variable are.
So we'll use list rows that count.
And the last thing we'll do is create a little message box down here that says rows and are and BBC
are all left for New Line and then we'll say columns and C and we've built message boxes in the past
that we've kind of explain some of this.
If you want more information on this particular code or why we're using these Ampersand that'll be in
the string manipulation video.
But to walk through this really quickly we're the the signs here combine different text string.
So in this case we have a texturing here that we specified as rows.
We know it's text because it's surrounded in these quotations here.
And we've told it we want you to combine that text with whatever stored in this particular variable
in this case the number of rows that we have that we use the.
And sine again to tell VBA we're not finished building this full stream.
We want to keep going.
BBC are Al F is a new line.
It's a code that just means like hitting enter on your keyboard just providing an extra space.
So on a new line we're going to combine that with the text columns which again we've surrounded includes
here to represent that it is text.
And the last thing we'll do is combine that with the C variable here which is the number of columns
that we had in our table.
So we're gonna go ahead and run this.
And again I'm going to do this line by lines that we can get a feel for what each of these lines in
the code does to our table so I'm going to click anywhere in my routine here and I'm going to click
f eight on my keyboard.
So again we should see this select the entire range of our table then we should see it select only the
data in our table moving to the header row of our table and moving to the totals row in our table so
we've just set the variable for X TTL so we don't want to continue to type out that particular object
path every time.
So this time we should see a row get added and we can see here.
I do actually have a new row in my particular table that I wanted to look at.
Next we should see a new column get added and we can see.
I do indeed have a new column over here.
Now we're gonna go ahead and delete this row that we just added and we can see that has disappeared.
And next we're going to delete the column that we just added and that's gone as well now.
So the last thing we'll do is count the number of columns count the number of rows and then display
that in a message box so we should expect somewhere around six rows and four columns and it looks like
that's what we've got six rows four columns and we'll click OK and then hit F eight one more time to
end your subroutine in this video we talked about tables and how we can interact with them in VBA we
covered the different ranges of the table we might want to use as well as the basics of adding deleting
and counting the number of rows and columns in the table and a previous video we talked about why tables
are important to use in excel so it's equally important to know how to reference them in VBA using the
methods we covered in this video.