Generazione del calendario ore con Excel

Vediamo come si costruisce una macro con Excel e il VBA per gestire più funzioni tramite una finestra di dialogo a menù. Questo esempio dimostra come è possibile generare il calendario delle ore lavorative con le macro e il VBA.

(Si consiglia di leggere prima il tutorial correlato: Calcolo ore lavorative con Excel )

Quello che vogliamo fare è lasciare che Excel crei una macro generica, che modificheremo in seguito manualmente via codice VBA per adeguarla ai nostri scopi.

Creeremo successivamente uno UserForm, cioè una finestra di dialogo personalizzata che avrà la funzione di menù per permettere all'utente di attivare diverse funzioni da una stessa macro.

L'apertura dello UserForm verrà collegato alla nostra macro. Ma procediamo passo passo.

Per prima cosa creiamo una macro generica con il registratore macro di Excel.

Facciamo clic sul menù Strumenti, poi su Macro, quindi su Registra nuova macro ( figura 1 ).

Nella finestra di dialogo Registra macro possiamo attribuire qualsiasi nome alla macro, lasciare il valore preimpostato di Macro1 per i nostri scopi va bene ugualmente.

E' molto importante invece inserire la lettera k in corrispondenza della casellina di testo 'Scelta rapida da tastiera: (figura 2 ), perchè in questo modo sarà possibile in seguito richiamare la macro mentre vi troverete sul foglio di lavoro premendo contemporaneamente i tasti CTRL e la lettera k.

Excel non fa differenza in questo caso tra lettere minuscole e lettere maiuscole, quindi la macro verrà attivata comunque in entrambi i casi.

Dopo aver fatto clic sul pulsante OK vi verrà mostrato il foglio di lavoro di Excel con il l'icona del registratore macro da qualche parte, fate almeno un'azione, per esempio selezionare una qualsiasi cella, quindi fate clic sul pulsante di arresto del registratore macro, in modo da terminarne la creazione.

A questo punto la macro di nome Macro1 è stata registrata nella cartella corrente con l'impostazione di avvio veloce CTRL + k e nello stesso tempo è stata creata una routine VBA di tipo Sub nel Modulo1 corrente: questo è quello che volevamo.

Da adesso in avanti useremo direttamente il codice VBA (Visual Basic for Applications) che fa parte di Excel ma funziona come se fosse un programma separato, per modificare la nostra macro e per costruire lo UserForm.

Facciamo quindi clic sul menù Strumenti, Macro, ma questa volta selezioniamo 'Visual Basic Editor' (si può attivare questa funzione anche premendo contemporaneamente i tasti ALT e F11).

Verrà visualizzato l'ambiente di sviluppo Microsoft Visual Basic. Sulla parte sinistra dovreste trovarvi una finestra di nome Progetto - VBAProject con l'elenco degli oggetti.

Nel controllo ad albero aprite la classe di oggetti Moduli, quindi fate doppio clic su modulo1.

Ora sulla destra dovreste poter visualizzare il contenuto del modulo1, come da figura 3 .

Eliminate tutte le istruzioni comprese tra 'Sub Macro1' e 'End Sub' ad eccezione delle righe di commento (visualizzate in colore verde), aggiungete la riga ' Si attiva con CTRL + 'k' a meno che il registratore Macro di Excel non abbia già generato un commento analogo.

Da adesso in poi abbiamo campo libero! Cioè possiamo personalizzare Macro1 con il VBA a piacimento.

Naturalmente tutte le volte che l'utente premerà CTRL + k (minuscolo o maiuscolo non importa), verrà eseguita la successione di istruzioni visual basic comprese tra 'Sub Macro1' e 'End Sub'.

A questo punto però lasciamo per un attimo la Macro1 e andiamo a costruirci lo UserForm.

Facciamo clic sul menù Inserisci (di Visual Basic Editor, non di Excel!), quindi su UserForm (vedi figura 4 ).

Vedrete apparire sulla destra uno UserForm vuoto e sulla sinistra la casella degli strumenti, che utilizzeremo tra poco.

