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 workbooks and worksheets: VB.NET examples. 
Leon Lai Kan




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

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/#_Working_with_ranges

nb: I wrongly typed the title of this message, but cannot correct it :(



There is a SUB to sort an Excel Table. Here it is: 
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


I have created a ribbon button to run the above sub when clicked.
Here is my code: 
Private Sub SortTbl_OnClick(ByVal sender As System.Object, _
                             ByVal control As AddinExpress.MSO.IRibbonControl, _ 
                             ByVal pressed As System.Boolean) Handles SortTbl.OnClick

        Dim Mysheet As Excel.Worksheet = TryCast(ExcelApp.ActiveSheet, Excel.Worksheet)
        Dim MytableName As String = "Table1"
        Dim sortyBy As Excel.Range = TryCast("Table1[[#All], [Item]]", Excel.Range)  
        'HOW TO DIM sortyBy?

        SortTable(Mysheet, MytableName, sortyBy)

    End Sub


My code does not work. The problem lies in the line "Dim sortyBy ... "

I shall apply the add-in to a table with 3 headings:
(1) Item (sort field)
(2) Qty
(3) Price
and with a few rows of data. I wish to sort the table using "Item" as the key.

red


Thanks

Leon
Posted 01 Mar, 2019 05:49:54 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Hello Leon,

Leon Lai Kan writes:
How would you declare \"sortyBy\" ?


sortyBy is declared correctly. Is is defined incorrectly: you cast a string to Excel.Range. That cast cannot be completed correctly. Actually, TryCast returns Nothing if the cast fails; see also https://stackoverflow.com/questions/2703585/casting-datatypes-with-directcast-ctype-trycast.

I've used the Macro Recorder while creating a test table and sorting it. The code lines (this is in VBA, not in VB.NET) that sorts the table is: 

    ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table2").Sort.SortFields.Add2 _
        Key:=Range("Table2[[#All],[b]]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal



Andrei Smolin
Add-in Express Team Leader
Posted 01 Mar, 2019 08:56:32 Top
Leon Lai Kan




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

Thanks a lot for your reply.

I have also tried using the Macro Recorder as you did, and used the generated code as a guide.

I also searched on the net.

But all my attempts to convert the VBA code to VB.NET failed.

Indeed I must use an Excel.Range to pass to sortyBy.
I don't know how to do that. We cannot convert a string to an Excel Range.

I note that when I sort a TABLE manually, using the Excel dialog box, I need simply specify the header (e.g. Item) as the sort field. We don't put a range. I suppose this is done by Excel internally.

So, how could we modify this line to make it work in VB.NET 
Dim sortyBy As Excel.Range = TryCast("Table1[[#All], [Item]]", Excel.Range)





Thanks
Leon
Posted 01 Mar, 2019 09:18:37 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Try to use Range("Table2[[#All],[Item]]"), not TryCast("Table1[[#All], [Item]]", Excel.Range).


Andrei Smolin
Add-in Express Team Leader
Posted 01 Mar, 2019 09:30:13 Top
Leon Lai Kan




Posts: 200
 Joined: 2018-12-20
I tried:
Dim sortyBy As Excel.Range = red("Table1[[#All],[Item]]")

Error: 'red' is not declared.
Posted 01 Mar, 2019 09:47:44 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Ah, ExcelApp.Range("Table2[[#All],[Item]]")


Andrei Smolin
Add-in Express Team Leader
Posted 01 Mar, 2019 09:54:14 Top
Leon Lai Kan




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

Your code works perfectly.

As soon as I click my button, the data is sorted correctly! Like magic!

Best Regards,
Leon
Posted 01 Mar, 2019 10:03:39 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Great!

Have a nice weekend.


Andrei Smolin
Add-in Express Team Leader
Posted 01 Mar, 2019 10:34:38 Top