domingo, 2 de enero de 2011

VBA: Concatenar elementos de una lista.

Me encontré con un problema planteado por un compañero; éste debía generar desde una lista de elementos, en una sola celda la unión de todos estos elementos, separados por una coma cada uno de ellos. Es decir, quería CONCATENAR todos los elementos de su lista.
El asunto puede parecer obvio y sencillo si aplicamos la función estándar de Excel
=CONCATENAR(texto_1; texto_2;...)
o bien empleando el operador &('ampersand' o 'y comercial')
=texto_1&texto1&...
pero cuando hablamos de un número de elementos muy alto o variable según la lista, la cosa se puede complicar... y este era el caso que me llevó a pensar si mediante una macro se podría facilitar la labor.

Supongamos un listado de países que deseamos tener unidos en un única cadena de elementos separados por comas. Este listado se encuentra en el rango de celdas A1:A10, aunque este rango es variable dependiendo del número de paises con el que trabajamos...

VBA: Concatenar elementos de una lista.


Nos aprovecharemos de lo visto en un post anterior sobre matrices - ARRAY, para construir nuestra macro.
El código siguiente lo podremos incluir bien en un Módulo del Editor de VBA (Alt + F11) o bien dentro de la Hoja deseado dentro del Explorador de proyectos del Editor de VBA (Alt + F11).

'numera como 1 el primer elemento de la matriz
Option Base 1
Sub
ConcatenaArray()
Dim matriz As Variant
Dim Matr() As Variant

matriz = Range("A1", Range("A" & Rows.Count).End(xlUp).Address).Value
Total = UBound(matriz,1)
x=1
For i = 1 To Total
If matriz(i, 1) <> "" Then
'redefinimos el Array-matriz preservando los elementos
ReDim Preserve Matr(x)
Matr(x) = matriz(i, 1)
x = x + 1
End If
'la función JOIN(matriz, delimitado por)
Cells(1, 2).Value = Join(Matr, ", ")
Next i
End Sub


Hemos logrado al ejecutar nuestra macro que en la celda B1 nos aparezcan concatenados y separados por comas todos los elementos de nuestra lista de paises, independientemente del número de elementos de esta lista.
La clave de esta macro ha sido el uso de la función de VBA JOIN(matriz, delimitador), con la que hemos conseguido nuestra meta, i.e., devuelve una cadena que se creó uniendo subcadenas contenidas en una matriz de una dimensión.

VBA: Concatenar elementos de una lista.

4 comentarios:

  1. Algunas consideraciones...
    . la línea
    Cells(1, 2).Value = Join(Matr, ", ")
    debería estar fuera del ciclo For ... Next
    . si los valores a concatenar son demasiados
    se producirá un error de 'Memoria insuficiente'
    ya que las celdas tienen un límite de caracteres
    . otro método

    Option Base 1
    Private Declare Sub CopyMemory Lib "Kernel32" Alias "RtlMoveMemory" (dest As Any, Source As Any, ByVal bytes As Long)

    Sub ConcatenaArray2()
    Dim matriz As Variant
    Dim Matr() As Variant
    matriz = Range("A1", Range("A" & Rows.Count).End(xlUp).Address).Value
    Total = UBound(matriz, 1)
    ReDim Matr(Total)
    CopyMemory Matr(1), matriz(1, 1), Total * 16
    Cells(1, 2).Value = Join(Matr, ", ")
    End Sub

    ResponderEliminar
  2. Muchas gracias por el aporte...
    siempre se agradecen otras ideas
    ;-)

    Me gustaría me explicaras la instrucción
    CopyMemory Matr(1), matriz(1, 1), Total * 16
    Saludos!!

    ResponderEliminar
  3. pues no hay mucho (o sí!!!) por explicar, es una API
    de Windows...
    la expresión Total * 16 es relativa, podría ser Total * 22
    fijate en la ayuda sobre el tamaño de los tipos de datos

    te dejo un par de links...
    http://www.unpocodelectronica.netau.net/byval-byref-y-punteros-como-manejar-argumentos-correctamente-en-visual-basic-6

    http://vexpert.mvps.org/Tips/tips_api.htm

    ResponderEliminar
  4. Muy bueno, gracias!!!

    ResponderEliminar