Пользовательская функция в Excel выдает ошибку #ЗНАЧ! , не знаю, с какой стороны подступиться

Здравствуйте.
У меня вопрос по пользовательским функциям в Excel'е. Похоже, мне необходимо сохранить значения моих переменных между вызовами функций, как мне это сделать? Ситуация такая: есть функция, которая выводит значения переменных в зависимости от значения входного параметра. Сами значения переменных рассчитываются в отдельной "вычисляющей процедуре". Все переменные - Public. Работать функция, по замыслу, должна так: когда ее вызывают в первый раз (всего на двух разных листах она вызывается 20 раз), то из функции вызывается "вычисляющая процедура" для расчета значений переменных и функция выводит значение требуемой (только что рассчитанной) переменной. В остальных 19 случаях функция должна просто брать из "памяти" значения рассчитанной переменной и выводить его, не обращаясь к "вычисляющей процедуре". А на следующей итерации все повторяется.
Не знаю, в чем проблема, но при первом вызове функции все работает нормально - функция выдает адекватное значение. При втором обращении (которое без вызова "вычисляющей процедуры") функция чаще всего выдает ошибку #ЗНАЧ!, хотя может и выдать значимый результат.
Подскажите, пожалуйста, как справиться с этой ошибкой.
Заранее большое спасибо.

в пользовательских функциях все зависит от пользователей... какие пользователи - такая и функция..

скажите где в этом абзаце текста информация на основании которой можно найти вашу ошибку?

Я так понимаю, что выкладывать сюда три с лишним страницы кода - это не comme il faut?
Не мог бы кто-нибудь мне (для начала) объяснить, как можно добиться в VBA, чтобы по окончании выполнения функции (процедуры) переменные Public продолжали хранить свое значение до следующего запуска функции, а не удалялись?

PS
Под следующим запуском функции имеется в виду запуск пользовательской функции из следующей ячейки таблицы Excel, а не вызов в той же процедуре через пару строк.

Сообщение отредактировано: Получайник - 29.02.08, 15:20

ну.. можно ведь и файл выложить?

что такое следующий запуск функции? (или "вызов процедуры через пару строк"???)
я понимаю с процедурой - она начитает работать в определенный момент времени/при каких-то условиях, а функцию в ячейку вставил - она и пересчитывается...

для того чтоб узнать значение public переменной существуют breakpoint'ы, окно watches (не помню как по-русски - у меня не локализированный офис), пошаговое выполнение процедуры/функции (F8) - очень помогает.

Попробую объяснить на свой дилетантский манер... Следующий запуск функции - это, как Вы говорите, "в ячейку вставил - она и пересчитывается", а потом в другую ячейку вставил - она там снова пересчитывается, потом в следующую ячейку и т.д. А вызов через пару строк - это (внутри одной функции) примерно так:

Sub s1()
...
End Sub
 
Function fun1()
...
Call s1
... 'пара строк
Call s1
...
End Function

В модуле книги

Public ДолгоживущаяПеременная as Long
 
Private Sub Workbook_Open()
    ДолгоживущаяПеременная = 0
End Sub

В общем модуле

Public Function МояПользовательскаяФункция(r As Range)
  ThisWorkbook.ДолгоживущаяПеременная = ThisWorkbook.ДолгоживущаяПеременная + 1
  МояПользовательскаяФункция = ThisWorkbook.ДолгоживущаяПеременная + 1
End Function

А теперь на листе в ячеку "А1" вставьте =МояПользовательскаяФункция(A1)
Протащите формулу вправо и насладитесь неожиданным эффектом, эксель пересчитывает формулы в том порядке, каком ему удобнее.

Цитата Krasnaja Shapka @ 29.02.08, 15:41

ну.. можно ведь и файл выложить?

Цитата Krasnaja Shapka @ 29.02.08, 15:41

для того чтоб узнать значение public переменной существуют breakpoint'ы, окно watches (не помню как по-русски - у меня не локализированный офис), пошаговое выполнение процедуры/функции (F8) - очень помогает.

А что, непосредственно к сообщению файл прикрепить нельзя? Только выложив на другие сайты? А есть ограничения, на какие сайты можно выкладывать?

Непосредственно к сообщению файл прикрепить можно. Для этого или перед вводом сообщения нажмите ссылку "Ответить", или это можно сделать на предварительном просмотре вашего сообщения

