Excel call to Fortran DLL function with array argument

[olmak]

Hi, I need a little help in building and calling a Fortran DLL from within an Excel spreadsheet. The exported Fortran function contains a bunch of arguments (more than 20). Therefore, I guess it would be more convenient for an Excel user to mark the corresponding number of cells at once (e.g. FUNC(A1:A20)) instead of treating each cell from A1 to A20 as single arguments (e.g. FUNC(A1;A2;A3;...;A20)). The question is how do I declare this array in the corresponding Excel add-in and how do I have to declare the array in the Fortran routine?

Furthermore, is it possible to directly transfer the information of a complete array from a Fortran DLL to a corresponding number of Excel cells at once (with one call) or am I limited to single elements?

Many thanks in advance!

Message Edited by olmak on 07-12-2004 05:08 AM

[wkramer]

Here is an example (for CVF).

You should call the VBAFunc function from Excel as an "array formula"

(first select the range of cells in which you want the array result returned, then type the function and enter the formula with CTRL+SHIFT+ENTER)

I explicitly didn't type the Arr() variable and VBAFunc (not even as VARIANT), because that is the only way I could make it work.

Walter Kramer

Code:

VBA Code

Option Explicit
Option Base 1
Private Declare Function FortranFunc Lib "MyDll.dll" (n As Long, ArrIn As Double, ArrOut As Double) As Double

Public Function VBAFunc(ExcelCells As Range)
  Dim cell
  Dim Arr()
  Dim ArrIn() As Double
  Dim ArrOut() As Double
  Dim Temp As Double
  n As Long
  n=0
  For Each cell In ExcelCells
    n = n + 1
  Next cell
  ReDim ArrIn(n)
  ReDim ArrOut(n)
  For i = 1 To n
    ArrIn(i) = ExcelCells.Item(i).Value
  Next i
  Temp = FortranFunc(n,ArrIn(1),ArrOut(1))
  For i = 1 To n
    Arr(i) = ArrOut(i)     
  Next i
  VBAFunc = Arr
End Function


Fortran Code

Real(8) Function FortranFunc(n, ArrIn, ArrOut)
  !dec$ attributes dllexport, alias : "FortranFunc" :: FortranFunc
  Integer(4), intent(in) :: n
  Real(8), intent(in) :: Arr1(n)
  Real(8), intent(out) :: Arr2(n)
  ArrOut=ArrIn
  FortranFunction=1.D0
End Function FortranFunc

[olmak]

Thank you very much!

It works. But instead of horizontal Excel cell ranges I would like to choose vertical arrays for input and output. Is this possible? Applying your example to vertical cell ranges only returns the first value of the input array in each cell of the vertical output range.

[olmak]

In the meantime I helped myself by applying the MTRANS function of Excel (German version) to VBAFunc (by treating MTRANS as array formula with VBAFunc as argument and pressing CTRL+SHIFT+ENTER).

Message Edited by olmak on 07-13-2004 06:19 AM

Message Edited by olmak on 07-13-2004 06:28 AM

[juergel]

Hi,

normally it doesn't matter for the range-object if you have columns or rows.

If you have only a singl vector the number of elements is:

ExcelCells .count

if you have a matrix it's:

ExcelCells.rows.count

ExcelCells.columns.count

You see, it's very easy. Writing Excells. you will see all the methods and events for the range-object.

Regards

Ute

[wkramer]

Hi,

Indeed I never had a problem reading in a range object (using the count property is a good tip), butwriting to a range as output from an array formulagives the problem as mentioned by Olmak.

By the way you can transpose the array within the VBA function:

VBAFunc = Arr ' returns a row vector

VBAFunc = Application.Transpose(Arr) ' returns a column vector

Regards,

Walter Kramer

[anthonyrichards]

You then call your FORTRAN function, compute the answers, put them into array 'Answers' of specified type and then return the answers to the labelled EXCEL cells using

You then call your FORTRAN function, compute the answers, put them into array 'Answers' of specified type and then return the answers to the labelled EXCEL cells using

Range("answers").value=Answers
End Sub

[anthonyrichards]

I have tried twice to post an answer to the original poster, but I have lost most of the text each time, with the results you see above, whenI have pre-viewed it. I am not going to do it again. Life is too short. Please fix the software!!!

[anthonyrichards]

I attach an example of the use of Visual Basic calls to FORTRAN functions and subroutines in a DLL created as a DLL project using Compaq Visual Fortran. An EXCEL file shows the use of a VBA subroutine call (triggered by pressing a button)to feed a cell range of values into a FORTRAN subroutine call and to feed the resulting array of values returned by the FORTRAN DLL back to a named cell range. It also demonstrates the use of a FUNCTION call in an equation used as an ArrayFormula (created by highlighting the cells to receive the answers, using theformula bareditor to define theformula and then CTRL+Shift+Enter to enter the formula into each of the highlighted cells) and the return of an vector of values to the selected cells. I enclose the simple FORTRAN code that is used to demonstrate the process. Hope this helps