Format as table style in Excel Add-in code example

Format as table

Excel provides numerous predefined table styles that we can use to quickly format a table. If the predefined table styles don’t meet your need, you can create and apply a custom table style. In this article I would guide you, how you can create format as table style using VSTO C# and VBA.

Let’s put some data into a table and build a source that we can automate using VSTO code to form a table by defining style. We will have to use ListObjects collection here. Following is the sample data I have created to make our code working:

VBA code example

Public Sub ListObjectExample()
    'sheet object
    Dim oSheet As Worksheet
    'Bind sheet
    Set oSheet = ActiveSheet
    'range object
    Dim oRange As Range
    'bind selection
    Set oRange = Selection
    'list object
    Dim oListObject As ListObject
    'create list
    Set oListObject = oSheet.ListObjects.Add(xlSrcRange, oRange, , xlYes)
    'Name list
    oListObject.Name = "vbaoverallTable"
    'set style
    oListObject.TableStyle = "TableStyleLight9"
    
     'Cleanup
    If Not oSheet Is Nothing Then
        Set oSheet = Nothing
    End If
    If Not oRange Is Nothing Then
        Set oRange = Nothing
    End If
    If Not oListObject Is Nothing Then
        Set oListObject = Nothing
    End If
End Sub

C# code example

private void btnFormattingListObject_Click(object sender, RibbonControlEventArgs e)
{
	//capture user selection
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;
	
	//get active sheet reference
	excel.Worksheet oWorksheet = Globals.ThisAddIn.Application.ActiveSheet;

	//create list
	excel.ListObject myListObject= oWorksheet.ListObjects.Add(excel.XlListObjectSourceType.xlSrcRange, oRange, System.Reflection.Missing.Value,
		excel.XlYesNoGuess.xlYes);

	//Give it name
	myListObject.Name = "MyTestTable";

	//Give style
	myListObject.TableStyle = "TableStyleLight9";

}

Run the code >> select range on which you want to apply style. I have chosen “TableStyleLight9“, you can make your own by recording a macro in Excel. Now click on the button on the Ribbon which will trigger code and your selection would turn as below:

Happy coding!!!

Next: apply, create or delete cell style in Excel using VSTO C#

Share on Facebook

Tweet