Switching from the Microsoft Excel 4.0 Macro Language to Visual Basic Programming

Microsoft Corporation

January 1999

Summary: This article introduces users of the Microsoft® Excel 4.0 Macro Language to Visual Basic® programming. You'll learn how Visual Basic differs from the Microsoft Excel 4.0 Macro Language, how you can continue using your existing Excel 4.0 macros, and where to find more information about Visual Basic. (5 printed pages)

Information for Users of Microsoft Excel 4.0 Macros

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 or later makes it easier for you to learn programming in other Microsoft Office applications (Microsoft Access, Microsoft Word, and Microsoft PowerPoint®), as well as Microsoft Project and other Microsoft applications that use Visual Basic. You can also control these other applications easily with Visual Basic code.

This section guides experienced users of Excel 4.0 macros to information about learning and using Visual Basic. For more detailed information, see the Help topics and the chapters in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press®, 1999) that are cross-referenced in this section.

Acting Directly on Objects in Visual Basic

In Microsoft Excel 4.0, macros follow the "select, then do" order of actions. With Visual Basic, however, 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 Excel 4.0, you must 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 simply by 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 Excel and Visual Basic, see Chapter 5, "Working with Office Applications," in the Microsoft Office 2000/Visual Basic Programmer's Guide.

Variables: More Powerful Than Names

To store a value as a variable in Excel 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, only to the procedures in a given module, or to 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 3, "Writing Solid Code," in the Microsoft Office 2000/Visual Basic Programmer's Guide.

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, because If is also a keyword in Visual Basic. You can use the Application qualifier to run an 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 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 Excel version or its Visual Basic version is the LOG function, because both function names are spelled the same way. The 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 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 Microsoft Excel Visual Basic Reference 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 8, "Error Handling and Debugging," in the Microsoft Office 2000/Visual Basic Programmer's Guide.

Visual Basic Equivalents for Common Macro Functions

The easiest way to see the Visual Basic equivalents for common macro functions and 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, Excel adds Visual Basic statements to your module.

No matter how you write programs in 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 the Microsoft Office 2000/Visual Basic Programmer's Guide for information about how to accomplish these tasks in Visual Basic.

Creating Custom Commands and Dialog Boxes Using Visual Basic

Microsoft Excel 97 and later versions include tools for creating custom menus, commands, and dialog boxes. For more information about creating custom commands and dialog boxes using Visual Basic, see Chapter 6, "Working with Shared Office Components" in the Microsoft Office 2000/Visual Basic Programmer's Guide.

Creating Custom Commands

To create a custom menu or command in Excel 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 Excel 97 and later, you use the Customize dialog box to assign custom commands and menus to menu bars. You can also use the command bars object model. For more information about command bars, see Chapter 6, "Working with Shared Office Components," in the Microsoft Office 2000/Visual Basic Programmer's Guide.

Displaying Built-In Dialog Boxes

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

In Excel 97 and later, you use the Dialogs method with the identifier of the dialog box you want displayed to display built-in dialog boxes while running a procedure. 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 Excel 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 an 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 an 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 Excel 97 and later, you create custom dialog boxes 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.

--------------------------------------------

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, this paper should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication. This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.

Microsoft, ActiveX, Microsoft Press, PowerPoint, and Visual Basic are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.