title: Storing Arrays in Access VBA
body: <p>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">Looping Through Dates in strSQL in Access VBA</a>. It is as follows:</p>
  
  <pre><code>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=" &amp; CurveID &amp; " AND MaxOfMarkAsofDate=#" &amp; MaxOfMarkAsofDate &amp; "# ORDER BY MaxOfMarkasOfDate, MaturityDate"
  
  Set rs = CurrentDb.OpenRecordset(strSQL, Type:=dbOpenDynaset, Options:=dbSeeChanges)
  If rs.RecordCount &lt;&gt; 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
  </code></pre>
  
  <p>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". </p>
  
  <pre><code>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
  </code></pre>
  
  <p>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. </p>
  
  <p>How would I store the numbers as an array, and then pass them into the function? </p>