Immettere l'ora senza due punti - Suggerimenti per Excel

La domanda su Excel di questa settimana viene da John di stanza a Okinawa.

Sto costruendo un foglio di calcolo Excel per riflettere le partenze e gli arrivi. Ci saranno fondamentalmente tre celle: ora di partenza effettiva, tempo di viaggio stimato e ora di arrivo stimato. Vorrei che la persona potesse inserire (ad esempio) 2345 e fare in modo che la cella formatta automaticamente il display per mostrare 23:45. Quello che ottengo invece è 0:00, indipendentemente dalla formula o dalla formattazione. Inoltre, il calcolo non visualizzerà altro che 0:00 se l'utente non riesce a spostare la chiave ei due punti. So che sembra semplice farlo, tuttavia, ogni piccolo secondo salvato conta, specialmente quando si immettono dati simili più e più volte in Excel.

Per farlo funzionare, è necessario utilizzare un gestore di eventi. I gestori di eventi erano nuovi in ​​Excel 97 e sono stati discussi in Eseguire una macro ogni volta che il valore di una cella cambia in Excel. Tuttavia, in quel suggerimento, il gestore di eventi applicava un formato diverso a determinate celle. Questa applicazione è leggermente diversa, quindi rivisitiamo il gestore di eventi.

Un gestore di eventi è un piccolo frammento di codice macro che viene eseguito ogni volta che si verifica un determinato evento. In questo caso, vogliamo che la macro venga eseguita ogni volta che modifichi una cella. Per configurare un gestore di eventi, segui questi passaggi:

  • Un gestore di eventi è associato a un solo foglio di lavoro. Inizia da quel foglio di lavoro e premi alt-F11 per aprire l'editor VB.
  • Nella finestra in alto a sinistra (Progetto - Progetto VBA) fare doppio clic sul nome del foglio di lavoro.
  • Nel riquadro di destra, fai clic sul menu a discesa a sinistra e cambia generale in Foglio di lavoro.
  • Nel menu a discesa a destra, seleziona Modifica.

Ciò farà sì che Excel preinserisca la seguente macro shell per te:

Private Sub Worksheet_Change(ByVal Target As Range) UserInput = Target.Value If UserInput> 1 Then NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2) Application.EnableEvents = False Target = NewInput Application.EnableEvents = True End If End Sub

Ogni volta che una cella viene modificata, la cella che è stata modificata viene passata a questo programma nella variabile chiamata "Target". Quando qualcuno inserisce un'ora con i due punti nel foglio di lavoro, restituirà un numero inferiore a uno. Il blocco If si assicura di cambiare le celle solo se sono maggiori di uno. Uso le funzioni left () e right () per suddividere l'input dell'utente in ore e minuti e inserire i due punti in mezzo.

Ogni volta che l'utente immette "2345", il programma cambierà questa voce in 23:45.

Possibili miglioramenti

Se vuoi limitare il programma a lavorare solo sulle colonne A&B, puoi controllare il valore di Target.Column ed eseguire il blocco di codice solo se ti trovi nelle prime due colonne:

Private Sub Worksheet_Change(ByVal Target As Range) ThisColumn = Target.Column If ThisColumn 1 Then NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2) Application.EnableEvents = False Target = NewInput Application.EnableEvents = True End If End If End Sub

Se vuoi apportare modifiche al foglio di lavoro senza inserire i due punti (ad esempio, devi aggiungere formule o modificare intestazioni, ecc.) Puoi disattivare il gestore eventi con questa breve macro:

Sub TurnEventHanderOff() Application.EnableEvents = False End Sub You can turn event handlers back on with this macro: Sub TurnEventHanderOff() Application.EnableEvents = True End Sub

Se prendi questo concetto e lo cambi, c'è un concetto importante di cui essere consapevoli. Quando la macro del gestore eventi assegna un nuovo valore alla cella a cui fa riferimento Target, Excel lo conteggia come una modifica del foglio di lavoro. Se non si disattivano brevemente i gestori di eventi, Excel inizierà a chiamare in modo ricorsivo il gestore di eventi e si ottengono risultati imprevisti. Prima di apportare una modifica a un foglio di lavoro in un gestore di eventi di modifica, assicurarsi di sospendere temporaneamente la gestione degli eventi con la riga Application.EnableEvents.

Articoli interessanti...