Casos prácticos

Veamos una serie de aplicaciones de macros de Excel programadas en VBA (Visual Basic para Aplicaciones).

1. Ciudades elegidas
2. Generación de tablas de bingo
3. Graficar por fechas

Ciudades elegidas

Puede descargar el archivo ciudadesElegidas.xlsm

Hoja1

Disponemos de una lista de ciudades, son en total 735 ciudades, y deseamos elegir aleatoriamente unas cuantas de ellas. La elección se realizará por dos métodos, ambos con la posibilidad de que salgan ciudades repetidas. Si obtenemos ciudades repetidas las detectaremos con Formato condicional.

Los métodos empleados son los siguientes.

• Método 1: con la función INDICE
• Método 2: con la función DESREF

Hoja2

Disponemos de una lista de ciudades en la columna A y de ellas deseamos elegir aleatoriamente y sin repetición un cierto número de ellas. Vamos a resolver este caso con la ayuda de una macro de Excel.

 Sub EligeNombresAleatorios()  
 Borra  
 Dim numElegidas As Integer  
 Dim numNombres As Long  
 Dim numAlea As Integer  
 Dim Nombres() As String  
 Dim i As Byte, j As Byte  
 Dim Fila As Long  
 Dim Repe As Boolean 'para ver si está repetida la ciudad seleccionada  
 numElegidas = Range("C3").Value  
 Fila = 6  
 ReDim Nombres(1 To numElegidas)  
 numNombres = Application.CountA(Range("A:A")) - 1  
 For i = 1 To numElegidas  
   Do  
     Repe = False 'inicialmente supondremos que no está repetida la ciudad  
     numAlea = Application.RandBetween(1, numNombres)  
     'veamos si la ciudad seleccionada ya ha sido elegida  
     For j = LBound(Nombres) To UBound(Nombres)  
       If Nombres(j) = Cells(numAlea + 1, 1).Value Then Repe = True: Exit For  
     Next j  
   Loop While Repe  
   Nombres(i) = Cells(numAlea + 1, 1).Value ' Assign random name to the array  
 Next i  
 'Escribe las ciudades elegidas  
 For j = LBound(Nombres) To UBound(Nombres)  
   Cells(Fila, 3) = Nombres(j)  
   Fila = Fila + 1  
 Next j  
 End Sub  
   
 Sub Borra()  
 Range("C6").Select  
 Range(Selection, Selection.End(xlDown)).ClearContents  
 Range("C3").Select  
 End Sub  

Y en la Hoja2 tenemos el siguiente código.

 Private Sub Worksheet_Change(ByVal Target As Range)  
   If Target.Address = "$C$3" Then  
     Call EligeNombresAleatorios  
   End If  
 End Sub  

Borrar celdas con macro

Puede descargar el archivo BorrarCelda.xlsm

Deseamos borrar el contenido de una celda o de un rango de celdas utilizando una macro de Excel con código VBA.

Si deseamos borrar la celda A1 podemos utilizar cualquiera de estas líneas.

1:  Range("A1") = Empty  
2:  Range("A1").ClearContents  
3:  Range("A1").ClearFormats  
4:  Range("A1").Clear  

• Las líneas 1, 2 son equivalentes y borran únicamente el contenido de la celda, pero no borran el formato.
• La línea 3 borra únicamente el formato de la celda.
• La línea 4 borra todo, tanto el contenido como el formato.

En el siguiente caso disponemos de tres botones de macro. Veamos qué hace cada uno de ellos.

• Rellenar Valores: deja la fórmula aleatorio.entre en las celdas amarillas, rellenando con valores aleatorios que van ente 200 y 300.
• Borrar celda: elige una celda al azar de entre las amarillas y borra su contenido.
• Borrar rango: borra el contenido del rango C5:C13.

Las celdas amarillas al quedar vacías adquieren color rosa. Esto se consigue utilizando 'Formato condicional'.

Veamos las macros sobre las que actúan los botones.

 Sub RellenarValores()  
 Dim i As Byte  
 For i = 1 To 9  
  Cells(i + 4, "C") = ["=RANDBETWEEN(200,300)"]  
 Next i  
 End Sub  
 Sub BorrarCelda()  
 Cells(Int(Rnd * 9 + 1) + 4, "C") = Empty  
 End Sub  
 Sub BorrarRango()  
 [C5:C13] = Empty  
 End Sub  

Buscar la última fila con macro

Puede descargar el archivo ultimaFila.xlsm

Cuando buscamos la última fila rellena de un rango de valores que se encuentran en una columna podemos usar el siguiente código.

 UltimaFila = Sheets("Hoja1").Range("A" & Rows.Count).End(xlUp).Row  

Este ejemplo está aplicado sobre la columna A. Nos dará la última fila de la columna A que contenga un valor no vacío, aunque en la columna A existan previamente huecos, esto es, aunque previamente existan celdas vacías.

Generación de tablas de bingo

Puede descargar el archivo: bingo.xlsm

Vamos a crear 6 tablas de bingo.

Para obtenerlas se exigen una serie de condiciones.

• Cada tabla tiene 5 columnas
• Cada columna contiene 5 números
• La columna 1 contiene números entre 1 y 15
• La columna 2 contiene números entre 16 y 30
• La columna 3 contiene números entre 31 y 45
• La columna 4 contiene números entre 46 y 60
• La columna 5 contiene números entre 61 y 75
• Los cinco números de cada tabla y columna no se repiten entre si
• El conjunto de 5 números de cada tabla y columna no se repiten entre si