Ora selezioniamo con un solo clic lo UserForm e quindi con il tasto destro del mouse apriamo il menù contestuale quindi selezioniamo Proprietà, in alternativa fate clic sul Menù Visualizza, quindi cercate la voce Proprietà, oppure premete F4, sempre mantenendo attiva la selezione sull'oggetto maschera (in inglese form significa maschera).

Cambiamo la proprietà BackColor da Immagine pulsante (predefinita) a Spazio di lavoro applicazione ( figura 5 ): noterete che ora lo sfondo della maschera è di colore grigio scuro.

Ora cercate la proprietà Caption e cambiatene il valore da UserForm1 in Generazione calendario ore lavorative, vedrete comparire questa etichetta sulla barra del titolo dello UserForm.

Chiudiamo la finestra di dialogo Proprietà e apriamo la finestra di dialogo Casella degli strumenti, facendo clic sull'icona da forma di Martello e Cacciavite (se non appare, assicuratevi che UserForm sia selezionato facendo clic con il mouse su un punto qualsiasi dello sfondo).

Nella figura 6 sotto vedete il contenuto della Casella degli strumenti; i controlli che utilizzeremo sono di 2 tipi: Cornice e Pulsante di comando (sono al centro).

Non fate caso al fatto che nella figura compaiano UserForm1 e UserForm2, voi avrete soltanto lo UserForm1 (quando abbiamo registrato questo tutorial, in UserForm1 c'era la maschera già terminata, usiamo UserForm2 invece per mostrarvi tutti i passaggi).

Ora trasciniamo con il mouse dalla Casella degli strumenti allo UserForm rispettivamente 2 cornici, posizionandole sulla sinistra e sulla destra dello UserForm.

Mentre è selezionata ciascuna cornice (in inglese Frame), facciamo clic su Proprietà, quindi cambiamo la proprietà Caption da Frame1 in Genera calendario e da Frame2 in Vuota le celle.

Ora inseriamo 5 pulsanti di comando, 2 per ciascuna cornice e il quinto lo posizioniamo nella parte bassa al centro dello UserForm, quindi ne modifichiamo la proprietà Caption come in figura qui sotto.

La parte grafica è completata, ora passiamo a definire il codice VBA di ciascuno dei 5 pulsanti di comando ( figura 7 ).

Cominciamo dal pulsante Chiudi: facciamo doppio clic su di esso, quindi nella finestra codice in corrispondenza della routine Private Sub CommandButton2_Click() (il nome del controllo potrebbe essere differente nel vostro caso, ma non importa, perché facendo doppio clic sul controllo in modalità grafica, la finestra codice si posizionerà sempre in corrispondenza della routine appropriata).

Inseriamo il codice seguente:

Unload me

Questo farà sì che la maschera (UserForm) verrà chiusa ogni volta che l'utente farà clic sul pulsante 'Chiudi'.

A questo punto, già che siamo in modalità codice, andiamo a cercare la Sub-Evento UserForm_Initialize() e vi inseriamo il codice che segue:

Me.Caption = "Generazione calendario " & ActiveCell.Value & " " & Cells(1, 4)

Questo farà sì che tutte le volte che la maschera verrà aperta, nella barra del titolo venga mostrato 'Generazione calendario ' + la descrizione del mese selezionato (Gennaio, Febbraio, Marzo, ecc. + l'anno corrente, che è sempre in D1 (prima riga, quarta colonna).

Ora facciamo clic sull'oggetto Modulo1, il modulo di codice dove risiede la Macro1.

Create 2 Routines Sub e chiamatele GeneraMese e VuotaMese.

La definizione della prima riga di ciascuna Sub è importante: assicuratevi che il nome Sub sia preceduto dalla parola chiave Public e che sia presente in entrambe un parametro di nome all tipo Boolean (Vero/Falso), di seguito riportiamo le definizioni di entrambe le Sub:

Public Sub GeneraMese(all As Boolean)

End Sub

Public Sub VuotaMese(all As Boolean)

End Sub

Lasciate per ora in bianco le due routines e tornate allo UserForm1.

Fate doppio clic rispettivamente sui pulsanti di comando Genera mese corrente, Genera per tutto l'anno, Azzera mese corrente e Azzera tutto l'anno.

In corrispondenza della routine evento _Click() di ciascun pulsante di comando inserite rispettivamente le istruzioni che seguono:

GeneraMese False

GeneraMese True VuotaMese False VuotaMese True

Se siete in modalità codice, ok, altrimenti fate doppio click su un punto qualunque della maschera, il contenuto a livello codice di UserForm1 dovrebbe essere simile a quello mostrato nella figura 8.

Adesso tornate alla Macro1 (su Modulo1) ed aggiungete le seguenti istruzioni immediatamente dopo le righe di commento in verde e prima di End Sub:

If ActiveCell.Row <> 3 Or PrendiMese(ActiveCell.Value) = 0 Then MsgBox "Non sei sulla cella giusta per eseguire questa macro!", vbCritical
Exit Sub
End If

  UserForm1.Show

La prima istruzione (tipo 'IF .. THEN .. ELSE') ha lo scopo di controllare che la macro proceda soltanto se l'utente era posizionato sulla terza riga e in corrispondenza delle dodici colonne relative ai 12 mesi dell'anno, quando egli aveva premuto CTRL + 'k'.

In caso di verifica negativa viene visualizzato il messaggio di avvertimento e quindi la macro termina.

In caso di verifica positiva invece si prosegue e viene visualizzata la finestra di dialogo menù (lo UserForm1) dal quale l'utente potrà selezionare le 4 funzioni.

Il modulo 1 deve contenere, oltre alle 4 funzioni complete, il cui codice è contenuto nel file formato testo che potete scaricare in fondo alla pagina (che va copiato e incollato integralmente nel vostro progetto) anche le seguenti funzioni di supporto:

Function PrendiMese(strItem As String) As Integer
Select Case LCase(strItem)

Case "gennaio"
PrendiMese = 1
Case "febbraio"
PrendiMese = 2
Case "marzo"
PrendiMese = 3
Case "aprile"
PrendiMese = 4
Case "maggio"
PrendiMese = 5
Case "giugno"
PrendiMese = 6
Case "luglio"
PrendiMese = 7
Case "agosto"
PrendiMese = 8
Case "settembre"
PrendiMese = 9
Case "ottobre"
PrendiMese = 10
Case "novembre"
PrendiMese = 11
Case "dicembre"
PrendiMese = 12
Case Else
PrendiMese = 0
End Select
 
End Function

Questa funziona si commenta da sola, ha come unico scopo quello di ricavare un numero compreso tra 1 e 12 a seconda del nome di ciascun mese solare.

Function UltimoDelMese(Mese As Integer, Bisest As Boolean) As Integer
Select Case Mese
Case 1, 3, 5, 7, 8, 10, 12
UltimoDelMese = 31
Case 4, 6, 9, 11
UltimoDelMese = 30
Case 2
If Bisest Then
UltimoDelMese = 29
Else
UltimoDelMese = 28
End If
End Select
End Function

Questa funzione ha lo scopo di determinare il numero dei giorni di ciascun mese, tenendo conto non soltanto del mese, ma anche del fatto che l'anno corrente possa essere un anno bisestile.

Infatti la funzione prevede il passaggio del parametro 'Bisest' che serve appunto a spcificare se l'anno è bisestile oppure no.

Ma come si fa a determinare se un dato anno è bisestile oppure no? Semplice, basta associare al parametro Bisest, che è di tipo Booleano (Vero/Falso) il risultato della funzione che segue:


Function LeapYear(YYYY As Integer) As Integer '
' Leap Year from standard rules
' YYYY: 4-digit year
'
LeapYear = YYYY Mod 4 = 0 And (YYYY Mod 100 <> 0 Or YYYY Mod 400 = 0) End Function


La funzioneLeapYear (Anno bisestile) verifica il resto della divisione della cifra dell'anno per 4, per 100 o per 400.

Per essere bisestile l'anno deve essere divisibile per 4 o per 400 ma non per 100.

Function FirstDayOfTheYear(dt As Date) As Date
Dim DescData As String
DescData = "01/01/" & CStr(Year(dt))
FirstDayOfTheYear = CDate(DescData)
End Function

Questa funzione è molto semplice: riceve un parametro di tipo Data/Ora (dt) quindi lo converte in una stringa formata che ha come valori fissi il giorno e il mese cioè il primo gennaio di ogni anno.

Function LastDayOfTheYear(dt As Date) As Date
Dim DescData As String
DescData = "31/12/" & CStr(Year(dt))
LastDayOfTheYear = CDate(DescData)
End Function

La funzione LastDayOfTheYear (ultimo giorno dell'anno) funziona in maniera analoga.

Function Settimana(dt As Date) As Integer
' scopo recuperare il numero della settimana, cioè il numero dei sabati precedenti a questa data e cadenti nello stesso anno
Dim nsett As Integer
Dim dtemp As Date
nsett = 0
For dtemp = FirstDayOfTheYear(dt) To dt
If Weekday(dtemp, vbSunday) = 7 Then
nsett = nsett + 1
End If
Next
Settimana = nsett
End Function

Questa funzione è un pò più complessa: di fatto cicla tutti i giorni precedenti una certa data, sfruttando il calendario perpetuo che sta nel BIOS del vostro PC e la funzioneWeekday() che restituisce il numero del giorno della settimana.

Occorre fare attenzione alle impostazioni di sistema, il primo giorno della settimana per noi è il lunedì, mentre per inglesi e americani è la domenica!

Perciò il sabato corrisponde al settimo giorno e non al sesto nel nostro caso perchè nel nostro PC abbiamo l'impostazione americana, controllate nel vostro caso sul pannello di controllo di windows le impostazioni relative alla Data e ora e quelle relative alle Impostazioni internazionali.

Avvertenza !

Il file zip dell'esempio Macro Generazione Calendario Ore Lavorative Con Excel contenente il progetto completo incluso il codice vba in chiaro di cui si è discusso in questo tutorial, è disponibile nell'area 'Videotutorial Download' riservata agli iscritti ai nostri corsi on line.

Nella colonna di destra puoi vedere una anteprima della 'Videotutorial Download area' e quanti e quali altri progetti Access, Excel, VBA sono disponibili.

Acquista anche uno solo dei nostri corsi per avere immediatamente accesso alla 'Videotutorial Download Area': è facile, basta un clic:

http://www.mc2.it






I Tutorial di Microsoft Excel


Home page MC2 E-Learning



Dr.Maurizio Cucchiara

Amministratore unico
MC2 E-Learning S.a r.l.
Formazione per il Web
autore del corso:
"Excel Corso Base"
disponibile on line su questo sito
http://www.mc2.it/html/excel.html

Condividi sui social network:

Social buttons



Iscriviti al nostro
nuovissimo
canale Youtube!


Iscrivi al nostro canale Youtube



Potrai restare aggiornato
sui nostri più recenti
video-tutorial su:
Microsoft Access ed Excel
e molto altro...



Cerca sul web con Google:




Guarda il video-tutorial
sul nostro canale Youtube:


Generazione del calendario ore lavorative con Excel

NOVITA'!

'VIDEO TUTORIAL
DOWNLOAD AREA'

(Fai clic sull'immagine
qui sotto per vedere
una anteprima della
pagina di accesso)



L'accesso è riservato agli iscritti ai corsi on line www.mc2.it

Acquista anche uno solo
dei nostri corsi
per avere immediatamente
accesso alla
'Videotutorial Download Area': è facile, basta un clic:

http://www.mc2.it



Iscriviti alla newsletter: E' GRATIS! (?)

 Nome: (?)

 (?)

e-mail: (?)

 (?)

Newsletter:

formato preferito:

Microsoft Access (?)

Impariamo Excel e il VBA(?)

Internet "da zero"! (?)

Html (?)

Consiglio

(?) = Aiuto

Leggi qui la Informativa completa sulla privacy.