Использование функции GoalSeek из пользовательской UDF-функц

[MAGRAV]

Доброго времени суток форумчане.
Столкнулся со следующей проблемой. Не могу воспользоваться функцией GoalSeek посредством пользовательской функции вводимой пользователем прямо в ячейку Excel. Работает только через GS() вызываемой через Alt+F8. Посредством ввода в ячейку =iGoalSeek(B5;B2;B3) или =sGoalSeek(5; 2; 2; 2; 3; 2) ничего не получается.

Код: Выделить всё

Public Function iGoalSeek(iRange As Range, _
                          iGoal As Range, _
                          iChangingCell As Range)
   Dim ir As Long
   Dim jr As Long
   Dim ig As Long
   Dim jg As Long
   Dim ic As Long
   Dim jc As Long
   
   ir = iRange.Row
   jr = iRange.Column
   ig = iGoal.Row
   jg = iGoal.Column
   ic = iChangingCell.Row
   jc = iChangingCell.Column

   fGoalSeek ir, jr, ig, jg, ic, jc
End Function

Function sGoalSeek(ir As Long, _
                   jr As Long, _
                   ig As Long, _
                   jg As Long, _
                   ic As Long, _
                   jc As Long)

   fGoalSeek ir, jr, ig, jg, ic, jc
End Function

Sub GS()
   fGoalSeek 5, 2, 2, 2, 3, 2
End Sub

Public Sub fGoalSeek(ir As Long, _
                     jr As Long, _
                     ig As Long, _
                     jg As Long, _
                     ic As Long, _
                     jc As Long)
   Dim iRange As Range
   Dim iGoal As Range
   Dim iChangingCell As Range
   
   Set iRange = Cells(ir, jr) 'ActiveCell
   Set iGoal = Cells(ig, jg) '"B2"
   Set iChangingCell = Cells(ic, jc) '"B3"
   iRange.GoalSeek Goal:=iGoal.Value, ChangingCell:=iChangingCell
End Sub


Заранее спасибо!

[Template]

Увы, но пользовательская функция, вызываемая из ячейки, не будет менять значения ячеек, в т.ч. и с помощью метода GoalSeek

[MAGRAV]

Увы, но пользовательская функция, вызываемая из ячейки, не будет менять значения ячеек, в т.ч. и с помощью метода GoalSeek

Вот способ:

Код: Выделить всё

Function ЧЕРВЬ(r As Range, t)
'взято тут:
'Нужна формула EXCEL которая даёт эффект копирования
'http://forum.msexcel.ru/empty-t194.0.html - Шпец Докапыч
    Application.Volatile
    With r
        .Replace IIf(Len(.Value), "*", ""), t
    End With
    ЧЕРВЬ = ""
End Function

Господа, как реализовать пользовательскую функцию способную убить саму себя?

[Template]

Хитёр бобёр ... я говорил о значении ячейки, а не о формуле с которой, собственно, и "работает" метод Replace.

А убить формулу, в т.ч. UDF, можно, если использовать событие листа/книги/приложения Calculate. Правда в этом случае
возникает резонный вопрос, а нужна ли вообще пользовательская функция ? ведь запускать рассчёт можно из события Change (разумеется, после заполнения всех необходимых ячеек)

[MAGRAV]

Вообще говоря, цель всех этих извращений в том чтобы измудриться использовать GoalSeek из пользовательской функции. А код функции находился только в одном модуле, который можно было бы подключать к любому документу. К примеру синтаксис на листе будет таким: "=iGoalSeek(A1;A2;A3)". Вся соль в том, что никак не удаётся сделать так чтобы можно было задавать из текста функции эти (A1;A2;A3). слабо верится,но хотел опробовать такой сценари:
-iGoalSeek запоминает свой собственный текст синтаксиса "=iGoalSeek(A1;A2;A3)" из текущей ячейки (B5);
-запоминает переменную из "А2";
-в "А2" помещаем ссылку на: "=B5";
-в "B5" число из "А2";
-после пробуем использовать встроенную функцию Excel "....GoalSeek Goal:=..., ChangingCell:=...",но ссылающуюся в параметре ChangingCell:=на "B5" вместо первоначаьного "А2".
если чудо произойдёт и и встроенный GoalSeek сработает, то возвращаем все на свои места (в том числе и новую переменую) и радумся

[Template]

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

Для того, чтобы процедуры, находящиеся в этом модуле, были доступны в других книгах - достаточно всего лишь расположить этот модуль в книге, которая будет открыта в течении всей работы с приложением. А это может быть собственная надстройка, личная книга макросов "Personal.xls" или любая другая книга, находящаяся в стандартном или альтернативном каталоге автозагрузке.

В общем, если интересна связка пользовательской функции и событий приложения, то смотрите пример, токмо метод GoalSeek вызывайте сами но без "разбивки и сборки" ячеек на номера строк и столбцов, ибо это совершенно лишняя операция.