Developer FAQ - General Questions

Category: General VBA | [Item URL]

Note: This document was written for Excel 97 - 2000.

Why does Excel have two macro languages?

Early versions of Excel used a macro language called XLM. The VBA language was introduced in Excel 5, and is vastly superior in every way. XLM has been phased out, so for new macro development you should use VBA.

I need to distribute a workbook to someone who still uses Excel 4. Is there a way to have Excel 97 or Excel 2000 record my actions to an XLM macro?

No, the macro recorder in these versions can only generate VBA macro code.

Do XLM macros written for previous versions of Excel work in Excel 97 and Excel 2000?

In most cases, they will work perfectly.

I'm looking for a third-party utility that will convert my Excel 4 macros to VBA. Am I out of luck?

Yes, you are. No such utility exists, and it is unlikely that one will be written. Such conversions must be done manually. Because all versions of Excel can execute XLM macros, however, there is really no reason to convert these macros -- unless, of course, you'd like to update the macro to include new features.

Is it possible to call a VBA procedure from an Excel 4.0 XLM macro?

Yes, by using XLM's RUN function. For example, the following macro runs the Test subroutine contained in Module1 in workbook Book1.xls:

=RUN(Book1.xls!Module1.Test)

Is there a way to automatically convert 1-2-3 or Quattro Pro macros to VBA macros?

No way. The macros will have to be rewritten for Excel.

Is there a utility that will convert my Excel application into a stand-alone EXE file?

No.

How can I add a drop-down list to a cell so the user can choose a value from the list?

Type the list of valid entries in a single column (this column can be hidden, if desired). Select the cell or cells that will display the list of entries, choose Data - Validation, and select the Settings tab. From the Allow drop-down list, select List. In the Source box, enter a range address or a reference to the items in your sheet. Make sure the In-cell dropdown box is selected. This technique does not require any macros.

How can I increase the number of columns in a worksheet?

You can't. This number is fixed and cannot be changed.

How can I increase the number of rows in a worksheet?

See the answer to the previous question.

Is it possible to change the color and font of the sheet tabs?

You can't change the color. You can, however, change the font size. In the Windows Control Panel, select Display. In the Display Properties dialog box, click the Appearance tab. In the Item list, select Scrollbar. Use the spinner to increase or decrease the size. This setting will affect other programs.

Note: Excel 2002 offers the ability to change the color of sheet tabs.

How can I print the workbook's full path and filename in a page header?

Amazingly, Microsoft continues to ignore what must amount to thousands of requests per year for this feature. The only way to print a workbook's path in a header or footer is to use VBA. The best approach is to take advantage of the WorkbookBeforePrint event. For example, place the following subroutine in the code module for the ThisWorkbook object to print the workbook's full path and filename in the left header of each sheet.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    For Each sht In ThisWorkbook.Sheets 
        sht.PageSetup.LeftHeader = ThisWorkbook.FullName 
    Next sht 
End Sub