请求开发VBA Sub/Function实现DD-MM-YY转DD-MM-YYYY（含格式校验）

Based on your requirement to convert non-standard dates to the Danish DD-MM-YYYY format while supporting repeated runs (without reprocessing already correct dates), here's a robust VBA solution tailored to your needs:

The VBA Code #

Sub ConvertToDanishDateFormat()
    Dim targetRange As Range
    Dim cell As Range
    Dim parsedDate As Date
    
    ' Prompt user to select the range of dates to process
    On Error Resume Next
    Set targetRange = Application.InputBox("Select the range containing dates to convert", "Select Date Range", Type:=8)
    On Error GoTo 0
    
    ' Exit if no range was selected
    If targetRange Is Nothing Then
        MsgBox "No range selected. Procedure cancelled.", vbInformation
        Exit Sub
    End If
    
    ' Disable screen updates to speed up processing
    Application.ScreenUpdating = False
    
    ' Loop through each cell in the selected range
    For Each cell In targetRange
        ' Skip empty cells
        If cell.Value = vbNullString Then Continue For
        
        ' Attempt to parse the cell's value into a valid date
        On Error Resume Next
        parsedDate = CDate(cell.Value)
        On Error GoTo 0
        
        ' Only proceed if parsing was successful
        If IsDate(parsedDate) Then
            ' Check if the cell is already formatted as DD-MM-YYYY
            If LCase(cell.NumberFormat) <> "dd-mm-yyyy" Then
                ' Update the cell to the parsed date and apply the correct format
                cell.Value = parsedDate
                cell.NumberFormat = "dd-mm-yyyy"
            End If
        Else
            ' Optional: Add a comment to mark unparseable values
            ' If Not cell.Comment Is Nothing Then cell.Comment.Delete
            ' cell.AddComment "Cannot parse as a valid date"
        End If
    Next cell
    
    ' Re-enable screen updates and notify user
    Application.ScreenUpdating = True
    MsgBox "Date conversion completed successfully!", vbInformation
End Sub

Key Features Explained #

• User-Friendly Range Selection: The macro prompts you to select exactly which cells to process, making it flexible for different datasets.
• Reusability: Before converting, the code checks if the cell already uses the dd-mm-yyyy number format. This means you can run the macro as many times as needed without overwriting correctly formatted dates.
• Robust Parsing: Uses VBA's CDate function to handle both date values and text-based dates (supports most common input formats like MM/DD/YYYY, YYYY-MM-DD, etc.).
• Efficiency: Disables screen updates during processing to speed up the macro, especially for large datasets.
• Error Handling: Skips empty cells and optionally marks unparseable values with a comment (remove the comment markers to enable this feature).

How to Use the Macro #

1. Open your Excel workbook containing the date data.
2. Press Alt + F11 to launch the VBA Editor.
3. Insert a new module: Right-click your workbook in the Project Explorer pane > Insert > Module.
4. Paste the code above into the module window.
5. Close the VBA Editor.
6. Run the macro: Press Alt + F8, select ConvertToDanishDateFormat from the list, and click Run.
7. When prompted, select the range of cells with your dates and click OK.

Notes & Customization Tips #

• Locale Adjustments: If your system's regional settings don't match the input date formats (e.g., US-style MM/DD/YYYY dates aren't parsing correctly), you can add custom parsing logic. For example, split text dates by "/" and reconstruct the date using DateSerial.
• Validity Checks: The macro skips invalid dates (like 30-02-2024) since CDate can't parse them. You can add additional logic to flag these if needed.
• Hardcoded Range: If you always process the same column (e.g., column B), replace the range selection prompt with Set targetRange = ThisWorkbook.Sheets("YourSheetName").Range("B:B") (adjust sheet name and column as needed).

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