Quando la turnistica prevede fasce orarie in pronta disponibilità, ogni mese bisogna preparare almeno due prospetti : il primo serve alla Portineria per sapere chi contattare al bisogno e il secondo serve all’Ufficio rilevazione presenze per il riconoscimento delle indennità. Di solito la compilazione del foglio richiede tempo e impegno. In questo post mostrerò il mio prospetto e il sistema per la compilazione automatica.

Il modello per la reperibilità che uso è ripreso dal prospetto della programmazione mensile, per cui riprendo il file già visto nella prima parte di questa serie:

Impostazione grafica del foglio delle reperibilità

Il foglio va duplicato, rinominato per distinguerlo dagli altri e va variata la leggenda con le fasce di reperibilità previste. Io uso i colori per le sigle e le righe alterne per cercare di aiutare l’occhio del lettore, quindi tramite la formattazione condizionale (FC) ho inserito le regole stando attento al loro ordine di applicazione (le righe devono essere le prime, cioé stare in fondo all’elenco, le sigle invece ultime). Dopo aver controllato di averle inserite correttamente cambiando mese con l’apposito pulsante si può eliminare anche questo:

Ora non rimane che implementare il sistema di compilazione automatica. Usare le formule di Excel in questo caso mi sembra davvero arduo, per cui introdurrò un nuovo argomento, il VBA

Il linguaggio VBA e la scheda Sviluppo

Non è sicuramente questo il contesto giusto per spiegare i codici e il linguaggio VBA (Visual Basic for Application), infatti mi limiterò a cercare di far capire almeno vagamente cos’è e come funziona con le poche nozioni che servono per implementare il codice che seguirà e capire cosa fa. Le spiegazioni arrivano da un utente che probabilmente definirlo di medio livello è esagerato e sono dirette a un utente che non ha mai sentito parlare di VBA.

Chi non ha mai sentito parlare di VBA e preme per la prima volta ALT+F11, richiamando la schermata dell’editor VBA, rimarrà disorientato di fronte a una schermata che sembra non aver nulla a che fare con Excel. Questa è l’interfaccia per la gestione del codice. È incoraggiante sapere che partendo da questa schermata si possono implementare soluzioni di enorme potenzialità e flessibilità. Queste soluzioni, almeno nel mio caso, sono due: le macro che sono una sequenza di istruzioni operative (quando sono eseguite fanno qualcosa a degli oggetti come le celle, gli intervalli, i fogli, ecc) e le funzioni VBA che sono delle nuove funzioni aggiuntive che possono essere usate sia in Excel che nelle macro.

Per quanto mi riguarda, si è trattato di prendere una minima confidenza con i comandi basilari, poi cercando su Internet si trovano soluzioni (quasi) pronte a tanti problemi e con le nozioni base sono riuscito ad adattarle ai miei scopi. In Internet si trovano anche guide, informazioni, esempi, per non parlare dei forum frequentati da persone davvero competenti e disponibili. Tanto per citarne uno dove ho trovato diversi aiuti www.forumexcel.it

Questi argomenti sono considerati avanzati tanto che la scheda Sviluppo in cui ci sono i comandi di interesse è nascosta di default. Per renderla visibile è sufficiente seguire le istruzioni che trovate qui.

Il formato con l’attivazione delle macro

La prima cosa da fare quando si inserisce del codice VBA in una cartella di Excel è salvare il file in un formato che permette il salvataggio del codice inserito: il formato di file corretto è quello con estensione xlsm:

In questo formato il file diventa un file considerato pericoloso e non solo da Excel perché un malintenzionato potrebbe avere inserito codice malevolo. Excel chiederà una conferma all’apertura.

Registrazione ed esecuzione delle macro

Nella parte bassa a sinistra di Excel si trova il pulsante per la registrazione delle macro. Si tratta di una registrazione delle operazioni fatte dall’utente, operazioni che verranno eseguite pari pari ogni volta che si comanda l’esecuzione della macro registrata. Premendo questo pulsante apparirà una finestra dove si potrà scegliere il nome da dare alla macro. Dopo aver premuto “OK”, tutte le operazioni saranno registrate fino a quando si fa clic di nuovo su questo pulsante (senza usare i comandi della scheda Sviluppo). Il registratore delle macro è molto utile per registrare brevi macro da incollare in macro complesse.

