I have a model that I support that uses vba to error proof the document and reinsert formulas to restore defaults and things of that nature. Some the the new spill functionality is causing problems with the way the model is structured.
I added if statements into the vba, to select whether to insert the version of the formula set up for dynamic array aware excel and another version for older excel.
If Application.Build > 11726 Then
.Value2 = "=IFERROR(@CHOOSE(MATCH(@OperatingExpenseUnit,ExpUnitTypeId,)..............
Else
.Value2 = "=IFERROR(CHOOSE(MATCH(OperatingExpenseUnit,ExpUnitTypeId,)..............
End If
What I am realizing though is that the functionality is not consistent across builds of excel.
I guess the problem is that I have seen versions of Build 11726 that do not have dynamic arrays and others that do have them, so my question is: Is there a better way to check for the dynamic array functionality rather than using the application.build syntax? The reason it is becoming problematic is that we sell this model to 3rd parties who maintain their office subscriptions separately within their organizations. Right now about 5 of our 100+ accounts are having issues, while others aren't, and I am hoping to find a solution that will address this for all of our accounts.
