Assigning an object to a collection: error '91': Object variable or With block variable not set

Question

I'm setting up a macro to open different websites based on what deal ID cells are not empty in an Excel sheet. It will create four objects and store the objects in a collection. Four is an arbitrary number because I won't ever need more than two, but I created more just in case.

The macro loops through the Excel sheet and picks out as many deal IDs as necessary (deal IDs are appended to the url to go to the different sites).

I get the error saying

Object variable or With block variable not set

on the highlighted line below.

Sub TransactionMatching()
    Dim first_day As String

    Dim ieapp As Object
    Dim ieapp2 As Object
    Dim ieapp3 As Object
    Dim ieapp4 As Object


    ' collection to hold deal names
    Dim dealnameArray As New Collection
    ' collection to hold deal IDs
    Dim dealIDArray As New Collection
    ' collection to hold required ieapp objects
    Dim totalDealObjectArray As New Collection

    ' add all ieapp objects to the collection
    totalDealObjectArray.Add ieapp
    totalDealObjectArray.Add ieapp2
    totalDealObjectArray.Add ieapp3
    totalDealObjectArray.Add ieapp4

    Windows("transaction_matching.xlsm").Activate

    ' loop through each row in the excel sheet and add the deal names and deal IDs...
    ' ...with check marks nect to them to their respective collections
    For i = 5 To 51
        If IsEmpty(Range("C" & i).Value) = False Then
            dealnameArray.Add (Range("A" & i).Value)
            dealIDArray.Add (Range("B" & i).Value)
        End If
    Next

    'get the required number of objects from the ieapp object collection
    For i = 1 To dealnameArray.Count - 1
        ' set each object in ieapp object collection to a new internet explorer object
        Set totalDealObjectArray(i) = New InternetExplorerMedium
        totalDealObjectArray(i).Visible = True

        ' define the last business day
        lastDay = DateSerial(Year(Date), Month(Date), 0)

        ' define the first day of the previous month
        first_day = lastDay - Day(lastDay) + 1

        With totalDealObjectArray(i)
            .navigate "http://website" & dealIDArray(i)
            Application.DisplayFullScreen = True
            Call busy((totalDealObjectArray(i)))
            Call DoThings((totalDealObjectArray(i)))
        End With
    Next

    Application.WindowState = xlNormal
    Application.WindowState = xlMaximized

End Sub

user_5448626 · Answer

Collections in VBA use .Add and .Remove to add and remove items. Changing values of items in collection is done through additional code - How to change value of an item of a collection

The collection.Item(N) displays the value, but does not change it. Concerning the code, you can add the new object and thus it would be set:

Sub TransactionMatching()

    Dim i As Long
    Dim totalDealObject As New Collection

    totalDealObject.Add New InternetExplorerMedium
    totalDealObject.Add New InternetExplorerMedium
    totalDealObject.Add New InternetExplorerMedium
    totalDealObject.Add New InternetExplorerMedium

    For i = 1 To 4
        Debug.Print totalDealObject.Item(i).FullName
    Next

End Sub

If the task is to add the items in a collection through a loop, then something like this can work, adding InternetExplorerMedium on every second position of the collection:

Sub TransactionMatching()

    Dim i As Long
    Dim totalDealObject As New Collection

    For i = 1 To 10
        If i Mod 2 = 0 Then
            totalDealObject.Add New InternetExplorerMedium
        Else
            totalDealObject.Add i
        End If
    Next

End Sub

user_1188513 · Answer

Vityata correctly identified the problem:

The collection.Item(N) displays the value, but does not change it

In other words, the = assignment operator is NOT going against the null object reference at the specified collection index; the left-hand-side assignment target is the default member of the retrieved object, and since the retrieved object is Nothing, that implicit default member call is why you're getting error 91, because any member call (explicit or not) against Nothing will always throw error 91.

Now, if the reference was set, it would still be an implicit default member call to resolve the LHS of the assignment operation; if the object doesn't have a default member, the run-time error would be 438 "object does not support property or method".

Default Member?

Many classes have a default member. That is, a member that can be omitted. One example would be Collection.Item - these two statements do exactly the same thing:

Debug.Print myCollection.Item(i)
Debug.Print myCollection(i) '<~ call to .Item default member is implicit

Rubberduck has several code inspections that could have found and prevented this bug... and others waiting to be caught:

Assigning an object to a collection: error '91': Object variable or With block variable not set

2 Answers