Macro Freezeing Excel

[patsfan]

Hey guys,

I was trying out this VBA code I received from one of my "excel tips" emails and although it works, excel freezes up immediately after.

I am running Excel 2010 and the test file is saved as a 2003 workbook.

The code will change any text entered within the target range to uppercase.

I ran it a few times and it froze after each. (I guess I was expecting different results each time)

Any idea what could have caused the excel freeze?

Here is the code

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Range("P3:P10")) Is Nothing) Then
With Target
If Not .HasFormula Then
.Value = UCase(.Value)
End If
End With
End If
End Sub

[/pre]

 

[Marc L]

LOL !

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Not Application.Intersect(Target, Range("P3:P10")) Is Nothing Then
With Target
If Not .HasFormula Then .Value = UCase(.Value)
End With
End If

Application.EnableEvents = True
End Sub

[/pre]

 

[patsfan]

Thanks Marc, now it works.

What was the cause and how did the enableevents correct this?

 

[Luke M]

This type of macro is called an event macro. Note that this particular one is set to go off whenever a change is made. However, the macro itself is making a change! So, the macro starts, makes a change, and WOOPS! Calls the macro again...makes a changes...calls macro...makes a change...calls macro... and gets caught in an endless loop.


To avoid this, we turn off the EnableEvents at the beginning, and back on at the end. Note that unlike Application.ScreenUpdating, the EnableEvents will not automatically get reset after the macro runs, so it's very important to make sure you turn it back on.

 

[Marc L]

Answer is in the code !   Must see the VBA integrate help !


Worksheet_Change is an event launched when at least a cell is modified.

In your event code, you modify a cell within a range, so the same event is called, and so on, and so on, and ...

EnableEvents set to False stops the events detection, so no more NES ...

(1)


But try to see what could happen if you modify several cells once within the range ?

(For example select several cells and click on delete key)


Due to the conception of your code, certainly an error will occur.

In fact, Target could be not only a cell but a range of cells.

So Target must be analysed cell by cell ...


[code](1) Never Ending Story !

Edit : didn't see the Luke's answer during writing mine ...[/code]

 

[patsfan]

Luke,

Thanks for taking the time to explain this to me.


Now I'm trying to figure out why Marc began his reply with LOL!

1)He thinks it's funny I receive excel tips via email

2)He's laughing because it was a no-brainer fix.

3)Because I'm one of those idiots who will repeat the same process over and over and expect different results.

4)Other

I'm hoping it's #3

Thanks Marc, I didn't see your reply before posting mine.

 

[bobhc]

#3 with a pinch of #2

 

[Luke M]

Expanding on #2, I'd say it's also because it's something that all of has has done (or will do) at one point or another. Just a common thing that happens when people are learning VB.


I suppose it's also a little like the short film "Sorcerer's apprentice" where Mickey starts a process of the broom getting water and starting to clean. If we think of this as a macro, he started a process, but didn't clearly define when to stop, and things quickly got out of hand! =P


Quick tip, whenever you accidentally fire off a macro that gets caught in an inifinite loop, you can use Ctrl+Alt+Pause to pause the macro. Can save you from having to force XL to close.

 

[SirJB7]

Hi, patsfan!


It's a good practice to insert next 1st. line at the beginning of the code, 2nd. one at the end of a cycle that might never end, and optionally 3rd. one before exiting.


Application.EnableCancelKey = xlInterrupt

DoEvents

Application.EnableCancelKey = xlDisabled


Regards!