Using Collections in Access VBA

Question

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?

Use the help on collections, but I'd use a scripting dictionary with the key as the date and the value as the rate. Google scripting.dictionary vba — Nathan_Sav
– Nathan_Sav, Commented Jan 13, 2016 at 19:53
You can easily use Collections. Two approaches: Store each date/rate pair in a variant array, and add the array to the collection; or create a user defined object (Class), and add the object to the Collection. I find the latter easier to debug and maintain, but you can start with the array approach. — Ron Rosenfeld
– Ron Rosenfeld, Commented Jan 13, 2016 at 20:37
Thanks for replying. For the array approach, I can create a new variable and dimension it as Variant and set it equal to the date/rate pair, then add it to the collection. I can put this within the loop so that it does it for each I. Is this what you mean or am I misunderstanding? — beeba
– beeba, Commented Jan 13, 2016 at 20:53

NLMDEJ · Accepted Answer · 2016-06-23 13:40:12Z

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

Collectives™ on Stack Overflow

Using Collections in Access VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related