Execution very slow

[jimmy]

Hi all,
The function:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("BJ1") <= Range("BI1") And Range("BK1") = 0 Then
MsgBox (Range("BH1").Value & " reach the minimum level")
Range("BK1") = 1
End If

If Range("BJ1") > Range("BI1") Then Range("BK1") = 0

End Sub

BH1 is pointed to a cell contains text, BI1 and BJ1 are pointed to different
cells which do simple +/-. The above function is just for testing, there
will be about 50 items that I will use array and for loop if the testing is
ok.
To prevent the message prompt so many times, I have set the flag(BK1) keep
on 1 until "BJ1" > "BI1" again. But I don't know why everytime a increment
in BJ1, the application is hang for about 10 seconds. Please help to point
out my mistake, thank you.

 

[John Coleman]

Maybe you can try Application.EnableEvents = False as the first line
in the sub and
Application.EnableEvents = True as the last.

 

[KL]

How about something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("BJ1") <= Range("BI1") And Range("BK1") = 0 Then
MsgBox (Range("BH1").Value & " reach the minimum level")
Range("BK1") = 1
ElseIf Range("BJ1") > Range("BI1") Then
Range("BK1") = 0
End If
Application.EnableEvents = True
End Sub

also, you may want to introduce a condition for the code depending on the range that is changed. For example: if the range that is
going to affect the result is [A110] you could do something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A110")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Range("BJ1") <= Range("BI1") And Range("BK1") = 0 Then
MsgBox (Range("BH1").Value & " reach the minimum level")
Range("BK1") = 1
ElseIf Range("BJ1") > Range("BI1") Then
Range("BK1") = 0
End If
Application.EnableEvents = True
End Sub

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

 

[jimmy]

Thank you very much. No problem now ^^
Actually, what is Application.EnableEvents = False and True means?
Why Application.EnableEvents = False can still take effect to the Change
event?

How about something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("BJ1") <= Range("BI1") And Range("BK1") = 0 Then
MsgBox (Range("BH1").Value & " reach the minimum level")
Range("BK1") = 1
ElseIf Range("BJ1") > Range("BI1") Then
Range("BK1") = 0
End If
Application.EnableEvents = True
End Sub

also, you may want to introduce a condition for the code depending on the
range that is changed. For example: if the range that is going to affect
the result is [A110] you could do something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A110")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Range("BJ1") <= Range("BI1") And Range("BK1") = 0 Then
MsgBox (Range("BH1").Value & " reach the minimum level")
Range("BK1") = 1
ElseIf Range("BJ1") > Range("BI1") Then
Range("BK1") = 0
End If
Application.EnableEvents = True
End Sub

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile:
https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

Hi all,
The function:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("BJ1") <= Range("BI1") And Range("BK1") = 0 Then
MsgBox (Range("BH1").Value & " reach the minimum level")
Range("BK1") = 1
End If

If Range("BJ1") > Range("BI1") Then Range("BK1") = 0

End Sub

BH1 is pointed to a cell contains text, BI1 and BJ1 are pointed to
different cells which do simple +/-. The above function is just for
testing, there will be about 50 items that I will use array and for loop
if the testing is ok.
To prevent the message prompt so many times, I have set the flag(BK1)
keep on 1 until "BJ1" > "BI1" again. But I don't know why everytime a
increment in BJ1, the application is hang for about 10 seconds. Please
help to point out my mistake, thank you.

 

[John Coleman]

The problem is that if you change a cell during a change event, it in
turn will invoke the change event handler and thus possibly create a
cascade of function calls - unless you turn events off during the
initial invocation.

As an experiment try (in a new workbook):

Sub pause(secs As Double)
Dim start As Double
start = Timer
Do While Timer < start + secs
DoEvents
Loop
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
pause 0.01
Target.Offset(1).Value = Target.Value + 1
pause 0.01
Target.Interior.ColorIndex = 10
End Sub

the in A1 (say) just enter 1 and sit back and watch for about 20-30
seconds.

When I run this in Excel 2003 it seems to result in a cascade of 225
function calls (at which time I guess some internal "event stack"
overflows). In the worst case scenario, something like this would
result in over 60,000 calls before crashing - so experiment at your
own risk.

Thank you very much. No problem now ^^
Actually, what is Application.EnableEvents = False and True means?
Why Application.EnableEvents = False can still take effect to the Change
event?

"KL" <[email protected]>
???????:[email protected]...

How about something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("BJ1") <= Range("BI1") And Range("BK1") = 0 Then
MsgBox (Range("BH1").Value & " reach the minimum level")
Range("BK1") = 1
ElseIf Range("BJ1") > Range("BI1") Then
Range("BK1") = 0
End If
Application.EnableEvents = True
End Sub

also, you may want to introduce a condition for the code depending on the
range that is changed. For example: if the range that is going to affect
the result is [A110] you could do something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A110")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Range("BJ1") <= Range("BI1") And Range("BK1") = 0 Then
MsgBox (Range("BH1").Value & " reach the minimum level")
Range("BK1") = 1
ElseIf Range("BJ1") > Range("BI1") Then
Range("BK1") = 0
End If
Application.EnableEvents = True
End Sub

--
KL
[MVP - Microsoft Excel]
RU:http://www.mvps.ru/Program/Default.aspx
ES:http://mvp.support.microsoft.com/?LN=es-es
EN:http://mvp.support.microsoft.com/?LN=en-us
Profile:
https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6...

- Show quoted text -