excel - Error 438 when trying to call a sub method from a module class with parameters

Question

I get

error 438 : Object does not support this property or method

when trying to run the following code. Could you help me with that please?

My macro code :

Sub test() 
   Dim upList As New ListRoot
   upList.msg 'this one works fine 

   Dim g As New Gradient
   upList.AppendRoot g 'line that raises the issue
End Sub

My module class code :

Public Sub AppendRoot(grad As Gradient)
    If IsEmpty(content.content) Then
       Set content.content = grad
    Else
       content.Append (grad)
    End If
End Sub

Public Sub msg()
    MsgBox "HEY"
End Sub

I have already tried different calls of my method:

upList.AppendRoot(g) 
Call upList.AppendRoot(g) 

and the one that is quoted above. None works.

Answer 1

Note that calling a sub/function without returning a value must be called without parenthesis:

content.Append grad

If the function returns some value then parenthesis must be added

ReturnValue = content.Append(grad)

But if you add parenthesis to a sub/function that does not return any value like you did

content.Append (grad)

… that forces the variable grad to be submitted ByVal while ByRef is the standard method to submit them. So by adding parenthesis here you change from ByRef to ByVal (you can also see that there is a space between the function/sub name and the parenthesis).

---

Example:

Sub TestFunction(Param1, Param2, Param3) 'all 3 parameters are ByRef by default in VBA (not in VB.NET!)

End Sub

Some examples to call this function:

'submit 3 parameters ByRef, don't return anything
TestFunction Param1, Param2, Param3 
TestFunction(Param1, Param2, Param3) 'throws error

'submit 3 parameters ByRef, return a Value
ReturnValue = TestFunction(Param1, Param2, Param3) 
ReturnValue = TestFunction Param1, Param2, Param3   'throws error 

'submit parameters 1 and 3 ByVal and 2 ByRef, don't return anything
TestFunction (Param1), Param2, (Param3) 

'submit parameters 1 and 3 ByVal and 2 ByRef, return a Value
ReturnValue = TestFunction((Param1), Param2, (Param3)) 

While with 3 parameters it will throw an error if you add parenthesis where they should not be, it will be more difficult with only one parameter:

TestFunction Param1   'correct
TestFunction (Param1) 'this threw an error with 3 parameters, but does not throw an error 
                      'with one parameter instead it changes behavior ByRef to ByVal
ReturnValue = TestFunction(Param1) 'correct
ReturnValue = TestFunction Param1  'throws error