Topic: Help needed with VBA dll function call

Hello all.

I keep getting the Excel #Value error when I am trying to use a pretty simple function. The goal is to compile this older FORTRAN 77 code in a dll that can be used in VBA. I believe the issue is with the type definitions.

I am using a 64 bit version of Excel on a Windows 11 Pro 64 bit machine.

The DLL compiles without issue.

I followed the example provided on the forum that creates the three statistical functions and that all works correctly. I followed this example and changed the required input types to an INTEGER and a DOUBLE.

The full code from FOTRAN 77 is listed below inside the function. The MTCODE is an integer value and the TEMP is a double value. ALPHA is a double value that is returned from the function. Basically, pass an MTCODE and a TEMP and the function performs a simple calculation to obtain the ALPHA value.

c     Computes the thermal expansion coefficient
c   
      function ALPHA(MTCODE, TEMP) bind(c)
      use ISO_C_BINDING
      implicit none
     
cGCC$ ATTRIBUTES DLLEXPORT :: ALPHA
     
      real(kind=c_float)::ALPHA
     
      integer(kind=c_int),intent(in)::MTCODE
      real(kind=c_double),intent(in)::TEMP
   
      IF (MTCODE .NE. 1) GO TO 100
           ALPHA=(-0.0000006609*(TEMP**2))+(0.0026933877*TEMP)+
     Z       5.88847007751
           GO TO 1000
100   IF (MTCODE .NE. 2) GO TO 200
           ALPHA=(-0.0000005112*(TEMP**2))+(0.0021199360*TEMP)+
     Z       5.5841093063
           GO TO 1000
200   IF (MTCODE .NE. 3) GO TO 300
           ALPHA=(-0.0000001792*(TEMP**2))+(0.0014422280*TEMP)+
     Z       9.0099201202
           GO TO 1000
300   IF (MTCODE .NE. 4) GO TO 400
           ALPHA=(-0.0000005258*(TEMP**2))+(0.0020210845*TEMP)+
     Z       5.1010999680
           GO TO 1000
400   IF (MTCODE .NE. 5) GO TO 500
           IF (TEMP .LT. 300) ALPHA=(0.0000005694*(TEMP**2))+
     Z      (-0.0002694445*TEMP)+8.8211059570
           IF (TEMP .GE. 300 .AND. TEMP .LT. 875) ALPHA=(0.0000005046*
     Z      (TEMP**2))+(-0.0002305726*TEMP)+8.8937530518
           IF (TEMP .GE. 875 .AND. TEMP .LT. 975) ALPHA=(0.0000008251*
     Z      (TEMP**2))+(-0.0007913236*TEMP)+9.0507049561
           IF (TEMP .GE. 975) ALPHA=(0.0000003460*(TEMP**2))+
     Z      (0.0001429432*TEMP)+8.6817054749
           GO TO 1000
500   IF (MTCODE .NE. 6) GO TO 600
           IF (TEMP .LT. 400) ALPHA=(0.0000000003*(TEMP**2))+
     Z      (0.0045000138*TEMP)+7.0000047684
           IF (TEMP .GE. 400) ALPHA=(0.0000002479*(TEMP**2))+
     Z      (0.0006652861*TEMP)+8.4942169189
           GO TO 1000
600   IF (TEMP .LT. 400) ALPHA=(-0.0000014229*(TEMP**2))+
     Z  (0.0023960555*TEMP)+6.9692468643
      IF (TEMP .GE. 400 .AND. TEMP .LT. 800) ALPHA=(0.0010000509*TEMP)+
     Z  7.2999811172
      IF (TEMP .GE. 800) ALPHA=(-0.0000000833*(TEMP**2))+
     Z  (0.0014166683*TEMP)+7.0200033188
1000  RETURN
     
      end function ALPHA


Here is the calling statement in VBA. Please note that I removed the actual path to the dll file.


Option Explicit


Private Declare PtrSafe Function ALPHA Lib "path to dll \thexp.dll" (ByVal MTCODE As Integer, ByVal TEMP As Double) As Double
   

Function wmALPHA(MTCODE, TEMP) As Double


    wmALPHA = ALPHA(MTCODE, TEMP)


End Function

Any help would be greatly appreciated. Thanks!

  • jeff
  • Administrator
  • Offline

Re: Help needed with VBA dll function call

I would check if the DLL is actually exporting the ALPHA function.  You should be able to check by using File->Open" in Simply Fortran and selecting the DLL itself.  The tab that opens should provide a list of all symbols in the DLL. 

My concern is that the function was actually exported as alpha in lowercase.  Also, did you make sure that the Simply Fortran project is compiling as 64-bit?

Jeff Armstrong
Approximatrix, LLC

Re: Help needed with VBA dll function call

Thanks for the reply.

Yes, it is compiling as 64 bit.

When I open the dll file it is a long list of items but I am not sure what they all are.

I checked the .def file and its contents are shown below.

EXPORTS
    thexp @1

I tried using alpha in the VBA declaration statement but still end up with #Value.

  • jeff
  • Administrator
  • Offline

Re: Help needed with VBA dll function call

There are a few problems in the code.  First, despite using intent(in), Fortran is still accepting the arguments by reference.  You would actually need to use the attribute value instead that is only permissible when the function is bind(c).  However, I don't think you should change the Fortran.  Instead, change the VBA to:

Private Declare PtrSafe Function alpha Lib "C:\PathToDLL\thexp.dll" (MTCODE As Integer, TEMP As Double) As Single

All I did was remove the ByVal argument modifiers.

The other problem that took me a few minutes to realize was that you've declared the Fortran function to return single-precision (c_float), but your original declaration line was declaring it as Double.  I changed it to Single in my declaration above, and I was able to get an answer.

Jeff Armstrong
Approximatrix, LLC

Re: Help needed with VBA dll function call

Thanks!!! I made the recommended changes and all works correctly now.

Much appreciated.