Ty Anderson

Working with Excel tables & ranges: VB.NET code samples

I like grids. They can be an Excel spreadsheet, a table, or graph paper. I think I like them because they make me feel organized. This is probably because utilizing them automatically enforces structure to my thinking. Believe it or not, sometimes I need the help.

Microsoft Excel, being a spreadsheet application, automatically brings structure to your thinking. You can build models to help you think through just about anything. You can organize your data and create lists of any imaginable type. In fact, most apps in the mobile app stores these days can easily be replaced by Excel files. Even games. Don’t believe? Excel is gamers delight.

But I digress. Today is all about Excel tables and ranges and how to do some basic automation of them.

Working with tables

When creating Excel files, when are you not working with tables? I’m not sure but the answer if probably “often”… we’ll get to this situation later when I discuss ranges. An Excel table is a contiguous range of cells. Excel makes life easier for a user by automatically including rows and columns in the table (if no space exists between the table and what is added). Life is easier for the developer because you call a table by name (as well its columns). Of course, there are more features but I don’t want to regurgitate what’s available here.

Let’s look at some code samples to learn some of the basics.

Create a table

You create a table by calling the Add method of the ListObjects collection. This collection resides under a Worksheet object. Therefore, you need to first reference the worksheet that will be the table’s home. You then add it.

Private Sub CreateTable(location As Excel.Range, tableName As String)
    Dim sheet As Excel.Worksheet
    Dim table As Excel.ListObject
 
    sheet = ExcelApp.ActiveSheet
    table = sheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, _
        location, , Excel.XlYesNoGuess.xlGuess)
    table.Name = tableName
 
    Marshal.ReleaseComObject(table)
    Marshal.ReleaseComObject(sheet)
End Sub

The CreateTable method accepts a Range and a String parameter. The range is the location for the table to be created. The string is the table’s name.

Insert a column or a row

If you have a table you want to automate, most likely you will want to add rows and columns to it. The InsertColumnOrRow method does both.

Private Sub InsertColumnOrRow(sheet As Excel.Worksheet, _
    tableName As String, rowOrColumn As String, Optional beforeRC As Integer = 0)
 
    Dim table As Excel.ListObject
    table = sheet.ListObjects(tableName)
 
    Select Case rowOrColumn
 
        Case "Column"
            'I'm making a big assumption the BeforeRC value will not case 
            'the function to choke

            If beforeRC > 0 Then
                table.ListColumns.Add(beforeRC)
            Else
                'Just insert to the right
                table.ListColumns.Add()
            End If
 
        Case "Row"
            If beforeRC > 0 Then
                'insert above row
                table.ListRows.Add(beforeRC)
            Else
                'Just insert at the bottom
                table.ListRows.Add()
            End If
        Case Else
    End Select
    Marshal.ReleaseComObject(table)
End Sub

The sheet parameter is the worksheet that contains the table. The tableName parameter speaks for itself. The method calls the table by name to gain a reference to it. It then uses the rowOrColumn parameter to determine whether to add a column or row. The beforeRC parameter specifies the row or column to insert before. If this parameter is missing, we insert at the end of the table (at the right for columns, at the bottom for rows).

Sort a table

To sort a table, you need to build a query string and then add this string to the SortFields collection. When you believe the sort is properly set (and the compiler agrees), can apply by calling the Apply method of the Sort object.

Private Sub SortTable(sheet As Excel.Worksheet, _
    tableName As String, sortyBy As Excel.Range)
 
    sheet.ListObjects(tableName).Sort.SortFields.Clear()
    sheet.ListObjects(tableName).Sort.SortFields.Add(sortyBy, _
        Excel.XlSortOn.xlSortOnValues)
    With sheet.ListObjects(tableName).Sort
        .Header = Excel.XlYesNoGuess.xlYes
        .MatchCase = False
        .SortMethod = Excel.XlSortMethod.xlPinYin
        .Apply()
    End With
End Sub

