Manejar matrices con VBA

Puede descargar el archivo manejarMatrices.xlsm

Vamos a trabajar con matrices en Excel programando en VBA (Visual Basic for Applications).

Lo interesante de este caso es ver que para dejar el contenido de una matriz en la hoja de cálculo lo que debemos hacer es lo que nos dice la intuición.

Rango = Matriz 'respetando las dimensiones

Option Base 1 'Las matrices empiecen en 1 y no en 0

Sub manejaMatriz()
Dim A As Variant
Dim B As Variant
'para asignar un rango a una matriz
A = Range("B4:E9")
'para asignar una matriz a un rango
B = WorksheetFunction.Transpose(A)
'para imprimir una matriz en un rango
Range("B11:G14") = B
End Sub

Sub aleatorios()
Dim A(6, 4) As Double
Dim inicio As Range
Dim final As Range
Randomize 'para mejorar la aleatoriedad
For i = 1 To 6
  For j = 1 To 4
    'números aleatorios [0,1) a dos decimales
    A(i, j) = Int(Rnd() * 100) / 100
  Next j
Next i
Set inicio = Cells(4, "B")
Set final = Cells(9, "E")
Range(inicio, final) = A
End Sub

Sub multiplicaMatriz()
Dim A() As Variant
Dim B() As Variant
Dim C() As Variant
A = Range("B4:E9")
B = Range("B11:G14")
C = WorksheetFunction.MMult(A, B)
Range("B16:G21") = C
End Sub

Sub invierteMatriz()
Dim A() As Variant
A = Range("B23:D25")
Range("B27:D29") = WorksheetFunction.MInverse(A)
Range("B27:D29").Interior.Color = 6750156
Call extraeElementos
End Sub

Sub extraeElementos()
Dim A() As Variant
Dim origen As Range
Range("M4:AC30").Clear
Range("M4:AC30").Interior.Color = 13434879 'amarillo
Set origen = Range("L3")
A = Range("B27:D29")
fila = [L14]
columna = [S2]
For i = 1 To 3 'recorremos las 3 filas de la matriz
  For j = 1 To 3 'recorremos las 3 columnas de A
    With origen.Offset(fila + i - 1, columna + j - 1)
    .Value = A(i, j)
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    End With
  Next j
Next i
Range(origen.Offset(fila + i - 4, columna + j - 4), origen.Offset(fila + i - 2, columna + j - 2)).Interior.Color = 6750156
End Sub

Sub copia()
Dim A As Variant
A = Range("B4:D6")
Range("B23:D25") = A
Range("B4:D6,B23:D25").Interior.ColorIndex = 8
End Sub

Sub Borra()
Range("B4:AD32").ClearContents
[S2] = 1: [L14] = 1
Range("B4:G29").Interior.Pattern = xlNone
Range("M4:AC30").Clear
Range("M4:AC30").Interior.Color = 13434879 'amarillo
End Sub

También es interesante ver el pos siguiente.

• Propiedad Resize para manejar rangos en Excel

Propiedad Resize para manejar rangos en Excel

Puede descargar el archivo resize.xlsm

Cuando programamos macros en Excel usando VBA es muy habitual trabajar con rangos. Una propiedad de los rangos que modifica su tamaño es resize.

Rango.Resize(filas, columnas).Select

Veamos algunos ejemplos de su uso.

Resize1

Da color aleatorio a las celdas del rango B4:E8.

Sub Resize1()
Dim rojo As Byte, verde As Byte, azul As Byte
Dim R As Range
Set R = Range("B4")
Range("A1").Select
Randomize 'elegimos colores aleatorios
rojo = Int(Rnd() * 100) + 100
verde = Int(Rnd() * 100) + 100
azul = Int(Rnd() * 100) + 100
R.Resize(5, 4).Interior.Color = RGB(rojo, verde, azul)
End Sub

Observe que después de Resize(5.4) se ha de poner algo, o bien, .Select para seleccionar, o bien, .Interior.Color=RGB(rojo, verde, azul) como en este caso. Pero si después de Resize se deja sin poner nada nos dará error.

Resize2

Permite seleccionar un rango de 3 filas y 2 columnas usando como celda de origen la B4.

Range("B4").Resize(3, 2).Select

Sub Resize2()
'Seleccionamos un rango de 3 filas y 2 columnas
'La celda de origen es B4
Range("B4").Resize(3, 2).Select
End Sub

Resize3

Permite seleccionar un rango de 3 filas en la primera columna usando como celda de origen la B4.

Range("B4").Resize(3).Select

Sub Resize3()
'Seleccionamos un rango de 3 filas de la primera columna
'La celda de origen es B4
Range("B4").Resize(3).Select
End Sub

Resize4

