Working with VBA Events
VBA can capture many "events" such as recalculation and cell changes, giving you a lot of control. Here are a couple of nice techniques.
Avoiding the re-entry problem
Suppose you want to do some validation when anything is changed. You might create a routine called Validate, and then you'll put the word Validate inside the change event of the worksheet, like so
Private Sub Worksheet_Change(ByVal Target As Range)
Validate
End Sub
Unfortunately, let's suppose your worksheet is built so that when you validate, it causes a cell on that same sheet to change, and that triggers the worksheet Change event all over again, which triggers Validate, which changes a cell, which...... and before you know it, you have a stack overflow error because of the endless looping.
What you need is a way of telling VBA not to react to the Change event if you are already busy dealing with it. So here's a suggestion.
Private Sub Worksheet_Change(ByVal Target As Range)
Static Busy As Single
If Timer < Busy Then Exit Sub
Busy = Timer + 3
Validate
Busy = 0
End Sub
The code defines a variable called Busy, and sets it to the number of seconds past midnight (that's what the Timer function tells us) plus 3 seconds, ie it sets it to 3 seconds from now. It then runs the Validate routine and sets Busy to 0 when it is finished.
If, while Validate is running, the Change event gets triggered again, the code will compare Timer with the value of Busy (Busy is defined as Static so it remembers its value between visits to the routine). If Timer is less than Busy, this means that Validate is running, and VBA exits without doing anything.
Now, I hear you cry, what's with the Timer bit? Why can't I just use a TRUE/FALSE Boolean to tell me if Validate is running, setting it to True before I run it, and False when I've finished? That's fine, but it has one problem - if for some reason, the Validate routine is interrupted and doesn't complete properly, the Busy variable could get stuck in the TRUE position and miss updating future changes.
If, however, you use the Timer method, Busy will only be bigger than Timer for 3 seconds, so even if Validate is interrupted, the Busy variable will not prevent Validate running after just 3 seconds, so it can't get stuck in the wrong position. In other words, the Busy flag will automatically time out. Neat, huh?
.
Note: Where possible, I've attributed ideas to the people who thought of them. If I have missed someone or made a mistake, please let me know.