Fortran DLL does not work in EXCEL VBA

[Vahid_B_]

Hi,

I created a DLL file (testDLL.dll) to add two numbers in Fortran as follows:

SUBROUTINE addFunction(a, b, Sum)

!DEC$ ATTRIBUTES DLLEXPORT,STDCALL,REFERENCE, ALIAS:"addFunction" :: addFunction

IMPLICIT NONE
REAL :: a, b, sum

sum = a + b

RETURN
END SUBROUTINE addFunction 

and then copied the DLL file in C:\Users\vb\AppData\Roaming\Microsoft\AddIns

when I try to use this DLL in EXCEL VBA as follows:

Public Declare PtrSafe Sub addFunction Lib "testDLL.dll" (a As Single, b As Single, abSum As Single)

Public Function addNumbers(a, b)

Dim sumNums As Single

Call addFunction(a, b, sumNums)
addNumbers = sumNums

End Function

It gives me #VALUE instead of the sum of two numbers in EXCEL. I debugged my code in VBA and it seems that it cannot find (or read) my dll file. My computer is a 64-bit machine and my EXCEL is 32-bit, so I generated a 32-bit DLL in Fortran to be compatible with my EXCEL. I really appreciate if anyone can help me to solve this error.     

Thanks

[DavidWhite · Accepted solution]

Vahid,

You need to make sure that the DLL is on the PATH.  To edit the Path in Win7, type environment in the search for programs, and select the option for Edit environment variable for your account.

Also, you may want to check that all of the dependencies for the DLL are accessible.  If this is not so, Excel will report that the DLL is not found, when it is actually a dependent DLL that is missing.

To do this, download dependency walker from dependencywalker.com.

Regards,

David

[Vahid_B_]

Thanks David,

It worked when I added the DLL path to the PATH variable. The DLL above was a test for a large DLL file that I wanted to run in Excel. Now it works for my test DLL but when I follow the same procedure for my original DLL it doesn't work. I used Dependency Walker and added all the dependent DLL paths to the PATH variable in the environment but Excel still says  the DLL is not found. Dependency Walker gives me the following errors:

Error: At least one module has an unresolved import due to a missing export function in an implicitly dependent module.
Error: Modules with different CPU types were found.
Warning: At least one module has an unresolved import due to a missing export function in a delay-load dependent module.

I had the same 2 errors for my test DLL but I was able to run it in Excel, so I suspect that the reason that I cannot run my original DLL is the above Warning. Is it correct?  If it's correct how can I fix the problem?

Thanks again for your help

Vahid

[Vahid_B_]

I have some mathematical functions such as exp() and abs() in my DLL and Dependency Walker shows that my DLL is dependent to libmmd.dll

That is the only difference between my test DLL that runs in Excel and my original DLL that generates error message. I have added the address of libmmd.dll, which is C:\Program Files (x86)\Intel\Compiler\Fortran\10.0.025\em64t\Lib to my PATH variable. Can libmmd.dll be the cause of the problem?

I have two mathematical functions in one of my source files that depend on each other. I add the following lines to them to export them to the DLL file

REAL Function Get_HazVAL(RtnPrd , ary_Ymean, ary_Yrate,ary_Count , sigma_lnY, distflag, T, nsigma)

!DEC$ ATTRIBUTES DLLEXPORT, STDCALL, REFERENCE, ALIAS: "Get_HazVAL" :: Get_HazVAL

REAL Function SumProbExcd(Ycrit, ary_Ymean, ary_Yrate,ary_Count, sigma_lnY, distflag, T, nsigma)

!DEC$ ATTRIBUTES DLLEXPORT, STDCALL, REFERENCE, ALIAS: "SumProbExcd" :: SumProbExcd

Get_HazVAL() calls SumProbExcd() function. When I debug my program, compiler gives me the following error:

error LNK2019: unresolved external symbol _sumprobexd@32 referenced in function Get_HazVAL

If I remove !DEC command line from the SumProbExcd() function, compiler creates the DLL file with no errors but Excel does not run the DLL file. Could you please help me solve this problem. I'm sorry if my questions are very basic. I'm a beginner in writing code with Fortran.

Thanks,

Vahid

[DavidWhite]

Vahid,

To avoid some of these dependencies, especially if your DLL is going to be used on other machines, set the runtime libraries option to Multithreaded (not the DLL version).  Then the other DLL's will be statically linked into your DLL (downside is your DLL gets bigger).

The other warning error you noted was Modules with different CPU types - check that all your routines have been compiled for x86.

Check the instructions for dependencywalker - some of the missing dependencies (esp. the delay loaded ones) are not an issue.

Regards,

David

[Steven_L_Intel1]

Dependency Walker doesn't follow WIndows' rule for how it searches for DLLs on a 64-bit system. Windows will skip over "wrong architecture" DLLs but Dependency Walker just looks at the first one it finds and gripes.

Your DLL is 32-bit so you definitely don't want the "em64t" (Intel 64 or x64) version of libmmd.dll to be used. However, I think the recommendation to use the static libraries makes the most sense in your situation.

[DavidWhite]

Further to Steve's comment about Dependency Walker on 64-bit systems, there is a 64-bit version.  I think this may behave better with regard to architecture issues.

David

[Steven_L_Intel1]

Sadly, it does not.

[Vahid_B_]

Thanks David and Steve,

I set the runtime libraries option to Multithreaded and My DLL file worked in Excel but now when I try to run the same DLL file in R it gives me the following error:

Error in inDL(x, as.logical(local), as.logical(now), ...) : 
  unable to load shared object 'C:/Vahid/GMPE_Libx32.dll':
  LoadLibrary failure:  %1 is not a valid Win32 application.

Could you please help me to solve this problem.

Thanks,

[Steven_L_Intel1]

Is your R executable 32-bit or 64-bit? Use Dependency Walker to check. The DLL must match the platform.

[Vahid_B_]

My R executable is 64-bit. It gives the same error for both 32-bit and 64-bit versions of DLL. Should I change anything in project properties when I want to create DLL for R? The 32-bit version of DLL works fine with Excel VBA. 

 Thanks,

Vahid

[Steven_L_Intel1]

That's because you are using a 32-bit version of Office (which is typical.) If your DLL is 32-bit you'll want to use the 32-bit R. But it seems that like VBA, R gives rather useless error messages for loaded DLLs. Try building your DLL with the static libraries (Fortran > Libraries > Runtime library > Multithreaded.