在Access VBA中浏览记录集 [英] Moving through the Recordset in Access VBA

问题描述

我有一个使用Excel VBA来计算波动率的简单函数.它以一列数字(零)和两个日期作为输入.代码是:

I have a simple function using Excel VBA for calculating volatility. It takes as inputs a column of numbers (Zeros) and two dates. The code is:

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电子表格相同的字段.不过，我不确定如何将代码转换过来.这是我到目前为止的内容:

The main feature enabling the function to work is the For loop. I want to re-create this in Access VBA using recordset objects. The recordset has the same fields as the Excel spreadsheet. I'm not exactly sure how to convert the code over, though. Here is what I have so far:

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中循环遍历电子表格)?

The function is called in an earlier subroutine in Access. What am I missing in order to move through the recordset in Access, similar to looping through the spreadsheet in Excel VBA?

推荐答案

最简单的方法是使用GetRows从记录集中提取数组:

The easiest method would be to use GetRows to pull an array from your recordset:

Recordset.GetRows方法

然后，新的代码将几乎是从经过验证的代码中复制并粘贴的，其基本代码如下:

Then the new code would be nearly a copy-n-paste of your proven code starting with basically this:

vZeros = rsCurve.GetRows(rsCurve.RecordCount)

作为旁注，这里不需要CDate:

As a side note you wouldn't need CDate here:

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

这篇关于在Access VBA中浏览记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！