VBA Create Table in Excel |Tutorial |Examples | Instructions

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.

Table of Contents:

Objective
Syntax to create Table in Excel VBA
Simple example to create Table in Excel
Example to create Table in Excel
Instructions to Run VBA Macro Code
Other Useful Resources

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.