Microsoft Office Developer is a special version of Office designed for IT professionals who do a lot of VBA work but build their solutions in Office rather than straight Visual Basic. Even if you aren’t a professional code jockey, this version of Office may be something you’ll want to consider. In fact, there’s one feature, the Code Librarian, that I think may justify the purchase of Developer on its merits alone. Read on, and see what you think.

All features great and small
Before we look at some of the neat features in Office Developer, let’s clear up one thing it doesn’t have—a newer or better version of Office proper. The releases of the Office applications themselves are the same as those you get with Office Premium.

On the development side, though, you get a slew of features, tools, and add-ins. Some are small and/or obscure; others you’ll use every day. For the record, here is a list of all the Office Developer features, although I won’t cover all of them here.

Let’s get professional
I’m always appreciative of the IT professional who takes the time to insert a comment block at the top of a procedure documenting the purpose, editing date, variables, and history of the procedure. With Office Developer, though, it’s so easy to insert such a block that I’ll have to stop being impressed. By simply clicking the Code Commenter on the Add-Ins menu in the VBA Editor, you get the dialog box shown in Figure A.

Figure A
If you don’t see Code Commenter in the available add-ins, it’s either not installed or not loaded. See Where Are My Tools at the end of this article for more information.

You can add comments to the current procedure, all the procedures in the current module, or all procedures in the current project. The comments are based on a simple text template, which you can edit (more on this in a moment). If you want, you can browse to a new template, or take a look at the current template before you use it.

When you click OK, the Code Commenter inserts a comment block that looks something like this:
‘ Code Header inserted by the Procedure Header Add-In
‘=============================================================
‘ ThisDocument.junk
‘————————————————————-
‘ Purpose
‘ Author : Bruce Maples, 08-17-2002
‘ Notes :
‘————————————————————-
‘ Parameters
‘———–
‘
‘————————————————————-
‘ Returns:
‘————————————————————-
‘ Revision History
‘————————————————————-
‘ 08-17-2002 WBM:
‘=============================================================
‘ End Code Header block

Once the block is in place, it’s up to you to fill in the blanks for Purpose, Notes, and the Revision History.

If you click Show Template, you get a view window where you can examine the current template (Figure B). To modify the template to include the comments and fields you want in your projects, open it in Notepad and change it as you see fit.

Figure B

Error Handler
Another mark of the IT professional is consistently applied error handling. Many IT pros have standard error-handler code that they routinely insert by hand in a procedure. The Error Handler add-in works the same as the Commenter to add a basic error handler to one or more procedures. If you change the template, the add-in will also go back and update all error-handling blocks it inserted to match. Here’s an example of the basic error-handler that you get out of the box:
On Error GoTo HandleErr
ExitHere:
    Exit Sub

‘ Error handling block added by Error Handler Add-In.
‘ DO NOT EDIT this block of code.
‘ Automatic error handler last updated at 08-17-2002 07:22:02   ‘ErrorHandler:$$D=08-17-2002    ‘ErrorHandler:$$T=07:22:02

HandleErr:
    Select Case Err.Number
        Case Else
            MsgBox “Error ” & Err.Number & “: ” & Err.Description, _
            vbCritical, “ThisDocument.junk”
            ‘ ErrorHandler:$$N=ThisDocument.junk
    End Select
‘ End Error handling block.

Let’s get safe
For years, IT professionals have copied files to floppies, other directories, and network servers in an attempt to make sure that their work was safe in case of disaster. In addition, IT pros working in teams have had to come up with ways to share their code while ensuring that everyone on the team had the latest version.

Some years ago, Microsoft came up with a solution to both these problems: Visual SourceSafe. SourceSafe (included with Office Developer) is essentially a database of documents. You can store almost anything in it: the Word document that is the project charter, the Excel spreadsheet of the schedule, and the code itself. The SourceSafe server lets you add keywords and comments to whatever you’re storing. Then, if you or someone else on the team wants to work on a particular piece of code, you “check it out” of the SourceSafe library. When others browse the database, SourceSafe shows that particular code segment as checked out to you. Once you make your changes, you check the code segment back in to SourceSafe, and your newly improved version is immediately available to all.

All of the code segments and documents stored in SourceSafe are kept in a single database file, which makes it simple for network administrators to provide backup support for your code. Simply back up the SourceSafe database, and you’ve backed up all the code and documents inside it.

Sounds neat, right? If you develop in a team environment, it’s more than neat: It’s invaluable. But until now, there was not an easy way to integrate your VBA work with SourceSafe. You had to save the document containing the code, then start the SourceSafe client and manually add the document to SourceSafe. The only way to use the check-out/check-in feature was on a document-by-document basis.

Now with Office Developer, SourceSafe is integrated directly into the VBA Editor. Choose Source Code Control from the Add-Ins menu, and you can check in code, check it back out, and manage the SourceSafe database, all from within your editing environment.

