Пример макроса: Автоматизированная сортировка данных

Исходный пример на VBA
Sub SortBySelectedColumn()  '' e21
    'Create three variables to capture the target column selected and the maximum column and row of _
    '   the tabular data
    Dim Col As Integer, RCol As Long, RRow As Long
    
    'Check that the user has selected the header row – row 1 otherwise exit sub
    If Selection.Row <> 1 Then Exit Sub
    
    'Capture the maximum rows in the tabular data range using the "UsedRange" object
    RCol = ActiveSheet.UsedRange.Columns.Count
    
    'Capture the maximum columns in the tabular data range using the "UsedRange" object
    RRow = ActiveSheet.UsedRange.Rows.Count
    
    'Check that the user has not selected a column outside of the tabular data range
    If Selection.Column > RCol Then Exit Sub
    
    'Capture the column that the user has selected
    Col = Selection.Column
    
    'Clear away previous sort parameters
    ActiveSheet.Sort.SortFields.Clear
    
    'Sort the tabular range as defined by maximum rows and columns from the "UsedRange" object
    'Sort the tabular data using the selected by the user column as the sort key
    ActiveSheet.Range(Cells(1, 1), Cells(RRow, RCol)).Sort Key1:=Cells(1, Col), Header:=xlYes
    
    'Select cell A1 – this is to ensure that the user is not left in edit mode after the sort is _
    '   completed
    ActiveSheet.Range("A1").Select
End Sub
JavaScript Р7
(function() {
    var oSheet = Api.GetSheet("sort21");    
    var oCell = oSheet.GetActiveCell();
    if (oCell.Row != 1) {
        return;
    }    
    var RCol = oSheet.GetUsedRange().GetCols().Count;
    var RRow = oSheet.GetUsedRange().GetRows().Count;
    if (oCell.Col > RCol) {
        return;
    }    
    var Column = oCell.Col;
    // Function "GetRangeByNumber()" use arguments in range (0..count-1)
    oSheet.GetRange(oSheet.GetRangeByNumber(1, 0), 
                    oSheet.GetRangeByNumber(RRow-1, RCol-1)).SetSort(oSheet.GetRangeByNumber(0, Column-1), "xlAscending");    
    oSheet.GetRange("A1").Select();
})();
Поддержка слушателей курса
"Основы Java Script для Р7"