CODEWITHSUNDEEP
arraysexcelvba
Pierre44
Pierre44

Reputation: 1741

Compile error: ByRef arugment type mismatch: How to call a value in an array

In a loop, I want to check if value is in an array and if so, then skip to the next iteration.

My low understanding of Arrays are blocking me though:

I am using the function below (from: Check if a value is in an array or not with Excel VBA) to see check whether the value is in the array. 

Public Function IsInArray(Vtobefound As Long, arr As Variant) As Boolean
    Dim i
    For i = LBound(arr) To UBound(arr)
        If arr(i) = Vtobefound Then
            IsInArray = True
            Exit Function
        End If
    Next i
    IsInArray = False

End Function

But my Sub below still doesn't work: 

Sub CountCellstest()

    Dim i, k As Long
'   Dim iArray() As Single

    ReDim iArray(1 To 1) As Single

    For i = 1 To 3
        If IsInArray(i, iArray) Then 'ERROR HERE on the i

    GoTo next_iteration

        End If

            ReDim aArray(1 To 1) As Single
            iArray(UBound(iArray)) = 2
            ReDim Preserve iArray(1 To UBound(iArray) + 1) As Single
            'DO smth
            MsgBox "test"
next_iteration:
    Next i

End Sub

The error comes from the line: 

If IsInArray(i, iArray) Then

I get Compile error: ByRef arugment type mismatch The function IsInArray needs a long and I put a long in the formula so I don't understand the issue... Can someone explain?

Upvotes: 1

Views: 89

Answers (1)

Rory
Rory

Reputation: 34075

Common mistake. Your i variable is actually a Variant hence the mismatch. You have to type all the variables individually like this:

Dim i As Long, k As Long

Upvotes: 8

Related Questions