Understanding Instructions Modules and Procedures

In Chapter 1, you learned that Microsoft Excel's macro recorder creates a series of instructions that are the exact equivalents of the actions you perform. These instructions are automatically placed in a workbook sheet called a module. Excel stores the module in a module folder located in the current workbook, a new workbook, or in the Personal Macro Workbook. To review the recorded macro code, you must activate the Visual Basic Editor window and double-click the module folder in the Project Explorer window. When the module sheet opens up in the Code window, you are finally able to analyze your procedure's code.

A procedure contains all of the recorded instructions. Each line in a procedure is an instruction. There are various types of instructions, such as keywords, operators, or calls to other procedures. Keywords words carry a special meaning in Visual Basic. In Chapter 1, you learned the most popular VBA keywords—the words Sub and End Sub, which begin and end a procedure. By default, keywords appear in blue. Because keywords are reserved by Visual Basic, don't use these words for other purposes.

In addition to keywords, Visual Basic instructions can contain operators. There are four types of operators: arithmetic, string concatenation, logical, and comparison. Operators allow you to combine, join, and manipulate certain values. For example, the division operator (/) can be used to calculate the percentage of the total. In this book, you get many opportunities to see how operators are used in VBA procedures.

Another type of a Visual Basic instruction is a call to a procedure. Calls to procedures allow you to quickly jump to other procedures and execute other sets of instructions. Is it hard to picture this? Let's take the WhatsIn-ACell macro you recorded in Chapter 1. Suppose you also want to include the statement you entered in the FormulasOnOff macro in this procedure.

How can you do this? You could copy the required line of code from one procedure to another. However, there is an easier and quicker way. Instead of copying instructions between procedures, you can call the procedure by specifying its name. For example, if you want to process the FormulasOnOff macro instructions before Visual Basic encounters the instruction MsgBox "All actions have been performed", you can add the following line of code:

FormulasOnOff

When Visual Basic reaches this line, it will jump right into the FormulasOn-Off procedure and execute its code. Next, it will return to the WhatsInACell macro to continue on with the remaining code, stopping when it reaches the End Sub keywords.

Before you can try out this example, you must learn how to assign names to VBA projects and modules, as well as how to call procedures from different projects.

0 0

Post a comment