Código

 Sub generaBingo()  
 Dim i As Byte, j As Byte, k As Byte, n As Byte, m As Byte, p As Byte, num As Byte  
 Dim esta As Boolean  
 Dim linearepe As Boolean  
 Dim A(5, 5, 6) As Long  
 B = Array(2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47) 'quince primos  
 Randomize  
 Do  
  linearepe = False  
  For j = 1 To 5 'número de columna B=1 I=2 N=3 G=4 O=5  
   For k = 1 To 6 'número de tablas  
    A(0, j, k) = 1  
    For i = 1 To 5 'número de fila  
     A(i, j, k) = (Int(Rnd() * 15) + 1) + (j - 1) * 15  
     For n = 2 To i  
      Do  
       num = (Int(Rnd() * 15) + 1) + (j - 1) * 15  
       esta = False  
       For m = 1 To n - 1  
        If num = A(m, j, k) Then esta = True: Exit For  
       Next m  
      Loop While esta  
      A(i, j, k) = num  
     Next n  
     Cells(k * 7 - 5 + i, j + 1) = A(i, j, k)  
     'Producto de números primos  
     A(0, j, k) = A(0, j, k) * B(A(i, j, k) - 1 - (j - 1) * 15)  
    Next i  
    'Cells(k * 7 - 5, j + 1) = A(0, j, k) 'Imprime el producto de primos  
   Next k  
  Next j  
  For j = 1 To 5 'para cada columna  
   For k = 1 To 5 'analizamos cada tabla con las siguientes, por eso son 5  
    For p = k + 1 To 6  
     If A(0, j, k) = A(0, j, p) Then linearepe = True  
    Next p  
   Next k  
  Next j  
 Loop While linearepe  
 End Sub  

Hemos resuelto este caso usando los quince primeros números primos, que podemos ver en el array.

B = Array(2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47)

La idea es la siguiente.

• Por cada columna, tenemos que estudiar si el conjunto de los cinco números de la tabla 1, son iguales o distintos de los cinco números de esa misma columna de las tablas siguientes, esto es de las tablas 2, 3, 4, 5, 6. 
• Por cada columna, tenemos que estudiar si el conjunto de los cinco números de la tabla 2, son iguales o distintos de los cinco números de esa misma columna de las tablas siguientes, esto es de las tablas 3, 4, 5, 6.
• Por cada columna, tenemos que estudiar si el conjunto de los cinco números de la tabla 3, son iguales o distintos de los cinco números de esa misma columna de las tablas siguientes, esto es de las tablas 4, 5, 6.
• Por cada columna, tenemos que estudiar si el conjunto de los cinco números de la tabla 4, son iguales o distintos de los cinco números de esa misma columna de las tablas siguientes, esto es de las tablas 5, 6.
• Por cada columna, tenemos que estudiar si el conjunto de los cinco números de la tabla 5, son iguales o distintos de los cinco números de esa misma columna de la tabla siguiente, esto es de la tabla 6.

Esto se podría resolver comparando número a número de entre los cinco de cada grupo, con los cinco correspondientes de las demás tablas y esto se ha de hacer por cada columna.

En este caso hemos optado por otro método que utiliza números primos. Siempre es más cómodo comparar número contra número que comparar grupo de cinco números contra otro grupo de cinco número y ver si todos ellos coinciden. Por tanto, lo que hemos hecho es convertir ese grupo de cinco números en un único número, para sí poder comparar de forma más sencilla.

¿Cómo convertimos un grupo de cinco números en un solo número?

Podríamos haber optado por sumar los cinco número y su resultado sería el número único que los representa a todos ellos. Pero esta opción, no sería correcta ya que se puede dar el caso de que varios conjuntos de cinco números tengan la misma suma. Por ejemplo, 1,2,3,4,6,7 suman lo mismo que 1,2,3,4,5,8. Por tanto, el método de la suma de los cinco número no conduce a un único valor que los represente.

Probemos multiplicando. Se trataría de multiplicar los cinco números de un grupo y ver si su producto es siempre distinto del producto de otro conjunto de cinco números distintos. Si el producto siempre fuera diferente en estos dos conjuntos de números habríamos encontrado un método para diferenciar ambos conjuntos mediante la comparación de un único valor, que en este caso sería su producto. Pero este método no es siempre cierto ya que por ejemplo los dos conjuntos de cinco números presentan el mismo producto: 1,2,7,4,6 y 1,2,7,8,3. El motivo es que 6 no es primo y es 3 por 2. Dejamos en su lugar el 3 y el 2 lo multiplicamos por cualquiera de los otros, por ejemplo por 4 para que de 8. De esta forma ambos productos coinciden y son iguales a 336. Esto nunca podría haber sucedido si todos los números fueran primos.

Aunque los números no son primos los podemos convertir en primos asignando cada valor que varía en el rango de 1 a 15 para la primera columna en cada uno de los primero quince números primos. Esto se haría para cada columna y así no aseguramos que el producto de los primos nunca podrá coincidir.

Por ejemplo, los números 1,2,7,4,6 se transforman en los siguientes primos, siguiendo el orden: 2,3,17,7,13. Y los números 1,2,7,8,3 se transforman en: 2,3,17,19,4. El producto del primer conjunto de primos es 9.282 y del segundo 7.752. Observamos que sus productos son diferentes y esto siempre se puede garantizar ya que operamos con números primos.