Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Calculate Event
I am using this if statement in 5 sheets calculate event:
Private Sub Worksheet_Calculate() If Range("IdleG").Value < 0 Then blnGFlag = True 'change this if you add one to go back positive Range("NoOfG").Value = Range("NoOfG").Value + 1 Else 'handles if you are greater than zero and not just moved above it 'avoid div by 0 If Range("IdleG") 0 And blnGFlag < True And Range("NoOfG") < 1 Then Range("NoOfG").Value = Range("NoOfG").Value - 1 End If End If End Sub The blnNFlag is a global variable declared in a code module. What I am attempting to do here is test if Idle time is positive remove one more processor and check if it is still positive, until it goes negative. Then I add one back on and throw a flag that tells me not to subract any more, thus determining how many processors are needed. That works, THE PROBLEM IS resetting my blnNFlag after the five differnt sheets calculate event have been performed. Every time I add or subtract one processor the event calculate event is called again, this is acting as a do while loop for me. So it has to run through each sheets event many times, and If I reset the blnNFlag before the last itereation, the second if test: If Range("IdleG") 0 And blnGFlag < True And Range("NoOfG") < 1 Then will be entered and an endless loop is created. Is there some after calculate event? Or some ideas on how to reset my flags? I was thinking maybe the worksheet calculated event? If I could then control how many time the flags are reset? Or if I could reset the flags before each calcualte, so a before_calculate event that would work also? Thanks for your time, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Calculate Event
To create a psuedo after calculate event I just used a global variable to
count the number of if loops I entered and another variable to count the number of if loops I exiteded. At the end of each calculate event I tested to see if they were the same number (all iterations finsihed) and if so called my module. This worked really well and could be useful to others. Greg "Greg P" wrote: I am using this if statement in 5 sheets calculate event: Private Sub Worksheet_Calculate() If Range("IdleG").Value < 0 Then blnGFlag = True 'change this if you add one to go back positive Range("NoOfG").Value = Range("NoOfG").Value + 1 Else 'handles if you are greater than zero and not just moved above it 'avoid div by 0 If Range("IdleG") 0 And blnGFlag < True And Range("NoOfG") < 1 Then Range("NoOfG").Value = Range("NoOfG").Value - 1 End If End If End Sub The blnNFlag is a global variable declared in a code module. What I am attempting to do here is test if Idle time is positive remove one more processor and check if it is still positive, until it goes negative. Then I add one back on and throw a flag that tells me not to subract any more, thus determining how many processors are needed. That works, THE PROBLEM IS resetting my blnNFlag after the five differnt sheets calculate event have been performed. Every time I add or subtract one processor the event calculate event is called again, this is acting as a do while loop for me. So it has to run through each sheets event many times, and If I reset the blnNFlag before the last itereation, the second if test: If Range("IdleG") 0 And blnGFlag < True And Range("NoOfG") < 1 Then will be entered and an endless loop is created. Is there some after calculate event? Or some ideas on how to reset my flags? I was thinking maybe the worksheet calculated event? If I could then control how many time the flags are reset? Or if I could reset the flags before each calcualte, so a before_calculate event that would work also? Thanks for your time, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Calculate Event
Hi Greg,
Thanks for your quickly reply! It seems that you have found the workaround, If I misunderstood, please let me know. BTW: I think you may try to disable the event temporarily when you did not want the event fire. Sub Test() Application.EnableEvents = False Range("D1").Text = "Test" Application.EnableEvents = True End Sub Best regards, Peter Huang Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate working days but change working week | Excel Discussion (Misc queries) | |||
Calculate Event | Excel Programming | |||
Change event and calculate event | Excel Programming | |||
AFTER Calculate Event | Excel Programming |