Итерации по коллекции

Я пытаюсь создать пользовательскую функцию для вычисления процента «средней месячной производительности» на основе диапазона ячеек в 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 
Interesting Posts

python xlrd неподдерживаемый формат или поврежденный файл.

Скрыть отображение: столбец с экрана: таблица struts и экспорт в Excel

Копировать и вставить значение в пункт назначения

Абсолютное имя файла в связанных книгах

Вырезать и вставить все столбцы, если ячейка равна «Fri» в диапазоне

VBA – SendKeys "Enter" Альтернатива для Mac Excel 2011

Поток огромный файл Excel, создавая его на лету?

Как определить, сколько строк генерирует сводная таблица (Aspose Cells)?

vba Автоматическое восстановление сводной таблицы DataField

Я хочу изменить значение раскрывающегося списка другой ячейки на основе значения, выбранного в раскрывающемся списке в excel

OleDb Connection не считывает все строки из файла excel

Запуск счетчика каждый раз, когда выполняется функция (VBA)

Заполнение второго поля со списком на основе первого поля со списком

Excel – переменное число ведущих нулей в номерах переменной длины?

Как использовать данные, введенные в пользовательскую форму в основном модуле

Давайте будем гением компьютера.