Ange tid utan kolon - Excel-tips

Innehållsförteckning

Den här veckans Excel-fråga kommer från John stationerad i Okinawa.

Jag bygger ett Excel-kalkylblad för att återspegla avgångar och ankomster. Det kommer i grunden att finnas tre celler: Faktisk avgångstid, beräknad tidsträcka och beräknad ankomsttid. Jag skulle vilja att personen bara kan komma in (till exempel) 2345 och att cellen automatiskt formaterar skärmen så att den visar 23:45. Vad jag får istället är 0:00, oavsett formel eller formatering. Och beräkningen visar inte annat än 0:00 om användaren inte flyttar tangent och kolon. Jag vet att det verkar enkelt att göra det, men varje liten sekund som sparas räknas, särskilt när man anger liknande data om och om igen i Excel.

För att detta ska fungera måste du använda en händelsehanterare. Eventhanterare var nya i Excel 97 och diskuterades tillbaka i Kör ett makro varje gång ett cellvärde förändras i Excel. Men tillbaka i det tipset tillämpade händelsehanteraren ett annat format på vissa celler. Denna applikation är lite annorlunda, så låt oss återvända till händelsehanteraren.

En händelsehanterare är en liten bit makrokod som körs varje gång en viss händelse inträffar. I det här fallet vill vi att makrot ska köras när du ändrar en cell. Så här ställer du in en händelsehanterare:

  • En händelsehanterare är associerad med bara ett enda kalkylblad. Börja från det kalkylbladet och tryck på alt-F11 för att öppna VB-redigeraren.
  • I det övre vänstra fönstret (Project - VBA Project) dubbelklickar du på namnet på ditt kalkylblad.
  • I den högra rutan klickar du på vänster rullgardinsmeny och ändrar allmänt till kalkylblad.
  • Välj Ändra i det högra rullgardinsmenyn.

Detta kommer att leda till att Excel förut anger följande makroskal åt dig:

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

Varje gång en cell ändras skickas cellen som ändrades till detta program i variabeln "Target". När någon går in i en tid med ett kolon i kalkylbladet utvärderas det till ett antal mindre än en. If-blocket ser till att endast ändra celler om de är större än en. Jag använder funktionerna vänster () och höger () för att dela upp användarinmatningen i timmar och minuter och infoga ett kolon däremellan.

Närhelst användaren skriver in "2345", kommer programmet att ändra denna post till 23:45.

Möjliga förbättringar

Om du vill begränsa programmet till att bara fungera på kolumner A&B kan du kontrollera värdet på Target.Column och bara köra kodblocket om du befinner dig i de två första kolumnerna:

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

Om du någonsin vill göra ändringar i kalkylbladet utan att kolon kommer in (till exempel måste du lägga till formler eller ändra rubriker, etc.) kan du vända till händelsehanteraren med detta korta makro:

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

Om du tar detta koncept och ändrar det finns det ett viktigt koncept att vara medveten om. När händelsehanteringsmakroet tilldelar ett nytt värde till cellen som Target refererar till, räknas Excel som en ändring av kalkylbladet. Om du inte kort vänder dig om händelsehanterare börjar Excel rekursivt att ringa till händelsehanteraren och du får oväntade resultat. Innan du gör en ändring av ett kalkylblad i en ändringshändelsehanterare, se till att tillfälligt avbryta händelsehantering med raden Application.EnableEvents.

Intressanta artiklar...