How to Store the Formula result into a Variable in Excel VBA

Excel Formula to get the Workbook Path and store into a Variable in VBA
Sub Formula_Variable()
Dim SrcSht As Worksheet

Application.DisplayAlerts = False

    Set SrcSht = ThisWorkbook.Sheets("Src_Sht")

    F1 = Evaluate("=LEFT(CELL(""FileName""),FIND(""["",CELL(""FileName""),1)-1)")
    
    SrcSht.Range("C3").Value = F1

End Sub

Notes :

Here the Formula "=LEFT(CELL(""FileName""),FIND(""["",CELL(""FileName""),1)-1)" will returns the Active workbook path.

The Evaluate() Function returns the formula result.