Microsoft Office provides intelligence, integration, and Internet functionality to help users work efficiently and effectively. With Microsoft Office, you can create custom solutions using Visual Basic for Applications in each Microsoft Office application.
Figure 4.5 The Microsoft Office suite
Microsoft Office contains a number of components that can be used in developing business solutions:
The Microsoft Office Binder is an application that allows users to store multiple Office documents within a single file. The following types of Office documents can be stored in an Office Binder:
As an OLE object application, the Office Binder provides a very simple object model that can be used for saving, retrieving, and printing documents. Microsoft Office must be installed in order to access Office Binder objects through OLE automation.
In the following exercise, you will create a new binder and add a new Microsoft Word document and a new Microsoft Excel document to the binder.
* To create a binder and add components
Dim BND As OfficeBinder.Binder
Set BND = CreateObject("Office.Binder")
BND.Visible = True
BND.Sections.Add ("Word.Document")
BND.Sections.Add ("Excel.Sheet")
BND.SaveAs filename:="NewBinder.obd"
BND.Close 0
Set BND = Nothing
Your application will create an instance of Office Binder and add a Word document and Excel spreadsheet the binder through automation.
Microsoft Visual Basic for Applications (VBA) is a new version of Visual Basic included in Office applications.
Rather than working with forms and controls, as in Visual Basic, VBA works within the context of the application. The most significant difference between VB and VBA is that VBA does not support events. Instead, applications supporting VBA expose objects or functionality, such as:
There are three basic scenarios in which VBA is used:
VBA Object Model
Microsoft Office allows you to create custom business solutions by exposing application functionality through programmable objects that take advantage of the built-in functionality of each application.
Using Office components you can create custom solutions with less code, which in turn results in shortened development cycles and more powerful solutions. Using Office components adds stability and standardization to the business processes in which they are used.
Microsoft Office components can be controlled and incorporated into a business solution by using the VBA programming language. For example, by writing a program that defines and uses a Microsoft Excel chart object, a corporate developer makes the very powerful charting functionality of Microsoft Excel an integral part of the custom application. In the same way, using a Microsoft Access report object as part of a business solution provides the custom application with the versatile reporting features of Microsoft Access.
Example
There are many levels of objects in VBA; however, the Application object is needed to create an instance of an Office component. This example creates an Application object in Visual Basic.
'Declare the Application object
Dim XL As Excel.Application
'Use the CreateObject Function
Dim XL2 As Object
Set XL2 = CreateObject("Excel.Application")