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: VB.NET code samples
• Working with ranges: VB.NET code examples

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)

• Part 1: Application and base objects
• Part 2: Customizing the Excel User Interface: What is and isn't customizable
• Part 3: Customizing Excel ribbons and toolbars
• Part 4: Customizing Excel main menu, context menus, and Backstage view
• Part 5: Creating custom task panes for Excel 2013 – 2003
• Part 6: Working with Excel workbooks and worksheets: VB.NET examples
• Part 7: Working with Excel cell values, formulas and formatting: C# samples
• Part 8: Working with Excel charts
• Part 10: Importing data from SQL databases and other sources to Excel
• Part 11: Working with Excel pivot tables: VB.NET code examples