CODEWITHSUNDEEP
ms-accessvba
Jason247
Jason247

Reputation: 1024

MS Access VBA: UBound & LBound functions returning subscript out of range error on class array

I am receiving a "Subscript out of range" error when calling the LBound() and UBound() functions on a global object's array in MS Access 2003 VBA. I don't understand why this is occurring because I am certain the array has been initialized since I can access values from the array by referencing a known index of the array.

Here is my class that has a string array as a property. I am initializing the array the Class_Initialize method.

Private pIgnoreDifferencesInDatabaseComparisonForFields() As String

Public Property Get ignoreDifferencesInDatabaseComparisonForFields() As String()
    ignoreDifferncesInDatabaseComparisonForFields = pIgnoreDifferencesInDatabaseComparisonForFields
End Property

Public Property Get ignoreDifferencesInDatabaseComparisonForField(index As Long) As String
    ignoreDifferencesInDatabaseComparisonForField = pIgnoreDifferencesInDatabaseComparisonForFields(index)
End Property

Public Property Let ignoreDifferencesInDatabaseComparisonForField(index As Long, fld As String)
    If index > UBound(pIgnoreDifferencesInDatabaseComparisonForFields) Then ReDim Preserve pIgnoreDifferencesInDatabaseComparisonForFields(index)
    pIgnoreDifferencesInDatabaseComparisonForFields(index) = fld
End Property

Private Sub Class_Initialize()

    Dim ignoreDiffInDBComparisonForFields() As String
    Dim i As Long

    ' Add any new columns to skip inside this string
    ignoreDiffInDBComparisonForFields = Split("EligOvr,UpdateTS,groupStartDate,groupEndDate", ",")

    ReDim pIgnoreDifferencesInDatabaseComparisonForFields(0)

    For i = LBound(ignoreDiffInDBComparisonForFields) To UBound(ignoreDiffInDBComparisonForFields)
        Me.ignoreDifferencesInDatabaseComparisonForField(i) = ignoreDiffInDBComparisonForFields(i)
    Next i

End Sub

I am declaring the object as a global variable at the top of my main module outside of any functions or subs.

Public settings As Options

Then, in my main function that I am calling, I have the following line of code.

Set settings = New Options

Later on in my code, this function is called.

Function isAnIgnoreDifferencesInDatabaseComparisonField(field As String) As Boolean

    Dim found As Boolean
    Dim i As Long

    found = False

    x = settings.ignoreDifferencesInDatabaseComparisonForField(1)

    For i = LBound(settings.ignoreDifferencesInDatabaseComparisonForFields) To UBound(settings.ignoreDifferencesInDatabaseComparisonForFields)
        If (LCase(field) = LCase(settings.ignoreDifferencesInDatabaseComparisonForField(i))) Then
            found = True
        End If
    Next i

    isAnIgnoreDifferencesInDatabaseComparisonField = found

End Function

I get the error message in the for loop where I call the UBound and LBound functions the first time it hits the "For i ..." line. It does not make it through any iterations of the loop. I set x to the value at location 1 in the array as a test, and the value does populate without errors, so the array has been initialized. Does anyone know why I am getting the "Subscript out of Range" error? Thanks!

Upvotes: 1

Views: 1214

Answers (2)

kismert
kismert

Reputation: 1692

Your problem is in the line:

Public Property Get ignoreDifferencesInDatabaseComparisonForFields() As String()
    ignoreDifferencesInDatabaseComparisonForFields = pIgnoreDifferencesInDatabaseComparisonForFields
    ' ^ correct typo here ^
End Property

When I fixed this, your code compiled, and it ran properly without the Subscript out of range error.

Upvotes: 2

Holmes IV
Holmes IV

Reputation: 1739

I can't say I have used VBA to much in Access, but I know its can do some interesting things in Excel. I would try setting the the upper bound to Ubound() -1 and see what happens. Sometimes VBA uses the standard convention of Starting at 0-9 other times its Starts at 1 depending on the object type.

Also as a side in the final for loop, you might want to consider adding an Exit For, if the match is found. No reason to complete the loop if its True.

Upvotes: 0

Related Questions