Спасибо, bi-lya.
Выкладываю файл. Я слегка поправил макрос за прошедшие дни, но проблема не исчезла: если нажать F9 (1 итерация при ручном пересчете), то на листе Расчеты вместо нулей (единственные черные символы на всем листе) возникнут сообщения об ошибке. Причем на следующей итерации сообщения об ошибке вновь сменятся на нули, но из-за перекрестных ссылок в формулах на эти ячейки ошибка начнет расползаться по листам.
Чтобы было удобней искать нужные ячейки, я все ячейки кроме ячеек, в которых вызывается пользовательская функция dtr(), покрасил белым. Функция вызывается только на листах Агенты и Расчеты.
Прикреплённый файлforum.zip (120.61 Кбайт, скачиваний: 172)

Неужели, никто не может подсказать, в чем дело?

Цитата Получайник @ 29.02.08, 11:19

Все переменные - Public

ну и хде они???

ваша функция у меня выдает 0, что вполне логично, ибо в начале стоит проверка на used=0 она не выполняется, программа переходит в конец и функция приравнивается переменной равной нулю.

и еще раз повторюсь:

Цитата Krasnaja Shapka @ 29.02.08, 15:41

для того чтоб узнать значение public переменной существуют breakpoint'ы, окно watches (не помню как по-русски - у меня не локализированный офис), пошаговое выполнение процедуры/функции (F8) - очень помогает.

кстати, это все надо использовать не только из-за public переменных, а постоянно, если хочешь узнать как работает твоя функция

Все таки видимо дело не в Public (т.к. сама функция Static), а в том, что как отметил pvr, Excel может вызывать функции не в том порядке, в каком ожидает автор, т.е. сначала вызываются функции с used=1 (в независимых ячейках), и только потом с used = 0 (т.к. эта ячейка зависит от других)

Krasnaja Shapka, так в том и проблема, что при в режиме отладки (Debugger) значения переменных правильные и процедура выполняется корректно, а на самом листе Excel'я возникают ошибки.

Цитата Krasnaja Shapka @ 14.03.08, 07:39

ну и хде они???

Public, извиняйте, больше нет - я писал о правках, которые были внесены уже после начала данной ветки на форуме. Вместо них я изменил тип функции на Static. Но проблема с тем, что функция выдает ошибку #ЗНАЧ! осталась.

Цитата Krasnaja Shapka @ 14.03.08, 07:39

ваша функция у меня выдает 0, что вполне логично, ибо в начале стоит проверка на used=0 она не выполняется, программа переходит в конец и функция приравнивается переменной равной нулю.

Да, не спорю. Я же говорил, проблемы начинаются со второй итерации (нажмите 1 раз кнопку F9 и взгляните на результат в оставленных мною ячейках).

leo, а есть какой-то стандартный способ контроля над тем, в какой последовательности Excel вызывает функции?

PS
Кстати, позвольте уточнить. В данном случае, я правильно понимаю, что, поскольку функция Static, Excel будет постоянно хранить последние значения всех внутренних переменных функции, покуда я не закрою Excel? Или при переходе вычислений (во время следующей итерации) на следующую ячейку/лист значения переменных будут обнуляться?

Ответьте, пожалуйста, хотя бы на вопрос из постскриптума про Static функцию в случае пересчета вручную.

про static, на сколько я понимаю static переменные будут сохранять свои значения между последовательными вызовами процедуры в которой они описаны... пиши public и не прогадаешь...

Цитата Получайник @ 16.03.08, 15:47

в режиме отладки (Debugger) значения переменных правильные и процедура выполняется корректно, а на самом листе Excel'я возникают ошибки.

не верю! (с) станиславский
главное не то что переменные правильные, весь вопрос в результате вычислений самой функции... т.е. какое значение ей присваивается в конечном итоге.

и еще так как результат у тебя зависит от того с какой ячейки excel начинает пересчет значений... то попробуй переписать все используя процедуры например... ибо ты не можешь сказать excel-ю в каком порядке ему все пересчитывать...
например вписать все в стандартные процедуры Workbook_Open и Workbook_SheetCalculate, т.е. при открытии или пересчете документа обновлять значения нужных ячеек...

Сообщение отредактировано: Krasnaja Shapka - 24.03.08, 14:13