Excel VBA Only User-Defined Types error

Question

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

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

See what I've done wrong?

sam092 · Accepted Answer · 2014-01-03 03:13:26Z

3

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

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

answered Jan 3, 2014 at 3:13

sam092

1,3251 gold badge8 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

stenci · Accepted Answer · 2014-01-03 03:03:37Z

0

I think that VBA collections can only store native variables or objects.

I always create classes with the required members (and later I'm happy about it because I end up for adding a few methods as well).

edited Jan 3, 2014 at 3:03

answered Jan 3, 2014 at 2:53

stenci

8,55914 gold badges69 silver badges113 bronze badges

Collectives™ on Stack Overflow

Excel VBA Only User-Defined Types error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related