Въведете време без дебело черво - Съвети на Excel

Въпросът за Excel тази седмица идва от Джон, разположен в Окинава.

Изграждам електронна таблица на Excel, за да отразяват заминаванията и пристиганията. По принцип ще има три клетки: действително време на заминаване, прогнозно време за пътуване и прогнозно време на пристигане. Бих искал човекът просто да може да въведе (например) 2345 и да накара клетката автоматично да форматира дисплея да показва 23:45. Вместо това получавам 0:00, независимо от формулата или форматирането. И, изчислението няма да покаже нищо освен 0:00, ако потребителят не успее да премести клавиша и двоеточието. Знам, че изглежда лесно да го направя, но всяка малка секунда, записана, се брои, особено при повторно въвеждане на подобни данни в Excel.

За да направите тази работа, трябва да използвате манипулатор на събития. Манипулаторите на събития бяха нови в Excel 97 и бяха обсъдени в Изпълнение на макрос всеки път, когато се променя стойността на клетката в Excel. Обаче в този съвет съветникът на събитията прилагаше различен формат към определени клетки. Това приложение е малко по-различно, така че нека отново посетим манипулатора на събития.

Манипулаторът на събития е малка част от макрокода, която се изпълнява всеки път, когато се случи определено събитие. В този случай искаме макросът да се изпълнява всеки път, когато промените клетка. За да настроите манипулатор на събития, изпълнете следните стъпки:

  • Манипулаторът на събития е свързан само с един работен лист. Започнете от този работен лист и натиснете alt-F11, за да отворите редактора на VB.
  • В горния ляв прозорец (Project - VBA Project) щракнете двукратно върху името на вашия работен лист.
  • В десния прозорец щракнете върху лявото падащо меню и променете общото на Работен лист.
  • В дясното падащо меню изберете Промяна.

Това ще накара Excel предварително да въведе следната обвивка на макроса за вас:

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

Всеки път, когато се променя клетка, клетката, която е променена, се предава на тази програма в променливата, наречена "Цел". Когато някой въведе време с двоеточие в работния лист, то ще се изчисли на число по-малко от единица. Блокът If гарантира, че променя клетките само ако са по-големи от една. Използвам функциите left () и right (), за да разбия въведеното от потребителя на часове и минути и да вмъкна двоеточие между тях.

Всеки път, когато потребителят въведе „2345“, програмата ще промени този запис на 23:45.

Възможни подобрения

Ако искате да ограничите програмата да работи само върху колони A&B, можете да проверите стойността на Target.Column и да изпълните кодовия блок само ако сте в първите две колони:

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

Ако някога искате да направите промени в работния лист, без да се въвеждат двоеточие (например трябва ли да добавите формули или да промените заглавия и т.н.), можете да обърнете манипулатора на събития с този кратък макрос:

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

Ако вземете тази концепция и я промените, има важна концепция, която трябва да знаете. Когато макросът за обработка на събития присвоява нова стойност на клетката, посочена от Target, Excel отчита това като промяна на работния лист. Ако не завъртите за кратко манипулаторите на събития, Excel рекурсивно ще започне да извиква манипулатора на събития и ще получите неочаквани резултати. Преди да направите промяна в работен лист в манипулатора на събития за промени, не забравяйте временно да спрете обработката на събития с реда Application.EnableEvents.

Интересни статии...