访问 VBA：迭代窗体上的控件，显示属性只有某些控件类型具有

Access VBA: Iterating controls on a form, displaying property only some control types have

提问by Simon Tewsi

I'm trying to iterate through the controls on a form to display the ControlSource properties of those controls that support ControlSource.

我试图遍历窗体上的控件以显示支持 ControlSource 的那些控件的 ControlSource 属性。

I've found it relatively easy to identify the controls that have ControlSource properties. The problem is in accessing the property for those controls. A generic Control object does not have a ControlSource property. If I was using another language I would simply cast a control that is a listbox to a ListBox object, for example, to access the ControlSource property. But my understanding is that in VBA there is no type inheritance so I can't cast one type of object to another.

我发现识别具有 ControlSource 属性的控件相对容易。问题在于访问这些控件的属性。通用 Control 对象没有 ControlSource 属性。如果我使用另一种语言，我只需将作为列表框的控件转换为 ListBox 对象，例如，访问 ControlSource 属性。但我的理解是，在 VBA 中没有类型继承，所以我不能将一种类型的对象转换为另一种类型。

So how do I access the ControlSource properties of those controls that support the property?

那么如何访问支持该属性的那些控件的 ControlSource 属性呢？

Here's my code so far:

到目前为止，这是我的代码：

Private Sub IterateControlsOnForm()
Dim frm As Form, formName As String
' The only controls that have a ControlSource property are either
'   BoundObjectFrame controls or ListBox controls.
Dim ctrl As Control
Dim boundObjectFrame As boundObjectFrame, listBoxCtrl As listBox
Dim boundObjectFrameTypes() As String, listBoxTypes() As String
Dim ctrlType As String

    formName = "MAINFORM"

    ' Useful way of populating a string array - use Split function.
    '   Array function only works with Variants.
    boundObjectFrameTypes = _
        Split("CheckBox,ComboBox,CustomControl,GroupLevel", ",")
    listBoxTypes = Split("OptionButton,OptionGroup,TextBox,TextBox", ",")

    ' Assumes form is open.
    Set frm = Forms(formName)
    For Each ctrl In frm.Controls
        ' Ignore controls that do not have a ControlSource property.
        ctrlType = TypeName(ctrl)
        If IsStringInArray(ctrlType, boundObjectFrameTypes) Then
            ' **** FOLLOWING LINE FAILS ****
            Set boundObjectFrame = ctrl
            Debug.Print boundObjectFrame.Name & "(" & ctrlType & ") " & _
                "ControlSource Property: " & boundObjectFrame.ControlSource
        ElseIf IsStringInArray(ctrlType, listBoxTypes) Then
            ' **** FOLLOWING LINE FAILS ****
            Set listBoxCtrl = frm.Controls(ctrl.Name)
            Debug.Print listBoxCtrl.Name & "(" & ctrlType & ") " & _
                "ControlSource Property: " & listBoxCtrl.ControlSource
        End If
    Next ctrl
End Sub

I've tried two methods of converting a generic Control object to a more specific control that has a ControlSource property. See the two comments "** FOLLOWING LINE FAILS **".

我尝试了两种将通用 Control 对象转换为具有 ControlSource 属性的更具体控件的方法。请参阅两条评论“ ** FOLLOWING LINE FAILS **”。

采纳答案by Fionnuala

How about using Object?

使用对象怎么样？

Private Sub IterateControlsOnForm()
Dim frm As Form, formName As String
' The only controls that have a ControlSource property are either
'   BoundObjectFrame controls or ListBox controls.
Dim ctrl As Control
Dim boundObjectFrame As Object, listBoxCtrl As Object
Dim boundObjectFrameTypes As String, listBoxTypes As String
Dim ctrlType As String

    formName = "MAINFORM"

    ' Useful way of populating a string array - use Split function.
    '   Array function only works with Variants.
    boundObjectFrameTypes = _
        ",CheckBox,ComboBox,CustomControl,GroupLevel"
    listBoxTypes = ",OptionButton,OptionGroup,TextBox,TextBox"

    ' Assumes form is open.
    Set frm = Forms(formName)
    For Each ctrl In frm.Controls
        ' Ignore controls that do not have a ControlSource property.
        ctrlType = TypeName(ctrl)
        If InStr(boundObjectFrameTypes, "," & ctrlType) Then
            Set boundObjectFrame = ctrl
            Debug.Print boundObjectFrame.Name & "(" & ctrlType & ") " & _
                "ControlSource Property: " & boundObjectFrame.ControlSource
        ElseIf InStr(listBoxTypes, "," & ctrlType) Then
            Set listBoxCtrl = frm.Controls(ctrl.Name)
            Debug.Print listBoxCtrl.Name & "(" & ctrlType & ") " & _
                "ControlSource Property: " & listBoxCtrl.ControlSource
        End If
    Next ctrl
End Sub

With VBA, you can also use On Error Resume Next, though I agree it is generally best to avoid errors.

使用 VBA，您还可以使用 On Error Resume Next，尽管我同意通常最好避免错误。

formName = "MAINFORM"

Set frm = Forms(formName)
For Each ctrl In frm.Controls
On Error Resume Next
    Debug.Print ctrl.Name _
        & " ControlSource Property: " & ctrl.ControlSource
    If Err.Number = 438 Then
        Err.Clear
    End If
Next