X.G.1.1 Programming the VBA-ruby bridge

The sources are made of three C++ “cpp” files, three C++ header files and one “def” file that defines the four functions that will be exported into the dll library. More precisely:

• Files “conversion.h” and “conversion.cpp” are devoted to type conversion between VBA and ruby. More precisely, the corresponding “convert” C++ methods perform conversions between VBA “Variant” and ruby “VALUE” types. Note that conversion are done from Variant to VALUE, or in the other direction. Some “convert” methods from Variant to other C++ types are also defined for internal use only.
• “win32ole.h” and “win32ole.cpp” define methods that are called from “conversion.cpp” functions. They contain specifics to the conversion of COM automation types. The reason why these methods have been kept separate from the conversion ones, is that the bits of code in our “win32ole” files have been extracted from the corresponding sources of ruby “win32ole” extension.
• Files “marshal.h” and “marshal.cpp” contain the definition of methods that dispatch the calls from VBA to ruby. In particular, four methods that are meant to be exported in the dll library are defined here.
• File “marshal.def” exports the methods that will be used in excel: “RubyInit”, “RubyFinish”, “RubyRequire”, “RubyLoad” and “RubyCallMethod”. The “RubyInit” method has no argument and simply initializes the ruby runtime. The “RubyFinish” method is used to quit ruby interpreter and unload the bridge library. Methods “RubyRequire” and “RubyLoad” can be used to load the ruby programs that shall later be called from VBA. Each of these two methods has one argument corresponding to the name of the ruby file to be imported. Finally, method “RubyCallMethod” is the one that really performs the dispatch between VBA and ruby. The method has four arguments that correspond to the methods receiver (module or class name), the name of the method, an Array containing the arguments, and the Variant in which the value returned by the method shall be stored. The four methods listed above return an integer containing the error code.

  Note that the four arguments of the method “RubyCallMethod” are pointers to VARIANT objects:

      extern "C" int __stdcall RubyCallMethod(const VARIANT *objName,  
              const VARIANT *methodName,const VARIANT *args,  
              VARIANT *ret) {  
          ...  
      }

One remarks that the bridge between VBA and ruby implicitly assumes that the ruby “win32ole” extension is present. (This extension is required in the “RubyInit” method.) It was not a priori mandatory. However, the early loading of “win32ole” extension helps the programming of the for COM automation types translations. As the manipulation of these types by ruby programs might be necessary, it is necessary to load the extension as early as possible. This means that the ruby distribution with which the bridge is used must contain the “win32ole” extension. (Of course, the bridge should be used only on Windows platforms on which Office is installed.)

The compilation of the library is straightforward. On my computer, it looks like this:

Note that the “vbaruby.dll” is linked to the “msvcrt-ruby18.dll” dynamic library. This means that the ruby runtime library is automatically loaded into excel when “vbaruby.dll” is loaded. This also means that “msvcrt-ruby18.dll” must be located in a directory defined in the “PATH” environment variable. Also, the “vbaruby.dll” library is linked to a particular version of ruby runtime library. If another version of ruby is installed, the bridge may have to be re-compiled.