Forum Discussion
Implicit intersection operator: @
I am writing a formula in a cell in vba.
the formula is ws.Range("A1").Formula = "=UNIQUE('Sheet1'!E:E)"
The new "improved" excel is inserting an @ in front of my formula so...
In cell A1 I see =@ UNIQUE('Sheet1'!E:E)
(due to this message boards use of the @ symbol I had to add a space to show you what it looked like. The @ is between the = and the word UNIQUE with no spaces)
This @ forces unique to display only 1 cell (not the list I am expecting)
Does anyone know how to stop Excel from adding this "Implicit intersection operator" into my formula using VBA?
This is what excel is telling me about the @ character it added
"The implicit intersection operator was introduced as part of substantial upgrade to Excel's formula language to support https://support.office.com/en-us/f1/topic/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531?NS=EXCEL&Version=90. Dynamic arrays bring significant new calculation ability and functionality to Excel."
I figured it out. If I use Formula2R1C1 then Excel does not put the @ in.
I am so use to using Formula I didn't want to start using Formula2R1C1, but now I will.
So now the formula is ws.Range("A1").Formula2R1C1 = "=UNIQUE('Sheet1'!C5:C5)"
and it works.
No need to reply. This has been solved. Thanks.
4 Replies
I figured it out. If I use Formula2R1C1 then Excel does not put the @ in.
I am so use to using Formula I didn't want to start using Formula2R1C1, but now I will.
So now the formula is ws.Range("A1").Formula2R1C1 = "=UNIQUE('Sheet1'!C5:C5)"
and it works.
No need to reply. This has been solved. Thanks.
- One last thing. Formula2R1C1 ends up making "=Unique('Sheet1'!$E:$E)" which was okay for my current issue. However, as I wanted it to work exactly like Formula used to I have discovered I can use Formula2 and it gives me exactly what formula used to give me. "=Unique('Sheet1'!E:E)" without the @. So, for me, it is Formula2 from now on.
- I think the key sentence from the VBA object model help is "In Dynamic Arrays enabled Excel, Range.Formula2 supersedes Range.Formula". Personally, I would get rid of the practice of direct cell referencing from VBA at the same time, but that is just my approach to increasing robustness.
