Viewing the Visual Basic Module

Macros are recorded in a programming language called Visual Basic for Applications (VBA). VBA is very similar to the programming language Visual Basic. Recall that Excel translates recorded actions into VBA.

Overview of Visual Basic, VBA, and VBScript

The distinction between Visual Basic and Visual Basic for Applications (VBA) has caused some confusion for programmers. There are three major Visual Basic programming products that can be purchased: Visual Basic, Visual Basic for Applications (VBA), and Visual Basic Script (VBScript). All three languages use similar commands and syntax, but each serves a different functional purpose.

Visual Basic is for developing stand-alone programs, while VBA is the programming language in the Office suite of programs (Excel, Access, Word etc.). It is pseudo-compiled, meaning that it cannot be used to create separate, stand-alone programs. VBA must always be run from within the program that created it. VBScript is the newest dialect of Visual Basic, and it competes with JavaScript in the dynamic HTML/Internet area.

The code that is generated when a macro is recorded is Visual Basic and VBA code since the language syntax is nearly identical. The only distinction is that the macros we recorded can only be run from Excel.

In order to get a better idea of how macros work, let's look at the VBA-coded version of the DataSorter macro we just created.

Opening the Visual Basic Editor

The Visual Basic Editor can be enabled through the Macro dialog box on the View tab.

Step1. To switch to the View tab, on the Ribbon,

Click the View tab

Step2. To access the macro commands, on the Ribbon,

Click

NOTE for MacOS Users: To access the macro commands, on the Ribbon,
Click.

We see a list of all our macros.

Step3. Ensure that the DataSorter macro is highlighted in the dialog box.

Step4. To edit the DataSorter macro, in the dialog box,

Click

A new window opens for Microsoft Visual Basic, a Microsoft application that allows VBA code to be viewed and edited.

There are three sub-windows in the Microsoft Visual Basic window, but the relevant window shows the actual code itself, on the right side of the Microsoft Visual Basic window.

You will see the entire DataSorter macro code:

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

 
   Range("A10").Select
    ActiveWorkbook.Worksheets("2003").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

Some careful reading will reveal the error in our code on the the highlighted lines in the code above. We can see three places where the macro recorder referenced our worksheet explicitly:

ActiveWorkbook.Worksheets("2003")

Exiting code block.

In Excel, macros do exactly what you record, nothing more, nothing less. In this case, we are asking Excel to perform these tasks only on the 2003 worksheet. This is why when we tried to run the macro on the 2004 worksheet, we saw an error message telling us that our project would be reset.

Let's explore the different parts of the macro code in more detail.

Comments

At the top of the module, you see the macro title and description written in green with a single quotation mark (') before each line. This combination of color and coding indicates that these are comments, which we created when we recorded the macro. Comments are for documentation purposes only, they are not executed. This information can be changed without altering the operation of the macro. Note that the main comment is the description we entered when recording the macro. This illustrates why it is important to give a good description when recording a macro; it helps make the recorded code easier to read.

Sub Statements

Above the comments, you see Sub DataSorter (), and beneath the comments is a list of steps, ending with End Sub. All macros begin and end with Sub statements. This tells Visual Basic that this is a sub procedure as opposed to Excel's main procedures, which control program execution.

Statements

The list of statements which lie between the beginning and ending sub statements are the steps we followed in creating the DataSorter macro.

Let's take a quick look at the steps to see how our actions were coded.

The first step we took was to select cell A10. This action is represented in VBA by the following statement:

Range("A10").Select

Exiting code block.

Visual Basic refers to the different components of a statement as objects, properties, and methods.

In the above example:

• The currently active worksheet is the Object, which is not explicitly stated.
• Range is a property of the current worksheet.
• Select is the method being applied to the Range property.
• A10 is the value of the specific cell being selected with the Select Method.

In other words, this command is selecting a new active cell by applying the Select Method to the current Worksheet Object's Range property. Though the remainder of the macro looks a little more complicated, it follows the same kind of syntax with a few extra properties and methods thrown in.