Per adesso, quando avrete premuto il pulsante “OK”, premetelo di nuovo il pulsante per la fine registrazione senza fare niente. Tornando nella finestra dell’editor, si vede la struttura di una macro: in alto tra la parola Sub (che sta per macro) e le parentesi l’editor inserisce il nome della macro (attenzione alle restrizioni nella definizione del nome), segue in verde un commento (sempre preceduto dall’apostrofo) dove si può inserire per esempio cosa fa la macro e poi termina con la parola End Sub (fine macro). Fra commento e End Sub va inserito il codice delle operazioni:

Come detto, oltre alle macro esistono le funzioni che sono funzioni aggiuntive a quelle già inserite in Excel (tipo CONTA.SE). Tanto per capire le potenzialità del VBA, queste poche righe di codice dicono a Excel di vedere se un determinato foglio esiste tra tutti quelli inseriti in una cartella:

Function SheetExists(nomefoglio As String) As Boolean
     On Error Resume Next
     SheetExists = Worksheets(nomefoglio).Name = nomefoglio
     On Error GoTo 0
 End Function

Ora, se in una cella inseriamo la formula “=SheetExists(“GEN”)”, Excel scorre tutti i fogli e se trova un foglio nominato GEN restituisce VERO, al contrario restituisce FALSO. Ma le funzioni possono essere usate anche nelle macro, ad esempio, questa sotto rinomina il foglio corrente con il nome abbreviato del mese estratto dalla data in C4 a meno che un foglio con lo stesso nome esista già. I commenti spiegano cosa fa la riga di codice che li precede:

Sub Foglio_rinomina()
 'rinomina un foglio dopo aver controllato che non esista già
     Dim data As Long    'la variabile “data” è un numero
     Dim mese As String    'la variabile "mese" è un testo
     data = Range("C4").Value    '“data” è uguale al valore della cella C4
     mese = UCase(Format(data, "mmm"))    '“mese” è uguale al nome del mese di "data"
     If SheetExists(mese) Then    'SE il foglio con il nome del mese esiste…
         MsgBox "Il foglio " & mese & " esiste già", vbExclamation    ' avvertimi che il foglio esiste già…
         Exit Sub    ' … e poi esci dalla macro
     Else    ' altrimenti
         ActiveSheet.Name = mese    ' rinomina il foglio con il nome del mese…
         MsgBox "Foglio rinominato: " & ActiveSheet.Name    '… e dimmi il nome usato
     End If    'esci dal ciclo SE
End Sub

Sotto, un’altra macro per intuire il funzionamento di VBA. Questa duplica il foglio corrente e lo mette subito dopo il foglio corrente e avverte di dove ci si trova:

Sub CopiaFoglio()
 'duplica il foglio corrente
 Dim nomefoglio As String, fogliopart As String 'queste variabili sono stringhe
     fogliopart = ActiveSheet.Name '"fogliopart" è il nome del foglio corrente
     Sheets(fogliopart).Copy After:=Sheets(fogliopart) ' copia il foglio corrente dopo il foglio corrente
     MsgBox "Sei nel foglio: " & ActiveSheet.Name ' avvertimi dove mi trovo
End Sub

Per mandare in esecuzione una macro si può far clic sul tasto Macro nella scheda Sviluppo oppure si premono i tasti ALT+F8. Apparirà la finestra sotto dove dopo aver selezionato la macro corretta si preme Esegui:

