copy

Wednesday, 26 March 2014



Calling A FORTRAN DLL From Excel & VBA


Introduction



According to Wikipedia: “Fortran (derived from Formula Translating System) is a general-purpose, imperative programming language that is especially suited to numeric computation and scientific computing. Originally developed by IBM in New York City in the 1950s for scientific and engineering applications, Fortran came to dominate this area of programming early on and has been in continuous use for over half a century in computationally intensive areas such as numerical weather prediction, finite element analysis, computational fluid dynamics, computational physics and computational chemistry. It is one of the most popular languages in the area of high-performance computing and is the language used for programs that benchmark and rank the world's fastest supercomputers”.

Only the above paragraph is probably not enough to describe the importance of Fortran language on engineering and scientific world. Literally, thousands lines of Fortran code have been written the last 50+ years from several developers throughout the world. And, now, the big question is: why not take advantage of the code already written in Fortran and use it from Excel/VBA? Is it possible to do that?

Well, I have some good news, the answer is YES!  So, in this post I will try to provide you some insights about how to do it. In short, the idea is to build a Dynamic-link library (DLL) file that will include the necessary Fortran functions and subs, and, then, call these functions/subs (DLL file) from VBA.



Fortran code



The first step on this tutorial is to build the DLL file. I used the Fortran code that you will find below; the FRICTIONFACTOR function is actually a Fortran version of an old VBA function that I developed some years ago. By the way, sorry for the uppercase in the code, but it is an old habit that I acquired when I was writing the Fortran code for my M.Sc. thesis (actually Fortran became case-insensitive from Fortran 90 and onwards).

FUNCTION  FRICTIONFACTOR (ROUGHNESS, DIAMETER, VELOCITY,VISCOSITY)
    !DEC$ ATTRIBUTES DLLEXPORT:: FRICTIONFACTOR

    !-----------------------------------------------------------------------------
    !Calculates the friction factor of a pipe using Churchill's equation (1977).
    !This equation is valid for all types of flows (from laminar to turbulent).
    !
    !Written by:    Christos Samaras
    !Date:          21/03/2014
    !e-mail:        xristos.samaras@gmail.com
    !site:          http://www.myengineeringworld.net
    !-----------------------------------------------------------------------------

    IMPLICIT NONE

    !Declaring the necessary variables.
    REAL*8::  FRICTIONFACTOR,ROUGHNESS, DIAMETER, VELOCITY, VISCOSITY ,REYNOLDS, A, B, C, D

    !Calculate the Reynolds number (diameter in m, velocity in m/s, kinematic viscosity in m2/s).
    REYNOLDS = (DIAMETER * VELOCITY) / VISCOSITY

    !Calculate the intermediate variables A and B.
    A=((2.457 * LOG(1. / ((ROUGHNESS / (3.7 * DIAMETER)) + ((7. / REYNOLDS) ** 0.9)))) ** 16)
    B = (37530. / REYNOLDS) ** 16.

    !Apply the equation.
    FRICTIONFACTOR = 8. * ((((8. / REYNOLDS) ** 12.) + ((A + B) ** (-3./2.))) ** (1./12.))
    
    RETURN

END FUNCTION FRICTIONFACTOR

SUBROUTINE DOUBLEARRAY (ELEMENTS, INARRAY, OUTARRAY)
    !DEC$ ATTRIBUTES DLLEXPORT:: DOUBLEARRAY

    !---------------------------------------------------------------------
    !Receives as input a one-dimensional array and it doubles tis values.
    !
    !Written by:    Christos Samaras
    !Date:          23/03/2014
    !e-mail:        xristos.samaras@gmail.com
    !site:          http://www.myengineeringworld.net
    !---------------------------------------------------------------------
    
    IMPLICIT NONE
    
    !Declaring the necessary variables.
    INTEGER*4, INTENT(IN) :: ELEMENTS
    INTEGER*4, INTENT(IN) :: INARRAY(ELEMENTS)
    INTEGER*4, INTENT(OUT) :: OUTARRAY(ELEMENTS)
    INTEGER*4:: I
    
    !Double the values of the input array.
    OUTARRAY = 2*INARRAY

    RETURN

END

Important notes

1. In order to build the DLL file I used the old Compaq Visual Fortran (version 6.6) compiler. I am sure that you can find a much more recent compiler (for example an Intel Fortran Compiler).

2. Be careful of the attributes required for the DLL export, otherwise the Fortran functions/subs will not be “visible” from VBA:
!DEC$ ATTRIBUTES DLLEXPORT:: FRICTIONFACTOR
!DEC$ ATTRIBUTES DLLEXPORT:: DOUBLEARRAY
The above lines might be slightly different for your compiler, but in any case must be included in the code (as comments).

