Runtime error 13. Incompatible types

Question

I was building a simple listing, and came across the 'Runtime Error 13. Incompatible Types'. When I Clear it tells me that the error is in the two lines of code of my 'If', referring to the dates.

What happens is that I have a listing, a search button and 4 filters to be used - two of them are Combobox ('input types' and 'status'), and two others are Textbox (expiration date' and 'end date'').

My intention is to be able to use the filters or not. But when I click 'search' it returns the error. If I enter only a date and search, error. Search with only one Combobox or both, error. If I do not select anything and search, error.

Can anyone help me? I am new with VB, I may be making a basic mistake and not have noticed. I thank you already.

Note: I came across people saying that my spreadsheet could be protected but I already checked and was not.

Edit: In the third paragraph I made an error but already corrected. In fact even inserting start date and end of maturity I have found error. None of the filters are working.

Private Sub BtnPesquisar_Click()

    LISTAR_ENTRADAS

End Sub



Private Sub TextFim_Change()

    'Formatação para datas

    TextFim.MaxLength = 10

    If Len(TextFim) = 2 Or Len(TextFim) = 5 Then
        
        TextFim.Text = TextFim.Text & "/"
    
    End If

End Sub

Private Sub TextFim_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    If KeyAscii < Asc(0) Or KeyAscii > Asc(9) Then
    
        KeyAscii = 0
        
    End If
    
End Sub

Private Sub TextInicio_Change()

    'Formatação para datas
    
    TextInicio.MaxLength = 10

    If Len(TextInicio) = 2 Or Len(TextInicio) = 5 Then
        
        TextInicio.Text = TextInicio.Text & "/"
    
    End If

End Sub


Private Sub TextInicio_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    
    If KeyAscii < Asc(0) Or KeyAscii > Asc(9) Then
    
        KeyAscii = 0
        
    End If
    
End Sub

Private Sub UserForm_Initialize()

    'Declaração dos itens no Select
    ComboTipo.AddItem "Pagar"
    ComboTipo.AddItem "Receber"
    
    ComboStatus.AddItem "Realizado"
    ComboStatus.AddItem "Não Realizado"
    

End Sub

Sub LISTAR_ENTRADAS()

    'Declaração das variáveis
    Dim item As ListItem
    Dim i As Integer
    
    ListViewEntradas.ColumnHeaders.Clear
    ListViewEntradas.ListItems.Clear
    
    ListViewEntradas.Gridlines = True
    ListViewEntradas.View = lvwReport
    ListViewEntradas.FullRowSelect = True
    
    ListViewEntradas.ColumnHeaders.Add Text:="Data de Registro", Width:=60, Alignment:=0
    ListViewEntradas.ColumnHeaders.Add Text:="Tipo", Width:=70, Alignment:=0
    ListViewEntradas.ColumnHeaders.Add Text:="Valor", Width:=80, Alignment:=0
    ListViewEntradas.ColumnHeaders.Add Text:="Categoria", Width:=60, Alignment:=0
    ListViewEntradas.ColumnHeaders.Add Text:="Cliente/ Fornecedor", Width:=120, Alignment:=0
    ListViewEntradas.ColumnHeaders.Add Text:="CPF/ CNPJ", Width:=100, Alignment:=0
    ListViewEntradas.ColumnHeaders.Add Text:="Data de Pagamento", Width:=70, Alignment:=0
    ListViewEntradas.ColumnHeaders.Add Text:="Status", Width:=60, Alignment:=0
    ListViewEntradas.ColumnHeaders.Add Text:="Observação", Width:=100, Alignment:=0
    
    For i = 2 To Planilha2.Range("A1000000").End(xlUp).Row
    
        If (ComboTipo.Text = "" Or ComboTipo.Text = Planilha2.Range("b" & i)) And _
            (ComboStatus.Text = "" Or ComboStatus.Text = Planilha2.Range("h" & i)) And _
            TextInicio.Text <= CDate(Planilha2.Range("g" & i)) And _
            TextFim.Text >= CDate(Planilha2.Range("g" & i)) Then
            
            'And _
            'TextInicio.Text <= CDate(Planilha2.Range("g" & i)) And _
            'TextFim.Text >= CDate(Planilha2.Range("g" & i))
            
            Set item = ListViewEntradas.ListItems.Add(Text:=Planilha2.Range("a" & i))
            item.SubItems(1) = Planilha2.Range("b" & i)
            item.SubItems(2) = Planilha2.Range("c" & i)
            item.SubItems(3) = Planilha2.Range("d" & i)
            item.SubItems(4) = Planilha2.Range("e" & i)
            item.SubItems(5) = Planilha2.Range("f" & i)
            item.SubItems(6) = Planilha2.Range("g" & i)
            item.SubItems(7) = Planilha2.Range("h" & i)
            item.SubItems(8) = Planilha2.Range("i" & i)
        
         End If
    
    Next


End Sub

Answer 1

Hail, my dear!

The problem is that you are comparing data with a string that is not date (and sometimes even an empty string).