The sort objects are children of the ListObjects collection. There is lot of object traversing, so be careful and make sure you attempting to build the sort with the correct objects. A big hint is that if the sort objects do not display in Intellisense, you are doing it wrong. I speak from experience.

Filter a table

Excel provides users with incredibly useful controls that allow the user to quickly filter a table at whim. Developers don’t care about fancy controls. We like a good object model combined with a challenging business rule or two. This can really get the blood flowing.

If you encounter a need to automate table filtering… no sweat, use AutoFilter.

Private Sub FilterTable(sheet As Excel.Worksheet, _
    tableName As String, fieldName As String, filter As String)
 
    Dim table As Excel.ListObject
    table = sheet.ListObjects(tableName)
    table.Range.AutoFilter(fieldName, filter)
 
    Marshal.ReleaseComObject(table)
End Sub

To use AutoFilter, you need the table. This method accepts a table name as a parameter and then finds in the manner we recently covered. AutoFilter needs to know the field and the filter criteria. The field is an integer representing the column number to use for filter. The criteria is a string used as the filter.

Working with ranges

Ranges are like tables but with less structure. To user, they often resemble a table but we developers are smarter and wiser. We know they are different because they are different objects within the object model. Tables are ListObject objects (that sounds funny) and ranges are Range objects.

In the VB.NET code samples that follow, I have named ranges in mind.

Select a range

I like named ranges because I can call them directly… by their name. The SelectRange method uses the passed string to find the desired range in the Range collection.

Private Sub SelectRange(rangeName As String)
    Dim myRange As Excel.Range
    myRange = ExcelApp.Range(rangeName).Select
 
    Marshal.ReleaseComObject(myRange)
End Sub

This method works if you pass the name of a named range (e.g. “My super awesome named range”). It works if you specify the range using R1C1 notation (e.g. “B1:E29″).

Insert a column or a row

Just like with tables, adding columns and/or rows is a popular range-related automation activity.

Private Sub InsertRangeRowOrColumn(rangeName As String, rowOrColumn As String)
    Dim myRange As Excel.Range
    myRange = ExcelApp.Range(rangeName)
 
    Select Case rowOrColumn
        Case "Column"
            myRange.Insert(Excel.XlInsertShiftDirection.xlShiftToRight)
 
        Case "Row"
            myRange.Insert(Excel.XlInsertShiftDirection.xlShiftDown)
 
        Case Else
    End Select
    myRange.Select()
    Marshal.ReleaseComObject(myRange)
End Sub

This method finds the desired range and then adds a row or column as directed by the value of the rowOrColumn parameter.

Sort a range

To sort a range, you need to specify at least one key as well as the range to sort. In the SortRange method, rangeName is the Key1 and needs to be a string that serves as the filter criteria.

Private Sub SortRange(rangeName As String, sortByRange As String)
    Dim rangeToSort As Excel.Range
    rangeToSort = ExcelApp.Range(rangeName).Select
 
    rangeToSort.Sort(sortByRange, _
        Excel.XlSortOrder.xlAscending, , , , , , Excel.XlYesNoGuess.xlGuess)
 
    Marshal.ReleaseComObject(rangeToSort)
End Sub

In this sample the Sort method utilizes the string value in sortByRange as Key1. The string can something like “A1″… just tell it the range used to key the sort.

Filter a range

Filtering a range is similar to sorting one. You need a range to filter but then you also need the column to filter on as well as the value to use as the filter.

Private Sub FilterRange(rangeName As String, sheet As Excel.Worksheet, _
    filterField As Integer, filterCriteria As String)
 
    Dim myRange As Excel.Range
    myRange = sheet.Range(rangeName)
    myRange.AutoFilter(filterField, filterCriteria, , , True)
 
    Marshal.ReleaseComObject(myRange)
End Sub

The FitlerRange method uses the range object’s AutoFilter method to perform the filter. The filterField is an integer representing the range column to filter. The filterCriteria string contains the values to use as the filter.

