Public Sub Test1()

End Sub

Public Sub Test1()
    MsgBox "This is the Test1 procedure running"
End Sub

Sub DisplayWelcome()
    MsgBox "Welcome"
End Sub

Function AddThree(OriginalValue As Long)
    AddThree = OriginalValue + 3
End Function

Private Sub Test1()
    MsgBox "This is the Test1 procedure running"
End Sub

Private Function AddThree(OriginalValue As Long)
    AddThree = OriginalValue + 3
End Function

Public Sub Test1()
    MsgBox "This is the Test1 procedure running"
End Sub

Public Function AddThree(OriginalValue As Long)
    AddThree = OriginalValue + 3
End Function

Sub Test1()
    MsgBox "This is the Test1 procedure running"
End Sub

Function AddThree(OriginalValue As Long)
    AddThree = OriginalValue + 3
End Function

Function ConeSurface(radius, height)
    Const Pi = 3.14159
    coneBase = Pi * radius ^ 2
    coneCirc = 2 * Pi * radius
    coneSide = Sqr(radius ^ 2 + height ^ 2) * coneCirc / 2
    ConeSurface = coneBase + coneSide
End Function

totalSurface = ConeSurface(3, 11) + 2 * ScoopSurface(3)

TestTools.DisplayWelcome

If necessary, you can also specify the project that the procedure resides in. The following example runs a Sub procedure named "DisplayWelcome" that's stored in a module named "TestTools" in a project named "TestDocument."

TestDocument.TestTools.DisplayWelcome

Note that the name of the project you specify is the project's code name, not the name of the document the project is associated with. You can check and modify the project's code name in the space to the right of (Name) in the Properties window for the project. To see the Properties window, select the project in the Project Explorer, and then click Properties Window on the View menu. You can also change the code name of a project by typing a new name in the Project Name box on the General tab in the Project Properties dialog box. You display this dialog box by right­clicking the project in the Project Explorer, and then clicking Properties on the shortcut menu (the command appears on the shortcut menu preceded by the current name of the project). For more information about the options in the Project Properties dialog box, click a tab and press F1.

Tip   If you want to be able to call a procedure from other modules in the same project but not from other projects, declare the procedure as public, but make the module private to the project by adding the Option Private Module statement to the (Declarations) section of the module.

If you want to be able to call procedures in one project from another project, there must be a reference from the project containing the calling code to the project containing the called code. To create a reference to a project, use the References dialog box (Tools menu).

Note   If you get an error when you try to create a reference from one project to another one, make sure that the project you're trying to reference doesn't have the same code name as the other project. (Multiple projects in an application may be given the same default code name, such as "Project" in Word or "VBAProject" in Microsoft Excel.) To check a project's code name, click the project name in the Project Explorer, and then click and then click Properties Window on the View menu. The text to the right of (Name) in the Properties window is the project's code name. To change the code name for a project, select the current code name and then type a new one. Keep in mind that you cannot have circular references — that is, if you have a reference to project A from project B, you cannot have a reference from project B to project A.

Passing Arguments to a Procedure

If your procedure needs information to perform its task that it cannot get from the context in which it's being run, you can pass that information to the procedure in the form of arguments. To indicate that a given procedure takes arguments, include an argument list between the parentheses that follow the procedure name in the procedure declaration. The argument list can contain multiple argument declarations, separated by commas.

