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.

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). It's 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.

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).

0 0

Post a comment