Understanding Office Code: Macros and Procedures | Accessing and Analyzing Data with Microsoft Excel (Bpg-Other)

Microsoft data analysis solutions do not need to be complex, nor do they require a lot of effort to deploy. In many cases, a macro (a self-contained piece of code) can automate a data analysis task that involves several steps. For example, you could use macros to automate data analysis tasks such as the following:

Sort and filter lists of records in Excel or Access.
Insert standard Excel worksheet functions or Analysis ToolPak functions.
Conditionally format Excel worksheet cells.
Create Excel charts, PivotTable reports, or PivotChart reports from lists of records.
Run Access queries.
Display Access data tables or reports.
Create Office Web Components on Web pages and connect them to a data source.
Analyze OLAP data with Microsoft Data Analyzer views.

Open and save XML data in Excel.

Note

The code descriptions and examples in this chapter use the Visual Basic family of programming languages, which are the primary programming languages used by Office solution developers. Complete coverage of the Visual Basic family of programming languages is outside the scope of this chapter. For more information about Visual Basic, see the Microsoft Developer Network (MSDN) Visual Basic Web site at http://msdn.microsoft.com/vbasic. For more information on learning Visual Basic for Applications, see the MSDN Office Developer Center at http://msdn.microsoft.com/office. For more information on learning VBScript, see the MSDN Windows Script Developer Center at http://msdn.microsoft.com/scripting.

Each macro begins with one procedure. When you instruct an Office application to run a macro, the application starts with the first line of code contained within the macro’s corresponding procedure.

If you’re unsure what a procedure is, imagine performing a simple addition problem on a hand-held calculator, adding 3 to 5, for example. You first press the button for the number 3, then the addition symbol (+), and the number 5. When you press the equals sign (=), the calculator displays 8, as you’d expect. By following steps like this, you execute, or run, the procedure 3 + 5 = 8.

We can express a procedure such as this in symbolic terms as

intFirst + intSecond = AdditionResult

where intFirst represents the integer (a number without fractions) value 3, intSecond represents the integer value 5, and AdditionResult represents the integer value 8.

We can express this simple procedure in VBA code as follows:

Public Function AdditionResult(ByVal intFirst As Integer, _ ByVal intSecond As Integer) As Integer AdditionResult = intFirst + intSecond End Function

In programming terms, the parts of this procedure are defined as follows:

The values intFirst and intSecond are the procedure’s arguments or input parameters.
The As Integer keyword that follows the values intFirst and intSecond refers to the arguments’ input type.
The As Integer keyword that follows the final parenthesis indicates the procedure’s return type.
The AdditionResult statement before the keyword End Function is the procedure’s return value.

A few additional items to note:

The Public keyword signifies the procedure’s scope. Scope determines in what context a procedure can be executed. In this case, the Public keyword means that the procedure can be executed from other procedures in this code module or from other code modules within the same workbook or database, for example.
The AdditionResult portion following the Function keyword signifies the procedure’s name. You can refer to this entire portion of code (or code block) as “the AdditionResult procedure.”
The ByVal keyword indicates that the procedure is not modifying the actual values of intFirst or intSecond in any way. In this example, the AdditionResult procedure is using the values intFirst and intSecond to determine what the return value of AdditionResult will be. If you wanted to modify the actual values of intFirst and intSecond, you would use the ByRef keyword and write code that actually modifies the values of intFirst and intSecond.
The space-underscore ( _) symbol at the end of the first line (following the code ByVal intFirst As Integer) is a line continuation character. This character is used for readability; it signifies that code on this line and the next line should be treated as a single line of code.
The Function keyword means that the procedure can return a value to any other procedure that calls, or refers to, it. There are two types of procedures: subroutines and functions. A subroutine does not return a value (although it can declare arguments) and is signified by the keywords Sub and End Sub.
The keyword End Function signifies the end of the function.

Your Turn

Now that you understand the basics of how procedures work, practice creating and running a simple procedure that calls the AdditionResult function.

Start Excel.
On the Tools menu, point to Macro and then click Macros.
In the Macro Name box, type CallAdditionResult.
In the Macros In box, select This Workbook.
Click Create. The Visual Basic Editor appears, and you’ll see the following code:
```
Sub CallAdditionResult() End Sub
```

Edit the code so that it looks like this:

Sub CallAdditionResult() Dim intFirstNumber As Integer Dim intSecondNumber As Integer intFirstNumber = InputBox(Prompt:="Type the first integer.") intSecondNumber = InputBox(Prompt:="Type the second integer.") MsgBox Prompt:=intFirstNumber & " + " & _ intSecondNumber & " = " & AdditionResult _ (intFirst:=intFirstNumber, intSecond:=intSecondNumber) End Sub Public Function AdditionResult(ByVal intFirst As Integer, _ ByVal intSecond As Integer) As Integer AdditionResult = intFirst + intSecond End Function

Click anywhere on the screen between the code Sub CallAdditionResult() and End Sub.
On the Run menu, click Run Sub/UserForm.
When the first input box appears, type 3 and then click OK.
When the second input box appears, type 5 and then click OK. The message 3 + 5 = 8 appears.

Caution

You will not be able to run the CallAdditionResult subroutine if your computer’s Office security settings prohibit it. For example, if your Office security settings are set to High, the CallAdditionResult subroutine will not run. If your Office security settings are set to Medium, the CallAdditionResult subroutine will run only if you enable macros when prompted by Office. For more information on Office security settings, see the online help included with the application you are using.

Here’s how the code you just wrote works:

VBA starts running the macro at the line Sub CallAdditionResult().
The two Dim statements instruct VBA to create two locations in computer memory to store values. The first memory location is called intFirstNumber; the second memory location is called intSecondNumber. The two memory locations will be big enough to hold integer values.
The lines intFirstNumber = InputBox(Prompt:=“Type the first integer.”) and intSecondNumber = InputBox(Prompt:=“Type the second integer.”) are interpreted by VBA as follows:
1. Display an input box with the label Type the first integer.
2. After the user clicks OK, store in the memory location called intFirstNumber whatever the user has typed in the box.
3. Display an input box with the label Type the second integer.
4. After the user clicks OK, store in the memory location called intSecondNumber whatever the user has typed in the box.
The line MsgBox Prompt:=intFirstNumber & “ + ” & intSecondNumber & “ = ” & AdditionResult(intFirst:=intFirstNumber, intSecond:=intSecondNumber) displays a message box with a label that is constructed as follows:
1. Display the value that is stored in the memory location called intFirstNumber.
2. Display the plus symbol (+).
3. Display the value that is stored in the memory location called intSecondNumber.
4. Display the equals symbol (=).
5. Display the return value of the AdditionResult function.
The value of the AdditionResult procedure is determined by passing the value that is stored in the memory location called intFirstNumber, followed by the value that is stored in the memory location called intSecondNumber. The AdditionResult procedure adds the values stored in the memory locations intFirstNumber and intSecondNumber (in our example, 3 and 5) and returns the result (8).