3. Be aware of DLL dependencies. If the DLL file is going to be used in other computers (that haven’t the same Fortran compiler installed), ensure that is “independent”. For example, to achieve this in Compaq Visual Fortran I had to go to Project -> Settings -> Fortran tab -> select Libraries from the dropdown menu Category, select the Single-Threaded from the Use run time library dropdown menu, and, finally, press the OK button (see the picture below). Otherwise, the DLL file that the compiler built required the MSVCRTD.dll file in order to run. You can check all the DLL dependencies using a great freeware tool called Dependency Walker.

Single-Threaded Library



VBA code



And here is the VBA code that “calls” the compiled Fortran code from the MySamble.dll file.

Option Explicit
Option Base 1

'---------------------------------------------------------------------------
'This module contains two examples of calling a FORTRAN dll from Excel/VBA.
'The first one uses a FORTRAN function straight from the worksheet,
'while the second one calls a FORTRAN sub from a VBA sub.

'Written by:    Christos Samaras
'Date:          25/03/2014
'e-mail:        xristos.samaras@gmail.com
'site:          http://www.myengineeringworld.net
'---------------------------------------------------------------------------
                
'Declaring the function and the sub from dll file.
'NOTE: if the dll file is NOT in the same folder with the workbook, use its full path instead. Example:
'Public Declare Function FRICTIONFACTOR Lib "C:\Users\Christos\Desktop\MySample.dll"
Public Declare Function FRICTIONFACTOR Lib "MySample.dll" _
                            (ROUGHNESS As Double, _
                            DIAMETER As Double, _
                            VELOCITY As Double, _
                            VISCOSITY As Double) As Double

Public Declare Sub DOUBLEARRAY Lib "MySample.dll" _
                        (ELEMENTS As Long, _
                        INARRAY As Long, _
                        OUTARRAY As Long)

Sub CallDoubleArray()

    'Declaring the necessary variables.
    Dim i           As Long
    Dim LastRow     As Long
    Dim ArrayIn()   As Long
    Dim ArrayOut()  As Long
             
    'Activate the Sub sheet and find the last row.
    With Sheets("Sub")
        .Activate
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    End With
    
    'If there is at least one value proceed.
    If LastRow >= 4 Then
        
        'Resize the arrays.
        ReDim ArrayIn(1 To LastRow - 3)
        ReDim ArrayOut(1 To LastRow - 3)
        
        'Populate the input array.
        For i = 4 To LastRow
            ArrayIn(i - 3) = ActiveSheet.Range("B" & i)
        Next i
        
        'Call the FORTRAN Sub.
        Call DOUBLEARRAY(LastRow - 3, ArrayIn(1), ArrayOut(1))
        
        'Pass the results back into sheet.
        For i = 4 To LastRow
            ActiveSheet.Range("D" & i) = ArrayOut(i - 3)
        Next i
        
    Else
        
        'Empty column, inform the user.
        MsgBox "Please enter at least one value on B column and retry!", vbExclamation, "No data"
        ActiveSheet.Range("B4").Select
        
    End If

End Sub

Important notes

1. It is essential to call the function/sub included on the DLL file using the correct data type. Here is a short list of Fortran data types, along with the corresponding VBA/VB 6.0 data types.
Fortran data types      VBA/Visual Basic data types
----------------------  --------------------------------
INTEGER*2               Integer
INTEGER*4               Long
REAL                    Single
REAL*4                  Single
DOUBLE PRECISION        Double
REAL*8                  Double
LOGICAL*2               Integer
LOGICAL*4               Boolean/Long
CHARACTER*n             String*n (Passed ByVal) 

2. Note the usage of Option Base 1 in the beginning of the code.
Contrary to VBA, arrays in Fortran are NOT zero-based, so we use this expression in order to avoid array sizing problems between the two languages.

3. In order to avoid calling the DLL file using its full path I used the following workbook open event, which changes the current directory to the folder where the workbook is located:
Option Explicit

Private Sub Workbook_Open()

    ChDir (ThisWorkbook.Path)
    Application.CalculateFull

End Sub

If the DLL file is NOT in the same folder with the workbook, use its full path. For example:
Public Declare Function FRICTIONFACTOR Lib "C:\Users\Christos\Desktop\MySample.dll"

4. The second example (with the sub), although might looks trivial, it actually shows you how to transfer arrays between VBA and Fortran. Note that in the following line we use the first element of each array:
Call DOUBLEARRAY(LastRow - 3, ArrayIn(1), ArrayOut(1))



Epilogue



Fortran is not a dead/outdated programming language. It’s still being used by many developers/scientists/engineers. My suggestion is to take advantage of the code already written in Fortran and don’t re-inventing the wheel. I know that the mixing of Fortran and VBA is probably a little advanced topic, but the above example, along with the side notes will help you overcome many of the obstacles that you will find on your way.



Downloads



Download

The zip file contains a f90 file with the sample Fortran code, the DLL file and a workbook with the VBA code. The Excel file can be opened with Excel 2007 or newer. Please enable macros before using it.

Did you like this post? If yes, then share it with your friends. Thank you!


Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info