Greg R. Buckwalter
Microsoft Product Support Services
November 11, 1995
This article consists primarily of a graphic map of the functions, statements, and methods contained in the Microsoft® Visual Basic® for Applications library. These functions and statements are common throughout the Microsoft family of Office products. Additional functions are available in other product libraries that may be used with OLE Automation in other products.
This article consists primarily of a graphic map of the functions and statements contained in the Microsoft® Visual Basic® for Applications library. These functions are common throughout the Microsoft family of Office products, such as Microsoft Access version 7.0 or higher, Microsoft Excel version 5.0 or higher, Microsoft Project version 4.0 or higher, and Visual Basic version 4.0. The library described is the latest version of the Visual Basic for Applications library found in Visual Basic 4.0 and Microsoft Access 7.0. The Visual Basic for Applications library in Microsoft Excel version 7.0 and Microsoft Project version 4.1 is an earlier version. Additional functions and statements are available in other product objects, which may be used with OLE Automation in other products. These functions, statements, and methods maps should be used in conjunction with the extended object maps created by Ken Lassesen.
Visual Basic for Applications is the lingua franca of the Microsoft family of solutions products—Visual Basic, Microsoft Access, Microsoft Project, and any Microsoft Office products containing Visual Basic for Applications. Developers familiar with one product fail to realize that all of the functions available in any of the solutions products are available in all of the solutions products via OLE Automation. Microsoft Access developers and Visual Basic developers have had the functions and commands divided into two parts: the common Visual Basic for Applications library and the product-specific library. Learning which library contains each component allows the developer to grow beyond a single-product perspective and become a solutions developer instead of a product developer.
Developers who say, "I wish Microsoft Access had a CEILING function like Microsoft Excel; I guess that I have to write my own version in Microsoft Access" demonstrate that their understanding is a bit "walled in." The cool way of using these libraries is shown below.
In this sample function for use in Microsoft Access (or other products), the function accesses the CEILING function in the Microsoft Excel Automation server.
Dim MyExcel As Object
Function Startup() 'Called by AutoExec macro
Set MyExcel = CreateObject("Excel.Application")
End Function
Function Access_CeilingNickels(ByVal Price)
Access_CeilingNickels = MyExcel.Parent.Ceiling(Price, 0.05)
End Function
End Function
This function may then be used in Microsoft Access queries:
Select Item, ItemCode, Access_CeilingNickels(Price * .96) From Stock
Where ItemFlag="SaleItem"
Figure 1 shows the functions, statements, and methods within functional categories "By Programming Task," as described by the Object Viewer and online Help files. A map is a good learning aid and a quick reference when developing an application. Posting the appropriate map on my wall for quick on where functions and commands are located can encourage a solutions approach instead of a product specific approach.
Because Figure 1 is unreadable online, I have included the two most common graphics formats—encapsulated PostScript™ (.EPS) and Windows® metafile (.WMF)—as well as a copy of my original Shapeware® Visio™ version 4.0 file (.VSD). The first two formats can be printed across multiple pages using any of the commercial graphics applications—such as Adobe™ PageMaker™, Corel® Draw, or Microsoft® Publisher—or using Microsoft Excel. The original Visio file is included for those who have a copy of Visio and wish to modify the diagram easily.
Figure 1. The Microsoft Visual Basic for Applications library
The function categories in Figure 1 are defined in Table 1. These categories are listed in the same sequence as they appear in the map.
Table 1. Object and Collection DefinitionsTD
| Functional Group | Definition |
| Conversion | This group of functions converts, gets, and changes variable's values between data types. |
| Variables and Constants | This group of functions assigns, creates, declares, gets, requires, and sets data variables and constants. |
| Dates and Times | This group of functions converts, gets, performs calculations on, and sets dates and times. Dates and times are stored as variant-7 data types. |
| File System | This group of functions manages the operating system's files and directories, such as copying a file, changing the directory, and so forth. |
| Financial | This group of functions performs financial calculations, such as calculating return on investment or calculating loan payments. |
| Information | This group of functions creates, changes, declares, or gets information about components, such as getting information about an array or getting information about a variant. |
| Interaction | This group of functions controls interaction with the application or the operating system, such as creating an OLE object or getting/setting registry entries. |
| Math | This group of functions performs mathematical functions, such as trigonometry functions or generating random numbers. |
| String Manipulation | This group of functions compares, converts, creates, or manipulates strings. |
| Control Flow | This group of functions controls the flow of your program. |
| Operators Summary | This group of operators is used to perform mathematical calculations, perform comparisons, combine strings, and perform logical operations. |
Lassesen, Ken. "Mapping the Visual Basic for Applications Object Library: VBA 2.2." (MSDN Library, Technical Articles)
Lassesen, Ken. "Using Microsoft OLE Automation Servers to Develop Solutions." (MSDN Library, Technical Articles)
"Microsoft Access Programming and Language Reference" (VBA_ACC.HLP) is installed with Microsoft Access version 7.0.