excel - 将对象分配给集合: error '91' : Object variable or With block variable not set

Question

我正在设置一个宏，以根据 Excel 工作表中的交易 ID 单元格不为空来打开不同的网站。它将创建四个对象并将这些对象存储在一个集合中。四是任意数字，因为我不需要超过两个，但为了以防万一，我创建了更多。

该宏循环遍历 Excel 工作表，并根据需要挑选出尽可能多的交易 ID(交易 ID 附加到 URL 中以转到不同的网站)。

我收到错误消息

Object variable or With block variable not set

在下面突出显示的行上。

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

Answer 1

VBA 中的集合使用 .Add 和 .Remove 来添加和删除项目。更改集合中项目的值是通过附加代码完成的 - How to change value of an item of a collection

collection.Item(N) 显示值，但不更改它。关于代码，您可以添加新对象，从而将其设置为:

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

如果任务是通过循环添加集合中的项目，则可以使用类似的方法，在集合的每隔两个位置添加 InternetExplorerMedium:

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