Crear un calendario anual en Excel y VBA

Un calendario para realizar turnos. Versión 1.0.1

Para mí, Excel + VBA (Visual Basic for Applications) componen una herramienta poderosísimamente poderosísima. Si ya de por sí, una hoja de cálculo nos permite realizar tareas complejas, añadir la posibilidad de programar con Visual Basic permite generar asombrosas aplicaciones y obtener resultados que una sola persona, y a mano, tardaría días en conseguir (lo digo basándome en mi experiencia). Aunque siga haciendo hincapié en ello, pienso que Microsoft Office + VBA es tan poderoso que estoy plenamente convencido que muchas empresas no se pasan al software libre por lo que ambas conforman.

Hace un tiempo, unas trabajadoras de una empresa que hacían turnos, me mostraron el cuadrante anual que llevaban y cuando vi el enorme tiempo que perdían en "dibujar" el calendario, les propuse crear un calendario anual que se dibujara automáticamente. Les dije:

Ustedes únicamente seleccionarán el año del calendario que quieran y Excel automáticamente dibujará el año completo, mostrando los meses, días, el día actual (lunes,...,domingo), pintará el color de fondo de las celdas sábado y domingo y finalmente comprobará si el año es o no bisiesto para mostrar o no el día 29 de febrero.

Como fue un código muy sencillo de hacer (lo difícil ha sido escribir después este artículo explicándolo paso a paso) y que os puede ser muy útil a muchos, voy a poneos en descarga el archivo Excel + Macros, (si no quieres saber nada del código, puedes dirigirte al final del artículo a la zona de descarga) y para los que queréis adaptar el código, voy a explicar resumidamente los pasos que seguí para crearlo.

Crear archivo Excel para Macros

El archivo lo voy a crear compatible con Excel 2007 - Excel 2013. Lo primero que debes hacer es crear un archivo Excel habilitado para macros para que podamos crear código Visual Basic. Lo siguiente es pensar en el diseño. Yo lo tenía bastante claro en mi cabeza: trabajar con 2 hojas, poner en la hoja principal un desplegable arriba a la izquierda para elegir el año, y según esa elección, se creará automáticamente el calendario.

Hoja Meses

Cuando creas el archivo Excel, por defecto, se crean tres hojas. Elimino la tercera y en la segunda, que llamo Meses, voy a crear el contenido de la lista desplegable y otros datos que luego explicaré. Esta es la vista previa:

Asigno al rango C2:C13 el nombre AÑOS y al rango A2:A13 el nombre MESES.

Hoja principal, Calendario

Me dirijo ahora a la hoja principal, que llamo Calendario. Al desplegable donde se encuentran los años, voy a asignarle el rango que he nombrado en la hoja Meses como AÑOS. De este modo, a partir de ahora, podremos seleccionar entre 2014-2025.

Diseño del Calendario

Ahora voy dibujando el calendario pensando en su usabilidad, en la distinción de los meses, bloqueando paneles para que el nombre de usuario y otros datos sean siempre visibles, bloqueando la hoja para que usuarios menos experimentados puedan modificar partes importantes y finalmente intentando que se vea un trabajo cuidado y profesional. El diseño final lo puedes ver en el ejemplo en descarga.

Los principales pasos que sigo son:

1. En el desplegable, selecciono el año 2014 (por ejemplo) y dibujo el calendario totalmente vacío y de fondo blanco para todo ese año. Le doy grosor a las líneas de los meses, añado 5 usuarios inventados y a la celda que contiene el desplegable con el año, le pongo una celda vacía a su derecha para que el desplegable se pueda seleccionar cuando nos desplacemos con la barra hacia la derecha.
2. Inmovilizo los paneles para que el nombre de los usuarios esté siempre visible.
3. La parte superior de todos los meses la divido en 2 partes excepto el mes de febrero que la divido en 3. A los meses impares les doy un fondo grisáceo, estas tres partes son:
   1. Nombre del mes (mira AH1:AU1)
   2. Una celda que únicamente se utilizará en el mes de febrero (mira la celda AV1) y sirve para calcular el número de días de dicho mes. Más abajo explico el código.
   3. Escribe el año que se ha seleccionado en el desplegable (por ejemplo, mira AW1:BI1)
