Microsoft Corporation
October 1996
Microsoft Visual Basic for Applications and Macro Languages
Microsoft Visual Basic for Applications and Microsoft Office
Microsoft Visual Basic for Applications and Microsoft Visual Basic
Microsoft Visual Basic for Applications and Microsoft Visual Basic Scripting Edition
Microsoft Visual Basic for Applications Cross-Platform Support
Advanced Topics (Developing with Microsoft Visual Basic for Applications)
Microsoft® Visual Basic® for Applications, which provides developers with professional-quality development tools for building custom solutions, is a powerful development environment built into the Microsoft Office family of applications. Microsoft Visual Basic for Applications provides an opportunity to learn a single language and use that knowledge across multiple applications. In addition to being part of Microsoft Office, Microsoft Visual Basic for Applications is also a member of the Visual Basic family of languages, which includes the new Visual Basic Scripting Edition for Internet browsers and the Microsoft Visual Basic programming system products.
Many developers have questions about Microsoft Visual Basic for Applications, including how it differs from macro languages, which applications support Microsoft Visual Basic for Applications, and the relationship of Microsoft Visual Basic for Applications to Visual Basic Scripting Edition and Visual Basic. This informal question and answer sheet is designed to help you understand Visual Basic for Applications and how it provides developers with a leveraged development tool for building robust custom solutions.
Microsoft Visual Basic for Applications is the development environment included in several applications within the Microsoft Office family. It comprises the Microsoft Visual Basic for Applications language engine and an integrated development environment (IDE) with a full-featured editor, debugger, and OLE object browser. Extensive context-sensitive help is available for both Microsoft Visual Basic for Applications language syntax and OLE objects such as Automation objects and OLE Controls (OCXs).
Microsoft Visual Basic for Applications has all the procedural language features developers expect in a professional development environment. These features include:
Microsoft Visual Basic for Applications enables developers to learn a single language syntax and development environment, which can then be used across multiple applications, such as Microsoft Excel and Microsoft Project.
The IDE in Microsoft Visual Basic for Applications includes an editor with support for color-coded syntax, a line continuation character, and immediate syntax checking. Complete debugging facilities are also built into the IDE, including support for setting breakpoints and watches and a call stack trace for modifying and tracking the execution path of an application. An object browser for viewing the properties and methods of OLE objects is also included.
The popularity of Microsoft Visual Basic for Applications and Microsoft Visual Basic has resulted in the creation of a worldwide development community. In addition to Microsoft documentation and product support, there are worldwide developer events and hundreds of books and magazines available in any bookstore. Some of the many resources available to Microsoft Visual Basic for Applications developers include the following:
The most up-to-date source for both Microsoft and third-party information for Microsoft Visual Basic for Applications developers is the Microsoft Developer Network Online Web page at http://www.microsoft.com/msdn/.
A macro is a set of actions recorded or written by a user. For example, a user can create a macro that always prints his or her name in bold on a spreadsheet. The user can name it PrintName and then reuse the PrintName macro in any spreadsheet.
Macros are written or recorded using a macro language. A macro language includes built-in commands that mimic the functionality available from menus and dialog boxes within an application. Macro languages were originally included in applications such as Microsoft Excel and Lotus 1-2-3. While these languages were powerful enough for simple automation, they were not designed to support development of large-scale custom solutions.
Macro languages did not scale to large development efforts for several reasons, including:
"Development environment" is much more appropriate and frequently used; however, "macro" has meaning for a wide range of users even though Microsoft Visual Basic for Applications provides many benefits over macro languages. These benefits include an integrated development environment that can be leveraged in multiple applications and is scalable across the Microsoft Visual Basic family of tools.
No. Users who have working macros do not need to do any work to convert them to Microsoft Visual Basic for Applications. Depending on the application they are using, their macros will either continue to run (as with Microsoft Excel Macro Language in Microsoft Excel) or will be automatically converted to Microsoft Visual Basic for Applications (as with Microsoft Project). Microsoft Access for Windows® 95 users have the choice of continuing to run their existing macros or converting them to Microsoft Visual Basic for Applications.
Microsoft Excel is the only application in which macros may need to be migrated, and it provides several tools to make migration easier. First, Microsoft Excel Macro Language macros can call Microsoft Visual Basic for Applications code and vice versa. Second, the macro recorder in Microsoft Excel version 5.0 and later includes an option to record macros in either the Microsoft Excel Macro Language or Microsoft Visual Basic for Applications. Developers can use the macro recorder to learn how Microsoft Excel Macro Language functionality maps to Microsoft Visual Basic for Applications. Finally, the debugging tools in Microsoft Excel provide support for debugging Microsoft Excel Macro Language macros during the migration process.
Microsoft Visual Basic for Applications is currently available in the following Microsoft Office family applications: Microsoft Access for Windows 95, Microsoft Excel version 5.0, Microsoft Excel for Windows 95, Microsoft Project version 4.0, and Microsoft Project for Windows 95.
Microsoft Visual Basic for Applications will be included in the entire Microsoft Office family, including Microsoft PowerPoint® and Microsoft Word, with the next release of Microsoft Office.
Microsoft Visual Basic is a development environment that enables developers to create standalone applications (.EXE files) using an extensible and advanced forms development environment.
Microsoft Visual Basic includes the entire Microsoft Visual Basic for Applications feature set and extends it further. Both tools share the same language engine, but Microsoft Visual Basic provides a superset of the Microsoft Visual Basic for Applications development environment. Microsoft Visual Basic includes many of the Microsoft Visual Basic for Applications IDE elements, including the debugger and object browser, and can also integrate with additional third-party tools such as version control and test utilities. Developers using Microsoft Visual Basic for Applications can integrate OLE objects into a custom solution. Microsoft Visual Basic developers can integrate, create, and distribute OLE objects in a network environment. Finally, Microsoft Visual Basic for Applications must be included or "hosted" within an application such as Microsoft Excel, while Microsoft Visual Basic is a standalone development tool.
Visual Basic Scripting Edition is designed to be "hosted" within an Internet browser such as the Microsoft Internet Explorer or other, third-party, browsers. Visual Basic Scripting Edition is a lightweight and extremely fast language engine designed specifically for environments such as the Internet or World Wide Web. Visual Basic Scripting Edition leverages the strengths of Microsoft Visual Basic for Applications and enables developers to use their Microsoft Visual Basic for Applications development knowledge to quickly create solutions for the Internet or World Wide Web.
Please refer to the Visual Basic Scripting Edition Web site at http://www.microsoft.com/vbscript/ for detailed information.
Visual Basic Scripting Edition supports a subset of the Microsoft Visual Basic for Applications language syntax. Visual Basic Scripting Edition does not include an IDE like that found in Microsoft Visual Basic for Applications because it is designed to be a lightweight language engine. Visual Basic Scripting Edition is designed to be embedded in third-party applications such as web browsers, while Microsoft Visual Basic for Applications is delivered as part of host application such as Microsoft Access or Microsoft Excel.
Developers can write Microsoft Visual Basic for Applications code that is backward compatible if they only use Microsoft Visual Basic for Applications language features that are supported in Visual Basic Scripting Edition. A complete listing of Microsoft Visual Basic for Applications features supported in Visual Basic Scripting Edition is included on the Microsoft Visual Basic Scripting Edition Web page.
Yes, Microsoft Access for Windows 95, Microsoft Excel for Windows 95, Microsoft Project for Windows 95, and Microsoft Visual Basic 4.0 all include Microsoft Visual Basic for Applications and run on the Windows NT® version 3.51 or later operating system.
Yes, Microsoft Visual Basic for Applications is included in Microsoft Excel 5.0 for the Apple® Macintosh® and Microsoft Project 4.0 for the Macintosh. Future versions of Microsoft Office family applications for the Macintosh will include Microsoft Visual Basic for Applications.
A type library defines the set of objects, properties, and methods exposed by an application. For example, Microsoft Excel has a type library describing all of its exposed spreadsheet and chart objects. Microsoft Visual Basic for Applications developers can access these objects in their custom solution, either from within Microsoft Excel or from another application such as Microsoft Access or Microsoft Visual Basic. Type libraries also enable developers to perform early binding, which provides faster, design-time syntax checking of Microsoft Visual Basic for Applications code that refers to these objects.
Early binding means that checking of references to OLE objects in Microsoft Visual Basic for Applications code is performed once, during design time, rather than during run time (late binding). Early binding is much faster than late binding and is the preferred method. Every Automation application supports late binding, while only some applications support early binding.
Early binding can be used when the following are true:
Not strongly typed object examples:
Dim MySheet as Object
Dim MyDoc as Variant
Strongly typed object examples:
Dim MySheet as WorkSheet
Dim MyDoc as WordBasic
The last two cases are strongly typed because the object references are created with a specific object type.
Microsoft Access for Windows 95, Microsoft Excel for Windows 95, Microsoft Project for Windows 95, and Microsoft Visual Basic 4.0 all use a version of Microsoft Visual Basic for Applications. In order to maintain compatibility with Access Basic and address developer issues in moving to 32 bits, several functions (math, financial, and date), the Byte data type, Windows registry functions, and conditional compilation were added to Visual Basic for Applications in Microsoft Access and Microsoft Visual Basic. This means that there are slight differences between Microsoft Visual Basic for Applications in Microsoft Access and Visual Basic and the version in Microsoft Excel and Microsoft Project. The next version of Microsoft Office will include the same version of Microsoft Visual Basic for Applications across all applications.
Yes. Developers can write code that can be reused across all the applications that include Microsoft Visual Basic for Applications. Developers who want to write portable Microsoft Visual Basic for Applications code must account for the slight differences between Microsoft Visual Basic for Applications implementations discussed previously.
Developers should also fully declare all object references in their Microsoft Visual Basic for Applications code. Fully declaring an object reference means there is no ambiguity about which object the Microsoft Visual Basic for Applications code refers to. Developers can fully declare an object reference by using both the library (application) name and the object name of the object in the form: <application name>.<object name>.
Not fully declared object example:
Dim MyWorksheet as Worksheet
Dim MyDoc as WordBasic
These two examples are not fully declared. Moving either of these lines of code from their original application to another application could produce unexpected results. These problems can be avoided by fully declaring object references as shown below.
Fully declared object examples:
Dim MyWorksheet as Excel.Worksheet
Dim MyDoc as Word.WordBasic
In these cases both the library or application name (Microsoft Excel or Word) and the object name (WorkSheet or WordBasic) are used to completely describe the object. There is only one possible object that can be referenced and Microsoft Visual Basic for Applications can immediately locate that object. If the first line of code was moved from Microsoft Excel to Microsoft Project, it would always run correctly since Microsoft Project would know that the reference was to an object within Microsoft Excel.