Beyond Excel: VBA and Database Manipulation

Jan 21 2010   5:58PM GMT

Adding Sorting

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Another common requirement for reports is sorting.  While this can be done in SQL with an ORDER BY clause, I like to use Excel to sort because the desktop PC usually has a much higer CPU-Power-to-Number-of-Users ratio than a database server.  Today, we add sort columns to the Fields table.

Fields Table w/Sort

Fields Table w/Sort

 Once again, we need to add the columns to our Fields table, redefine it (using Ctrl-Shift-N), add a routine, and modify Macro1(). 

Function Sort_Data(sDataRange As String, sFieldRange As String) As Boolean
'   Sort_Data:          Sorts data based on settings in the field definition table
'   Parameters:         sDataRange  = Range containing data
'                       sFieldRange = range containing data's field definitions
'   Example:            bResult = Sort_Data("Data", "Fields")
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    Sort_Data = Failure         'Assume the Worst
    On Error GoTo ErrHandler
    Settings "Save"             'Save current settings
    Settings "Disable"          'Disable events, screen updates, and calculation
          
    Dim i As Integer            'Generic Counter
    Dim lColOrd As Long         'Sort Order Column in FieldRange (1st, 2nd, etc.)
    Dim lColHdg As Long         'Column Headings Column in FieldRange
    Dim lColSeq As Long         'Sort Seq. Column in FieldRange (Asc, Desc)
    Dim lRow As Long            'Row in FieldRange
    Dim sField() As String      'Sort Field
    Dim sSequence() As String   'Sort Sequence
   
    With Range(sFieldRange)
        ReDim sField(.Columns.Count - 2)
        ReDim sSequence(.Columns.Count - 2)
        lColOrd = FieldColumn("S.Ord", sFieldRange)
        lColHdg = FieldColumn("Heading", sFieldRange)
        lColSeq = FieldColumn("S.Seq", sFieldRange)
        For lRow = 2 To .Rows.Count
            If Val(.Cells(lRow, lColOrd)) > 0 Then
                sField(.Cells(lRow, lColOrd) - 1) = .Cells(lRow, lColHdg)
                sSequence(.Cells(lRow, lColOrd) - 1) = .Cells(lRow, lColSeq)
            End If
        Next
    End With
   
    With Range(sDataRange)
        Worksheets(.Worksheet.Name).Activate
        .Cells(2, .Column).Select
        For i = UBound(sField, 1) To 0 Step -1
            If sField(i) > "" Then
                Selection.Sort _
                    Key1:=sField(i), _
                    Order1:=IIf(UCase(Left(sSequence(i), 1)) = "A", _
                                xlAscending, xlDescending), _
                    DataOption1:=xlSortTextAsNumbers, _
                    Header:=xlYes, _
                    OrderCustom:=1, _
                    MatchCase:=False, _
                    Orientation:=xlTopToBottom
            End If
        Next i
    End With
    Sort_Data = Success
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Sort_Data - Error#" & Err.Number & vbCrLf & Err.Description, _
            vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error Resume Next
    Settings "Restore"     'Restore previous settings
    On Error GoTo 0
End Function

Add the Sort_Data routine (highlighed in red) to Macro1() as shown:

If NameExists("Data") Then
    If Range("Data").Rows.Count > 1 Then
        Freeze_Pane "Data", "Fields"
        Sort_Data "Data", "Fields"
        Pivot_Template
    End If
End If

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: