Введіть час без двокрапки - Поради Excel

Запитання Excel цього тижня надходить від Джона, дислокованого на Окінаві.

Я створюю таблицю Excel, щоб відображати вильоти та прибуття. В основному буде три комірки: фактичний час виїзду, передбачуваний час проїзду та передбачуваний час прибуття. Я хотів би, щоб особа могла просто ввести (наприклад) 2345 і дати комірці автоматично відформатувати дисплей, щоб відобразити 23:45. Натомість я отримую 0:00, незалежно від формули та форматування. І, обчислення не відображатиме нічого, крім 0:00, якщо користувач не змінить клавішу та двокрапку. Я знаю, що це здається простим, однак, враховується кожна маленька секунда, збережена, особливо при повторному введенні подібних даних у Excel.

Щоб це працювало, вам потрібно використовувати обробник подій. Обробники подій були новими в Excel 97 і обговорювались у розділі Запуск макросу кожного разу, коли змінюється значення комірки в Excel. Однак ще в тій підказці обробник подій застосовував інший формат до певних комірок. Цей додаток дещо відрізняється, тому давайте переглянемо обробник подій.

Обробник події - це невеликий шматочок макрокоду, який виконується кожного разу, коли відбувається певна подія. У цьому випадку ми хочемо, щоб макрос запускався щоразу, коли ви змінюєте комірку. Щоб налаштувати обробник подій, виконайте такі дії:

  • Обробник подій пов'язаний лише з одним аркушем. Почніть з цього аркуша та натисніть alt-F11, щоб відкрити редактор VB.
  • У верхньому лівому вікні (Проект - Проект VBA) двічі клацніть назву робочого аркуша.
  • На правій панелі клацніть лівий випадаючий список і змініть загальний на Worksheet.
  • У правому спадному меню виберіть Змінити.

Це призведе до того, що 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

Цікаві статті...