Error handling in Visual Basic Editor for email field in excel form

Question

Hi folks,

I am currently coding a form in excel. I was wondering if there was a way to search the contents of a field for certain characters. I was thinking about using this method to construct a code to check for the integrity of the data entered within the email field. It would look for an "@" and a "." and probably output a boolean value (true or false) if there are not there.

Thank you in advance.

Answer 1

You could pass the value into a function like this:

Function blnValidEmail(strText As String) As Boolean

    Dim intPosAt As Integer
    Dim intPosDot As Integer

    'finds the position of the @ symbol'
    intPosAt = InStr(strText, "@")

    'finds the position of the last full stop'
    'checks the last full stop because you might'
    'have an address like [email protected]'
    intPosDot = InStrRev(strText, ".")

    'makes sure that both exist'
    If intPosAt > 0 And intPosDot > 0 Then
        'makes sure that there is a fullstop after the @'
        If intPosDot > intPosAt Then
            blnValidEmail= True
        End If
    End If

End Function