Questions about a Blog Article

Add-in Express™ Support Service
That's what is more important than anything else

Questions about a Blog Article
Working with Excel tables & ranges: VB.NET code samples 
Leon Lai Kan




Posts: 200
 Joined: 2018-12-20
Hi,

I am studying this blog sample:
Working with Excel tables & ranges: VB.NET code samples
https://www.add-in-express.com/creating-addins-blog/2013/10/24/excel-tables-ranges-vbnet/

In particular, I am studying the section "Filter a Table".

Here is the SUB provided by the sample: 
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)  '//Error points to here: filter

        Marshal.ReleaseComObject(table)
    End Sub


To see the code in action, I created a ribbon button with the following code: 
 Private Sub FilterAtable_OnClick(ByVal sender As System.Object, _
    ByVal control As AddinExpress.MSO.IRibbonControl, _
    ByVal pressed As System.Boolean) Handles FilterAtable.OnClick


        Dim MySheet As Excel.Worksheet = TryCast(ExcelApp.ActiveSheet, Excel.Worksheet)
        Dim MytableName As String = "Table1"
        Dim MyFieldName As String = "Item"
        Dim MyFilter As String = "A"  '//My table shows A, B, C, D in successive rows
        FilterTable(MySheet, MytableName, MyFieldName, MyFilter)  

End Sub




When I run the program, it crashes and the error points to "filter" in the 1st SUB 
table.Range.AutoFilter(fieldName, filter)  '//Error points to here: filter

Error Msg: "_Autofilter method of Range class failed."


red

In the Visual Studio Object Browser, I see that the Function Autofilter has the following optional parameters:

Autofilter( [Field As Object],
[Criteria1 As Object],
[Operator As ...],
[Criteria2 As Object],
[VisibleDropDown As Object] )
As Object


Thanks
Leon
Posted 05 Mar, 2019 01:53:00 Top
Dmitry Kostochko


Add-in Express team


Posts: 2887
 Joined: 2004-04-05
Hi Leon,

Thank you for the detailed description.

I think it is a bug in the FilterTable() method. It uses the Range.AutoFilter() method that requires not a field name but the integer offset of the field on which you want to base the filter:
https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.range.autofilter?view=excel-pia

Please try to call the method as follows: 

Private Sub FilterAtable_OnClick(ByVal sender As System.Object, _ 
    ByVal control As AddinExpress.MSO.IRibbonControl, _ 
    ByVal pressed As System.Boolean) Handles FilterAtable.OnClick 
 
 
        Dim MySheet As Excel.Worksheet = TryCast(ExcelApp.ActiveSheet, Excel.Worksheet) 
        Dim MytableName As String = "Table1" 
        'Dim MyFieldName As String = "Item" 
        Dim MyFieldName As String = "1" 
        Dim MyFilter As String = "A"  '//My table shows A, B, C, D in successive rows 
        FilterTable(MySheet, MytableName, MyFieldName, MyFilter)   
End Sub


Meanwhile we will review the code and update the blog post and example.
Posted 05 Mar, 2019 10:02:44 Top
Leon Lai Kan




Posts: 200
 Joined: 2018-12-20
Hi, Dmitry

Now the code works perfectly!

No need to review your code. The error is in MY code (the button code).
The sample does not provide ribbon button codes.

Thanks a lot and Best Regards,

Leon
Posted 05 Mar, 2019 11:09:34 Top