martes, enero 01, 2013

Validación de direcciones de e-mail en Excel

Validación de datos es una herramienta excelente, indispensable diría, en todo modelo de Excel que requiera ingresar datos. Pero, como todo en este mundo, tiene sus limitaciones. Por ejemplo, ¿cómo comprobamos que el usuario ingrese direcciones válidas de correos electrónicos?

Si se tenemos una lista de direcciones válidas, nos basta con usar la opción Lista de validación de datos. Pero, ¿qué hacemos si no contamos con una lista de direcciones pero queremos asegurarnos que la sintaxis de la dirección es correcta? Es decir, abc123@xyz.com es una dirección válida, independientemente de que exista o no; pero abc..123@xyz no lo es).

La solución es usar expresiones regulares (regular expresions). La expresión regular es un patrón que nos permite describir un conjunto de texto sin enumerar sus elementos. No voy entrar en los detalles técnicos, que pueden leer en la nota del enlace, pero si señalar que estos patrones pueden ser usados en Vba. De esta manera podemos crear una función definida por el usuario para validar direcciones de correo electrónico.

La función para validar la sintaxis de una dirección de correo electrónico es

Public Function blnEmailValid(ByVal strEmailAdd As String) As Boolean
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Global = True
        .Pattern = "^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$"
        blnEmailValid = .Test(strEmailAdd)
    End With
End Function


Este código fue publicado por Vishesh en ExcelExperts.com.

En este ejemplo, ponemos la función en la celda D2 para evaluar la dirección ingresada en la celda C2; si la dirección es válida el valor en la celda D2 es VERDADERO, en caso contrario FALSO



También podemos usarla en Validación de Datos, usando el valor de la celda D2 en la opción Personalizada (en este ejemplo, ponemos la función en A2 que podamos hemos ocultado)



