软件:excel vba
版本:2010
各位大大好，以下我有一个function，item_range为储存格(如A1:A10)，内容为文字串，
如"A", "B", "A", "B", "B", "C", "B", "C", "D", "B"，而number_range亦为储存格
(如B1:B10)，内容为数字，如1, 2, 3, 4, 5, 6, 7, 8, 9, 10。
今天想要计算各item_range的项目(以取唯一值)之累加金额，并回传金额第k大(rank_
order)之项目名称，我之前以虚拟阵列在Sub中执行都没有问题，但是改以Function就
执行不出结果，想要请问一下我哪里做错了?(变量定义?) 谢谢!
Function inventory_rank(item_range() As Variant, number_range() As Variant,
rank_order As Integer) As String
Dim c, d, str1, str2
Dim ary1$(), ary2(100), ary3(100)
'求算资料笔数(阵列上限+1)
c = UBound(item_range)
'若某字串a(i)在组合字串(str)中没有出现才会被加入组合字串(str)中
For i = 0 To c
If InStr(str1, item_range(i)) = 0 Then
str1 = str1 & "," & item_range(i)
End If
Next
'将第2位开始之字串转换为阵列
ary1 = Split(Mid(str1, 2), ",")
'求算不重复项目之个数(阵列上限+1)
d = UBound(ary1)
'求算不重复项目之累加金额
For i = 0 To d
ary2(i) = ary1(i)
ary3(i) = Evaluate("sum(if({""" & Join(item_range, """,""") & """}="""
& ary1(i) & """, {" & Join(number_range, ",") & "}))")
Next
'求算第k位金额之项目名称
inventory_rank = Application.WorksheetFunction.Index(ary2, Application.
WorksheetFunction.Match(Application.WorksheetFunction.
Large(ary3, rank_order), ary3, 0))
End Function