Passing User Defined Type Array to Listbox

[PC]

Trying to update some VBA I've developed to change a two dimensional array
to a custom data type as I've found it to be easier to work with the entire
array.

However, I ran into a problem when I tried to pass the array to a Listbox in
a userform.

With the array (Item) defined as a 2-d array the code

UserForm1.ListBox1.List = Item

works just fine and the array appears in the Userform.

However if I dim the "Item" array as a custom type I get the following error

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

Is there something I can do to make this work?

TIA

PC

 

[Tom Ogilvy]

Probably not.

But you don't show how your array is defined.

 

[PC]

Tom

Thanks for looking at this.

Here's what I have now (without the custom data type)

Public Sub Find()

'Search Macro to locate items in a list and return the result array to a
listbox.

Application.ScreenUpdating = False

Dim Ctr As Integer
Dim TestVal As String
Dim Item() As String

On Error GoTo Canceled 'Never been sure if this is the best way to handle
an error

UserForm4.Show

'compval is a public variable

If compval = "" Then
GoTo Canceled
End If

Worksheets("List").Activate
numrows = Range("list").Rows.Count 'list is a dynamic named range

Ctr = 0
For i = 1 To numrows 'all values are converted to lower case to
eliminate issues with case
TestVal = Evaluate("LOWER(OFFSET(A3," & i & ",0))")
If TestVal Like "*" & LCase(compval) & "*" Then
Ctr = Ctr + 1
End If
Next i

If Ctr = 0 Then
MsgBox "No Matching Products Found", vbOKOnly, "SELECTION"
GoTo Canceled

Else 'add items to the array
ReDim Item(1 To Ctr, 1 To 3) 'redim the item list based on the
number of items (I'd love to do this without counting the items first)
For i = 1 To numrows
TestVal = Evaluate("LOWER(OFFSET(A3," & i & ",0))")
If TestVal Like "*" & LCase(compval) & "*" Then
CtrA = CtrA + 1
Item(CtrA, 1) = Range("A3").Offset(i, 0)
Item(CtrA, 2) = Range("A3").Offset(i, 1)
Item(CtrA, 3) = Format(Range("A3").Offset(i, 2), "$#.00")
End If
Next i

End If


UserForm1.ListBox1.List = Item
UserForm1.Show

Worksheets("Form").Activate

' Find the next available row and then do stuff with the selection

Canceled:
Application.ScreenUpdating = True
End Sub




When I tried it with the custom type I used the following type declaration
at the beginning of the Module after the public variable declarations but
before any proceedures (I adjusted the references in the section where the
items are added to the array from "Item(Ctra, 1)" to "Item(Ctra).Name ...
and that did not error).

Type Listitem
Name as string
Description as string
Value as currency
End Type

Thanks for your always helpful input.

PC