“RubyMarshal” VBA module

This VBA module performs the loading of dynamic libraries, and defines methods that can be called from anywhere in the VBA code and that dispatch the calls to corresponding ruby methods. The code begins as follows:

    Const vbaRubyLib As String = _
        "D:' SHARED' FERESPOST' SRC' OUTPUTS' VBARUBY' vbaruby.dll"
    ’
    Private Declare Function LoadLibrary Lib "kernel32" _
        Alias "LoadLibraryA" (ByVal lpLibFileName As String) As Long
    Private Declare Function FreeLibrary Lib "kernel32" _
        (ByVal hLibModule As Long) As Long
    ’
    Public Declare Function RubyInit _
        Lib "vbaruby" () As Long
    Public Declare Function RubyFinish _
        Lib "vbaruby" () As Long
    Public Declare Function RubyRequire _
        Lib "vbaruby" (ByVal param As Long) As Long
    Public Declare Function RubyLoad _
        Lib "vbaruby" (ByVal param As Long) As Long
    Public Declare Function RubyCallMethod _
        Lib "vbaruby" (ByVal objName As Long, _
            ByVal methodName As Long, ByVal args As Long, _
            ByVal ret As Long) As Long
    ’
    Private testLibrary As Long

The VBA procedure “libInit” performs the loading of the bridge library and the require statement to ruby main file:

    Public Sub libInit()
        Dim rbFile As Variant
        If testLibrary = 0 Then
            testLibrary = LoadLibrary(vbaRubyLib)
            RubyInit
        End If
        rbFile = ThisWorkbook.Path + "' RUBY' main.rb"
        RubyRequire VarPtr(rbFile)
    End Sub

Note that the path to the main required ruby be file is defined in the subroutine. Other choices are possible. You can change the way of accessing the ruby programs according to your preferences.

The VBA function “CallMethod” calls the bridge method “RubyCallMethod”. Its three arguments are the receiver of the method call (the name of a volume or of a class), the name of the method, and a ParamArray VARIANT argument containing an optional number of arguments.

    Public Function CallMethod(obj As String, method As String, _
            ParamArray args() As Variant) As Variant
        Dim varObj As Variant, varMethod As Variant, _
            varArgs As Variant, ret As Variant
        Dim var As Variant
    ’
        varObj = obj
        varMethod = method
        varArgs = args
    ’
        RubyCallMethod VarPtr(varObj), VarPtr(varMethod), _
            VarPtr(varArgs), VarPtr(ret)
        CallMethod = ret
    End Function

Note that the arguments passed to the “RubyCallMethod” in bridge library are pointers to VARIANT objects. These pointers are obtained by calls to “VbaPtr” function. The last argument of call to “RubyCallMethod” is a pointer to “ret” VARIANT that shall contain the value returned by the called ruby method. Note that the creation of pointers to VARIANT arguments and the call to “RubyCallMethod” bridge function are the main things done by the function.

Note also that the solution one proposes allows to call methods defined in modules, or class methods. It is not possible to directly call methods on instances of a class.

A “CallMethodValue” method is also defined in “RubyMarshal” VBA module. This method is very similar to “CallMethod”. The difference is that each time a “Range” argument is found, it is replaced by an Array containing the correspond Cell values.

X.G.2.2 “RubyMarshal” VBA module