Create a range (using an array)

I saved this one for last. While working with dynamic, say from a database, you might be tempted to build an array and then insert the array into an Excel range. This is perfectly reasonable and is easy to do if you know the potential bugaboo.

If you want to know all the details of this bugaboo, you can read about it later. The gist of it is this: you need to create at least two-dimensional array. This is true even if you only want to insert data into a single column. This is what CreateRangeWithArray does.

Private Sub CreateRangeWithArray(insertRange As Excel.Range)
 
    'We'll use a dynamically silly array (or is it silly-ly dynamic?)
    'But it is easy to imagine creating your own array dynamically

    Dim dataToInsert As Object(,) = New Object(5, 1) {}
 
    For i = 1 To 5
      dataToInsert(i, 0) = "Row " + i
    Next
 
    insertRange.Value2 = dataToInsert
End Sub

The sample method creates a 5 row, 2 column array. It then inserts 5 rows of data into it and inserts the array into a range. It sounds simple and maybe even obvious. But I’ve just saved a few hours of wondering “why doesn’t this work?!!” caused by building a 1-dimension array. Dmitry saved you time too because this sample was his idea.

*****

Okay, that’s all the time there is for today. These samples are a bit of Excel Tables and Ranges 101. Be careful out there when building solutions because clients will jump from the 100 level to the 401 level in the first 15 minutes of requirements gather. They can’t help it. Users mean well but they are shifty.

Available downloads:

This sample Excel add-in was developed using Add-in Express for Office and .net:

Excel Tables and Ranges add-in (VB.NET)

