(Originally published in 2002. )
I'm coauthoring a book about the Ruby programming language (Beginning Ruby Programming, from Wrox Ltd.), and use Microsoft Word on Windows 2000. Over the years I've spent a fair amount of time writing VBA macros for Word, even going so far as to write a suite of classes for exporting Word docs as XML. I like having common routines automated, and have acquired a number of macros. Typically, I use the macro recorder to capture the general behavior, and then use the VBA IDE to tweak the results.
VBA is essentially Visual Basic, coupled with an IDE, and built into various Windows (and Office for Macintosh) applications. I like it well enough, having spent much time writing Visual Basic programs. You can call Win32 libraries, or script ActiveX DLLs, as well as manipulate office applications. However, I would now much prefer to write the macros in Ruby.
There is a Ruby application called RubyCOM that exposes Ruby classes as COM automation objects, and I tried using that inside of VBA. Unfortunately, I ran into some problems I've yet to solve. However, there is another way to exploit Ruby inside of Word macros, and that's through the use of theShell function. This is perhaps not quite graceful; you write some or most of the code in a Ruby script, and call the script using Shell, much as you would call it from the command line. Shell does not let you grab the output. You need to redirect text to a temporary files, and have the VBA code slurp it in. Still, if you can live with some degree of hackishness, you can make good use of Ruby.
Microsoft Word has a spelling checker, and a thesaurus, but it does not have a dictionary. It would be nice to be able to highlight a word and find out the meaning. There are a few web sites offering free dictionary services, such asdictionary.com andm-w.com. Ralph Mason, the author ofRubyCOM, wrote a Ruby console app that takes a word from the command line, calls out to dictionary.com for the definition, strips the HTML from the results, and displays the final text in the console window. Very handy. I decided I wanted to be able to call this from Word, so I set out to write a VBA wrapper routine.
Spawning an application from VBA is simple. You useShell, like this:
To execute a Ruby script, you might do this:
This actually calls the command interpreter (cmd.exe), passing it the name of the Ruby script. The second parameter to Shell says to hide the console window (as oppose to, say, simply minimizing it).
The/C switch tells the interpreter to execute the command, and then quit. If you wanted to keep the command interpreter running, you would use/K. (You can get full list of switch by runningcmd /?) All well and good, calling a Ruby script from VBA is no problem. Just pass the name of the script, use output redirection if needed, and have the VBA code grab the results.
Well, this is almost correct. The problem is thatShell executes asynchronously. Hence, if you have subsequent code relying on the results of the spawned script, there is no guarantee the Ruby code will have completed by the time the remaining VBA code runs. I tried a few things myself, little loops that checked for file existence or error codes, but found a much better solution on the MSDN (Microsoft Developer Network)web site. There is anarticle, "HOWTO: 32-Bit App Can Determine When a Shelled Process Ends", that explained how to track a processes ID (available as the return value fromShell), and gives complete code for a betterShell function. The betterShell, calledExecCmd, takes the name of a shell command, but does not return until the called process is done. Presto: synchronous code.
Here is the code, taken from that article. All copyrights for this part remain with Microsoft, and I'm presenting it here only for convenience.
The original code had various line breaks, which didn't travel well when I did a cut-n-paste. I removed them, so the code here may have bad line wrapping. I'm not going toexplain this code, other than to say it declares the use of various Win32 library functions for tracking a process. Read the MSDN article for more details. However, you don't need to understand it to use it.
The next part is the betterShell:
Good. Now comes theDictionary routine, which calls a Ruby script and displays the results in a user form text box. (You'll need to create a form, with a text box, for this macro to work. The user form is nameduser_form; the text box is namedtext_box. Clever, no? Set the text box to multi-line, with vertical scrolling. I also set the border to single-width, since I make no provisions for form resizing. The caption is set by the macro, so it doesn't really matter what it is in the IDE.)
The subroutine itself is pretty sparse:
The sub starts by defining a few constants and variables. The output of the Ruby script will go into a file in the system temp directory; theEnviron method lets the code look up environment variables, in this case use TEMP. You may prefer to have the output text go someplace else, but in any case using an environment variable is a good way to keep the code portable.
If there is an existing temp file, it gets deleted:
(This is one of those places I start thinking, Oh how simpler the Ruby code would be.) A command string is built from the selected text and the name of the temp file; the string is passed toExecCmd for execution:
Note that the code does not use a fully qualified path to the Ruby script. That's because I've previously defined a particular directory on my machine for scripts, and have added this to my PATH environment variable. Any script in that directory can be called from any command prompt as if it were a native Windows command. If you don't do this then you'll need to provide a full path to the Ruby file.
Next, the output file is is read into a local variable:
Finally, the form is displayed. The caption is set, the text loaded, and the form is made visible. The selection point set to the beginning of the text box in order to scroll the text back to the top. Without this, the form displays with the text scrolled to the very end, and you have to manually scroll to the top to read it.
The call toReplaceEntities swaps out certain HTML character entities in favor of the actual characters. You might want to add to this as you encounter additional characters, or change the Ruby script to do more HTML cleansing. But there may be a difference in what the console window can display, and what Word renders.
Ralph Mason wrote the original Ruby code that does the useful part. I reorganized the code to provide a class one could reuse in other programs.WebDictionary does a lookup on www.dictionary.com. The results are reformatted and returned as plain text.
That's it. I put this code inside of mynormal.dot template, and assigned a keyboard combination to the macro. I can now highlight a word, pressCTRL+ALT+W, and get a pop-up window with the definition:
Ralph Mason's Ruby dictionary script:http://www.geocities.com/masonralph/ruby.html
Microsoft Knowledge Base Article - Q129796HOWTO: 32-Bit App Can Determine When a Shelled Process Ends
Download the code:RubyVba.zip
Disclaimer: I've presented this article and code for informational purposes only, and make no claims for its correctness or suitability for any tasks whatsoever. Use this information at your own risk. The article text is owned and copyrighted 2002 by James Britt, and may not be reproduced without permission., TheDictionary andReplaceEntities VBA macro are copyrighted 2002 by James Brit, and released for public use under the GPL. All other code is owned and copyrighted as noted, and all rights remain with the original creators.