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

I am studying this sample blog:
Working with Excel tables & ranges: VB.NET code samples
Add-in Express blog

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... ???????????????????????????
    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.

Could you help me find where the problem lies?

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

Add-in Express team

Posts: 2841
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)

        key1 = ExcelApp.Range(sortByRange)

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

    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,
Posted 06 Mar, 2019 09:59:35 Top