Writing formula in VBA results in Type Mismatch 13

Question

Overlying question: Is it possible to code formulas in VBA that save the values as a variable, without putting them in a cell on the worksheet?

I get the error for the line starting with "My_Date = Left(Replace" ... I assume it will also be an issue for the "My_Take = Right(Replace" line.

The function is supposed to take numbers out of strings:

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

This formats the numbers as a date and take without any letters:

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

Answer 1

I am a bit new to Stack Overflow so I wasnt sure how to set GSerg's comment to the answer, but all I had to do was change each "Replace" to "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)

Thank you GSerg!!