各为版大好，以下有一自订函数，目的是要计算某一项目名称在某一天时的存货天数(以
金额加权平均权重)，共计要输入的8个参数举例如下：
1.日期 date_range：42401;42416;42431;42446;42461;42476;42491;42506;42521;42536
2.当日日期 cur_date：42536
3.计画名称 pjt_range：甲;甲;乙;甲;甲;乙;乙;乙;甲;甲
4.编号 num_range：001;002;001;003;004;002;001;002;001;002
5.序号 srl_range：1;1;1;1;1;1;1;1;1;1
6.项目名称 item_range：A;A;B;A;A;B;B;B;A;A
7.金额(负数表出售) amount5;5;7;5;5;7;-7;-7;-5;-5
8.目标项目名称 item_name：A
我的问题是，只要我以下阵列范围<=27程式都没有问题，但只要改为28以上时，在求算
ary6(w)时就会有问题，不知原因为何? 还请版上大大解惑，谢谢!
Function inventory_period(date_range As Range, cur_date As Range, _
pjt_range As Range, num_range As Range, srl_range As Range, _
item_range As Range, amount As Range, item_name As Range) _
As Double
Dim date_rangex, cur_datex, pjt_rangex, num_rangex, srl_rangex, _
item_rangex, amountx(27), str1
Dim ary1(27), ary2(27), ary3(27), ary4(27), ary5(27), ary6(27), ary7(27)
Dim i&, w&
date_rangex = date_range
pjt_rangex = pjt_range
num_rangex = num_range
srl_rangex = srl_range
item_rangex = item_range
'将计画名称、编号、序号、项目名称组合成一字串
'计算库存天数
For i = 0 To UBound(pjt_rangex) - 1
ary1(i) = pjt_rangex(i + 1, 1) & num_rangex(i + 1, 1) & _
srl_rangex(i + 1, 1) & item_rangex(i + 1, 1)
ary2(i) = cur_date - date_rangex(i + 1, 1)
amountx(i) = amount(i + 1, 1)
Next
For i = 0 To 27
If ary1(i) = "" Then ary1(i) = 0
If ary2(i) = "" Then ary2(i) = 0
If amountx(i) = "" Then amountx(i) = 0
Next
'将不重复之组合字串取出
'将不重复组合字串所对应的库存天数取出
'计算不重复组合字串之累计金额
'计算不重复组合字串之累计库存天数
For i = 0 To UBound(pjt_rangex) - 1
If InStr(str1, ary1(i)) = 0 Then
str1 = str1 & "," & ary1(i)
ary3(w) = item_rangex(i + 1, 1)
ary4(w) = ary1(i)
ary5(w) = Evaluate("sum(if({""" & Join(ary1, """,""") & """}=""" _
& ary1(i) & """, {" & Join(amountx, ",") & "}))")
ary6(w) = Evaluate("sum(if({""" & Join(ary1, """,""") & """}=""" _
& ary1(i) & """, {" & Join(ary2, ",") & "}))")
w = w + 1
End If
Next
'计算累计金额与累计库存天数之乘积，所该组合字串已出售(累计金额为0)则乘积为0
For i = 0 To 27
If ary4(i) = "" Then ary4(i) = 0
If ary5(i) = "" Then ary5(i) = 0
If ary6(i) = "" Then ary6(i) = 0
ary7(i) = ary5(i) * ary6(i)
Next
'找出欲求之项目名称在阵列中之位置i
'求算以该项目名称为条件之sumproduct(累计金额,累计库存天数)/sum(累计金额)
i = WorksheetFunction.Match(item_name, ary3, 0) - 1
inventory_period = Evaluate("sum(if({""" & Join(ary3, """,""") & """}=""" _
& ary3(i) & """, {" & Join(ary7, ",") & "}))") _
/ Evaluate("sum(if({""" & Join(ary3, """,""") & """}=""" _
& ary3(i) & """, {" & Join(ary5, ",") & "}))")
End Function