VBA中与工作表函数DATE等效的函数是DateSerial。它们具有相同的参数。

Option Explicit

Sub ConvertStrToDate()
    Dim rngPeriods As Range, rngPeriod As Range, strDate As String, datDate As Date

    Set rngPeriods = ThisWorkbook.Worksheets("Sheet1").Range("A2:A7")

    For Each rngPeriod In rngPeriods
        strDate = Format(rngPeriod.Value, "0000")
        datDate = DateSerial(Right(strDate, 2), Left(strDate, 2), 1)
        rngPeriod.Value = datDate
    Next rngPeriod

    rngPeriods.NumberFormat = "mmm-yy"

    Set rngPeriods = Nothing
End Sub

注意，我将整数转换为四个字符的字符串，以使解析更容易。