When you declare an argument, you can specify the data type of the argument by using the As keyword (whether or not the procedure can change the argument's value by using the ByVal and ByRef keywords), and you can specify whether the argument is required or optional by using the Optional keyword. For more information about a specific keyword, see the appropriate topic in Help. For more information about the available data types in Visual Basic, see "Visual Basic Data Types" later in this chapter.

The following example shows the declaration line of a Sub procedure that takes three arguments.

Sub UpdateRecord(ByVal custId As Long, ByRef custName As String, _
    Optional custRepeat As Boolean)

Dim newId As Long
Dim newName As String
Dim newRepeat As Boolean

newId = 3452
newName = "Mary Boyd"
newRepeat = True
UpdateRecord newId, newName, newRepeat

Note that the name of the variable you pass from the calling procedure doesn't have to match the name of the argument declared in the called procedure.

Using Named Arguments

If either a procedure you create or a built­in function, statement, or method takes more than one optional argument, you may want to pass arguments to it by name rather than by position.

For example, the Open method of the Microsoft Excel Workbooks object, which opens a workbook, takes 13 arguments. If you want to write code that opens the workbook Book2.xls and adds it to the list of recently used files, you could write the code shown in the following example.

Workbooks.Open "book2.xls", , , , , , , , , , , , True

However, this code is difficult to write correctly without introducing bugs, because you have to count the number of commas to insert between the arguments. The code is also very difficult to read, and it gives no clues about what the arguments represent. The following example shows a better way to write this code.

Workbooks.Open FileName:="book2.xls", AddToMru:=True

Because every argument has a name, you can use the name and the := operator to assign a value to an argument. When you use named arguments, you don't have to remember the order of the arguments. For instance, the preceding code could have been written with the order of the arguments reversed, as in the following example.

Workbooks.Open AddToMru:=True, FileName:="book2.xls"

You can also use named arguments with the procedures you create. Visual Basic automatically associates argument names with their corresponding procedures. For instance, assume that you've created a FormatList procedure that takes two required arguments and two optional arguments, as shown in the following declaration.

Sub FormatList(startRow As Integer, startCol As Integer, _
	Optional redText, Optional sortList)

The DoList procedure in the following example uses named arguments to call the FormatList procedure.

Sub DoList()
	FormatList redText:=True, startCol:=2, startRow:=2
End Sub

The arguments are now out of order, and one of the optional arguments was omitted.

Note   Using named arguments doesn't negate the need to enter required arguments.

Writing Code to Respond to Events

Certain objects in the Office 97 applications recognize a predefined set of events, which can be triggered either by the system or by the user. Examples of events recognized by objects in Office include the Open and Close events for Word documents; the Open, BeforePrint, BeforeSave, and BeforeClose events for Microsoft Excel workbooks; the Calculate and SelectionChange events for Microsoft Excel worksheets; the Click, Initialize, and Terminate events for custom dialog boxes; and the Click, GotFocus, and LostFocus events for ActiveX controls. For detailed information about the events available in Microsoft Excel and Word, see Chapter 7, "Microsoft Word Objects," and Chapter 4, "Microsoft Excel Objects." For detailed information about using custom dialog boxes and ActiveX controls, see Chapter 12, "ActiveX Controls and Dialog Boxes."

You can control how your application responds to a recognized event by writing code in the Code window for the object. Every time an event occurs, the code, or event procedure, associated with that event runs. For instance, if you write a procedure that's associated with the Open event for a Word document, every time that document is opened, the procedure automatically runs.

Where Event Code Is Stored

An event procedure is stored in the document, workbook, worksheet, slide, or UserForm where the event can be triggered. For example, the procedure that runs when you calculate the worksheet named "Last Quarter" in the workbook named "Sales" would be stored in the Last Quarter worksheet in the project associated with the Sales workbook. To view the code in a document, workbook, worksheet, slide, or UserForm, click the object in the Project Explorer, and then click the View Code button to open the Code window.

Note   PowerPoint presentations and slides don't recognize events. Therefore, unless you can place ActiveX controls (which recognize events) on a PowerPoint slide, there can be no event procedures associated with the slide, and you won't see the slide in the Project Explorer. For more information about adding ActiveX controls to documents, see Chapter 12, "ActiveX Controls and Dialog Boxes."

How Event Procedures Are Named

The name of an event procedure is the name of the object that recognizes the event — such as "Document," "Worksheet," "UserForm," or "CommandButton1" — followed by an underscore (_), followed by the name of the event that the procedure runs in response to — such as "Open," "Calculate," or "Click." For example, the procedure that runs when you open a Word document is Document_Open.

Note   Whereas the name of an event procedure for most objects is linked to the class name (such as Document, Worksheet, or UserForm), the name of an event procedure for an ActiveX control is linked to the control's code name — either the default name or a name you assign. If you change the code name of a control after writing event procedures, you must rename your procedures to match; otherwise, they will never run in response to the events for that control.

To view an event procedure, open the Code window for the document, workbook, worksheet, slide, or UserForm where the event can be triggered; select the name of the object that recognizes the event (this can be either the object where the event can be triggered itself or an ActiveX control contained in the object) in the Object box; and then select the name of the specific event you want to respond to in the Procedure box.

Note   If you want a procedure to be associated with a specific document, workbook, worksheet, slide, or custom dialog box, but not with a specific event — for instance, if you want to be able to call the procedure from several different event procedures — store it in the (General) section of the document, workbook, worksheet, or slide module.

Timesaving Tools for Writing Code

Many keywords used in Visual Basic are extremely long and difficult to type without making mistakes. To reduce the time you spend typing and the number of typing errors in your code, Visual Basic includes tools that complete words and build expressions for you.

When you've typed enough letters for Visual Basic to recognize a word, press CTRL+SPACE or click the Complete Word button on the Edit toolbar to have Visual Basic automatically complete the word for you.

In the Options dialog box (Tools menu), you can turn on tools that automatically do the following after you enter a line of code: verify correct syntax, display information, and give you appropriate options to choose from at each stage of building your expression.

You can also use the List Properties/Methods, List Constants, Quick Info, Parameter Info, and Complete Word buttons on the Edit toolbar to get help completing a word or an expression at any time. For more information about using a specific tool to help you complete words and statements, see the Help topic for that button or option. For information about using these tools to build statements using Office properties and methods, see Chapter 2, "Understanding Object Models."

Writing Code That's Easy to Read and Navigate

There are many ways to make your Visual Basic code more readable, as described in the following paragraphs.

Add comments to your code by using an apostrophe ('). At run time, Visual Basic ignores everything between the apostrophe and the end of the line. Each line in the following example includes a comment.

'This procedure calculates the burdened cost
'of the specified employee
Dim baseSalary As Currency          'salary not including benefits or bonuses
baseSalary = employeeLevel * 2500   'employeeLevel passed as argument

Set myField = ActiveDocument.Fields.Add(Range:=Selection.Range, Type:=wdFieldDate)

Set myField = ActiveDocument.Fields.Add(Range:=Selection.Range, _
    Type:=wdFieldDate)

MsgBox "This is a string that I have to break up " & _
    "so that I can continue it on another line"

You cannot follow a line­continuation character with a comment on the same line.

Use indentation levels to show logical levels in your code. Press TAB or click the Indent button on the Edit toolbar to shift each line in a selected block of code one indentation level to the right. Lines within the selection retain their indentation levels relative to one another. Press SHIFT+TAB or click the Outdent button on the Edit toolbar to shift each line in a selected block of code one indentation level to the right.

Use bookmarks to mark key areas in your code that you want to be able to move between quickly without having to navigate manually. Add a bookmark to a line by clicking the Toggle Bookmark button on the Edit toolbar. A blue, rounded rectangle appears in the margin to indicate a bookmark. To navigate between bookmarks, click the Next Bookmark or Previous Bookmark button on the Edit toolbar.

For more information about a specific feature, see the appropriate topic in Help.

Document Projects vs. Template Projects

If you're writing procedures that are specifically designed to be run on a single document, workbook, or presentation, you can store the code in the project associated with that document, workbook, or presentation.

If, however, you want to be able to get to a procedure from more than one document, workbook, or presentation, you can store the code in the project associated with a particular template.

When you apply a template to a Word document, the template is attached to the Word document. All procedures in the attached template are available to the document. If you change the code in a template, the changed code is available for use in all documents based on that template. If you want a procedure to be available to all Word documents, regardless of which templates they're based on, store the procedure in Normal.dot, which is automatically referenced by all documents.

When you apply a template to a workbook or a presentation, any code in the template project is copied to the project for the workbook or presentation. Unlike Word, Microsoft Excel and PowerPoint don't attach the template to the workbook, so changes you make to the code in the template project won't be reflected in the workbook or presentation projects after the template has been applied. If you want a procedure to be available to all Microsoft Excel workbooks, regardless of which templates they're based on, store the procedure in Personal.xls.

Class Modules

You use class modules to create your own custom objects when you want to create encapsulated, reusable units of code. The Sub and Function procedures you define in a class module become methods of the custom object. The properties you define with the Property Get, Property Let, and Property Set statements become properties of the custom object. For more information about creating and using custom classes, see Mastering Office 97 Development, a CD­ROM product available from Microsoft.

If you've added class modules to your project, either by clicking Class Module on the Insert menu or by copying modules from another project, you'll see a Class Modules folder under the project name in the Project Explorer. You get to the code for a particular class by clicking the class name and then clicking the View Code button at the top of the Project Explorer.