Visual Basic for Applications/Avoiding Change Event Recursion
Summary
edit- This VBA code is intended to run in Microsoft Office applications that can run macros, like Excel or Word.
- It provides two different examples of a text box change event, one for TextBox1 and another for TextBox2.
- It will be noted that on displaying the form and entering one character into TextBox1, the resulting number found there is about 290 or so; this is coded to show the number of iterations of the change event that have taken place.
- Doing the same in TextBox2 shows that there has been just one run of the event.
- The code of the TextBox2_Change event avoids multiple runs of the procedure, avoiding the possibility of false results in certain circumstances.
The VBA Code
editCode Changes
editFor the ThisWorkbook Module
edit'...............................................
' Notes: Code needs a user form named UserForm1,
' with two text boxes, TextBox1 and Textbox2,
'...............................................
Private Sub Workbook_Open()
'Runs on opening the workbook
Load UserForm1
UserForm1.Show
End Sub
For the UserForm1 Module
editPrivate Sub TextBox1_Change()
' This Change event runs about 294 times
' for each character entered
Static nC As Long
'yield to commands-just in case
DoEvents
'increment for each iteration
nC = nC + 1
'this line causes this procedure to run again
TextBox1.Value = nC
End Sub
Private Sub TextBox2_Change()
' This Change event runs only once
' for each character entered
Static nC As Long
Static bEnableEvents As Boolean
'yield to commands-just in case
DoEvents
' increment for each iteration
nC = nC + 1
' false to start then true after that
If bEnableEvents = True Then
Exit Sub
End If
bEnableEvents = True
' this runs only once
TextBox2.Value = nC
' reset flag
bEnableEvents = False
End Sub