Costruire un calendario con excel

 

Il lavoro che sto per presentare questa volta è un progetto ibrido. Cioè il lavoro eseguito in Excel in parte viene eseguito usando le funzioni proprie di Excel in parte aiutati dal codice VBA.

Per l'occasione presentiamo la preparazione di un semplice calendario. Ma non un calendario piatto, monocromatico, ma con le feste ed i prefestivi colorati.

Quel che vogliamo da questo calendario:

  1. le feste saranno evidenziate di un colore di nostra scelta (il rosso) ed anche i prefestivi
  2. reperimento e segnalazione della Pasqua e del lunedì di Pasqua tramite una funzione persolalizzata che verrà usata direttamente sul foglio di lavoro
  3. inserimento delle feste infrasettimanali compresa quella patronale
  4. il calendario non fisso con 31 giorni per tutti i mesi, ma con l'adeguato numero dei giorni relativi ad ogni singolo mese, compreso il Febbraio dell'anno bisestile

Potrebbe sembrare una banalità o una semplice perdita di tempo o addirittura un'attività ludica. In realtà è un utile esercizio. Ma chi non ha mai avvertito la necessità di gestire un turno, uno scadenziario, un gestore di appuntamenti?

Da questo che sto per presentare questa volta si può fare tutto questo e molto di più.

Iniziamo quindi a costruire un semplice calendario.

 

Il lavoro sui fogli

Iniziamo dal foglio2

  1. nella colonna A scriviamo i dodici mesi
  2. nella colonna C alcuni anni (io ho scritto gli anni dal 1990 al 2020)
  3. nella colonna E i numeri da 1 a 31
  4. le celle F1 e H1 le coloro per riconoscerle poi (non è necessario)
  5. nella colonna K l'elenco delle feste infrasettimanali (compresa la festa patronale); per il momento non scrivo il giorno della Pasqua e del Lunedi di Pasqua perchè ci ritornerò in un secondo tempo.
  6. nella colonna M scriviamo i nomi delle feste
K L M N Le formule usate
nella colonna K
01/01/2007   Capodanno   =DATA($H$1;1;1)
06/01/2007   Epifania   =DATA($H$1;1;6)
25/04/2007   Liberazione   =DATA($H$1;4;25)
01/05/2007   Festa del Lavoro   =DATA($H$1;5;1)
02/06/2007   Festa della Repubblica   =DATA($H$1;6;2)
15/08/2007   Ferragosto   =DATA($H$1;8;15)
01/11/2007   Tutti i Santi   =DATA($H$1;11;1)
08/12/2007   Immacolata   =DATA($H$1;12;8)
25/12/2007   Natale   =DATA($H$1;12;25)
26/12/2007   S. Stefano   =DATA($H$1;12;26)
22/04/2007   Festa Patronale   =DATA($H$1;4;22)
    Pasqua    
    Lunedi dell'Angelo    

 

Attenzione:

Per il momento nella colonna K non verranno le date che ci aspettiamo in quanto la cella H1 è ancora vuota

 

Ora possiamo passare al Foglio1

Disegnamo due ComboBox:

Le impostazioni da dare alla ComboBox presa dalla Barra dei Moduli

  1. fare clic col tasto destro del mouse sulla ComboBox
  2. dal menù contestuale scegliere la voce Formato controllo
  3. nella finestra che ne consegue andare nella scheda controllo
  4. mettere il cursore nella casella Intervallo di input
  5. selezionare col mouse il Foglio2 ed in questo foglio selezionare l'intervallo A1:A12
  6. mettere il cursore nella casella Collegamento cella
  7. selezionare col mouse ancora il Foglio2 ed in questo foglio selezionare la cella F1

Questa ComboBox porrà nella cella il numero relativo al mese scelto

 

Le impostazioni da fare alla ComboBox presa dalla Barra Strumenti di controllo

  1. se la barra Strumenti di controllo non è visibile fare clic destro in un punto qualsiasi delle barre degli strumenti e scegliere Strumenti di controllo
  2. dalla Barra Strumenti di controllo fare clic sull'icona con riga, squadra e matita se non è attiva per porsi in modalità di progettazione
  3. scegliere l'icona l'icona con la manina che regge un foglio di carta per far apparire la finestra Proprietà
  4. in questa finestra:
    1. in LinkedCell scrivere Foglio2!H1
    2. in ListFillRange scrivere Foglio2!$C$1:$C$31
  5. chiudere la finestr Proprietà
  6. uscire dalla modalità Progettazione (icona con squadra, riga e matita)

 

Dovremmo avere una situazione del genere

   

