Setting (Private WithEvents As Sheet1) sheetUI = Sheet1 causes error 438: Object doesn't support this property or method

Question

I am using this excellent tutorial as a base to create a simple "Hello World" Excel VBA project leveraging on Mathieu Guindon's concept of writing Object-Oriented Programming VBA code, discussed in a series of articles on the https://rubberduckvba.wordpress.com/ blog.

I have created a "bare bones" project without any Model containing an Excel worksheet (HelloSheet), a View, a ViewAdapter (including ViewCommands and ViewEvents interfaces) and a Controller. The VBA project compiles without errors but when I try to run the "application entry" macro I get the dreaded "Run-time error 438: Object doesn't support this property or method". This happens inside the Class_Initialize() sub of my View class where I have declared "Private WithEvents sheetUI As HelloSheet" and try to set "sheetUI = HelloSheet".

Here is an overview of my project tree, as seen in the RubberDuck VBIDE.

I have tried updating the VBA project references to exactly match those of the "Battleship" sample project. I also tried the two different approaches to implementing the Lazy Object / Weak Reference in the View class - the one in the "Battleship (WorksheetView).xlsm" linked in the original article vs the approach used in the latest version on GitHub, more specifically:

Private adapter As ***IWeakReference***
Private WithEvents sheetUI As HelloSheet

Private Sub Class_Initialize()
    sheetUI = HelloSheet
End Sub

Private Property Get ViewEvents() As ISheetViewEvents
    Set ViewEvents = adapter ***.Object***
End Property

VS

Private adapter As ***SheetViewAdapter***
Private WithEvents sheetUI As HelloSheet

Private Sub Class_Initialize()
    sheetUI = HelloSheet
End Sub

Private Property Get ViewEvents() As ISheetViewEvents
    Set ViewEvents = ***adapter***
End Property

..but the "Run-time error 438: Object doesn't support this property or method" persisted.

Below is all relevant code split in sheets, classes, interfaces etc.:

1) HelloSheet (regular Excel sheet code-behind):

'@Folder("HelloWorld.View.Worksheet")
Option Explicit

Public Event DoubleClick(ByVal clickedRow As Integer)

Public Sub HideShape(shapeName As String)
    Dim currentShape As Shape
    Set currentShape = Me.Shapes(shapeName)
    currentShape.Visible = msoFalse
End Sub

Public Sub ShowShape(shapeName As String)
    Dim currentShape As Shape
    Set currentShape = Me.Shapes(shapeName)
    currentShape.Visible = msoTrue
End Sub

Public Sub OnLaunchCommand()
    ShowShape ("WarningTriangle")
End Sub

Public Sub TempManualHide()
    HideShape ("WarningTriangle")
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    RaiseEvent DoubleClick(Target.Row)
End Sub

Public Sub PreviewSelectedRecord(ByVal selectedRow As Integer)
    Me.Cells(1, 1).Value2 = "Row is " & CStr(selectedRow)
End Sub

2) SheetView class:

'@Folder("HelloWorld.View.Worksheet")
Option Explicit
Implements ISheetViewCommands

Private adapter As SheetViewAdapter ' IWeakReference
Private WithEvents sheetUI As HelloSheet

Private Sub Class_Initialize()
    sheetUI = HelloSheet
End Sub

Private Property Get ViewEvents() As ISheetViewEvents
    Set ViewEvents = adapter '.Object
End Property


':GameSheet event handlers
':Messages sent from the view
':***************************

Private Sub sheetUI_DoubleClick(ByVal clickedRow As Integer)
    ViewEvents.PreviewSelectedRecord clickedRow
End Sub


':IGridViewCommands
':Messages sent from the controller
':*********************************

Private Property Set ISheetViewCommands_Events(ByVal value As ISheetViewEvents)
    Set adapter = value ' WeakReference.Create(Value)
End Property

Private Property Get ISheetViewCommands_Events() As ISheetViewEvents
    Set ISheetViewCommands_Events = adapter '.Object
End Property

Private Sub ISheetViewCommands_OnLaunchCommand()
    sheetUI.OnLaunchCommand
End Sub

Private Sub ISheetViewCommands_OnPreviewSelectedRecord(ByVal selectedRow As Integer)
    sheetUI.PreviewSelectedRecord selectedRow
End Sub

3) ISheetViewEvents interface:

'@Folder("HelloWorld.View")
'@Interface
Option Explicit

Public Sub PreviewSelectedRecord(ByVal selectedRow As Integer)
End Sub

4) ISheetViewCommands interface:

'@Folder("HelloWorld.View")
'@Interface
Option Explicit