Permite seleccionar un rango de 3 columnas en la primera fila usando como celda de origen la B4.

Range("B4").Resize(, 3).Select

Sub Resize4()
'Seleccionaremos un rango de 3 columnas de la primera fila
'La celda de origen es B4
Range("B4").Resize(, 3).Select
End Sub

Resize5

Permite seleccionar un rango de 2 columnas en la primera fila usando como celda de origen la B4 y hasta la fila 8, ya que el rango indicado es hasta la E8.

Range("B4:E8").Resize(, 2).Select

Sub Resize5()
Range("B4:E8").Resize(, 2).Select
End Sub

Resize6

Permite seleccionar un rango de 3 columnas en la primera fila usando como celda de origen la A4.

Range("B4").Offset(, -1).Resize(, 3).Select

Es una maravilla poder usar Offset para cambiar el rango de referencia, y usar, como en el ejemplo, valores negativos. Esto aporta flexibilidad al manejar gran cantidad de rangos cambiantes.

Sub Resize6()
Range("B4").Offset(, -1).Resize(, 3).Select
End Sub

Resize7

Copia el rango amarillo (B11:E15) en el rango superior (B4:E8).

Range("B4").Resize(5, 4) = A

Este es un uso muy útil de la propiedad Resize ya que nos permite depositar (imprimir) de golpe todo un rango o el contenido de una matriz.

Sub Resize7()
Dim A As Variant
Worksheets("Hoja1").Range("A1").Select
A = Range("B11:E15")
Range("B4").Resize(5, 4) = A
End Sub

Resize8

Hace lo mismo que la macro anterior, copia el rango amarillo (B11:E15) en el rango superior (B4:E8).

Range("B4").Resize(R.Rows.Count, R.Columns.Count) = A

Este procedimiento realiza el mismo trabajo que el anterior aunque lo hemos programado de otra forma. En este caso, creamos las variables n y m que calculan el número de filas y columnas respectivamente del rango R. De esta forma hacemos más flexible el código ya que evitamos tener que dar nosotros los parámetros a la propiedad Resize.

Sub Resize8()
Dim A As Variant
Dim R As Range
Worksheets("Hoja1").Range("A1").Select
Set R = Range("B11:E15")
A = R
n = R.Rows.Count
m = R.Columns.Count
Range("B4").Resize(n, m) = A
End Sub

Resize9

Genera una matriz de números aleatorios y la imprime en una posición inicial variable y con un tamaño variable.

Range(Cells(pf, pc), Cells(pf, pc)) .Resize(n, m) = A

Seguidamente se muestra el código de ejemplo utilizado para ilustrar la versatilidad de la propiedad Resize.

Sub Resize9()
Dim A As Variant
Dim R As Range
Dim tf As Byte, tc As Byte, pf As Byte, pc As Byte
Worksheets("Hoja2").Activate
Range("E7:AQ45").Clear
Randomize
Range("A1").Select
tf = Int(Rnd() * 20) + 1 'tamaño:fila
tc = Int(Rnd() * 20) + 1 'tamaño: columna
pf = Int(Rnd() * 20) + 7 'posición inicial: fila
pc = Int(Rnd() * 20) + 5 'posición inicial: columna
[C6] = tf
[D6] = tc
[C5] = pf
[D5] = pc
Set R = Range(Cells(pf, pc), Cells(pf + tf - 1, pc + tc - 1))
R.Interior.Color = RGB(0, 255, 100)
A = R
n = R.Rows.Count
m = R.Columns.Count
ReDim A(n, m)
For i = 1 To n
  For j = 1 To m
    A(i, j) = Int(Rnd() * 100)
  Next j
Next i
Range(Cells(pf, pc), Cells(pf, pc)).Resize(n, m) = A
End Sub

Veamos un GIF animado donde se aprecia que el rango varía en posición y tamaño de forma aleatoria.

Conviene ver cómo se resolvió en otros casos la necesidad de imprimir de golpe toda una matriz, incluso de gran tamaño. Recomiendo ver los siguientes enlaces a otros post publicados.

• Transferir los datos de un Rango a un Array
• Ordenar un millón de valores

Pasar matrices a una función

Pasar matrices a una función en programación de macros para Excel es de lo que nos vamos a ocupar en este Post. También veremos el caso en el que la función devuelve una matriz. Disponemos de un procedimiento (Sub) que llama a una función (Function), la cual devuelve o bien un valor, o bien una matriz.

Primer caso

La función recibe una matriz y devuelve un único valor.

Código:

Sub principal()
Dim A(2)
A(1) = 1
A(2) = 2
MsgBox sumatorio(A)
End Sub

Private Function sumatorio(B)
Dim s
s = B(1) + B(2)
sumatorio = s
End Function

Segundo caso

La función no recibe nada y devuelve una matriz.