Se ora si clicca sulla ComboBox dei mesi nella cella F1 del foglio2 verrà scritto il numero relativo al mese scelto

Se si clicca sulla ComboBox degli anni nella cella H1 del foglio2 verrà scritto l'anno scelto

Se andiamo a controllare le date scritte nella colonna K del foglio2 ora possiamo osservare che si sono aggiornate all'anno scelto

 

La costruzione del calendario sul foglio: parte finale della progettazione

Ora siamo pronti per creare il nostro calendario.

  1. nella cella A2 scriviamo la funzione =DATA(Foglio2!$H$1;Foglio2!$F$1;Foglio2!E1)
  2. nella cella B2 scriviamo =A2
  3. nella cella A1 scriviamo =A2

Avremo questa situazione

=A2  
=DATA(Foglio2!$H$1;Foglio2!$F$1;Foglio2!E1) =A2

Queste formule ci faranno vedere una cosa del genere

01/08/2007  
01/08/2007 01/08/2007

Ma ancora non ci siamo, perciò continuaiamo col lavoro.

  1. facciamo clic destro del mouse nella cella A1 e scegliamo Formato cella per lavorare con questa finestra di dialogo
    1. scegliamo la scheda Numero
    2. in Categoria scegliamo Personalizzato
    3. nella casella tipo digitiamo mmmm per visualizzare il nome intero del mese (gennaio, febbraio, ecc) oppure mmm per visualizzare le prime tre lettere del mese (gen, feb, mar, ecc)
  2. facciamo clic destro del mouse nella cella A2 e scegliamo Formato cella
    1. scegliamo la scheda Numero
    2. in Categoria scegliamo Personalizzato
    3. nella casella tipo digitiamo gggg per visualizzare il giorno della settimana per intero (lunedì, martedì, ecc) oppure ggg per vedere le prime lettere del giorno della settimana (lun, mar, mer, ecc)
  3. facciamo clic destro del mouse nella cella B2 e scegliamo Formato cella
    1. scegliamo la scheda Numero
    2. in Categoria scegliamo Personalizzato
    3. nella casella tipo digitiamo gg per vedere i giorni del mese a due cifre (01, 02, ecc) oppure g per edere i giorni del mese ad una o due cifre (1, 2, .... 10, 11, ecc)

Verremo a trovarci in questa condizione:

agosto  
mercoledì 01

A questo punto selezioniamo le celle A2 e B2 e, quando il cursore assume la forma simile al più (+) trasciniamo la selezione fino alla riga 32. Così facendo visualizzeremo tutti i giorni del mese fino al giorno 31

verremmo a trovarci in questa situazione:

agosto    
mercoledì 01  
giovedì 02  
venerdì 03  
sabato 04  
domenica 05  
lunedì 06  
martedì 07  
mercoledì 08  
ecc.    

 

E' arrivato il tempo di pensare al codice VBA

Si inizia col risolvere il discorso della Pasqua risolvendo un problema lasciato in sospeso qui sopra

Il calcolo della Pasqua

Ho dovuto girare molto per trovare l'algoritmo per il calcolo della Pasqua e finalmente ho trovato delle spiegazioni alquanto complesse in Wikipedia. Chi volesse approfondire (http://it.wikipedia.org/wiki/Calcolo_della_Pasqua).

Continuando nell'esplorazione mi sono fermato a questo che mi sembra abbastanza semplice.

Perciò in un modulo standard scriveremo questo codice:

Function CercaLaPasqua(Optional ByVal Y As Integer = 2007) As Date
Dim M As Integer, N As Integer, A As Integer, B As Integer, C As Integer
Dim D As Integer, E As Integer
Dim ED As String
M = 24: N = 5
A = Y Mod 19
B = Y Mod 4
C = Y Mod 7
D = (19 * A + M) Mod 30
E = (2 * B + 4 * C + 6 * D + N) Mod 7
ED = 22 + D + E
If ED <= 31 Then
ED = ED & "/03/" & Y
Else
ED = D + E - 9 & "/04/" & Y
End If
CercaLaPasqua = CDate(ED)
End Function

Nel Foglio di lavoro questa funzione si usa così:

=cercalapasqua(H1)

Nel codice VBA la funzione viene richiamata con:

Sub testaFunzione()
Dim Anno As Integer
Anno = 2007
MsgBox "nel " & Anno & " la Pasqua capita il " & CercaLaPasqua(Anno)
End Sub

Scritta questa funzione possiamo completare la nostra tabella delle feste che sta nel Foglio2

