Topic: Fortran DLL for use in Excel VBA - another round of questions

Hi:
I am trying to use SimplyFortran to create a dll that is useable in Excel VBA, and perhaps also VB.NET in Visual Studio.

I looked at the discussion on 2013-11-11 and following days by rexxitall and Jeff's helpful comments.
When I copy and try to run Jeff's final types.dll example Excel will give me Run-time error '48':
File not found: c"\my-fortran\types.dll

And I get the same thing when I try my simpler example.

I don't seem to be able to register the dll, so I don't understand what is going wrong.
I assume I am missing something required to make a useable dll.

I am using the most recent version of SimplyFortran 2.15

My simple example is supposed to be a subroutine that takes two numbers and returns the addition, passing data types of double by reference.  This dll works fine when called by Fortran programs, but not when using in Microsoft stuff like Excel VBA or Visual Studio VB.NET

The Fortran code is in a file called aplusb_sub.f90
Here is a paste of the code:

Subroutine apb(a,b,c)
use iso_c_binding, only: c_double
IMPLICIT NONE
!GCC$ attributes dllexport, stdcall :: apb
REAL(kind=c_double),INTENT(IN) :: a,b
REAL(kind=c_double),INTENT(OUT) :: c

c = a + b

END Subroutine
-----------------------------------------------------------------------------------------


Here is the makefile that is used to create the dll:

# Automagically generated by Approximatrix Simply Fortran 2.15
#
FC="C:\Program Files (x86)\Simply Fortran 2\mingw-w64\bin\gfortran.exe"
CC="C:\Program Files (x86)\Simply Fortran 2\mingw-w64\bin\gcc.exe"
AR="C:\Program Files (x86)\Simply Fortran 2\mingw-w64\bin\ar.exe"
WRC="C:\PROGRA~2\SIMPLY~1\MINGW-~1\bin\windres.exe"
RM=rm -f

IDIR=
# -I error: Directory C:\Users\cboardman\AppData\Local\\\sfpm\64\include does not exist

LDIR=
# -L error: Directory C:\Users\cboardman\AppData\Local\\\sfpm\64\lib does not exist


OPTFLAGS= -g

SPECIALFLAGS=$(IDIR)

RCFLAGS=-O coff

PRJ_FFLAGS=-fno-underscoring -fcheck=all

PRJ_CFLAGS=

PRJ_LFLAGS=-mrtd -static

FFLAGS=$(SPECIALFLAGS) $(OPTFLAGS) $(PRJ_FFLAGS) -Jmodules

CFLAGS=$(SPECIALFLAGS) $(OPTFLAGS) $(PRJ_CFLAGS)

"build\aplusb_sub.o": ".\aplusb_sub.f90"
    $(FC) -c -o "build\aplusb_sub.o" $(FFLAGS) ".\aplusb_sub.f90"

clean: .SYMBOLIC
    $(RM) "build\aplusb_sub.o"
    $(RM) "apb.dll"

"apb.dll":  "build\aplusb_sub.o"
    $(FC) -o "apb.dll" -shared -Wl,--out-implib="apb.dll.a" -static "build\aplusb_sub.o" $(LDIR) $(PRJ_LFLAGS)

all: "apb.dll" .SYMBOLIC

-------------------------------------------------------------------------------------------

The build works and creates the apb.dll in my folder c:\my-fortran
Note that this folder is also in my PATH variable, and it is my primary location for my fortran programs.


And I can use this dll in a hello world type program:
program hello
use iso_c_binding, only: c_double
real(kind=c_double) :: a,b,c
real(kind=c_double) :: ansy
  print *,"Hello World!"
  a=3.3
  b=8.1
 
  Call apb(a,b,ansy)
   
   print *, "from sub=", ansy
   write(*,*)"Press Enter to Continue"
   read(*,*)
end program hello

----------------------------------------------------------------------------------------------------------------
Now for the details on the Excel VBA. I have a module created to work with a macro-enabled spreadsheet (using Excel 2010). 
I have the following Declare that tries to reference the dll

Declare Sub apb Lib "C:\my-fortran\apb.dll" Alias "apb@12" (ByRef a As Double, ByRef b As Double, ByRef c As Double)

I have tried this with and without the path to the dll.  Both give me the error about not being able to find the dll

To exercise the dll I have created a public subroutine called Tryit, and I can run that from the Macros button.  If you use it directly in a function you get the unhelpful error #Value! in the cell which references a function that calls the dll.

Here is the Tryit subroutine:
Public Sub Tryit()
  Dim a, b, c As Double
 
   a = 2.1
   b = 3.2
 
   Call VBdll.Addit(a, b, c)
MsgBox "addit " & c

'Call VBdll.Addit2(a, b, c)
Call apb(a, b, c)
MsgBox "apb " & c
End Sub

