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



我不知道真的了解它,UDT和sub是公开的。

这是我定义的UDT。

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

基本上,我想将一个表(包含零售商,类别描述,产品代码等)存储到数组中,然后按零售商对它们进行排序,对于同一零售商,则按类别进行排序。
我从另一张纸复制了它们,然后将它们粘贴到当前工作簿的“数据”纸中。
然后,我定义了一个公共(public)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

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

最佳答案

添加一个新的类模块,并将其重命名为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

关于vba - VBA中只能将公共(public)对象模块中定义的公共(public)用户定义类型用作参数,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/25013024/

10-11 09:26
查看更多