A example to pass User Defined Data between CVF and Excell VBA

[jinny]

HI,

I just successfully passed some user defined data between CVF and Excell VBA using DLL. If anybody is interested, I post my source codes here for your reference.


CVF source code:
******************************************************
! ftranavg.f90
!
! FUNCTIONS/SUBROUTINES exported from ftranavg.dll:
! ftranavg - subroutine
!
subroutine ftranavg(passdata,passing)

! Expose subroutine ftranavg to users of this DLL
!
!DEC$ ATTRIBUTES DLLEXPORT::ftranavg
!DEC$ ATTRIBUTES ALIAS:'ftranavg'::ftranavg
!DEC$ ATTRIBUTES STDCALL::ftranavg
!DEC$ ATTRIBUTES REFERENCE::passdata,passing
type newdata
sequence
integer m
integer n
double precision reading(5,15)
double precision feedback(5)
end type newdata
type newing
sequence
integer mym(2)
integer nyn(2)
end type newing
! Variables
type (newdata) :: passdata
type (newing) :: passing
double precision sum
open (1,file='c: ewtest.d',form='formatted',status='unknown')
close(1,status='delete')
open (1,file='c: ewtest.d',form='formatted',status='new')
write(1,10) sum,passing%mym(1),passing%mym(2),passing%nyn(1),passing%nyn(2),passdata%m,passdata%n
do i=1,5
do j=1,15
write(1,11) passdata%reading(i,j)
enddo
enddo
close (1)
sum=0.0
! Body of ftranavg
do i=1,5
do j=1,15
sum=passdata%reading(i,j)+sum
enddo
passdata%feedback(i)=sum/15
enddo
10 format(f10.3,6i10)
11 format(f10.3)
Return
end subroutine ftranavg

******************************************************

VBA source code:
*****************************************************
Declare Sub ftranavg Lib "c:VBSfortrantest userdefinedtypeDebugaootest.dll" (ByRef newdata As mydata, ByRef newing As mying)
Type mydata
iaa As Long
ibb As Long
rmtemp(4, 14) As Double
feedback(4) As Double
End Type

Type mying
ia(1) As Long
ib(1) As Long
End Type

Sub test()
' Dim rmtemp(5, 15) As Double
' Dim feedback(5) As Double
Dim newdata As mydata
Dim newing As mying
m = 5
n = 15
newing.ia(0) = m
newing.ia(1) = n
newing.ib(0) = m + 1
newing.ib(1) = n + 1
newdata.iaa = m + 2
newdata.ibb = n + 2
For j = 0 To 4
For i = 0 To 14
newdata.rmtemp(j, i) = 70 * j + i
Next i
Next j
Call ftranavg(newdata, newing)
For i = 1 To 5
Worksheets("sheet1").Cells(1, i).Value = newdata.feedback(i)
Next i
End Sub

********************************************************

Several tips about passing data between CVF and VBA:
1) Don't forget that VBA use 0 base arrays. Hence when pass arrays using their head address, use array(0,0) instead of array (1,1). And when define the same array in VBA and CVF, the VBA size needs to be 1 less than CVF. For instance, define an array A(5,7) in fortran, the same array needs to be defined as A(4,6) in VBA.

2) When passing integer type data, define the data as integer in CVF. But define it as Long in VBA.

3) When passing an USER-DEFINED-DATA, only pass the data name without anymore information.

4) Other useful links about passing data between CVF and VBA:
A previous answer in forum:
http://www7.compaq.com/forum?14@179.M4d4eJ2sWED^1@.ee93fb0
Newsletter:
http://h18009.www1.hp.com/fortran/visual/dvf-news-1.txt

Good Luck!

Jinny