使用VBA在Excel中跟踪时间

最新推荐文章于 2024-06-11 17:41:25 发布

culiao2169

最新推荐文章于 2024-06-11 17:41:25 发布

阅读量430

点赞数 1

文章标签： java python excel linux 机器学习

原文链接：https://contexturesblog.com/archives/2012/02/28/track-time-in-excel-with-vba/

版权

Last week, we saw the steps for entering project start and stop times in Excel, by using keyboard shortcuts. Then, Excel formulas can calculate the total project time, based on the start and stop times.

上周，我们看到了使用键盘快捷键在Excel中输入项目开始和结束时间的步骤。然后，Excel公式可以根据开始和停止时间来计算总项目时间。

One problem with using the keyboard shortcut to enter the current time is that it doesn't include the seconds in the time. All the times are entered as 00 seconds, as you can see in the screen shot below.

使用键盘快捷键输入当前时间的一个问题是它不包含时间中的秒。所有时间都输入00秒，如下面的屏幕截图所示。

So, if you're keeping track of very short tasks, or need precision to the second, the keyboard shortcut for entering time won't help you.

因此，如果您要跟踪非常短的任务，或者需要精确到秒，那么输入时间的键盘快捷键将无济于事。

使用Excel VBA输入当前时间 (Enter Current Time with Excel VBA)

Instead of using the default shortcut for entering the current time, you can use Excel VBA to enter the time.

您可以使用Excel VBA输入时间，而不必使用默认的快捷方式输入当前时间。

For example, you can select a cell on the worksheet, and run the following macro, to enter the current time in the active cell. The time will automatically include hours, minutes, and seconds.

例如，您可以在工作表上选择一个单元格，然后运行以下宏，以在活动单元格中输入当前时间。时间将自动包括小时，分钟和秒。

Sub EnterTime()
    ActiveCell.Value = Time
End Sub

双击输入当前时间 (Enter the Current Time with a Double-Click)

It's not very convenient to manually run a macro when you want to enter the current time. So, to make it easier, you can use Event code, and automatically run the macro when a cell is double-clicked.

若要输入当前时间，手动运行宏不是很方便。因此，为了简化操作，您可以使用事件代码，并在双击单元格时自动运行宏。

In this example, the start and stop times will be entered in a range named TimeEntry. That range is selected in the screen shot below – cells B2:C4

在此示例中，开始时间和停止时间将在名为TimeEntry的范围内输入。在下面的屏幕快照中选择了该范围–单元格B2：C4

The following code is stored on the code module for the worksheet where you want to enter the times.

以下代码存储在要输入时间的工作表的代码模块上 。

The code only runs if you double-click an empty cell (.Value = "") in the TimeEntry range.

只有在TimeEntry范围内双击一个空单元格(.Value =“”)，该代码才会运行。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
Dim rng As Range
Set rng = Range("TimeEntry")
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, rng) Is Nothing Then
    Application.EnableEvents = False
    Cancel = True 'stop the edit mode
    With Target
        If .Value = "" Then
            .Value = Time
        End If
    End With
End If
Application.EnableEvents = True
End Sub

After you put this procedure onto the worksheet module, double-click in an empty Start or Stop time cell. The current time, including seconds, is automatically entered.

在将此步骤放到工作表模块上之后，双击一个空的“开始”或“停止”时间单元。当前时间(包括秒)将自动输入。

移至下一个单元格 (Move to the Next Cell)

As a refinement to the previous procedure, you can add a line that moves you to the next cell, after entering the time. In the following revised code, the cell that is one column to the right is activated, after the time is entered.

作为对上一过程的改进，您可以在输入时间后添加一行以将您移至下一个单元格。在下面的修订代码中，输入时间后，将激活右侧一列的单元格。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
Dim rng As Range
Set rng = Range("TimeEntry")
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, rng) Is Nothing Then
    Application.EnableEvents = False
    Cancel = True 'stop the edit mode
    With Target
        If .Value = "" Then
            .Value = Time
            .Offset(0, 1).Activate
        End If
    End With
End If
Application.EnableEvents = True
End Sub