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 edit

Code Changes edit

For 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

End Sub

For the UserForm1 Module edit

Private Sub TextBox1_Change()
    ' This Change event runs about 294 times
    ' for each character entered
    Static nC As Long
    'yield to commands-just in case
    '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
    ' 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

See Also edit

External Links edit