Compilation-error while passing Variant argument ByRef in VBA

4
votes

I've got a "compilation" error in Excel VBA, which I don't understand...

I've got a method like that :

    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's working.

BUT, if I define a Variant variable like :

    Dim var as Variant
    Set var = New SomeClass

It doesn't work.

If I call :

    SomeMethod var

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

If I call :

    SomeMethod (var)

it "compiles", but var is then passed ByVar and it gave me a runtime-error message : 'Object doesn't support this property or method'

So I basically just want to tell VBA that my Variant variable 'var' is in fact a 'SomeClass' object (in debugger, VBA says so), but I don't know how to do that...

Could someone please help me ?

3
excelvba
Why does it need to be a Variant at all? If you know it's of type SomeClass, then declare it as such. – Widor
My whole code is in a full VBA project and the var object could be of multiples types. – adrien.pain
My whole code is in a full VBA project and the var object could be of multiples types. And depending of its type (TypeName var), I must call a specific method which has typed argument (Method_A(ByRef arg as SomeClass or Method_B(ByRef arg as SomeOtherClass)) – adrien.pain
"Even if a called procedure has declared its parameters as ByRef, you can force those to be ByVal by enclosing each argument within parentheses." -- cpearson.com/excel/byrefbyval.aspx – Fionnuala

3 Answers

5
votes

Decorate the argument with ByVal

Public Sub SomeMethod(ByVal argument As someclass)

then you can pass a variant;

SomeMethod var

This works because the ByVal argument is received as a local copy of a reference to a Variant[someclass] which means VB can freely perform a type-conversion (to convert the Variant[someclass]->someclass).

It wont work when passed as ByRef because any changes to argument would also affect the calling object reference variable outside the current procedure.

If you pass As Object you don't need the ByVal.

If your doing this a lot with custom classes VBA supports interfaces; function foo(obj as IOneOfMyWidgets)

3
votes

Two things:

Proper way to call a Sub

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

SomeSub var

without parentheses or this

Call SomeSub(var)

This: SomeSub (var) doesn't do what you think it does.

Casting Variant to class or data type

As I was writing this, Alex K's answer came in, so I will just supplement by showing that the type mismatch isn't restricted to objects, but also affects primitive data types contained in a 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
0
votes

You could also explicitly cast to a type:

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