SOLVED

Implicit intersection operator: @

%3CLINGO-SUB%20id%3D%22lingo-sub-2329359%22%20slang%3D%22en-US%22%3EImplicit%20intersection%20operator%3A%20%40%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2329359%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20writing%20a%20formula%20in%20a%20cell%20in%20vba.%3C%2FP%3E%3CP%3Ethe%20formula%20is%26nbsp%3Bws.Range(%22A1%22).Formula%20%3D%20%22%3DUNIQUE('Sheet1'!E%3AE)%22%3C%2FP%3E%3CP%3EThe%20new%20%22improved%22%20excel%20is%20inserting%20an%26nbsp%3B%40%20in%20front%20of%20my%20formula%20so...%3C%2FP%3E%3CP%3EIn%20cell%20A1%20I%20see%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3D%40%20UNIQUE('Sheet1'!E%3AE)%3C%2FP%3E%3CP%3E(due%20to%20this%20message%20boards%20use%20of%20the%26nbsp%3B%40%20symbol%20I%20had%20to%20add%20a%20space%20to%20show%20you%20what%20it%20looked%20like.%26nbsp%3B%20The%26nbsp%3B%40%20is%20between%20the%20%3D%20and%20the%20word%20UNIQUE%20with%20no%20spaces)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%26nbsp%3B%40%20forces%20unique%20to%20display%20only%201%20cell%20(not%20the%20list%20I%20am%20expecting)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20know%20how%20to%20stop%20Excel%20from%20adding%20this%20%22Implicit%20intersection%20operator%22%20into%20my%20formula%20using%20VBA%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20excel%20is%20telling%20me%20about%20the%26nbsp%3B%40%20character%20it%20added%3C%2FP%3E%3CP%3E%3CSPAN%3E%22The%20implicit%20intersection%20operator%20was%20introduced%20as%20part%20of%20substantial%20upgrade%20to%20Excel's%20formula%20language%20to%20support%20%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Ff1%2Ftopic%2Fdynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531%3FNS%3DEXCEL%26amp%3BVersion%3D90%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Edynamic%20arrays%3C%2FA%3E%3CSPAN%3E.%20Dynamic%20arrays%20bring%20significant%20new%20calculation%20ability%20and%20functionality%26nbsp%3Bto%20Excel.%22%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2329359%22%20slang%3D%22en-US%22%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%3CLINGO-SUB%20id%3D%22lingo-sub-2329591%22%20slang%3D%22en-US%22%3ERe%3A%20Implicit%20intersection%20operator%3A%20%40%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2329591%22%20slang%3D%22en-US%22%3EOne%20last%20thing.%20Formula2R1C1%20ends%20up%20making%20%22%3DUnique('Sheet1'!%24E%3A%24E)%22%20which%20was%20okay%20for%20my%20current%20issue.%20However%2C%20as%20I%20wanted%20it%20to%20work%20exactly%20like%20Formula%20used%20to%20I%20have%20discovered%20I%20can%20use%20Formula2%20and%20it%20gives%20me%20exactly%20what%20formula%20used%20to%20give%20me.%20%22%3DUnique('Sheet1'!E%3AE)%22%20without%20the%20%40.%20So%2C%20for%20me%2C%20it%20is%20Formula2%20from%20now%20on.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2329483%22%20slang%3D%22en-US%22%3ERe%3A%20Implicit%20intersection%20operator%3A%20%40%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2329483%22%20slang%3D%22en-US%22%3E%3CP%3EI%20figured%20it%20out.%26nbsp%3B%20If%20I%20use%26nbsp%3BFormula2R1C1%20then%20Excel%20does%20not%20put%20the%26nbsp%3B%40%20in.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20so%20use%20to%20using%20Formula%20I%20didn't%20want%20to%20start%20using%26nbsp%3BFormula2R1C1%2C%20but%20now%20I%20will.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20now%20the%26nbsp%3B%3CSPAN%3Eformula%20is%26nbsp%3Bws.Range(%22A1%22).Formula2R1C1%20%3D%20%22%3DUNIQUE('Sheet1'!C5%3AC5)%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Eand%20it%20works.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ENo%20need%20to%20reply.%26nbsp%3B%20This%20has%20been%20solved.%20Thanks.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2331657%22%20slang%3D%22en-US%22%3ERe%3A%20Implicit%20intersection%20operator%3A%20%40%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2331657%22%20slang%3D%22en-US%22%3EI%20think%20the%20key%20sentence%20from%20the%20VBA%20object%20model%20help%20is%20%22In%20Dynamic%20Arrays%20enabled%20Excel%2C%20Range.Formula2%20supersedes%20Range.Formula%22.%20Personally%2C%20I%20would%20get%20rid%20of%20the%20practice%20of%20direct%20cell%20referencing%20from%20VBA%20at%20the%20same%20time%2C%20but%20that%20is%20just%20my%20approach%20to%20increasing%20robustness.%3C%2FLINGO-BODY%3E
New Contributor

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 dynamic arrays. Dynamic arrays bring significant new calculation ability and functionality to Excel."

3 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

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.