Whats the Difference Between VBA and a Macro?

What’s the Difference Between VBA and a Macro?

If VBA is the remote control for Excel, then what is a macro? And what is the difference between VBA and a macro? It’s all very confusing. In essence, a macro is a computer program that gives automated instructions to the computer. The original macros were a way to use a few characters to represent a lot of instructions. They were called macros because the output was much bigger than the input.

In fact, the first spreadsheet macro programs really did just expand a short string of characters into a long set of actions. They were just shortcuts for the user interface commands. For example, if in the user interface you typed R (for “Range”), N (for “Name”), and C (for “Create”), you would enter RNC into the macro to automate the process. This approach was intuitive, but it also had inherent weaknesses. Not only were keystroke macros difficult to read, but they also didn’t adapt well to a graphical user interface. What keystrokes would you use to represent dragging a rectangle with the mouse? They also made it difficult to enhance the user interface, because any changes to the menu structure would cause any previously created macros to fail.

To solve these problems, the early versions of Excel contained a new type of programming language-one that was independent of the user interface command names. For example, in Microsoft Excel version 4 (Excel 2007 is version 12), you could copy a range in at least three different ways: press Ctrl+C, click the Copy button on the Standard toolbar, or click Copy on the Edit menu. All of these methods were represented by the same instruction =COPY(). This new programming language was not technically a macro language in the old sense of expanding a few characters into a sequence of instructions. It was technically a set of functions, much like the functions used to perform tasks in spreadsheet cells. But saying “You can now automate simple tasks by writing custom programs based on specialized spreadsheet functions” sounded scary, so the Excel team continued to refer to the custom programs as macros. The word macro came to mean a program that users can write by themselves.

Excel’s early function-based macros were a major improvement over keystroke macros, but they still had two big drawbacks. First, Excel macros were very specific to Excel-the functions looked too much like spreadsheet formulas to be able to adapt to other applications, such as Microsoft Office Word. Second, the number of functions increased with each new version of Microsoft Office, and there was no good way to organize or group the thousands of possibilities.

To solve the first limitation-that function-based macros are specific to Excel-Microsoft introduced Visual Basic for Applications, or VBA, starting with Microsoft Excel version 5. VBA acts as a general-purpose language that is independent of the application. Suddenly, anyone who knows how to work with any version of Microsoft Visual Basic has a big head start in automating Excel, and anyone who learns how to write Excel macros in VBA can transfer that knowledge to other types of Visual Basic programming. In addition, although Excel was the first major application to use VBA, VBA is not tied directly to Excel; it works just as well with other VBA-enabled applications, such as Word and Microsoft Office PowerPoint.

To solve the second limitation of function-based macros-that there are too many commands to manage effectively-VBA works with an object model. The term object model sounds pretty scary, but it’s really just a logical way to organize all the commands you can carry out in an application. In an object model, each different part of the application-for example, a workbook, a range, or a point on a chart-becomes an object, and each object has its own list of functions. You’ll learn more later about what an object is and how objects relate to functions, but the point is that the object model organizes all the millions of possible commands around how each command is used-for example, you copy and paste a range of cells, but you don’t copy and paste points on a chart.

Because of the object model, VBA doesn’t need any special access to the internals of Excel. Rather, Excel exposes its capabilities to the outside world by means of the object model, and VBA talks to the object model.

This means that an Excel VBA macro can control not only Excel, but also any application that provides an object model. All Microsoft Office applications, and several other Microsoft and non-Microsoft applications, provide appropriate object models.

The VBA that comes with Excel isn’t the only language that can communicate with the object model. Any language that supports Automation can control Excel. You can control Excel not only with the VBA hosted by Excel, but also with the VBA hosted by Word, with Microsoft Visual Basic version 6, or even with a language such as C++. With a simple translation layer, you can also talk to the Excel object model from Microsoft .NET applications written in C# or Microsoft Visual Basic .NET.

VBA and the .NET Framework

If .NET doesn’t mean anything to you, skip this explanation and don’t worry about it. If you are familiar with .NET, you may wonder how VBA macros relate to .NET programs. In Excel 2007, VBA is native code, and the object model is a Component Object Model (COM) interface. I don’t know future plans for the product, but it is possible that a future version of Excel could use managed code such as C# or Visual Basic .NET as the embedded macro language. If that happens, most of what you learn for this version will still be valid, because the fundamentals of the object model will change only to the degree that the features in Excel change.

In this book, you will learn how to create VBA macros for your own use and to share with others in your workgroup. For most business users, writing macros in VBA is sufficient. If, however, you want to create a more sophisticated add-in, or if you want to create a stand-alone application that uses Excel in the background, you can create a.NET application in Microsoft Visual Studio. Microsoft Visual Studio 2005 includes a downloadable component called Visual Studio Tools for Office (VSTO), which includes the necessary Primary Interop Assembly (PIA) to communicate with the Excel object model. Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office System (VSTO 2005 SE) also includes tools that you can use to create custom task panes.

The object model exposes Excel’s capabilities to VBA, but more importantly, it also exposes Excel’s capabilities to you. When you know how to read and interpret an object model, you discover new features and quickly figure out how to put them to work. The best way to learn how VBA communicates with Excel objects is to create some simple macros by using the macro recorder. Eventually, however, you’ll want to move beyond the limitations of the macro recorder.