VBA Create Static Table in Excel

VBA Create Static Table in Excel. Let us see to create table in Excel using VBA ListObject. Here we use static method to create table. Defining constant range. We have different table styles to create. in In this tutorial we have clearly explained example and step by instructions.

Example to Create Static Table in Excel VBA

Let us see the example to create table in Excel. We have declared two variables called tableListObj & TblRng. Here tableListObj if ListObject object. It belongs to listobjects collection. TblRng is a range to define static range to create static table. Here we have defined range as ‘A1:D10‘. You can change this according to your requirement. We have used ListObjects collection to create table. I have specified table name as ‘MyStaticTable‘. The table style has specified as ‘TableStyleMedium16‘.

'Create Static Table in VBA
Sub VBAF1_Create_Static_Table()
    
    'Variable Declaration
    Dim tableListObj As ListObject
    Dim TblRng As Range
    
    'Sheet Name
    With Sheets("Table")
                
        'Static Range to create table
        Set TblRng = .Range("A1:D10")
        
        'Create table at above specified range
        Set tableListObj = .ListObjects.Add(xlSrcRange, TblRng, , xlYes)
        
        'Specifying table name
        tableListObj.Name = "MyStaticTable"
        
        'Specify table style
        tableListObj.TableStyle = "TableStyleMedium16"
    End With
    
    'Display message on the screen
    MsgBox "Table has created successfully.", vbInformation, "VBAF1"
    
End Sub

Note: Before running above macro, create sheet called ‘Table’, otherwise change sheet name in the above specified macro.

Table Name: You can find the table name by following the below specified instructions.

1. You can select the table range.
2. Go to Table Design from the Excel ribbon.
3. Go to Properties group.
4. You can see the selected name of the table under Table Name in the textbox.
5. You can also edit table name manually in the specified box and press enter.

Output: Here is the following output screenshot for the above macro. The output message displays on the system screen.

Instructions to Use Example Macro Code

You can follow step by step instructions to run above macro example VBA code.

• 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 in the visual basic editor(VBE)
• Run macro by clicking F5 or Run command
• You can see the new table MyStaticTable in the specified sheet.

Other Related References

Click on the following links for related reference articles. It helps for your reference.

VBA Tables Tables Examples