ByRef argument type mismatch, Excel VBA

Question

Quite new to VBA however been programming in java and C quite a lot.

I have what seems to be a classic error unfortunately. The other people who had this problem do not seem to have the same root to the problem. The reason is that all other posts' errors that I've found flag on the function-call. My error flags on the function definition!

Declarations:

Dim aktier() As String
ReDim aktier(antalTr) As String
Dim unikaAktier() As String
ReDim unikaAktier(antalTr) As String

Dim antalTr As Integer
Dim Kop As Integer
Kop = 1
Dim Salj As Integer
Salj = 2
Dim antalUnika As Integer
antalUnika = 0

Function-call in a for-loop. There is no error flag here.

For o = 1 To antalUnika
    Cells(o + 1, 3).Value = snittInkopEllerSalj(unikaAktier(o), antalTr, Kop)
    Cells(o + 1, 4).Value = snittInkopEllerSalj(unikaAktier(o), antalTr, Salj)
Next o

Function. Flagging on the first line.

Function snittInkopEllerSalj(bolag As String, antalTr As Integer, InUt As Integer) As Integer

Dim totKopSalj As Integer
Dim totAktier As Integer
totKopSalj = 0
totAktier = 0

For i = 1 To antalTr
    If Sheet1.Cells(i, 4).Value = bolag Then
        If InUt = 1 Then
            If isCorrectTrans(i, InUt) = True Then
                totKopSalj = totKop + Sheet1.Cells(i, 7)
                totAktier = totAktier + Sheet1.Cells(i, 5)
            End If
        Else
            If isCorrectTrans(i, InUt) = True Then
                totKopSalj = totKop + Sheet1.Cells(i, 7)
                totAktier = totAktier + Sheet1.Cells(i, 5)
            End If
        End If
    End If
Next i

snittInkopEllerSalj = Round(totKopSalj / totAktier)

End Function

I really don't understand why i get this error. I feel like everything is well-defined?

Leviathan Leviathan · Accepted Answer · 2016-05-06T21:18:45

You should always put Option Explicit in the first line of all modules. That enforces that all variables must be declared.

If you add all missing variable declarations your code should work. Also, antalTr must be declared before you use it in ReDim aktier(antalTr) As String

Not related to your problem, but:

you should never use the Integer type. Use Long instead. Since Integers in VBA are just 16 bit, but computers nowadays don't have 16 bit registers, it will be stored in as a 32 bit long anyways. Plus you don't have to worry about breaking the 32767 boundary.
Explicitly scope your functions. If you don't prepend a Private or Public, the latter will be the default.
All parameters will be passed by reference (ByRef) by default. If you do not want that put a ByVal before the parameter name in the Sub or Function declaration.

ByRef argument type mismatch, Excel VBA

1 Answers