A P P E N D I X   A Microsoft Office 97/Visual Basic Programmer's Guide

Switching from the Microsoft Excel 4.0 Macro Language

Contents

This appendix introduces users of the Microsoft Excel 4.0 Macro Language to Visual Basic programming. In this appendix, you'll learn how Visual Basic differs from the Microsoft Excel 4.0 Macro Language, how you can continue using your existing Microsoft Excel 4.0 macros, and where to find more information about Visual Basic.

Visual Basic is a true programming language that features variables with scoping, an integrated editor, and enhanced dialog box tools and debugging tools. Learning Visual Basic in Microsoft Excel 97 makes it easier for you to learn programming in other Microsoft Office 97 applications (Microsoft Access, Word, and PowerPoint) as well as Microsoft Project and other Microsoft applications that use Visual Basic. You can also control these other applications easily in your Visual Basic code.

Information for Users of Microsoft Excel 4.0 Macros

This section guides experienced users of Microsoft Excel 4.0 macros to information about learning and using Visual Basic. For more detailed information, see the chapters and Help topics that are cross­referenced in this section.

Acting Directly on Objects in Visual Basic

In Microsoft Excel version 4.0, macros follow the "select, then do" order of actions that pertains to all of Microsoft Excel. With Visual Basic, you don't need to select an object you want your procedure to change; you can change the object directly.

For example, to make a range of text bold in Microsoft Excel version 4.0, you have to first select the range with the SELECT function before changing the format of the text with the FORMAT.FONT function. In Visual Basic, you make a range of text bold by just setting the Bold property of the range to True. The following example applies bold formatting to cells C1:G5 on Sheet1. 

Sub MakeSectionBold()
	Worksheets("Sheet1").Range("C1:G5").Font.Bold = True
End Sub

Note that you can use Visual Basic to change an object (the range C1:G5,in this case) directly, without first selecting it or canceling the current selection. For more information about how to change cells, sheets, and other objects in Microsoft Excel with Visual Basic, see Chapter 4, "Microsoft Excel Objects."

Variables: More Powerful Than Names

To store a value as a variable in Microsoft Excel version 4.0, you'd typically store the value in a name. In Visual Basic, you'd assign the value to a variable instead.

Variables are much more flexible than names. You can make variables available to all procedures, to only the procedures in a given module, or to only a single procedure. You can control the type of data that can be stored in a variable, and you can even create variables that store a combination of data types of your choice.

In Visual Basic, you can also define constants to hold static (constant) values that you can refer to repeatedly. For more information about variables and constants in Visual Basic, see Chapter 1, "Programming Basics."

Using Worksheet Functions in a Procedure

There are many worksheet functions that you can use directly in Visual Basic procedures; the IF function is one exception, though, given that If is also a keyword in Visual Basic. You can use the Application qualifier to run a Microsoft Excel worksheet function rather than a Visual Basic function. The following example causes a "Sub or Function not defined" error because it doesn't identify ACOS as a worksheet function. 

Sub MissingObject()
	x = Acos(-1)
End Sub

The following example successfully uses the Microsoft Excel worksheet function ACOS because the code first refers to the Application object. 

Sub ReturnArccosine()
	x = Application.Acos(-1)
End Sub

The only worksheet function that requires you to explicitly specify that you're referring to either the function's Microsoft Excel version or its Visual Basic version is the LOG function, because both function names are spelled the same way. The Microsoft Excel LOG function returns the logarithm of a specified number to whatever base you indicate. The Visual Basic Log function, on the other hand, returns the natural logarithm of a specified number.

Using Your Existing Macros in a Procedure

You can include your existing macros in new Visual Basic procedures by using the Run method. When you debug Microsoft Excel 4.0 macros as part of your Visual Basic procedures, the Visual Basic debugger steps into your macros as if they were written in Visual Basic. Your macros can return information to a procedure by using the RETURN macro function.

For more information and an example of the Run method, see "Run Method" in Help.

New Tools to Make Debugging Easy

