vba - 查看接口(interface)类时，VBE 和 Excel 在调试时崩溃

Question

我已经实现了一个实现接口(interface)的 VBA 类。我的问题是，在我的实现类存储到接口(interface)类之后，我无法调试创建的类。该类工作正常，如果表现正常。当我尝试在 VBE 调试器的本地窗口中展开变量时，崩溃发生可重现。

如果这是 VBA 中的一个已知错误，那么我会因为没有在谷歌上找到这个而感到羞耻。

如果我的类和界面有设计错误，也许你可以帮我找到它。

我在一个空工作簿中使用一个标准模块和两个类模块。 Attribute Value.VB_UserMemId = 0只是一个提醒。它不适合通过 export+ioprt 的代码。很抱歉我的评论是用德语 :P 的。正如我所指出的，我不知道代码的哪一部分导致了问题。因此，我提供了一个带有类和接口(interface)的功能齐全的测试例程。
IxTable

Option Explicit

Public Property Get Name() As String
End Property

Public Property Get Columns() As xCol()
End Property

Public Property Get Column(ByVal Index) As xCol
End Property

'Attribute Value.VB_UserMemId = 0
Public Property Get Data(ByVal Row As Long, ByVal Column) As String
End Property

Public Property Get RowCount() As Long
End Property

Public Property Get ColumnCount() As Long
End Property

Public Function ToString() As String
End Function

xTable

Option Explicit
Implements IxTable

' Private Speichervariablen
Private c() As xCol    ' Spalteneigenschaften
Private d As Variant   ' Datenfeld Data(Row,Col)
Private n As String    ' Name der Tabelle

' Buffer für Spaltenzugriff
Private lastColNumber As Long
Private lastColName As String

''' <summary>
''' Initialisierung des zweidimentionalen Datenfeldes als Data(1,1)
''' </summary>
Private Sub Class_Initialize()
    ReDim d(1 To 1, 1 To 1) As Variant
    Erase d
End Sub

''' <summary>
''' Name der abgefragten Tabelle
''' </summary>
Public Property Get Name() As String
    Let Name = n
End Property
Public Property Get IxTable_Name() As String
    Let IxTable_Name = Me.Name
End Property
''' <summary>
''' Ergänzung für Initialisierung
''' </summary>
Friend Property Let Name(ByVal value As String)
    n = value
End Property

''' <summary>
''' Zugriff auf alle Spalten
''' </summary>
Public Property Get Columns() As xCol()
    Let Columns = c
End Property
Public Property Get IxTable_Columns() As xCol()
    Let IxTable_Columns = Me.Columns
End Property

''' <summary>
''' Zugriff aus einzelne Spalte
''' </summary>
Public Property Get Column(ByVal Index) As xCol
    Let Column = c(ColumnIndex(Index))
End Property
Public Property Get IxTable_Column(ByVal Index) As xCol
    Let IxTable_Column = Me.Column(Index)
End Property

''' <summary>
''' Umsetzung von Spaltenname zu Index mit Buffer
''' </summary>
''' <param name="index">Name oder Index</param>
''' <returns>Index numerisch</returns>
Private Function ColumnIndex(ByVal Index) As Long
    If IsNumeric(Index) Then
        Let ColumnIndex = CLng(Index)
        If Not ColumnIndex = lastColNumber Then
            ' Letzten Zugriff aktualisieren
            lastColNumber = ColumnIndex
            lastColName = c(lastColNumber).Name
        End If
    Else
        ' Gleiche Spalte wie letzter Zugriff?
        If Index = lastColName Then
            ' Index aus Speicher
            ColumnIndex = lastColNumber
        Else
            ' Spalte suchen
            lastColName = Index
            For lastColNumber = 1 To Me.ColumnCount
                If c(lastColNumber).Name = Index Then Exit For
            Next
            Let ColumnIndex = lastColNumber
        End If
    End If
    If ColumnIndex > UBound(c) Then ColumnIndex = 0
End Function


''' <summary>
''' Ergänzung für Initialisierung
''' </summary>
Friend Sub SetColumn(ByVal Index As Long, value As xCol)
    c(Index).Index = Index
    c(Index).Name = value.Name
    c(Index).Length = value.Length
    c(Index).Offset = value.Offset
    c(Index).Decimals = value.Decimals
    c(Index).Inttype = value.Inttype
    c(Index).xType = value.xType
    c(Index).Text = value.Text

    lastColNumber = 0
    lastColName = vbNullString
End Sub

''' <summary>
''' Zugriff auf das Datenfeld
''' </summary>
'Attribute Value.VB_UserMemId = 0
Public Property Get Data(ByVal Row As Long, ByVal Column) As String
    Column = ColumnIndex(Column)
    Let Data = d(Row, Column)
End Property
Public Property Get IxTable_Data(ByVal Row As Long, ByVal Column) As String
    Let IxTable_Data = Me.Data(Row, Column)
End Property

''' <summary>
''' Ergänzung für Initialisierung
''' Daten sind READ ONLY
''' </summary>
Friend Property Let Data(ByVal Row As Long, ByVal Column, ByVal value As String)
    Column = ColumnIndex(Column)
    d(Row, Column) = Trim(value)
End Property

''' <summary>
''' Anzahl der Spalten
''' </summary>
Public Property Get ColumnCount() As Long
    On Error Resume Next
    Let ColumnCount = UBound(c)
    On Error GoTo 0
End Property
Public Property Get IxTable_ColumnCount() As Long
    Let IxTable_ColumnCount = Me.ColumnCount
End Property

''' <summary>
''' Anzahl der Zeilen
''' </summary>
Public Property Get RowCount() As Long
    On Error Resume Next
    Let RowCount = UBound(d, 1)
    On Error GoTo 0
