Approximatrix Forums — Help needed with VBA dll function call

Re: Help needed with VBA dll function call

2023-07-02T19:56:02Z

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

Much appreciated.

Re: Help needed with VBA dll function call

2023-07-02T19:45:53Z

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.

Re: Help needed with VBA dll function call

2023-07-02T14:26:17Z

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.

Re: Help needed with VBA dll function call

2023-07-02T14:13:00Z

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?

Help needed with VBA dll function call

2023-07-02T11:07:05Z

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!