Editing a Macro

Our original macro isn't very scalable. This is because Excel references the worksheet we were working on by name. In the next section, we'll see what we can do to recognize this problem as well as repair it.

Fixing the DataSorter Macro

Although we don't know Visual Basic for Applications, we can make some simple changes to our macro to keep it from resetting the 2003 worksheet every time we run the macro. After examining the code we just recorded, we see that a worksheet can be referred to as ActiveWorksheet as well as by its longer name (ActiveWorkbook.Worksheets("sheet_name")). Our DataSorter macro refers to the 2003 sheet by name:

ActiveWorkbook.Worksheets("2003")

Exiting code block.

We can solve our current problem by replacing this code with:

ActiveSheet

Exiting code block.

Making this simple change will allow us to run the macro on any worksheet in any workbook without resetting the 2003 worksheet every time.

Step1. To select the text to replace,

Press & Drag the first occurrence of

ActiveWorkbook.Worksheets("2003")

Step2. To replace the code, type:

ActiveSheet

Step3. Repeat steps 1 and 2 every time the problematic code appears (shown in the highlighted lines in the code below):

Sub DataSorter()
'
' DataSorter Macro
' Sort data and return subtotals by Expense type.
'

'
    Range("A10").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("2003").Sort.SortFields.Add Key:=Range("B11:B256") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("2003").Sort
        .SetRange Range("A10:D256")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

Exiting code block

Any macro can be edited in the Visual Basic window or by copying and pasting code from another macro.

Step4. Save the document.

Recording a New Macro

Looking back at our workbook, it would be nice if we could make our macro only show us the subtotals for each category. We didn't think of this while we were recording our macro, so we have to add code to what Excel already created for us.

Since we haven't learned to program in Visual Basic for Applications, we will generate additional code by recording a macro. We will then add that code to our current macro by copying and pasting the recorded code.

Step1. To return to Excel, on the toolbar,

Click

NOTE: You can also move back to Excel from within the Visual Basic application by pressing Alt key+F11 key.

NOTE for MacOS Users: You can also move back to Excel from within the Visual Basic application by using the Window menu on the Menu bar to select the worksheet.

You should be back in the 2004 Worksheet. We need to switch to the 2003 worksheet where our data has already been manipulated.

Step2. To switch to the 2003 worksheet,

Click the 2003 worksheet tab

The 2003 worksheet appears.

Step3. To begin recording a new macro, in the Status bar,

Click

NOTE for MacOS Users: To begin recording a new macro, in the Status bar,
Click.

