只有在公共对象模块中定义的公共用户定义类型才能作为VBA中的参数使用。

Question

只有在公共对象模块中定义的公共用户定义类型才能作为VBA中的参数使用。

vbaexceluser-defined-types

8

我有一个公共UDT，想要在普通模块中的公共子程序中使用它作为参数。然而，我遇到了编译错误：

只有在公共对象模块中定义的公共用户定义类型才能用作类模块的公共过程的参数或返回类型，或作为公共用户定义类型的字段。

我不太理解，这个UDT和子程序都是公共的。

以下是我定义的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，并将它们存储在一个数组中。

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

接下来，我编写了两个函数用于过滤和比较数据，将数据传入一个数组。

    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。有人能解释一下出了什么问题以及如何解决吗？

- user3804820

使用类而不是UDT来实现此功能。（您只需要将UDT成员作为类的公共成员添加即可实现最简单的实现） - Rory

@Rory 谢谢你的回复，但我是新手，以前从未使用过类。我需要添加一个新的类模块，然后将代码粘贴到该类中吗？ - user3804820

请参考 http://www.cpearson.com/Excel/Classes.aspx 了解有关使用类的基本信息。 - Ron Rosenfeld

@RonRosenfeld 谢谢，非常有帮助。 - user3804820

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Rory · Accepted Answer

新增一个类模块，并将其重命名为perf，而不是Class1。粘贴下面的代码：

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

你需要修改循环代码，为数组的每个元素创建该类的新实例：

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