Few Toy Programs to Mesh Excel and Fortran together

Fortran DLL and Excel
Fortran 90 with Excel – gfortran example is one of the post that constantly gets lot of attention as measured by the top posts sidebar widget on this blog.

So thought about revisiting it and this post is to show some toy programs that shows how to link fortran program as a dll with excel.

I am using gfortran and the following simple fortran programs to create a DLL which is called from excel.



' Fortran Codes
'

'      Subroutine fortdll(Array1, upbound)
'        Implicit None
'! …argument declarations
'        Integer :: upbound
'        Double precision :: Array1(1:upbound)
'! Local variables
'        Integer :: i
'        do i=1,upbound
'        Array1(i) = 288.16 - 0.0065 * Array1(i)
'        end do
'        End Subroutine
'
'
'      Subroutine fortdll2(Array1, upbound)
'        Implicit None
'! …argument declarations
'        Integer :: upbound
'        integer :: Array1(1:upbound)
'! Local variables
'        Integer :: i
'        do i=1,upbound
'        Array1(i) = 1000 * Array1(i)
'        end do
'        End Subroutine
'
'
'        Subroutine fortdll3(name)
'        Implicit None
'! …argument declarations
'        character(len=*) :: name
'! Local variables
'
'        name = "Sukhbinder"
'
'        End Subroutine
'
'
'     Subroutine fortdll4(Array1, upbound, Array2, upbound1, name)
'        Implicit None
'
'! …argument declarations
'        Integer :: upbound,upbound1
'        integer :: Array1(1:upbound)
'        double precision :: Array2(1:upbound1)
'        character(len=*) :: name
'! Local variables
'        Integer :: i
'        do i=1,upbound
'            Array1(i) = 1000 * Array1(i)
'        end do
'
'        do i=1,upbound1
'                Array2(i) = 1000 * Array2(i)
'        end do
'
'        name = "Done"
'
'      End Subroutine
'
'
'
'

Here is how to build the fortran programs as DLL with gfortran?

'
' Building the fortran file as a DLL using Gfortran
'
gfortran  -mrtd -fno-underscoring -shared -o fortdll4.dll fort4.f90
gfortran  -mrtd -fno-underscoring -shared -o fortdll3.dll fort3.f90
gfortran  -mrtd -fno-underscoring -shared -o fortdll2.dll fort2.f90
gfortran  -mrtd -fno-underscoring -shared -o fortdll1.dll fort1.f90



Here’s the VBA code in excel to call the DLL’s


Declare Sub fortdll Lib "d:\game\dll\fortdll.dll" (ByRef Array1 As Double, ByRef upbound As Long)
Declare Sub fortdll2 Lib "d:\game\dll\fortdll2.dll" (ByRef Array1 As Long, ByRef upbound As Long)
Declare Sub fortdll3 Lib "d:\game\dll\fortdll3.dll" (ByVal Array1 As String, ByVal upbound As Long)
Declare Sub fortdll4 Lib "d:\game\dll\fortdll4.dll" (ByRef Array1 As Long, ByRef upbound As Long, ByRef Array2 As Double, ByRef upbound1 As Long, ByVal name As String, ByVal upbound As Long)



Sub testing1()
    Dim II As Long
    Dim test(1 To 10) As Double
    II = 10
    
    For i = 1 To 10
      test(i) = i * 1500
    Next i
    
    Call fortdll(test(1), II)
    
    For i = 1 To 10
     Cells(i, 1) = i * 1500
     Cells(i, 2) = test(i)
    Next i
End Sub




Sub testing2()
    Dim II As Long
    Dim test(1 To 10) As Long
    
    II = 10
    
    For i = 1 To 10
      test(i) = i
    Next i
    
    Call fortdll2(test(1), II)
    
    For i = 1 To 10
     Cells(i, 1) = i
     Cells(i, 2) = test(i)
    Next i
End Sub

Sub testing3()
    Dim test As String
    Dim II As Long
    test = "Sukhwinder"
    II = Len(test)
   
    Call fortdll3(test, II)
    
    Cells(1, 1) = test
    
End Sub


Sub testing4()
 Dim II As Long
    Dim test(1 To 10) As Long
    Dim test1(1 To 10) As Double
    Dim name As String
    II = 10
    
    name = "success is failure"
    i2 = Len(name)
    For i = 1 To 10
      test(i) = i * 15
      test1(i) = i * 1.5
    Next i
    
    Call fortdll4(test(1), II, test1(1), II, name, i2)
    
    For i = 1 To 10
     Cells(i, 1) = i
     Cells(i, 2) = test(i)
     Cells(i, 3) = test1(i)
     Cells(i, 4) = name
    Next i
End Sub


You might also want to check this post titled Fortran DLL and Excel – How to mesh them together

3 thoughts on “Few Toy Programs to Mesh Excel and Fortran together

  1. Thanks you very much for these examples. The way to move arrays of numbers between Gfortran and Excel is something I am looking for. What about two indexed arrays?

    Like

  2. Sukhbinder:
    With your examples it was trivial to use numerical arrays like A(m,n) mn when testing a dll library. It worked fine. Thanks again Sukhbinder. Gfortran is a good tool for numerical computation.
    Aníbal

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s