Background: Microsoft Excel Macros
Microsoft Excel has long given users the ability to automate tasks within the program and to expand the functionality of the program
by creating macros. Original Microsoft Excel macros (XLM macros) used a unique programming language to construct routines that would ultimately be stored as Excel macro files (.xlm), separately from spreadsheet files (.xls). This type of Excel macro is also called an Excel 4 macro.
XLM macro sheets have function calls listed in columnar format. Every function of Excel was addressable by these function calls, although macro files were known to grow quite large and become somewhat incomprehensible when programming extensive operations. XLM macros were preferred by those with programming aptitude because they could be used to automate highly complex tasks. Ordinary users, however, typically struggled with XLM macros because of the amount of time and experience required to become proficient at creating them.
The “new” Microsoft Excel macro language began with Excel 5 and produces macros that are called VBA or Excel 5 macros. VBA (Visual Basic for Applications) is an adaptation of the Visual Basic object oriented programming language. Because many programmers are familiar with Visual Basic, and because Visual Basic has a fairly small learning curve, programmers and power users alike are happy with Excel 5 macros. Users with little or no interest in programming, however, have a powerful tool called the Excel Macro Recorder, where a user can record mouse and keyboard behavior and store it as a macro.
The powerful implications of VBA macros in Excel have rendered XLM macros almost obsolete. VBA macros can be digitally signed (unlike XLM macros) and thus avoid some of the data security issues that were associated with the Excel 4 style.
Image Credit: Wikimedia Commons/Alphax
Although many users have already converted their macros from XLM to VBA, Microsoft has been criticized for dragging its feet when it comes to making all the functions from XLM available in VBA. In Office 2010, Microsoft included new features to close the gap between XLM and VBA and seems to suggest that XLM support may be dropped from subsequent versions of Excel.
Microsoft has a Web page dedicated to the migration of XLM macros to VBA using Excel 2010, which provides valuable information about how to complete the conversion process.
Because Excel macros using xlm files have largely disappeared from use, most people use VBA for automating and extending Microsoft Excel. Still, many of the old style macros are still around and will run in Microsoft Excel. When editing or updating xlm macro files, Microsoft has help available. The first tool is the “Excel 7.0/97 Macro Function Help File for Excel 4.0 Macros.” This file allows the help files for Excel macro functions for use as a reference. Another version of this file for Excel 2000 macros is also available.