There are numerous tools in Visual Basic to help you debug your code. Visual Basic debugging functionality includes breakpoints, break expressions, watch expressions, stepping through code one statement or one procedure at a time, and displaying the values of variables and properties. Visual Basic also includes special debugging functionality, such as the "edit­and­continue" feature, setting the next statement to run, and procedure testing while the application is in break mode.

For more information about the debugging capabilities of Visual Basic, see Chapter 14, "Debugging and Error Handling."

Visual Basic Equivalents for Common Macro Functions

The easiest way to see the Visual Basic equivalents for common macro functions and Microsoft Excel commands is to use the macro recorder to record macros in Visual Basic. You can arrange the windows on your desktop so that one window shows your Visual Basic module and the other one shows the worksheet or chart you're working on while you're recording a macro. As you work, Microsoft Excel adds Visual Basic statements to your module.

No matter how you write your programs in Microsoft Excel, there are common tasks you'll want to accomplish, such as referring to ranges, controlling how macros run, accessing data in other applications, getting information about workbooks and objects, and creating procedures that run in response to certain events. The following table shows you where to look in this book for information about how to accomplish these tasks with Visual Basic.

For information about See this chapter
Referring to cells and ranges on worksheets Chapter 4, "Microsoft Excel Objects"
Controlling the flow of a macro Chapter 1, "Programming Basics"
Accessing data in other applications Chapter 11, "Data Access Objects"
Getting information about objects in Microsoft Excel Chapter 4, "Microsoft Excel Objects"
Running procedures in response to events Chapter 4, "Microsoft Excel Objects"

Top
Creating Custom Commands and Dialog Boxes Using Visual Basic

Microsoft Excel 97 includes tools for creating custom menus, commands, and dialog boxes. For more information about creating custom commands and dialog boxes using Visual Basic, see Chapter 8, "Menus and Toolbars," and Chapter 12, "ActiveX Controls and Dialog Boxes."

Creating Custom Commands

To create a custom menu or command in Microsoft Excel version 4.0, you first create a menu or command table. You then use the macro function ADD.MENU or ADD.COMMAND to place your custom menu or command on a menu bar or a menu.

To create a custom menu or command in Microsoft Excel 97, you use the Customize dialog box to assign custom commands and menus to menu bars. For more information about using the Customize dialog box, see Chapter 8, "Menus and Toolbars."

Displaying Built­in Dialog Boxes

In Microsoft Excel version 4.0, to display built­in dialog boxes while running a macro, you use the question­mark form of the macro function corresponding to the dialog box. For example, the DEFINE.STYLE? macro function displays the dialog box in which you define worksheet styles.

In Microsoft Excel 97, to display built­in dialog boxes while running a procedure, you use the Dialogs method with the identifier of the dialog box you want displayed. The following example displays the Open dialog box (File menu). 

Sub OpenFile()
	Application.Dialogs(xlDialogOpen).Show
End Sub
Creating and Displaying Custom Dialog Boxes

To create a custom dialog box in Microsoft Excel version 4.0, you use the Dialog Editor to generate a dialog box definition you place on a macro sheet. You then use the DIALOG.BOX macro function to display your dialog box.

You can use the DialogBox method in your Visual Basic procedures to run a Microsoft Excel 4.0 custom dialog box. The following example uses the DialogBox method to display such a dialog box and then tests the result. The variable DialogRange refers to the range (on a Microsoft Excel 4.0 macro sheet) that contains the dialog­box definition table. 

Result = DialogRange.DialogBox
If Not Result Then
	' User canceled the dialog box
Else
	' Result is position number of chosen control
End If

In Microsoft Excel 97, you create custom dialogs by adding ActiveX controls to forms, or UserForms, in the Visual Basic Editor. To create a custom dialog box, you must create a UserForm to contain controls, add whatever controls you want to the UserForm, set properties for the controls, and write code that responds to form and control events. You use the Show method in a Visual Basic procedure to display your custom dialog box. For more information about creating dialog boxes in Microsoft Excel 97, see Chapter 12, "ActiveX Controls and Dialog Boxes."