Only public user defined types defined in public object modules can be used as parameters in VBA

Question

I have a public UDT, and wanted to use it as parameter in a Public Sub in a normal Module. I then get a compile error:

Only public user defined types defined in public object modules can be used as parameters or return type for public procedures of class modules or as fields of public user defined types.

I don't know really understand it, the UDT and sub are public.

Here is the UDT I defined.

Public Type perf
    retailer As String
    sale As Integer
    cateDiscrip As String
    prodCode As String
    forecast As Integer
    score As Double
End Type

Basically, I wanted to store a table(with retailer,category description, product code etc.) into an array and then sorted them by retailer, for the same retailer, sorted by category. I copied them from another sheet and then pasted them into the current workbook "data" sheet. Then, I defined a public UDT and stored them in an array.

Public Sub getlist()

    Dim highvol() As perf
    Dim lowvol() As perf
    Dim oneArr() As perf
    Dim i As Integer
    Dim s As Integer

    Set ws = Application.Worksheets("data")

    'find the number of retailers, redimension the array, and fill them with
    'the data in the lists

    With ws.Range("A2")
        nRetailer = ws.Range(.Offset(1, 0), .End(xlDown)).Rows.Count
        ReDim highvol(nRetailer)
    End With

    For isale = 2 To nRetailer
          If ws.Range("M1").Cells(isale) >= 10 Then
              n = n + 1
          Else
              m = m + 1
          End If
     Next

    ReDim highvol(n)
    ReDim lowvol(m)
    ReDim oneArr(nRetailer)

    nsale = 0
    msale = 0

''isale is the current row, nsale is the size of highvol sales. 
    For isale = 2 To nRetailer
        If ws.Range("M1").Cells(isale) >= 10 Then
            nsale = nsale + 1
            highvol(nsale).sale = ws.Cells(isale, 13)
            highvol(nsale).forecast = Str(ws.Range("N1").Cells(isale))
            highvol(nsale).retailer = ws.Range("A1").Cells(isale)
            highvol(nsale).cateDiscrip = ws.Range("B1").Cells(isale)
            highvol(nsale).prodCode = ws.Range("C1").Cells(isale)
            highvol(nsale).score = Str((1 - AbsPerErr(ws.Range("M1").Cells(isale), ws.Range("N1").Cells(isale))) * 100)
        Else
            msale = msale + 1
            lowvol(msale).sale = Str(ws.Range("M1").Cells(isale))
            lowvol(msale).forecast = Str(ws.Range("N1").Cells(isale))
            lowvol(msale).retailer = ws.Range("A1").Cells(isale)
            lowvol(msale).cateDiscrip = ws.Range("B1").Cells(isale)
            lowvol(msale).prodCode = ws.Range("C1").Cells(isale)
            lowvol(msale).score = Str((1 - AbsPerErr(ws.Range("M1").Cells(isale), ws.Range("N1").Cells(isale))) * 100)
        End If
    Next

After that, I had two functions for filter and compare, passed the data into one array.

    For i = 1 To nsale
        oneArr(i) = highvol(i)
    Next

    For s = 1 To msale
        oneArr(nsale + s) = lowvol(s)
    Next

    Dim result1() As perf
    Dim result2() As perf

    filter oneArr, "AED", 1, result1
    filter result1, "RhinoBulk1", 2, result2

End Sub

This is where I get the error filter oneArr. Can anyone explain what's going wrong and how to fix it?

Use a class instead of a UDT for this. (you only need to add your UDT members as public members of the class for the simplest implementation) — Rory
@Rory thanks for that, but I'm new and never used a class before. Do I add a new class module and then paste the code into that class? — user3804820
@user3804820 See cpearson.com/Excel/Classes.aspx for some basic information on using Classes — Ron Rosenfeld

Rory Rory · Accepted Answer · 2014-07-29T10:46:30

Add a new class module, and rename it perf rather than Class1. Paste in this code:

Public retailer As String
Public sale As Integer
Public cateDiscrip As String
Public prodCode As String
Public forecast As Integer
Public score As Double

You then need to alter the loop code to create new instances of the class for each element of the array:

For isale = 2 To nRetailer
    If ws.Range("M1").Cells(isale) >= 10 Then
        nsale = nsale + 1
        Set highvol(nsale) = New perf
        highvol(nsale).sale = ws.Cells(isale, 13)
        highvol(nsale).forecast = Str(ws.Range("N1").Cells(isale))
        highvol(nsale).retailer = ws.Range("A1").Cells(isale)
        highvol(nsale).cateDiscrip = ws.Range("B1").Cells(isale)
        highvol(nsale).prodCode = ws.Range("C1").Cells(isale)
        highvol(nsale).score = Str((1 - AbsPerErr(ws.Range("M1").Cells(isale), ws.Range("N1").Cells(isale))) * 100)
    Else
        msale = msale + 1
        Set lowvol(msale) = new perf
        lowvol(msale).sale = Str(ws.Range("M1").Cells(isale))
        lowvol(msale).forecast = Str(ws.Range("N1").Cells(isale))
        lowvol(msale).retailer = ws.Range("A1").Cells(isale)
        lowvol(msale).cateDiscrip = ws.Range("B1").Cells(isale)
        lowvol(msale).prodCode = ws.Range("C1").Cells(isale)
        lowvol(msale).score = Str((1 - AbsPerErr(ws.Range("M1").Cells(isale), ws.Range("N1").Cells(isale))) * 100)
    End If
Next

Only public user defined types defined in public object modules can be used as parameters in VBA

1 Answers