Bra Utbildning AB

 

<-- Back
VBA & Object models
Work with text
Bookmarks
Ranges
Fields
Form fields
Autotext
Doc var & Doc Prop
Built-in commands
Built-in dialogs
Automation
External databases

Visual Basic for Applications (VBA) and object models


This is a brief introduction for someone who has previously not been developing procedures in Microsoft Office1) (Excel, Word, Outlook, Access and PowerPoint).

Visual Basic for Application (VBA)
VBA is the common programming language used in all MS Office applications. VBA is not equal to VB (Visual Basic) a separate application which can be purchased for developing stand-alone applications. Someone working with VB will feel at ease with VBA and vice versa (to some extent).

Examples of VBA 
IF xxxxx THEN xxxxx ELSE xxxxx END IF
DO xxxxxxx LOOP
SELECT CASE xxxx   CASE 1 xxxx CASE 2 xxx CASE ELSE xxx END SELECT
Format(Date,  "MMMM d, yyyy")

None of the statements above have any immediate connection to Excel, Word or the other applications within the the MS Office suite. The VBA statements are used together with commands originating from each of the Office applications - to be more precise - from each application's object model. 

This is what it could look like if we would use the Select Case statement to identify capitals based on number (LanguagePhoneID) entered by the user. It would also be possible to check the operating system's language version or the application (Word or Excel etc). 

Object models in Office from Microsoft
Each application in MS Office can be divided into a large number of objects. Word has documents, Excel has workbooks and sheets whereas Access offer's tables etc.  

Office development is about combining VBA commands with the objects you have in mind.  When developing you usually find yourself knowing one or two object models very well and the others to some extent. Our emphasis in on Word and Excel.  

Example: Code from Excel's object model
ActiveWorkbook.Sheets("January").Range("C7").Value = 19

Example from Word's object model 
ActiveDocument.Paragraphs(1).Range.Text = "Any text you wish"

Example from Outlook's object model
Dim TheDefaultContactsFolder As MAPIFolder
TheDefaultContactsFolder = _
         GetNamespace("mapi").GetDefaultFolder(olFolderContacts)

Example from Access' object model
Set db = DBEngine.Workspaces(0).OpenDatabase("Invoices.mdb")

Example - a combination of VBA and Excel's object model
ActiveWorkbook.Sheets("January").Range("C7").Value = _
         Format(Date,  "MMMM d, yyyy")

Example - a combination of VBA and Word's object model
ActiveDocument.Bookmarks("TodaysDate").Range.Text = _
         Format(Date,  "MMMM d, yyyy")

Conclusion
To create a professional result for the end user, basic knowledge regarding the underlying application is necessary. This combined with VBA knowledge and knowledge about the application's object model makes it fairly doable. The learning curve is considered steep when one starts developing for the Office applications. This has been our experience as well. 

Is that all!?
The operating system, possibly Windows from Microsoft,  in your computer can be reached from the MS Office applications using API-calls. You could find it handy to verify if a certain disk drive, for instance  W: is installed and if it is a network drive or a local drive. An API-call can be used for this. There are also DLL-files, COM-objects and OCX components which can be referenced and used in your code.

Automation
An application which exposes its object model and supports automation can be referenced and launched from other applications. An Excel solution could start Word or Access and perform actions (including closing and cleaning up) within the launched  application - without any interaction with the user. An application like a self-contained invoice application, possibly created with VB, could for instance use the best suited application for printing professional reports. Such a choice could be Word. The developer doesn't have to worry about print routines and error handlers as they come in the off-the-shelf Word-package.

More on Automation.

--------------------------------------
Microsoft, Word, Excel, PowerPoint, Outlook, Access & FrontPage are registered trademarks of the Microsoft Corporation. Code and information on our pages are free of charge and we do not assume liability for use of any code or information provided. Usage of this site's content is at your own risk.