Introduceți timpul fără colon - Sfaturi Excel

Întrebarea Excel din această săptămână vine de la John staționat în Okinawa.

Construiesc o foaie de calcul Excel pentru a reflecta plecările și sosirile. Vor fi practic trei celule: Ora efectivă de plecare, Timpul estimat de parcurs și Ora estimată de sosire. Aș dori ca persoana respectivă să poată introduce doar (de exemplu) 2345 și ca celula să formateze automat afișajul pentru a afișa 23:45. Ceea ce primesc în schimb este 0:00, indiferent de formulă sau formatare. Și calculul nu va afișa altceva decât 0:00 dacă utilizatorul nu reușește să schimbe tasta și două puncte. Știu că pare simplu să faceți acest lucru, cu toate acestea, fiecare secundă salvată contează, mai ales atunci când introduceți date similare din nou și din nou în Excel.

Pentru ca acest lucru să funcționeze, trebuie să utilizați un gestionar de evenimente. Handlerele de evenimente erau noi în Excel 97 și au fost discutate înapoi în Executarea unei macro-uri de fiecare dată când se schimbă valoarea celulei în Excel. Cu toate acestea, înapoi în acel sfat, gestionarul de evenimente aplica un alt format anumitor celule. Această aplicație este ușor diferită, așa că haideți să revizuim gestionarul de evenimente.

Un gestionar de evenimente este un mic fragment de cod macro care se execută de fiecare dată când se întâmplă un anumit eveniment. În acest caz, dorim ca macro-ul să ruleze ori de câte ori schimbați o celulă. Pentru a configura un gestionar de evenimente, urmați acești pași:

  • Un gestionar de evenimente este asociat cu o singură foaie de lucru. Începeți din acea foaie de lucru și apăsați alt-F11 pentru a deschide editorul VB.
  • În fereastra din stânga sus (Proiect - Proiect VBA) faceți dublu clic pe numele foii de lucru.
  • În panoul din dreapta, faceți clic pe meniul drop-down din stânga și schimbați generalul în Foaie de lucru.
  • În meniul derulant din dreapta, alegeți Modificare.

Acest lucru va face ca Excel să introducă în prealabil următoarea shell macro:

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

De fiecare dată când se schimbă o celulă, celula care a fost modificată este transmisă acestui program în variabila numită „Țintă”. Când cineva introduce o dată cu două puncte în foaia de lucru, acesta va fi evaluat la un număr mai mic decât unul. Blocul If asigură schimbarea celulelor numai dacă acestea sunt mai mari decât una. Folosesc funcțiile stânga () și dreapta () pentru a împărți intrarea utilizatorului în ore și minute și pentru a insera două puncte între ele.

Ori de câte ori utilizatorul introduce „2345”, programul va schimba această intrare la 23:45.

Îmbunătățiri posibile

Dacă doriți să limitați programul să funcționeze numai pe coloanele A&B, puteți verifica valoarea Target.Column și puteți executa blocul de cod numai dacă vă aflați în primele două coloane:

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

Dacă vreți vreodată să faceți modificări la foaia de lucru fără a fi introduse două puncte (de exemplu, trebuie să adăugați formule sau să schimbați titluri, etc.) puteți opri gestionarul de evenimente cu această macrocomandă scurtă:

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

Dacă luați acest concept și îl schimbați, există un concept important de care să fiți conștienți. Atunci când macrocomanda de gestionare a evenimentelor atribuie o nouă valoare celulei la care face referire Target, Excel contează aceasta ca o modificare a foii de lucru. Dacă nu vă întoarceți pe scurt la gestionarele de evenimente, atunci Excel va începe recursiv apelând gestionarul de evenimente și veți obține rezultate neașteptate. Înainte de a face o modificare la o foaie de lucru într-un gestionar de evenimente de modificare, asigurați-vă că ați suspendat temporar gestionarea evenimentelor cu linia Application.EnableEvents.

Articole interesante...