Migrating Excel 4 Macros to VBA

Thanks to Eric Patterson for writing this blog post.

As promised in our Programmability Improvements in Excel 2010, here are more details about the Excel 2010 improvements to aid in migrating Excel 4 Macros to VBA.

Excel has a macro facility, known as Excel 4 macros (XLM for short) that was the primary macro language prior to the introduction of VBA in Excel 5.0. Most people have long since migrated their Excel 4 macros to VBA; however, some Excel 4 macro capabilities were missing from VBA, which made this migration difficult.

In Excel 2010, one of our goals was to remove any remaining barriers that people had to complete the migration of Excel 4 macros to VBA. Excel 2010 does still enable the creation, editing and execution of Excel 4 macros. Please use Excel 2010 to migrate your macros and let us know if we missed any functionality.

Print Performance

The PAGE.SETUP() XLM function allows multiple Page Setup properties to be set with one call resulting in better performance than setting multiple PageSetup properties via VBA. In Excel 2010, we have added a new PrintCommunication property that suspends printer communication to improve performance. You can set the property to False in your code prior to using page setup methods and then set it to True afterwards to execute all of the built up commands. If there are cached commands, setting the property to True will commit all cached PageSetup commands. This should eliminate any need for the legacy PAGE.SETUP() function.

For example, If you want to set your page margins, orientation, paper size, and scale to fit one page, you set the following 8 properties using VBA. Each one will need to establish communications with the printer driver and pass along the setting.

With ActiveSheet.PageSetup

.LeftMargin = Application.InchesToPoints(0.25)

.RightMargin = Application.InchesToPoints(0.25)

.TopMargin = Application.InchesToPoints(0.75)

.BottomMargin = Application.InchesToPoints(0.75)

.Orientation = xlLandscape

.PaperSize = xlPaperLetter

.FitToPagesWide = 1

.FitToPagesTall = 1

End With

With the new PrintCommunications property you can add one statement before the PageSetup statements and one after and reduce the calls to the printer from 8 to 1 for this simple example.

Application.PrintCommunication = False

With ActiveSheet.PageSetup

.LeftMargin = Application.InchesToPoints(0.25)

.RightMargin = Application.InchesToPoints(0.25)

.TopMargin = Application.InchesToPoints(0.75)

.BottomMargin = Application.InchesToPoints(0.75)

.Orientation = xlLandscape

.PaperSize = xlPaperLetter

.FitToPagesWide = 1

.FitToPagesTall = 1

End With

Application.PrintCommunication = True

Charting Enhancements

There are a number of properties for chart elements that were previously only accessible through the Excel 4 Macro Language. We have added additional properties to VBA for these items.

Formula Properties

New properties replace the GET.FORMULA() XLM command, providing formula support for missing chart elements. New Formula properties (Formula, FormulaR1C1, FormulaLocal, FormulaR1C1Local) have been added for AxisTitle, ChartTitle, DisplayUnitLabel, and DataLabel objects.

Series/Point Name

The chart object model currently doesn’t provide a way to determine the series name that is given to a series when it is created. This information is available when hovering over a data point with the mouse, or using the SELECTION() function in XLM. The XLM command returns a point name in the format “S_mP_n” where m is the series number assigned when the chart is created and n is the point number. For example, when selecting a point on a chart, SELECTION() returns "S1P3". A new Point.Name property has been added that Returns a point name in the format “SmPn” where m is the series number assigned when the chart is created and n is the point number.

Position Properties

The XLM Function GET.CHART.ITEM returns the X,Y coordinates of the corners or mid-points of any chart item. This function had been necessary for some chart elements that did not have positional (.Left, .Top, .Width, and .Height) properties. In Excel 2010 we added .Left, .Top, .Width, and .Height properties for any chart elements that did not have them. This included AxisTitle, ChartTitle, DisplayUnitLabel, Point, and DataLabel objects. Additionally we added a Point.PieSliceLocation Method that returns coordinates of multiple points on Pie Slices.

General Improvement

In addition to the charting enhancements, there are a few additional XLM functions that needed equivalent VBA fucntions.

Addins2 Collection Object

Excel currently has an AddIns Collection that represents the list of add-ins that is displayed in the Add-Ins dialog box in Excel. What this collection is missing and can be determined by the DOCUMENTS(2) XLM function are any add-ins that are currently open (and not shown in the dialog). A new AddIns2 collection object has been added that represents all the add-ins currently available or open in Microsoft Excel, regardless of whether they’re installed.

AddIn.IsOpen Property

A new IsOpen Property has been added that returns True if the AddIn is currently open

Printed Comment Pages

Excel has the facility to print any cell comments on separate pages from the printed worksheet. The Excel 4 macro language has a function (Get.Document(51)) that returns the number of comments pages that will be printed. A new PrintedCommentPagesCount Property has been added to Excel 2010 to provide this functionality.

Argument Descriptions for User Defined Functions

The only way to get argument descriptions for custom worksheet functions into the Function Wizard is using XLM Macro Options dialog box. In Excel 2010, we added a new argument to the MacroOptions VBA Method to provide support for this.

Additional Resources

To aid in the migration of Excel 4 macros, the Macro Function help file can be found here:

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=10b40a6b-7485-437a-819b-0f446f74ed81