Let’s do data and COM
As you grow in your knowledge and sophistication with VBA, you’ll eventually need to do more with data access. You might, for instance, want to pull contact information from a back-end database and drop it into a merge file for Word. Or, your application might need to use data from Access as the basis for a chart in Excel that you then publish on a regular basis to your intranet server.

Data access in regular Visual Basic has progressed over the years, moving from one methodology to another. The current standard is ActiveX Data Objects, otherwise known as ADO. While ADO isn’t particularly complicated, it does involve writing a large amount of code simply to return a recordset. To simplify the process, Visual Basic includes a control that, when dropped on a form, makes working with data much easier—the ADO Data Control. Now that this same control is available in Office Developer, all of us who build solutions in Office can have the same easy path to data access.

To work with ADO Data Control, simply add the Data Control to your toolbox, drop it on your user form, set the properties to connect to the record source, and add some bound controls. Run the form, and—voila. Of course, just like in regular Visual Basic, you’ll eventually want to learn to do your ADO data access through code. But if you’re new to ADO, the Data Control is a great way to get your feet wet and add functionality at the same time.

COM add-In
Another source of envy between VBA developers and regular VB developers has been the ability to compile your application. Since VBA depends on the functionality inherent in the Office application it’s working with, you still can’t compile a stand-alone executable in VBA. You can, though, build a COM Add-In in VBA.

A COM Add-In is a DLL file that you distribute to your users. It’s loaded with one or more Office applications and “adds in” functionality to the application. For example, you could build an add-in that would pull a set of records from a back-end customer database and create a merge file for Word. As an add-in, your VBA project is always available, no matter which document or template is open.

The COM Add-In Designer provides you with a built-in set of events and properties, such as the LoadBehavior property and the OnConnection event. Once you add the necessary code to have your add-in do what you intend, you can choose “Make projectname.dll” from the File menu in the VBA editor to compile your DLL.

Let’s learn from others
Now we come to a feature that for some people will be reason enough to get Office Developer: the Code Librarian. One of the best ways to learn to program is to read others’ code. The problem comes in finding the right code snippet you need at the time. Digging through online help can be tedious, and the Knowledge Base doesn’t always have what you need.

The Code Librarian is a combination of an Access database and a custom front end, accessible from within the VBA editing environment. Simply choose Code Librarian from the Add-In menu and pretty soon you’ll be browsing code samples to your heart’s content.

Suppose, for example, that you’ve decided it’s time to take off the training wheels (the ADO Data Control) and begin doing data access through ADO code. You fire up the Code Librarian, open up the “booklet” marked ADO, and you see a screen like the one that appears in Figure C.

Figure C

Inside the ADO section are subsections: ADO Samples, ADO Samples With Oracle, and Jet Provider. When you click on the first subsection, the right-hand pane will fill with a list box of the individual samples (actually, records in the database). When you choose, say, Using The ODBC OLE-DB Provider in the record pane (as I’ve done above), the lower right-hand pane—the Description pane—will show a text description of the code snippet.

If I then double-click the snippet name, I’ll see the actual code in a new tabbed window (Figure D).

Figure D

The Code tab, obviously, shows the sample code. The Description tab contains the description that you saw when you clicked on the name, and the Settings tab allows you to set which booklet your code sample is assigned to. Once you’ve opened the code sample you want, you can do some or all of the following with it:

  • ·        Use the mouse to select part or all of it and then copy it to the clipboard.
  • ·        Click the Insert Code To Module button to insert the code snippet into the currently active module in the Visual Basic editor.
  • ·        Click the Copy To Clipboard button to perform a copy-and-paste action.

What makes the Code Librarian really compelling is that it’s editable. You can collect, store, and organize your own code snippets or those of your team or company. Suppose that, after a two-hour research-and-test sequence, you’ve finally been able to build a connection string that your company’s SQL back-end server will accept. You think, “I don’t want to have to figure that out again,” so you create a new record in the code library, add the connect string, a name, and a description, and save it for future use.

Now imagine that the code library is not on your personal PC but is in a shared location on your network. When you figure out your connection string and share it via the Code Librarian, all the other folks on your team will have access to the same code. This feature alone is worth the price of admission.

Where are my tools?
If you’ve installed Office Developer but don’t see any of the tools under the Add-In menu, you may have to load them. Perform the following steps to make sure you have all the tools:

  1. 1.      Choose Tools | Add-In Manager (Figure E).

Figure E

  1. 2.      Click on the tool you want to use and select the Loaded/Unloaded option. (Or, simply double-click the tool name.) This should change the status of the tool to Loaded.
  2. 3.      If you want the tool to always be available, select Load On Startup.

Note that the Help Workshop and Answer Wizard Builder are stand-alone applications listed in their own sections of the Start menu.