在Access VBA中移动Recordset

Question

我有一个使用Excel VBA计算波动率的简单函数。它将一列数字（零）和两个日期作为输入。代码是：

Function EWMA(Zeros As Range, Lambda As Double, MarkDate As Date, MaturityDate As Date) As Double 

    Dim vZeros() As Variant 
    Dim Price1 As Double, Price2 As Double 
    Dim SumWtdRtn As Double 
    Dim I As Long 
    Dim m As Double 

    Dim LogRtn As Double, RtnSQ As Double, WT As Double, WtdRtn As Double 

vZeros = Zeros 

m = Month(MaturityDate) - Month(MarkDate) 

For I = 2 To UBound(vZeros, 1) 

    Price1 = Exp(-vZeros(I - 1, 1) * (m/12)) 

    Price2 = Exp(-vZeros(I, 1) * (m/12)) 

    LogRtn = Log(Price1/Price2) 

    RtnSQ = LogRtn^2 

    WT = (1 - Lambda) * Lambda^(I - 2) 

    WtdRtn = WT * RtnSQ 

    SumWtdRtn = SumWtdRtn + WtdRtn 

Next I 

EWMA = SumWtdRtn^(1/2) 

End Function 

启用函数的主要功能是For循环。我想在Access VBA中使用记录集对象重新创建它。记录集与Excel电子表格具有相同的字段。虽然我不确定如何转换代码。这是我到目前为止：

Function EWMA(rsCurve As Recordset, InterpRate As Double, Lambda As Double) As Double 

    Dim vZeros() As Variant 
    Dim Price1 As Double, Price2 As Double 
    Dim SumWtdRtn As Double 
    Dim I As Long 
    Dim mat As Date 
    Dim mark As Date 

    Dim LogRtn As Double, RtnSQ As Double, WT As Double, WtdRtn As Double 


    CurveInterpolateRecordset = Rnd() 

    If rsCurve.RecordCount <> 0 Then 

    vZeros = CVar(rsCurve.Fields("CurveInterpolateRecordset")) 

    mat = CDate(rsCurve.Fields("MaturityDate")) 
    mark = CDate(rsCurve.Fields("MarkDate")) 

    m = Month(mat) - Month(mark) 

For I = 2 To UBound(vZeros, 1) 

    Price1 = Exp(-vZeros(I - 1, 1) * (m/12)) 

    Price2 = Exp(-vZeros(I, 1) * (m/12)) 

    LogRtn = Log(Price1/Price2) 

    RtnSQ = LogRtn^2 

    WT = (1 - Lambda) * Lambda^(I - 2) 

    WtdRtn = WT * RtnSQ 

    SumWtdRtn = SumWtdRtn + WtdRtn 

Next I 

EWMA = SumWtdRtn^(1/2) 

End If 

     Debug.Print EWMA 

End Function 

函数在Access中的一个较早的子例程中调用。为了在Access中移动记录集，我错过了什么，类似于在Excel VBA中循环显示电子表格？

Answer 1

最简单的方法是使用GetRows从您的记录拉数组：

Recordset.GetRows Method

Th恩新代码将几乎你证明代码的副本正贴开始基本上是这样的：

vZeros = rsCurve.GetRows(rsCurve.RecordCount) 

作为一个侧面说明，你就不需要CDATE这里：

mat = rsCurve.Fields("MaturityDate").Value 

Answer 2

以下是关于使用记录集的一些基本知识。

Dim rs As New ADODB.Recordset 

'Add fields to your recordset for storing data. 
With rs 
    .Fields.Append "Row", adInteger 
    .Fields.Append "ColumnName2", adChar, 30 
    .Fields.Append "ColumnName3", adInteger 
    .Open 
End With 

记录添加到它手动

rs.AddNew 
rs.Fields("Row").Value = 1 
rs.Fields("ColumnName2").Value = "Put some value in" 
rs.Update 

rs.AddNew 
rs.Fields("Row").Value = 2 
rs.Fields("ColumnName2").Value = "Put another value in" 
rs.Update 

你也可以用表的查询来填充它。

移动到记录

If rs.EOF = False Then 
    rs.MoveFirst 
End If 

循环的开始时，通过记录

Do While rs.EOF = False 

     msgbox(rs.Fields("ColumnName2").Value) 

rs.MoveNext 
Loop