Wprowadź czas bez dwukropka - wskazówki programu Excel

Spisie treści

Pytanie dotyczące programu Excel w tym tygodniu pochodzi od Johna stacjonującego na Okinawie.

Tworzę arkusz kalkulacyjny Excel, aby odzwierciedlić odloty i przyloty. Będą zasadniczo trzy komórki: rzeczywisty czas odlotu, szacowany czas w trasie i szacowany czas przybycia. Chciałbym, aby ta osoba mogła po prostu wpisać (na przykład) 2345 i automatycznie sformatować komórkę tak, aby pokazywała 23:45. Zamiast tego otrzymuję 0:00, niezależnie od formuły lub formatowania. Obliczenie nie wyświetli niczego poza 0:00, jeśli użytkownik nie zmieni klawisza i dwukropka. Wiem, że wydaje się to proste, jednak liczy się każda zaoszczędzona sekunda, zwłaszcza przy wielokrotnym wprowadzaniu podobnych danych do programu Excel.

Aby to zadziałało, musisz użyć programu obsługi zdarzeń. Procedury obsługi zdarzeń były nowością w programie Excel 97 i zostały omówione ponownie w temacie Uruchamianie makra za każdym razem, gdy wartość komórki zmienia się w programie Excel. Jednak w tej wskazówce program obsługi zdarzeń stosował inny format do niektórych komórek. Ta aplikacja jest nieco inna, więc wróćmy do obsługi zdarzeń.

Procedura obsługi zdarzeń to mały fragment kodu makra, który jest wykonywany za każdym razem, gdy ma miejsce określone zdarzenie. W tym przypadku chcemy, aby makro było uruchamiane za każdym razem, gdy zmienisz komórkę. Aby skonfigurować procedurę obsługi zdarzeń, wykonaj następujące kroki:

  • Program obsługi zdarzeń jest powiązany tylko z jednym arkuszem. Zacznij od tego arkusza i naciśnij alt-F11, aby otworzyć edytor VB.
  • W lewym górnym oknie (Projekt - Projekt VBA) kliknij dwukrotnie nazwę swojego arkusza.
  • W prawym okienku kliknij lewą listę rozwijaną i zmień ogólne na Arkusz.
  • W menu po prawej stronie wybierz Zmień.

Spowoduje to, że program Excel wstępnie wprowadzi następującą powłokę makra:

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

Za każdym razem, gdy zmieniona zostanie komórka, zmieniona komórka jest przekazywana do tego programu w zmiennej o nazwie „Cel”. Kiedy ktoś wprowadzi czas z dwukropkiem w arkuszu, otrzyma liczbę mniejszą niż jeden. Blok Jeśli zapewnia zmianę komórek tylko wtedy, gdy są większe niż jeden. Używam funkcji left () i right (), aby podzielić dane wejściowe użytkownika na godziny i minuty i wstawić między nimi dwukropek.

Za każdym razem, gdy użytkownik wpisze „2345”, program zmieni ten wpis na 23:45.

Możliwe ulepszenia

Jeśli chcesz ograniczyć program do działania tylko na kolumnach A i B, możesz sprawdzić wartość Target.Column i wykonać blok kodu tylko wtedy, gdy jesteś w pierwszych dwóch kolumnach:

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

Jeśli kiedykolwiek zechcesz wprowadzić zmiany w arkuszu bez wpisywania dwukropków (na przykład musisz dodać formuły lub zmienić nagłówki itp.), Możesz wyłączyć obsługę zdarzeń za pomocą tego krótkiego makra:

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

Jeśli weźmiesz tę koncepcję i zmienisz ją, jest ważna koncepcja, o której należy pamiętać. Gdy makro obsługi zdarzeń przypisuje nową wartość do komórki, do której odwołuje się Target, program Excel liczy to jako zmianę w arkuszu. Jeśli nie wyłączysz na krótko programów obsługi zdarzeń, program Excel zacznie rekurencyjnie wywoływać procedurę obsługi zdarzeń i otrzymasz nieoczekiwane wyniki. Przed dokonaniem zmiany w arkuszu w programie obsługi zdarzeń zmian należy tymczasowo zawiesić obsługę zdarzeń za pomocą wiersza Application.EnableEvents.

Interesujące artykuły...