jueves, 1 de marzo de 2012

VBA: objeto Collection para lograr listar valores únicos.

En una entrada anterior explicaba cómo poder obtener un listado de valores únicos desde un rango de celdas en Excel. Lo que veremos en el post de hoy es cómo conseguir lo mismo con una macro, mediante el uso de un objeto Collection.
Debemos aclarar antes de empezar qué es una Colección dentro del VBA, la definición es sencilla, ya que sólo es un conjunto ordenado de elementos a los que se puede hacer referencia como una unidad; pudiendo trabajar sobre ese conjunto igual que ,por ejemplo, con una Array (Matriz), recorriendo sus diferentes elementos con bucles tipo FOR...NEXT o cualquier otro.
Para comprobar la eficacia de estas Collection, trabajaremos con la misma tabla de datos que en la entrada a la que nos referíamos al comienzo de este post. Recordemos que hemos asignado un nombre a nuestro rango de estudio:
Equipo =Hoja1!$B$2:$B$20

VBA: objeto Collection para lograr listar valores únicos.


Abriremos nuestro editor de VBA (Alt+F11) e insertaremos un módulo, en el cual añadiremos el siguiente código:

Sub elementosunicos()

Dim celda As Object
Dim i As Integer

'generamos la coleccion
Set unicos = New Collection
'loop en todas las celdas y agregarlas a la coleccion
For Each celda In Range("equipo")
    'cuando encuentre un item repetido, daría un error
    'que salvamos con la instrucción On Error Resume Next
    On Error Resume Next
    'por tanto, nuestra coleccion solo agrega elementos no repetidos
    'objeto.Add item, key, before, after
    'ocurre un error si una key especificada duplica la key de un miembro existente de la colección
    unicos.Add celda.Value, CStr(celda.Value)
    On Error GoTo 0
Next celda

'escribir los datos unicos en la Hoja de cálculo
For i = 1 To unicos.Count
    Sheets(1).Range("E2").Offset(i - 1, 0).Value = unicos(i)
Next i

End Sub


Si asignamos nuestra macro a un botón (control de formulario) en nuestra hoja de cálculo de Excel, al ejecutarla conseguiremos nos lleve los registros únicos, sin repetir, a partir de la celda E2 hacia abajo.

VBA: objeto Collection para lograr listar valores únicos.


La clave por la que hemos usado una Collection es que éstas no admiten registros repetidos, por tanto en el proceso de incorporar elementos a esta Collection cada vez que intentaramos añadir (Add) un elemento repetido daría un error, hecho que salvamos con la instrucción On Error Resume Next/On Error Go To 0, es decir, cada vez que haya un error en la introducción de elementos pasa al siguiente. De esta forma conseguimos una colección de elementos únicos, que finalmente reportaremos a nuestra hoja de cálculo desde la celda E2.
Obteniendo un resultado similar a la de la fórmula matricial:
{=SI.ERROR(INDICE(Equipo;K.ESIMO.MENOR(SI(COINCIDIR(Equipo;Equipo;0)=FILA(INDIRECTO("1:"&FILAS(Equipo)));COINCIDIR(Equipo;Equipo;0);"");FILA(INDIRECTO("1:"&FILAS(Equipo)))));"")}

11 comentarios:

  1. Hola ExcelForo:
    Por favor quisiera saber si el resultado de la celda F2:F5, me lo puede dar ordenado alfabéticamente, tanto en la macro como en la fórmula matricial.
    Un saludo
    Atte:
    René

    ResponderEliminar
    Respuestas
    1. Hola René...
      devolver ordenados de manera ascendente o descendente, alfabéticamente, en la macro es posible; yo iría a lo más sencillo, añadiría un código de ordenación después de las últimas líneas con el objeto Sort.
      Respecto de la función matricial, se podría complicar bastante la fórmula.. ya que además en este caso son valores de texto, con lo que la ordenación se dificulta, quizá con valores numéricos podría conseguirse.
      Slds

      Eliminar
  2. Como hacer para que salte valores en blanco, me explico, que no cuente la celda en blanco con un valor de la lista??

    No sé si me he explicado bien.

    Gracias

    ResponderEliminar
  3. Como hacer para que salte valores en blanco, me explico, que no cuente la celda en blanco con un valor de la lista??

    No sé si me he explicado bien.

    Gracias

    ResponderEliminar
  4. Como puedo hacer para que me muestre solamente los equipos que son de la fecha 12/09/2012 usando estos objetos collection?

    ResponderEliminar
    Respuestas
    1. Hola,
      dentro del bucle for... next añade un IF THEN
      que evalue la fecha
      IF celda.offset(0,-1).value="12/09/2012" then
      unicos.Add celda.Value, CStr(celda.Value)
      end if

      quizá tendrás que luchar con la fecha.. pero la idea es esa.

      Un cordial saludo

      Eliminar
  5. Hola Excelforo!

    Mi inquietud es la siguiente: ¿Es posible realizar algo similar a lo que se muestra en la línea de código que adjunto más abajo en una Collection sin utilizar FOR... NEXT?

    arrayServicios = Worksheets("BDServicios").Range("Servicios").Value

    De antemano gracias por su gran apoyo a todos los que estamos iniciando.

    ResponderEliminar
    Respuestas
    1. Hola Luís,
      en principio la forma de identificar 'duplicados' es recorrer los diferentes valores de rango o matriz... puedes emplear cualquier bucle, pero yo no conozco otra manera de recorrer algún objeto
      Saludos

      Eliminar