Visio and VBA -- PART - II - AuthorKen Getz and Mike Gilbert

Visio

Although it may seem daunting to have to learn a new programming environment (and really, it’s very simple – Intellisense, as you’ll see, makes all the difference), once you learn Visio’s development environment, you’re going to be comfortable in any application that hosts VBA. Because all the keystrokes, the tools, and the language are all the same, it takes little time to get going in a new environment.

As you know, things aren’t nearly as rosy as marketing-speak would make them seem. The programming tool is only a small portion of using a new product. The big hurdle is the application’s object model. There’s no substitute for digging in and learning how an application works, as well. These two issues: the object model, and the application’s UI, are crucial for writing applications that work in any environment. Visio is no exception (and perhaps, it’s the rule – its interface is awfully idiosyncratic), so be prepared to dig in and study its object model before and during your application development. Of course, as you’ll see, IntelliSense will come to the rescue.

Word Completion

This one’s a bit less discoverable, because you must press a special key to make it happen (Ctrl+Space), but it makes typing much faster. Once you enter enough characters to uniquely identify an object or keyword, you can simply press Ctrl+Space and have VBA provide the rest. If the choice is ambiguous, it’ll drop down the list as shown in List Properties/Methods illustration above. Otherwise, it completes your word for you. For example, if you type "Msg" and then press Ctrl+Space, it’ll fill in the "Box" (giving you "MsgBox", of course) automatically.

Two’s Better Than One

Unlike Visual Basic 3 and 4, VBA supports multiple projects open in the VBA environment. This allows you to work on multiple-document applications and add-ins, without having to load two separate copies of VBA. In addition, this makes it possible to drag and drop forms, modules, and class modules between projects. No more importing! Just open both projects, and drag the modules where you need them.

Drag ‘n’ Drop Code

Want to move a procedure from one module to another? Need to copy a chunk of code to another procedure? Either of these tasks can be accomplished using drag and drop in the VBA editor.

Don’t Lose Your Place

All to often, you want to leave your current location, go somewhere else in the code, and then return to your previous location. To make this easy, VBA supports bookmarks. You can toggle a bookmark, move to the next or previous bookmark, or remove all bookmarks from the Edit toolbar.

References are Everything

In order to allow you to code to objects outside the current host application, most ActiveX components that can be controlled provide a type library. This file (normally with a TLB extension) provides information for ActiveX components that need to use the functionality exposed by the server component. The type library contains information about constants, using the help file associated with the application to find out about its objects, as well as information about the methods, and properties of the objects. In order to use this information, however, you’ve got to tell VBA where to find it. To do so, you’ll use the Tools > References dialog, shown below.

Creating Objects

Once you’ve created the necessary references, you can use code like the following to instantiate variables that point to the different objects:

Dim oExcel As Excel.ApplicationLater, when you need to use the Excel Application object, you can use code like this:

Set oExcel = New Excel.Application

Finally, when you’re finished using oExcel, make sure and set it to Nothing, thereby releasing the reference count and allowing Windows to remove the object from memory:

Set oExcel = Nothing

You can also create and instantiate the variable at the same time:

Dim oExcel As New Excel.Application

This isn’t recommended, however, because you have no control over the time at which the application gets started. By explicitly assigning the variable to be a new instance of the application when you need it, you maintain control over when (and if) the object comes to life. This technique is called "early binding", because VBA can look up the properties and methods of your object at compile time.

Note: Multiple ActiveX components expose objects with the same name. For example, both Excel and Visio expose a Cell object. In your code, within Visio, you can refer to a Cell object, and its use will be unequivocal. If you then take the same code and run it in Excel (and you should be able to do this), it will fail. Excel will think you’re referring to its Cell object, and your code will not do what you'd like. To avoid this problem, always provide complete object references in your code. That is, always include the application name (as in Visio.Cell, or Excel.Cell) in any object declaration.

Early vs. Late Binding

You may find that some applications don’t allow you to create variables that refer to specific objects within their type libraries—Schedule+ 95 was one such ActiveX server component. In that case, you’ll have to write code something like this:

Dim oBad As Object
Set oBad = CreateObject("BadApp.Application")

This technique causes your code to run much slower than it might otherwise, however. Because VBA can’t look up, and bind to, the methods and properties of your object until run time, and it must do so each and every time you run your application, you pay a stiff performance penalty. This technique, known as "late binding," causes your code to run slower, and should be avoided if possible.

Note: You can use CreateObject in early binding scenarios, as well. It gives you the most explicit control over when your objects come into being. You’ll want to investigate the GetObject function, as well, which attempts to retrieve a reference to an existing object in memory, as opposed to starting a new instance of the application.

Object Browser

The Object Browser (press F2 to get it started) allows you to investigate the objects, constants, methods, and properties of all the applications for which you’ve set a reference (using the Tools > References dialog), and for all the ActiveX controls you’ve added to your project. Image below shows the Object Browser in use.

You can peruse the objects in the browser, working through objects in the left pane, to the objects, methods and properties of the selected object on the right. Working from left to right, and back over to the left again, you can investigate the hierarchy of objects which makes up the object model of any application.

.
Surgery Through a Straw

OK, so it’s not so bad any more. Debugging in early versions of Visual Basic always seemed as productive as performing major surgery through a hole the size of a straw. Things have improved a bit, and VBA supports the full complement of break points, watch points, conditional watch points, and the wonderful Quick Info tips. The Locals window (shown below), allows you to view all the variables that are currently in scope, as you’re debugging your code

One useful tip: you can alter the value of any simple variable, from the Locals window. Simply select the Value column, and enter a new value. If you’re debugging and want to modify the value of a variable, this is certainly a convenient way to do it.

PART - II Click here