martes, 21 de diciembre de 2010

VBA: definición de una matriz -ARRAY.

Llevaba un tiempo queriendo explicar el uso de la función Array, como código VBA, dentro de una macro.
Esta función se define de manera muy sencilla, con un único argumento, que corresponde a una lista de valores delimitados por comas.

Lo siguiente a explicar antes de emplear esta función ARRAY será identificar el código de los diferentes caracteres que correspondan a ciertas letras; es decir, sabemos que cada caracter/letra que podamos usar equivale a un código numerado entre 1 y 255 que especifica el caracter deseado, este caracter forma parte del conjunto de caracteres empleado por nuestro sistema.

Podemos realizar una prueba muy sencilla de esta equivalencia en nuestra hoja de cálculo. Escribimos en la celda A1
=CARACTER(FILA())
y arrastramos 255 filas hacia abajo, obteniendo un listado de equivalencias de cada uno de los 255 códigos con el caracter correspondiente.

En concreto, nuestros códigos buscados son los números 70, 69, 76, 73, 90, 32, 78, 65, 86, 73, 68, 65 y 68, en ese orden preciso.

Estamos listos para empezar a construir nuestra macro. Macro que tras su ejecución insertará un texto en nuestra hoja de cálculo.
Previo al código, explicaré dos funciones VBA que voy a emplear, y que tienen sentido al trabajar con grupos de valores (como es nuestro caso al trabajar con una matriz). Hablo de:
  • LBOUND(nombre_matriz,[dimensión]): que nos devuelve el subíndice más pequeño disponible para la dimensión indicada de la matriz.

  • UBOUND(nombre_matriz,[dimensión]): que nos devuelve el subíndice mayor disponible para la dimensión indicada de la matriz.


El código es el siguiente y lo incluiremos desde del editor de VBA (Alt+F11) en un módulo, como cualquier otra macro que deseemos ejecutar, lo que nos permitirá ejecutarla en cualquier hoja activa de nuestro libro de Excel:

'macro para crear un texto determinado.
Sub feliz()
Dim primero, ultimo As Variant

'se definen los códigos de los caracteres a emplear con una matriz - ARRAY.
Matriz = Array(70, 69, 76, 73, 90, 32, 78, 65, 86, 73, 68, 65, 68)
primero = LBound(Matriz)
ultimo = UBound(Matriz)

'damos formato al rango de celdas
With Range("A1:M1")
.Font.Name = "Arial"
.Font.Size = 14
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With

For elto = primero To ultimo
i = 1
'realizamos un ciclo DO LOOP UNTIL hasta que se consiga
'un valor definido en la matriz

Do
'ralentiza el proceso
For j = 1 To 500000
Next j
'fin del retardo
i = i + 1
Cells(1, elto + 1).Font.ColorIndex = Int(Rnd() * 56)
Cells(1, elto + 1).Value = Chr(i)
Loop Until i = Val(Matriz(elto))

Next elto
End Sub



Hemos conseguido que al ejecutar la macro feliz se introduzca en un rango definido por nosotros (A1:M1) unos caracteres determinados en un ARRAY (o matriz de valores); pero para darle un efecto diferente (como de caida) hemos empleado un bucle o ciclo tipo DO... LOOP UNTIL, de tal forma que el valor de cada celda del rango recorre todos los caracteres hasta encontrar el correspondiente al elemento de la matriz; además para ralentizar o retardar el proceso de ejecución, y poder visualizarlo mejor, he incluido una instrucción FOR...TO...NEXT.
Por ejemplo, para la primera celda del rango, y por tanto para el primer elemento de la matriz, la macro recorre todos los códigos de caracteres hasta igualarse al 70, cuya correspondencia es la letra F; y así sucesivamente para los 13 elementos de la matriz.

Hasta conseguir que en esas 13 celdas nos aparezca el texto:
'FELIZ NAVIDAD'
mensaje con el que me despido hasta el año que viene....

