An earlier series of posts described how to link Excel to programs written in C or C++. The same advantages of increased security and performance may be obtained by linking to Fortran programs with the additional advantages:
- Many specialist engineering and scientific programs are only available in Fortran
- The structure and syntax of Visual Basic is closer to Fortran than C
- Fortran has features for the manipulation of arrays that are particulary useful for engineering and scientific analysis.
- The linking process is much simpler
This post will look at creating four simple maths functions in Fortran, and linking them so that they are available in Excel, either to VBA routines, or directly from the spreadsheet. Later posts will look at creating more complex and useful programs, including passing arrays, and some catches in the handling of floating point numbers that need to be watched for.
The examples will all be using Silverfrost Personal Fortran, which is available for download, and is free for non-commercial use.
To create MathFunc.dll
1. Create a new directory called MathFunc at any convenient location
2. Start up the Plato IDE (the Silverfrost Fortran editor)
3. Create a new dll project called MathFunc:
Select File – New Project
Select Fortran DLL
Enter MathFunc for the name
Browse for the MathFunc directory that you previously created
Craete new Fortran DLL Project
4. Create a new file in the project: Project – Add New Item; select Free Format Fortran file, and enter MathFunc.f95 for the name.
5. Enter or copy and paste the code shown below:
! MathFuncsF_stdcall function ADD(a, b)
REAL(2), intent(in) :: a, b
REAL(2) ADDADD = a + b
RETURN
end function ADDF_stdcall function SUBTRACT(a, b)
REAL(2), intent(in) :: a, b
REAL(2) SUBTRACT
SUBTRACT = a - b
RETURN
end function SUBTRACT
F_stdcall function MULTIPLY(a, b)
REAL(2), intent(in) :: a, b
REAL(2) MULTIPLY
MULTIPLY = a * b
RETURN
end function MULTIPLY
F_stdcall function DIVIDE(a, b)
REAL(2), intent(in) :: a, b
REAL(2) DIVIDE
IF(b == 0) THEN
DIVIDE = 0
ELSE
DIVIDE = A/B
END IF
RETURN
end function DIVIDE
6. Select Build – Build, or click the Build icon.
7. That’s all.
The program will create a file called MathFunc.dll in a subdirectory: \CheckMate\Win32\. When debugging is complete you can select “Release Win32” from the drop-down list at the top of the editor screen, and a release version of the dll will be created in \Release\Win32\.
To make the four functions in MathFunc.dll available in Excel:
1. Open a new workbook and save it as MathFunc.xls
2. Open the Visual Basic Editor (press Alt-F11), insert a new module, and enter the following lines of code:
Declare Function ADD Lib "D:\Users\...\MathFunc\CheckMate\Win32\poly.dll" (A As Double, b As Double) As Double
Declare Function SUBTRACT Lib "D:\Users\...\MathFunc\CheckMate\Win32\poly.dll" (A As Double, b As Double) As Double
Declare Function MULTIPLY Lib "D:\Users\...\MathFunc\CheckMate\Win32\poly.dll" (A As Double, b As Double) As Double
Declare Function DIVIDE Lib "D:\Users\...\MathFunc\CheckMate\Win32\poly.dll" (A As Double, b As Double) As Double
… adjusting the file path to the full path name for your file locations.
The four new MathFunc should now be available in the worksheet, just like the built in Excel functions:
MathFunc functions called from spreadsheet
The Fortran files and Excel spreadsheet described in this post may be downloaded from: MathFunc.zip
Newton Excel Bach, not (just) an Excel Blog 
Excellent tutorial. I have struggled with acessing FTN95 DLL with VB6, have just got it working with this example. A useful note for anyone else trying the same:- make the declare in a module only in VB6 and the function call must be in uppercase letters.
Cheers
LikeLike
Thanks for the comments David. Good point about the function call needing to be upper case. I meant to mention that but it somehow got left out.
LikeLike
This is a great piece.
I wondering if you have some post on how to read from excel and write into excel
LikeLike
Samuel – you mean how to read and write from/to an Excel file in Fortran?
I don’t have any plans for doing anything on that at the moment. I’d be inclined to write the Excel data to a text file in a suitable format for the Fortran program myself, using a VBA routine.
There is a commercial program that does what you want, if you search on reading excel files fortran.
LikeLike
Hi all I want to do is take a x/y array from my fortran 95 prog an write them into two columns in Excel so I can take advantage of the graphics therein…tried this using F90SQL s/w followed there examples it almost worked but failed…no support backup to the s/w…
LikeLike
cannot done in my excel, how to trace the error?
LikeLike
jang – based on the information provided, I have no idea.
If you provide your Excel version and Fortran compiler name and version, a description of the steps you went through and the error you are getting then I might be able to help.
LikeLike
hi dougaj4, thanks for the replay.
I have solved it, something wrong with my fortran compiler.
Great job!!
LikeLike
Hi,
I’m currently doing a project that requires me to translate a Fortran source code into the Excel-VBA format. Just wondering, what would be the best way to start? I’ve tried compiling the source code and currently am having trouble in executing it as there are quite a number of errors. Some have adviced me to convert it without the need of understanding the source code, bt directly translate the coding from the inputs available in the source code. What would be the best way to start out here??
LikeLike
Translating code that doesn’t work without understanding what it is doing sounds like asking for trouble to me.
The approach I’d adopt is to find all the input and output required, which will require working through the code and an understanding of the process. I’d then write code to collect the input data and write the output from scratch. That will leave the intermediate processing which should be fairly straightforward if the Fortran is well written, because the control and looping structures in Fortran and VBA are quite similar. If the Fortran code is not well written it might be easier to extract the algorithm from the code and than use that to write the VBA from scratch.
An alternative worth considering is to write the front and back ends of the program in VBA then link to a compiled Fortran routine to do the main processing, as described in the blog article. It really depends how comfortable you are working in Fortran, and how much modification is required to get it running reliably.
LikeLike
Hi Doug, thank you for a very nice blog post. I have tried for a few hours to get his working on my machine but without success. One thing that I found confusing where the references to “Declare Function ADD Lib “D:\Users\…\MathFunc\CheckMate\Win32\poly.dll” (A As Double, b As Double) As Double” poly.dll that is not the dll included in the zip file that one I believe is the MathFunc.dll.
My computer set up is Windows 7 64 bit, 64 bit Excel 2010 and using compiler, Silverfrost Plato 4.5.1, tried both the release and debuggger modes. When calling from VBA 7 or 64 bit seems necessary to include the keyword PtrSafe in the function declaration but that seemed fine. Had somewhat of battle when I did it with C/C++ but got that one working. The only error I get is in Excel the cells turning up with value error message.
In your other post the one Fortran 2 there was the correct dll and changing that to PtrSafe seemed to do the trick, so I am assuming that there is something strange with the compilation of the Fortran file, but yet again I did not get any error messages.
I have also tried another Complier, simply fortran but that one does not seem to support the F_stdcall command in front of the function. Did find some other information about something similar on http://www.emagenit.com/FORTRAN%20DLL.htm where they are using commands like !MS$ ATTRIBUTES DLLEXPORT:: FinVel
!MS$ ATTRIBUTES ALIAS: ‘FinVel’:: FinVel
I guess these would correspond to definition files used when doing the C/C++ route. Also did not get that example to work. Any ideas of what I am doing wrong? Extending Excel with Fortran seems like a nice tack to add some power to the the GUI.
LikeLike
Hi cjrberg – I will need to take a closer look at this when I have more time, but I did find just recently that the Fortran functions were not working on my current (Windows 7, 32 bit) computer, but were working on my previous machine (Windows Vista). I don’t know if this is a Windows problem or an installation problem.
I’m tied up with other work at the moment, but I will review all my files linking to dlls as soon as I get time, and if I find anything useful I’ll publish it here.
LikeLike
Pingback: Daily Download 18: Simultaneous Equations and Matrix Arithmetic Functions | Newton Excel Bach, not (just) an Excel Blog
Pingback: Daily Download 31: Linking to Fortran | Newton Excel Bach, not (just) an Excel Blog
Pingback: Programas en Fortran desde Excel
Pingback: Run Fortran from Excel with Python and F2Py | Newton Excel Bach, not (just) an Excel Blog
Hi dougj4, I have tried to follow your steps but no function. This computer is win7.
Even rebuilded by paste your code and debugg according to instruction-no errors.
Is there a problem with win7?
LikeLike
Unfortunately I haven’t had time to look into problems running the Silverfrost compiler with Win 7 or 8. Recently I have been concentrating on using Python with the ExcelPython add-in, and this gives a route to Fortran using the F2PY package, which does work with Win 7. See the link at the bottom if you are interested in trying this way.
LikeLike
Hi Dougaj,
I have a .f90 source and a compiled to get a dll file. it works in excel 32 bit but not excel 64 bit, I have two machines ,
1.windows 8.1 x64 and office 2013 32 bit.- it works with this combination
2.windows 8.1 x64 and office 2013 64 bit- does not work , gives an runtime 48 error dll file not found .
I have been breaking my head for this to work in the 64 bit excel.
LikeLike
Hi Arun, I’m afraid all I can suggest is trying the F2PY route, and then linking to that with ExcelPython. It might seem a very roundabout way of doing it, but it should work with 64 bit Excel (I can’t guarantee that as I am still on 32 bit Office with 64 bit Windows).
LikeLike