vba - 检查字符串格式

Question

我有一个循环，它循环列中的所有条目:

Dim indexOfDATE As Integer
indexOfDATE = 3
Do Until indexOfDATE - 1 = Cells(Rows.Count, 2).End(xlUp).Row
    Dim tempDate As String
    tempDate = Replace(Sheets("Pirmie Ievaddati").Cells(indexOfDATE, 2).Value, ",", ".")
    tempDate = Replace(tempDate, ".", "/")
    indexOfDATE = indexOfDATE + 1
Loop

我需要检查我的 tempDate 字符串变量是否采用 dd.MM.yyyy 格式，我该如何执行此操作，如果不是，则显示消息框，而不是出现错误？

编辑:

我正在这样做:

Dim indexOfDATE As Integer
indexOfDATE = 3
Do Until indexOfDATE - 1 = Cells(Rows.Count, 2).End(xlUp).Row
    Dim tempDate As String
    tempDate = Replace(Sheets("Pirmie Ievaddati").Cells(indexOfDATE, 2).Value, ",", ".")
    tempDate = Replace(tempDate, ".", "/")
    Dim current As Date
    current = Format(CDate(tempDate), "dd/mm/yyyy")
    Sheets("Pirmie Ievaddati").Cells(indexOfDATE, 2).Value = current
    indexOfDATE = indexOfDATE + 1
Loop

正如您所看到的单元格是一个字符串单元格，我需要确保在进行转换之前 tempDate 字符串的格式正确，否则应用程序将失败。

我想显示用户消息，在单元格(，)中他有不正确的数据

或者可能是转换时的 Try Catch block - 但我在第一个错误后未能停止代码执行

Answer 1

试试这个(未经测试)

Dim indexOfDATE As Long
Dim tempDate As String
Dim current As Date

indexOfDATE = 3

Do Until (indexOfDATE - 1) = Cells(Rows.Count, 2).End(xlUp).Row
    tempDate = Replace(Sheets("Pirmie Ievaddati").Cells(indexOfDATE, 2).Value, ",", "/")

    If IsDate(tempDate) Then
        current = Format(CDate(tempDate), "dd/mm/yyyy")
        With Sheets("Pirmie Ievaddati").Cells(indexOfDATE, 2)
            .NumberFormat = "dd/mm/yyyy"
            .Value = current
        End With
        indexOfDATE = indexOfDATE + 1
    End If
Loop

或更短的版本

Dim indexOfDATE As Long
Dim tempDate As String
Dim current As Date

indexOfDATE = 3

With Sheets("Pirmie Ievaddati").Cells(indexOfDATE, 2)
    Do Until (indexOfDATE - 1) = Cells(Rows.Count, 2).End(xlUp).Row
        tempDate = Replace(.Value, ",", "/")

        If IsDate(tempDate) Then
            current = Format(CDate(tempDate), "dd/mm/yyyy")
            .NumberFormat = "dd/mm/yyyy"
            .Value = current
            indexOfDATE = indexOfDATE + 1
        End If
    Loop
End With