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.

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

Where expression represents a ListObjects object.

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

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

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

XlListObjectHasHeaders is an optional parameter. It conatins 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 conatins 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 TableStyle.

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"

Eample 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 = "MyTable1"
    
    'Create a Table
    Set tableListObj = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)
    tableListObj.Name = sTableName
    tableListObj.TableStyle = "TableStyleMedium1"
    
 End Sub

Note:

Step by Step Instructions to use VBA macro code

Here are the instructions to use above macro in Visual basic editor.

  • Open Visual Basic Editor(VBE) by clicking Alt +F11
  • Go to code window by clicking F7
  • Copy above specified macro or procedure
  • Paste above copied code in code window
  • Run macro by clicking F5 or Run command
  • Check

Other Related References

You can also learn complete details like syntax, example and etc by clicking on the following buttons.

Tables Tutorial Tables Examples VBA Functions List

Leave a Comment

Your email address will not be published. Required fields are marked *