It is hard to think of a more divergent pair of topics to write about than Microsoft Word and the text formatting language in Pick called Runoff. This article will bring these to products together. Hopefully I have not lost too many of you just by mentioning Runoff in the first paragraph. Runoff was good in it's day but now there are many better tools available, if only one could get all the existing documentation converted, first. Hopefully at the end of this article there will be no more need for anyone to continue to use Runoff for documenting a Pick style system. In our industry there are volumes of documentation for legacy systems in Runoff and Jet, using VBA with Word, these documents can be quickly brought into Word and made more presentable. There are many more features in Word than in Runoff. Word is more efficient to use and there are more people who know Word than Runoff or Jet.
This article is about using Microsoft Word and the embedded programming language called Visual Basic for Applications (VBA). VBA is a powerful, full featured language that is now part of all the Microsoft Office Applications. VBA has replaced all the old macro languages that were part of old style spreadsheets and word processors. Each application in Office 97 has it's own object model now that defines the components for documents and allows the VBA language to easily manipulate a document. For the old "Ed/AE" guru's reading this, think of VBA for Word as an editor pre-store on steroids, blood doped and using performance enhancing drugs, except VBA is quite legal.
Formatting long documents in any word processor is a laborious task full of frustration. For a long time, I found it difficult to use Word for long documents. I often wondered if the dirty little secret in Microsoft world was that authors who wrote books on Word were actually using WordPerfect or some other editor. But, with Office 97 and NT, I have to admit being able to edit long documents without frequent out of memory issues, corrupted documents and crashes.1
Why should you have to resort to programming in a language inside a word processor? Well, in short to save time. Documentation is just another form of data, normally we write programs to manipulate data, documentation is not really any different. Very few people who use Word ever delve into the macro features. As a result, they can end up spending many hours modifying documents manually that could have been done in few minutes/seconds with a macro. Using Word without using VBA, is like using any Pick application without developing some custom reports. Being able to quickly write a program that saves hours of labor and automates a task is a great advantage.
Managing long documents means many hours of scrolling back and forth jumping to locations and making repetitive changes. If you use the VBA language to automate some of these functions editing long documents becomes infinitely easier and more productive. There are a lot of things that can be done quickly with VBA that would normally require time consuming manual editing. This is especially true when the text formatting is being modified instead of just words. In example 1, I have a simple VBA macro that loops through a Word document that was imported from a Runoff document . This macro looks for the ".BP" string in Runoff for the page break and instructs Word to place a Page break before the paragraph. The Runoff language has many commands and every programmer seems to have used Runoff differently, so writing a single Runoff to Word macro is out of the question (I think it could be done in about 160 hours/4 weeks). All of the commands in Runoff have a parallel function in Word. It only takes a little research into the Word Object model and a copy of your existing documentation to be able to use VBA to convert your documentation.
Sub Example1()
'
' Macro created 07/09/99 by Tom Packert
'
Dim para As Paragraph
For Each para In ActiveDocument.Paragraphs
If para.Range.Words.First = "." Then
If para.Range.Words(2) = "BP " Then
'Display a message box that we did this because this is a sample
MsgBox "Page Break Inserted"
'then actually insert the page break
para.PageBreakBefore = True
'then delete the first two words
para.Range.Words.First.Delete
para.Range.Words.First.Delete
End If
End If
Next para
End Sub
This code may not make any sense to someone who has never seen an object oriented version of Basic before. In VBA, the objects do most of the work for the programmer. The programmer just needs to know the Basic syntax and what objects are available. To someone familiar with coding Pick applications at the Basic level of Pick, without the help of a 4GL tool, this will seem as if you aren't writing enough code to do things. The best analogy I can think of to help is that you would have to imagine a set of subroutines for every account, file, item, attribute, value and sub-value in your application. In other words you don't write code to open a Vendor file and read an item anymore. You now just call (fire) the read Item method of the Vendor file object. Just like with a large Pick Application, it takes a while to learn the file names, relationships and attributes, with VBA the effort is on learning the Word Object Model.
Learning the Word Object Model is a daunting task because the model is huge. Fortunately, the VBA Integrated Development Environment (a very fancy editor) is very helpful and can prompt you with plenty of information. You really need very little information to get started and to know that the F1 key bring you the help for almost anything. Also, most pick professionals know Basic already, so it is a minor adjustment to be able to use a slightly different syntax. For a Pick programmer, getting started in VBA is simply a matter of clicking on TOOLS-MACRO and creating a new macro. You might also want to pick up the books mentioned at the end of this article.
Another way to learn how to do something in Word is 'record' a macro. Recording a macro will create the executable source code in a macro from simply performing a task. You name the macro and then turn on recording, go perform the tasks you need and then click the stop recording button. Then, you can go edit this macro to see what it did in VBA code. Normally a recorded macro is quite verbose and only does a very specific function so the real world usefulness of recorded macros is limited, but recording a macro is a good way to learn how Word and VBA work.
In example one, I used a powerful feature of VBA, the FOR EACH loop. The FOR EACH loop is many times more efficient than the plain FOR/NEXT. The FOR EACH loop in example 1 can be written using a plain FOR/NEXT loop but with a long document, the FOR/NEXT will take hours to run whereas the FOR/Each Loop will take only minutes to run. Because of the hidden implementation of objects, similar lines of codes can produce dramatically different performance levels of macros.
For/Next Example
ImaxPara = ActiveDocument.Paragraph.Count
For IndxPara = 1 to ImaxPara
Next IndxPara
In order to use Word to document a Pick application efficiently. Somehow a bulk of the text needs to be created directly by the system. Every system is different, but if you are using some sort of a 4GL or other tools, you should be able to output the screen images and then create some form of basic Field prompts from the parameters in your tool's files. I have developed tools for my Pick applications that read the programs and screen parameters to output the text with embedded HTML tags or Rich text Format command (rtf).
Getting some text output or having existing Runoff or Jet documentation is a step in the right direction, but it is still labor intensive producing a final document using Word. This is where VBA comes in. VBA allows a person to write macros that can conditionally format a document in ways many times more sophisticated than simple search and replace of text can. Unlike search and replace, VBA can adjust headings, footings, insert bookmarks, insert text boxes, frames and pictures, insert keywords, for indices and headings for Tables of Contents. I prefer to place all my screen images inside Text Boxes in Word. To do this by hand takes about 1 minute per screen. But a simple VBA macro can identify the screen image, lift the selected text, insert a text box of exactly the right size and paste the screen image into the text box.
VBA also allows you to implement all the Windows type prompt boxes to prompt the user for information and then disseminate this information throughout a document. For example, you can develop a data entry form which allows the user to enter information, then click on the OK button and fill in a document, rather that forcing the user to move around a formatted document like a fax cover page. This makes the user feel more like they are using a Windows application rather than just using a formatted Word document.
If you are not familiar with Word, then the above features may not mean anything to you. Please trust me, they are important and save you a lot of time in the documentation process. In order to illustrate some formatting that would need to be done by hand that can not be done my a Search and Replace command, I created example 2. The macro in example 2 loops through all of the paragraphs and all of the words in each paragraph. If the first two letters of the word are "DP" then the color of the word is changed to RED. Notice that the assignment of the color uses the variable wdRed. This is part of the Word VBA object model. Thousands of these enumerated constants are declared within Word and are available for use to make the code more readable.
Sub Example2()
'
' Macro created 07/09/99 by Tom Packert
'
Dim para As Paragraph
Dim myword As Words
For Each para In ActiveDocument.Paragraphs
IwordCount =
para.Range.Words.Count
For Indx = 1 To IwordCount
If
Left(para.Range.Words(Indx), 2) = "AP" Then
para.Range.Words(Indx).Font.ColorIndex
= wdRed
End If
Next Indx
Next para
I hope I have piqued your curiosity about the power of VBA and Word. The topic is huge while the space for this article is small. Because VBA is embedded in all the Office applications, like Excel, you can use VBA to enhance all of the applications. VBA combined with Excel is an especially powerful combination. Many things can be done in VBA for Excel that make moving data in and out of Excel more efficient and make Excel an integral part of any information environment. I have used Excel Templates with VBA along with menu driven downloads to automate spreadsheets for users to analyze transaction data. On the template, there is only a single large button, when the user presses the button. The VBA code opens the downloaded data, sorts it , filters it, and creates multiple pivot tables. The user can then answer a large variety of questions about the data using the pivot tables but the user does not need to be an Excel expert.
The best part of learning VBA is that learning it is not a waste of knowledge. It can solve your immediate problems with documentation, Word and Excel but also gives you an introduction to VB and Object Oriented applications. Moving up to VB to develop applications is a logical next step. The concepts you learn in VBA will transfer directly to any VB environment. While the merits of VB applications in general are a bit controversial, it is easy for everyone to agree that with a knowledge of VB/VBA you will not be struggling to find work.
I realize that I have made very general statements about this rather large topic. If anyone has comments or questions about this article or documenting their application, please feel free to contact me via email at tom@phase1systems.com.
1- Note: It is still not advisable to attempt using the master/sub document features in Word. The word on the information street is that the common problems caused by attempting to use master/sub document features is not the fault of users, rather the fault of the application. Numerous books on Word still devote pages and chapters to master/sub documents. My advice is to skip those chapters and edit long documents and/or use reference documents.
Want to learn more? Check out these books from O'Reilly.
1. Learning Word Programming 1998 - Steven Roman - O'Reilly & Associates, ISBN - 1-565592-524-6
2. Writing Excel Macros - 1999 - Steven Roman - O'Reilly & Associates, ISBN - 1-565592-587-4
3. VB & VBA in a Nutshell - 1998 - Edited by Ron Petrusha - O'Reilly & Associates, ISBN - 1-565592-358-8
Or monitor these Usenet Newsgroups:
1. Microsoft.public.excel
2. Microsoft.public.excel.printing
3. Microsoft.public.excel.programming
4. Microsoft.public.word
5. Microsoft.public.word.word97vba
6. Microsoft.public.word.formattinglongdocuments
7. Microsoft.public.word.pagelayout