-----
In the first Addit function call I used a dll created by VB.NET and that works.  It is a COM enabled dll created by Visual Studio.

But on the call to apb which is supposed to reference the Fortran dll, I get the Run-time error '48':
File not found c:\my-fortran\apb.dll

So, what do I need to do be enable that dll to work in Excel VBA?  Or to fix it in some other way to make it useful.
The idea was to be able to create a much more complex subroutine in Fortran that takes multiple inputs and returns multiple outputs.

Thanks for any insight.

Regards

CR

  • jeff
  • Administrator
  • Offline

Re: Fortran DLL for use in Excel VBA - another round of questions

CR,

I've noticed a few possible problems right off the bat.  First, I've noticed that you're building a 64-bit DLL.  Is your version of Excel 64-bit?  Microsoft provides directions on how to determine which version of Excel you're using.  Excel and your DLL must be using the same setting (either 32-  or 64-bit).  You may see the error you're experiencing as a result of this.

Second, my directions focus on 32-bit Excel and Fortran DLLs.  The decorations of the function declaration will change if you're using 64-bit Fortran and Excel.   Specifically, there will no longer be a "@12" decoration (or whatever might be appropriate) after your function's name.  That standard only applies to 32-bit DLLs utilizing the stdcall calling convention.  Microsoft cleared things up considerably with 64-bit Windows, eliminating the need for the decoration.

Let me know what happens if you are, in fact, using consistent "bit-ness" across both Excel and your Fortran DLL.  I suspect that may be the source of your issue.

Jeff Armstrong
Approximatrix, LLC

Re: Fortran DLL for use in Excel VBA - another round of questions

Hi Jeff:
Thank you for looking at this for me.  I was not making progress.
I am using Windows 7 64 bit OS.
But my Excel is 32 bit.

So, I switched to making a 32 bit dll and it works in my Excel just fine.

Thanks!

CR

  • jeff
  • Administrator
  • Offline

Re: Fortran DLL for use in Excel VBA - another round of questions

CR,

That's great to hear that it's working!

I know the process is not particularly straightforward, but that is often the nature of mixed-language programming.

Jeff Armstrong
Approximatrix, LLC

Re: Fortran DLL for use in Excel VBA - another round of questions

I have tried this out and gotten the apb example to work just fine as it is (mostly... I did it in fixed-form and found out the !GCC$ has to start in the first column unless using free-form). I tried to extend this to arrays as follows:

      subroutine aArrPbArr(a,b,c,n)
       use iso_c_binding, only: c_double, c_int
       IMPLICIT NONE
!GCC$ attributes dllexport, stdcall :: aArrPbArr
       INTEGER(kind=c_int), INTENT(IN) :: n
       REAL(kind=c_double), INTENT(IN) :: a(1:n),b(1:n)
       REAL(kind=c_double), INTENT(OUT) :: c(1:n)
       INTEGER(kind=c_int) :: i
       DO i = 1, n
        c(i) = a(i) + b(i)
       END DO
       return
      end subroutine aArrPbArr

Excel links to it and appears to run the link to aArrPbArr, but running it does not actually keep any of the values in the c array. The c() array contains all zeros before and after running aArrPbArr. Excel VBA code is:

Option Explicit

Declare Sub aArrPbArr Lib "C:\[path]\Test\test.dll" Alias "aarrpbarr@16" (ByRef a() As Double, ByRef b() As Double, ByRef c() As Double, ByRef n As Integer)

Public Sub test2()
    Const n As Integer = 5
    Dim i As Integer
    Dim a(1 To n) As Double, b(1 To n) As Double, c(1 To n) As Double
    Dim WorkString As String


    'ReDim a(1 To n), b(1 To n), c(1 To n)
    a(1) = 0: a(2) = 1: a(3) = 1.5: a(4) = 1.75: a(n) = 2
    b(1) = 0: b(2) = 0.25: b(3) = 0.5: b(4) = 0.75: b(n) = 1

    Call aArrPbArr(a, b, c, n)
    WorkString = ""
    For i = 1 To n
        WorkString = WorkString & "c(" & CStr(i) & ")=" & CStr(c(i)) & vbNewLine
    Next i
    MsgBox "aArrPbArr " & WorkString
End Sub

