我有一个公共(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/