VBA Blogs: Unforget-TABLE, That’s What You Are

The first in a series about using ListObjects to manipulate Tables within an Excel workbook in VBA.

Warning! This was written whilst listening to a Nat King Cole playlist…

“Look What You’ve Done To Me”, storing all the data in a table in the Excel workbook. How does one access the table from VBA and “Make Her Mine”? It’s a lot simpler than one would think, “Pick Yourself Up” and consider the ListObjects method in VBA.

First let’s create a table from a Range in VBA. “I’m Never Satisfied” with storing data as raw, it’s nicer to make it into a table. This involves using the Add method of ListObjects.

The parameters are as follows:

• SourceType – the SourceType. xlSrcRange indicates that is a Range item not a “Make Believe Island”
• Source – this is only applicable when xlSrcRange is used so that VBA doesn’t have to go searching “To The Ends of the Earth”
• LinkSource – this is when external data sets are used, and not applicable when using the Range item. “Don’t Blame Me” for skipping it in this case as it isn’t necessary
• xlListObjectHasHeaders – if the first row of the Range is actually a header row.

The Name method is added to the end of the ListObjects call to rename the table as required and stamp it “My Personal Possession”.

Before:

After:

“You Call It Madness” but if xlListObjectHasHeaders is set to xlNo for headers the following result is achieved:

The range of the data is actually shifted down by one and generic headers created! By default, VBA will assume xlGuess where it makes a judgement call on the data. It is best practice to specify to VBA directly the header intention otherwise there may be “Looking Back” on unexpected results thinking “That Ain’t Right” at all.

Stay in tune to see "Whatcha' Gonna Do" in order to manipulate the data in the table!