Введите время без двоеточия - Советы по Excel

Вопрос Excel на этой неделе исходит от Джона, работающего на Окинаве.

Я создаю электронную таблицу Excel, чтобы отразить отправления и прибытия. В основном будет три ячейки: Фактическое время отправления, Расчетное время в пути и Расчетное время прибытия. Я хотел бы, чтобы человек мог просто ввести (например) 2345, и ячейка автоматически отформатировала дисплей, чтобы показать 23:45. Вместо этого я получаю 0:00, независимо от формулы или форматирования. И расчет не будет отображать ничего, кроме 0:00, если пользователю не удается сдвинуть клавишу и двоеточие. Я знаю, что это кажется простым, однако каждая сохраненная секунда имеет значение, особенно при многократном вводе одинаковых данных в Excel.

Чтобы это работало, вам нужно использовать обработчик событий. Обработчики событий были новинкой в ​​Excel 97 и обсуждались еще в статье «Запуск макроса при каждом изменении значения ячейки в Excel». Однако в том же совете обработчик событий применял другой формат к определенным ячейкам. Это приложение немного отличается, поэтому давайте вернемся к обработчику событий.

Обработчик событий - это небольшой фрагмент кода макроса, который выполняется каждый раз, когда происходит определенное событие. В этом случае мы хотим, чтобы макрос запускался всякий раз, когда вы меняете ячейку. Чтобы настроить обработчик событий, выполните следующие действия:

  • Обработчик событий связан только с одним листом. Начните с этого листа и нажмите Alt-F11, чтобы открыть редактор VB.
  • В верхнем левом окне (Проект - Проект VBA) дважды щелкните имя своего рабочего листа.
  • На правой панели щелкните левый раскрывающийся список и измените общее на Рабочий лист.
  • В правом раскрывающемся списке выберите Изменить.

Это заставит 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

Каждый раз, когда ячейка изменяется, измененная ячейка передается этой программе в переменной с именем «Target». Когда кто-то вводит время с двоеточием на листе, оно оценивается как число меньше единицы. Блок 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.

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