#### 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!