The .NET Platform
Development Tools
COM & COM+
Data Access
Web Development
XML Technologies
Windows Servers
Wireless & Mobile
Security issues
Design & Process
Career Development
Analysis & Comment
Disposable Objects
We found this book useful!
You are not logged in: login here to access all areas.
VBA inside
Why not build the power of Office 2000, or any of over a hundred off-the-shelf applications, into your program? Kay Ewbank sings the praises of VBA.
Author: Kay Ewbank
Last updated: Apr 2000
Getting started The wise include a reference to the object libraries of the other applications you want to use in your code. This isn?t vital but it offers several advantages in that you?ll be able to use early binding (see panel overleaf), your code will run faster, you?ll have the ability to use the Object Browser, and you get the option of using constants and functions from the other applications in your code. Dim objPP as PowerPoint.Application This is early binding, and it gives you the fastest performance. You could use late binding, which offers more in terms of backwards compatibility and is more generic, but early binding is more efficient (again, see overleaf). However it?s important to remember to include a reference to the object library in your application. Dim objAccess As Object CreateObject, GetObject or New? Dim objWord as Word.Document What this code does is hopefully fairly obvious: it dimensions an object variable as a Word document, then creates a new document in that variable. Dim objExcel as Object It can also be used to create a new instance of an application that?s already running. Dim objAccess As Object The servername is the same as the machine name element of the share name. So if you?d usually refer to a share name of \\MyServer\PublicData, the servername would be MyServer. Set objWord=GetObject
(?C:\docs\mydoc.doc?) The other form grabs the instance without opening a document: Set objExcel=GetObject
(,?Excel.Application.9?) You would use GetObject if you want to create a reference to a running instance of an application. Again, this is mainly of use when you?re automating Access. In the case of the other Office applications, you might as well open a new instance. With Access, though, you might well be working with a secured database, in which case you have to supply password and user group information. You do this by starting Access using the Shell function, after which you use GetObject to refer to the instance that you created. Dim objExcel As Excel.Application
Err.Number = 0
failed: This isn?t very pretty, but at least it works. Using the objects Do
Or maybe:
If blnIOpened = True And
appExcel.Workbooks.Count = 0 Then This isn?t perfect, though, because the user may not think of closing the document. It?s abandoning them at the last moment, and it would be a lot neater to close things down for them. The problem is, how to go about it. Yet again, the answer is there if you think about it: just remember the other application can also run VBA code, so put a command button somewhere nice and obvious that takes them back to the controlling application. Private Sub Exit_Click() This closes the document, and then the code back in the main application can kick into action. If blnIOpened = True Then You also need to close the automation object that VBA creates when you either get or create the object, so you need to have a line such as: Set AppWord = Nothing This is all good practice in theory, but you should also check that your user hasn?t taken advantage of the open application to do a quick bit of word processing, spreadsheeting or the like on the side. Having closed your own documents, you should check that there isn?t anything else open before closing the instance. If there is another document open, check with the user before shutting everything down - users tend to get pretty upset if your application closes down Word when they wanted to carry on and use it. Early and late binding If you're a C programmer, early binding is vtable or
virtual function table binding, while late binding is another name for
IDispatch binding. Top: Your Visual Basic for Applications (VBA) code can call the objects exposed either by its host application or by other applications that support automation directly. Your code can even call functions that you have written using the copy of VBA in the other application.
Bottom: Alternatively you can call objects exposed by any applications that support automation, or by the VBA code they contain, from any of the languages provided by Visual Studio.
What?s the most productive programming environment out there today? Many of you are probably scrapping over whether C++ or Java is the winner, with another major group touting for Visual Basic. The sad thing is that far too few of you will be waving the banner for an extremely powerful environment that?s present on the majority of business PCs: Visual Basic for Applications.
Why is VBA such an important development environment? The major point in its favour is that you have the whole of Microsoft Office already written and available for your use. Most programs that we write are in some way related to the main desktop business applications, so why re-invent the wheel?
Of course, many programmers do make partial use of VBA by writing a few small macros to carry out specific tasks, but they are missing out on the full strength of the environment. A VBA program written in Word or Access can be powerful, but it isn?t making use of VBA?s full potential.
Where VBA becomes really powerful is when you take advantage of the ability to combine the facilities offered by more than one application (Access or Excel, for example), from a single program. VBA allows you to launch and control the behaviour of other applications, so you can call an Excel analysis spreadsheet from your Access database, change the progress of tasks in Project, send messages using Outlook, or show a PowerPoint presentation all from a single block of code.
One great thing about using Microsoft Office is that it gives you an easy way to make your applications look impressive You can use facilities like the Office Assistant to provide online help and to get information from the user, while PowerPoint?s presentation facilities are far snazzier than anything you?re likely to want to write. Office is designed to present information in easy to digest formats, so why re-invent the wheel?
Another point to remember is that many of your users will already have cross-application programs running on their systems - it?s just that the individual elements aren?t linked. Why should your users have to remember which Excel spreadsheet is used for the earnings analysis, and which is the right presentation to show the end results, when you could handle it for them?
Furthermore you needn?t restrict yourself to the Microsoft Office suite. The business drawing package Visio was the first major third party application to expose an object model that could be programmed by VBA, and proved so successful that Microsoft bought the company. There are now hundreds of applications that support and ship with VBA, including the Arc/INFO geographical information system, AutoCAD, CorelDraw, the MYOB accounting system, Popkin?s System Architect, Rational Rose, StarTeam, TurboCAD and WordPerfect Office 2000.
There is one drawback to all this: cross-application VBA doesn?t give as good a performance as you see if you work in the ?native? application. Things get better with each release of Office, so those of you running Office 2000 will be a lot better off than those running Office 97, but in the 97 version you can see a drop in performance of up to 50 per cent - in other words, your Excel macro takes twice as long to complete if executed from Word.
On the other hand, how long will it take your user to open Excel, find the right workbook, run the correct macro, save the data back to the right place in the right format, and close down Excel? The macro might run slower, but in practise not involving the user makes it faster.
Having been persuaded of the delights of cross application VBA, the next step is to think carefully about where you?re actually going to do the work. There?s a terrible tendency to decide on the principal application you intend to use and do all the work there. For example, if you?re working on a database solution that is going to make use of some of Excel?s facilities, then you might think it logical to write the whole thing from Access.
This would be a mistake. You will find life a lot easier if you write the Excel portion of the code in Excel, where you have full access to Excel?s on-line help, and then bring it across to Access once you know it?s working. Alternatively, you could simply leave your procedures in Excel and call them from Access. Even if you do eventually decide to bring the code into Access, at least you will know it worked in Excel, so any problems you encounter must be down to cross-application elements rather than the actual logic.
So how do you go about cross-application coding? There are three main stages:
For those not familiar with adding references to object libraries, you do it from the Visual Basic Editor of the application where you?re writing the code. Select References from the Tools menu. Scroll down the list of available items and you?ll see the name of the application you want to use: Microsoft Word 9.0 Object Library, for example. Tick the box to add the reference, and that?s all there is to it.
In terms of coding, the next thing you need to do is dimension an object to reference the application:
Dim objExcel As Excel.Application
While the main members of the Office family are fine for early binding, non-Office applications won?t necessarily offer the same facilities, so you might need to use late binding. Here you dimension a generic object, and then use CreateObject to reference the specific application:
Set objAccess=CreateObject
(?Access.Application?)
CreateObject and GetObject allow you to use the object. Alternatively there is the New keyword:
Set objWord = New Word.Document
This is the preferred method, but it doesn?t work for all Office objects and will only work if you use early binding. As a result, you may need to resort to CreateObject and GetObject.
What CreateObject does depends on the situation. If you?re using late binding, it can be used to both create the object and open the application.
Set objExcel =
CreateObject(?Excel.Application?)
Furthermore, CreateObject gives you the flexibility to refer to an application on another machine. This situation occurs most frequently when you?re automating Access. In many companies, Access isn?t installed on all PCs, just on those that ?need? it. Cross-application VBA is a great way to run a report on a remote PC where Access is installed, and have the results pulled back into Word or Excel on the local PC, without the user ever needing to think about how to get the data. Because Access isn?t available locally, you can?t use New. Instead, you use CreateObject in its fullest form:
Set objAccess = CreateObject
(?Access.Application?, ?MyServer?)
You can also use CreateObject when you?re not sure what will be installed on the user?s PC because CreateObject fails in a way that you can trap and handle gracefully - but more of this later.
GetObject can be used as long as the automation object is already running, and it has two forms. The first uses the instance and opens an existing document:
Another place where it?s worth using GetObject is if you want to combine the actions of returning a reference to the application and opening a specific document at the same time.
So how do you decide between New, GetObject, or CreateObject? Unfortunately, there isn?t a single answer as it depends on what the user?s been up to. A major problem with cross-application VBA is that you can?t be sure what the user already has open, so you could end up opening an instance of an application when the user already has a copy open that you could simply use. However, you can?t rely on this, so you have to work checking if the object is already open before you attempt to re-open it.
If you attempt to use an application that isn?t running, VBA returns an error code of 429. You can?t guarantee the environment your application will be running in, so the only way you can ?neatly? open the application is by handling this error:
Dim blnIOpened As Boolean
On Error GoTo failed
blnIOpened = False
Set objExcel = GetObject
(, ?Excel.Application.9?)
If Err.Number = 429 Then
Set objExcel = CreateObject
(?Excel.Application.9?)
blnIOpened = True
End If
Just because the application is open, it doesn?t mean that it will appear on screen. In order for the application to show up, the Visible property must be set to True. If you used GetObject then the Visible property will already be set to True, but if you used CreateObject it will be set to False.
There is, however, a small snag as you can only write to the Visible property when the UserControl property is False. UserControl is True if the application was opened by the user rather than your code, and in these circumstances the Visible property becomes read only. You could also have problems if the application is already visible but the user has decided to minimise it, or if the user has opened another instance of the application.
The popularity of Office means users are more likely to take over the controls. UserControl can?t be changed by your application, but it does tell you what?s happening.
Having made the object visible, you can now (finally) begin using it, and this is where things get really easy. Once it?s active, all the standard methods of the object are available and you can use the Object Browser to see the methods of each application. Furthermore AutoComplete is active, so you don?t have to remember the exact syntax.
It?s worth remembering that you aren?t limited just to the methods and objects provided by VBA. You can also use the native functions from applications such as Excel and Outlook. Excel, for example, provides the WorksheetFunction object for this purpose.
However bear in mind that the functions provided with VBA don?t necessarily use the same algorithms as their equivalent in the application itself. The ROUND and MOD functions, for example, work differently in VBA and Excel, and can give different results - something which is not properly documented.
So now your application is motoring along, impressing the users with the impressiveness of the interface and facilities. Brilliant, but there?s just one final hurdle to jump. The problem is this: your code is sitting in - say - Access while your users are off playing in PowerPoint, watching your brilliant presentation, or analysing the data in Excel. How will you know when they?ve finished?
The easiest way is to count the open documents. If there are no documents open in the other application, or if the number of open documents is the same as when you started, then hopefully the user has finished and you can pull control back into the application where your code resides:
Loop Until .Presentations.Count = 0
appExcel.Quit
End If
For example, if you?re working with PowerPoint, put a command button onto the last slide, with code along the following lines:
With Application.Presentations
(?DNJ.ppt?)
.Saved = True
.Close
End With
End Sub
The trickiest bit about cross-application VBA is finishing things neatly. Unlike environments where you have complete control, your users will feel familiar enough with Office to open their own applications, create documents or worksheets, or shut down items they see as unnecessary.
We?ve looked at some ways to guard against that. When you?re closing the application down, the main thing is to try to leave the machine in the way you found it. Close the instance if you originally created it, and close the automation object
objPP.Quit
End If
Set objPP = Nothing
One final point of advice. The main problem with cross-application VBA is a slow degeneration in performance over time. In most cases this is caused by not closing instances, but even closed instances seem over time to slow the system. There are a number of things you can do to minimise these problems. For a start, don?t chop and change between applications just for the heck of it - keep calls down to a minimum and you?ll minimise the performance drop.
Having eulogised about VBA for this entire feature, you might be wondering why I?m now going to suggest another development environment. However it is worth remembering that you can make use of Office applications from environments like Visual Studio. Why not reference the Excel object library from your Visual C++ application? Or use the Access query language from Visual Basic? You?ll keep the benefits of working in a language you?re familiar with, and still have access to all the benefits of Office.
So Visual Basic for Applications is a good environment to work with. The program editor is familiar to anyone who?s used to Visual Studio, you get all the benefits of auto-completion of code and the object browser, and all the facilities of Office and hundreds of other applications that will save you oodles of time and effort. Go out and use it!
Binding is the process whereby one application finds out about the objects, properties and methods exposed by another application. When you dimension objExcel as an Excel.Application, Word (if that?s where you?re working) goes off and finds out what Excel has to offer. When you dimension the object as a specific type of application, VBA finds out at design time what?s exposed by the application, so you can then define variables that you?re going to use as specific types - an Excel chart, an Access recordset. Because the querying is carried out at design time, there?s less to do at runtime, so your code runs faster.
Late binding, by contrast, waits until runtime to discover what objects are exposed, and the details of their properties and methods. Late binding might be necessary if the application you?re going to use doesn?t support early binding, but the main Office applications support early binding, so use early binding. The only time you won?t be able to use early binding with Office is if you?re coding in JavaScript or VBScript, because they don?t support it.
Click here for our Privacy Statement. Copyright © Matt Publishing. All rights reserved. No part of this site may be reproduced without the prior consent of the copyright holder.
