Excel VBA仅用户定义类型错误 [英] Excel VBA Only User-Defined Types error

问题描述

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

I have seen some discussion around this, but I am still puzzled by this error:

只有在公共对象模块中定义的用户定义类型可以是 强制于变体或来自变体，或传递给后期绑定函数

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(请参见下文)):

This is what I have (and it is also possible that the problem is with the failure to assign New LookupItem (see below)):

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

看看我做错了什么?

推荐答案

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

As per my understanding (may be wrong), you cannot add a UDT into a collection (which is an object)

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

Therefore, you need to turn your UDT into a class. Then, instantiate an object and add it to the collection

这篇关于Excel VBA仅用户定义类型错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！