Compilation error when passing ByRef parameter argument to VBA

Question

I have a compilation error in Excel VBA that I do not understand ...

I have a method like this:

Public Sub SomeMethod(ByRef argument As SomeClass) ... <- 'argument' must be called ByRef because I have to modify it End Sub 

If I define a variable like:

  Dim var As SomeClass 

No problem, I can call:

  SomeMethod var 

and it works.

BUT, if I define the Variant variable as:

  Dim var as Variant Set var = New SomeClass 

This does not work.

If I call:

  SomeMethod var 

VB pops up a message: 'ByRef argument type mismatch'

If I call:

  SomeMethod (var) 

it is "compiled", but var is then passed to ByVar, and it gave me a runtime error: "The object does not support this property or method"

So I just want to tell VBA that the Variant "var" variable is actually a "SomeClass" object (in the debugger, says VBA), but I don't know how to do this ...

Can anyone help me out?

Answer 1

Decorate ByVal

 Public Sub SomeMethod(ByVal argument As someclass) 

then you can pass the option;

 SomeMethod var 

This works because the ByVal argument accepted as a local copy of the link to Variant[someclass] , which means that VB is free to perform type conversion (to convert Variant[someclass]->someclass ).

It does not work when passed as ByRef , because any changes to the argument also affect the referenced variable of the calling object outside the current procedure.

If you pass the As Object , you won't need ByVal .

If you do a lot with custom classes, VBA supports interfaces; function foo(obj as IOneOfMyWidgets)

Answer 2

Two things:

The correct way to call Sub

In VBA, to call Sub, you can either write this

 SomeSub var 

without parentheses or

 Call SomeSub(var) 

This: SomeSub (var) does not do what you think it does.

Listing A variant of a class or data type

As I wrote, Alex K's answer came, so I’ll just add, showing that the type mismatch is not limited to objects, but also affects the primitive data types contained in Variant:

 Sub tester() Dim v As Variant v = 1.234567 ' v is now type Variant/Double dothis v ' No problem. dothat v ' Compile error: ByRef argument type mismatch. End Sub Sub dothis(ByVal d As Double) 'stuff End Sub Sub dothat(ByRef d As Double) 'stuff End Sub 

Answer 3

You can also explicitly specify the type:

 '/* Factory */ Select Case TypeName(var) Case "SomeClass": Dim cast As SomeClass Set cast = var SomeMethod cast End Select