VBA Tip: Using Variables in Control Names

Being able to manipulate a control based on a variable can be very useful for more efficient development.

To replace the control name with a variable, use Controls.

For example, to remove the contents of 18 TextBox, you can write:

Private Sub CommandButton_clear_Click()
    
    TextBox1.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox5.Value = ""
    TextBox6.Value = ""
    TextBox7.Value = ""
    TextBox8.Value = ""
    TextBox9.Value = ""
    TextBox10.Value = ""
    TextBox11.Value = ""
    TextBox12.Value = ""
    TextBox13.Value = ""
    TextBox14.Value = ""
    TextBox15.Value = ""
    TextBox16.Value = ""
    TextBox17.Value = ""
    TextBox18.Value = ""
    
End Sub

But thanks to Controls, we can reduce the code to 3 lines:

Private Sub CommandButton_clear_Click()
    
    For i = 1 To 18
        Controls("TextBox" & i).Value = ""
    Next
    
End Sub