Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions won't calculate without F2
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 (ToolsOptions, 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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions won't calculate without F2
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions won't calculate without F2
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" wrote in message ... 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 (ToolsOptions, 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions won't calculate without F2
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" wrote in message ... 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 (ToolsOptions, 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions won't calculate without F2
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" wrote in message ... 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 (ToolsOptions, 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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions won't calculate without F2
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions won't calculate without F2
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions won't calculate without F2
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions won't calculate without F2
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions won't calculate without F2
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions won't calculate without F2
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions won't calculate without F2
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions won't calculate without F2
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Functions / Drop Downs won't calculate | Excel Worksheet Functions | |||
functions do not calculate correctly | Excel Worksheet Functions | |||
Logical functions to calculate points | Excel Worksheet Functions | |||
Formula & functions intermittently fail to calculate | Excel Worksheet Functions | |||
functions do not calculate | Excel Worksheet Functions |