You see the Macro dialog box again. This time, we will accept the default name (Macro#) since we will only be using it for a short time.

Step4. To set the macro to record into the This Workbook, if necessary,

Click, Click This Workbook

We don't need a description for this macro because it is only temporary. As soon as we move the code out of it, we will delete the macro.

Step5. To begin recording,

Click

The recorder console appears on the worksheet, and the status bar displays the word "Recording," indicating that Excel is recording your actions.

Step6. To reveal only the subtotals, at the top left of the worksheet,

Click

You see the subtotals for the expense types and the grand total. All the rest of the data is hidden. These totals were added when we ran the subtotal function when recording our macro. We can see that some of our columns need to be resized to allow our data to fit. We will add the code to run AutoFit when we return to the VBA editor.

We're done with this part of our macro, so let's stop recording.

Step7. To stop recording, in the Status bar,

Click

Our macro is finished, so let's move the code.

Moving Code

We now have a new macro which collapses the subtotals. Let's take a look at its code.

Step1. To return to the module, press:

Alt key+F11 key

NOTE for MacOS Users: You can also move back to Excel from within the Visual Basic application by using the Window menu on the Menu bar to select the worksheet.

You now see code for the new macro under the code for the DataSorter macro.

The new macro has the following lines of code:

ActiveSheet.Outline.ShowLevels RowLevels:=2

Exiting code block.

This Visual Basic statement tells Excel to act on the currently open sheet within the data outline in order to display the subtotals only (level 2). This is the code we want to make part of the DataSorter macro.

Viewing Multiple Macros in a Single Module

All macros in this workbook are shown in this Module 1 sheet. There can be one or more module sheets, each containing one or more macros or functions, and additional module sheets can be created and added to a worksheet. The main reason for using multiple modules is to help you keep things organized.

We will need only the Module1 sheet for our workbook macros. Since it is the only Module sheet we are using, all our macros are automatically recorded into this module. This could get a little unwieldy if we had a lot of macros in this one module, so it's useful to know how to easily move from one macro to another in a Module window.

Using the Procedure Drop-Down List

The Procedure text field and drop-down list are located in the top right corner of the Visual Basic window. Different parts of a Module window can be displayed from the drop-down list.

NOTE for MacOS Users: The Procedure Drop-Down list does not exist on MacOS.

1. To see the different components of this module window,

Click

You see a list of three items: (Declarations), DataSorter, and Macro#. The Declarations section is used to declare variables for the entire module. DataSorter and Macro# are the names of the two macros in this module.

Step2. To move into another procedure, in the Procedure drop-down list,

Click DataSorter

The cursor moves into the Sub DataSorter statement at the top.

Step3. To move the cursor back to Macro#, in the Procedure drop-down list,

Click, Click Macro#

The cursor moves into Macro#.

NOTE: In a small module like this, you can move to different macros by scrolling and clicking, but the drop-down list is another good way to move to any macro you want, especially in a long module with many macros.

Copying and Pasting the Code

Now that we've seen how to move from one macro to another, let's copy and paste the code we just recorded into the DataSorter macro.

Step1. To select the code to copy, in Macro#,

Press & Drag the uncommented code, Control key+c

Now that the code is copied, let's position our cursor inside the DataSorter macro's code.

Step2. To position the cursor,

Click to the left of the EndSub statement at the end of the DataSorter macro

Let's create a new line.

Step3. To create space for the code, press:

Enter, Up Arrow key

The cursor should now be at the beginning of a blank line, just before the EndSub statement in the DataSorter macro.

Step4. To paste the code, press:

Control key+v

The DataSorter macro now includes the code to collapse the subtotals, pasted code highlighted below:

Sub DataSorter()
'
' DataSorter Macro
' Sort data and return subtotals by Expense type.
'
 
'
    Range("A10").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("B11:B256") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A10:D256")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), _
            Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub

Exiting code block.

We can now remove the Macro# code from the code module.

Step5. To delete the Macro# subroutine,

Press & Drag from Sub Macro#() to End Sub,
press: Delete key

The pasted code will be executed at the end of the DataSorter macro. Let's add the code to run AutoFit at the end of our DataSorter macro.

Step6. To create a new line before the end of the macro,

Click to the left of the EndSub statement at the end of the DataSorter macro, press: Enter, Up Arrow key

Now that we have a new line, let's add the code to run AutoFit.

Step7. To add the code to run AutoFit, in the blank line we just created, type:

Application.Run "AutoFit"

Exiting code block.

The code for this macro will look like this, added code highlighted below:

Sub DataSorter()
'
' DataSorter Macro
' Sort data and return subtotals by Expense type.
'
 
'
    Range("A10").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("B11:B256") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A10:D256")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    Application.Run "AutoFit"
End Sub

Exiting code block.

Step8. Save macroex.xlsm.

Making a Macro Scalable

Scalability is a term used to describe how a chunk of code handles differing amounts of data. Currently, our macro will only work with values in the cells 11 - 256. If our data were to expand, any values outside cell 256 would not be included when our macro runs. The fix to this is simple, we will remove the cell number references in the code but keep the letters. For example, range A10:D256 would become A:D. This allows our code to extend past cell 256 and still be included when the macro is executed.

There are only two spots where the code needs to be changed; the range B11:B256 needs to be B:B and the range A11:D256 needs to become A:D.

Let's make these changes now.

Step1. To make the changes, remove the cell numbers from the code in the highlighted lines below:

Sub DataSorter()
'
' DataSorter Macro
' Sort data and return subtotals by Expense type.
'

'
    Range("A10").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("B11:B256") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A10:D256")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), _
                Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

Exiting code block

When you're finished, the DataSorter Macro should read:

Sub DataSorter()
'
' DataSorter Macro
' Sort data and return subtotals by Expense type.
'

'
    Range("A10").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("B:B") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A:D")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    Application.Run "AutoFit"
End Sub

Exiting code block.

We are done with the VBA editor and our macro is now ready to be tested. Let's close the VBA editor and test our macro.

Closing the VBA Editor

We don't have to save the work we've done in the VBA editor because it is stored in the Excel workbook. As long as we save the workbook, our macro work will be saved.

Step1. To close the VBA editor, in the upper right corner,

Click

The VBA editor closes and we are returned to Excel. Let's save our workbook.

Step2. To save the workbook, on the Quick Access Toolbar,

Click

Our workbook is saved.

Testing the New Macro

Now that we have revised the DataSorter macro, let's test it.

Step1. To test the modified macro on the 2004 Sheet, at the bottom of the workbook,

Click the 2004 worksheet tab

The 2007 worksheet appears.

Step2. To run the macro, on the Ribbon,

Click, Click DataSorter, Click

NOTE for MacOS Users: To run the macro, on the Ribbon,
Click, Click DataSorter, Click.

You see the collapsed subtotals displayed.

Step3. Repeat steps 2 and 3 for worksheets 2005 and 2006.

All of our data is now in good shape.

Quitting Excel

This brings us to the end of our content. Let's go ahead and close Excel.

Step1. To close Excel,

Click

NOTE for MacOS Users: To close Excel, on the Menu bar, Click Excel, Click Quit Excel

You might be prompted to save the open files.

Step2. If you are prompted to save macroex.xlsm,

Click

You should be returned to the Desktop.