CODEWITHSUNDEEP
excelvbabyref
Scryper
Scryper

Reputation: 343

Why do I have this error : "argument type ByRef not compatible"?

I'm knew to this language. Okay so, I'm creating a VBA app which aims to manage the distribution of the deifferent courses in a school.

I have this error, which is really "weird" as I really don't understand why I have this even after few research on the web. It says "Argument type ByRef is not compatible". What is really strange with this error is that tt only appends when I define the type of my variable. But if I don't define the type, the it is empty and it makes the function obsolete. HHow do I fix that ? 

Private Sub Ajouter(LBConcernee As MSForms.ListBox, Colonne As String, _
                    Quadrimestre As String, UEnseignement As String)
    ' This statement is "active" when the user wants to display the courses of the learning unit he chose (Uenseignement)
    If UEnseignement = Range("C" & ActiveCell.Row) Then
        [...]
    End IF
    ' If the variable is empty, it means that all the courses havve to be displayed and no verification on the learning unit is done
    If Uenseignement = ""
        [...]
    End If
End Sub

This process aims to filter the courses printed on screen by verifying the quadrimester and the Learning Unit of the course. But, with the "as string", I have an error, and without, UEnseignement is empty and it makes my filter completely obsolete as the first condition is never true.

The code and vars are in french (Belgian student) so feel free to ask any complementary infos.

Thanks.

EDIT :

Here is the code that call this sub :

Private Sub Remplir_ListBox(Section As String, ColonneDepart As String, _
                  ListBoxConcernee As MSForms.ListBox, Bloc As Integer, _
                   Optional Quadri As String, Optional UESelectionne, _
                   Optional OptionCompta As String)

    Dim CellulePrecedente, ValeurBloc As String
    While Range(ColonneDepart & ActiveCell.Row) <> ""
        ' On ne réécrit pas 2 fois le/la même cours/UE
        If CellulePrecedente <> Range(ColonneDepart & ActiveCell.Row) Then
            ' On ajoute les cours pour le/s bloc/s demandés
            If Bloc = 0 Then
                Call Ajouter(ListBoxConcernee, ColonneDepart, Quadri, UESelectionnee)
            [...]
            End If
        End If
        Wend
End Sub

Upvotes: 0

Views: 658

Answers (2)

Tim Williams
Tim Williams

Reputation: 166970

UESelectionne is a Variant in the calling sub, but Ajouter expects a String for that argument.

Upvotes: 0

Variatus
Variatus

Reputation: 14383

VBA passes arguments ByRef by default. Meaning, unless you specify ByVal all arguments will be passed ByRef.

There are two ways to pass parameter to a sub or function. (A) ByVal A new variable can be created for use in the sub or function. Initially it's just a copy of the original but if it's modified in the receiving procedure the original doesn't change. (B) ByRef Instead of making a copy, VBA just passes the address of the existing variable. Any changes the subordinate code makes to that variable will be available to the calling procedure. The value of that variable can be changed by the subordinate.

Not all kinds of variables can be passed ByVal. As a rule, VBA requires that all objects must be passed ByRef. So, if you try to pass a ListBox object ByVal VBA will complain. Perhaps, if you turn off Variable declaration, VBA won't bother you with such minor detail and simply refuse to work without explanation.

In your code all arguments are passed ByRef (by default), or so it seems. You can also define an argument to be passed ByVal by enclosing the call in parentheses, like A = 15: MySub (A). We don't see your function call, so this is one possible source of error.

Presuming that this isn't the case I suggest you still focus on the ListBox. It might not be of the MSForm variety. For some reason VBA doesn't seem to be able to pass it ByRef.

The other parameters appear inconspicious. But perhaps there is a mixup in the calling procedure and you are unintentionally assigning the ListBox to one of the strings. In any case, you should look at the interaction between the sub and its caller. There is something wrong in passing the parameters.

Upvotes: 1

Related Questions