20 comentarios:

  1. ¡Muchas gracias, Jorge! Disculpame mi ignorancia, quisiera consultarte cómo podría colocar la función en la celda D2 y en la validación de datos para que pueda funcionar.

    ResponderBorrar
  2. Juan Gabriel,
    el código de la función hay que ponerlo en un módulo común del editor de Vb (puedes acceder al editor desde el menú Programador--Visual Basic o con el atajo Alt+F11).
    Una vez instalada la función, puedes usarla como en el ejemplo: la ponemos en la celda A2 y en validación de datos-fórmula creamos una referencia a esa celda.

    ResponderBorrar
  3. Muchas gracias Jorge, estuve probando y cuando pongo la formula en la celda y presiono enter, aparece el error NOMBRE. ¿Que es el modulo común?

    ResponderBorrar
  4. Juan Gabriel, como puse en el comentario anterior, hay que activar el editor de Vb, agregar un módulo con el menú Insert-Module y en él pegar el código.

    ResponderBorrar
  5. ¡Muchísimas gracias, Jorge, ahora sí me funcionó! ¿Qué significa "insertar un módulo? Perdón por tantas preguntas.

    ResponderBorrar
  6. Buenas tardes. Muchas gracias por el código de verdad es muy útil. Me gustaría saber cuál es la fórmula que ingresas en la validación de datos personalizada para lograr el efecto mostrado en la validación. Pongo la fórmula (blnEmailValid) en la celda A2 y trato de hacer la validación, obligando a que la celda sea "verdadero" pero así escriba un mail correctamente, el argumento no alcanza a cambiar de falso a verdadero, por lo que no logro avanzar.

    De nuevo muchas gracias por su valiosa ayuda.

    ResponderBorrar
  7. Como pongo en la nota, en la validación de datos uso, sencillamente, =A2.
    Cualdo el valor en la celda de control (A2) es FALSO, validación de datos no acepta ese valor.
    No se pueden usar funciones definidas por el usuario directamente en validación de datos.

    ResponderBorrar
  8. Hola Jorge, en una aplicación en la que estuve trabajando yo validaba la entrada de email con esta formula:
    =Y(HALLAR("*@*.*";A1);ESERROR(HALLAR(" ";A1)))
    donde el pattern es *@*.* y además tiene que cumplir que no haya espacios en el string.

    Es básica y se puede mejorar pero sirve.
    Yo la use dentro de "Validación de datos -> Personalizada"

    ¿Se podría utilizar una expresión regular dentro de una formula?

    ResponderBorrar
  9. Juani, gracias por colaborar.
    Toda función definida por el usuario, como la que muestro en esta nota, se puede usar en una fórmula.

    ResponderBorrar
  10. Impecable, muchas gracias!!!!!!

    ResponderBorrar
  11. La expresión regular está mal ya que no admite por ejemplo dominios .info . Me imagino porque se habrá puesto en la expresión regular que a partir del punto solo se admitan 3 caracteres.

    ResponderBorrar
  12. hola, juani,
    yo estoy probando tu formula con HALLAR...y me parece bastante buena.
    Tambien he porbado a hacerlo usando la opción de validación de listas y pegando la expresion pattern en la opcion de personalizada (es decir, que la meto como fórmula directamente) pero me da peor resultado. En conclusion juani, creo que tu formula es más sencilla y util.

    Saludos!

    ResponderBorrar
  13. Efectivamente, la fórmula de Juani es sencilla, pero sólo controla que el texto contenga el símbolo @ y un punto después. Por ejemplo, la dirección A@b@c@example.com es válida si la controlamos con la fórmula de Juani pero en realidad no lo es (se admite sólo un sómbolo@ en la dirección); lo mismo sucedería con la dirección A@email.b
    ¿Cuáles son los problemas que encuentras con el uso de la expresión regular?

    ResponderBorrar
  14. El problema es que si antes de cambiar el texto de la celda C2, la celda D2 estaba en falso, la validación es incorrecta ya que en la secuencia de actualización de Excel, primero valida y luego cambia D2 de Falso a Verdadero

    ResponderBorrar
  15. La validación funciona. Para simplificar puedes hacer esta prueba:
    # - en la celda D2 ponemos la expresión =C2>5
    # - como la celda C2 está vacía, veremos FALSO en la celda D2
    # - ahora ponemos 4 en la celda C2 lo que dispara la validación de datos no permitiendo poner ese valor en la ceda. Si optamos por cancelar, la celda C2 sigue vacía y D2 sigue mostrando FALSO
    # - introducimos 6 en C2; el valor es aceptado y D2 muestra ahora VERDADERO.

    ResponderBorrar
  16. Buenas tardes Jorge.
    Estpy de acuerdo contigo en que utilizando la RegExp es mas seguro, pero se me está presentando un problemka distinto.
    La idea es que en una TABLA se realice la validación del dato ingresado por el usuario, sin que le permita continuar si la dirección de correo no es estructuralmente válida. Mi problema consiste en al ingresar la nueva funcion dentro de la tabla me arroja el error NO SE PUEDE ENCONTRAR UNO DE LOS RANGOS
    Me podrias ayudar?
    Gracias de antemano

    ResponderBorrar
  17. Para realizar la validación hay que usar la la UDF en el formulario de validación de datos. ¿Pod´rias explicar un poco más cómo estás usando al función?

    ResponderBorrar
  18. Buenísimo el tema, me han ayudado muchísimo

    ResponderBorrar
  19. a mi se me ocurrio hacerla asi, tal vez sea muy rebuscada, pero me funciono:

    =Y(NO(ESERROR(SI(ENCONTRAR("@",M2,2),VERDADERO,FALSO))),ESERROR(ENCONTRAR("@",M2,((ENCONTRAR("@",M2,1)+1)))),ESERROR(ENCONTRAR(" ",M2,1)),NO(ESERROR(ENCONTRAR(".",M2,((ENCONTRAR("@",M2,1)+2))))))

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.