Altering formula with VBA string functions

[nyconfidential]

Hi all - I'm trying to look at cell formulas in a few different worksheets and remove a file path reference if it exists(eg [=c:\test\Test.xlsm]Worksheet1'!rngTest would become =Worksheet1'!rngTest). Unfortunately, the string functions I normally use in Access vba do not work when dealing with a formula - I get a "Type Mismatch" error every time I encounter a cell with a formula in it. Is there a relatively simple way to edit the string within a formula without receiving that type mismatch error? Thanks in advance, code below...

Public Sub RemovePath()
Dim varray As Variant ' Variant array, cell data stored here
Dim WrkSht As Worksheet
Dim rRng As Range
Dim i As Long, j As Long
Dim SplitArray() As String ' Split array - will use to break up path string
Dim strFormula As String


For Each WrkSht In ActiveWorkbook.Worksheets
    Set rRng = WrkSht.UsedRange
    varray = rRng.Value
        For i = LBound(varray, 1) To UBound(varray, 1)
            For j = LBound(varray, 2) To UBound(varray, 2)
            Debug.Print varray(i, j)
             If InStr(varray(i, j), ".xlsm]") > 0 Then ' Look for "xlsm" as part of string
                SplitArray() = Split(varray(i, j), ".xlsm]") 'Split up string, use ".xlsm" as a delimiter
                varray(i, j) = Trim(SplitArray(UBound(SplitArray))) ' set the cell value to the workbook/docvar, without the file path
              End If
            Next j
        Next i
        rRng.Value = varray
    
    'Application.StatusBar = "Updating " & Str(WrkSht.Name)
    'Debug.Print WrkSht.Name
    WrkSht.Calculate
Next WrkSht




End Sub