|
最近写了个递归程序,用于计算期望次数,但是只要数字稍微大一些,EXCEL就未响应了,非常尴尬.
代码如下
Function expTimes(prob As Range, counts As Range)
a = prob.Count
Dim arr_prob()
Dim arr_counts()
ReDim arr_prob(1 To 10)
ReDim arr_counts(1 To 10)
For i = 1 To a
arr_prob(i) = prob(i).Value
arr_counts(i) = counts(i).Value
Next
ReDim Preserve arr_prob(1 To a)
ReDim Preserve arr_counts(1 To a)
expTimes = expCount(arr_prob, arr_counts, a)
End Function
Function expCount(arr_prob, arr_counts, n)
sum_prob = Application.WorksheetFunction.SumProduct(arr_prob)
b = Application.WorksheetFunction.SumProduct(arr_counts)
a = Application.WorksheetFunction.Max(arr_counts)
If sum_prob = 0 Then
expCount = 0
ElseIf a = b Then
expCount = 1 / sum_prob * b
Else
temp_counts = 1 / sum_prob
For i = 1 To n
If arr_counts(i) > 0 Then
arr_counts(i) = arr_counts(i) - 1
temp_prob = arr_prob(i)
arr_prob(i) = Application.WorksheetFunction.Min(arr_prob(i), arr_counts(i))
temp_counts = temp_prob * expCount(arr_prob, arr_counts, n) / sum_prob + temp_counts
arr_counts(i) = arr_counts(i) + 1
arr_prob(i) = temp_prob
End If
Next
expCount = temp_counts
End If
End Function
expTimes是将选定的区域转化为数组,expCount是根据传入的数组和数组的元素个数,得出最终的结果.
这个代码已经改过几次了,但是还是很卡.有没有大佬指点一下?
|
|