Long array formula into VBA

Thanks to @TMS and his extensive knowledge the following formula was offered to me as a wonderful solution:

Formula:

=IF($A2="","",IF(MAX(ISNUMBER(MID($A2,ROW($A$1:INDEX($A:$A,LEN($A2))),1)+0)*ROW($A$1:INDEX($A:$A,LEN($A2))))=0,A2,   IFERROR(REPLACE($A2,  MAX(ISNUMBER(MID($A2,ROW($A$1:INDEX($A:$A,LEN($A2))),1)+0)*ROW($A$1:INDEX($A:$A,LEN($A2))))+1,  1,  " " & CHAR(149) & " "),$A2)&""))

But because I need a dynamic header in the resulting spreadsheet I must use VBA.

Since it has to be an array, there is a limit of 255 characters. But because of my lack of knowledge with any of this I am unable to divide correctly:

Selection.FormulaArray = _
        "=IF(RC1="""","""",IF(MAX(ISNUMBER(MID(RC1,ROW(R1C1:INDEX(C1,LEN(RC1))),1)+0)*ROW(R1C1:INDEX(C1,LEN(RC1))))=0,RC[-1],   IFERROR(REPLACE(RC1,  MAX(ISNUMBER(MID(RC1,ROW(R1C1:INDEX(C1,LEN(RC1))),1)+0)*ROW(R1C1:INDEX(C1,LEN(RC1))))+1,  1,  "" "" & CHAR(149) & "" ""),RC1)&""""))"

I would like to apply the following workaround but do not know where the mistake is:

Sub LongArrayFormula()
     Dim theFormulaPart1 As String
     Dim theFormulaPart2 As String
     theFormulaPart1 = "=IF(RC1="""","""",IF(MAX(ISNUMBER(MID(RC1,ROW(R1C1:INDEX(C1,LEN(RC1))),1)+0)*ROW(R1C1:INDEX(C1,LEN(RC1))))=0,RC[-1],""X_X_X)"")"
                          
     theFormulaPart2 = "IFERROR(REPLACE(RC1,  MAX(ISNUMBER(MID(RC1,ROW(R1C1:INDEX(C1,LEN(RC1))),1)+0)*ROW(R1C1:INDEX(C1,LEN(RC1))))+1,  1,  "" "" & CHAR(149) & "" ""),RC1)&""""))"
     With ActiveSheet.Range("B2")
         .FormulaArray = theFormulaPart1
        .Replace """X_X_X)"")", theFormulaPart2
 
    End With
     
End Sub

Maybe the code does not work with RC1 formula. I do not know. I am using Excel 2010.

In the end I would like to apply this formula to column B as long as there is content in Column A.

Thank you so much for your help.