軟體: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