If you leave one comparison at a time on If, will see that the problem is not with the text comparisons of ComboBoxes, Yes with date comparisons. When trying to fill in both dates, it worked.

Therefore, to make it work even without one (or both) filled in dates, just ensure that the comparison is valid even if the user does not fill in a date.

There are a few ways to do this, but I chose to declare a variable of type Boolean to check if it is to filter for each of the dates. If the value of TextInicio or TextFim is not of the date type, the respective variable shall be False; if it is date type, it can be used as a problem-free search parameter.

The error you described occurs only when Excel evaluates CDate(TextInicio.Text) of the empty text box, or when comparing this value with a date. Therefore, it is necessary to put the comparison of TextInicio.Text within a If, to run only if it is actually a date.

To get around this, I created a boolean variable bolPassouFiltros. It starts true, and each filter can turn it into a fake. Only if it passes through all of it remains true and the line is inserted into the ListView. Strictly speaking, this is only a dismemberment of If only, but it is one of the ways to avoid the error of the dates.

In the end, the code went like this:

Sub LISTAR_ENTRADAS()

    'Declaração das variáveis
    Dim item As ListItem
    Dim i As Integer
    Dim bolFiltrarDtInicio As Boolean, bolFiltrarDtFim As Boolean, bolPassouFiltros As Boolean
    
    ListViewEntradas.ColumnHeaders.Clear
    ListViewEntradas.ListItems.Clear
    
    ListViewEntradas.Gridlines = True
    ListViewEntradas.View = lvwReport
    ListViewEntradas.FullRowSelect = True
    
    ListViewEntradas.ColumnHeaders.Add Text:="Data de Registro", Width:=60, Alignment:=0
    ListViewEntradas.ColumnHeaders.Add Text:="Tipo", Width:=70, Alignment:=0
    ListViewEntradas.ColumnHeaders.Add Text:="Valor", Width:=80, Alignment:=0
    ListViewEntradas.ColumnHeaders.Add Text:="Categoria", Width:=60, Alignment:=0
    ListViewEntradas.ColumnHeaders.Add Text:="Cliente/ Fornecedor", Width:=120, Alignment:=0
    ListViewEntradas.ColumnHeaders.Add Text:="CPF/ CNPJ", Width:=100, Alignment:=0
    ListViewEntradas.ColumnHeaders.Add Text:="Data de Pagamento", Width:=70, Alignment:=0
    ListViewEntradas.ColumnHeaders.Add Text:="Status", Width:=60, Alignment:=0
    ListViewEntradas.ColumnHeaders.Add Text:="Observação", Width:=100, Alignment:=0
    
    ' Pega as datas de pesquisa
    If IsDate(TextInicio.Text) Then
        bolFiltrarDtInicio = True
    Else
        bolFiltrarDtInicio = False
    End If
    
    If IsDate(TextFim.Text) Then
        bolFiltrarDtFim = True
    Else
        bolFiltrarDtFim = False
    End If
    
    ' Preenche a ListView
    For i = 2 To Planilha2.Range("A1000000").End(xlUp).Row
        bolPassouFiltros = True
        If Not (ComboTipo.Text = "" Or ComboTipo.Text = Planilha2.Range("b" & i)) Then bolPassouFiltros = False
        If Not (ComboStatus.Text = "" Or ComboStatus.Text = Planilha2.Range("h" & i)) Then bolPassouFiltros = False
        If bolFiltrarDtInicio = True Then If Not (TextInicio.Text <= CDate(Planilha2.Range("g" & i))) Then bolPassouFiltros = False
        If bolFiltrarDtFim = True Then If Not (TextFim.Text >= CDate(Planilha2.Range("g" & i))) Then bolPassouFiltros = False
            
        If bolPassouFiltros = True Then
            Set item = ListViewEntradas.ListItems.Add(Text:=Planilha2.Range("a" & i))
            item.SubItems(1) = Planilha2.Range("b" & i)
            item.SubItems(2) = Planilha2.Range("c" & i)
            item.SubItems(3) = Planilha2.Range("d" & i)
            item.SubItems(4) = Planilha2.Range("e" & i)
            item.SubItems(5) = Planilha2.Range("f" & i)
            item.SubItems(6) = Planilha2.Range("g" & i)
            item.SubItems(7) = Planilha2.Range("h" & i)
            item.SubItems(8) = Planilha2.Range("i" & i)
        End If
    Next
End Sub

The other functions have not changed.

Another way, even simpler, to solve, is to create two variables of the type Date and, before its If, rotate another If: if IsDate (TextInicio.Text), the variable shall be equal to TextInicio.Text; if not, the variable shall be 0 (in the case of the start date) or a very high date (in the case of the end date). The problem with this approach is that, although it’s simpler, the highest date you can use will arrive one day, then it’ll be like a millennium bug in your software.

Finally, a tip: you can configure the property MaxLength of the text boxes once, at development time (your code does it every KeyPress, which implies unnecessary processing).