VBA Array

[Excel_Kid1081]

Hello-

I've created this code below that loops through a file and perform some caclulations. However, I am having trouble with the output. The output table will have the same number of columns and rows however I cannot seem to be able to get past the first column in my array before I get a run-time error 9 telling me my subscripts are out of the range. Basically, I am looking to have the first column to contain text (the names of the columns) and first row to be the same. The corresponding data will fill in appropriately.

Example of Output
Index 1 Index 2
Index 1 0.52 0.64
Index 2 0.64 0.33

I’ve played around with the code and I still can’t seem to get it to work. Any pointers are much appreciated.

Thanks!

EK

Here is the code

Sub CovarianceMatrixLoader()
Dim rs As ADODB.Recordset
Dim IndexNames() As String
Dim arrOutputTable() As Double
Dim RetOne, AvgOne, RetTwo, AvgTwo, CoVar As Double
Dim TotalRows As Double
Dim icount, kcount As Long
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
'Find the total count of number of indices for the matrix
rs.Open "SELECT tblIndices.Index FROM tblIndices;", , adOpenForwardOnly, adLockReadOnly
icount = 0
ReDim IndexNames(0)
Do While Not rs.EOF
    ReDim Preserve IndexNames(icount)
    IndexNames(icount) = rs.Fields("Index")
    icount = icount + 1
    rs.MoveNext
Loop
rs.Close
'Find the Average for the first index in the group as well as the total count of observations
For icount = 0 To UBound(IndexNames)
    ReDim arrOutputTable(0 To UBound(IndexNames), 0 To UBound(IndexNames))
 
    rs.Open "SELECT Avg(" & IndexNames(icount) & ") AS AvgFirst, Count(*) As TotalRows FROM tblMonthlyTotalReturns;", , adOpenForwardOnly, adLockReadOnly
    AvgOne = rs.Fields("AvgFirst")
    TotalRows = rs.Fields("TotalRows")
    rs.Close
'Find the Average for the second index in the group
        For kcount = 0 To UBound(IndexNames)
 
            rs.Open "SELECT Avg(" & IndexNames(kcount) & ") AS AvgSecond FROM tblMonthlyTotalReturns;", , adOpenForwardOnly, adLockReadOnly
            AvgTwo = rs.Fields("AvgSecond")
            rs.Close
 
'Pull the monthly individual returns for each index
                rs.Open "SELECT " & IndexNames(icount) & ", " & IndexNames(kcount) & " FROM tblMonthlyTotalReturns;", , adOpenForwardOnly, adLockReadOnly
 
                CoVar = 0
'Loop through the monthly returns and calculate the variance and covariance of returns
                Do While Not rs.EOF
                RetOne = rs.Fields(IndexNames(icount))
                RetTwo = rs.Fields(IndexNames(kcount))
                CoVar = CoVar + (RetOne - AvgOne) * (RetTwo - AvgTwo)
                rs.MoveNext
                Loop
                    CoVar = CoVar / TotalRows
                    arrOutputTable(kcount, icount + 1) = CoVar
                    rs.Close
        Next kcount
'Paste CoVar and the corresponding index into Correlation Matrix Table
'The corresponding index only needs to be added on the first run
            If icount < 1 Then
                rs.Open "CorrelationMatrix", , adOpenDynamic, adLockOptimistic
                    rs.AddNew
                    rs.Fields("Index") = IndexNames(kcount)
                    rs.Fields(IndexNames(icount)) = arrOutputTable(icount, kcount)
                    rs.Update
                    rs.Close
            Else
                    rs.Open "CorrelationMatrix", , adOpenDynamic, adLockOptimistic
                    rs.Fields(IndexNames(icount)) = arrOutputTable(icount, kcount)
                    rs.Update
                    rs.Close
            End If
 
 
 
Next icount
'rs.Close
End Sub

 

[gemma-the-husky]

are you trying to get 3 values in the array, or just two

try defining the array as a type, then you can just have single dimension array

{you have to put the type declaration in a code module, i think}
type tmystruc
fieldname as string
value1 as long
value2 as long
end type


then in your form or code module

dim myarray() as tmystruc


then you get

myarray(1).fieldname or
myarray(2).values1

etc etc

types are good, because you get easier array indexing, and intellisenses on the field names