Martin Green's Mailbag
|   Home   |   Excel   |   Access   |   Word   |   Outlook   |   VBA   |   eBooks   |   Handouts   |   Site Index   |   RSS   |
Mailbag

Back to the Mailbag

 
Support this site!

Please make a small donation to help support this site.

Thank you.

Modifying Recorded Macros: A Universal Sort Macro for Excel

Published: 2 November 2018
Author: Martin Green
Screenshots: Access 2016, Windows 10
For Access Versions: 2007-2016

What's the Problem?

"I recorded a macro to sort some data. It works fine on the original data but when I try to use it on different sets of data it doesn't work."

Excel's Macro Recorder is a great tool for creating simple macros and for helping you learn VBA programming. Even now, after 20 years as a VBA developer, I occasionally use the Macro Recorder to remind me how to perform a particular action, to show me how Excel performs a task I've never had to automate before, or simply to save time writing the code myself. Then comes the job of modifying the recorded VBA code so that it will still work in the different circumstances in which it might be used.

NOTE: You might be wondering what is the point of creating a macro for a task as simple as sorting but remember, not all users will know how to sort data and, more likely, you will be incorporating a sort into a more complex macro that also carries out other tasks.

Sorting data in Excel is a good example of the macro recorder creating code that is highly specific to the circumstances in which it was recorded. You wouldn't think so because sorting is such a simple task. The user selects a cell in the column they want to sort the data by, then they simply give the command to Sort. The whole block of data gets sorted by the chosen column (Fig. 1).

Sorting data in Excel.
Fig. 1 Sorting data in Excel.

A simple task, but Excel has done a couple of things for you. It has assumed that you wanted to sort by the column in which the selected cell was located, and it has detected whether or not the data has a header row. Here is the code the Macro Recorder created (Listing 1) when I performed the sorting task shown above (Fig. 1).

Listing 1:

Sub Macro1()
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("C3"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
   
With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:E27")
        .Header = xlNo
        .MatchCase =
False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
   
End With
End Sub

Streamlining the Code

Recorded macros often include surplus code. They tend to record default values and there is usually an amount of repetition, so the first thing I like to do with a recorded macro is to remove what I don't need and try to streamline the code. You can see that the expression ActiveWorkbook.Worksheets("Sheet1").Sort is used twice at the beginning of the macro and later in a With Statement. I can incorporate the first two statements into the With Statement since they all refer to the same thing.

NOTE: When moving different commands into a With Statement make sure that they all refer to exactly the same thing. This may not be the case with, for example, a Selection which might change and refer to a different range of cells as the macro progresses.

Everything else needs to stay except SortMethod = xlPinYin which only applies when sorting Chinese characters so this statement can be removed unless you are doing that. Here is the tidied-up macro (Listing 2).

Listing 2:

Sub Macro1_Streamlined()
   
With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range("C3"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A2:E27")
        .Header = xlNo
        .MatchCase =
False
        .Orientation = xlTopToBottom
        .Apply
   
End With
End Sub

Generalizing the Macro

Having streamlined the macro the task remains to make it universal, so that it will work on any range of data. There are three statements which are specific to the data that the macro was recorded on. Each of these need to be generalized so that they will apply to the current data when the macro is run.

  • Worksheets("Sheet1").Sort can be changed to ActiveSheet.Sort

  • Key:=Range("C3") can be changed to Key:=Range(ActiveCell.Address)

  • SetRange Range("A2:E27") can be changed to SetRange Range(ActiveCell.CurrentRegion.Address)

Where I have used ActiveSheet and ActiveCell you might want to refer to a specific sheet or cell that suits the tasks that your macro is performing.

One item is slightly problematical. You can see in my recorded macro code that Excel seems to think there is no Header row, when my data clearly shows that there is a header and the macro sorted it correctly allowing for that. The clue is in the SetRange statement which specifies a range that excludes the first row of data. Excel has correctly identified that a Header is present then specified a range that does not include it. The CurrentRegion property I have used to define the range to be selected can't identify a Header so if the data is likely to have a Header then xlNo must be changed to xlYes. Here is the modified macro (Listing 3).

Listing 3

Sub Macro1_Generalized()
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range(ActiveCell.Address), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range(ActiveCell.CurrentRegion.Address)
        .Header = xlYes
        .MatchCase =
False
        .Orientation = xlTopToBottom
        .Apply
   
End With
End Sub

Job (almost) done, but I'm not too happy about assuming that there is a header row. If you are creating a macro for a particular task then you will probably know whether or not the data is likely to have a Header row. But what if you don't know? Maybe I could create a routine that can detect a Header automatically. Obviously it can be done because Excel can do just that. It isn't foolproof though, which is why the Advanced Search gives you the option to specify whether or not your data has headers.

I don't know how the built-in header detection works but my guess is that it compares the data type of the entries in the first row of data with that of those below. If any are different it is likely that the first row is a header. If they are all the same then it's possible that the first row is data and not a header. But rather than agonize over this, surely the easiest thing to do is simply ask the user. Like this (Listing 4) ...

Listing 4:

Sub Macro1_AskHeaders()
   
On Error Resume Next
   
Dim Response As VbMsgBoxResult
   
With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range(ActiveCell.Address), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range(ActiveCell.CurrentRegion.Address)
        Response = MsgBox("Does the data have a Header Row?", vbQuestion + vbYesNo, "Headings?")
       
If Response = vbYes Then .Header = xlYes Else .Header = xlNo
        .MatchCase =
False
        .Orientation = xlTopToBottom
        .Apply
   
End With
End Sub

Summary

Excel's Macro recorder is a great tool. Alone it can help you create useful time-saving macros, but the real power comes when you learn to generalize the macros it creates so they can be used in a range of different circumstances. This isn't always an easy task but with a little thought and some VBA programming skills you can create powerful macros that will save time, increase productivity and help avoid user errors.

In this example I took a simple recorded macro that sorted a range of cells. I removed unnecessary code and streamlined that which was needed and then changed the parts that referred to specific items so that the macro could perform the same task on any range of cells regardless of size. The resulting macro can, of course, be used alone but it can now be incorporated into a more complex macro where the range of cells to be sorted might vary.

Download Example Workbook

You can download a sample workbook containing the completed macros used in this article. To download the file right-click the icon or text link below and choose Save target as... and follow the instructions. The workbook is supplied in a *.zip folder. After downloading you should extract the file from the zip folder before attempting to use it. To do this right-click the zip file icon then choose Extract All... and follow the instructions.

Download Masterclass 1 Database SortVariableRange.zip [26KB]

^ top

 

  ©2000-2018 Martin Green martin@fontstuff.com All rights reserved