Es importante definir la función Genera escribiendo el tipo y unos paréntesis detras del tipo de variable:

Function Genera() As Byte()

Estos últimos paréntesis son imprescindibles.

Código:

Sub Ejecuta()
Dim Z() As Byte
Z = Genera()
MsgBox (Z(1) & ", " & Z(2))
End Sub

Private Function Genera() As Byte()
Dim A(2) As Byte
A(1) = 1: A(2) = 2
Genera = A
End Function

Otro ejemplo que se ajustaría a este segundo caso se da cuando se pide a la matriz que genere ciertos números aleatorios. Por ejemplo, la función podría generar números aleatorios sin repetición en cierto rango de valores enteros.

Tercer caso

La función recibe una matriz y devuelve otra matriz.

Código:

Sub Transforma()
Dim Z() As Byte
Dim p, q
p = 4: q = 5
Z = Dobla(p, q)
MsgBox (Z(1) & ", " & Z(2))
End Sub

Private Function Dobla(x, y) As Byte()
Dim A(2) As Byte
A(1) = x * 2
A(2) = y * 2
Dobla = A
End Function

Transferir los datos de un Rango a un Array

Descargar el fichero pasa_matriz.xlsm.

Deseamos transferir los datos que existen en el rango de una hoja a una matriz (ARRAY) mediante una macro. Vamos a verlo por varios métodos. El primer método es el clásico, cargando dato a dato en la matriz. Otro método nos permite cargar la matriz de una sola vez. También vamos a conocer cómo se determina la dimensión de una matriz.

Método Clásico

Podemos utilizar el método clásico que consiste en tomar los datos uno a uno del rango e ir alimentando la matriz. Esto se puede hacer con bucles FOR..NEXT.

Imagine que el rango es B4:D13 y esta lleno de datos (algunos numéricos y otros alfanuméricos). Queremos trasladar todos los datos a la matriz X de dimensión X(1 to 10, 1 to 3).

Utilicemos el método clásico consistente en pasar los datos del rango a la matriz de uno en uno. Esto se hace utilizando, en este caso, dos bucles FOR..NEXT anidados.

Sub pasardatos()
Dim MiMatriz(1 To 10, 1 To 3)
Dim i As Integer, j As Integer
For i = 1 To 10
  For j = 1 To 3
    MiMatriz(i, j) = Worksheets("Hoja1").Cells(3 + i, j + 1).Value
  Next j
Next i
End Sub

El otro método es pasar el rango de datos de una sola vez a la matriz.

Pruebe con esto:

MiMatriz = [B4:D13]

o bien

MiMatriz = Range("B4:D13")

y tambien funciona al reves:

[F4:H13] = MiMatriz

o bien

Range("F4:H13") = MiMatriz

Se ha de tener en cuenta que si declaras la matriz como una matriz dará error.

Declarando la variable como una matriz no funciona.

Me explico. Si prueba las cuatro siguientes macros:

Sub Matrix1()
Dim X(11, 5)
X = Range("B4:D13")
End Sub

Sub Matrix2()
Dim X As Double
X = Range("B4:D13")
End Sub

Sub Matrix3()
Dim X As Variant
X = Range("B4:D13")
End Sub

Sub Matrix4()
Dim X
X = Range("B4:D13")
End Sub

comprobara que las dos primeras macros dan error. Las que funcionan bien son la tercera y la cuarta.

Si lo que queremos es tomar un valor de la matriz (por ejemplo el primero, el de la celda B4) y luego dejarlo en la celda H1 hacemos lo siguiente.

Sub test()
Dim x
x = [B4:D13]
[H1]= x(1, 1)
End Sub

¿Cómo se puede conocer la dimensión de la matriz creada?

Supongamos que creo una función, como esta:

Function MiRef(Rng)
Dim X
X = [Rng]
'......
End Function

Para tratar los elementos de la matriz debo conocer su dimensión. Por
ejemplo, sería interesante saber si tiene 10 filas y 4 columnas, o la
dimensión de que se trate. ¿Cómo saber esto?.

Disponemos de dos métodos:

1) Tomar las dimensiones del rango a la hora de asignar:

Function test1(rng As Range) As String
Dim Matriz, x As Long, y As Long
Matriz = rng
x = rng.Columns.Count
y = rng.Rows.Count
test1 = "Matriz ( 1 To " & y & " , 1 To " & x & ")"
End Function

2) Tomar las dimensiones de la matriz:

Function test2(rng As Range) As String
Dim Matriz, x As Long, y As Long
Matriz = rng
x = UBound(Matriz, 2)
y = UBound(Matriz, 1)
test2 = "Matriz ( 1 To " & y & " , 1 To " & x & ")"
End Function

También puede interesarle ...

• Ordenar un millón de valores