Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculation of User Function when Hiding arguments
Hi,
I have a function which returns some values, but sometimes I don't want to see all the results and so hide some rows. When hiding some of the rows, some of the function input arguments also get hidden. This results in a recalculation of the formula. This would not normally be a problem except that when the formula is recalculated accessing properties like .Text returns an error. Pressing F2 on the formula and enter to re-evalulate and everything is fine. Has anyone come across this before and solved the problem (or not). I would appreciate some help! Thanks, David |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculation of User Function when Hiding arguments
Hi Dave,
I'm afraid you'll have to be a lot more explicit. What formulas? What function (code?)? What input values? What results did you expect? What error exactly did you get? -- Kind Regards, Niek Otten Microsoft MVP - Excel "Dave" wrote in message om... Hi, I have a function which returns some values, but sometimes I don't want to see all the results and so hide some rows. When hiding some of the rows, some of the function input arguments also get hidden. This results in a recalculation of the formula. This would not normally be a problem except that when the formula is recalculated accessing properties like .Text returns an error. Pressing F2 on the formula and enter to re-evalulate and everything is fine. Has anyone come across this before and solved the problem (or not). I would appreciate some help! Thanks, David |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculation of User Function when Hiding arguments
Hi Dave,
In Excel 2003 hiding and unhiding rows results in a recalculation. However I cannot duplicate your UDF problem in accessing the .text property of a cell when it is hidden: it works fine for me in both Excel 2002 and 2003. (as a matter of interest why would you want to access .Text in a UDF anyway rather than say .Value2 ?) regards Charles ______________________ Decision Models FastExcel 2.1 now available www.DecisionModels.com "Dave" wrote in message om... Hi, I have a function which returns some values, but sometimes I don't want to see all the results and so hide some rows. When hiding some of the rows, some of the function input arguments also get hidden. This results in a recalculation of the formula. This would not normally be a problem except that when the formula is recalculated accessing properties like .Text returns an error. Pressing F2 on the formula and enter to re-evalulate and everything is fine. Has anyone come across this before and solved the problem (or not). I would appreciate some help! Thanks, David |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculation of User Function when Hiding arguments
I couldn't reproduce any behavior like that in Excel 2003.
-- Regards, Tom Ogilvy "Dave" wrote in message om... Hi, I have a function which returns some values, but sometimes I don't want to see all the results and so hide some rows. When hiding some of the rows, some of the function input arguments also get hidden. This results in a recalculation of the formula. This would not normally be a problem except that when the formula is recalculated accessing properties like .Text returns an error. Pressing F2 on the formula and enter to re-evalulate and everything is fine. Has anyone come across this before and solved the problem (or not). I would appreciate some help! Thanks, David |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculation of User Function when Hiding arguments
Tom Ogilvy wrote:
I couldn't reproduce any behavior like that in Excel 2003. OK, I have some code below to reproduce the problem under Excel 2003. Copy in this code and run BuildWorkbook to insert some functions. It will give error message in cells A7 down (never knew it would produce error on building!). Recalculate function A7 down by pressing F2 and Ctrl+Shift+Enter. Now double-click in Cell A1. The reason for using Text property is because the parameters for the function are all over the workbook and I have linked some cells so that they can be read in as one range. Unfortunatly when you have a link to a blank cell Excel gives a 0 as the formula result, which is sometimes a valid parameter. I have used conditional formatting to blank out this value as neccessary, and then the text property to access the values. This is probably not a good solution, so if anyone has a better (or even different:-)) idea please post. Thanks Code in Module 1 is : Public Function RangeText(R As Range) As String On Error GoTo Failed RangeText = R.Text Exit Function Failed: RangeText = Err.Description End Function Public Sub BuildWorkbook() 'build worksheet ActiveSheet.Cells(1, 1).Value = "Double-click this cell to hide" ActiveSheet.Cells(5, 1).Value = "5" ActiveSheet.Cells(7, 1).Resize(5, 1).FormulaArray = "=RangeText(A5)" End Sub Code in Sheet1 is : Private unhide_ As Boolean Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Address = ActiveSheet.Cells(1, 1).Address Then If unhide_ Then Target.Value = "Double-click this cell to hide" Else ActiveSheet.Cells(5, 1).EntireRow.Hidden = True Target.Value = "Double-click this cell to unhide" End If unhide_ = Not unhide_ ActiveSheet.Cells(5, 1).EntireRow.Hidden = unhide_ Cancel = True End If End Sub --- David (posting from a different address) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculation of User Function when Hiding arguments
You also need automatic calculation on.
And its not a problem under Excel 2000 with either calculation setting! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculation of User Function when Hiding arguments
I think this is another instance of the problem of referring to
format-related properties of a cell inside a UDF with automatic recalculation. (another is referring to .Bold and renaming sheets in Automatic mode). It happens because an automatic recalculation has different phases for recalculating the values and then formatting the results, and the UDF is trying to get a formatted result (.Text) after the values have been recalculated but before they have been reformatted. It does not happen with manual hiding/unhiding of rows. A simple bypass, which is also more efficient, is for your VBA code to switch to manual mode before hiding/unhiding the rows, then switch back to automatic. Alternatively use .value rather than .text The reason this does not happen for your code with Excel 2000 is that hiding/unhiding rows does not trigger a recalculation in Excel versions before 2003. Charles ______________________ Decision Models FastExcel 2.1 now available www.DecisionModels.com "David Welch" wrote in message ... You also need automatic calculation on. And its not a problem under Excel 2000 with either calculation setting! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculation of User Function when Hiding arguments
Charles Williams wrote:
I think this is another instance of the problem of referring to format-related properties of a cell inside a UDF with automatic recalculation. (another is referring to .Bold and renaming sheets in Automatic mode). It happens because an automatic recalculation has different phases for recalculating the values and then formatting the results, and the UDF is trying to get a formatted result (.Text) after the values have been recalculated but before they have been reformatted. It does not happen with manual hiding/unhiding of rows. A simple bypass, which is also more efficient, is for your VBA code to switch to manual mode before hiding/unhiding the rows, then switch back to automatic. Alternatively use .value rather than .text The reason this does not happen for your code with Excel 2000 is that hiding/unhiding rows does not trigger a recalculation in Excel versions before 2003. Charles ______________________ Decision Models FastExcel 2.1 now available www.DecisionModels.com Thanks, I now see why some properties do work and some do not. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
recalculation not recalculating some cells containing user functio | Excel Discussion (Misc queries) | |||
empty arguments recalculation | Excel Worksheet Functions | |||
Recalculation on hiding rows in Excel 2003 | Excel Discussion (Misc queries) | |||
weird recalculation of user defined function | Excel Worksheet Functions | |||
Excel user-defined Function: definition/help of arguments | Excel Programming |