VBA MSFORMS与控件 - 有什么区别？

Question

在向用户窗体添加控件时，以下内容有何区别。我不确定在何时使用特定的控件是适当的。

Dim aButton1 as MSFORMS.CommandButton
Dim aButton2 as Control.CommandButton
Dim aButton3 as CommandButton

- TYale

Answer 1

首先添加UserForm。然后在VBA IDE中按F2，将会出现对象浏览器。在左上角是一个组合框，选择MSForms。在类列表中，您可以看到属于MSForms对象库的类。

您可以在该列表中看到CommandButton和Control:

在代码中声明CommandButton类型的变量：

Dim button1 As MSForms.CommandButton
Dim button2 As CommandButton

变量button1肯定是来自MSForms的CommandButton类型。而button2可能是你在本地VBA项目中定义的自己的类，但是如果你的本地VBA项目中没有包含这样的类，它也会被认为是来自MSForms。然而，如果你引用另一个对象库，比如'MSFoo'，其中也包含CommandButton类，你就必须像这样完全限定声明它们：

Dim button1 As MSForms.CommandButton
Dim button2 As MSFoo.CommandButton

在您的代码中声明一个 Control 类型的变量：

Dim controlObject As MSForms.Control

使用 Control 类型的变量作为控件的基类。例如，枚举 Controls 集合：

For Each controlObject In Me.Controls
    Debug.Print VBA.TypeName(controlObject)
Next controlObject

或者作为一个参数传递给期望不仅仅是一种类型控件的函数：

Private Sub PrinControlName(ByRef c As MSForms.Control)
    Debug.Print c.Name
End Sub

因此，我认为在大多数情况下使用完全限定名（如MSForms.CommandButton）是适当的。 使用Control.CommandButton是错误的，并且在您引用了一个名为“Control”的对象库并带有其中的Class CommandButton之前无法编译。

编辑：

这里有一个与MSForm.CommandButton同名的本地创建类的例子：

以及在这种情况下TypeName和TypeOf的工作方式：

Option Explicit

Private m_buttonMsForms As MSForms.CommandButton
Private m_buttonLocal As CommandButton

Private Sub UserForm_Initialize()
    Set m_buttonMsForms = Me.Controls.Add( _
        "Forms.CommandButton.1", "testMsButton", True)
    Set m_buttonLocal = New CommandButton

    m_buttonLocal.Name = "testLocalButton"

    Debug.Print "We have two instances of two different button types: " & _
        m_buttonLocal.Name & " and " & m_buttonMsForms.Name

    ' Check instances with TypeName function
    ' TypeName function returns same results
    If VBA.TypeName(m_buttonMsForms) = VBA.TypeName(m_buttonLocal) Then
        Debug.Print "TypeName of both buton types returns same result"
    End If

    ' Check instances with TypeOf operator
    ' TypeOf doen't work with not initialised objects
    If m_buttonLocal Is Nothing Or m_buttonMsForms Is Nothing Then _
        Exit Sub

    ' TypeOf operator can distinguish between
    ' localy declared CommandButton type and MSForms CommandButton
    If TypeOf m_buttonLocal Is MSForms.CommandButton Then _
        Debug.Print "m_buttonLocal Is MSForms.CommandButton"

    If TypeOf m_buttonMsForms Is CommandButton Then _
        Debug.Print "m_buttonMsForms Is CommandButton"

    If TypeOf m_buttonLocal Is CommandButton Then _
        Debug.Print "m_buttonLocal Is CommandButton"

    If TypeOf m_buttonMsForms Is MSForms.CommandButton Then _
        Debug.Print "m_buttonMsForms Is MSForms.CommandButton"

End Sub

Output:

We have two instances of two different button types: testLocalButton and testMsButton
TypeName of both buton types returns same result
m_buttonLocal Is CommandButton
m_buttonMsForms Is MSForms.CommandButton

- Daniel Dušek

Answer 2

数字1和3创建了相同类型的控件。第一个语句只是完全限定 - 等同于使用Dim ws as WorkSheet或Dim ws as Excel.WorkSheet。

我不熟悉第二种类型 -“Control.CommandButton” - 并且它在Excel 2010中的用户窗体中无法编译。

- Doug Glancy