What am I missing? ExcelFormula spilling from Code not working

[S K]

Hello,

I actually have a simple but tricky question, which I'm currently struggling heavily with. I have a decent ExcelDNA application so far which does thermal engineering calculations. However, I have created a UDF which gives back an array and though in Excel a spilled table. It works well when used manually.

Now I need the very same formula to be created automatically and I just use 

CType(ws.Cells(row, col), Excel.Range).Formula = formelString

The problem is that the formula is set in a cell automatically with the @-operator, which disables the spilling completely. I searched for almost two days, tried many different approaches, but nothing works. 

I cannot believe that this is not possible with ExcelDNA.
I work with ExcelDNA 1.7 and would prefer to stay at this version.

Best regards,
Stefan

[Govert van Drimmelen]

Hi Stefan,

You need to use the Range.Formula2 property to set the dynamic array formula.

That property might not exist on the Range type you have, so then use a late-binding mechanism to set it.

E.g. cast to dynamic in C#, or to Object in VB.NET.

-Govert

[S K]

Hi Govert,

Thanks for the quick reply.

Actually, this is also what I tried. But it is not working. 

Dim rng As Object = ws.Cells(currentRow, startCol)  
Microsoft.VisualBasic.CallByName(rng, "Formula2", CallType.Let,  formelString)

This reverts with the following error: 'Public member 'Formula2' on type 'Range' not found.' 

I don't know why the COM property is not available.

BR, Stefan

[Govert van Drimmelen]

Surely just 

ring.Formula2 = ....

Will work fine.

-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 visit https://groups.google.com/d/msgid/exceldna/331b90b7-fee4-4cd5-bd26-6f3e65c2400cn%40googlegroups.com.

[S K]

Hi Govert,

Same here. " Public member 'Formula2' on type 'Range' not found."   for 

rng.Formula2 = formel

Could there be another reason that the late-binding Range Objects properties are not available? 

The complete definition tree for rng is

Dim xl As  Microsoft.Office.Interop.Excel.Application   = CType(ExcelDna.Integration.ExcelDnaUtil.Application,  Microsoft.Office.Interop.Excel.Application)

Dim ws As Microsoft.Office.Interop.Excel.Worksheet = CType(xl.ActiveSheet,  Microsoft.Office.Interop.Excel.Worksheet  )

Dim rng As Object = ws.Cells(currentRow, startCol) 

As you see, I had to cast the Worksheet and Application objects to get things running. 

But I doubt that this causes the problem. 

I really appreciate the support since this problem seems really low-level. but still it is very frustrating.

BR, Stefan

[Govert van Drimmelen]

Hi Stefan,

 

It works fine for me, even keeping it as a Range variable.

Here is a test project: govert/TestFormula2

 

This is the macro:

 

    <ExcelCommand(MenuName:="Test Formula2", MenuText:="Write Formula", ShortCut:="^F")> ' Ctrl+Shift+F

    Public Sub WriteFormula()

        Dim xlApp As Application = ExcelDnaUtil.Application

        Dim rangeA1 As Range = xlApp.Range("A1")

 

        rangeA1.Formula2 = "=RANDARRAY(3,4)"

    End Sub

 

What do you see when you try this project?

 

-Govert

To view this discussion visit https://groups.google.com/d/msgid/exceldna/7d009c63-9994-4c8f-a58d-5d0fc2953665n%40googlegroups.com.

[S K]

Hi Govert,

Thanks a lot. I had to overcome some issues to get it running, but your example was very helpful.

If someone is dealing with the same issue, please take care that:
- Both frameworks "net472" & "net9.0-windows" are needed as in Govert's example. I do not know the reason,  but I tried without Net4.7.2, and it was not working (for me)

- net9.0 requires a more or less up-to-date studio version (I had to install a big update to version 17.14 of Visual Studio) 
- Also it looks like the ExcelDNA version needs to be higher than 1.7. Since Govert's example was for the newest RC of 1.9, I chose that and it worked.

Thanks again for the support!

Stefan

[Govert van Drimmelen]

Hi Stefan,

This project file works for me too:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <RootNamespace>TestFormula2</RootNamespace>
    <TargetFramework>net8.0-windows</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="ExcelDna.AddIn" Version="1.8.0" />
    <PackageReference Include="ExcelDna.Interop" Version="15.0.1" />
  </ItemGroup>

</Project>

* I think you need at least Excel-DNA 1.8 for the SDK-style build and the embedded COM references to all work together.

* I don't know why you need at least .NET 8 (when targeting .NET core) but they did make COM improvements over the years.

  Compiling for .NET Framework (net472) should not be needed (but of course works if that's what you prefer to target).

-Govert