(Oh, and the commented ReDim in the VBA code was me trying to see if it would work either with fixed size or redimensionable arrays. It didn't matter.)

What am I doing wrong?

Thanks,
Jason

  • jeff
  • Administrator
  • Offline

Re: Fortran DLL for use in Excel VBA - another round of questions

Jason,

You're almost correct, but there's one change you need to make to your declaration of aArrPbArr in VBA that needs to change that might seem illogical.  When you pass arrays to Fortran from VBA, you actually want to pass ByVal because an array in VBA is just a pointer.  When you pass by reference, you're actually passing a pointer to the array's starting address rather than just the array's starting address.  Fortran, on the other hand, just expects the array's starting address.

If you change your declaration to:

Declare Sub aArrPbArr Lib "callee.dll" Alias "aarrpbarr@16" (ByVal a() As Double, ByVal b() As Double, ByVal c() As Double, ByRef n As Integer)

It should work.  I tested it in VB.Net, and everything ran smoothly (except it didn't like your VB array dimensions starting at 1, but that's not the problem you were having).

Jeff Armstrong
Approximatrix, LLC

Re: Fortran DLL for use in Excel VBA - another round of questions

Jeff,

Hmm... I might need to use a wrapper through a .net language then. VBA will not permit you to pass arrays ByVal (not even in a Declare Sub). Do you have any other ideas for how to get around that? I don't tend to think in pointers (aside from whether to use ByRef or ByVal), but maybe telling the Declare Sub that it's a pointer and then pointing the c() array to that location? (not sure how to even do that.)

Thanks,
Jason

  • jeff
  • Administrator
  • Offline

Re: Fortran DLL for use in Excel VBA - another round of questions

Jason,

What happens if you simply don't include the ByVal in front of each argument in the declaration?

I'll try using actual VBA to see if I can find a solution.

Jeff Armstrong
Approximatrix, LLC

Re: Fortran DLL for use in Excel VBA - another round of questions

Jeff,

It does let me compile and run without any ByRef/ByVal directive, but it still behaves as a ByRef - i.e.: the c array still contains all zeros after going through aArrPbArr.

I wish VBA behaved a bit more like VB/C#.net. I do a lot of stuff in Excel, so unfortunately I have to live with the inadequacies of VBA.

Thanks,
Jason

Re: Fortran DLL for use in Excel VBA - another round of questions

Jeff,

I'm guessing you haven't had any luck with this so far. Looks like a wrapper through .Net is in order to get the functionality in VBA.

Jason

Re: Fortran DLL for use in Excel VBA - another round of questions

Jason,

Although it's been a while since I've used VBA, here are a couple of suggestions:

'--- Set all variables to be declared
Option Explicit

'--- Set array index starting at 1
Option Base 1

'--- To pass an array from VBA to Fortran, you must pass the first element
'--- of the array. By default, Visual Basic passes variables by reference, so
'--- passing the first element of the array as a scalar will give Fortran the
'--- starting address of the variable arrays.
'
'--- Double in VBA is the same as REAL*8 in Fortran
'--- Long in VBA is the same as Integer*3 in Fortran
'
'--- Call your Fortran subroutine as follows:
CALL aArrPbArr( A(1), B(1), C(1), n )

I hope this is helpful,
Frank

  • jeff
  • Administrator
  • Offline

Re: Fortran DLL for use in Excel VBA - another round of questions

Jason,

Sorry, I was caught up in a few other things.  Frank's suggestion about passing the first array element ByRef might work, but I'm not sure.  I promise to look into it more early next week.

Jeff Armstrong
Approximatrix, LLC
  • jeff
  • Administrator
  • Offline

Re: Fortran DLL for use in Excel VBA - another round of questions

Jason,

Frank's VBA call is what really needs to happen.  So I have a Fortran subroutine that I wish to call that will modify an array:

subroutine timestwo(x, n) bind(c)
implicit none
!gcc$ attributes stdcall, dllexport::timestwo

    integer, intent(in)::n
    real(kind=8), intent(inout), dimension(n)::x
    
    x = 2.0*x
    
end subroutine timestwo

To call from VBA, the following works for me:

Declare PtrSafe Sub TimesTwo Lib "C:\workspace\example\excel\timestwo.dll" Alias "timestwo" (ByRef x As Double, n As Integer)

Sub Button2_Click()

    Dim x(1 To 10) As Double
    
    For i = 1 To 10
        x(i) = Cells(i, 1)
    Next i
        
    Call TimesTwo(x(1), 10)
    
    For i = 1 To 10
        Cells(i, 2) = x(i)
    Next i

End Sub

A few things to note:

In VBA, I'm declaring that my Fortran function, timestwo, accepts a scalar double by reference, which may seem contradictory.  When I call my subroutine, I am passing the first element of my array only.  This procedure works because what we've effectively done is passed to Fortran the memory address of the first element of the array.  Our Fortran code expects an array, but it is actually receiving an address to the array in reality.  Everything actually works because the address of the first element of the array is effectively the address of the array.

In my example, the second column of my spreadsheet ends up populated with double the values of the first column.

I should point out that everything here is tested on Windows 7 64-bit using 64-bit Excel 2013.  There may be some differences if Excel is in 32-bit mode because the STDCALL decorations will be present.  However, the base code should work.

Jeff Armstrong
Approximatrix, LLC

Re: Fortran DLL for use in Excel VBA - another round of questions

Hi Jeff & Frank,

I was able to get that to work. Thanks so much for your help!

Jason