Functions won't calculate without F2

Good morning all!

I wrote a simple function to calculate the values of all hidden cells in a
range.
It works fine, except that I have to edit the formula with [F2] and [Enter]
before it displays the correct answer - otherwise, it just displays the
result of the previous calculation.

Can anyone help, please?

Thanks in advance

Pete

Function SumHiddenRows(TheHiddenRange)
Dim HiddenTotal As Long
Dim HiddenCell As Range

HiddenTotal = 0
For Each HiddenCell In TheHiddenRange
If HiddenCell.EntireRow.Hidden = True Then
HiddenTotal = HiddenTotal + HiddenCell.Value
End If
Next
SumHiddenRows = HiddenTotal
Calculate
End Function

Hi Peter,

If you change the value of any of the cells in the hidden range, the
function should recalculate, unless you happen to have Manual calculation
switched on (Tools>Options, Calculation tab).
But changing a cell form visible to hidden does not trigger a recalculation,
not even if you include Application.Volatile.
You could force a recalc by including a volatile function in the formula,
like in

=SumHiddenRows(A1:A10)+(now()*0)

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"Peter Rooney" <[email protected]> wrote in message
news:[email protected]...
> Good morning all!
>
> I wrote a simple function to calculate the values of all hidden cells in a
> range.
> It works fine, except that I have to edit the formula with [F2] and
> [Enter]
> before it displays the correct answer - otherwise, it just displays the
> result of the previous calculation.
>
> Can anyone help, please?
>
> Thanks in advance
>
> Pete
>
> Function SumHiddenRows(TheHiddenRange)
> Dim HiddenTotal As Long
> Dim HiddenCell As Range
>
> HiddenTotal = 0
> For Each HiddenCell In TheHiddenRange
> If HiddenCell.EntireRow.Hidden = True Then
> HiddenTotal = HiddenTotal + HiddenCell.Value
> End If
> Next
> SumHiddenRows = HiddenTotal
> Calculate
> End Function
>

Hmm. Perhaps I should have been a little more specific with the problem.

I have a range of cells containing the numbers 1-10, with 4 buttons that run
macros to hide/unhide the rows containg the odd or even numbers.

It's when I run these macros to hide 1/3/5/7/8 (displaying 2/4/6/8/10) or
hide 2/4/6/8/10 (displaying 1/3/5/7/9) that the function doesn't recalculate.

There is a SumVisibleRows function too that does the opposite of
SumHiddenRows, displaying a total for all the visible cells in the range thus:

Function SumVisibleRows(TheVisibleRange)
Dim VisibleTotal As Long
Dim VisibleCell As Range

VisibleTotal = 0
For Each VisibleCell In TheVisibleRange
If VisibleCell.EntireRow.Hidden = False Then
VisibleTotal = VisibleTotal + VisibleCell.Value
End If
Next
SumVisibleRows = VisibleTotal
Calculate
End Function

If I change the value of a visible cell in the list, SumVisibleCells
correctly calculates the new value without F2, but neither formula calculates
new totals on its own when the hide/unhide macros are run.

Should this be some sort of Worksheet_Change macro instead, and if so, can
anyone advise on the syntax for picking up changes to the hidden property of
a row?

Thanks again

Pete



"Peter Rooney" wrote:

> Good morning all!
>
> I wrote a simple function to calculate the values of all hidden cells in a
> range.
> It works fine, except that I have to edit the formula with [F2] and [Enter]
> before it displays the correct answer - otherwise, it just displays the
> result of the previous calculation.
>
> Can anyone help, please?
>
> Thanks in advance
>
> Pete
>
> Function SumHiddenRows(TheHiddenRange)
> Dim HiddenTotal As Long
> Dim HiddenCell As Range
>
> HiddenTotal = 0
> For Each HiddenCell In TheHiddenRange
> If HiddenCell.EntireRow.Hidden = True Then
> HiddenTotal = HiddenTotal + HiddenCell.Value
> End If
> Next
> SumHiddenRows = HiddenTotal
> Calculate
> End Function
>

