Working with Calculate Event

[Greg P]

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,

 

[Greg P]

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

 

[Peter Huang [MSFT]]

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.