23562

Storing Arrays in Access VBA

Question:

I have coded a subroutine in Access VBA, shown here for reference: <a href="https://stackoverflow.com/questions/34594837/looping-through-dates-in-strsql-in-access-vba/34595227?noredirect=1#comment56947670_34595227" rel="nofollow">Looping Through Dates in strSQL in Access VBA</a>. It is as follows:

Sub SampleReadCurve() Dim rs As Recordset Dim iRow As Long, iField As Long Dim strSQL As String Dim CurveID As Long Dim MarkRunID As Long Dim MaxOfMarkAsofDate As Date Dim userdate As String CurveID = 15 Dim I As Integer Dim x As Date userdate = InputBox("Please Enter the Date (mm/dd/yyyy)") x = userdate For I = 0 To 150 MaxOfMarkAsofDate = x - I strSQL = "SELECT * FROM VolatilityOutput WHERE CurveID=" & CurveID & " AND MaxOfMarkAsofDate=#" & MaxOfMarkAsofDate & "# ORDER BY MaxOfMarkasOfDate, MaturityDate" Set rs = CurrentDb.OpenRecordset(strSQL, Type:=dbOpenDynaset, Options:=dbSeeChanges) If rs.RecordCount <> 0 Then rs.MoveFirst rs.MoveLast Dim BucketTermAmt As Long Dim BucketTermUnit As String Dim BucketDate As Date Dim MarkAsOfDate As Date Dim InterpRate As Double BucketTermAmt = 3 BucketTermUnit = "m" BucketDate = DateAdd(BucketTermUnit, BucketTermAmt, MaxOfMarkAsofDate) InterpRate = CurveInterpolateRecordset(rs, BucketDate) Debug.Print BucketDate, InterpRate End If Next I End Function

Running this subroutine calculates a range of 76 numbers. I want to take these 76 numbers, and use them in the following function as "ZeroCurveInput".

Function EWMA(ZeroCurveInput As Range, Lambda As Double) As Double Dim vZeros() As Variant vZeros = ZeroCurveInput 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 = ZeroCurveInput m = BucketTermAmt 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

I originally coded this function in Excel VBA and I'm trying to port it to Access VBA. In Excel, I'd just read that column of 76 numbers as range and dimension it as variant to store as an array, then use it in the function. However I can't use the range property in Access to do something similar and I'm not sure what to use instead.

How would I store the numbers as an array, and then pass them into the function?

Answer1:

You could also simply write the numbers from SampleReadCurve to a table, and then loop through the table same as you'd loop through your array. So, your sub would change to this (as an aside, does this work? You're creating a Sub and ending a Function...):

Sub SampleReadCurve() Dim rs As Recordset Dim rs2 as Recordset Dim iRow As Long, iField As Long Dim strSQL As String Dim CurveID As Long Dim MarkRunID As Long Dim MaxOfMarkAsofDate As Date Dim userdate As String CurveID = 15 Dim I As Integer Dim x As Date userdate = InputBox("Please Enter the Date (mm/dd/yyyy)") x = userdate For I = 0 To 150 MaxOfMarkAsofDate = x - I strSQL = "SELECT * FROM VolatilityOutput WHERE CurveID=" & CurveID & " AND MaxOfMarkAsofDate=#" & MaxOfMarkAsofDate & "# ORDER BY MaxOfMarkasOfDate, MaturityDate" Set rs = CurrentDb.OpenRecordset(strSQL, Type:=dbOpenDynaset, Options:=dbSeeChanges) Set rs2 = CurrentDb.OpenRecordset("MyNewTable") If rs.RecordCount <> 0 Then rs.MoveFirst rs.MoveLast Dim BucketTermAmt As Long Dim BucketTermUnit As String Dim BucketDate As Date Dim MarkAsOfDate As Date Dim InterpRate As Double BucketTermAmt = 3 BucketTermUnit = "m" BucketDate = DateAdd(BucketTermUnit, BucketTermAmt, MaxOfMarkAsofDate) InterpRate = CurveInterpolateRecordset(rs, BucketDate) Debug.Print BucketDate, InterpRate rs2.AddNew rs2("BucketDate") = BucketDate rs2("InterpRate") = InterpRate rs2.Update End If Next I End Function

Then your EWMA function would look like this:

Function EWMA(Lambda As Double) As Double Dim Price1 As Double, Price2 As Double 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 = BucketTermAmt Set rec = CurrentDB.OpenRecordset("SELECT InterpRate FROM MyNewTable") I = 2 Do While rec.EOF = False Price1 = Exp(-rec("InterpRate")(I - 1, 1) * (m / 12)) Price2 = Exp(-rec("InterpRate")(I, 1) * (m / 12)) LogRtn = Log(Price1 / Price2) RtnSQ = LogRtn ^ 2 WT = (1 - Lambda) * Lambda ^ (I - 2) WtdRtn = WT * RtnSQ SumWtdRtn = SumWtdRtn + WtdRtn I = I + 1 Loop EWMA = SumWtdRtn ^ (1 / 2) End Function

Answer2:

There are a few different ways you could do this, including using an array as you suggest. If all you need are the 76 numbers, you could do this using a collection. The advantage to the collection is you don't need to know ahead of time how many items will be in it.

Here's a simple working example of using a collection:

<pre class="lang-vb prettyprint-override">Sub TestColl() Dim TestCollection As Collection Set TestCollection = CreateColl() LoopThruColl TestCollection End Sub Function CreateColl() As Collection Dim MyColl As Collection Set MyColl = New Collection Dim i As Integer For i = 1 To 5 MyColl.Add i * 2 Next i Set CreateColl = MyColl End Function Sub LoopThruColl(CollToLoop As Collection) Dim i As Integer For i = 2 To CollToLoop.Count Debug.Print i, CollToLoop.Item(i - 1), CollToLoop.Item(i) Next i End Sub

Recommend

  • VBScript - Return a Recordset in an Array (SQL Like function)
  • mailmerge with visto how to get each record of MailMerge.DataSource.DataFields
  • delete record and move previous not working
  • compare two record sets in vb6
  • Maximo scripting cannot load second MboSet
  • Recordset Edits and Updates the Wrong Record
  • how can i get an array of recently inserted records ids?
  • SQLDependency — Invalid SQL
  • MS Access 2003 - Is there a way to programmatically define the data for a chart?
  • Arguments not optional in vb6
  • c# combination process time too slow and out of memory exception
  • Unable to decode certificate at client new X509Certificate2()
  • OOP Javascript - Is “get property” method necessary?
  • Date Conversion from yyyy-mm-dd to dd-mm-yyyy
  • Passing 1 of 3 values to a Sub
  • How to test if a URL from an Eclipse bundle is a directory?
  • ActiveRecord query for a count of new users by day
  • Set the selected item in dropdownlist in MVC3
  • How to create a file in java without a extension
  • Spring Data JPA custom method causing PropertyReferenceException
  • Splitting given String into two variables - php
  • What is Eclipse's Declaration View used for?
  • Counter field in MS Access, how to generate?
  • Possible to stop flickering java tooltip in heavyweight mode?
  • sending/ receiving email in Java
  • How to set my toolbar fixed while scrolling android
  • AT Commands to Send SMS not working in Windows 8.1
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • Trying to get generic when generic is not available
  • embed rChart in Markdown
  • How to get Windows thread pool to call class member function?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • apache spark aggregate function using min value
  • Sorting a 2D array using the second column C++
  • costura.fody for a dll that references another dll
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?
  • java string with new operator and a literal