I'm afraid even that won't work. You'll have to include a dummy argument and
supply NOW() as value to get the function recalculated

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"Niek Otten" <[email protected]> wrote in message
news:[email protected]...
> Hi Peter,
>
> If you change the value of any of the cells in the hidden range, the
> function should recalculate, unless you happen to have Manual calculation
> switched on (Tools>Options, Calculation tab).
> But changing a cell form visible to hidden does not trigger a
> recalculation, not even if you include Application.Volatile.
> You could force a recalc by including a volatile function in the formula,
> like in
>
> =SumHiddenRows(A1:A10)+(now()*0)
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "Peter Rooney" <[email protected]> wrote in message
> news:[email protected]...
>> Good morning all!
>>
>> I wrote a simple function to calculate the values of all hidden cells in
>> a
>> range.
>> It works fine, except that I have to edit the formula with [F2] and
>> [Enter]
>> before it displays the correct answer - otherwise, it just displays the
>> result of the previous calculation.
>>
>> Can anyone help, please?
>>
>> Thanks in advance
>>
>> Pete
>>
>> Function SumHiddenRows(TheHiddenRange)
>> Dim HiddenTotal As Long
>> Dim HiddenCell As Range
>>
>> HiddenTotal = 0
>> For Each HiddenCell In TheHiddenRange
>> If HiddenCell.EntireRow.Hidden = True Then
>> HiddenTotal = HiddenTotal + HiddenCell.Value
>> End If
>> Next
>> SumHiddenRows = HiddenTotal
>> Calculate
>> End Function
>>
>
>

Thanks for giving it a go, anyway, Niek! :-)

Pete



"Niek Otten" wrote:

> I'm afraid even that won't work. You'll have to include a dummy argument and
> supply NOW() as value to get the function recalculated
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "Niek Otten" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Peter,
> >
> > If you change the value of any of the cells in the hidden range, the
> > function should recalculate, unless you happen to have Manual calculation
> > switched on (Tools>Options, Calculation tab).
> > But changing a cell form visible to hidden does not trigger a
> > recalculation, not even if you include Application.Volatile.
> > You could force a recalc by including a volatile function in the formula,
> > like in
> >
> > =SumHiddenRows(A1:A10)+(now()*0)
> >
> > --
> > Kind regards,
> >
> > Niek Otten
> >
> > Microsoft MVP - Excel
> >
> > "Peter Rooney" <[email protected]> wrote in message
> > news:[email protected]...
> >> Good morning all!
> >>
> >> I wrote a simple function to calculate the values of all hidden cells in
> >> a
> >> range.
> >> It works fine, except that I have to edit the formula with [F2] and
> >> [Enter]
> >> before it displays the correct answer - otherwise, it just displays the
> >> result of the previous calculation.
> >>
> >> Can anyone help, please?
> >>
> >> Thanks in advance
> >>
> >> Pete
> >>
> >> Function SumHiddenRows(TheHiddenRange)
> >> Dim HiddenTotal As Long
> >> Dim HiddenCell As Range
> >>
> >> HiddenTotal = 0
> >> For Each HiddenCell In TheHiddenRange
> >> If HiddenCell.EntireRow.Hidden = True Then
> >> HiddenTotal = HiddenTotal + HiddenCell.Value
> >> End If
> >> Next
> >> SumHiddenRows = HiddenTotal
> >> Calculate
> >> End Function
> >>
> >
> >
>
>
>

Niek,

I found a cheat.

In each of my Hide/Unhide Odd/Enen Numbers, I called a macro that simply put
the formulae back into the cells again thus:

Sub RedoFormulae()
Range("HiddenTotal").FormulaR1C1 = "=sumhiddenrows(NumberRange)"
Range("VisibleTotal").FormulaR1C1 = "=sumvisiblerows(NumberRange)"
End Sub

It's the equivalent of pressing F2 in each of the cells calling the functions.

Cheers

Pete
"Niek Otten" wrote:

> I'm afraid even that won't work. You'll have to include a dummy argument and
> supply NOW() as value to get the function recalculated
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "Niek Otten" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Peter,
> >
> > If you change the value of any of the cells in the hidden range, the
> > function should recalculate, unless you happen to have Manual calculation
> > switched on (Tools>Options, Calculation tab).
> > But changing a cell form visible to hidden does not trigger a
> > recalculation, not even if you include Application.Volatile.
> > You could force a recalc by including a volatile function in the formula,
> > like in
> >
> > =SumHiddenRows(A1:A10)+(now()*0)
> >
> > --
> > Kind regards,
> >
> > Niek Otten
> >
> > Microsoft MVP - Excel
> >
> > "Peter Rooney" <[email protected]> wrote in message
> > news:[email protected]...
> >> Good morning all!
> >>
> >> I wrote a simple function to calculate the values of all hidden cells in
> >> a
> >> range.
> >> It works fine, except that I have to edit the formula with [F2] and
> >> [Enter]
> >> before it displays the correct answer - otherwise, it just displays the
> >> result of the previous calculation.
> >>
> >> Can anyone help, please?
> >>
> >> Thanks in advance
> >>
> >> Pete
> >>
> >> Function SumHiddenRows(TheHiddenRange)
> >> Dim HiddenTotal As Long
> >> Dim HiddenCell As Range
> >>
> >> HiddenTotal = 0
> >> For Each HiddenCell In TheHiddenRange
> >> If HiddenCell.EntireRow.Hidden = True Then
> >> HiddenTotal = HiddenTotal + HiddenCell.Value
> >> End If
> >> Next
> >> SumHiddenRows = HiddenTotal
> >> Calculate
> >> End Function
> >>
> >
> >
>
>
>

