在 VBA 中编写公式会导致类型不匹配 13

首要问题：是否可以在 VBA 中编写公式，将值保存为变量，而不将它们放入工作表上的单元格中？

我收到以“My_Date = Left（Replace”）开头的行的错误...我认为这也是“My_Take = Right（Replace”行）的问题。

该函数应该从字符串中取出数字：

Public Function SplitText(pWorkStr As String, pIsNumber As Boolean) As String

Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkStr)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkStr, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

这会将数字格式化为日期，并且不带任何字母：

Sub Fill_Date_and_Take()

    Dim NAMEoFILE As String

    Dim My_Date As String
    Dim My_Take As String

    NAMEoFILE = "I love dogs 09-20-17 Take Number 2.xlsx"

    My_Date = Left(Replace(Replace(Replace(SplitText(NAMEoFILE, True), 2, 0, "-"), 5, 0, "-"), 8, 0, "_"), 7)
        MsgBox (My_Date)

    My_Take = Right(Replace(Replace(Replace(SplitText(NAMEoFILE, True), 2, 0, "-"), 5, 0, "-"), 8, 0, "_"), 1)
        MsgBox (My_Take)

End Sub

我对 Stack Overflow 有点陌生，所以我不确定如何将 GSerg 的评论设置为答案，但我所要做的就是将每个“替换”更改为“WorksheetFunction.Replace”！

My_Date = Left(WorksheetFunction.Replace(WorksheetFunction.Replace(WorksheetFunction.Replace(SplitText(NAMEoFILE, True), 2, 0, "-"), 5, 0, "-"), 8, 0, "_"), 7)

My_Take = Right(WorksheetFunction.Replace(WorksheetFunction.Replace(WorksheetFunction.Replace(SplitText(NAMEoFILE, True), 2, 0, "-"), 5, 0, "-"), 8, 0, "_"), 1)

谢谢你，GSerg！