Using variables in dot notation-CodePudding

On an excel form I'd like to use variable names in the dot notation. For example

Private Sub Close_Button_Click()

Dim Var_Name As String
Var_Name = "Label1"
With Me
    With Var_Name
        .Caption = "Hello, world"
    End With
End With
End Sub

Is this possible?

FWIW the reason for wanting to this is that the form contains a significant, but variable, number of text labels and I'd rather loop through them than write a line of code per text label.

Another approach might be to place a text label on the form dynamically at run time only if the text label is required, but I don't know if this is possible either.

CodePudding user response：

If you wish to loop through the controls, and access them by index:

Private Sub Close_Button_Click()
Dim x As Long

    With Me
        For x = 0 To .Controls.Count - 1
            If TypeName(.Controls(x)) = "Label" Then
                ' IT IS A LABEL, YOU CAN ACCESS IT WITH: .Controls(x) LIKE THIS: 
                ' Debug.Print .Controls(x).Name
            End If
        Next x
    End With
End Sub

If you wish to set certain controls with predefined values:

Private Sub Close_Button_Click()
Dim vControls As Variant
Dim vValues As Variant
Dim lIndex As Long

    With Me
        ' THE TWO ARRAY'S INDEX ORDERS MUST MATCH!
        vControls = Array(.Label1, .Label2)  ' you can continue to fill the array with labels
        vValues = Array("value1", "value2")   ' you can continue to fill the array with values
        
        For lIndex = LBound(vControls) To UBound(vControls)
            vControls(lIndex).Caption = vValues(lIndex)
        Next lIndex
    End With
End Sub