CODEWITHSUNDEEP
excelvbams-access
beeba
beeba

Reputation: 432

Using Collections in Access VBA

I have this following subroutine In Access VBA:

   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

This subroutine put outs a 2x2 list of 76 numbers, consisting of a date and an associated rate. I want to store this list of values as a Collection, so that I can use it as an input in another function. Is it possible to use collections to do this? What would be the appropriate syntax?

Upvotes: 0

Views: 713

Answers (1)

NLMDEJ
NLMDEJ

Reputation: 395

I agree with comments that a data dictionary is probably the way to go. The reason is that with a dictionary you can actually loop through the keys if needed. You will need to add a reference to the Microsoft Scripting Runtime. Here is a brief example:

Public Function a()

    Dim dRates As New Scripting.Dictionary
    Dim key As Variant

    dRates.Add #1/1/2016#, 1
    dRates.Add #2/1/2016#, 1.5
    dRates.Add #3/1/2016#, 2

    'You can either access the rate directly with the key:
    Debug.Print dRates(#2/1/2016#)

    'Or you can loop through the keys/values
    For Each key in dRates.Keys
        Debug.Print key & " - " & dRates(key)
    Next

    'Or, you can pass the entire collection to a function
     Call b(dRates)

End Function

Public Function b(d As Scripting.Dictionary)

    For Each key In d.Keys
        Debug.Print key & " - " & d(key)
    Next

End Function

This will provide the following output:

1.5
1/1/2016 - 1
2/1/2016 - 1.5
3/1/2016 - 2
1/1/2016 - 1
2/1/2016 - 1.5
3/1/2016 - 2

Upvotes: 1

Related Questions