The VBA editor window is used to create and edit VBA program files. To create a VBA program, follow these steps:
1 Select Tools>Macro>Macros to invoke the Macro dialog box as shown in Figure 3.3.
2 Type in a meaningful macro name, such as: UpdateSales and click the Create button. You will see the VBE screen appear as shown in Figure 3.4.
Figure 3.3 The Macro dialog box
Note that the larger pane on the right-hand side appear in the VBE. This is almost blank apart from the inclusion of the Sub UpdateSales() and End Sub. This is the Editor window, and each window that is used is known as a module. A module is used to store the VBA program code.
Now type in the rest of the source code as shown in Listing 3.1. Be careful when entering the first two lines below the Sub UpdateSales(): these are comment lines text preceded by an apostrophe. Comments are ignored by the VBA program and are intended to help the programmer understand the code. They are coloured green in the VBA editor. You are free to insert as many comments as is required. Some tips for using comments in programs are given in the section on programming style (page 50).
Properties pane with Module 1 selected
Begin and end sub procedure statements Module 1 selected
Figure 3.4 The VBE screen showing a new module and the program template
Take note
The terms macro, procedure, sub VBA program and VBA code are used synonymously in practice.
VBA editor window
Take note
In the Visual Basic Editor, the words Sub and End Sub are displayed in dark blue. These are key words, reserved by VBA as system commands. The update sales() part appears in black. VBA uses different colours for different types of statements. Statements, which contain syntax errors lines that are grammatically incorrect will appear in red.
Listing 3.1 The sales update VBA macro 1 Sub updateSales()
2 'Update Sales VBA program Version 1.0 34 Worksheets("Weeklysales"). Select 5 ActiveSheet.Unprotect
6 Range("End_month_sales").Copy
7 Range("sales_to_date").PasteSpecial xlValues 8 Range("Week_sales").ClearContents
9 Range ("month_no") = Range ("month_no") + 1 10 ActiveSheet.Protect
11 End Sub
This is a similar example to the recorded Excel macro updateSalesRep in Chapter 2. The purpose of the example is to copy the range end_month_sales and paste the values into the range sales_to_date. The week_sales range that contains the week sales data during the month will then be cleared ready to receive the data for the next month. Unlike the recorded version, we will also alter the cell named month_no, so that it will be increased by 1 at the end of each month.
The window that contains the code is called a module. A module is a separate file that contains a collection of one or more macros. You can store each macro in a separate module or save them all in the same one. As a general rule, it is advisable to store related macros in the same module. For example, if you have a number of calculation macros in your workbook, then they should be stored in the same module. This should make the macro easier to
Do not include the line numbers in your code they are here simply to identify the lines in the text.
find when required for editing. The macros in the module are available throughout the application.
You can also see that the beginning of the procedure named UpdateSales.
Line 2 is a comment line. A comment begins with the symbol, or Rem (short for remark) and does not cause VBA to perform any action when the program is run. They are used to document programs and improve readability.
Line 3 is a blank line, inserted to enhance program readability (i.e. by separating the comments from the rest of the program statements). Its vital to learn to use proper program documentation style from the beginning.
Line 4 is the first command in the VBA program. It sets the active sheet to weeklysales, so that the correct sheet is manipulated by the macro.
The statement in line 5 will unprotect the worksheet so that changes can be made to it.
The statement in line 6 will copy the range end_month_sales into the Clipboard.
The statement in line 7 will then paste this range, by values, from the Clipboard to the range sales_to_date.
The statement in line 8 will clear the contents of the cell range week_sales.
The statement in line 9 will increment the contents of the month_no range.
The statement in line 10 will protect the sheet from inadvertent changes.
The statement in line 11 will end the program.
Take note
A macro is also known as a procedure. There are two types of procedures available in VBA. They are:
sub procedures and function procedures. Function procedures will be studied in Chapter 9. Every sub procedure written in VBA must begin with a Sub keyword followed by the name of the procedure and opening and closing parenthesis as shown in Listing 3.1, Line 1. Every sub procedure must end with an End Sub statement (see Listing 3.1 Line 11).