Excel VBA 时间戳和用户名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17702257/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA timestamp and username
提问by user2591771
The code below detects data when inputted into column A and automatically inserts the current user into the cell to the right. I would also like this code to add a timestamp as well. I need to log the user name and time. Any suggestions?
下面的代码在输入到 A 列时检测数据，并自动将当前用户插入到右侧的单元格中。我还希望此代码也添加时间戳。我需要记录用户名和时间。有什么建议？
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range
On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("A:A"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
With rCell.Offset(0, 1)
.Value = UserName()
End With
Else
rCell.Offset(0, 1).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
Public Function UserName()
UserName = Environ$("UserName")
End Function
采纳答案by Katstevens
You could just use something like date & " " & time. This will output the date and time concatenated like so:
你可以使用类似的东西date & " " & time。这将输出连接的日期和时间，如下所示：
17/07/2013 11:49:39 PM
Here is your code with the date/time value added into the next column:
这是将日期/时间值添加到下一列的代码：
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rCell As Range Dim rChange As Range
On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("A:A"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
rCell.Offset(0, 1).Value = UserName()
rCell.Offset(0, 2).Value = date() & " " & time() <-- added line
Else
rCell.Offset(0, 1).Clear
End If
Next
End If
