Passing Arrays from MS Excel to a DLL

[Intel_C_Intel]

Hi

(1)
I was delighted to (eventually) be able to access a FORTRAN 6.1 DLL from MS
Excel. The example DLL I made and used had only discrete numerical inputs
and these were supplied by simply selecting the appropriate spreadsheet
cells after typing the function name, adding the commas, etc. So far, so
good.

My next attempt involved arguments that consisted of arrays of numbers. The
problem comes with the need to supply FORTRAN only the first element of an
array (or, the address?). How can I supply only the first element when
selecting a range of cells in the spreadsheet? Also, what is the correct way
to declare a function in VBA so that it recognizes that a particular
argument is an array?

The FORTRAN function looks like :

Function LINEARLOOKUP(X,XARRAY,YARRAY,NUMENTRIES)

and my VBA declaration :

Declare Function LINEARLOOKUP Lib "LibName.DLL" Alias "_LINEARLOOKUP*16"
(Byval X as Single,ByVal XARRAY() as single,byVal YARRAY() as
single,NUMENTRIES as long) as single

The result, when attempting to supply the array arguments as a selection of
cells is #VALUE! I'm stumped and terribly frustrated. Please help if you
can.

(2) I am u user in the JSF program at Boeing and as such I do not have
access to the installation disks; is there some way I can become a recipient
of the VF Newsletter?

Thanks in anticipation

Colin Widdison

[Intel_C_Intel]

(1) You don't really show enough info about LINEARLOOKUP to be definitive about your code, although I'll make some general statements.
It's unlikely that you want byVal for any of the parameters in the VB/VBA Declare statement. Also, don't use "XARRAY() as single" for the array, instead use "XARRAY as Single". The former is a topic for a more advanced discussion. The latter is what you want to pass the first element of the array in that position.

Here's a simple example for you:

 
! f90 
subroutine Severity(lambda, pmean, pstd, numsims, sims) 
  implicit none 
  !DEC$ ATTRIBUTES DLLEXPORT :: Severity 
 
  ! dummy arguments 
  real, intent(IN) :: lambda, pmean, pstd 
  integer, intent(IN) :: numsims 
  real, dimension(1:numsims), intent(OUT) :: sims 
 
  ! ... 
end subroutine Severity 
 
------------- 
' Excel VBA code for Sheet1 
Private Declare Sub _SEVERITY@20 Lib "Psnd.dll" Alias "Severity" (lambda As _ 
Single, pmean As Single, pstd As Single, numsims As Long, sims As Single) 
 
Sub sims2() 
    Dim mysim() As Single, i As Long, TargetRange As Range 
    ReDim mysim(1 To [Simulations], 1 To 1) 
     
    Call Severity([lambda], [Mean], [StDev], [Simulations], mysim(1, 1)) 
    Set TargetRange = Range(Range("TopLeft").Offset(0, 5), Range("TopLeft").Offset([Simulations] - 1, 5)) 
    TargetRange.Value = mysim 
' ... 
End Sub 

note:
lambda, Mean, StDev, Simulations are range names (the brackets - [] - are the evaluate operator). XL columns map to vectors as N x 1 arrays. This passes the array mysim to the f90 code by passing mysim(1, 1) by reference, the address of the start of the array is passed to the f90 code - which is what it expects in this case.

(2) If you didn't know already, you can catch up all but the most recent newsletters at http://www6.compaq.com/fortran/visual/newsletter.html.

hth,
John

[Intel_C_Intel]

Here is how someone did what you want. The following text was saved from
a thread in the previous message board.

QUOTE"

When VBA code passes the first element of an array to a Fortran DLL,
then the address of that array element is passed,
which is what Fortran expects to receive.

Using named ranges in Excel makes pasting in an array of
values returned by Fortran relatively easy. My practice is to use
the same names for the named ranges in my worksheet and the corresponding
arrays in my VBA code. You can then use VBA code something like this:

Option Explicit
Option Base 1
'
' Declare VLE_CALC as DLLEXPORTed subroutine in
' Fortran DLL file core_routine.dll. Arrays
' componentMass and bublMassGas are double precision
' arrays passed explicitly by reference.
'
Private Declare Sub VLE_CALC Lib "core_routine.dll" _(ByRef componentMass As Double, _
ByRef bublMassGas As Double)

Sub calcVLEBtn_Click()
'
' Declare the componentMass and bublMassGas arrays as
' both being componentArraySize in length. Variant
' array componentMassVnt corresponds to double
' precision array componentMass.
'
Const componentArraySize As Long = 50
Dim counter As Long
Dim componentMass(componentArraySize) As Double
Dim componentMassVnt As Variant
Dim bublMassGas(componentArraySize, 1) As Double
'
' Select the worksheet on which the named range
' componentMass exists. Read the range into the
' variant array componentMassVnt in a single
' statement. The same thing could be done in a For
' loop.
'
Worksheets("VLE_calc").Select
componentMassVnt = Range("componentMass").Value
'
' The For loop copies the values from the variant
' array componentMassVnt into the double precision
' array componentMass. It also initializes the
' bublMassGass array.
'
For counter = 1 To componentArraySize Step 1
componentMass(counter) = CDbl(componentMassVnt(counter, 1))
bublMassGas(counter, 1) = CDbl(0)
Next counter
'
' Call the VLE_CALC subroutine in the Fortran DLL
' passing the componentMass array as input and the
' bublMassGas array as output.
'
Call VLE_CALC(componentMass(1), bublMassGas(1, 1))
'
' Copy the values in the bublMassGas array into the
' named range bublMassGas in the worksheet. You can
' also use a For loop to do the same thing.
'
Range("bublMassGas").Value = bublMassGas
'
End Sub

"UNQUOTE

HTH

Tony Richards