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

Without knowing your code, I'm guessing you are writing formulas to a worksheet? Why not check the version first and write the appropriate code depending on the version? Or don't write formulas at all. Just do the calculations within VBA. — Ron Rosenfeld
– Ron Rosenfeld, Commented May 13, 2021 at 0:47
Yes, writing formulas to the worksheet (as required in Mac environment). But, also have prewritten formulas. re: Run a check for the version: if it were that easy, I would have done that. Again, the emulated Office 365 Windows 10 environment on Mac is indistinguishable from the actual OS and any test for version comes up Office 365 Windows 10. — Stephen Rush
– Stephen Rush, Commented May 14, 2021 at 16:24
There are as many formulas on the existing sheet as there is macro code in VBA and is too numerous. So, the answer is a VBA check. Suggestions only please. — Stephen Rush
– Stephen Rush, Commented May 14, 2021 at 16:32
Hmm. Version check for VBA version is returning Office 365 Windows 10? How are you doing that? I don't have a Mac to test. I'll do some more research. — Ron Rosenfeld
– Ron Rosenfeld, Commented May 14, 2021 at 17:14
Oh. The OS emulator is Parallels, which creates a virtual drive to pose as Win10 that lets Ofc365 function. So, VBA having "If Application.OperatingSystem Like ""Mac""" fails because it is running inside the emulated Windows. Any version check that follows that command is ignored (eg, "If Val(Application.Version) <") and skips to my WinOS checker. But, the keyboard and sandbox behave like Mac. — Stephen Rush
– Stephen Rush, Commented May 14, 2021 at 22:55

Stephen Rush · Accepted Answer · 2021-05-17 21:56:36Z

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

Collectives™ on Stack Overflow

Excel VBA Precompile Test For Dynamic Array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related