Moving through the Recordset in Access VBA

Question

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

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

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?

Answer 1

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

Recordset.GetRows Method

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

vZeros = rsCurve.GetRows(rsCurve.RecordCount)

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

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