formula refresh problem

[BoringAccountant]

Background:

I have a series of user-defined functions exposed as formulas within
Excel. They are being refreshed using the .CalculateFull method when a
user presses a command button on the right-click command bar.

They also have the option of using the F2 shortcut key to show the
cell contents of the formula and then pressing "enter" to refresh
formula within the single cell. To determine target cell in this case
is fairly easy. You can use the Application.ActiveCell.Formula

How can I determine which cell is the "ActiveCell" when a user invokes
the .CalculateFull method? The normal Application.ActiveCell.Formula
does not work and only returns the same cell where the cursor was
located when the .CalculateFull method was first invoked.

I am using VB 6.0 and programming against the Excel 2003 Object Model.

Any guidance would be appreciated!!!

 

[Niek Otten]

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

| Background:
|
| I have a series of user-defined functions exposed as formulas within
| Excel. They are being refreshed using the .CalculateFull method when a
| user presses a command button on the right-click command bar.
|
| They also have the option of using the F2 shortcut key to show the
| cell contents of the formula and then pressing "enter" to refresh
| formula within the single cell. To determine target cell in this case
| is fairly easy. You can use the Application.ActiveCell.Formula
|
| How can I determine which cell is the "ActiveCell" when a user invokes
| the .CalculateFull method? The normal Application.ActiveCell.Formula
| does not work and only returns the same cell where the cursor was
| located when the .CalculateFull method was first invoked.
|
| I am using VB 6.0 and programming against the Excel 2003 Object Model.
|
| Any guidance would be appreciated!!!
|

 

[BoringAccountant]

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

 

[Niek Otten]

Sorry, I have no idea at all what you're trying to do.

Did you look at Application.Caller?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| > 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
|
|
|
|
|
|

 

[BoringAccountant]

Sorry, I have no idea at all what you're trying to do.

Did you look at Application.Caller?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

That's it! I am dancing around in my office. You have made my month.
It shows up under the
Excel 2007 http://msdn2.microsoft.com/en-us/library/bb220902.aspx
reference but not under the
Excel 2003 http://msdn2.microsoft.com/en-us/library/aa213696(office.11).aspx
(Vba was all I could find.)
but it seems to work with excel 2003! OH YEAH!
Thanks again,
Jordan