Creating VBA to Fortran Excel Dynamic Libraries

[jimmck]

Microsoft Office 2011 for Mac now allows VBA calls to external dynamic libraries. Workbooks with these custom functions can be saved as XLAM files and made available to all workbooks. I have searched the forums as well as the net at large and learned a lot, but some sample code would help tremendously for me. I have a lot of existing Excel VBA code calling DLL's on Windows and I want to port these to Mac Excel. Even code that works in MS Visual Studio and Intel Fortran does not work with strings on the Mac side as it appears it should with minor changes. I can get functions to work passing doubles and singles. Here is some example code:

VBA code:

Option Explicit
Option Base 1

Private Declare Sub mySub Lib "myLib.dylib" (ByVal myString As String, StringLength As Long) 'strings usually are passed a hidden 2nd string length

Function myFortranString()
Dim str As String * 8
Call mySub(str, Len(str)) ' also seen as mySub(str, 8&) - which I also have no success with
myFortranString = str
End Function

Fortran code:

      subroutine mySub (str)

      !DEC$ ATTRIBUTES C, DECORATE, ALIAS:'mySub' ::mySub
c    !DEC$ ATTRIBUTES REFERENCE :: str
      character str*8
      str='success!'
      return
      end

Compile the fortran code: ifort -dynamiclib -m32 mySub.for and rename myLib.dylib (how do I make it name it in the commandline?) Place myLib.dylib in ~/lib folder (create it in your home directory). This location is automatically searched by Excel.

The attributes declaration makes the subroutine pass by value (like C), name capitalization (like C) and gets rid of the trailing underscore fortran adds to the library subroutine name. I thought this should make the VBA work as written. What am I missing?

Interestingly, I found that adding the 2nd declaration, which tells fortran it is not getting a string length, works as long as the VBA call does not pass the string length. However, I would have to change a LOT of existing VBA code to make that work.

Is there a "best" way to make these kind of dynamic libraries? Are there Xcode templates anywhere? Example code anywhere? Thanks for any help.

Jim 

[Steven_L_Intel1]

Take out the "C" attribute - you don't need it.  You've already overridden all of the other effects. You can add the REFERENCE attribute for the str argument to prevent it from looking for a length, but keep in mind then that VBA doesn't pass any length and you'll need to ensure the lengths match. I would recommend leaving that out and letting Fortran look for the length. It wants the length by value, so use ByVal in the VBA code.

I would give up on Xcode for building this. Use -o to name the shared library.

[jimmck]

Thanks, Steve. That worked great.

One more question: I have many fortran files that make up the legacy code. How do I use them for a dynamic library when one of the files is meant to be "exported" for its routine symbols, and all the rest of the files are routines internal to the code? On Windows I do the DLLEXPORT declaration for exported routines. I am not a commandline expert as I do programming infrequently over my 25 years as an engineer. Thus my desire for copious example code. :) BTW, all the code files compile to object files.

Thanks, Jim

[Steven_L_Intel1]

I'm glad to hear it - it was somewhat of a guess by me, but I figured VBA worked much the same as it did on Windows in this regard.

On Linux, and probably OS X, you don't need DLLEXPORT. All global symbols will be "exported" when you build the .dylib.

[Baker__Bryan]

I'm having trouble getting excel to see my dylib.  I've taken the example from windows and modified it as follows:

Fortran:

subroutine FortranCall (r1, num)
!DEC$ ATTRIBUTES DLLEXPORT, STDCALL, REFERENCE, ALIAS:"FortranCall" :: FortranCall
integer, intent(in) :: r1
character(10), intent(out) :: num
!DEC$ ATTRIBUTES REFERENCE :: num

num = ''
write (num,'(i0)') r1 * 2

return
end subroutine FortranCall

Compile:

ifort -m32 -dynamiclib tmp.f90 -o tmp.dylib

Excel:

Declare Sub FortranCall Lib "tmp.dylib" (r1 As Long, ByVal num As String)

Sub tmp(varX As Variant)

    Dim r1 As Long
    Dim num As String * 10

    r1 = 123
    Call FortranCall(r1, num)

 End Sub

I can compile the code without error, but when I run the vba code from the excel vba editor I get "Specified DLL function not found".  My tmp.dylib is in the same directory as the excel file.  I also tried creating a ~/lib/ directory and copying it there without success.  Any ideas?

Thanks! -Bryan

[Steven_L_Intel1]

It's odd that Excel on Mac would refer to DLLs, but...

The problem would appear to be that the global name of your function in the dylib is not what Excel wants to see. Outside of Windows, the DLLEXPORT and STDCALL attributes aren't used, though STDCALL may have some side effects. Try using this instead and see what it does:

!DEC$ ATTRIBUTES ALIAS:"FortranCall" :: FortranCall

I assume there is some tool on OS X that will show you what names are in a dylib - I'm not familiar with that so don't know what it might be.

[Izaak_Beekman]

The tool which Steve is referring to (for Mac) to do that is `nm`. Try `man nm` for more info on how to use it.

[Baker__Bryan]

No love.

Here is what I get from Izaak's suggestion (which looks right):

nm tmp.dylib | grep FortranCall
000010c0 T FortranCall
000662a4 d _FortranCall$format_pack.0.1

Here is my fortran code compiled in OSX:

subroutine FortranCall (r1, num)
!DEC$ ATTRIBUTES ALIAS:"FortranCall" :: FortranCall
integer, intent(in) :: r1
character(10), intent(out) :: num
!DEC$ ATTRIBUTES REFERENCE :: num

num = ''
write (num,'(i0)') r1 * 2

return
end subroutine FortranCall

compile: ifort -m32 -dynamiclib tmp.f90 -o tmp.dylib

and the VB:

Private Declare Sub FortranCall Lib "tmp.dylib" (r1 As Long, ByVal num As Long)

Sub tmp()

    Dim r1 As Long
    Dim num As Long

    r1 = 123
    Call FortranCall(r1, num)
    
End Sub

still get the same error:

Runtime error '453'

Specified DLL function not found.

I'll also try Microsofts support and see what suggestions they give me.

Thanks,

-Bryan