use strict;

# This turns off output buffering, so output is immediate.
$| = 1; 

# Load Wordnet
use WordNet::QueryData;
# Adjust location as needed
my $wordnet = WordNet::QueryData->new("wordnet.db"); 

# This reads lines from STDIN.  If there is no input, 
# it waits.
while (my $input = <>) {
    # Remove the newline from the end
    chomp $input;

    # Here we have to think about a protocol.
    # I suggest something of the form command;arg1;arg2
    my ($command, $arg1, $arg2) = split ';', $input;

    my @results;

    # Dispatch based on command
    if ($command eq 'quit') {
       exit;
    } elsif ($command eq 'word') {
       # Do a WordNet queryWord
       @results = $wordnet->queryWord($arg1, $arg2);
    } elsif ($command eq 'sense') {
       @results = $wordnet->querySense($arg1, $arg2);
    } else {
       die "Unknown command $command";
    }

    # OK, send the results back out over STDOUT
    my $line = join ',', @results;
    print $line . "\n";
}
[download]

  perl.exe wordnet-ipc.pl
[download]

Looks great.

Like you say, I'm not sure whether VBA can directly pass parameters to an already running perl program. This is definitely a start, though.

Can Perl take its STDIN from another text file / script? I can easily make VBA dynamically write a small text file, and if Perl is setup to dynamically (since the Perl script will keep running until I kill it) read that same text file for its parameters, it just might work. Perl would keep looping and reading the text file until VBA changed it, then Perl would spit out some different output and VBA would read it (I think reading dynamic output from an already running Perl program is easier than writing to it).

That's a good idea. It would have the downside that you'd have to flag the other process when you're done writing, and I think you'd have some headaches there. You'd probably have to introduce a waiting period - like, sleep for a second, check for file, read it, write out results, etc. Those seconds can add up.

Fortunately, I finally sat down in front of Excel and here's what I whipped up. It actually works, which stunned me.

First, in your VBA project, go to Tools->References, find 'Windows Script Host Model', and check the box.

Next add this VBA code, or something like it:

Option Explicit

Dim perlProc As WshExec
Dim perlIn As TextStream
Dim perlOut As TextStream

Private Sub Workbook_Open()
  Dim wsh As New WshShell
  Set perlProc = wsh.Exec("C:\perl\bin\perl C:\perl\play\wordnet-ipc.p
+l ")
  Set perlIn = perlProc.StdIn
  Set perlOut = perlProc.StdOut
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  perlProc.Terminate
End Sub
[download]

This handles starting and stopping the wordnet-ipc script, which can basically be as above. You should see a DOS box appear when you open the worksheet, if it's working right.

You could add a sub to restart the perl process if it dies.

Next step is to hook in your functions in VBA....

Function doQueryWord(aWord As String, aRole as String) As String
  ' Construct a command in the simple protocol wordnet-ipc expects
  Dim cmd as String
  cmd = "word" & ";" & aWord & ";" & aRole

  ' Send the command to the waiting perl script
  perlIn.writeLine cmd

  ' Read the output from the perl script.  This will block until outpu
+t appears.
  Dim results As String
  results = perlOut.readLine

  ' Return results.  You could split on commas - not sure what format 
+you need.
  doQueryWord = results
End Function

' doQuerySense would be nearly identical
[download]

That should do the trick. I haven't tried it with WordNet, as I don't have it installed on my windows box, but I did write a simple Perl print-execute-wait loop, and it worked fine.

best of luck! --Clinton

To apply a nomenclature that may help understand clwolfe's idea consider the Perl application as a server and your VBA code as a client. The overall process is a little like using OLE to allow one application (your VBA code) to connect to another application (the Perl code).

In fact it may be that using OLE is exactly the solution you are looking for. It is not a 'free' option however. ActiveState provide tools for doing the heavy lifting as part of their pro package. See OLE automation server and Creating a COM/OLE server for some discussion of this option.

OLE / COM does sound like exactly what I need, but anything non-free (barring paltry sums... say less than $20) really is out of the picture for now. This is primarily a pet project, not a necessity, and I can't think of a time when I would need to use Perl again.