VBA Create Table in Excel

VBA Create Table in Excel

VBA Create Table in Excel. In this tutorial we learn how to create table and it’s syntax, different examples using VBA in Excel. Also find and learn step by step instructions to run VBA example macro code.

VBA Syntax to create Table in Excel

Let us see the syntax to create table in Excel using VBA.

expression.Add (SourceType, Source, LinkSource, XlListObjectHasHeaders, Destination, TableStyleName)

Where expression represents a List-objects object.

SourceType is an optional parameter. It contains XlListObjectSourceType type data. Represents the kind of source for the query.

Source is an optional parameter. It contains variant type data. Represents either xlSrcRange (range object) or xlSrcExternal(An array of String values).

LinkSource is an optional parameter. It contains Boolean type data. specifies whether an external data source is to be linked to the List-object object. It depends on Source. Returns an error if not omitted.

XlListObjectHasHeaders is an optional parameter. It contains variant type data. It is an XlYesNoGuess constant that indicates whether the data being imported has column labels. If the Source data does not contain headers, Excel will automatically generate headers. The default value is xlGuess.

Let us see the XlYesNoGuess constant table.

Value Name Description
0 xlGuess Excel determines whether there is a header, and its location.Default value.
1 xlYes The entire range should not be sorted.
2 xlNo The entire range should be sorted.

Destination is an optional parameter. It contains variant type data. The destination range must be on the worksheet that contains the ListObjects collection specified by expression. New columns will be inserted at the Destination to fit the new list. Therefore, existing data will not be overwritten.

TableStyleName is an optional parameter. It contains string type data. Represents the Table Style.

Simple example to create Table in Excel

Here is a simple example macro statement to create table in Excel.

ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = "MyTable"

Example to create Table in Excel

Let us see another example by using object and create table in Excel. Here you can also set the name and style to the table, .

'VBA Create Table in Excel
Sub VBAF1_Create_Table_in_Excel()
    'Variable Declaration
    Dim tableListObj As ListObject
    Dim sTableName As String
    'Define Table Name
    sTableName = "MyTable10"
    'Create a Table
    Set tableListObj = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)
    tableListObj.Name = sTableName
    tableListObj.TableStyle = "TableStyleMedium1"
 End Sub

Note: The above VBA code macro creates a table in Excel Worksheet.
create Table in Excel

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays in Excel VBA Tables and ListObjects

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog

Leave a Comment