编写VBA子程序统计会话回车次数并记录文件打开时间

Hey there! Let's build this VBA solution to track your Enter key presses per session and log them with timestamps. Here's a complete, tested implementation that fits all your requirements:

Full Code Implementation #

Paste this code into the ThisWorkbook module in your Excel file (press Alt+F11 to open the VBA editor, then double-click ThisWorkbook in the Project Explorer):

' Module-level variables to persist data across the current session
Dim enterKeyCount As Integer
Dim sessionStartTime As Date

Private Sub Workbook_Open()
    ' Initialize session data when the file opens
    enterKeyCount = 0
    sessionStartTime = Now() ' Capture the exact date/time the file was opened
    
    ' Optional: Add headers if they don't exist for clarity
    With ThisWorkbook.Sheets(1)
        If .Range("A1").Value = vbNullString Then .Range("A1").Value = "Enter Key Count"
        If .Range("B1").Value = vbNullString Then .Range("B1").Value = "Session Start Time"
    End With
End Sub

Private Sub Workbook_SheetKeyPress(ByVal Sh As Object, ByVal KeyAscii As Integer)
    ' Increment count every time the Enter key is pressed (KeyAscii = 13)
    If KeyAscii = 13 Then
        enterKeyCount = enterKeyCount + 1
    End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Find the next empty row in column A (starting from row 2)
    Dim nextLogRow As Long
    nextLogRow = ThisWorkbook.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    ' Ensure we start logging at row 2 if it's the first session
    If nextLogRow < 2 Then nextLogRow = 2
    
    ' Write the session data to the log
    With ThisWorkbook.Sheets(1)
        .Cells(nextLogRow, "A").Value = enterKeyCount
        .Cells(nextLogRow, "B").Value = sessionStartTime
        ' Format the timestamp for readability
        .Cells(nextLogRow, "B").NumberFormat = "yyyy-mm-dd hh:mm:ss"
    End With
    
    ' Save the file automatically to preserve logs
    ThisWorkbook.Save
End Sub

How This Works #

1. Session Data Storage #

• enterKeyCount: A module-level variable that keeps track of how many times Enter is pressed during the current file session. It resets to 0 every time you open the file.
• sessionStartTime: Captures the exact date and time the file was opened, so we can link each count to its corresponding session.

2. Opening the File (Workbook_Open) #

• Initializes the count to 0 and records the start time.
• Adds default headers to row 1 if they're missing, making your log table easier to read.

3. Tracking Enter Key Presses (Workbook_SheetKeyPress) #

• Listens for key presses across all worksheets in the workbook. Every time Enter is pressed (identified by KeyAscii=13), it increments the count.
• If you only want to track presses on a specific sheet, add a check like If Sh.Name = "YourSheetName" Then before incrementing the count.

4. Saving the Log on Close (Workbook_BeforeClose) #

• Finds the next empty row in column A to ensure each session's data gets written to a new line.
• Writes the count to column A and the session start time to column B, starting at row 2 for the first session, then row 3, row 4, etc.
• Formats the timestamp column for consistent, readable output.
• Automatically saves the file so you don't lose your logs if you forget to save manually.

Quick Tips #

• Save your file as an Excel Macro-Enabled Workbook (.xlsm) to ensure the code works when you reopen it.
• Enable macros in Excel's security settings (you'll see a prompt when opening the file—click "Enable Content").
• Test it out: Open the file, press Enter a few times, close the file, then reopen it. You'll see your first log entry in row 2, and subsequent sessions will add new rows below.

内容的提问来源于stack exchange，提问作者Liriscia Savegna