Workaround Needed For Cross Platform Dynamic Array Formula Interference

%3CLINGO-SUB%20id%3D%22lingo-sub-2460351%22%20slang%3D%22en-US%22%3EWorkaround%20Needed%20For%20Cross%20Platform%20Dynamic%20Array%20Formula%20Interference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2460351%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EOffice%20365%20worksheet%20will%20not%20function%20in%20a%20cross%20platform%20shared%20environment%20where%20Concatenate%20(%22%26amp%3B%22)%2C%20Indirect()%20and%20Cell()%26nbsp%3Bare%20used%20for%20extreme%20vector%20variable%20positioning%20of%20non-table%20lookup%20(conditional%20column%20and%20row%20attributes%20for%20custom%20ranges)%2C%20not%20for%20dynamic%20arrays.%26nbsp%3B%20While%20this%20is%20effective%20in%20solving%20the%20dilemma%20of%20a%20non-sequential%20lookup%20feature%20in%20a%20custom%20report%2C%20its%20functionality%20is%20complicated%20by%20the%20presence%20of%20Implicit%20Intersection%20(%22%40%22)%20and%20curly%20braces%20(%22%7B%20%7D%22)%20in%20versions%20that%20return%20%23Name%2C%20and%20%23Ref%2C%20respectively.%26nbsp%3B%20This%20is%20especially%20troublesome%20for%20older%20platforms%26nbsp%3Bwhen%20the%20older%20version%20Excel%20is%20%22infected%22%20with%20the%20update%20from%20shared%20content%20across%20a%20small%20business%20platform.%26nbsp%3B%20For%20example%2C%20VBA%20execution%20results%20in%20abnormal%20termination%20of%20code%20-%20even%20with%20%22On%20Error%22%20error%20checking%2C%20or%2C%20%22skips%22%20arguments%20that%20try%20to%20store%20cell%20content.%26nbsp%3B%20Office%20365%20emulated%20on%20the%20Mac%2C%20with%20its%20non-MS%20keyboard%20arrangement%20and%20unaccounted%20Sandbox%20programming%2C%20requires%20extra%20steps%20like%20specifying%20what%20cell%20is%20selected%20and%20storing%20results%20of%20a%20condition%20in%20a%20cell%20for%20certain%20operations.%26nbsp%3B%20Which%20is%20a%20catch-22%20if%20VBA%20code%20cannot%20recognize%20the%20value%20when%20sharing%20data%20on%20an%20older%20version%20of%20Excel%3F%20How%20can%20VBA%20force%20a%20cell%20to%20be%20non-dynamic%20without%20the%20use%20of%20user%20intervention%20and%20without%20the%20use%20of%20%22%40%22%3F%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20following%20arguments%26nbsp%3Bfail%20to%20remove%20the%20curly%20braces%20from%20dynamic%20array%20formula%20conflicts%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3B%26nbsp%3BActiveCell.Formula%20%3D%20ActiveCell%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20Cells.Replace%20What%3A%3D%22%7B%22%2C%20Replacement%3A%3D%22%22%2C%20LookAt%3A%3DxlPart%2C%26nbsp%3BSearchOrder%3A%3DxlByRows%2C%20_%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20MatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse%2C%26nbsp%3BReplaceFormat%3A%3DFalse%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2460351%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Office 365 worksheet will not function in a cross platform shared environment where Concatenate ("&"), Indirect() and Cell() are used for extreme vector variable positioning of non-table lookup (conditional column and row attributes for custom ranges), not for dynamic arrays.  While this is effective in solving the dilemma of a non-sequential lookup feature in a custom report, its functionality is complicated by the presence of Implicit Intersection ("@") and curly braces ("{ }") in versions that return #Name, and #Ref, respectively.  This is especially troublesome for older platforms when the older version Excel is "infected" with the update from shared content across a small business platform.  For example, VBA execution results in abnormal termination of code - even with "On Error" error checking, or, "skips" arguments that try to store cell content.  Office 365 emulated on the Mac, with its non-MS keyboard arrangement and unaccounted Sandbox programming, requires extra steps like specifying what cell is selected and storing results of a condition in a cell for certain operations.  Which is a catch-22 if VBA code cannot recognize the value when sharing data on an older version of Excel? How can VBA force a cell to be non-dynamic without the use of user intervention and without the use of "@"?  

 

The following arguments fail to remove the curly braces from dynamic array formula conflicts:

    ActiveCell.Formula = ActiveCell

    Cells.Replace What:="{", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _

        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    

0 Replies