Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula refresh problem
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!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula refresh problem
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 "BoringAccountant" wrote in message ups.com... | 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!!! | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula refresh problem
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula refresh problem
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 "BoringAccountant" wrote in message oups.com... | 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 | | | | | | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula refresh problem
On Nov 1, 3:53 pm, "Niek Otten" wrote:
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/lib...ffice.11).aspx (Vba was all I could find.) but it seems to work with excel 2003! OH YEAH! Thanks again, Jordan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Excel functions formula and auto refresh refresh | Excel Worksheet Functions | |||
getpivotdata refresh problem | Excel Worksheet Functions | |||
Form 'Refresh' problem | Excel Programming | |||
Refresh Problem | Excel Programming | |||
Screen refresh problem. | Excel Programming |