CODEWITHSUNDEEP
vbaclassmismatchbyref
Mark Tallentire
Mark Tallentire

Reputation: 183

VBA ByRef Error Passing a Class Object to a Sub

I'm trying to pass an object to a new sub but keep hitting a ByRef Mismatch error.

I've declared my object as:

Dim targetWorkbook
Set targetWorkbook = New CWorkbooks

I'm calling my sub by using:

checkbook targetWorkbook

And my sub is set as:

Sub checkbook(targetWorkbook As CWorkbooks)

'Checking if passthrough is working

End Sub

Any help is appreciated, my types are aligned and everything so I'm not sure why this is occuring.

Thanks!

Upvotes: 0

Views: 821

Answers (2)

ReturnVoid
ReturnVoid

Reputation: 1126

Sub Foo()
    'single class object
    Dim myClass1 As New clsClass
    myClass1.StringName = "cls1"
    Call Par(myClass1)

    'or class array
    Dim myClass2(1 To 5) As New clsClass
    myClass2(1).StringName = "cls2"
    Call Par(myClass2)

End Sub

Sub Par(ByRef lClass As Variant) 'same function call used for both
    'Debug.Print lClass.StaffName & vbNewLine 'single class object
    'Debug.Print lClass(1).StaffName & vbNewLine 'array version
End Sub

google brought me here for same problem but found the accepted answer lacking & didn't work at all in my case where Foo() was a module & Par() a worksheet, trying to pass class array.

Upvotes: 1

OldUgly
OldUgly

Reputation: 2119

I was able to duplicate your problem with the compiler. The following passes the compiler and runs. You declared TargetWorkbook as Variant, then set it to CWorkbooks - this works, but not when passed to the sub.

Sub main()
    Dim TargetWorkbook As CWorkbooks
    Set TargetWorkbook = New CWorkbooks
    checkbook TargetWorkbook
End Sub
Sub checkbook(ByRef TargetWorkbook As CWorkbooks)

'Checking if passthrough is working

End Sub

Upvotes: 0

Related Questions