The VBA Editor

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

Sheet 1 Worksheet ^

Alphabetic Categorized

Sheet 1 Worksheet ^

Alphabetic Categorized

(Name)

Sheetl

Display Page Breaks

False

DisplayR.ightToLeft

False-

Enable AutoF ¡Iter

False

EnableCalc illation

True

EnableOutlining

False

EnablePivotTable

False

EnableSelection

0 - xlMoRestrictions

Name

Weeklysales

5c roll Area

5tandardWidth

8.43

Visible

-1 - xlSheetVisible

Macro

Macro name:

updateSalesI

RM

A

Cancel

SfceplRto: |

•Edit

Create

zi

Delete;

Macros in: | All Open Workbooks ▼]

Description —

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

¡Microsoft Visual Basic - Bookl - [Modulel (Code)]

File Edit View insert Format Debug Run Tools Add-Ins Window Help

.JflJxj

¡Microsoft Visual Basic - Bookl - [Modulel (Code)]

File Edit View insert Format Debug Run Tools Add-Ins Window Help

.JflJxj

Properties pane with Module 1 selected

Figure 3.4 The VBE screen showing a new module and the program template

Properties pane with Module 1 selected

Figure 3.4 The VBE screen showing a new module and the program template

The terms macro, procedure, sub VBA program and VBA code are used synonymously in practice.

1 Sub updateSales()

2 'Update Sales VBA program Version 1.0

4 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

Do not include the line numbers in your code -they are here simply to identify the lines in the text.

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

0 0

Post a comment