K L M N Le formule usate
nella colonna K
01/01/2007   Capodanno   =DATA($H$1;1;1)
06/01/2007   Epifania   =DATA($H$1;1;6)
25/04/2007   Liberazione   =DATA($H$1;4;25)
01/05/2007   Festa del Lavoro   =DATA($H$1;5;1)
02/06/2007   Festa della Repubblica   =DATA($H$1;6;2)
15/08/2007   Ferragosto   =DATA($H$1;8;15)
01/11/2007   Tutti i Santi   =DATA($H$1;11;1)
08/12/2007   Immacolata   =DATA($H$1;12;8)
25/12/2007   Natale   =DATA($H$1;12;25)
26/12/2007   S. Stefano   =DATA($H$1;12;26)
22/04/2007   Festa Patronale   =DATA($H$1;4;22)
08/04/2007   Pasqua   =cercalapasqua(H1)
09/04/2007   Lunedi dell'Angelo   =K12+1

 

Preparazione del codice

Finito anche questo lavoro ci possiamo dedicare al resto del lavoro.

Come accennato all'inizioil nostro scopo è quello di:

  1. colorare i giorni festivi e prefestivi
  2. indicare anche i festivi infrasettimanali
  3. non visualizzare i giorni che vanno oltre la fine del mese per i mesi più brevi di 31 giorni.

Questo è quanto faremo nel codice che presento.

Sub Colora()
Dim UltimoGiorno
Dim Forse As Boolean
Dim Intervallo As Range
Dim IntervDate As Range
Dim cAnno, cMese, MeseCorrente
Dim URiga, R, Fest, Colonne
Dim DataCal
With Worksheets("foglio2")
cAnno = .Range("H1")
cMese = .Range("F1")
End With
'viene cercato l'ultimo giorno del mese
UltimoGiorno = DateDiff("d", DateSerial(cAnno, cMese, 1), DateSerial(cAnno, cMese + 1, 1))
'vengono determinati gli intervalli del calendario che sta sul foglio1
'e delle feste che sono nella colonna K del foglio2
With Range("A1").CurrentRegion
Set Intervallo = .Offset(1, 0).Resize(.Rows.Count - 1, 3)
End With
Set IntervDate = Sheets("Foglio2").Range("K1:K13")
'viene cercata l'ultima riga occupata dal calendario
URiga = Intervallo.Rows.Count
'si inizia col cancellare le formattazioni applicate precedentemente
Application.ScreenUpdating = False
With Intervallo
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 1
.Font.Bold = False
.Rows("29:31").EntireRow.Hidden = False
.Columns("C:D").ClearContents
For R = 1 To URiga
' si colorano le domeniche
If Weekday(.Item(R, 1)) = 1 Then
With Range(.Item(R, 1), .Item(R, 3)).Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
With Range(.Item(R, 1), .Item(R, 3)).Font
.Name = "Arial"
.FontStyle = "Grassetto"
.ColorIndex = 2
End With
End If
' ora si colorano i sabato
If Weekday(.Item(R, 1)) = 7 Then
With Range(.Item(R, 1), .Item(R, 3)).Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
With Range(.Item(R, 1), .Item(R, 3)).Font
.Name = "Arial"
.FontStyle = "Grassetto"
.ColorIndex = 2
End With
End If
Next
'inizia la ricerca delle feste infrasettimanali
For R = 1 To URiga
DataCal = .Item(R, 1)
For Fest = 1 To IntervDate.Rows.Count
If DataCal = IntervDate(Fest, 1) Then
With Range(.Item(R, 1), .Item(R, 3)).Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
With Range(.Item(R, 1), .Item(R, 3)).Font
.Name = "Arial"
.FontStyle = "Grassetto"
.ColorIndex = 2
End With
.Item(R, 3) = IntervDate(Fest, 3)
If (R - 1) > 0 And .Item(R - 1, 1).Interior.ColorIndex <> 3 Then
With Range(.Item(R - 1, 1), .Item(R - 1, 3)).Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
With Range(.Item(R - 1, 1), .Item(R - 1, 3)).Font
.Name = "Arial"
.FontStyle = "Grassetto"
.ColorIndex = 2
End With
End If
End If
Next
Next
End With
'vengono nascoste le righe che contengoo i giorni eccedenti al mese corrente
If UltimoGiorno <> 31 Then
Intervallo.Rows(UltimoGiorno + 1 & ":" & URiga).EntireRow.Hidden = True
End If
Application.ScreenUpdating = True
End Sub

Il codice può essere migliorabile ma, almeno per il momento sembra svolgere bene il suo compito. Perciò lo lascio così.

 

Buon lavoro