Using Offcie 365 Dynamic Arrays, Excel automatically adds @

[guido debouver]

just converted all of my UDF's to use the new dynamic array feature in Office 365 - works great :-)

Just a small problem, Excel always adds the @ sign automatically when adding the formula, reuslting in one cell to be returned.

User had to go and remove teh @ to get the full array

Is tehre a setting in ExcelDNA to avoid this - or am i doing something wrong ( again )

kind regards

guido

[Govert van Drimmelen]

Hi Guido,

 

You can read about the implicit intersection @ operator here: https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34?ui=en-us&rs=en-us&ad=us

 

The @ gets added to your formula in two cases:

• When opening a sheet that was saved in a pre-Dynamic Arrays version of Excel. This is for compatibility as discussed in the article linked above.
• When setting the formula of a cell through the COM interface property Range.Formula. For this case you should instead use the new Range.Formula2 property to set the formula. See https://docs.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2

 

I have put a few other links here: https://github.com/Excel-DNA/ExcelDna/wiki/Dynamic-Arrays

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/0b5fa217-b6d2-4af8-8b42-3cc798024e35%40googlegroups.com.

[guido debouver]

Got it.

In fact, and for the benefit of others with the same problem, I incldued a formula when clicking a button.

The code then called 

ExcelDnaUtil.Application.Activecell.formula = "=whateverfunction()"

ExcelDnaUtil.application.Dialogs(450).Show()

I changed it to ExcelDnaUtil.Application.Activecell.formula2 = "=whateverfunction()" and all works fine

thanks

[Kevin Fox]

Guido

Did you solve this in C# or VBA? I am having the same issue.

[Kevin Fox]

If anyone else stumbles on this issue for me the fix was changing the way I was inserting the formula from 

activeCell.Formula = $"=SOME_FUNCTION()";

to 

activeCell.FormulaArray = $"=SOME_FUNCTION()";

[Govert van Drimmelen]

Hi Kevin,

 

Are you

1. creating formulas in pre-Dynamic Array Excel so that when the sheet is opened in Dynamic Array Excel they will not show with the @-sign, or
2. are you running in Dynamic Arrays Excel and you want to insert formulas without the @-sign?

 

In the second case, you would use activeCell.Formula2 = "=SOME_FUNCTION()"

 

-Govert

 

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/6fb5cec6-676a-47b7-ad17-25c5eb7fe49cn%40googlegroups.com.

[guido.d...@pa-ats.com]

hello all,

I had following vb .NET code to add a formula in a selected cell, which worked fine

ExcelDnaUtil.Application.ActiveCell.formula2 = "=" & formule.ToUpper & "()"

now, I need to recompile as per client instructions with Option Strict = On, and I am having problems.

Dim a1 As Excel.Application = CType(ExcelDnaUtil.Application, Excel.Application)
Dim c1 As Excel.Range = a1.ActiveCell

c1.Value2 = "=" & formula & "()"
            

Now the above code adds again the @ operator into the cell ????

AM I doing something stupid ?

[guido.d...@pa-ats.com]

PLEASE DISREGARD PREVIOUS POSTING - THERE WAS A STUPIDITY IN IT

hello all,

I had following vb .NET code to add a formula in a selected cell, which worked fine

ExcelDnaUtil.Application.ActiveCell.formula2 = "=" & formule.ToUpper & "()"

now, I need to recompile as per client instructions with Option Strict = On, and I am having problems.

Dim a1 As Excel.Application = CType(ExcelDnaUtil.Application, Excel.Application)
Dim c1 As Excel.Range = a1.ActiveCell

c1.formula2 = "=" & formula & "()"
            

The above doesnt work - it doesnt seem formula2 is a member of Microsoft.Office.Interop.Excel.Range

Any ideas ?

thanks

[guido.d...@pa-ats.com]

I have to load rev 16 of the  Microsoft.Office.Interop library, this seems to include the Formula2 field on the Exce.Range.

However, this now invalidates the loading of Office.dll ( Microsoft.Office.core ), which I dont find in rev 16. 

If I reference rev 15 of Office.dll, it conflicts with rev 16 of Microsoft.Office.Interop

Anyone any idea - where can I find rev 16 of Microsoft.Office.core. It sure is not on my PC ( with Office 365 installed )

thanks

guido