How to sort a range

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

How to sort a range
 
Leon Lai Kan




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

I am studying this sample blog:
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 "Sort a range".

Here is the code as provided by the sample download: 
Private Sub SortRange(ByVal rangeName As String, ByVal sortByRange As String)
        Dim rangeToSort As Excel.Range
        'rangeToSort = ExcelApp.Range(rangeName).Select                'Original

        rangeToSort = CType(ExcelApp.Range(rangeName), Excel.Range)    'Option Strict On
        rangeToSort.Select()                                           'Option Strict On

        'I have set Option Strict On. So I modified the original code.

        rangeToSort.Sort(sortByRange, _
             Excel.XlSortOrder.xlAscending, , , , , , Excel.XlYesNoGuess.xlGuess)
        ' // The program crashes at the above line. No explanation except 
        '      "There is a problem with this formula... ???????????????????????????
      
        Marshal.ReleaseComObject(rangeToSort)
    End Sub



The sample does not come with buttons and button codes.
So, I wrote the foll. button code to call the above sub: 
 Private Sub SortARange_OnClick(ByVal sender As System.Object, _
      ByVal control As AddinExpress.MSO.IRibbonControl, _ 
      ByVal pressed As System.Boolean) Handles SortARange.OnClick

        Dim MySortField As String = "A1"      '// Sort column A ???
        Dim MyRangeName As String = "Sample"   '// the name of the range to be sorted
        SortRange(MyRangeName, MySortField)

    End Sub


when I run the program, it crashes at the line indicated.

red

Thanks
Leon
Posted 06 Mar, 2019 05:54:06 Top
Dmitry Kostochko


Add-in Express team


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

Please update the code of the SortRange() method as follows: 

    Private Sub SortRange(rangeName As String, sortByRange As String)
        Dim rangeToSort As Excel.Range
        Dim key1 As Excel.Range

        rangeToSort = ExcelApp.Range(rangeName)
        rangeToSort.Select()

        key1 = ExcelApp.Range(sortByRange)

        rangeToSort.Sort(key1, Excel.XlSortOrder.xlAscending,
            Orientation:=Excel.XlSortOrientation.xlSortColumns, Header:=Excel.XlYesNoGuess.xlGuess)

        Marshal.ReleaseComObject(rangeToSort)
        Marshal.ReleaseComObject(key1)
    End Sub


You can use it by passing a range address (or name) as the first argument and key range address as the second argument, for example: 

SortRange("A1:D10", "A1:A10")
Posted 06 Mar, 2019 09:17:41 Top
Leon Lai Kan




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

Thanks for your update.

It works perfectly!

Best Regards,
Leon
Posted 06 Mar, 2019 09:59:35 Top