VBA中只能将公共对象模块中定义的公共用户定义类型用作参数 [英] Only public user defined types defined in public object modules can be used as parameters in VBA

问题描述

我有一个公共UDT，并且想将其用作普通模块中Public Sub中的参数.然后，我得到一个编译错误:

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.

我不知道是不是真的，UDT和sub是公开的.

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

这是我定义的UDT.

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

基本上，我想将一个表(包含零售商，类别描述，产品代码等)存储到数组中，然后按零售商对它们进行分类，对于同一零售商，按类别进行分类. 我从另一张纸复制了它们，然后将它们粘贴到当前工作簿的数据"纸中. 然后，我定义了一个公共UDT，并将它们存储在一个数组中.

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

这是我收到错误 filter oneArr 的地方.谁能解释发生了什么问题以及如何解决?

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

推荐答案

添加一个新的类模块，并将其重命名为perf而不是Class1.粘贴此代码:

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

这篇关于VBA中只能将公共对象模块中定义的公共用户定义类型用作参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！