Adapting an Excel VBA Function to Access VBA

Question

I am revisiting some old code in Excel VBA and trying to convert it to Access VBA.

This code takes a range of numbers ("Zeros") and calculates a simple weighted average with a weight of "Lambda". This calculation and this code works fine and has been verified as correct.

The Excel code is as follows.

Option Explicit


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 = 1 / ((1 + vZeros(I - 1, 1)) ^ (m / 12))
    Price2 = 1 / ((1 + 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

Now I've tried to reproduce this same function in Access VBA. This code references a table ("HolderTable") which consists of the exact same numbers as the "Zeros" range in the above Excel code. In Access, these are labelled "InterpRate". Then it applies exactly the same calculation as in the Excel code, except adapted for Access syntax.

The Access code is as follows:

Function EWMA(Lambda As Double) As Double

Dim Price1 As Double, Price2 As Double
Dim vInterpRate() As Variant
Dim SumWtdRtn As Double
Dim I As Long
Dim m As Double
Dim rec As Recordset


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

m = 3

Dim x As Integer

Set rec = CurrentDb.OpenRecordset("SELECT InterpRate FROM HolderTable")

x = 1

Do While rec.EOF = False

ReDim Preserve vInterpRate(x + 1)

vInterpRate(x) = rec("InterpRate")

x = x + 1

rec.MoveNext

Loop

For I = 1 To x

Price1 = 1 / ((1 + vInterpRate(I - 1)) ^ (m / 12))

Price2 = 1 / ((1 + vInterpRate(I)) ^ (m / 12))


  LogRtn = Log(Price2 / Price1)

  RtnSQ = LogRtn ^ 2

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

  WtdRtn = WT * RtnSQ

  SumWtdRtn = SumWtdRtn + WtdRtn

Next I

EWMA = SumWtdRtn ^ (1 / 2)

End Function

Ideally, these should produce the exact same numbers. The "Zeros" range and the "interpRate" numbers are identical. I suspect the problem is with how I've defined my array in Access, however I can't seem to fix it. Are there any inconsistencies between the two codes?

For reference, I have attached the Excel spreadsheet w/ the VBA code. http://www.filedropper.com/soewma_1

Is it just that x gets incremented 1 time too many in your access version? — Gordon K
i think that that might be possible, how could i change it to fix that? — beeba
Easiest way would be to add "x = x - 1" after your Do...While loop — Gordon K
Thanks, it's much closer now - but still off. I think the sizing of the array is the issue and something else must still be wrong. — beeba

Andre Andre · Accepted Answer · 2016-06-13T15:04:01

The IMHO best readable way is to start with x=0 and put the x=x+1 at the beginning of each loop:

Set rec = CurrentDb.OpenRecordset("SELECT InterpRate FROM HolderTable")
x = 0
Do While rec.EOF = False
    x = x + 1
    ReDim Preserve vInterpRate(x)
    vInterpRate(x) = rec("InterpRate")
    rec.MoveNext
Loop

And since you access vInterpRate(I - 1), your second loop must be

For I = 2 To x

instead of For I = 1 To x.

Adapting an Excel VBA Function to Access VBA

1 Answers