Zadajte čas bez hrubého čreva - tipy pre Excel

Otázka tohto týždňa o programe Excel pochádza od Johna umiestneného na Okinawe.

Staviam tabuľku programu Excel, aby odrážala odchody a prílety. Budú v zásade tri bunky: Skutočný čas odchodu, Odhadovaný čas na ceste a Odhadovaný čas príchodu. Bol by som rád, keby osoba mohla zadať (napríklad) 2345 a nechať bunku automaticky naformátovať displej tak, aby zobrazoval 23:45. Namiesto toho dostávam 0:00, bez ohľadu na vzorec alebo formátovanie. Ak sa používateľovi nepodarí posunúť kláves a dvojbodku, výpočet nezobrazí nič iné ako 0:00. Viem, že sa to zdá byť jednoduché, počíta sa však každá malá sekunda uložená, najmä keď opakovane zadávate podobné údaje do Excelu.

Aby to fungovalo, musíte použiť obslužný program udalostí. Obslužné rutiny udalostí boli v programe Excel 97 nové a boli diskutované späť v časti Spustenie makra pri každej zmene hodnoty bunky v programe Excel. Avšak späť v tomto tipe obslužná rutina udalosti aplikovala na určité bunky iný formát. Táto aplikácia sa mierne líši, preto sa vráťme k obslužnej rutine udalosti.

Obsluha udalosti je malý kúsok makrokódu, ktorý sa vykoná vždy, keď sa stane určitá udalosť. V takom prípade chceme, aby sa makro spustilo vždy, keď zmeníte bunku. Ak chcete nastaviť obslužnú rutinu udalosti, postupujte takto:

  • Obsluha udalosti je spojená iba s jedným hárkom. Začnite od tohto pracovného hárka a stlačením klávesu alt-F11 otvorte editor VB.
  • V ľavom hornom okne (Projekt - Projekt VBA) dvakrát kliknite na názov vášho pracovného hárka.
  • Na pravom paneli kliknite na rozbaľovaciu ponuku vľavo a zmeňte všeobecný na pracovný hárok.
  • V pravej rozbaľovacej ponuke kliknite na možnosť Zmeniť.

To spôsobí, že Excel za vás vopred zadá nasledujúci makro shell:

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

Pri každej zmene bunky sa bunka, ktorá bola zmenená, odovzdá do tohto programu v premennej nazvanej „Cieľ“. Keď niekto zadá čas s dvojbodkou v hárku, vyhodnotí sa to ako číslo menšie ako jeden. Blok If zabezpečí, že bunky sa zmenia iba vtedy, ak sú väčšie ako jedna. Pomocou funkcií left () a right () rozdeľujem vstup používateľa na hodiny a minúty a medzi ne vkladám dvojbodku.

Kedykoľvek užívateľ zadá „2345“, program zmení tento záznam na 23:45.

Možné vylepšenia

Ak chcete obmedziť program tak, aby fungoval iba na stĺpcoch A&B, môžete skontrolovať hodnotu Target.Column a vykonať blok kódu, iba ak sa nachádzate v prvých dvoch stĺpcoch:

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

Ak chcete v pracovnom hárku vykonať zmeny bez zadania dvojbodiek (napríklad je potrebné pridať vzorce alebo zmeniť nadpisy atď.), Môžete obsluhu udalosti zmeniť pomocou tohto krátkeho 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

Ak vezmete tento koncept a zmeníte ho, je potrebné si uvedomiť dôležitý koncept. Keď makro obsluhy udalostí priradí bunke, na ktorú odkazuje Target, novú hodnotu, Excel to započíta ako zmenu pracovného hárka. Ak obsluhu udalostí nakrátko neobrátite, Excel začne rekurzívne volať obsluhu udalostí a získate neočakávané výsledky. Pred vykonaním zmeny v pracovnom hárku v obslužnej rutine udalosti zmeny nezabudnite dočasne pozastaviť spracovanie udalostí pomocou riadku Application.EnableEvents.

Zaujímavé články...