Created: March 20, 1992
ABSTRACT
This article describes some of the issues you may need to consider when converting macro applications from the MicrosoftÒ Excel spreadsheet with business graphics and database version 2.x to Microsoft Excel version 3.0.
Issue: CLOSE() now closes the active, running macro sheet.
In Microsoft Excel version 2.x, a macro error occurs when the CLOSE function attempts to close the macro sheet containing the running macro. In Microsoft Excel version 3.0, the CLOSE function successfully closes the macro sheet containing the running macro.
This poses a conversion problem if you’ve written macros based on the Microsoft Excel version 2.x limitation. For example, you may have a macro that loops through a list of open documents to be closed, and the macro has ERROR(FALSE) in the loop. In Microsoft Excel version 2.x, when the macro attempts to close the macro sheet containing the running macro, ERROR(FALSE) suppresses the resulting error, and the macro sheet does not close. In Microsoft Excel version 3.0, however, the macro sheet closes.
Resolution
With Microsoft Excel version 3.0, your macro needs to include a check to determine whether the active document is the macro sheet containing the running macro. For example, the following statement closes any document other than the macro sheet containing the statement:
IF(GET.DOCUMENT(1)<>GET.CELL(32,A1),CLOSE())
Issue: DEFINE.NAME(“name”,A1) bug fix changes its behavior.
With Microsoft Excel version 2.1 for the MicrosoftÒ WindowsÔ graphical environment, the DEFINE.NAME(“name”,A1) statement defines a name on the active worksheet or macro sheet. This statement should instead define a name on the macro sheet containing the statement. The behavior was already fixed in Microsoft Excel version 2.2 for the AppleÒ MacintoshÒ computer, and now it is also fixed in Microsoft Excel version 3.0 for Windows.
This poses a conversion problem if you’ve been using DEFINE.NAME(“name”,A1) to define a name on the active worksheet or macro sheet in Microsoft Excel version 2.1 for Windows.
Resolution
If you want to define a name on the active worksheet or macro sheet, be sure your statement is of the form DEFINE.NAME(“name”,!A1) by inserting an exclamation point (!) where necessary.
Issue: DIALOG.BOX clears the Clipboard.
Microsoft Excel version 3.0 for Windows clears the Clipboard when the DIALOG.BOX function is executed. This poses a conversion problem if you have any macros that display a dialog box between execution of a COPY function and a PASTE function.
Resolution
Move the DIALOG.BOX function so that it precedes the COPY function.
Issue: Change in font size for user-defined dialog boxes.
Microsoft Excel version 3.0 uses HelveticaÒ 8 for all dialog boxes, whereas Microsoft Excel version 2.x uses Helvetica 10. Scaling occurs to keep dialog boxes proportional. As a result, user-defined dialog boxes are smaller in Microsoft Excel version 3.0. This poses a conversion problem if you’ve used hard-coded height and width parameters and above-average-length text strings in user-defined dialog boxes.
Resolution
If you remove the hard-coded length and width dimensions from your user-defined dialog box definition, Microsoft Excel version 3.0 will display dialog box text and objects correctly.
Issue: Change in focus priority for user-defined dialog boxes.
Microsoft Excel version 2.1 for Windows does not give focus priority to default command buttons (type 1 or type 4), whereas Microsoft Excel version 3.0 for Windows does. This poses a conversion problem if you want the focus in a user-defined dialog box set to some object other than a default button.
Resolution
You can alter the focus priority by changing a default OK button from type 1 to type 3 or a default Cancel button from type 4 to type 2. You can also put a number in the first row of the initial/result column of a dialog box definition to specify which dialog box item gets focus priority. Items are numbered starting with 1 in the second row of the dialog box definition.
Issue: Linked list box now must be accompanied by an edit box.
Microsoft Excel version 2.x for all platforms allowed you to have a linked list box in a user-defined dialog box without an accompanying edit box. In Microsoft Excel version 3.0, you’ll encounter a macro error when attempting to display a dialog box of this type. This poses a conversion problem if you have any user-defined dialog boxes with stand-alone linked list boxes.
Resolution
You can either change the linked list box (type 16) to a list box (type 15) or add an edit box to the dialog definition prior to the linked list box entry. The edit box does not necessarily need to be displayed in the dialog box, but it must be present in the dialog box definition.
Issue: EXEC(“DOCUMENT.DOC”,1) now violates system integrity.
With Microsoft Excel version 2.1 for Windows, the EXEC(“D:\WINWORD\DOCUMENT.DOC”,1) statement launches Microsoft Word for Windows and opens the DOCUMENT.DOC file. In Microsoft Excel version 3.0 for Windows, this statement attempts to open an MS-DOSÒ file and violates system integrity. This poses a conversion problem if you’ve written macros assuming that issuing the EXEC statement on a document first launches the appropriate Windows application.
Resolution
To launch Microsoft Word for Windows and open a document with Microsoft Excel version 3.0 for Windows, use the EXEC(“D:\WINWORD\WINWORD.EXE D:\WINWORD\DOCUMENT.DOC”,1) statement.
Issue: EXTRACT() prioritizes the name Extract over the selection.
With Microsoft Excel version 2.x, you had to select cells to identify the extract range before executing the EXTRACT function. Because Microsoft Excel version 3.0 uses the name Extract to identify the extract range, cell selection is no longer necessary. This poses a conversion problem if your macros attempt to identify the extract range by selecting cells, when the name Extract has been defined elsewhere on the worksheet.
Resolution
You can use DELETE.NAME(“Extract”) to be sure the name Extract, if present, does not override your cell selection. Or better yet, use the name Extract to your advantage when writing extraction macros in Microsoft Excel version 3.0.
Same issue and resolution as in the preceding description of CLOSE.
Issue: DLLs must use the file name extension DLL instead of EXE.
Microsoft Excel version 2.1 for Windows searches for dynamic link libraries (DLLs) with the EXE file name extension, whereas Microsoft Excel version 3.0 searches for DLLs with the DLL file name extension. This poses a conversion problem if you have any macros in Microsoft Excel version 2.1 for Windows that use DLLs.
Resolution
Rename the DLL with the DLL file name extension, and be sure that the REGISTER function contains the correct file name and path to the DLL.
Issue: The code B data type is now a floating-point number.
In Microsoft Excel version 3.0 for Windows, the value returned by code B is a floating-point number. In Microsoft Excel version 2.x for Windows, code B returns a pointer to a value. This change is well documented in Appendix A of the Microsoft Excel Function Reference. This poses a conversion problem if you have any REGISTER functions that use code B to return a pointer—for example:
REGISTER(“MATH.DLL”,”BESSEL”,”BIB”)
Resolution
Use code E instead of code B—for example:
REGISTER(“MATH.DLL”,”BESSEL”,”EIB”)
Issue: Cell references defined as memory variables are now dynamic.
In Microsoft Excel version 2.x, the SET.NAME(“cell”,SHEET.XLS!$A$1) statement defines a memory variable associated with the macro sheet that always refers to cell A1 on SHEET.XLS. Because of improved linking in Microsoft Excel version 3.0, the reference named cell adjusts to refer to that cell if its location changes. For example, if your macro inserts a row at row 1, the referenced named cell changes to $A$2 in Microsoft Excel version 3.0. This behavior change is certainly of broader impact than the SET.NAME function only, although it may occur in SET.NAME.
This poses a conversion problem if your macros assume that a reference value defined as a memory variable is static.
Resolution
You’re probably used to this dynamic behavior if you’ve been using names all along to refer to specific cells in a worksheet or macro sheet regardless of their location. However, in cases in which you need static references, you can use the SET.NAME(“cell”,“SHEET.XLS!$A$1”) text notation.