Calling A Fortran DLL From Excel & VBA

Share this

March 26, 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 the Fortran language in engineering and the scientific world. Literally, thousands of lines of Fortran code have been written in the last 50+ years by 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 in 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:        [email protected]
    !Site:          https://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:        [email protected]
    !Site:          https://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. 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, they 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 the DLL 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 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:        [email protected]
'Site:          https://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 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 at the beginning of the code. Contrary to VBA, arrays in Fortran are NOT zero-based, so we use this expression to avoid array sizing problems between the two languages.

3. 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 as 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 it might look trivial, 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 an 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.

Page last modified: October 1, 2021

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

  • Fortran DLL only works for 32bit Excel. Did somebody successfully compiled Fortran dll for 64-bit Excel?

  • If you add in the sample workbook the PtrSafe declaration but pointing to the sample DLL is it still not working?
    I am insisting on the sample DLL because I have tested and I know that is working.

  • OK, when you unzip the file and open the workbook, does the friction function works with the existing DLL (not the one you created)?
    I am trying to understand if it an Excel issue or not.

  • when you unzip the sample, the files (MySample.dll and the xls) are in the same path. Later, the recompiled dll can run the DOUBLEARRAY sub but generates error running the frictionfactor function… so i consider the PATH issue is OK

  • Did you update the paths (as the comment suggest)?

    '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:UsersChristosDesktopMySample.dll"
  • later I noticed that i forgot to inform the office version: 2013 x64

    that is why de PtrSafe declaration.

    the sample after downloaded does not work. After adding the “ptrsafe” it generates error 53 (dll not found) when you click RUN button on the Sub excel tab

  • Hi,

    I haven’t used the compiler you wrote, so I don’t know if there is something wrong there.
    However, why you use PtrSafe in the declaration? Do you have a 64bit office?
    And something else:
    When you open the sample workbook using my DLL, does the code work in Excel?

    Best Regards,
    Christos

  • Using:
    Windows 8.1 © 2013
    64 bit OS, x64-based processor

    Download the example from https://myengineeringworld.net/2014/03/fortran-dll-excel-vba.html

    Copy the “Calling A FORTRAN DLL From Excel & VBA.xlsm” to the folder ..MySampleReleasex64

    Open the .xlsm
    Click on the “Enable Content” button

    In VBA window, add “PtrSafe” to both Public Declare…
    Save and Close

    As seen in https://www.silverfrost.com/32/ftn95/ftn95_personal_edition.aspx just downloaded the compiler FTN95 Personal Edition (FTN95PE) version 8.61 from http://www.ftn95.co.uk/ftn95/ftn95-8.61/ftn95_personal.exe

    Run “C:Program Files (x86)SilverfrostFTN95plato.exe”

    File > New > Project:
    Project Type: Fortran DLL
    Name: MySample

    OK

    In “Project Explorer”, right click on “Source Files”, click on “Add New Item”

    Type: Free format Fortran file
    Name: MyCode

    And paste the content of the downloaded MyCode.f90 in a new .f95 source file

    Configuration: Release Platform: x64

    Build > Build

    So far so good:
    “Compiling file: MyCode.f95
    C:UsersBarRErADocumentsMySampleMyCode.F95(17) : warning 197 – Variable C has been declared but not used
    C:UsersBarRErADocumentsMySampleMyCode.F95(17) : warning 197 – Variable D has been declared but not used
    C:UsersBarRErADocumentsMySampleMyCode.F95(51) : warning 197 – Variable I has been declared but not used
    Compilation completed with no errors.
    Linking…
    Creating dynamic link library C:UsersBarRErADocumentsMySampleReleasex64MySample.dl”

    When opening the .xlsm, Excel crashes or a exception is thrown.

    “FATAL ERROR > Silverfrost exception handler has failed”

    Commenting the FRICTIONFACTOR on the .f95 code, compiling again, then going to Excel > tab Sub > clicking RUN (i.e. running the VBA Sub CallDoubleArray()), it works. The problem is with the function call.

    I have no clue about what to do. It is my first contact with FORTRAN, I’m an engineer that wants to translate some thermodynamic calculations from VBA to Fortran.

    Can anyone give some help?

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    Add Content Block
    >