Crear un calendario anual en Excel y VBA

Tamaño de letra:

Un calendario para realizar turnos. Versión 1.0.1

Excel desplegablePara 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:

Excel rango

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.

Excel Calendario

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:

CalendarioExcelVBA.zip

Nombre del archivo

Tamaño (bytes)

MD5

CalendarioExcelVBA.zip 40.221 bytes 4eeb35892c9d5a98209e376e817fbfac

Datos del archivo Excel para macros. Zip descomprimido:

CalendarioExcelVBA.xlsm

Nombre del archivo

Tamaño (bytes)

MD5

Compatible

CalendarioExcelVBA.xlsm 47.690 bytes b0a0f539bdbe45d9e96830d37389f6ac Excel 2007-2013

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

Última actualización: Domingo, 26 Octubre 2014
Comentarios  
+1 # Javier 25-12-2015 21:01
Una consulta, intento insertar una columna a la derecha de los usuarios, justo antes del mes de Enero de manera que me permita tener un dato mas de cada empleado, pero cada vez que añado una columna ya no me pone de color azul ningun dia festivo al cambiar el año.
En la funcion pintar_calendar io() modifico columna = 4 To 370 para controlar la columna nueva añadida, pero no funciona.
Responder | Responder con una citación | Citar
0 # Editor 26-12-2015 23:00
Le echo un vistazo en cuanto pueda
Responder | Responder con una citación | Citar
0 # Javier 27-12-2015 11:58
Otra modificacion que hice logicamente es en la parte de limpiar el calendario, modificando logicamente la columna por la que empieza, quedando de la siguiente manera:

'Limpia todo el calendario:
ActiveSheet.Ran ge("D2:ND" & NumeroTotalFila s()).Select
Responder | Responder con una citación | Citar
0 # Javier 27-12-2015 12:01
Otro dato, si entro en el codigo VB y lo ejecuto, es en ese momento cuando se actualizan los dias festivos en el calendario.
Responder | Responder con una citación | Citar
0 # Javier 27-12-2015 13:11
Solucionado, dejo la solucion porsi a alguien mas le sucede algo similar:
El problema logicamente es que no llegaba a ejecutar la funcion pintar_calendar io, por lo que el error era que no entraba en la parte de comprobacion correspondiente al dia 28 o 29 de Febrero. El problema era que no modifique la columna de comprobacion en el IF, quedando el codigo asi:

'Este código muestra u oculta el día 29 de febrero:
If ActiveSheet.Ran ge("AW1").Value = "28" Then
Responder | Responder con una citación | Citar
0 # karmany 28-12-2015 10:48
Genial, gracias por el aporte. No he tenido tiempo de verlo antes.
Un saludo Javier.
Responder | Responder con una citación | Citar
0 # SERGIO 15-12-2015 11:53
Una consulta cuando pinta el calendario el formato de celdas de días festivos solo se aplica a la fila 4 el resto de fila los días festivos deja el tipo de letra en color negro y no en blanco como en la fila 4, ¿como se podría arreglar eso?.

Saludos
Responder | Responder con una citación | Citar
0 # karmany 16-12-2015 06:56
Cuando agreges filas nuevas, hazlo copiando una que esté bien (una original) y pegándola. Así se guardarán los formatos originales.
Responder | Responder con una citación | Citar
0 # Sergio 26-12-2015 18:20
Buenas Karmany, he realizado lo de copiar y pegar nuevas filas, lo guardo pero en cuanto cambio de año me sigue cambiando el formato en los días festivos. Solo mantiene el formato de días festivos en la fila 4 de la 5 en adelante lo cambia.
Responder | Responder con una citación | Citar
0 # Editor 26-12-2015 23:02
Lo miraré. Te responderé lo antes que pueda.
Responder | Responder con una citación | Citar
0 # karmany 28-12-2015 13:11
Sergio estoy mirando lo que dices. Tienes que tener presente varias cosas:
1.- El código para calcular el número de filas que existen comprueba el texto de la celda. Esto significa que todas las celdas que tú quieras que tengan un formato deben tener algo escrito en la columna B.
2.- POr lo de antes, justo después de la última fila, debe estar la columna BX (donde X es un número entero) vacía de texto.
Fíjate que el modo de calcular el número de filas se realiza con esto, que puedes ver más arriba en el artículo:
Código:
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
Responder | Responder con una citación | Citar
+1 # Juan 23-06-2015 02:52
Felicitaciones por el gran aporte. te sigo en pagina web....saludos
Responder | Responder con una citación | Citar
0 # DIEGO 03-04-2015 04:58
Estimado,
Excelente trabajo, consulta deseo modificarlo y me pide contraseña, te lo agradezco!!!
Responder | Responder con una citación | Citar
0 # Luis 03-04-2015 15:48
Contraseña:
KARMANY.NET
Responder | Responder con una citación | Citar
Escribir un comentario
Antes de publicar un comentario, usted debe aceptar nuestras condiciones de uso: Condiciones de uso. Debido al spam, todos los comentarios serán moderados. Normalmente se responde en unos minutos, refresca los comentarios para comprobarlo.



 
Visitas: 8489547