Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Execution very slow
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Execution very slow
Maybe you can try Application.EnableEvents = False as the first line
in the sub and Application.EnableEvents = True as the last. On Mar 4, 3:04 pm, "jimmy" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Execution very slow
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 [A1:D10] you could do something like this: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1:D10")) 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/pr...A-9E6C73C09A36 "jimmy" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Execution very slow
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" ... 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 [A1:D10] you could do something like this: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1:D10")) 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/pr...A-9E6C73C09A36 "jimmy" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Execution very slow
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. On Mar 4, 3:47 pm, "jimmy" wrote: 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" ... 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 [A1:D10] you could do something like this: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1:D10")) 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/pr...-4AB9-ADDA-9E6... "jimmy" wrote in message ... 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.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slow code execution | Excel Programming | |||
Macro Slow In Execution Under Excel 2002 | Excel Programming | |||
Excel to Powerpoint Slow Execution | Excel Programming | |||
Extremely Slow VBA Execution Speed | Excel Programming | |||
slow macro execution | Excel Programming |