Declaring Variables

[steve_4477]

When all else fails there is always Access World....

So, I have a ListBox that returns a different number of instances depending on which "account" I am working on. Example:

Tenor Spread COF
5 1.58% 2.15%
7 2.81% 2.74%
10 2.57% 3.33%

It could be anywhere from 1 to 15 lines. I need a function that sets columns 1 and 2 for each instance as a variable i.e.

x1 = 5, 1.58%
x2 = 7, 2.81%
x3 = 10, 2.57%
and so on....

I have the code to set each variable down (Me.PricingList.Column(0, 0) & " yrs, " & Format(Me.PricingList.Column(1, 0), "#.00%")


Is there a way I can use a dynamic array to specify how many different variables I will need based on the list box? Ulimately I will be using these variables as text in the body of an email generated via vba.

Does this make sense and any ideas?

 

[steve_4477]

An easier way of asking might be to say:

How can I take what is displayed in a list box and insert it into the body of an email?

 

[VilaRestal]

With Me.PricingList
    Dim varPricingList() As Variant
    ReDim varPricingList(.ColumnCount - 1, .ListCount - 1)
    Dim i, j As Integer
    For i = 0 To .ColumnCount - 1
        For j = 0 To .ListCount - 1
            varPricingList(i, j) = .Column(i, j)
        Next j
    Next i
End With

However, you are duplicating what the list has already. Unless you intend to clear the list items or close the form that contains the listbox and want vba to remember its list then there's little point replacing lines of code:
x = Me.PricingList.Column(i, j)
with
x = varPricingList(i, j)