Returning value via VBA

Question

I created a database in Excel that should accept only 1 CPF. So I created the following function that checks and prevents repeated entries from being entered.

Function verifica_cpf(cpf As Integer) As Boolean

Dim i As Integer, intValueToFind As Long, x As Boolean
cpf = False

intValueToFind = Range("i3").Value

Sheets("Banco de Dados").Select
NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count

For i = 3 To NumRows
    If Cells(i, 1).Value = intValueToFind Then
        MsgBox ("CPF já cadastrado na linha " & i & vbNewLine & "Dados não inseridos no banco")
        x = True
    End If
Next i

Return cpf   ' Esta linha apresenta o erro de "fim de instrução esperado" ao compilar

End Function

When the code was inside my main macro, it worked smoothly. Mistakes started when I migrated her to a separate function to get more organized.

In the main sub it is called as follows:

Private Sub Cadastrar()

Sheets("Cadastrar").Select

Dim cpf = verifica_cpf() ' Esta linha apresenta o erro de "fim de instrução esperado" ao compilar
.
.
.
end sub

Answer 1

The function is being called incorrectly and VBA does not use Return functions. VBA syntax is a little different, according to the following code:

Function verifica_cpf(cpf As Long) As Boolean

    Dim i As Long

    Sheets("Banco de Dados").Select
    NumRows = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 3 To NumRows
        If Cells(i, 1).Value = cpf Then
            MsgBox ("CPF já cadastrado na linha " & i & vbNewLine & "Dados não inseridos no banco")
            verifica_cpf = True
            Exit Function
        End If
    Next i

    verifica_cpf = False

End Function

Private Sub Cadastrar()
    Dim cpf As Long
    Dim x As Boolean
    cpf = InputBox("Digite o CPF: ")

    x = verifica_cpf(cpf)
    If x Then
        MsgBox ("É Verdadeiro")
    Else
        MsgBox ("É Falso")
    End If
End Sub

Obs.: A loop in each row can be time consuming, there is other ways to locate. Each with a different performance. The fastest is the use of Arrays (Variant Array, Scripting.Dictionary or Collection), because it decreases the interaction between the VBA and the Excel spreadsheet, therefore, it is the most recommended for large databases. However, the easiest one is the Find Method.