next up previous contents index
Next: X.F Copying FeResPost Up: X. Appendices Previous: X.D Predefined criteria   Contents   Index


X.E FeResPost ruby extension in excel

The description of FeResPost COM component in Part VI, and the corresponding examples in Part VII might suggest that the only way to use FeResPost in excel is to write VBA modules that use FeResPost COM component.

It is not true. One explains in this Appendix, how ruby can be embedded into excel, and how VBA calls may be marshaled to ruby interpreter. Then, the programming of post-processing can be done nearly entirely with ruby language and FeResPost ruby extension may be used instead of the COM component.

X.E.1 A VBA-ruby bridge

The technique we propose here makes use of excel/VBA capability to load dll libraries and of ruby language to be dynamically embedded into another application by loading the ruby interpreter dynamic library. A schematic representation of the process is given in Figure X.E.1:

In order to explain how the bridging works, one gives in section X.E.1.1 information about the programming of the bridge. In section X.E.2.1 one explains what is done in excel VBA modules to use the bridge and ruby programs.

Figure X.E.1: Schematic representation of the connections existing between excel objects, VBA, VBA-ruby bridge, ruby and the external world inside excel memory.

X.E.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:

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:

    g++ -O2 -IC:/NewProgs/RUBY/Ruby187/lib/ruby/1.8/i386-mingw32 \
        -c marshal.cpp
    g++ -O2 -IC:/NewProgs/RUBY/Ruby187/lib/ruby/1.8/i386-mingw32 \
        -c conversion.cpp
    g++ -O2 -IC:/NewProgs/RUBY/Ruby187/lib/ruby/1.8/i386-mingw32 \
        -c win32ole.cpp
    gcc -O2 -LC:/NewProgs/RUBY/Ruby187/bin \
        -fPIC -shared -Wl,--enable-auto-import \
        marshal.o conversion.o win32ole.o -lmsvcrt-ruby18 \
        -lws2_32 -luuid -lole32 -loleaut32 -lstdc++ -lm \
        marshal.def -o vbaruby.dll
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.

X.E.1.2 Requirements

One summarizes here the requirements to use the VBA-ruby bridge:

Note that the installation of FeResPost is not a requirement for the use of VBA-ruby bridge. Indeed the bridge is independent of FeResPost and can be used for any ruby post-processing.

X.E.2 An example

One presents in directory ``TESTSAT/VBARUBYEX'' an example that corresponds to the ``PostProject.xls'' example illustrating FeResPost COM component. (See section VII.4.) The programming of ruby modules is discussed in section X.E.2.1. Two VBA modules are defined in the excel workbook: ``RubyMarshal'' and ``RubyFunctions''. These modules are discussed in section X.E.2.2 and section X.E.2.3 respectively. A few other points are discussed in section X.E.2.4.

X.E.2.1 Ruby programming

In this case, most of the VBA programming of post-processing operations has been replaced by corresponding code in ``TESTSAT/VBARUBYEX/RUBY'' directory. This code is loaded through the ``main.rb'' main file. This file looks as follows:

    $: << Dir.getwd+"/RUBY"
    $: << "D:/FERESPOST/BINARIES/FeResPost_4.0.10/RUBY"


    require "dl"

    require "UTIL/util"
    require "UTIL/xls"

    require "POSTPROJECT/postProject"
One makes the following comments: Note that the example we provide here depends on the availability of ``FeResPost'' and ``sqlite3'' ruby extensions. The example will not work on your computer if these two modules are not properly installed.

X.E.2.2 ``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 = _
    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
In this library: 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)
        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.E.2.3 ``RubyFunctions'' VBA module

One defines in ``RubyFunctions'' VBA module functions and subroutines that can be called from other VBA modules, or directly used as formulas in spreadsheets. For example, the function ``getParameter'' returns a parameter calculated from the load case name, and the parameter name:

    Function getParameter(lcName As String, paramName As String)
        getParameter = CallMethodValue("PostProject::DbAndLoadCases", _
            "getParameter", lcName, paramName)
    End Function
The function ``getShellVonMisesMax'' calculates the maximum von Mises stress on a Group of shell elements:
    Function getShellVonMisesMax(lcName As String, method As String, _
            groupName As String, Optional gmshFileName As String = "", _
            Optional gmshResName As String = "") As Variant
        getShellVonMisesMax = CallMethodValue( _
            "PostProject::ExtractionCriteria", _
            "getShellVonMisesMax", lcName, method, groupName, _
            gmshFileName, gmshResName)
    End Function
Note that the ruby methods called from VBA may also correspond to subroutines, even though the distinction between subroutines and functions do not exist in ruby. Examples, of calls to subroutines can be found in the VBA code corresponding to ``LcSelector'' spreadsheet. For example, one presents below the code associated to the button ``ReadDbAndLoadCases'' in the spreadsheet:
    Public Sub ReadDbAndLoadCases_Click()
        On Error GoTo locError:
        Dim x As Variant
        x = CallMethod("PostProject::DbAndLoadCases", _
            "setWorkbook", ThisWorkbook)
        x = CallMethodValue("PostProject::DbAndLoadCases", _
            "readDbAndLoadCases",, nbrReservedLines, _
        Exit Sub
        MsgBox prompt:="Something wrong happened! check standard output file.", _
        MsgBox prompt:=CurDir, Title:=CurDir()
    End Sub

X.E.2.4 Other tips

In the ``ThisWorkbook'' VBA code, two excel event subroutines are provided. When opening, the event ``Workbook_Open'' changes the excel execution directory to the directory containing the workbook, loads the vbaruby bridge library and initializes ruby by calling ``libInit'', then calls the ``PostProject::DbAndLoadCases::setWorkbook'' method to initialize the corresponding variable of the ruby post-processing program:

    Sub Workbook_Open()
        ChDrive (Left(ActiveWorkbook.Path, 1))
        ChDir (ActiveWorkbook.Path)
        Application.Calculation = xlCalculationAutomatic
        Call libInit
        Dim x As Variant
        x = CallMethod("PostProject::DbAndLoadCases", "setWorkbook", ThisWorkbook)
	End Sub
This step is mandatory if one wants the ``main.rb'' file to be loaded correctly, because a path relative to the directory containing ``PostProject.xls'' is used in the ``RubyMarshal'' VBA module. (Sees section X.E.2.2.) It also ensures that the ``main.log'' file to which ruby standard output is redirected is located in the same directory as ``PostProject.xls''. (See section X.E.2.1.)

When closing the excel workbook, the following method is called:

    Sub workbook_BeforeClose(cancel As Boolean)
        Application.Calculation = xlCalculationAutomatic
        Dim x As Variant
        x = CallMethodValue("PostProject", "clearModuleVariables")
   End Sub
This method is meant, among other things, to remove all references to excel automation objects, so that the closing of the application is cleanly done. Practically, it sometimes fail, so that you have to kill excel with the task manager. (If someone can explain me why...)

X.E.2.5 Things to do to match a particular configuration

In order to adapt the example to your configuration you must:

next up previous contents index
Next: X.F Copying FeResPost Up: X. Appendices Previous: X.D Predefined criteria   Contents   Index
FeResPost User Manual Version 4.4.2 (2018/01/01)