martes, 6 de diciembre de 2011

VBA: Crear una matriz (Array) a partir de un rango de Excel.

Alguna vez he hablado del concepto de matriz o Array en el contexto de nuestras macros (ver), normalmente empleándola o definiéndola a partir de elementos constantes, sin embargo, en ocasiones, se hace necesario emplear estas matrices sobre rangos dinámicos alojados en nuestras celdas de nuestras hojas de cálculo.
Hoy hablaré de la forma de crear una Array a partir de los valores de un rango de celdas.
Lo aplicaremos para el supuesto en que deseamos añadir un listado de clientes a un desplegable o Cuadro combinado, que se encuentra en la 'Hoja1' de nuestro Libro de trabajo y que empieza en la celda A2 y no sabemos dónde acaba...

VBA: Crear una matriz (Array) a partir de un rango de Excel.


Nuestro trabajo consistirá en pasar un rango a matriz, para luego emplear los elementos de esa matriz como parte de un ComboBox o desplegable (controles ActiveX).
Lo primero que haremos será incrustar un Cuadro combinado (control ActiveX) en nuestra 'Hoja1', lo que haremos desde la Ficha Programador > Controles > Insertar controles > Controles ActiveX > Cuadro combinado:

VBA: Crear una matriz (Array) a partir de un rango de Excel.


En el siguiente paso, haciendo doble click sobre el Control pasaremos a la ventana de código, donde incluiremos lo siguiente:

Private Sub ComboBox1_gotfocus()
Dim clientes As Long
'cuenta el número de elementos en la Hoja1
clientes = Sheets("Hoja1").Range(Range("A2"), Range("A2").End(xlDown)).Rows.Count

'definimos una Matriz sin dimensiones determinadas
Dim MyArray() As Variant
'sabiendo el número de elementos a incluir, redimensionamos nuestra Matriz
ReDim MyArray(1 To clientes, 1) As Variant
'En caso de error, que continúe
On Error Resume Next
'limpiamos el ComboBox
ComboBox1.Clear
'añadimos cada valor del rango definido de la Hoja1 como elemento de la Matriz
For i = 1 To clientes
    MyArray(i, 1) = Worksheets("Hoja1").Cells(i + 1, 1).Value
    'incluímos cada elemento de la Matriz dentro del Cuadro combinado
    ComboBox1.AddItem MyArray(i, 1)
Next i
End Sub


Lo más importante en este ejemplo ha sido ser capaces de Redimensionar nuestra Matriz 'MyArray' a una dimensión variable en cada momento, ajustada al número de elementos que componen el rango de celdas.
Podemos ver el resultado:

VBA: Crear una matriz (Array) a partir de un rango de Excel.


Comprobando como efectivamente cada nuevo registro añadido en nuestra Hoja de cálculo, es dinámicamente incluido como nuevo elemento dentro de nuestro Cuadro combinado:

VBA: Crear una matriz (Array) a partir de un rango de Excel.

11 comentarios:

  1. Todo perfecto, solo un detalle;
    Hago todo presiamente como lo indicas, al presionar en la flecha del Cuadro combinado, solo me aparece la primera apesar que el combobox esta para 8 lineas.
    Al presionar la flecha (lado derecho del combo)se desplega para 8 lineas pero solo muestra la primera, tengo que darle selecionando la linea y luego al volver si me muestra todas.
    ¿Que puede estar pasando?

    ResponderEliminar
  2. The blog was absolutely fantastic! Lots of great information and
    inspiration, both of which we all need!b Keep 'em coming... you all do
    such a great job at such Concepts... can't tell you how much I, for
    one appreciate all you do!

    ResponderEliminar
  3. Hola Joao,
    realmente no le pasa nada, es que un ComboBox es un 'cuadro combinado', es decir, ofrece dos posibilidades para desplegar datos, uno al presionar una vez sale sólo un primer elemento pero ofreciendo la posibilidad que indicas; la otra es presionando o haciendo doble click en la flecha desplegable para que aparezcan todos los elementos a elegir...
    Slds

    ResponderEliminar
  4. Y asi ... ?

    Private Sub ComboBox1_GotFocus()
    ComboBox1.ListFillRange = ("TesTab")
    End Sub

    Siendo TesTab un nombre dinamico de la forma :

    Se refiere a: =(DESREF(Hoja1!$A$1;1;0;CONTARA(Hoja1!$A$2:$A$20);1))

    ResponderEliminar
  5. Muhcas gracias cllach,
    efectivamente hay muchas formas de 'rellenar' con elementos un ComboBox...
    en esta entrada la finalidad es más bien enseñar cómo se puede crear una Matriz(Array) en VBA con valores de un rango de celdas, y una utilidad de esa matriz.
    Un cordial saludo y gracias nuevamente por el aporte!!

    ResponderEliminar
  6. Con arrays y rangos, tambien vale :
    Dim arrayRange as variant ' sin decir que sera

    arrayRange=Sheets("Hoja1").Range(Range("A2"), Range("A2").End(xlDown))

    y despues
    arrayRange=Range("TestTab") ' no importa que cambie ni el tamaño ni las dimensiones

    y despues, etc ....
    sin redimensionar ni recorrer el rango

    la diferencia de velocidad es muy grande y se incrementa proporcionalmente al tamaño del array

    Y gracias por el blog.

    BW- cllach

    ResponderEliminar
  7. Como siempre muchas gracias cllach,
    en bastantes ocasiones he empleado los métodos que describes, pero no lo había hecho hasta ahora para definir una Matriz-Array.
    Siempre se agradecen los aportes que nos enseñan algo nuevo...
    Un cordial saludo!!

    ResponderEliminar
  8. Respuestas
    1. Hola Erick, que tal estás???
      Espero te encuentres bien.

      Supongo estás preguntando sobre el ejemplo del usuario anterior cllach.
      TestTab es un nombre definido en la hoja de cálculo, al que luego llamamos desde la macro.

      Un saludo cordial

      Eliminar
  9. Buenas tardes. Por ejemplo tengo dos columna llamada "cliente" y en cada fila tiene un dato Samsung, sony, LG, wirlpool... hasta n clientes , así una columna "Productos" y cada fila tiene los diversos productos que fabrican...., pero tengo dos listas desplegables para seleccionar el cliente.

    como hacer para que en la segunda lista desplegable solo me arroje los productos que fabrican?

    ResponderEliminar