A volte abbiamo la necessità di calcolare quanto tempo intercorre tra due orari:
Molti diranno: "Elementare! che problema può essere mai questo. VBA ha un esercito di funzioni per manipolare il tempo".
È vero: ma che succederebbe se in un foglio Excel invece di trovare 10.05 trovassi 10,05?
Una catastrofe!!!
Ecco un esempio di ciò che ho dovuto scrivere in Excel per calcolare il tempo tra l'ora di entrata ed l'ora di uscita dal lavoro:
=INT((INT(F8)*60+(F8-INT(F8))*100-(INT(E8)*60+(E8-INT(E8))*100))/60)+((INT(F8)*60+(F8-INT(F8))*100-(INT(E8)*60+(E8-
INT(E8))*100))-INT((INT(F8)*60+(F8-INT(F8))*100-(INT(E8)*60+(E8-INT(E8))*100))/60)*60)/100
non è mostruoso?
Purtroppo nelle varie guide e manuali che riusciamo a trovare un po' ovunque, troviamo come trattare gli orari scritti nella normale sintassi (10.05), col punto separatore, ma è difficile riuscire a trovare qualcosa su come comportarsi su orari scritti con una differente sintassi (10,05), cioè con la virgola separatrice, cioè in formato decimale.
Finalmente una intuizione: perché non creare una funzione che possa trattare indifferentemente sia l'uno che l'altro formato liberando così il popolo di Excel dalla sudditanza del formato cella e/o della virgola/punto?
ATTENZIONE!
prima di passare al nocciolo del discorso è importante capire come vengono visti gli orari scritti nelle due diverse sintassi sopra menzionate:
in una cella (la A15) digitiamo 10.05 usando il punto che troviamo nella tastiera alfanumerica in un'altra cella (la B15) digitiamo 10,05 usando la virgola oppure il punto del tastierino numerico.
Questi saranno i risultati:
Queste sono già le prime grandi differenze.
A questo punto andiamo ad indagare anche in VBA (io vi invito a fare questa esperienza anche se può risultare banale in quanto vi farà toccare con mano quello che a fatica si va a leggere)
In un modulo qualsiasi scriviamo queste semplici istruzioni:
Sub Prova()
A = Range("A15").Value
B = Range("B15").Value
Range("A16").Value = A
Range("B16").Value = B
End Sub
vedendo i risultati dopo l'esecuzione delle istruzioni noteremo che in A16 è stato scritto 0,420138888888889 ed in B16 è stato scritto 10,05.
Come mai la presenza di quel numero così mostruoso invece del semplice orario scritto col punto?
Per rispondere a questo vi rimando al tasto destro del mouse su quella cella (la A16) per controllarne il formato cella.
Per ora basta sapere che è così che l'orario o, in ogni caso, il tempo in genere, è visto dal sistema e quindi anche dal VBA.
Facciamo un'altra prova: aggiungiamo le seguenti istruzioni alle istruzioni di sopra in modo da vedere il formato dei due valori:
Range("A17").Value = TypeName(A)
Range("B17").Value = TypeName(B)
i risultati saranno Double e Double quindi i due valori sono visti da VBA alla stessa maniera
Vogliamo fare una ulteriore verifica?
Andiamo a controllare la posizione della virgola o del punto nei valori inseriti nelle celle. Modifichiamo sempre le ultime due righe. Questa volta facciamo un'altra aggiunta:
andiamo a cercare la presenza del punto e della virgola tra i due dati
Range("A18").Value = InStr(1, A, ".") ' tra gli apici mettiamo una volta il punto
Range("B18").Value = InStr(1, B, ".")
Range("A19").Value = InStr(1, A, ",") ' tra gli apici mettiamo una volta la virgola
Range("B19").Value = InStr(1, B, ",")
andiamo a leggere i risultati ma vi avviso già da ora che troveremo delle sorprese:
se tra gli apici abbiamo messo una virgola i risultati saranno 2 e 3,
ma se abbiamo messo un punto il risultato sarà 0 (zero) e 0.
Ma come mai?
La risposta la troviamo nella prova che abbiamo fatto quando abbiamo trascritto nelle celle sottostanti i due valori:
Range("A16").Value = A
Range("B16").Value = B
Dopo queste considerazioni e questi test sono riuscito a creare una, anzi due funzioni:
Le due funzioni, inoltre, sono in grado di calcolare anche orari a cavallo della mezzanotte.
Prima funzione: Calcolo dei minuti:
Public Function CalcMinTot(Ora1, Ora2)
Dim A, B, A1, A2, A3
A = Len(Ora1)
B = Len(Ora2)
' Ora1 ed Ora2 rappresentano rispettivamente l'ora di inizio l'ora di fine di un evento
' A e B sono le discriminanti per determinare la sintassi usata per scrivere le ore e cioè:
' se l'ora inserita nel foglio usa la sintassi col punto il numero di cifre con cui è vista
' dal VBA saranno sicuramente superiori a 5
' altrimenti saranno minori di 6
If A > 5 Then
' siamo di fronte ad un orario riconosciuto come tale e per estrarre da questo le ore ed
' i minuti possiamo usare tranquillamente le funzioni che VBA ci mette a disposizione
' quindi in A1 vengono immessi i minuti dell'ora di ingresso tramite questa formula
A1 = (Hour(Ora1) * 60 + Minute(Ora1))
ElseIf A < 6 Then
' in caso contrario ci troviamo di fronte ad un numero normale e per calcolare i minuti
' da immettere in A1 usiamo questa semplice formula matematica
A1 = Int(Ora1) * 60 + (Format(Ora1 - Int(Ora1), "0.00") * 100)
End If
If B > 5 Then
' stesso discorso appena fatto per immettere in A2 i minuti dell'ora di uscita
A2 = (Hour(Ora2) * 60 + Minute(Ora2))
ElseIf B < 6 Then
A2 = Int(Ora2) * 60 + (Format(Ora2 - Int(Ora2), "0.00") * 100)
End If
' a questo punto abbiamo in A1 ed A2 l'ora di entrata e l'ora di uscita espressi in minuti ed
' in formato Double per cui con la semplice operazione matematica possiamo
' determinano i minuti trascorsi tra le due ore
CalcMinTot = A2 - A1
' ma...
' che succede se l'ora di inizio e l'ora di fine attività sono a cavallo della mezzanotte?
' Il valore in CalcMinTot sarà negativo e, di conseguenza, sbagliato per cui non basta
' convertirlo semplicemente in positivo ma visto che oramai stiamo lavorando con normali
' numeri basta un piccolo artificio:
' mettiamo in A3 il valore in minuti che per noi mortali è l'ora 24 e ci comportiamo come di seguito:
If CalcMinTot < 0 Then
A3 = 24 * 60
CalcMinTot = A3 - A1 + A2
End If
End Function
Seconda funzione: Calcolo delle ore:
Arrivati a questo punto il discorso si semplifica di molto.
Abbiamo appena costruito una funzione che ci calcola i minuti intercorsi tra due eventi (entrata ed uscita, partenza ed arrivo, ecc....).
Per calcolare le ore intercorse tra i due eventi ci è sufficiente prelevare i minuti dalla funzione CalcMinTot e, visto che a questo punto siamo in pieno regime numerico - matematico e non più temporale, è sufficiente applicare al valore che ci ritorna quelle elementari operazioni di matematica che studiamo anche a scuola.
Public Function CalcOreTot(Inizio, Fine)
Dim Minuti, A1, A2
Minuti = CalcMinTot(Inizio, Fine)
A1 = Int(Minuti / 60)
A2 = Minuti - (A1 * 60)
CalcOreTot = (A1 + A2 / 100)
End Function
È chiaro? Spero di sì, per cui questo è tutto per quanto riguarda i commenti al codice delle due funzioni.
In caso contrario io sto qui per qualsiasi delucidazione.
Uso delle funzioni
Fin qui la scrittura ed alcune semplici spiegazioni sulle due funzioni da me pensate.
Ma perché queste funzionino occorre in qualche modo usarle.
Allora possiamo agire in due modi, come con delle normali funzioni native di Excel.
Ora vediamo come:
dal foglio di Excel
oppure
il risultato sarà una cosa del genere:
=CalcOreTot(A4;B4)
Nel codice VBA
Assegniamo a due variabili il valore contenuto nelle due celle interessate: A = Range("A4").Value: B = Range("B4").Value
poniamo le due variabili come argomenti della funzione che preferiamo: CalcOreTot(A, B)
al ritorno dalle funzioni:
Attenzione:
mentre per testare la maggior parte delle istruzioni che trovate in questa ed in molte pagine è sufficiente un semplice copia ed incolla, le formule che trovate con il metodo relativo per le Proprietà FormulaR1C1 occorre adattarle
Ecco di seguito le tre versioni:
' cattura del risultato delle funzione tramite variabili
Sub prova()
A = Range("A4").Value
B = Range("B4").Value
C = CalcMinTot(A, B)
D = CalcOreTot(A, B)
End Sub
' inserimento diretto nelle celle del risultato
Sub prova()
A = Range("A4").Value
B = Range("B4").Value
Range("D15").Value = CalcMinTot(A, B)
Range("E15").Value = CalcOreTot(A, B)
End Sub
' uso della notazione R1C1
Sub prova3()
Visti i commenti inseriti nel listato, penso sia inutile aggiungerne altri. Infatti se riusciamo a leggere con calma il listato, le istruzioni sono abbastanza chiare.
Spero sia tutto chiaro. Buon lavoro