Microsoft Excel 97 offers a very fast, powerful and portable macro language for enhancing Microsoft Excel. You can create user-defined functions, write code that controls Microsoft Excel, and even create entire applications using Microsoft Excel as a platform. Prior to Microsoft Excel 5.0, the only choice for enhancing Microsoft Excel was the Microsoft Excel Macro Language (XLM). This language has fallen out of general use with the introduction of Visual Basic for Applications (VBA) in Microsoft Excel 5.0. Visual Basic for Applications brought a more understandable syntax, dimensioned variables, faster looping, enhanced debugging tools, and a host of programming enhancements found in Visual Basic.
This new power requires a programmer to use a more rigorous coding style. While sophisticated XLM code was frequently self-modifying, VBA code can not modify its source during execution. This presented new complexities and interesting trade-off decisions over when to use one versus the other. Virtually everything could be done in both languages, but certain operations frequently lent themselves to implementation in one language over the other. Because of the many advantages and improvements to VBA since version 5, these discussions have become overwhelmingly one sided toward the VBA camp and now XLM is supported primarily for backward compatibility.
In a similar vein, some operations are more easily implemented in a language other than the VBA provided by Microsoft Excel. Perhaps you already have a large base of code written in C or you feel that a truly compiled language would offer additional speed advantages as well as increased security. The Excel SDK was created just for you! When Excel 4.0 was released a new feature was included, the C API. The C API allowed the programmer to use a high-level, compiled language to extend Microsoft Excel's functionality. The C API allowed a programmer to:
The C API was based upon the XLM language, and there was a one-to-one correspondence between the functions available in the C API and commands available in XLM. This allowed many developers to greatly extend the capabilities of Excel. With the introduction of VBA in Microsoft Excel 5.0, a second method for extending the functionality of Microsoft Excel via a high-level language was exposed. As it turns out, VBA is not a compiled component of the Excel EXE file. Instead, VBA is implemented as a DLL external to Microsoft Excel. This makes it available as a shared component for all members of the office suite as well as for members of the Office Compatible program.
If VBA isn't internal to Microsoft Excel, how does it communicate with Microsoft Excel? While implementing VBA, the Microsoft Excel developers also implemented a new API for VBA to use to control Microsoft Excel. The API itself was accessed by a relatively new feature called OLE Automation. OLE2 allows an application called an OLE Automation Controller to control another application via a set of exposed methods and properties of an OLE Automation Server.