在Excel VBA中使用Fortran子程序创建DLL



我的目标是找到一种在Excel VBA中调用Fortran子程序的方法(可以在Alan Genz教授上找到)。该程序是MVNPACK)计算多元正态分布的CDF。理想情况下,我希望能够使用一个版本的DLL从源代码编译在一个c#项目以及在未来。但是,我不确定如何排除故障并进一步进行。我通常用Python编写代码,有一些C, Java等,但从不使用Fortran,也不太熟悉调用DLL中的函数时会发生什么。据我所知,这种计算并不是很普遍,编译Fortran源代码是我最好的选择。

我一直密切关注这里关于创建DLL的例子,这里关于在Excel VBA中使用它,并一直试图模仿结果。从上面提到的MVNPACK源代码开始,我认为我需要的是将输入传递给子例程MVNDST,并通过将指针作为参数传递给子例程来获得结果。所以我做的第一件事就是试着根据这些例子来修改代码。我修改的版本MVNDSTC是这样的。

SUBROUTINE MVNDSTC( N, LOWERC, UPPERC, INFINC, CORRELC, MAXPTS,
&                    ABSEPS, RELEPS, ERRORC, VALUEC, INFORMC)
& bind(c)
use ISO_C_BINDING
implicit none
cGCC$ ATTRIBUTES STDCALL, DLLEXPORT :: MVNDSTC
EXTERNAL MVNDFN

integer(kind=c_long), value:: N, MAXPTS
real(kind=c_double), value:: ABSEPS, RELEPS

type(c_ptr), value:: LOWERC, UPPERC, INFINC, CORRELC
type(c_ptr), value:: ERRORC, VALUEC, INFORMC

real(kind=c_double), dimension(:), pointer:: LOWER, UPPER, CORREL
integer(kind=c_long), dimension(:), pointer:: INFIN
real(kind=c_double), dimension(:), pointer:: ERROR_OUT, VALUE_OUT
integer(kind=c_int), dimension(:), pointer:: INFORM_OUT

INTEGER NN
INTEGER INFORM, INFIS, IVLS
DOUBLE PRECISION ERROR, VALUE, E, D, MVNDNT, MVNDFN
COMMON /DKBLCK/IVLS

NN = (N - 1) * N / 2
call C_F_POINTER(LOWERC, LOWER, [N])
call C_F_POINTER(UPPERC, UPPER, [N])
call C_F_POINTER(INFINC, INFIN, [N])
call C_F_POINTER(CORRELC, CORREL, [NN])
call C_F_POINTER(ERRORC, ERROR_OUT, [1])
call C_F_POINTER(VALUEC, VALUE_OUT, [1])
call C_F_POINTER(INFORMC, INFORM_OUT, [1])

IF ( N .GT. 500 .OR. N .LT. 1 ) THEN
INFORM = 2
VALUE = 0
ERROR = 1
ELSE
INFORM = MVNDNT(N, CORREL, LOWER, UPPER, INFIN, INFIS, D, E)
IF ( N-INFIS .EQ. 0 ) THEN
VALUE = 1
ERROR = 0
ELSE IF ( N-INFIS .EQ. 1 ) THEN
VALUE = E - D
ERROR = 2D-16
ELSE
*
*        Call the lattice rule integration subroutine
*
IVLS = 0
CALL DKBVRC( N-INFIS-1, IVLS, MAXPTS, MVNDFN, 
&                   ABSEPS, RELEPS, ERROR, VALUE, INFORM )
ENDIF
ENDIF
VALUE_OUT(0) = VALUE
ERROR_OUT(0) = ERROR
INFORM_OUT(0) = INFORM
END

然后我创建了一个小的子程序,上面有mvndstc声明。VBA代码如下:

Private Declare PtrSafe Sub mvndstc Lib "C:UserspoopaDesktopmvnmvn_projectfortran-library.dll" _
(ByVal N As Integer, _
ByRef LOWER As Single, _
ByRef UPPER As Single, _
ByRef INFIN As Single, _
ByRef CORREL As Single, _
ByVal MAXPTS As Integer, _
ByVal ABSEPS As Double, _
ByVal RELEPS As Double, _
ByRef ERROR As Single, _
ByRef VALUE As Single, _
ByRef INFORM As Single)
Sub mvn_test()
Dim value_1(1 To 1) As Single  ' Result of the function
Dim inform_1(1 To 1) As Single  ' Information
Dim error_1(1 To 1) As Single  ' Error estimate
Dim upper_1() As Single
Dim lower_1() As Single
Dim infin_1() As Single
Dim correl_1() As Single
Dim n_1 As Long, n_1_2 As Long, max_pts_1 As Long
n_1 = 5
ReDim lower_1(1 To n_1)
ReDim upper_1(1 To n_1)
ReDim infin_1(1 To n_1)
lower_1(1) = 0#
lower_1(2) = 0#
lower_1(3) = 1.7817
lower_1(4) = 0.14755
lower_1(5) = 0#

upper_1(1) = 0#
upper_1(2) = 1.5198
upper_1(3) = 0#
upper_1(4) = 0#
upper_1(5) = 1.5949

infin_1(1) = 1
infin_1(2) = 2
infin_1(3) = 1
infin_1(4) = 1
infin_1(5) = 0
n_1_2 = Int(n_1 / 2 * (n_1 - 1))
ReDim correl_1(1 To n_1_2)
correl_1(1) = -0.707107  ' 12
correl_1(2) = 0#  ' 13
correl_1(3) = 0.5 ' 14
correl_1(4) = 0#  ' 15
correl_1(5) = 0.5 ' 23
correl_1(6) = 0.5 ' 24
correl_1(7) = 0#  ' 25
correl_1(8) = 0.5 ' 34
correl_1(9) = 0.5 ' 35
correl_1(10) = 0.5 ' 45

max_pts_1 = 625000
mvndstc n_1, lower_1(1), upper_1(1), infin_1(1), correl_1(1), max_pts_1, 0.00005, 0, error_1(1), value_1(1), inform_1(1)
Debug.Print "Value = " & (value_1(1))
Debug.Print "Error Est = " & (error_1(1))
Debug.Print "Inform = " & inform_1(1)
End Sub

现在我的第一次尝试我没有修改ERROR, VALUE, INFORM参数,并简单地在Fortran中声明它们各自的基本类型。我实际上可以运行VBA子程序,但我得到的结果都是零。所以我推测程序运行了,但也许我没有得到正确的结果,我应该把这三个输出作为大小为1的指针。这样,我只是保持任何过程完全相同的Fortran,然后如果我把VALUE_OUT(0) = VALUE等,在函数结束之前,我应该得到的结果很好。现在使用我在这里发布的代码,我实际上可以看到在VBA中打印出来的结果,仍然是全零,但之后Excel会立即崩溃。

我想问一下,从这里我该如何继续?我哪里做错了?有什么值得调查的资源吗?

提前感谢。

我昨天修复了这个问题,这个问题确实是关于数据类型的。当我阅读教程时,我假设单是VBA中的某种对象类型。我一点也不知道,Single实际上是Double的单精度!我试着通过让DLL将函数内的所有值打印到一个文件来调试所有这些。

相关内容

  • 没有找到相关文章

最新更新