how to pass arrays between Fortran and VBA

[ripittard]

I am trying to pass arrays of doubles/real*8 between excel vba (64 bit) and fortran..  Both direct calls to exported fortran dlls and function callbacks into vba are required.  

I can transfer individual variables just fine but when it comes to arrays I am struggling.  I have seen references to VB.NET-Safearrays sample, but don't know where to find this (I have Fortran 2022.0 installed).

Can someone please direct me towards an example or documention on passing variants/safearrays in and out of Fortran?

Thanks,

[Steve_Lionel]

Samples are here:. But for simple numeric arrays you don't have to dive into SafeArrays. The VB_Calls_Fortran sample demonstrates passing an array of reals.

[DavidWhite]

Have a look for my example here

Linking FORTRAN subroutine to Excel VBA... and failing - Intel Communities

Particularly the code for the seawater density

[ripittard]

Thanks David you provided what I asked for,  The "VB_Calls_Fortran" sample works when teh arrays are created in vba.  I am a bit slow on this (it has been decades since I coded in Fortran and things have changed).

Initiaitng a call from VBA to Fortran is fine and I can easily pass the first element of an array of doubles to Fortran and it can access/update them.  My code works for this.  However, I do need to provide a callback into vba, passing an array of REALs  that it uses and it needs to return another array of doubles.  To do this I include the address of a vba function in the initial call for Fortran and then it uses this to call back into vba.  If the values I am passing are scalers then there is no problem, but I can see how to have vba acccess the array unless I somehow create a Variant for it in Fortran.  Sample code below works for passign arrays to fortran but not in passing fortran arrays to vba.  Any thoughts?  

VBA:  

Private Declare PtrSafe Function action Lib "C:\Users\Rick\source\repos\FortDLL\x64\Debug\FortDLL.dll" _
(ByVal functionaddress As LongLong _
) As Integer

Sub exampledllcall()
Dim i As Integer
Dim vec(1 To 11) As Double
Dim rval, cval As Double
Dim res As Double

For i = 1 To 10
vec(i) = i
Next i
rval = 10
cval = 5

res = result3(vec(1), rval, cval)
MsgBox ("res : " & CStr(res))

Call action(VBA.Int(AddressOf callback))
MsgBox (" after action")

End Sub

Function callback(X As Double, f As Double, C As Double) As Integer

MsgBox ("X is " & CStr(X))
MsgBox ("F is " & CStr(f))
MsgBox ("C is " & CStr(C))

callback = 1
End Function

FORTRAN:

real*8 function result3(X, value1, value2) BIND(C)
!DEC$ ATTRIBUTES STDCALL, DLLEXPORT :: result3
use, intrinsic :: ISO_C_BINDING

implicit none
real(kind=8),dimension(100),intent(inout) :: X
real(kind=8) :: value1, value2
integer :: unit

result3 = value1
unit = 25
open(newunit=unit, file="C:\Users\Rick\source\repos\FortDLL\x64\Debug\log.txt", access="append")
write(25,*) "result10"
write(25, *) " X(1) and X(2) are ", X(1), X(2), NEW_LINE('a'),"value1 and value2 are ", value1, value2
write(25, *) NEW_LINE('a')
close(25)

return
end function

subroutine action(vbfunction) bind(c)
!DEC$ ATTRIBUTES STDCALL, DLLEXPORT :: action
use, intrinsic :: ISO_C_BINDING

implicit none
interface ! for call back into VBA
logical function vbfunction(A, B, C) bind(c)
use, intrinsic :: ISO_C_BINDING
real*8,dimension(100),intent(inout) :: C
real*8, dimension(100), intent(out) :: A
real*8, intent(inout) :: B
end function
end interface

integer :: u, i, j
real*8, dimension(100) ::X, C
real*8 :: F
logical :: res

X = 5
C = -1
f = 0

res = vbfunction(X, f, c)

end subroutine

[DavidWhite]

I've never tried calling back into the VBA from Fortran, but from my experience going the other way, there seems to be less difficulty with variable types and handling information using Subroutine calls instead of Functions.  If you change the VBA entry to a Sub instead of a function, it may be easier.  If you can't change the VBA function, then write a Sub wrapper on the VBA side, which you call from Fortran, and which does the call to the VBA function.

[ripittard · Accepted solution]

I found a solution at http://www.axelvogt.de/axalom 

described in two docs: Reading_and_Writing_Arrays_across_Excel_and_DLLs.pdf and Working_with_Array_Functions_and_DLLs_in_Excel_VBA.pdf, both are attached.

Effectively the receiving VBA function (a callback) receives the addrss to arrays that it needs and then uses RtlMoveMemory from kernal32 to copy the specified number of bytes into a VBA array.

I'm sure using safeaarrays is another way to do this but this is now working for me.