Excel VBA 仅用户定义类型错误

Question

我已经看到了一些关于此问题的讨论，但我仍然对这个错误感到困惑:

Only User-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions

这就是我所拥有的(问题也可能是由于未能分配New LookupItem(见下文)):

Public Type LookupItem
    Stock As String
    Price As String
End Type

Sub GetData()

    Dim PreviousPrices As Collection

    Dim MyStock As String
    Dim CurrentPrice As String
    Dim ALookupItem As LookupItem

    Set PreviousPrices = New Collection

    ' Assumption:  the first line of good data is #4
    MyRow = 4

    Dim Item As Variant
    ' Assumption:  Column 2 is the Stock Symbol; there are no blank lines until the end
    Do Until Trim$(Cells(MyRow, 2).Value) = ""
        ' Assumption:  Column 8 is the Sell Date, blank if not yet Sold
        If (Cells(MyRow, 8).Value = "") Then
            MyStock = Cells(MyRow, 2).Value
            CurrentPrice = ""
            For Each Item In PreviousPrices
                If Item.Stock = MyStock Then
                    CurrentPrice = Item.Price
                    Exit For
                End If
            Next Item

            If CurrentPrice = "" Then 'Go get it and put it in CurrentPrice
                ...
                ' Set ALookupItem = New LookupItem (if not commented, this returns invalid use of New keyword)
                ALookupItem.Stock = MyStock
                ALookupItem.Price = CurrentPrice
                PreviousPrices.Add ALookupItem
            End If

        End If
        MyRow = MyRow + 1
    Loop
End Sub

看看我做错了什么吗？

Answer 1

根据我的理解(可能是错误的)，您不能将 UDT 添加到集合(这是一个对象)中

因此，你需要将你的UDT变成一个类。然后，实例化一个对象并将其添加到集合中