Throughout my working career I have been intrigued by the application of FORTRAN DLLs in EXCEL spreadsheets. In reality, though, my experience of taking advantage of FORTRAN’s speed with the convenience of EXCEL’s graphical interface for input/output has mostly been relegated to a somewhat crude work-around. 

 

In the work-around, the communication between EXCEL and FORTRAN is done by running an independent DOS-type (console) FORTRAN Program from a SHELL invoked within EXCEL’s VBA.  In this scenario, the EXCEL VBA code consists of creating the necessary input file(s) for the FORTRAN program (gathering the data from one or multiple worksheets, and then waiting until all output is generated by the FORTRAN program to import the results into EXCEL through more VBA code so that it is displayed in a nicely pre-formatted way for visualization.

 

There is no question that the work-around described above gets the job done. It has some issues, though, including the fact that that the VBA code execution does not automatically wait until the FORTRAN program is completed before it moves to the next command. When the FORTRAN program runs in fractions of a second, a short 1-5 sec wait function works perfectly fine (allowing for variations in execution time based on either input or computer resources). When the FORTRAN program takes more time, then there is a decision to make: How long do you pause the VBA program before it is allowed to look for the output? Or, should a mechanism be introduced to allow for constant checking to see if the FORTRAN program is done, thereby avoiding unnecessary waits?

 

All these issues can be avoided by using a direct way of communication between EXCEL and FORTRAN, through a DLL (Dynamic Link Library). Another benefit of linking FORTRAN to EXCEL via DLLs is that doing so makes FORTRAN-developed functions available for use directly from a worksheet instead of having to “run” a macro. This makes the workbook immediately “updatable.” 

 

Having said this, DLL implementation (at least for me) has not been straight forward. The last time I tried linking EXCEL and FORTRAN through a DLL – even using a rather simple and fully explained example to obtain averages (mean, mode and median) for a selected range of values in a worksheet -- I almost gave up in frustration. CLICK HERE for a short presentation on such implementation

 

The majority of the tasks we perform in EXCEL are fast enough to make using VBA is more than adequate and use of FORTRAN programs or DLLs does not provide real value. However; for those cases when the computations get to be numerically intensive, combining the speed of FORTRAN with the graphical input/output benefits of EXCEL is definitely the way to go!  Perhaps the easy thing to do is to have a DLL with no input/output transferred directly between EXCEL and FORTRAN, and still use the work-around of creating an input file for FORTRAN and reading the output through additional VBA, but by using the DLL, the VBA code will actually WAIT for the FORTRAN program to complete before executing the rest of the VBA code. Doing so would eliminate the need to introduce “waiting” schemes. I’ll try this soon and let you know how/if it works.

​

​