Error 438 getting default member in custom class

Question

Any help on why the default member is failing in the For Each would be really appreciate it.

This is based on this blog post from Doug Glancy:

https://yoursumbuddy.com/building-a-workbook-table-class/

1) Added a Class called

cWorkbookTables

Dim m_wb As Excel.Workbook
Dim m_Tables As Collection

Public Property Get NewEnum() As IUnknown
'the following line, added in a text editor,
'creates the ability to cycle through the items with For Each
'Attribute NewEnum.VB_UserMemId = -4
Set NewEnum = m_Tables.[_NewEnum]
End Property

Public Function Initialize(WbWithTables As Excel.Workbook)
Set m_wb = WbWithTables
Refresh
End Function

Public Sub Refresh()
Dim ws As Excel.Worksheet
Dim lo As Excel.ListObject

Set m_Tables = New Collection
For Each ws In m_wb.Worksheets
  For Each lo In ws.ListObjects
    m_Tables.Add lo, lo.Name
  Next lo
Next ws
End Sub

Public Property Get Item(Index As Variant) As Excel.ListObject
'the following line, added in a text editor,
'sets Item as the default property of the class
'Attribute Item.VB_UserMemId = 0
Set Item = m_Tables(Index)
End Property

Public Property Get Count()
Count = m_Tables.Count
End Property

Property Get Exists(Index As Variant) As Boolean
Dim test As Variant
On Error Resume Next
Set test = m_Tables(Index)
Exists = Err.Number = 0
End Property

2) Followed the steps to add the attributes to set the default member and use for each as stated in Chip's site:

So the final code imported from a text file is:

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "cWorkbookTables"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Dim m_wb As Excel.Workbook
Dim m_Tables As Collection

Public Property Get NewEnum() As IUnknown
'the following line, added in a text editor,
'creates the ability to cycle through the items with For Each
Attribute NewEnum.VB_UserMemId = -4
Attribute NewEnum.VB_MemberFlags = "40"
Set NewEnum = m_Tables.[_NewEnum]
End Property

Public Function Initialize(WbWithTables As Excel.Workbook)
Set m_wb = WbWithTables
Refresh
End Function

Public Sub Refresh()
Dim ws As Excel.Worksheet
Dim lo As Excel.ListObject

Set m_Tables = New Collection
For Each ws In m_wb.Worksheets
  For Each lo In ws.ListObjects
    m_Tables.Add lo, lo.Name
  Next lo
Next ws
End Sub

Public Property Get Item(Index As Variant) As Excel.ListObject
'the following line, added in a text editor,
'sets Item as the default property of the class
Attribute Item.VB_UserMemId = 0
Set Item = m_Tables(Index)
End Property

Public Property Get Count()
Count = m_Tables.Count
End Property

Property Get Exists(Index As Variant) As Boolean
Dim test As Variant
On Error Resume Next
Set test = m_Tables(Index)
Exists = Err.Number = 0
End Property

3) Finally, tested the class by adding to tables to the workbook and ran this code:

Sub TestTableClass()
Dim clsTables As cWorkbookTables
Dim lo As Excel.ListObject
Dim i As Long

Set clsTables = New cWorkbookTables
With clsTables
  .Initialize ThisWorkbook
  Debug.Print "Number of tables in workbook: " & .Count
  For i = 1 To .Count
    Debug.Print "clsTables(" & i & ") name: " & .Item(i).Name
  Next i
  For Each lo In clsTables
    Debug.Print lo.Name & " " & lo.DataBodyRange.Address
  Next lo
End With
Debug.Print "There is a Table1: " & clsTables.Exists("Table1")
Debug.Print "There is a Table3: " & clsTables.Exists("Table3")
End Sub

Error in line:

Answer 1

It looks like that in order for the attributes to be correctly processed, they need to be directly after the header. Apparently having comments between the header and the attribute cause it to be considered malformed and thus gets dropped.

This is an annoying thing about VBIDE - it will never give you an error if the attribute is malformed or incorrect. It will happily import the file and just drop the bad attribute without much as a peep about it. Thus, a good test to verify that attributes are processed is to export the same file that you just imported into VBIDE. If you see attributes suddenly missing, then you know VBIDE considered it invalid, which could be:

1) Not correct place to put it 2) Not a valid attribute name 3) Wrong data type used (e.g. didn't quote when it should be a string, or added quotes when it should be a number)

Obviously there's lot of guesswork involved. One way to help minimize this is to use Rubberduck which provides annotations & quickfixes to help you create attributes from VBIDE. It takes care of setting up the attribute and re-importing it for you. There's a wiki that explains how you can use the annotation feature.

Thanks @mathieu-guindon for the image!