Current album

Get it here ...

Ruby and Vba for Web Dictionary Searches

(Originally published in 2002. )

Ruby and VBA for Web Dictionary Searches

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:

Shell "cmd /C somescript.rb", vbHide

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.

' Code taken from the MSDN article, 'HOWTO: 32-Bit App Can Determine When

' a Shelled Process Ends'

' Microsoft Knowledge Base Article - Q129796

' http://support.microsoft.com/default.aspx?scid=kb;[LN];Q129796



cb As Long

lpReserved As String

lpDesktop As String

lpTitle As String

dwX As Long

dwY As Long

dwXSize As Long

dwYSize As Long

dwXCountChars As Long

dwYCountChars As Long

dwFillAttribute As Long

dwFlags As Long

wShowWindow As Integer

cbReserved2 As Integer

lpReserved2 As Long

hStdInput As Long

hStdOutput As Long

hStdError As Long

End Type



hProcess As Long

hThread As Long

dwProcessID As Long

dwThreadID As Long

End Type


Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long


Private Declare Function CreateProcessA Lib "kernel32" (ByVal lpApplicationName As String, ByVal lpCommandLine As String, ByVal lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As String, lpStartupInfo As STARTUPINFO, lpProcessInformation As PROCESS_INFORMATION) As Long


Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long


Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long



Private Const INFINITE = -1&

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:

' Code taken from the MSDN article, 'HOWTO: 32-Bit App Can Determine When

' a Shelled Process Ends'

' Microsoft Knowledge Base Article - Q129796

' http://support.microsoft.com/default.aspx?scid=kb;[LN];Q129796


Public Function ExecCmd(cmdline$)



' Initialize the STARTUPINFO structure:

start.cb = Len(start)

' Start the shelled application:

ret& = CreateProcessA(vbNullString, cmdline$, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&, vbNullString, start, proc)


' Wait for the shelled application to finish:

ret& = WaitForSingleObject(proc.hProcess, INFINITE)

Call GetExitCodeProcess(proc.hProcess, ret&)

Call CloseHandle(proc.hThread)

Call CloseHandle(proc.hProcess)

ExecCmd = ret&

End Function

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:

Sub Dictionary()

Const ForReading = 1, ForWriting = 2, ForAppending = 3

Dim def As String

Dim cmd As String

Dim word As String

Dim temp_file As String

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.

Set fs = CreateObject("Scripting.FileSystemObject")

def = ""

temp_file = Environ("TEMP") & "\dict.tmp"

If there is an existing temp file, it gets deleted:

FindIt = Dir(temp_file)

If Not Len(FindIt) = 0 Then

Kill temp_file

End If

(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:

word = Selection.Text

cmd = "cmd /C dictionary.rb " & word & " > " & temp_file

ExecCmd (cmd)

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:

Set ts = fs.OpenTextFile(temp_file, ForReading)

Do While ts.AtEndOfStream <> True

def = def & ts.ReadLine & vbLf



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.

user_form.Caption = word

user_form.text_box.Text = ReplaceEntities(def)


user_form.text_box.SelStart = 0

End Sub

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.

Function ReplaceEntities(str As String) As String

str = Replace(str, "&#183;", "ยท")

str = Replace(str, "&amp;", "&")

ReplaceEntities = str

End Function

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.



# Script to fetch a definition for dictionary.com, strip the HTML, and display

# the results in the console window. Original code written by Ralph Mason:

# http://www.geocities.com/masonralph/ruby.html


# Reorganized by James Britt


require 'net/http'


class WebDictionary


def look_up( word )

site = "www.dictionary.com"

page = "/cgi-bin/dict.pl?term=#{word}"


a = Net::HTTP.get( site , page )


res = ""

if ( a=~/No entry found for/ )

puts "No entry found for '#{word}' - Suggestions:\n\n"

while $' =~/<a href="\/cgi-bin\/dict.pl\?term=\w+">(\w+)/

res << $1



while a =~ /<!-- resultItemStart -->/

$' =~ /<!-- resultItemEnd -->/



res << $`.gsub(/<.*?>/m){ |i|

case i

when "<b>"


when "</b>"


when "<p>"












if __FILE__ == $0

if ARGV.size == 0

puts "Usage: #{$0} <some word> "



puts WebDictionary.new().look_up( ARGV[0] )


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:


James Britt


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.

My Music


American Electronic

Small Guitar Pieces

Maximum R&D

Love me on Tidal!!111!

Neurogami: Dance Noise

Neurogami: Maximum R&D