Angi tid uten kolon - Excel-tips

Innholdsfortegnelse

Denne ukens Excel-spørsmål kommer fra John stasjonert i Okinawa.

Jeg bygger et Excel-regneark for å gjenspeile avganger og ankomst. Det vil i utgangspunktet være tre celler: Faktisk avgangstid, estimert tid på vei og beregnet ankomsttid. Jeg ønsker at personen bare skal kunne gå inn (for eksempel) 2345 og at cellen automatisk formaterer skjermen slik at den viser 23:45. Det jeg får i stedet er 0:00, uavhengig av formel eller formatering. Og beregningen viser ikke annet enn 0:00 hvis brukeren ikke klarer å skifte nøkkel og kolon. Jeg vet at det virker enkelt å gjøre det, men hvert eneste sekund som lagres, teller, spesielt når du skriver inn lignende data om og om igjen i Excel.

For å få dette til å fungere, må du bruke en hendelsesbehandler. Eventbehandlere var nye i Excel 97 og ble diskutert tilbake i Kjør en makro hver gang en celleverdi endres i Excel. Imidlertid, i det tipset, brukte hendelsesbehandleren et annet format på visse celler. Denne applikasjonen er litt annerledes, så la oss gå tilbake til hendelsesbehandleren.

En hendelsesbehandler er en liten bit makrokode som blir utført hver gang en bestemt hendelse skjer. I dette tilfellet vil vi at makroen skal kjøre når du endrer en celle. Følg disse trinnene for å sette opp en hendelsesbehandler:

  • En hendelsesbehandler er tilknyttet bare et enkelt regneark. Start fra regnearket og trykk på alt-F11 for å åpne VB-redigereren.
  • Dobbeltklikk navnet på regnearket øverst til venstre (Prosjekt - VBA-prosjekt).
  • I høyre rute klikker du på venstre rullegardin og endrer generelt til Regneark.
  • Velg Endre i høyre rullegardin.

Dette får Excel til å forhåndsinnføre følgende makroskall for deg:

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

Hver gang en celle endres, blir cellen som ble endret sendt til dette programmet i variabelen kalt "Target". Når noen går inn i en tid med et kolon i regnearket, vil det evaluere til et tall mindre enn ett. If-blokken sørger for å bare endre celler hvis de er større enn en. Jeg bruker venstre () og høyre () funksjoner for å dele brukerinngangen i timer og minutter og sette inn et kolon i mellom.

Hver gang brukeren skriver inn "2345", vil programmet endre denne oppføringen til 23:45.

Mulige forbedringer

Hvis du vil begrense programmet til å bare fungere på kolonnene A&B, kan du sjekke verdien av Target.Column og bare utføre kodeblokken hvis du er i de to første kolonnene:

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

Hvis du noen gang vil gjøre endringer i regnearket uten at kolon blir lagt inn (for eksempel må du legge til formler eller endre overskrifter osv.), Kan du slå av hendelsesbehandleren med denne korte makroen:

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

Hvis du tar dette konseptet og endrer det, er det et viktig konsept å være klar over. Når hendelseshåndteringsmakroen tildeler en ny verdi til cellen det refereres til av Target, teller Excel dette som en regnearkendring. Hvis du ikke kort slår av hendelsesbehandlere, vil Excel rekursivt begynne å ringe til hendelsesbehandleren, og du får uventede resultater. Før du endrer et regneark i en endringshendelsesbehandler, må du sørge for å midlertidig avbryte hendelsesbehandling med Application.EnableEvents-linjen.

Interessante artikler...