如何使用VBA在Excel批注中查找并替换日期格式

Got it, the issue with your original text replacement code is that dates are dynamic—you can't hardcode every possible MMM DD, YYYY style date to replace. Instead, we need to use regular expressions to identify any date in that format, convert it to a proper date value, then reformat it to YYYY/MM/DD.

Here's a complete VBA sub that does exactly this:

Sub ReformatCommentDates()
    Dim wks As Worksheet
    Dim cmt As Comment
    Dim regex As Object
    Dim matches As Object
    Dim match As Object
    Dim originalText As String
    Dim reformattedText As String
    Dim dateValue As Date
    Dim monthNum As Integer
    
    ' Initialize regex object (late binding, no reference needed)
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "(\w{3}) (\d{1,2}), (\d{4})" ' Matches MMM DD, YYYY (e.g., May 26, 2017)
    regex.Global = True ' Find all matches in a single comment
    
    ' Loop through all worksheets in the workbook
    For Each wks In ThisWorkbook.Worksheets
        ' Loop through all comments in the current sheet
        For Each cmt In wks.Comments
            originalText = cmt.Text
            reformattedText = originalText
            
            ' Check if there are any date matches in the comment
            Set matches = regex.Execute(originalText)
            If matches.Count > 0 Then
                ' Process each matched date
                For Each match In matches
                    ' Convert month name to a number
                    monthNum = Month(DateValue(match.SubMatches(0) & " 1, 2000"))
                    ' Build a proper date object from the matched components
                    dateValue = DateSerial(match.SubMatches(2), monthNum, match.SubMatches(1))
                    
                    ' Replace the original date string with the new format
                    reformattedText = Replace(reformattedText, match.Value, Format(dateValue, "YYYY/MM/DD"))
                Next match
                
                ' Update the comment with the revised text
                cmt.Text reformattedText
            End If
        Next cmt
    Next wks
    
    ' Cleanup objects
    Set regex = Nothing
    Set matches = Nothing
    MsgBox "Date formatting in comments completed!", vbInformation
End Sub

How this works: #

• Regex Pattern: The pattern (\w{3}) (\d{1,2}), (\d{4}) targets any 3-letter month abbreviation, followed by 1-2 digits for the day, a comma, and 4-digit year.
• Global Matching: regex.Global = True ensures we catch all dates in a single comment (if there are multiple).
• Safe Date Conversion: We use DateValue to turn the month name into a numeric value, then DateSerial to create a proper date object. This lets us reliably reformat it to YYYY/MM/DD using Format().
• Full Workbook Coverage: The code checks every comment in every sheet—you can adjust this to target a specific sheet by replacing ThisWorkbook.Worksheets with ThisWorkbook.Worksheets("YourSheetName").

Steps to use: #

1. Open your Excel workbook.
2. Press Alt + F11 to open the VBA Editor.
3. Insert a new module (Right-click your workbook in the Project Explorer > Insert > Module).
4. Paste the code above into the module.
5. Press F5 to run the sub, or assign it to a button for quick access later.

Notes: #

• Handles single-digit days (like "Jan 5, 2020") and two-digit days equally well.
• Leaves all non-date text in comments unchanged—only the matching date strings are modified.
• Uses late binding, so you don't need to enable any references in the VBA Editor. If you prefer early binding, enable "Microsoft VBScript Regular Expressions 5.5" in Tools > References, then declare Dim regex As New RegExp.

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