Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Screen "Flickers" When macro Starts !!
Hello;
Sorry to bring up this old problem, but suddenly the screen flickers when I click the button to run a relatively simple macro. The Application.ScreenUpdating = False does no longer have the effect of suppressing this initial jiggle!! (I'm assuming that ScreenUpdating defaults back to True when the macro terminates its execution.) There're NO timers, NO event procedures, and NO other macros in this wb. Could someone please shed some light on the possible cause ?? and how to fix it ? Here's the simple macro code which works fine apart from the flickering of the screen when the macro starts. Sub Spiral_Solver_4() Dim myCheck As VbMsgBoxResult 'minimize the amount of flashing you see on the screen Application.ScreenUpdating = False ' initialize values Range("B13") = Range("B14") Range("C13") = Range("C14") Range("D17") = Range("A17") Range("D18") = Range("A18") Range("D19") = Range("E19") Range("J16") = Range("H16") Range("F13") = Range("F14") SolverLoad LoadArea:="$J$20:$J$27" SolverOptions MaxTime:=1000, Iterations:=10000, Precision:=0.0001, _ AssumeLinear:=False, StepThru:=False, Estimates:=2, Derivatives:=2, _ SearchOption:=2, IntTolerance:=5, Scaling:=False, Convergence:=0.0001, _ AssumeNonNeg:=False SolverOk SetCell:="$H$10", MaxMinVal:=3, ValueOf:="0", _ ByChange:="$D$17,$D$18,$D$19,$J$16" 'check if Solver failed Test = SolverSolve(True) MsgBox (Test) 'returns 0 or 1 for NO error myCheck = MsgBox("0 or 1 OK." & Chr(10) & "Continue ??", vbYesNo) If myCheck = vbNo Then Exit Sub End If 'continue with Solver if successful SolverSolve Userfinish:=True Solverfinish keepfinal = 1 Range("A1").Select End Sub Thank you kindly. (Excel 2003 SP2, Windows XP) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Screen "Flickers" When macro Starts !!
There are some functions (in the Analysis ToolPak, IIRC) that turn on
screenupdating. Maybe your code that uses solver suffers the same problem. I don't think there's a way you can change the behavior of these functions, but you can turn screenupdating off as soon as it gets turned back on. I'd add some of these debug.print statements thoughout the code: Debug.print "step 001." & application.screenupdating You'll be able to see what piece of code changes that setting and your code can react to it. monir wrote: Hello; Sorry to bring up this old problem, but suddenly the screen flickers when I click the button to run a relatively simple macro. The Application.ScreenUpdating = False does no longer have the effect of suppressing this initial jiggle!! (I'm assuming that ScreenUpdating defaults back to True when the macro terminates its execution.) There're NO timers, NO event procedures, and NO other macros in this wb. Could someone please shed some light on the possible cause ?? and how to fix it ? Here's the simple macro code which works fine apart from the flickering of the screen when the macro starts. Sub Spiral_Solver_4() Dim myCheck As VbMsgBoxResult 'minimize the amount of flashing you see on the screen Application.ScreenUpdating = False ' initialize values Range("B13") = Range("B14") Range("C13") = Range("C14") Range("D17") = Range("A17") Range("D18") = Range("A18") Range("D19") = Range("E19") Range("J16") = Range("H16") Range("F13") = Range("F14") SolverLoad LoadArea:="$J$20:$J$27" SolverOptions MaxTime:=1000, Iterations:=10000, Precision:=0.0001, _ AssumeLinear:=False, StepThru:=False, Estimates:=2, Derivatives:=2, _ SearchOption:=2, IntTolerance:=5, Scaling:=False, Convergence:=0.0001, _ AssumeNonNeg:=False SolverOk SetCell:="$H$10", MaxMinVal:=3, ValueOf:="0", _ ByChange:="$D$17,$D$18,$D$19,$J$16" 'check if Solver failed Test = SolverSolve(True) MsgBox (Test) 'returns 0 or 1 for NO error myCheck = MsgBox("0 or 1 OK." & Chr(10) & "Continue ??", vbYesNo) If myCheck = vbNo Then Exit Sub End If 'continue with Solver if successful SolverSolve Userfinish:=True Solverfinish keepfinal = 1 Range("A1").Select End Sub Thank you kindly. (Excel 2003 SP2, Windows XP) -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Screen "Flickers" When macro Starts !!
Dave;
Thank you and will follow your advice by inserting some debug print statements. In the meantime, here's some additional info which might or might not be related to my OP: 1) I've noticed that when I open the same w/b, "calculate" appears on the task bar although there're NO timers, NO event procedures, NO circ ref, NO other macros, and NO links! 2) "calculate" would disappear if I enter a value in any empty cell on any w/s, or change the text in any cell, or even press F9, etc. despite Tools::Options::Calculation::Automatic !! 3) What does "calculate" indicate ?? Why does it show now on the task bar, though there's nothing to calculate ?? How to identify the cell(s) that are "responsible" for this "calculate" ?? 4) Could "calculate" be somehow related to the screen "flickering" ?? Probably not, since the screen still jiggles at the start of the macro even after "calculate" disappears following 2) above! Thank you. (Excel 2003 SP2, Windows XP) "Dave Peterson" wrote: There are some functions (in the Analysis ToolPak, IIRC) that turn on screenupdating. Maybe your code that uses solver suffers the same problem. I don't think there's a way you can change the behavior of these functions, but you can turn screenupdating off as soon as it gets turned back on. I'd add some of these debug.print statements thoughout the code: Debug.print "step 001." & application.screenupdating You'll be able to see what piece of code changes that setting and your code can react to it. monir wrote: Hello; Sorry to bring up this old problem, but suddenly the screen flickers when I click the button to run a relatively simple macro. The Application.ScreenUpdating = False does no longer have the effect of suppressing this initial jiggle!! (I'm assuming that ScreenUpdating defaults back to True when the macro terminates its execution.) There're NO timers, NO event procedures, and NO other macros in this wb. Could someone please shed some light on the possible cause ?? and how to fix it ? Here's the simple macro code which works fine apart from the flickering of the screen when the macro starts. Sub Spiral_Solver_4() Dim myCheck As VbMsgBoxResult 'minimize the amount of flashing you see on the screen Application.ScreenUpdating = False ' initialize values Range("B13") = Range("B14") Range("C13") = Range("C14") Range("D17") = Range("A17") Range("D18") = Range("A18") Range("D19") = Range("E19") Range("J16") = Range("H16") Range("F13") = Range("F14") SolverLoad LoadArea:="$J$20:$J$27" SolverOptions MaxTime:=1000, Iterations:=10000, Precision:=0.0001, _ AssumeLinear:=False, StepThru:=False, Estimates:=2, Derivatives:=2, _ SearchOption:=2, IntTolerance:=5, Scaling:=False, Convergence:=0.0001, _ AssumeNonNeg:=False SolverOk SetCell:="$H$10", MaxMinVal:=3, ValueOf:="0", _ ByChange:="$D$17,$D$18,$D$19,$J$16" 'check if Solver failed Test = SolverSolve(True) MsgBox (Test) 'returns 0 or 1 for NO error myCheck = MsgBox("0 or 1 OK." & Chr(10) & "Continue ??", vbYesNo) If myCheck = vbNo Then Exit Sub End If 'continue with Solver if successful SolverSolve Userfinish:=True Solverfinish keepfinal = 1 Range("A1").Select End Sub Thank you kindly. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Screen "Flickers" When macro Starts !!
Dave;
Debug.Print statements showed Application.ScreenUpdating as False throughout the macro. "monir" wrote: Dave; Thank you and will follow your advice by inserting some debug print statements. In the meantime, here's some additional info which might or might not be related to my OP: 1) I've noticed that when I open the same w/b, "calculate" appears on the task bar although there're NO timers, NO event procedures, NO circ ref, NO other macros, and NO links! 2) "calculate" would disappear if I enter a value in any empty cell on any w/s, or change the text in any cell, or even press F9, etc. despite Tools::Options::Calculation::Automatic !! 3) What does "calculate" indicate ?? Why does it show now on the task bar, though there's nothing to calculate ?? How to identify the cell(s) that are "responsible" for this "calculate" ?? 4) Could "calculate" be somehow related to the screen "flickering" ?? Probably not, since the screen still jiggles at the start of the macro even after "calculate" disappears following 2) above! Thank you. (Excel 2003 SP2, Windows XP) "Dave Peterson" wrote: There are some functions (in the Analysis ToolPak, IIRC) that turn on screenupdating. Maybe your code that uses solver suffers the same problem. I don't think there's a way you can change the behavior of these functions, but you can turn screenupdating off as soon as it gets turned back on. I'd add some of these debug.print statements thoughout the code: Debug.print "step 001." & application.screenupdating You'll be able to see what piece of code changes that setting and your code can react to it. monir wrote: Hello; Sorry to bring up this old problem, but suddenly the screen flickers when I click the button to run a relatively simple macro. The Application.ScreenUpdating = False does no longer have the effect of suppressing this initial jiggle!! (I'm assuming that ScreenUpdating defaults back to True when the macro terminates its execution.) There're NO timers, NO event procedures, and NO other macros in this wb. Could someone please shed some light on the possible cause ?? and how to fix it ? Here's the simple macro code which works fine apart from the flickering of the screen when the macro starts. Sub Spiral_Solver_4() Dim myCheck As VbMsgBoxResult 'minimize the amount of flashing you see on the screen Application.ScreenUpdating = False ' initialize values Range("B13") = Range("B14") Range("C13") = Range("C14") Range("D17") = Range("A17") Range("D18") = Range("A18") Range("D19") = Range("E19") Range("J16") = Range("H16") Range("F13") = Range("F14") SolverLoad LoadArea:="$J$20:$J$27" SolverOptions MaxTime:=1000, Iterations:=10000, Precision:=0.0001, _ AssumeLinear:=False, StepThru:=False, Estimates:=2, Derivatives:=2, _ SearchOption:=2, IntTolerance:=5, Scaling:=False, Convergence:=0.0001, _ AssumeNonNeg:=False SolverOk SetCell:="$H$10", MaxMinVal:=3, ValueOf:="0", _ ByChange:="$D$17,$D$18,$D$19,$J$16" 'check if Solver failed Test = SolverSolve(True) MsgBox (Test) 'returns 0 or 1 for NO error myCheck = MsgBox("0 or 1 OK." & Chr(10) & "Continue ??", vbYesNo) If myCheck = vbNo Then Exit Sub End If 'continue with Solver if successful SolverSolve Userfinish:=True Solverfinish keepfinal = 1 Range("A1").Select End Sub Thank you kindly. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Screen "Flickers" When macro Starts !!
Calculate can appear in the statusbar when there are lots of formulas.
http://support.microsoft.com/default.aspx?scid=243495 XL: Calculate Message Remains in Status Bar If 65,536 Formula References But I've never seen that kind of thing interfere with .screenupdating. monir wrote: Dave; Thank you and will follow your advice by inserting some debug print statements. In the meantime, here's some additional info which might or might not be related to my OP: 1) I've noticed that when I open the same w/b, "calculate" appears on the task bar although there're NO timers, NO event procedures, NO circ ref, NO other macros, and NO links! 2) "calculate" would disappear if I enter a value in any empty cell on any w/s, or change the text in any cell, or even press F9, etc. despite Tools::Options::Calculation::Automatic !! 3) What does "calculate" indicate ?? Why does it show now on the task bar, though there's nothing to calculate ?? How to identify the cell(s) that are "responsible" for this "calculate" ?? 4) Could "calculate" be somehow related to the screen "flickering" ?? Probably not, since the screen still jiggles at the start of the macro even after "calculate" disappears following 2) above! Thank you. (Excel 2003 SP2, Windows XP) "Dave Peterson" wrote: There are some functions (in the Analysis ToolPak, IIRC) that turn on screenupdating. Maybe your code that uses solver suffers the same problem. I don't think there's a way you can change the behavior of these functions, but you can turn screenupdating off as soon as it gets turned back on. I'd add some of these debug.print statements thoughout the code: Debug.print "step 001." & application.screenupdating You'll be able to see what piece of code changes that setting and your code can react to it. monir wrote: Hello; Sorry to bring up this old problem, but suddenly the screen flickers when I click the button to run a relatively simple macro. The Application.ScreenUpdating = False does no longer have the effect of suppressing this initial jiggle!! (I'm assuming that ScreenUpdating defaults back to True when the macro terminates its execution.) There're NO timers, NO event procedures, and NO other macros in this wb. Could someone please shed some light on the possible cause ?? and how to fix it ? Here's the simple macro code which works fine apart from the flickering of the screen when the macro starts. Sub Spiral_Solver_4() Dim myCheck As VbMsgBoxResult 'minimize the amount of flashing you see on the screen Application.ScreenUpdating = False ' initialize values Range("B13") = Range("B14") Range("C13") = Range("C14") Range("D17") = Range("A17") Range("D18") = Range("A18") Range("D19") = Range("E19") Range("J16") = Range("H16") Range("F13") = Range("F14") SolverLoad LoadArea:="$J$20:$J$27" SolverOptions MaxTime:=1000, Iterations:=10000, Precision:=0.0001, _ AssumeLinear:=False, StepThru:=False, Estimates:=2, Derivatives:=2, _ SearchOption:=2, IntTolerance:=5, Scaling:=False, Convergence:=0.0001, _ AssumeNonNeg:=False SolverOk SetCell:="$H$10", MaxMinVal:=3, ValueOf:="0", _ ByChange:="$D$17,$D$18,$D$19,$J$16" 'check if Solver failed Test = SolverSolve(True) MsgBox (Test) 'returns 0 or 1 for NO error myCheck = MsgBox("0 or 1 OK." & Chr(10) & "Continue ??", vbYesNo) If myCheck = vbNo Then Exit Sub End If 'continue with Solver if successful SolverSolve Userfinish:=True Solverfinish keepfinal = 1 Range("A1").Select End Sub Thank you kindly. -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Screen "Flickers" When macro Starts !!
I don't have another guess...
Sorry. monir wrote: Dave; Debug.Print statements showed Application.ScreenUpdating as False throughout the macro. "monir" wrote: Dave; Thank you and will follow your advice by inserting some debug print statements. In the meantime, here's some additional info which might or might not be related to my OP: 1) I've noticed that when I open the same w/b, "calculate" appears on the task bar although there're NO timers, NO event procedures, NO circ ref, NO other macros, and NO links! 2) "calculate" would disappear if I enter a value in any empty cell on any w/s, or change the text in any cell, or even press F9, etc. despite Tools::Options::Calculation::Automatic !! 3) What does "calculate" indicate ?? Why does it show now on the task bar, though there's nothing to calculate ?? How to identify the cell(s) that are "responsible" for this "calculate" ?? 4) Could "calculate" be somehow related to the screen "flickering" ?? Probably not, since the screen still jiggles at the start of the macro even after "calculate" disappears following 2) above! Thank you. (Excel 2003 SP2, Windows XP) "Dave Peterson" wrote: There are some functions (in the Analysis ToolPak, IIRC) that turn on screenupdating. Maybe your code that uses solver suffers the same problem. I don't think there's a way you can change the behavior of these functions, but you can turn screenupdating off as soon as it gets turned back on. I'd add some of these debug.print statements thoughout the code: Debug.print "step 001." & application.screenupdating You'll be able to see what piece of code changes that setting and your code can react to it. monir wrote: Hello; Sorry to bring up this old problem, but suddenly the screen flickers when I click the button to run a relatively simple macro. The Application.ScreenUpdating = False does no longer have the effect of suppressing this initial jiggle!! (I'm assuming that ScreenUpdating defaults back to True when the macro terminates its execution.) There're NO timers, NO event procedures, and NO other macros in this wb. Could someone please shed some light on the possible cause ?? and how to fix it ? Here's the simple macro code which works fine apart from the flickering of the screen when the macro starts. Sub Spiral_Solver_4() Dim myCheck As VbMsgBoxResult 'minimize the amount of flashing you see on the screen Application.ScreenUpdating = False ' initialize values Range("B13") = Range("B14") Range("C13") = Range("C14") Range("D17") = Range("A17") Range("D18") = Range("A18") Range("D19") = Range("E19") Range("J16") = Range("H16") Range("F13") = Range("F14") SolverLoad LoadArea:="$J$20:$J$27" SolverOptions MaxTime:=1000, Iterations:=10000, Precision:=0.0001, _ AssumeLinear:=False, StepThru:=False, Estimates:=2, Derivatives:=2, _ SearchOption:=2, IntTolerance:=5, Scaling:=False, Convergence:=0.0001, _ AssumeNonNeg:=False SolverOk SetCell:="$H$10", MaxMinVal:=3, ValueOf:="0", _ ByChange:="$D$17,$D$18,$D$19,$J$16" 'check if Solver failed Test = SolverSolve(True) MsgBox (Test) 'returns 0 or 1 for NO error myCheck = MsgBox("0 or 1 OK." & Chr(10) & "Continue ??", vbYesNo) If myCheck = vbNo Then Exit Sub End If 'continue with Solver if successful SolverSolve Userfinish:=True Solverfinish keepfinal = 1 Range("A1").Select End Sub Thank you kindly. -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solution ... Screen "Flickers" When macro Starts !!
Dave;
Here's how to eliminate such irritating problem: 1. insert at the start of the VBA macro code: .......Application.ScreenUpdating = False and counter it before End Sub: .......Application.ScreenUpdating = True 2. name the w/s range of the saved optimizer model, e.g.: .......Range("$J$20:$J$27").Name = "SolverModelArea" 3. use the range name in the SolverLoad function: .......SolverLoad LoadArea:= SolverModelArea Problem solved! It works fine and now the macro executes very quietly! Regards. "Dave Peterson" wrote: Calculate can appear in the statusbar when there are lots of formulas. http://support.microsoft.com/default.aspx?scid=243495 XL: Calculate Message Remains in Status Bar If 65,536 Formula References But I've never seen that kind of thing interfere with .screenupdating. monir wrote: Dave; Thank you and will follow your advice by inserting some debug print statements. In the meantime, here's some additional info which might or might not be related to my OP: 1) I've noticed that when I open the same w/b, "calculate" appears on the task bar although there're NO timers, NO event procedures, NO circ ref, NO other macros, and NO links! 2) "calculate" would disappear if I enter a value in any empty cell on any w/s, or change the text in any cell, or even press F9, etc. despite Tools::Options::Calculation::Automatic !! 3) What does "calculate" indicate ?? Why does it show now on the task bar, though there's nothing to calculate ?? How to identify the cell(s) that are "responsible" for this "calculate" ?? 4) Could "calculate" be somehow related to the screen "flickering" ?? Probably not, since the screen still jiggles at the start of the macro even after "calculate" disappears following 2) above! Thank you. (Excel 2003 SP2, Windows XP) "Dave Peterson" wrote: There are some functions (in the Analysis ToolPak, IIRC) that turn on screenupdating. Maybe your code that uses solver suffers the same problem. I don't think there's a way you can change the behavior of these functions, but you can turn screenupdating off as soon as it gets turned back on. I'd add some of these debug.print statements thoughout the code: Debug.print "step 001." & application.screenupdating You'll be able to see what piece of code changes that setting and your code can react to it. monir wrote: Hello; Sorry to bring up this old problem, but suddenly the screen flickers when I click the button to run a relatively simple macro. The Application.ScreenUpdating = False does no longer have the effect of suppressing this initial jiggle!! (I'm assuming that ScreenUpdating defaults back to True when the macro terminates its execution.) There're NO timers, NO event procedures, and NO other macros in this wb. Could someone please shed some light on the possible cause ?? and how to fix it ? Here's the simple macro code which works fine apart from the flickering of the screen when the macro starts. Sub Spiral_Solver_4() Dim myCheck As VbMsgBoxResult 'minimize the amount of flashing you see on the screen Application.ScreenUpdating = False ' initialize values Range("B13") = Range("B14") Range("C13") = Range("C14") Range("D17") = Range("A17") Range("D18") = Range("A18") Range("D19") = Range("E19") Range("J16") = Range("H16") Range("F13") = Range("F14") SolverLoad LoadArea:="$J$20:$J$27" SolverOptions MaxTime:=1000, Iterations:=10000, Precision:=0.0001, _ AssumeLinear:=False, StepThru:=False, Estimates:=2, Derivatives:=2, _ SearchOption:=2, IntTolerance:=5, Scaling:=False, Convergence:=0.0001, _ AssumeNonNeg:=False SolverOk SetCell:="$H$10", MaxMinVal:=3, ValueOf:="0", _ ByChange:="$D$17,$D$18,$D$19,$J$16" 'check if Solver failed Test = SolverSolve(True) MsgBox (Test) 'returns 0 or 1 for NO error myCheck = MsgBox("0 or 1 OK." & Chr(10) & "Continue ??", vbYesNo) If myCheck = vbNo Then Exit Sub End If 'continue with Solver if successful SolverSolve Userfinish:=True Solverfinish keepfinal = 1 Range("A1").Select End Sub Thank you kindly. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro "disable or enable" screen | Excel Discussion (Misc queries) | |||
HELP! How to put an IF formula with "starts with" instead of bigger than or equal to | Excel Worksheet Functions | |||
Query starts a "read only" copy of the source sheet | Excel Programming | |||
How do I get "file" tab to click on, it starts with "edit" | Excel Discussion (Misc queries) | |||
Leftmost part of screen "blued" out when calculating via a macro | Excel Programming |