9 comentarios:

  1. Como siempre excelente aporte.
    Feliz Navidad ExcelForo.

    ResponderEliminar
  2. Estimados amigos de excelforos, tengo el siguiente código el cual me ejecuta una macro que abre una texto delimitado por ; a una plantilla específica, el asunto es que no estoy pudiendo formatear las columanas de texto, por ej: 01, lo muestra como 1 ó 004 lo muestra como4, por favor podrian ayudar,....

    Sub abrir_txt()

    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;C:\carpeta\file.txt", _
    Destination:=Range("A7"))
    .Name = "nuevo2"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = False
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    '.TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = True
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(2)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    End With

    End Sub

    ResponderEliminar
    Respuestas
    1. Buenas.
      No es un problema tanto de la herramienta Texto en columnas como de Formato de celdas; tendrías que aplicar sobre esa columna un formato personalizado de número con tipo 0000, es decir, tantos ceros como quieras mostrar:
      0000 para 0001 ó 0004
      ó bien
      00 para 01 ó 04.
      Slds cordiales

      Eliminar
  3. Hola Ismael Romero como podría hacer un código VBA para:

    1.- Enviar datos de 12 TextBox a una matriz (n filas (Según la cantidad de items que quiero ingresar), 12 columnas) y;

    2.- Luego con esa Matriz(Supongamos que ingrese 5 Items,12 Columnas)
    las envío a un listbox de 10 columnas.

    * JUSTAMENTE AQUÍ QUIERO CONSEGUIR MI PROPÓSITO:
    y es que el listbox solo se puede almacenar 10 columnas, pero yo necesito 12 columnas, entonces una vez almacenado los datos del Listbox

    En vez de enviar el listbox a la hoja de calculo, envío la matriz a la hoja de calculo.

    Nota: el listbox solo me servirá para efecto visuales.

    ResponderEliminar
    Respuestas
    1. Hola Piero,
      no me queda claro cuál es el proceso que necesitas... no entiendo el paso de 12 columnas a 10 del listbox ¿('solo puede almacenar 10 columnas')?.
      En todo caso mover datos desde 12 TextBox a la hoja de cálculo se realizaría por ejemplo
      range("A1").value = textbox1.value
      lógicamente con las celdas a modo de variable...
      Slds

      Eliminar
    2. Hola Ismael Romero, Aqui subo el código (lo he trabajado con formulario, pero no se como enviarte el archivo para que se me entienda mejor).

      El código que a continuación te presento me ha resultado con una excepción, NO PUEDO ENVIAR TODA LA MATRIZ A LA HOJA. Solo se envia el ultimo registro de la matriz.

      MI PROPOSITO DE ENVIAR LOS DATOS DE LOS 12 TEXTBOX A LA MATRIZ, Y LUEGO, DE LA MATRIZ AL LISTBOX, ES JUSTAMENTE, QUE EL LISTBOX SOLO PUEDE ALMACENAR 10 COLUMNAS, Y YO NECESITO 12 COLUMNAS, POR LO TANTO EL LISTBOX SOLAMENTE ME SIRVE COMO EFECTO VISUAL, YA QUE EN REALIDAD LO QUE ALMACENO EN LA HOJA1 ES LA MATRIZ "MatrizAuxiliar(nfilas,12 columnas)"

      ------------------------------------------------------------

      Dim j As Long
      Dim MatrizAuxiliar()

      Private Sub Btn_Agregar_Click()

      Dim k As Long
      Dim x As Long

      'Bucle para enviar datos del textBox a Matriz
      Do Until Me.TextBox1 = ""
      x = x + 1
      'Redimensionamos la matriz para darle el nuevo nro. de elementos, segun se vayan cargando los datos
      'Se observa que estoy enviando todos los 12 textbox a la matriz
      ReDim Preserve MatrizAuxiliar(1 To x, 1 To 12)
      MatrizAuxiliar(x, 1) = Me.TextBox1.Text
      MatrizAuxiliar(x, 2) = Me.TextBox2.Text
      MatrizAuxiliar(x, 3) = Me.TextBox3.Text
      MatrizAuxiliar(x, 4) = Me.TextBox4.Text
      MatrizAuxiliar(x, 5) = Me.TextBox5.Text
      MatrizAuxiliar(x, 6) = Me.TextBox6.Text
      MatrizAuxiliar(x, 7) = Me.TextBox7.Text
      MatrizAuxiliar(x, 8) = Me.TextBox8.Text
      MatrizAuxiliar(x, 9) = Me.TextBox9.Text
      MatrizAuxiliar(x, 10) = Me.TextBox10.Text
      MatrizAuxiliar(x, 11) = Me.TextBox11.Text
      MatrizAuxiliar(x, 12) = Me.TextBox12.Text

      For k = 1 To 12
      Me.Controls("TextBox" & k).Text = ""
      Next


      Loop

      'Enviamos los datos de la matriz al ListBox
      'Sin embargo, solo cargo al listbox 10 columnas de la matriz, porque el listbox solo permite 10 columnas.
      'Para luego, con el boton "enviar datos a hoja", enviar toda la matriz completa (12 columnas)

      For x = 1 To UBound(MatrizAuxiliar, 1)
      Me.ListBox1.AddItem
      Me.ListBox1.List(j, 0) = MatrizAuxiliar(x, 1)
      Me.ListBox1.List(j, 1) = MatrizAuxiliar(x, 2)
      Me.ListBox1.List(j, 2) = MatrizAuxiliar(x, 3)
      Me.ListBox1.List(j, 3) = MatrizAuxiliar(x, 4)
      Me.ListBox1.List(j, 4) = MatrizAuxiliar(x, 5)
      Me.ListBox1.List(j, 5) = MatrizAuxiliar(x, 6)
      Me.ListBox1.List(j, 6) = MatrizAuxiliar(x, 7)
      Me.ListBox1.List(j, 7) = MatrizAuxiliar(x, 8)
      Me.ListBox1.List(j, 8) = MatrizAuxiliar(x, 9)
      Me.ListBox1.List(j, 9) = MatrizAuxiliar(x, 10)


      j = j + 1

      Next




      End Sub


      Private Sub Btn_Salir_Click()
      Unload Me
      End Sub


      Private Sub CommandButton1_Click()
      For t = 1 To 12
      Me.Controls("TextBox" & t) = FormatNumber((t + 100) * Rnd(), 0)
      Next

      End Sub


      Private Sub CommandButton2_Click()
      Dim Rango As Range

      Set Rango = Range("A2")

      Rango.Resize(UBound(MatrizAuxiliar, 1), UBound(MatrizAuxiliar, 2)).Value = MatrizAuxiliar


      End Sub

      Private Sub CommandButton3_Click()
      Range("A2:L100").Clear
      End Sub

      Private Sub UserForm_Initialize()

      Dim k As Integer

      Me.ListBox1.ColumnCount = 10
      Me.ListBox1.ColumnWidths = "55;55;55;55;55;55;55;55;55;55"

      For k = 1 To 12
      Me.Controls("TextBox" & k).Text = FormatNumber((t + 100) * Rnd(), 0)
      Next

      End Sub

      Eliminar
    3. Hola,
      envíame el fichero a
      excelforo@gmail.com
      Slds

      Eliminar
  4. hola que tal buenas tardes, tengo una matriz 3 x 3 e intento hacer q se autoajuste... es decir es una regionalizacion de opoblacion en donde tengo q repartir 27 niños de 3 edades en 3 colonias... pero mi idea no es hacerlo manual sino que lo haga solito

    ResponderEliminar
    Respuestas
    1. Hola Iván,
      cuáles son las pautas para ese 'autoajuste' ???

      Slds

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.