End Property
Public Property Get IxTable_RowCount() As Long
    Let IxTable_RowCount = Me.RowCount
End Property


''' <summary>
''' Ergänzung für Initialisierung
''' </summary>
Friend Sub SetSize(ByVal Rows As Long, ByVal Columns As Long)
    ColumnCount = Columns
    Me.SetRowCount Rows
End Sub
Friend Sub SetRowCount(ByVal Rows As Long)
    RowCount = Rows
End Sub
Private Property Let ColumnCount(ByVal value As Long)
    ReDim c(1 To value)

    lastColNumber = 0
    lastColName = vbNullString
End Property
Private Property Let RowCount(ByVal value As Long)
    If value > 0 Then
        ReDim d(1 To value, 1 To Me.ColumnCount) As String
    Else
        On Error Resume Next
        Erase d
        On Error GoTo 0
    End If
End Property


''' <summary>
''' Ausgabe des Datenfeldes als String
''' </summary>
''' <returns>
''' Col1\tCol2\t...\tColn
''' d(1,1)\td(1,2)\td(1,n)
''' ...
''' d(m,1)\td(m,2)\td(m,n)
''' </returns>
Public Function ToString() As String
    Dim r As Long, i As Long, typing As String, descriptions As String
    For i = 1 To Me.ColumnCount
        If i = 1 Then
            ToString = c(i).Name
            typing = c(i).Inttype & "(" & c(i).Length & ")"
            descriptions = c(i).Text
        Else
            ToString = ToString & vbTab & c(i).Name
            typing = typing & vbTab & c(i).Inttype & "(" & c(i).Length & ")"
            descriptions = descriptions & vbTab & c(i).Text
        End If
    Next
    ToString = ToString & vbCrLf & typing & vbCrLf & descriptions
    For r = 1 To Me.RowCount
        ToString = ToString & vbCrLf
        For i = 1 To Me.ColumnCount
            If i = 1 Then
                ToString = ToString & Me.Data(r, i)
            Else
                ToString = ToString & vbTab & Me.Data(r, i)
            End If
        Next
    Next
End Function
Public Function IxTable_ToString() As String
    Let IxTable_ToString = Me.ToString
End Function

最后，这是测试模块。
Module1

Option Explicit

Public Enum xType
'String RFC
    TypeChar = 0
'Date RFC
    TypeDate = 1
'Numerical
    TypeNum = 2
End Enum

''' <summary>
''' Spalteneigenschaften
''' </summary>
Public Type xCol
    Index As Long
    Name As String
    Decimals As Integer
    Length As Integer
    Offset As Long
    Inttype As String
    xType As xType
    TypeName As String
    Text As String
End Type

Sub testIt()
    Dim x As xTable, ix As IxTable
    'works fine
    Set x = xTableTest
    'output is nice
    Debug.Print x.ToString

    'works fine
    Set ix = x

    ' ---> At this point x can be viewed in the locals window (all the time!)
    ' ---> ix causes Excel to crash and restart


    'output is nice
    Debug.Print ix.ToString
End Sub

Function xTableTest() As xTable
    Dim x As New xTable
    Dim c1 As xCol, c2 As xCol

    x.SetSize 3, 2
    c1.Name = "INDEX"
    c1.Length = 8
    c1.Text = "Index value"
    c1.Index = 1
    c1.Offset = 0
    c1.Inttype = "Integer"
    c1.xType = xType.TypeNum
    x.SetColumn 1, c1

    c2.Name = "TEXT"
    c2.Length = 20
    c2.Text = "Text value"
    c2.Index = 2
    c2.Offset = 8
    c2.Inttype = "String"
    c2.xType = xType.TypeChar
    x.SetColumn 2, c2

    Let x.Data(1, c1.Index) = 100
    Let x.Data(1, c2.Index) = "einhundert"
    Let x.Data(2, c1.Index) = 200
    Let x.Data(2, c2.Index) = "zweihundert"
    Let x.Data(3, c1.Index) = 210
    Let x.Data(3, c2.Index) = "zweihundertzehn"

    Set xTableTest = x
End Function

编辑:我发现这个问题似乎与我的相似。但是对于不匹配的数据类型只是一个提示，它没有得到答复。
viewing-an-object-in-locals-or-watch-window-causes-excel-to-crash

我有 testet 评论我的属性。评论 Public Property Get Columns() As xCol()在界面中解决了崩溃。但仍然没有其他属性显示值。所有房源显示 object doesn't support this property or method即使 x 值显示数据。

Answer 1

我能够使用您的代码重现相同的行为(崩溃)。删除成员后Columns , Column , 和 Data来自 IxTable界面，不再崩溃。但是，当扩展接口(interface)对象 ix在调试器中，我们得到消息对象不支持此属性或方法，而不是值，与 How to get property values of classes that implement an interface in the Locals window? 中报告的完全一样。 ，对此没有答案。因此，即使 Excel 没有崩溃，在调试器本地窗口中扩展接口(interface)变量也是没有用的。

我还找到了文章Interfaces in VBA - How to use them and how to work around them在 Expert Exchange 上，报告了与 VBA 接口(interface)相关的几个问题。

恐怕VBA接口(interface)并不是VBA最稳定的特性。

上面的专家交流文章提出了 VBA 接口(interface)的替代解决方案，我认为值得一看，因为最终结果是相同的。文章太长，无法在此处复制，但专家交流网站“永久”足以仅在此处留下文章的链接。