Zadejte čas bez dvojtečky - Excel tipy

Excelská otázka tohoto týdne pochází od Johna umístěného na Okinawě.

Stavím tabulku Excel, abych odrážela odlety a přílety. Budou v zásadě tři buňky: Skutečný čas odjezdu, Odhadovaný čas na cestě a Odhadovaný čas příjezdu. Chtěl bych, aby osoba mohla zadat (například) 2345 a nechat buňku automaticky naformátovat displej tak, aby zobrazoval 23:45. Místo toho dostávám 0:00, bez ohledu na vzorec nebo formátování. A výpočet nezobrazí nic jiného než 0:00, pokud se uživateli nepodaří posunout klíč a dvojtečku. Vím, že se to zdá jednoduché, nicméně každá malá sekunda se započítává, zvláště když zadáváte podobná data znovu a znovu v aplikaci Excel.

Aby to fungovalo, musíte použít obslužnou rutinu události. Obslužné rutiny událostí byly v aplikaci Excel 97 nové a byly diskutovány zpět v části Spustit makro při každé změně hodnoty buňky v aplikaci Excel. Zpátky v tomto tipu však obslužná rutina události používala na určité buňky jiný formát. Tato aplikace se mírně liší, tak se pojďme znovu podívat na obslužnou rutinu události.

Obslužná rutina události je malá část kódu makra, která se provede pokaždé, když dojde k určité události. V tomto případě chceme, aby se makro spustilo vždy, když změníte buňku. Chcete-li nastavit obslužnou rutinu události, postupujte takto:

  • Obslužná rutina události je přidružena pouze k jednomu listu. Začněte od tohoto listu a stisknutím alt-F11 otevřete editor VB.
  • V levém horním okně (Project - VBA Project) dvakrát klikněte na název vašeho listu.
  • V pravém podokně klikněte do levého rozevíracího seznamu a změňte obecný na List.
  • V rozevíracím seznamu vpravo vyberte Změnit.

To způsobí, že Excel za vás předem zadá následující prostředí maker:

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

Při každé změně buňky se buňka, která byla změněna, předá tomuto programu v proměnné zvané „Cíl“. Když někdo zadá čas s dvojtečkou v listu, vyhodnotí se na číslo menší než jedna. Blok If zajišťuje změnu buněk pouze v případě, že jsou větší než jedna. Používám funkce left () a right () k rozdělení uživatelského vstupu na hodiny a minuty a vložení dvojtečky mezi ně.

Kdykoli uživatel zadá „2345“, program tento záznam změní na 23:45.

Možná vylepšení

Pokud chcete omezit program tak, aby fungoval pouze na sloupcích A&B, můžete zkontrolovat hodnotu Target.Column a spustit blok kódu, pouze pokud jste v prvních dvou sloupcích:

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

Pokud budete někdy chtít provést změny v listu bez zadávání dvojteček (například je třeba přidat vzorce nebo změnit nadpisy atd.), Můžete pomocí tohoto krátkého makra otočit obslužnou rutinu události:

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

Pokud vezmete tento koncept a změníte ho, je třeba si uvědomit důležitý koncept. Když makro obslužné rutiny události přiřadí novou hodnotu buňce, na kterou odkazuje Target, Excel to počítá jako změnu listu. Pokud stručně neobsluhujete obslužné rutiny událostí, Excel začne rekurzivně volat obslužnou rutinu události a získáte neočekávané výsledky. Před provedením změny v listu v obslužné rutině události změny nezapomeňte dočasně pozastavit zpracování událostí pomocí řádku Application.EnableEvents.

Zajímavé články...