4. Pongo las fórmulas Excel que seguidamente comento y el código VBA.

Cálculo de días del mes de febrero

Utilizo la celda AV1 y el código es el siguiente:

=DIA(FECHA($A$1;BUSCARV($AH$1;Meses!$A$2:$B$13;2;FALSO)+1;0))

La parte de BUSCARV devuelve, buscando en la hoja Meses, el número que corresponde al mes. Pensarás que para qué quiero saber qué mes numérico es febrero si ya sabemos que corresponde al valor 2. Yo te pongo esa fórmula porque puede valerte en un futuro para calcular el valor numérico de cualquier mes desde Excel y sin VBA.

El día numérico de un mes

Pienso que este es el valor más importante. Me refiero a todos los números, observa el rango de celdas de C3 hasta ND3 para entenderlo. Esos números no son valores enteros, sino que son fechas y esto es muy importante para calcular posteriormente los días festivos. Por ejemplo, ¿cómo se calcula el 2 de enero? De esta forma:

=FECHA($A$1;BUSCARV($C$1;Meses!$A$2:$B$13;2;FALSO);DIA(C3)+1)

Es decir, en la celda D3 existe una fecha que, por ejemplo, para el año 2014 sería: 02/01/2014. Como puedes observar, la fórmula suma un día al día anterior (DIA(C3)+1) para poder realizar un copia-pega en todo el mes y no tener que escribirlos uno a uno. ¿Por qué no aparece la fecha y se muestra un número? Porque he puesto un formato personalizado a la celda.

El día de la semana

Este código Excel es muy importante porque calcula qué día de la semana es una fecha determinada: lunes, martes, miércoles, jueves, viernes, sábado o domingo.

=SI(C3<>"";DIASEM(FECHA($A$1;BUSCARV($C$1;Meses!$A$2:$B$13;2;FALSO);C3));"")

Código VBA

El código Visual Basic es muy sencillo. Para no repetir código he creado varias funciones que pueden ser muy útiles, por ejemplo, para calcular el número de usuarios que hay, utilizo este sencillo código con la única precaución de que tras el último de ellos tienes que dejar la siguiente celta vacía:

 
Private Function NumeroTotalFilas() As Integer
    'Calcula el número de líneas:
    For linea = 4 To 500
        If Range("B" & linea).Value = "" Then Exit For
    Next linea
    NumeroTotalFilas = linea - 1
End Function
 

La siguiente función devuelve si un día es festivo o no. Por defecto, solo pone festivos los sábados y domingos pero tú puedes añadir cualquier otro día. Esto es importante porque las celdas de estos días serán rellenadas de color azul.

 
Public Function DiaFestivo(Fecha As Date) As Boolean
    'vbSunday (1), vbMonday (2), vbTuesday (3), vbWednesday (4)
    'vbThursday (5), vbFriday (6), vbSaturday (7)
    
    Select Case Weekday(Fecha)
        Case vbSaturday, vbSunday
            DiaFestivo = True
        Case Else
            DiaFestivo = False
    End Select
End Function
 

Como pongo en los comentarios, tú puedes poner en festivo cualquier otro día del siguiente modo:

 
'Pon aquí los días del año que tú quieres que sean Festivos
'Por ejemplo, el 24 y 25 de diciembre:
If Day(Fecha) = "24" And Month(Fecha) = 12 Then DiaFestivo = True
If Day(Fecha) = "25" And Month(Fecha) = 12 Then DiaFestivo = True
 

La subrutina pintar_calendario() rellena de color azul todos los días festivos del año y el resto de código creo que está bien explicado en los comentarios del código fuente. Finalmente, he protegido todo el libro y hojas con una contraseña: KARMANY.NET para que los códigos Excel no puedan ser modificados de forma involuntaria. También he creado un código cuando se inicia el libro, que protege con contraseña todo el libro. Espero que pueda serte útil.

Descarga del archivo

Recuerda siempre descargar tus archivos desde fuentes fiables. Te voy a poner el valor MD5 y tamaño del archivo para que lo verifiques:

Datos del archivo zip en descarga:

Datos del archivo Excel para macros. Zip descomprimido:

Enlace desde Mega: CalendarioExcelVBA.zip, 1.0.1 versión