Excel VBA

css navigation by Css3Menu.com

Build Multi-Dimension Array

I'm building an Array that is the number of records returned by 4 columns (0 thru 3):
ReDim LookUpArray(Lookers.RecordCount - 1, 3)
. The GetSQL instruction goes to a short procedure that opens the connection, runs the query, and returns control to this macro.
Sub MakeLookupArray()
    Dim Lookers As New Recordset
    Dim GetLookups As String
    GetInfo = "SELECT * FROM TheTable"
    GetInfo = GetInfo & " WHERE (ReportCode=" & Report & ")"
    Set Lookers = getSql(GetInfo)
    If Not Lookers.Fields.Count > 0 Then GoTo ProcessFailure
    If Not Lookers.RecordCount > 0 Then GoTo ProcessFailure
    ReDim LookUpArray(Lookers.RecordCount - 1, 3)

    With Lookers
        .MoveFirst
        Dim A As Integer
        A = 0
        While Not .EOF
            LookUpArray(A, 0) = Lookers.Fields("ID")
            LookUpArray(A, 1) = Lookers.Fields("Classification")
            LookUpArray(A, 2) = Report
            LookUpArray(A, 3) = Date
            
        A = A + 1
        .MoveNext
    Wend
        .Close
        GetLookups = True
    End With
CloseAndExit:
    Set Lookers = Nothing
    Exit Sub
    
ProcessFailure:
    MsgBox "Selection of Get Data failed; Contact Support.", vbCritical
End Sub

In the end, you have all the data you need to extract data, etc from the array.

© 2006-2017

Updated:  09/30/2017 19:48
This page added:  24 July 2006