Excel VBA – How to add dynamic array formula

by

Good question, and I looked it up…

In short:

Use =Cells(x,y).Formula2 instead of =Cells(x,y).Formula

Explaination:

The @ that shows is called the implicit intersection operator. From MS docs:

Implicit intersection logic reduces many values to a single value. Excel did this to force a formula to return a single value, since a
cell could only contain a single value. If your formula was returning
a single value, then implicit intersection did nothing (even though it
was technically being done in the background).

But why does it appear in your newer Excel O365? Well, Range.Formula uses IIE (implicit intersection) thus adding the @ to basically undo your dynamic array functionality. UNIQUE is a new dynamic array function. So, to write this out in code, you should use the Range.Formula2 property (or Range.Formula2R1C1 if you use R1C1 notation). These properties use AE (array evaluation) and is now the default.

  • Here is an informative doc from MS on the subject which explains the difference between Formula and Formula2 in more detail.

  • If you want to know more about the implicit intersection operator then have a look at this

  • I answered another question earlier on that involved implicit intersection with an example on how that actually works here if one finds it interesting.

More Related Contents:

  1. Want to concatenate multiple row data in in cell along with its values if present. Emp Id should not come if no data available infromt of it
  2. Excel Number Separation [closed]
  3. Excel formula checklist in employees
  4. Sequence of numbers based on start and end value of 2 cells [closed]
  5. Error in finding last used cell in Excel with VBA
  6. Excel UNIQUE Across Columns
  7. Filling any empty cells with the value above
  8. Find last used cell in Excel VBA
  9. Count unique values in Excel
  10. Concatenate column headers if value in rows below is non-blank
  11. How do I apply a SumIf Formula in a VBA Sub? Getting Error 1004
  12. Countif With Multiple OR Criteria
  13. How to extract text within a string of text
  14. How to roundup a number to the closest ten?
  15. How to pause for specific amount of time? (Excel/VBA)
  16. Easiest way to loop through a filtered list with VBA?
  17. Quicker way to get all unique values of a column in VBA?
  18. Parsing an ISO8601 date/time (including TimeZone) in Excel
  19. Two column lookup in table array using INDEX and MATCH
  20. Concatenate cell data into another data if values matches
  21. How to convert 32 bit VBA code into 64 bit VBA code
  22. Outputting Excel rows to a series of text files
  23. How to create a range from two ranges in VBA?
  24. XML parse VBA excel (function trip, & MSXML2.DOMDocument)
  25. every possible combination of the contents of 2 columns in excel
  26. Excel VBA – select multiple columns not in sequential order
  27. How to check whether certain sheets exist or not in Excel-VBA? [duplicate]
  28. Application.WorksheetFunction.Match method
  29. Excel – Using COUNTIF/COUNTIFS across multiple sheets/same column
  30. Excel telling me my blank cells aren’t blank

Leave a Comment