On Nov 1, 1:47 pm, "Niek Otten" wrote:
UDFs are automatically refreshed if you follow the advice to include all input to the function in the argument list.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


Appreciate the quick response, so there is no way to know what cell is
the target cell?

I don't actually want to refresh the function using outside means, I
want to pass off the argument list without having to implicitly
declare thier names in the hand-off.
This would allow a "template" to be created for each UDF. I have an
example below:

Public Function MyFunction(myParmOne As Variant, ByVal myParmTwo As
String, Optional myOptionalParmThree as Variant)

Dim strCellFormula as String
Dim intCnt As Integer
Dim strArgs As String
Dim strFunctionName As String
Dim strSQL

strCellFormula = oApp.ActiveCell.Formula

If strCellFormula = "" Then
MsgBox "Problem with oApp.ActiveCell.Formula"
End If

strArgs = GetFunctionLine(strCellFormula)
strFunctionName = GetFunctionName(strCellFormula)
intCnt = CountCSWords(strArgs)

strSQL = DefineFunc(intCnt, strFunctionName, strArgs)

MyFunction = strSQL
End Function

Public Function MyFunction2(myParmOne As Variant, ByVal myParmTwo As
String, ByVal myParmThree as String, Optional myOptionalParmFour as
Variant)

Dim strCellFormula as String
Dim intCnt As Integer
Dim strArgs As String
Dim strFunctionName As String
Dim strSQL

strCellFormula = oApp.ActiveCell.Formula

If strCellFormula = "" Then
MsgBox "Problem with oApp.ActiveCell.Formula"
End If

strArgs = GetFunctionLine(strCellFormula)
strFunctionName = GetFunctionName(strCellFormula)
intCnt = CountCSWords(strArgs)

strSQL = DefineFunc(intCnt, strFunctionName, strArgs)

MyFunction2 = strSQL
End Function


If there is not a way to get the calling cell during a CalculateFull,
is there a way to transpose the function name and entire list of args
into a string without harcoding the arguments? Much of my code for the
return value of MyFunction and MyFunction2 can be resused and I was
trying to be smarter about reusing the common parts. What I don't want
to code is this:

Public Function MyFunction2(myParmOne As Variant, ByVal myParmTwo As
String, ByVal myParmThree, Optional myOptionalParmThree)

Dim strCellFormula As String
Dim intCnt As Integer
Dim strArgs As String
Dim strFunctionName As String
Dim strSQL

strCellFormula = oApp.ActiveCell.Formula

If strCellFormula = "" Then
MsgBox "Problem with oApp.ActiveCell.Formula"
End If

strArgs = "'" & IIf(IsMissing(myParmOne), "'", myParmOne &
"'") & ",'" & myParmTwo & "','" & IIf(IsMissing(myParmThree), "'",
myParmThree & "'")
strFunctionName = "MyFunction2"
intCnt = CountCSWords(strArgs)

strSQL = DefineFunc(intCnt, strFunctionName, strArgs)

MyFunction2 = strSQL

End Function

While this is a simple function that only has three arguments, many of
my functions have up to 20 parameters and cell ranges can be passed,
so this chunk of code would be much larger.

Using the first method saves me an estimated 5000 lines of code. I can
define my function logic in the "DefineFunc" function and all of my
200+ functions can resuse it. All I have to is copy this basic code
template and replace the function arguments and function name. It
works using "F2" manual refresh, but I am at a loss on the Calculate
Methods.

Are the Calculate Methods just a black box with no windows?

Thanks for you help,
Jordan