How to solve a type mismatch in vba when there seems to be no mismatch?-CodePudding

Public Sub ComboBoxPopulate()
    Sheets("MSS").Select
    
    
    Dim counter As Integer
    Dim cmbox_opt As String
    Dim array_cmbox As ArrayList
    Set array_cmbox = New ArrayList
    Dim last_row As Integer
    
    last_row = Range("B" & Rows.Count).End(xlUp).Row
    
    For counter = 3 To last_row
        cmbox_opt = Range("B" & counter).Value
        If IsInArray(cmbox_opt, array_cmbox) Then
            array_cmbox.Add cmbox_opt = False
        Else
            array_cmbox.Add cmbox_opt
        End If
    Next
        
End Sub
 

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

End Function

Hi I'm trying to figure out why it's telling me that I have a type mismatch when I try to use the IsInArray function to determine if cmbox_opt is in the empty array. I used the VarType function to ensure that cmbox_opt is indeed a string and it is. So I don't understand why it wont accept it as a parameter inside the IsInArray function?

CodePudding user response：

I think the issue is that you are treating an ArrayList as an array.

ArrayList does have a comtains method, so I don't think you need or want IsInArray anyway.

I'm not sure what you intend by

array_cmbox.Add cmbox_opt = False

but if that isn't your problem then I'd imagine it's equivalent to

array_cmbox.Add False

in which case you're asking a boolean to a string ArrayList.

CodePudding user response：

Your error is coming from LBound(arr) and UBound(arr). The parameter in both these functions is an Array not an ArrayList, hence the Type Mismatch. The error has nothing to do with the type of cmb_opt.

You were led to believe that the problem was different, because the error gets thrown as you step over your function call. However, in such a case, you should put a breakpoint within the function. That would have revealed on which line within the function the error was thrown, which would have made it more obvious to you.

As JSmart523 has pointed out Contains is much easier for your purpose. Use this instead:

Dim counter As Integer
Dim cmbox_opt As String
Dim array_cmbox As ArrayList
Set array_cmbox = New ArrayList
Dim last_row As Integer

last_row = Range("B" & Rows.Count).End(xlUp).Row

For counter = 3 To last_row
    cmbox_opt = Range("B" & counter).Value
    If Not array_cmbox.Contains(cmbox_opt) Then
        array_cmbox.Add cmbox_opt
    End If
Next