Molto più comodamente si può inserire nel foglio un tasto (scheda sviluppo > Inserisci > Controlli modulo > pulsante (controllo modulo) > trascinare sul foglio e associare la macro da attivare alla pressione del pulsante. Con un clic destro si può modificare il testo del pulsante. Questo sistema è più sicuro perché sceglie sicuramente la macro giusta e la fa girare nel foglio giusto per la quale magari l’avete progettata. Sotto due tasti:

Esiste inoltre un sistema molto comodo per vedere cosa fa ogni singola riga di codice: è sufficiente cliccare nel codice da testare e premere F8 riga per riga. La riga di codice che andrà in esecuzione sarà evidenziata in giallo, in questo modo è possibile vedere ad esempio se gli intervalli che la macro deve trattare sono corretti oppure vedere se i valori assunti dalle variabili sono corretti (finestra Variabili locali) e molto altro.

Proviamo ora a usare il VBA per i nostri scopi che, per come è costruito il modello, si limita a due azioni: un “copia e incolla” tra fogli diversi ed un “sostituisci”. Seguendo i passaggi non dovreste faticare ad adattarli ad un eventuale cartella già in vostro possesso.

Riportare i turni programmati dal mese d’interesse

Innanzitutto serve qualcosa per indicare Excel in quale foglio andare a copiare i turni. Io uso convalida data su una cella denominata per avere una scelta veloce e precisa. È sufficiente scegliere una cella, denominarla (nel mio caso “MeseRep”, in questo modo in seguito potete anche spostare la cella e la macro continuerà a funzionare) e poi nella scheda Dati > Strumenti dati > Convalida dati > Convalida dati > Elenco > Origine e inserire senza virgolette “GEN;FEB;MAR;APR;MAG;GIU;LUG;AGO;SET;OTT;NOV;DIC”

Sotto si vede la cella formattata con un riempimento grigio:

Ora si può inserire il codice che fa il copia e incolla. Come si può leggere nei commenti, prima prepara il foglio cancellando eventuali turni presenti e poi riporta dal mese indicato le celle d’interesse:

Sub CompilaFoglioRep()
'Cancella e riporta cal/nomi/turni
'
    Dim M As String, nomefoglio As String
    nomefoglio = ActiveSheet.Name
    M = Range("MeseRep").Value 'la variabile M è uguale al valore della cella "MeseRep"

    If SheetExists(M) Then 'Inizia un ciclo SE: se Il foglio del mese esiste...
    'Application.ScreenUpdating = False 'Evita l'aggiornamento del monitor
        Range("A4:AG22").Select 'seleziona l'intervallo nel foglio corrente
        Selection.ClearContents 'cancella il contenuto
        
        'riporta calendario 31gg da mese selezionato
        Sheets(M).Range("C4:AG5").Copy
        Sheets(nomefoglio).Range("C4").PasteSpecial Paste:=xlPasteValues
        'riporta nomi
        Sheets(M).Range("A6:B22").Copy
        Sheets(nomefoglio).Range("A6").PasteSpecial Paste:=xlPasteValues
        'riporta sigle
        Sheets(M).Range("C6:AG22").Copy
        Sheets(nomefoglio).Range("C6").PasteSpecial Paste:=xlPasteValues
    
    Else 'altrimenti...
        MsgBox "Foglio " & M & " non trovato" 'avvertimi che non hai trovato il foglio
        Exit Sub 'esci dalla macro
    End If 'chiudi il ciclo SE
    
    Application.CutCopyMode = False
    
    Range("A1").Select'seleziona la cella A1
    Application.ScreenUpdating = True 'riprendi l'aggiornamento del monitor
End Sub

A questo punto il foglio è riempito (non fatevi ingannare dalla FC):

La sostituzione

Per la sostituzione si può usare il registratore della macro e poi copiare e incollare il codice generato da una macro all’altra. Attivando la registrazione della macro (qualsiasi nome va bene), si seleziona l’intervallo dove sono inseriti i turni (bisogna includere tutti i 31 giorni del mese) e poi con il comando sostituisci si fanno tutte le opportune sostituzioni:

Completate le sostituzioni si può fermare il registratore. Tornando nell’editor di VBA, nel modulo nel quale abbiamo già inserito il codice oppure in un modulo creato in automatico, c’è il codice appena registrato. Dovrebbe essere simile a quello sotto: in alto ci sono gli intervalli selezionati (io ho selezionato 2 intervalli tenendo premuto CTRL) ea cui seguono le sostituzioni, nel mio caso sono parecchie. Per esempio sotto ho tolto le “A” maiuscole:

...
Range("C6:AG6,C8:AG22").Select
Range("C8").Activate
Selection.Replace What:="A", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
...

Ora bisogna copiare il codice posto fra il commento in verde e la scritta End Sub e incollarlo nella macro che copia e incolla prima della riga “Range(“A1″).Select” .

Se in seguito noterete che qualche turno non viene cancellato o modificato dalla macro, è sufficiente aprire l’editor, copiare e incollare un blocco di sostituzione e modificare cosa deve cercare e sostituire. Questa cerca i turni che iniziano con “@” e li elimina :

...
Selection.Replace What:="@*", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
...

Aggiungendo un tasto per eseguire la macro…

… in un secondo si avrà il foglio delle reperibilità pronto per la stampa o l’invio:

Va detto che una grafica simile aiuta a rilevare la distribuzione delle reperibilità, ma può far confusione, sopratutto quando i reperibili sono più di uno in una giornata: capitava ogni tanto qualche chiamata alla persona sbagliata in piena notte. Per questo per la Portineria ho adottato una grafica più semplice con sigle a due colori, il giallo per il giorno e il blu per la notte.

Qui a fianco potete scaricare il file usato durante l’articolo: Turni-Post6.zip (1241 download )

Buon lavoro!