Recalculation of User Function when Hiding arguments

[Dave]

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

 

[Niek Otten]

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

 

[Charles Williams]

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

 

[Tom Ogilvy]

I couldn't reproduce any behavior like that in Excel 2003.

 

[David Welch]

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

You also need automatic calculation on.

And its not a problem under Excel 2000 with either calculation setting!

 

[Charles Williams]

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]

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.