Итерации по коллекции
Я пытаюсь создать пользовательскую функцию для вычисления процента «средней месячной производительности» на основе диапазона ячеек в Excel. Функция должна игнорировать ячейки со значением 0 и строкой "NA" .
У меня нет проблем с созданием коллекции из диапазона ячеек:
Function KROWPERFMEAN(rng As Range) Dim val As Integer Dim i As Integer Dim cell As Range Dim coll As Collection Set coll = New Collection i = 1 For Each cell In rng If (cell.Value <> "NA" And cell.Value <> 0) Then coll.Add cell.Value End If Next cell
Когда я пытаюсь пройти через коллекцию, мой код ломается, не бросает ошибку и не возвращает результат:
Dim perf As Variant Dim y As Variant 'loop through collection and get perf For Each y In coll perf = perf + (coll(i) - coll(i + 1)) / coll(i) 'MsgBox (perf) '<-- both of these message boxes fire with exected #s 'MsgBox (i) i = i + 1 Next MsgBox ("This message box never fires with no errors thrown") 'assigned "1" to test, code is never reached KROWPERFMEAN = 1 End Function
Есть ли проблема с тем, как я перебираю коллекцию?
Я пробовал несколько решений (меняя тип y , объявляя переменную в блоке For Each) без успеха.
Проблема здесь:
perf = perf + (coll(i) - coll(i + 1)) / coll(i)
С coll (i + 1), как только вы дойдете до конца коллекции, вы пытаетесь получить доступ к члену, которого не существует. Ошибка, с которой она бесшумно терпит неудачу, – это «Подзаголовок вне диапазона».
Не зная подробностей о ваших расчетах, я думаю, что вы, вероятно, должны сделать что-то вроде этого, потому что нет второго значения для вычисления на последнем шаге.
Function KROWPERFMEAN(rng As Range) Dim val As Integer Dim i As Integer Dim cell As Range Dim coll As Collection Set coll = New Collection i = 1 For Each cell In rng If (cell.Value <> "NA" And cell.Value <> 0) Then coll.Add cell.Value End If Next cell Dim perf As Variant Dim y As Variant 'loop through collection and get perf For Each y In coll If (i + 1 < coll.Count) Then perf = perf + (coll(i) - coll(i + 1)) / coll(i) End If i = i + 1 Next KROWPERFMEAN = perf End Function