8 Comments

  • https://secure.gravatar.com/avatar/d22b7d1837d331212923c331273e592c?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G srikanth says:

    I tried using create table code:

    Sub calling()
    Set Excelobj = CreateObject(“Excel.Application”)
    Sheets(2).Select
    ActiveSheet.Cells(1, 1).Select
    ‘Call CreateTable(Excelobj.Sheets(2).Range(“A1″), “TestExecution”) ‘Err: Application Defined or Object defined error
    ‘Call CreateTable(Excelobj.Sheets(2).Range(“A1″), “TestExecution”) ‘Err: Application Defined or Object defined error
    Call CreateTable(ActiveCell, “TestExecution”) ‘this worked
    End Sub

    Private Sub CreateTable(location As Excel.Range, tableName As String)
    Dim sheet As Excel.Worksheet
    Dim table As Excel.ListObject

    ‘sheet = ExcelApp.ActiveSheet

    table = ActiveSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, _
    location, , Excel.XlYesNoGuess.xlGuess) ‘Err: Invalid use of property
    table.Name = tableName

    Marshal.ReleaseComObject (table)
    Marshal.ReleaseComObject (sheet)
    End Sub

    I am getting invalid use of property error, as mentioned in the above code, could you please help me fix this issue. Thank you.

  • https://secure.gravatar.com/avatar/5ffaf58d3c9ce5779c5a9e878dce141b?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Tom says:

    I would like to delete a given row from a table using VBA

    If I use the following Code it works
    [DeleteTest].Select
    I would like to Delete a row from a table

    Selection.ListObject.ListRows (120).Delete

    Is there anyway to run this where the 120 is inserted dynamically.
    I tried the following but it does not work. TestDeleteRow is a named range.
    It does insert the correct row number, but still does not execute
    Dim DelRow As String
    DelRow = [TestDeleteRow]
    [DeleteTest].Select
    Selection.ListObject.ListRows(DelRow).Delete

    Thanks

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    Hello srikanth,

    If you write this in VBA, you need to use the Set operator:

    Set table = …

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    Tom,

    Not sure that I understand your goals. How would you use this?

    I don’t think assigning a named range to a string variable makes sense.

  • https://secure.gravatar.com/avatar/a0c04c38923679b5d9a64f47a8910a82?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G loki says:

    hi, the code below gives me the following error at runtime “An exception of type ‘System.Runtime.InteropServices.COMException'”

    any suggestions please?

    Private Function WriteRange(long1 As Long, long2 As Long, string1 As String)
    Dim activeWorksheet As Excel.Worksheet = Globals.ThisAddIn.Application.ActiveSheet

    Dim range As Excel.Range
    range = activeWorksheet.Range(long1, [long2]).Select()
    range.Value = string1

    End Function
    it also highlights the Select function.

    thanks!

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    Hello Loki,

    The parameters accepted by the Worksheet.Range property are described at http://msdn.microsoft.com/en-us/library/office/ff836512%28v=office.15%29.aspx. You need to use activeWorksheet.Cells(long1, [long2]) instead. Note that it returns an Excel.Range while Range.Select() returns an object which isn’t Range; this is why range={…}.Select() fails.

  • https://secure.gravatar.com/avatar/b62a99ebece808d7c5ab3291dbb244aa?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Dharma Patil says:

    Hello Loki,

    Please guide me I wish to sort a excel range of data. below coded data. I tried by recording a Macro in excel. which generates a VBA code. It works but VBA is not directly usable in VB.NET 13

    Please guide me. Myself is not expert in Vb.Net but using this Vb13 to generate an industrial application.

    ‘Sort out the date data C col in ascending order excluding serial nos.
    Dim srt As Excel.Sort
    oRng = oSheet.Range(“B” & aRow, “F” & (MsfRow – 1))
    oRng.Select()
    srt = oSheet.Sort
    ‘Sort ascending
    srt.SortFields.Clear()
    srt.SortFields.Add(Key:=oRng.Range(“C” & aRow, “C” & (MsfRow – 1)), SortOn:=Excel.XlSortOn.xlSortOnValues, Order:=Excel.XlSortOrder.xlAscending, DataOption:=Excel.XlSortDataOption.xlSortNormal)
    With srt
    .SetRange(oRng)
    .Header = Excel.XlYesNoGuess.xlYes
    .MatchCase = False
    .Orientation = Excel.XlSortOrientation.xlSortColumns
    .SortMethod = Excel.XlSortMethod.xlPinYin
    .Apply()
    End With

    No error but not sorting any data. I will show here a Macro which works with excel sheet and selected data range.

    ActiveWorkbook.Worksheets(“Sheet3″).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(“Sheet3″).Sort.SortFields.Add Key:=Range(“C9:C37″) _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(“Sheet3″).Sort
    .SetRange Range(“B9:F37″)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

    Warm Regards,

    Dharma Patil, India
    M:00919325218518

  • https://secure.gravatar.com/avatar/c68f03884efb33fd95bcfbd9f1078c07?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Ty Anderson says:

    Hi Dharma,

    Macros serve as an imperfect start to your VB.NET coding efforts. But, due to the differences in the objects between ADX and VBA, a little bit of re-work of the VBA is required. I have provided a re-worked sample for you.


    Public Sub SortRange()
    Dim ws As Excel.Worksheet = Me.ExcelApp.ActiveWorkbook.Worksheets("Sheet3")
    Dim rg As Excel.Range = ExcelApp.Range("C9:C37")
    ws.Sort.SortFields.Clear()
    ws.Sort.SortFields.Add(rg, Excel.XlSortOn.xlSortOnValues, Excel.XlSortOrder.xlAscending, , Excel.XlSortDataOption.xlSortNormal)
    With ws.Sort
    .SetRange(ExcelApp.Range("B9:F37"))
    .Header = Excel.XlYesNoGuess.xlGuess
    .MatchCase = False
    .Orientation = Excel.XlSortOrientation.xlSortRows
    .SortMethod = Excel.XlSortMethod.xlPinYin
    .Apply()
    End With
    Marshal.ReleaseComObject(rg)
    Marshal.ReleaseComObject(ws)
    End Sub

    I have not tested it but it is a translation of the code you provided.

    Ty

Post a comment

Have any questions? Ask us right now!