You could add:

application.volatile

At the top of your sub, but that just means that the function will calculate the
next time excel calculates. (and I'd remove the calculate from your UDF--I bet
it doesn't help!)

But hiding a row doesn't force calculation (until you get to xl2003). Maybe you
can add an "application.calculate" to the routine that hides the rows???

(In xl2003, =subtotal() has been enhanced to be able to ignore manually hidden
rows: =subtotal(109,a1:a22)

(100 + # means to ignore the manually hidden row.)

Peter Rooney wrote:
>
> Hmm. Perhaps I should have been a little more specific with the problem.
>
> I have a range of cells containing the numbers 1-10, with 4 buttons that run
> macros to hide/unhide the rows containg the odd or even numbers.
>
> It's when I run these macros to hide 1/3/5/7/8 (displaying 2/4/6/8/10) or
> hide 2/4/6/8/10 (displaying 1/3/5/7/9) that the function doesn't recalculate.
>
> There is a SumVisibleRows function too that does the opposite of
> SumHiddenRows, displaying a total for all the visible cells in the range thus:
>
> Function SumVisibleRows(TheVisibleRange)
> Dim VisibleTotal As Long
> Dim VisibleCell As Range
>
> VisibleTotal = 0
> For Each VisibleCell In TheVisibleRange
> If VisibleCell.EntireRow.Hidden = False Then
> VisibleTotal = VisibleTotal + VisibleCell.Value
> End If
> Next
> SumVisibleRows = VisibleTotal
> Calculate
> End Function
>
> If I change the value of a visible cell in the list, SumVisibleCells
> correctly calculates the new value without F2, but neither formula calculates
> new totals on its own when the hide/unhide macros are run.
>
> Should this be some sort of Worksheet_Change macro instead, and if so, can
> anyone advise on the syntax for picking up changes to the hidden property of
> a row?
>
> Thanks again
>
> Pete
>
> "Peter Rooney" wrote:
>
> > Good morning all!
> >
> > I wrote a simple function to calculate the values of all hidden cells in a
> > range.
> > It works fine, except that I have to edit the formula with [F2] and [Enter]
> > before it displays the correct answer - otherwise, it just displays the
> > result of the previous calculation.
> >
> > Can anyone help, please?
> >
> > Thanks in advance
> >
> > Pete
> >
> > Function SumHiddenRows(TheHiddenRange)
> > Dim HiddenTotal As Long
> > Dim HiddenCell As Range
> >
> > HiddenTotal = 0
> > For Each HiddenCell In TheHiddenRange
> > If HiddenCell.EntireRow.Hidden = True Then
> > HiddenTotal = HiddenTotal + HiddenCell.Value
> > End If
> > Next
> > SumHiddenRows = HiddenTotal
> > Calculate
> > End Function
> >

--

Dave Peterson

Dave,

I modified my hide/unhide formulae to re-enter the custom functions into
their appropriate cells and it works OK. Can't wait for my Office XP upgrade!
:-)

Thanks

Pete



"Dave Peterson" wrote:

> You could add:
>
> application.volatile
>
> At the top of your sub, but that just means that the function will calculate the
> next time excel calculates. (and I'd remove the calculate from your UDF--I bet
> it doesn't help!)
>
> But hiding a row doesn't force calculation (until you get to xl2003). Maybe you
> can add an "application.calculate" to the routine that hides the rows???
>
> (In xl2003, =subtotal() has been enhanced to be able to ignore manually hidden
> rows: =subtotal(109,a1:a22)
>
> (100 + # means to ignore the manually hidden row.)
>
> Peter Rooney wrote:
> >
> > Hmm. Perhaps I should have been a little more specific with the problem.
> >
> > I have a range of cells containing the numbers 1-10, with 4 buttons that run
> > macros to hide/unhide the rows containg the odd or even numbers.
> >
> > It's when I run these macros to hide 1/3/5/7/8 (displaying 2/4/6/8/10) or
> > hide 2/4/6/8/10 (displaying 1/3/5/7/9) that the function doesn't recalculate.
> >
> > There is a SumVisibleRows function too that does the opposite of
> > SumHiddenRows, displaying a total for all the visible cells in the range thus:
> >
> > Function SumVisibleRows(TheVisibleRange)
> > Dim VisibleTotal As Long
> > Dim VisibleCell As Range
> >
> > VisibleTotal = 0
> > For Each VisibleCell In TheVisibleRange
> > If VisibleCell.EntireRow.Hidden = False Then
> > VisibleTotal = VisibleTotal + VisibleCell.Value
> > End If
> > Next
> > SumVisibleRows = VisibleTotal
> > Calculate
> > End Function
> >
> > If I change the value of a visible cell in the list, SumVisibleCells
> > correctly calculates the new value without F2, but neither formula calculates
> > new totals on its own when the hide/unhide macros are run.
> >
> > Should this be some sort of Worksheet_Change macro instead, and if so, can
> > anyone advise on the syntax for picking up changes to the hidden property of
> > a row?
> >
> > Thanks again
> >
> > Pete
> >
> > "Peter Rooney" wrote:
> >
> > > Good morning all!
> > >
> > > I wrote a simple function to calculate the values of all hidden cells in a
> > > range.
> > > It works fine, except that I have to edit the formula with [F2] and [Enter]
> > > before it displays the correct answer - otherwise, it just displays the
> > > result of the previous calculation.
> > >
> > > Can anyone help, please?
> > >
> > > Thanks in advance
> > >
> > > Pete
> > >
> > > Function SumHiddenRows(TheHiddenRange)
> > > Dim HiddenTotal As Long
> > > Dim HiddenCell As Range
> > >
> > > HiddenTotal = 0
> > > For Each HiddenCell In TheHiddenRange
> > > If HiddenCell.EntireRow.Hidden = True Then
> > > HiddenTotal = HiddenTotal + HiddenCell.Value
> > > End If
> > > Next
> > > SumHiddenRows = HiddenTotal
> > > Calculate
> > > End Function
> > >
>
> --
>
> Dave Peterson
>

I saw that in your other post.

I think I would have just recalculated in the subroutine that hid/unhid the
rows.


Peter Rooney wrote:
>
> Dave,
>
> I modified my hide/unhide formulae to re-enter the custom functions into
> their appropriate cells and it works OK. Can't wait for my Office XP upgrade!
> :-)
>
> Thanks
>
> Pete
>
> "Dave Peterson" wrote:
>
> > You could add:
> >
> > application.volatile
> >
> > At the top of your sub, but that just means that the function will calculate the
> > next time excel calculates. (and I'd remove the calculate from your UDF--I bet
> > it doesn't help!)
> >
> > But hiding a row doesn't force calculation (until you get to xl2003). Maybe you
> > can add an "application.calculate" to the routine that hides the rows???
> >
> > (In xl2003, =subtotal() has been enhanced to be able to ignore manually hidden
> > rows: =subtotal(109,a1:a22)
> >
> > (100 + # means to ignore the manually hidden row.)
> >
> > Peter Rooney wrote:
> > >
> > > Hmm. Perhaps I should have been a little more specific with the problem.
> > >
> > > I have a range of cells containing the numbers 1-10, with 4 buttons that run
> > > macros to hide/unhide the rows containg the odd or even numbers.
> > >
> > > It's when I run these macros to hide 1/3/5/7/8 (displaying 2/4/6/8/10) or
> > > hide 2/4/6/8/10 (displaying 1/3/5/7/9) that the function doesn't recalculate.
> > >
> > > There is a SumVisibleRows function too that does the opposite of
> > > SumHiddenRows, displaying a total for all the visible cells in the range thus:
> > >
> > > Function SumVisibleRows(TheVisibleRange)
> > > Dim VisibleTotal As Long
> > > Dim VisibleCell As Range
> > >
> > > VisibleTotal = 0
> > > For Each VisibleCell In TheVisibleRange
> > > If VisibleCell.EntireRow.Hidden = False Then
> > > VisibleTotal = VisibleTotal + VisibleCell.Value
> > > End If
> > > Next
> > > SumVisibleRows = VisibleTotal
> > > Calculate
> > > End Function
> > >
> > > If I change the value of a visible cell in the list, SumVisibleCells
> > > correctly calculates the new value without F2, but neither formula calculates
> > > new totals on its own when the hide/unhide macros are run.
> > >
> > > Should this be some sort of Worksheet_Change macro instead, and if so, can
> > > anyone advise on the syntax for picking up changes to the hidden property of
> > > a row?
> > >
> > > Thanks again
> > >
> > > Pete
> > >
> > > "Peter Rooney" wrote:
> > >
> > > > Good morning all!
> > > >
> > > > I wrote a simple function to calculate the values of all hidden cells in a
> > > > range.
> > > > It works fine, except that I have to edit the formula with [F2] and [Enter]
> > > > before it displays the correct answer - otherwise, it just displays the
> > > > result of the previous calculation.
> > > >
> > > > Can anyone help, please?
> > > >
> > > > Thanks in advance
> > > >
> > > > Pete
> > > >
> > > > Function SumHiddenRows(TheHiddenRange)
> > > > Dim HiddenTotal As Long
> > > > Dim HiddenCell As Range
> > > >
> > > > HiddenTotal = 0
> > > > For Each HiddenCell In TheHiddenRange
> > > > If HiddenCell.EntireRow.Hidden = True Then
> > > > HiddenTotal = HiddenTotal + HiddenCell.Value
> > > > End If
> > > > Next
> > > > SumHiddenRows = HiddenTotal
> > > > Calculate
> > > > End Function
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Dave,

I did and it doesn't work - whether you put the calculate command in the
function or the hide/unhide macros. It doesn't even work if you press F9 to
recalculate manually. You have to F2/Enter the cells containing the functions
(or, in VBA, re-enter the formulae)

Puzzling, isn't it?

Pete



"Dave Peterson" wrote:

> I saw that in your other post.
>
> I think I would have just recalculated in the subroutine that hid/unhid the
> rows.
>
>
> Peter Rooney wrote:
> >
> > Dave,
> >
> > I modified my hide/unhide formulae to re-enter the custom functions into
> > their appropriate cells and it works OK. Can't wait for my Office XP upgrade!
> > :-)
> >
> > Thanks
> >
> > Pete
> >
> > "Dave Peterson" wrote:
> >
> > > You could add:
> > >
> > > application.volatile
> > >
> > > At the top of your sub, but that just means that the function will calculate the
> > > next time excel calculates. (and I'd remove the calculate from your UDF--I bet
> > > it doesn't help!)
> > >
> > > But hiding a row doesn't force calculation (until you get to xl2003). Maybe you
> > > can add an "application.calculate" to the routine that hides the rows???
> > >
> > > (In xl2003, =subtotal() has been enhanced to be able to ignore manually hidden
> > > rows: =subtotal(109,a1:a22)
> > >
> > > (100 + # means to ignore the manually hidden row.)
> > >
> > > Peter Rooney wrote:
> > > >
> > > > Hmm. Perhaps I should have been a little more specific with the problem.
> > > >
> > > > I have a range of cells containing the numbers 1-10, with 4 buttons that run
> > > > macros to hide/unhide the rows containg the odd or even numbers.
> > > >
> > > > It's when I run these macros to hide 1/3/5/7/8 (displaying 2/4/6/8/10) or
> > > > hide 2/4/6/8/10 (displaying 1/3/5/7/9) that the function doesn't recalculate.
> > > >
> > > > There is a SumVisibleRows function too that does the opposite of
> > > > SumHiddenRows, displaying a total for all the visible cells in the range thus:
> > > >
> > > > Function SumVisibleRows(TheVisibleRange)
> > > > Dim VisibleTotal As Long
> > > > Dim VisibleCell As Range
> > > >
> > > > VisibleTotal = 0
> > > > For Each VisibleCell In TheVisibleRange
> > > > If VisibleCell.EntireRow.Hidden = False Then
> > > > VisibleTotal = VisibleTotal + VisibleCell.Value
> > > > End If
> > > > Next
> > > > SumVisibleRows = VisibleTotal
> > > > Calculate
> > > > End Function
> > > >
> > > > If I change the value of a visible cell in the list, SumVisibleCells
> > > > correctly calculates the new value without F2, but neither formula calculates
> > > > new totals on its own when the hide/unhide macros are run.
> > > >
> > > > Should this be some sort of Worksheet_Change macro instead, and if so, can
> > > > anyone advise on the syntax for picking up changes to the hidden property of
> > > > a row?
> > > >
> > > > Thanks again
> > > >
> > > > Pete
> > > >
> > > > "Peter Rooney" wrote:
> > > >
> > > > > Good morning all!
> > > > >
> > > > > I wrote a simple function to calculate the values of all hidden cells in a
> > > > > range.
> > > > > It works fine, except that I have to edit the formula with [F2] and [Enter]
> > > > > before it displays the correct answer - otherwise, it just displays the
> > > > > result of the previous calculation.
> > > > >
> > > > > Can anyone help, please?
> > > > >
> > > > > Thanks in advance
> > > > >
> > > > > Pete
> > > > >
> > > > > Function SumHiddenRows(TheHiddenRange)
> > > > > Dim HiddenTotal As Long
> > > > > Dim HiddenCell As Range
> > > > >
> > > > > HiddenTotal = 0
> > > > > For Each HiddenCell In TheHiddenRange
> > > > > If HiddenCell.EntireRow.Hidden = True Then
> > > > > HiddenTotal = HiddenTotal + HiddenCell.Value
> > > > > End If
> > > > > Next
> > > > > SumHiddenRows = HiddenTotal
> > > > > Calculate
> > > > > End Function
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Thats right, this is the behaviour under Excel 2000 and below, but not
Excel 2002 and greater. Your macro works under Excel 2003.


Peter Rooney wrote:
> Dave,
>
> I did and it doesn't work - whether you put the calculate command in the
> function or the hide/unhide macros. It doesn't even work if you press F9 to
> recalculate manually. You have to F2/Enter the cells containing the functions
> (or, in VBA, re-enter the formulae)
>
> Puzzling, isn't it?
>
> Pete
>
>
>
> "Dave Peterson" wrote:
>
>
>>I saw that in your other post.
>>
>>I think I would have just recalculated in the subroutine that hid/unhid the
>>rows.
>>
>>
>>Peter Rooney wrote:
>>
>>>Dave,
>>>
>>>I modified my hide/unhide formulae to re-enter the custom functions into
>>>their appropriate cells and it works OK. Can't wait for my Office XP upgrade!
>>>:-)
>>>
>>>Thanks
>>>
>>>Pete
>>>
>>>"Dave Peterson" wrote:
>>>
>>>
>>>>You could add:
>>>>
>>>> application.volatile
>>>>
>>>>At the top of your sub, but that just means that the function will calculate the
>>>>next time excel calculates. (and I'd remove the calculate from your UDF--I bet
>>>>it doesn't help!)
>>>>
>>>>But hiding a row doesn't force calculation (until you get to xl2003). Maybe you
>>>>can add an "application.calculate" to the routine that hides the rows???
>>>>
>>>>(In xl2003, =subtotal() has been enhanced to be able to ignore manually hidden
>>>>rows: =subtotal(109,a1:a22)
>>>>
>>>>(100 + # means to ignore the manually hidden row.)
>>>>
>>>>Peter Rooney wrote:
>>>>
>>>>>Hmm. Perhaps I should have been a little more specific with the problem.
>>>>>
>>>>>I have a range of cells containing the numbers 1-10, with 4 buttons that run
>>>>>macros to hide/unhide the rows containg the odd or even numbers.
>>>>>
>>>>>It's when I run these macros to hide 1/3/5/7/8 (displaying 2/4/6/8/10) or
>>>>>hide 2/4/6/8/10 (displaying 1/3/5/7/9) that the function doesn't recalculate.
>>>>>
>>>>>There is a SumVisibleRows function too that does the opposite of
>>>>>SumHiddenRows, displaying a total for all the visible cells in the range thus:
>>>>>
>>>>>Function SumVisibleRows(TheVisibleRange)
>>>>> Dim VisibleTotal As Long
>>>>> Dim VisibleCell As Range
>>>>>
>>>>> VisibleTotal = 0
>>>>> For Each VisibleCell In TheVisibleRange
>>>>> If VisibleCell.EntireRow.Hidden = False Then
>>>>> VisibleTotal = VisibleTotal + VisibleCell.Value
>>>>> End If
>>>>> Next
>>>>> SumVisibleRows = VisibleTotal
>>>>> Calculate
>>>>>End Function
>>>>>
>>>>>If I change the value of a visible cell in the list, SumVisibleCells
>>>>>correctly calculates the new value without F2, but neither formula calculates
>>>>>new totals on its own when the hide/unhide macros are run.
>>>>>
>>>>>Should this be some sort of Worksheet_Change macro instead, and if so, can
>>>>>anyone advise on the syntax for picking up changes to the hidden property of
>>>>>a row?
>>>>>
>>>>>Thanks again
>>>>>
>>>>>Pete
>>>>>
>>>>>"Peter Rooney" wrote:
>>>>>
>>>>>
>>>>>>Good morning all!
>>>>>>
>>>>>>I wrote a simple function to calculate the values of all hidden cells in a
>>>>>>range.
>>>>>>It works fine, except that I have to edit the formula with [F2] and [Enter]
>>>>>>before it displays the correct answer - otherwise, it just displays the
>>>>>>result of the previous calculation.
>>>>>>
>>>>>>Can anyone help, please?
>>>>>>
>>>>>>Thanks in advance
>>>>>>
>>>>>>Pete
>>>>>>
>>>>>>Function SumHiddenRows(TheHiddenRange)
>>>>>> Dim HiddenTotal As Long
>>>>>> Dim HiddenCell As Range
>>>>>>
>>>>>> HiddenTotal = 0
>>>>>> For Each HiddenCell In TheHiddenRange
>>>>>> If HiddenCell.EntireRow.Hidden = True Then
>>>>>> HiddenTotal = HiddenTotal + HiddenCell.Value
>>>>>> End If
>>>>>> Next
>>>>>> SumHiddenRows = HiddenTotal
>>>>>> Calculate
>>>>>>End Function
>>>>>>
>>>>
>>>>--
>>>>
>>>>Dave Peterson
>>>>
>>
>>--
>>
>>Dave Peterson
>>

I only run xl2003, so I can't test in xl2k or below.

But I don't recall any problems with using application.calculate in a sub (not a
function called from a worksheet cell).

(But that doesn't help you.)

Peter Rooney wrote:
>
> Dave,
>
> I did and it doesn't work - whether you put the calculate command in the
> function or the hide/unhide macros. It doesn't even work if you press F9 to
> recalculate manually. You have to F2/Enter the cells containing the functions
> (or, in VBA, re-enter the formulae)
>
> Puzzling, isn't it?
>
> Pete
>
> "Dave Peterson" wrote:
>
> > I saw that in your other post.
> >
> > I think I would have just recalculated in the subroutine that hid/unhid the
> > rows.
> >
> >
> > Peter Rooney wrote:
> > >
> > > Dave,
> > >
> > > I modified my hide/unhide formulae to re-enter the custom functions into
> > > their appropriate cells and it works OK. Can't wait for my Office XP upgrade!
> > > :-)
> > >
> > > Thanks
> > >
> > > Pete
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > You could add:
> > > >
> > > > application.volatile
> > > >
> > > > At the top of your sub, but that just means that the function will calculate the
> > > > next time excel calculates. (and I'd remove the calculate from your UDF--I bet
> > > > it doesn't help!)
> > > >
> > > > But hiding a row doesn't force calculation (until you get to xl2003). Maybe you
> > > > can add an "application.calculate" to the routine that hides the rows???
> > > >
> > > > (In xl2003, =subtotal() has been enhanced to be able to ignore manually hidden
> > > > rows: =subtotal(109,a1:a22)
> > > >
> > > > (100 + # means to ignore the manually hidden row.)
> > > >
> > > > Peter Rooney wrote:
> > > > >
> > > > > Hmm. Perhaps I should have been a little more specific with the problem.
> > > > >
> > > > > I have a range of cells containing the numbers 1-10, with 4 buttons that run
> > > > > macros to hide/unhide the rows containg the odd or even numbers.
> > > > >
> > > > > It's when I run these macros to hide 1/3/5/7/8 (displaying 2/4/6/8/10) or
> > > > > hide 2/4/6/8/10 (displaying 1/3/5/7/9) that the function doesn't recalculate.
> > > > >
> > > > > There is a SumVisibleRows function too that does the opposite of
> > > > > SumHiddenRows, displaying a total for all the visible cells in the range thus:
> > > > >
> > > > > Function SumVisibleRows(TheVisibleRange)
> > > > > Dim VisibleTotal As Long
> > > > > Dim VisibleCell As Range
> > > > >
> > > > > VisibleTotal = 0
> > > > > For Each VisibleCell In TheVisibleRange
> > > > > If VisibleCell.EntireRow.Hidden = False Then
> > > > > VisibleTotal = VisibleTotal + VisibleCell.Value
> > > > > End If
> > > > > Next
> > > > > SumVisibleRows = VisibleTotal
> > > > > Calculate
> > > > > End Function
> > > > >
> > > > > If I change the value of a visible cell in the list, SumVisibleCells
> > > > > correctly calculates the new value without F2, but neither formula calculates
> > > > > new totals on its own when the hide/unhide macros are run.
> > > > >
> > > > > Should this be some sort of Worksheet_Change macro instead, and if so, can
> > > > > anyone advise on the syntax for picking up changes to the hidden property of
> > > > > a row?
> > > > >
> > > > > Thanks again
> > > > >
> > > > > Pete
> > > > >
> > > > > "Peter Rooney" wrote:
> > > > >
> > > > > > Good morning all!
> > > > > >
> > > > > > I wrote a simple function to calculate the values of all hidden cells in a
> > > > > > range.
> > > > > > It works fine, except that I have to edit the formula with [F2] and [Enter]
> > > > > > before it displays the correct answer - otherwise, it just displays the
> > > > > > result of the previous calculation.
> > > > > >
> > > > > > Can anyone help, please?
> > > > > >
> > > > > > Thanks in advance
> > > > > >
> > > > > > Pete
> > > > > >
> > > > > > Function SumHiddenRows(TheHiddenRange)
> > > > > > Dim HiddenTotal As Long
> > > > > > Dim HiddenCell As Range
> > > > > >
> > > > > > HiddenTotal = 0
> > > > > > For Each HiddenCell In TheHiddenRange
> > > > > > If HiddenCell.EntireRow.Hidden = True Then
> > > > > > HiddenTotal = HiddenTotal + HiddenCell.Value
> > > > > > End If
> > > > > > Next
> > > > > > SumHiddenRows = HiddenTotal
> > > > > > Calculate
> > > > > > End Function
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Hi,
i wrote some code below.
a formula in a conditional format seems to be called when a cell
is repainted.
my idea is that making a volatile formula like
=SumHiddenRows(A1:A10)+NOW()*0
and setting an UDF in a format condition in the hidden/unhidden cells
(like '=CatchPaint()' in A1:A10), the hidden cell calls the UDF,
the UDF starts timer, the timer procedure calculates formulas.
i am not sure that this would work perfectly.
(i am testing this in excel 2000)

'Module1
Private Declare Function SetTimer Lib "user32" _
(ByVal hWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Private Declare Function KillTimer Lib "user32.dll" _
(ByVal hWnd As Long, ByVal uIDEvent As Long) As Long

Private TimerId As Long

Private Function TimerProc(ByVal hWnd As Long, ByVal uMsg As Long, _
ByVal idEvent As Long, ByVal dwTime As Long) As Long

On Error Resume Next
KillTimer 0, idEvent

ActiveSheet.Calculate
'ActiveSheet.Range("A1").Calculate
'Application.Calculate 'this interferes with entering formula in xl2k

TimerId = 0
End Function

Private Function CatchPaint()
If TimerId = 0 Then TimerId = SetTimer(0, 0, 0, AddressOf TimerProc)
End Function

--
Regards,

okaizawa


Peter Rooney wrote:
> Good morning all!
>
> I wrote a simple function to calculate the values of all hidden cells in a
> range.
> It works fine, except that I have to edit the formula with [F2] and [Enter]
> before it displays the correct answer - otherwise, it just displays the
> result of the previous calculation.
>
> Can anyone help, please?
>
> Thanks in advance
>
> Pete
>
> Function SumHiddenRows(TheHiddenRange)
> Dim HiddenTotal As Long
> Dim HiddenCell As Range
>
> HiddenTotal = 0
> For Each HiddenCell In TheHiddenRange
> If HiddenCell.EntireRow.Hidden = True Then
> HiddenTotal = HiddenTotal + HiddenCell.Value
> End If
> Next
> SumHiddenRows = HiddenTotal
> Calculate
> End Function
>

okaizawa wrote:
> Hi,
> i wrote some code below.
> a formula in a conditional format seems to be called when a cell
> is repainted.
> my idea is that making a volatile formula like
> =SumHiddenRows(A1:A10)+NOW()*0
> and setting an UDF in a format condition in the hidden/unhidden cells
> (like '=CatchPaint()' in A1:A10), the hidden cell calls the UDF,
> the UDF starts timer, the timer procedure calculates formulas.
> i am not sure that this would work perfectly.
> (i am testing this in excel 2000)
>
> 'Module1
> Private Declare Function SetTimer Lib "user32" _
> (ByVal hWnd As Long, ByVal nIDEvent As Long, _
> ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
> Private Declare Function KillTimer Lib "user32.dll" _
> (ByVal hWnd As Long, ByVal uIDEvent As Long) As Long
>
> Private TimerId As Long
>
> Private Function TimerProc(ByVal hWnd As Long, ByVal uMsg As Long, _
> ByVal idEvent As Long, ByVal dwTime As Long) As Long
>
> On Error Resume Next
> KillTimer 0, idEvent
>
> ActiveSheet.Calculate
> 'ActiveSheet.Range("A1").Calculate
> 'Application.Calculate 'this interferes with entering formula in xl2k
>
> TimerId = 0
> End Function
>
> Private Function CatchPaint()
> If TimerId = 0 Then TimerId = SetTimer(0, 0, 0, AddressOf TimerProc)
> End Function
>
This won't work i'm afraid, the calculate function won't calculate the
relevant cell because excel 2000 and lower doesn't refresh its
calculation tree on hide/unhide events.