Excel VBA Precompile Test For Dynamic Array

Question

Have beginning to moderate Excel VBA programming skill attempting to share code across older 32 bit and 64 bit Windows 10 and emulated Office 365 Windows on Mac: where one platform doesn't recognize the recent Dynamic Array additions of "{ }" (multi-cell array) and "@" (implicit intersection operator), and another platform doesn't recognize the "Cells.Replace" command with the Formula2 construct, but am using "Indirect" and "&" (concatenate) for variables with unique identifiers (where standard lookup function fails), but Excel is treating them as an array. The Formula2 is needed in one platform (Mac skips the instruction), the other does not even compile with it (stops altogether), and without it the macro code crashes (Mac has to "read" formulas on the sheet to retrieve a value, not from a declared variable due to its sandbox issues). I'm attempting a precompile inquiry to get around the error test as shown, but it fails. Suggestions for a precompile test to stop Excel from automatically adding curly braces "{}" or at symbols "@"?

#If IsError(Cells.Replace What:=WhatNow, Replacement:=Whatzit, LookAt:=xlPart, FormulaVersion:=xlReplaceFormula2) Then

Answer 1

Because the 'xlReplaceFormula2' must be included in certain Mac environments, but is kicked out by older compilers, the Formula2 construct must be placed in pre-compile (#) if statements. However, a test for new formulas won't execute in pre-compile mode since it creates a type mismatch error. Here's a workaround I found:

a) test for newer formulas to determine if environment creates dynamic array special characters ({ }, @), and b) perform replace command within a precompile statement.

This enables replacing the extra characters so it doesn't interfere with formula operation in older platforms where custom concatenate (&) combinations are used in lieu of PivotTable or rudimentary Lookup formulations.

    Dim WhatNow, Whatzit

   'Define special character
    WhatNow = "@"  'eg, "{", "}"
   'Define formula to edit
    Whatzit = "Cells"  'eg, "If", "Indirect", etc.

   'Test for formula that contains dynamic array
    If Application.Evaluate("=XLOOKUP(1,{1,2,3},{3,2,1})") Then
       'Include 'xlReplaceFormula2' within a precompile (#) 'If' statement
        #If Not Err = 0 Then
         Cells.Replace What:=WhatNow, Replacement:=Whatzit, LookAt:=xlPart, _
             SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
             ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        #Else
        'Else, older platform - use older formula construct
         Cells.Replace What:=WhatNow, Replacement:=Whatzit, LookAt:=xlPart, _
             SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
             ReplaceFormat:=False
        #End If
    End If