'@Description("Gets/sets a weak refererence to the view events.")
Public Property Get Events() As ISheetViewEvents
End Property

Public Property Set Events(ByVal value As ISheetViewEvents)
End Property

Public Sub OnLaunchCommand()
End Sub

Public Sub OnPreviewSelectedRecord(ByVal selectedRow As Integer)
End Sub

5) SheetViewAdapter class (PredeclaredId / has default instance):

'@Folder("HelloWorld.View")
Option Explicit
'@PredeclaredId
Implements ISheetViewCommands
Implements ISheetViewEvents

Public Event OnPreviewCurrentSelectedRecord(ByVal selectedRow As Integer)

Private Type TAdapter
    SheetViewCommands As ISheetViewCommands
End Type
Private this As TAdapter

Public Function Create(ByVal view As ISheetViewCommands) As SheetViewAdapter
    With New SheetViewAdapter
        Set .SheetViewCommands = view
        Set view.Events = .Self
        Set Create = .Self
    End With
End Function

Public Property Get Self() As SheetViewAdapter
    Set Self = Me
End Property

'@Description("Gets/sets a reference that exposes commands to send to the view.")
Public Property Get SheetViewCommands() As ISheetViewCommands
    Set SheetViewCommands = this.SheetViewCommands
End Property

Public Property Set SheetViewCommands(ByVal value As ISheetViewCommands)
    Set this.SheetViewCommands = value
End Property


':IGridViewEvents
':Messages sent from the view
':***************************

Private Sub ISheetViewEvents_PreviewSelectedRecord(ByVal selectedRow As Integer)
    RaiseEvent OnPreviewCurrentSelectedRecord(selectedRow)
End Sub


':IGridViewCommands
':Messages sent from the controller
':*********************************

Private Property Set ISheetViewCommands_Events(ByVal value As ISheetViewEvents)
    Err.Raise 5, TypeName(Me), "Invalid use of property"
End Property

Private Property Get ISheetViewCommands_Events() As ISheetViewEvents
    Set ISheetViewCommands_Events = Me
End Property

Private Sub ISheetViewCommands_OnLaunchCommand()
    this.SheetViewCommands.OnLaunchCommand
End Sub

Private Sub ISheetViewCommands_OnPreviewSelectedRecord(ByVal selectedRow As Integer)
    this.SheetViewCommands.OnPreviewSelectedRecord selectedRow
End Sub

6) HelloController class:

'@Folder("HelloWorld")
Option Explicit

Private viewCommands As ISheetViewCommands
Private WithEvents viewAdapter As SheetViewAdapter

Public Sub Launch(ByVal adapter As SheetViewAdapter)
    Set viewAdapter = adapter
    Set viewCommands = adapter
    viewCommands.OnLaunchCommand
End Sub

Private Sub viewAdapter_OnPreviewCurrentSelectedRecord(ByVal selectedRow As Integer)
    viewCommands.OnPreviewSelectedRecord selectedRow
End Sub

7) And finally the "Macros" standard module which serves as an entry point. This is where I encounter the error (the "Set view = New SheetView" line):

'@Folder("HelloWorld")
'@Description("Application entry points.")
Option Explicit
'@Ignore MoveFieldCloserToUsage
Private controller As HelloController

Public Sub LaunchWorksheetInterface()
    Dim view As SheetView
    Set view = New SheetView

    Set controller = New HelloController
    controller.Launch SheetViewAdapter.Create(view)
End Sub

Supposing I could get around the entry-level error, I would expect a very simple functionality:

1) A hidden Excel shape is made visible on the HelloSheet (the OnLaunchCommand);

2) When double-clicking on a cell, the row it is located on would be reported in cell A1 of the same worksheet (the Worksheet_BeforeDoubleClick event).

Obviously this amount of code for such simple tasks is overkill - my idea is once I get these basics working to add Model classes to the project and map them to certain areas (i.e. Tables/ListObjects) inside the Workbook.

Any help will be greatly appreciated! And kudos to anyone who has made it to the end of this rather long post :)

Answer 1

Private WithEvents sheetUI As HelloSheet

Private Sub Class_Initialize()
    sheetUI = HelloSheet
End Sub

sheetUI is an object reference, assigning it requires the Set keyword:

Private WithEvents sheetUI As HelloSheet

Private Sub Class_Initialize()
    Set sheetUI = HelloSheet
End Sub

Error 438 is thrown whenever you try to access the default member of a Worksheet class, since Worksheet has no default member - this code reproduces the error from the immediate pane:

?Sheet1

Or:

foo = Sheet1

Rubberduck inspections should have warned about this, under "Code Quality Issues":

Object variable 'sheetUI' is assigned without the 'Set' keyword.