Set Formula

[Hagai David]

Hello,

On my Project I created a Ribbon, in my Ribbon I implemented a button this button pop a Form that asks variables from the user, in addition in the same Form there is a " Sumbit " button while clickling on it pour/push  the variables into the WorkSheet. 

I don't understand two things: 

1) How via the button I pour into the WorkSheet not only the "variables", I want send a Formula I created via UDF and into the Formula set the "variables" the user gave me using the button. 

If to present my issue with small example: Govert made function named "timernow", so in my idea the user asked to enter Interval/Elapsed time for the timer in the Ribbon. 

Then the used press " Sumbit " and in the WorkSheet get written: 

=timernow("UserInput")

and its instead of writing the Formula by yourself

How do I send such a thing from the code to a certain cell in the WorkSheet and it will run me the UDF fucntion i created names "timernow"?

2)One more thing till now I'v used "ws.Range["B1"].Value" in order pour/push things into the Excel from the code. This helping in condition I determine the CELL, what if I want send it into the cell the user "HOVER\stands " on ? 

Thank in advance =), 

Hagai =)

[Kedar Kulkarni]

Hi Hagai,

I would suggest using the below code to set the formula to a cell. You must construct the formula as a string (can use StringBuilder if you have to join multiple parts to construct one formula but it must be a valid excel formula.) 

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

If you want to set the formula to a cell that is not the active cell, you could create a range picker that allows the user to select a range from excel. Check some example images at https://poweranalyticsforexcel.com/ which is open source excel dna addin  (Source at https://sourceforge.net/p/power-analytics-for-excel/code/HEAD/tree/) and it has a range picker control. I am afraid, you may not easily use the cell that user hovers over as you will have to make your form non modal or a task panel and it might add more complexity. Maybe range picker is a tried and tested solution. 

Please use the above links at your own risk, I am not affiliated with the site/developer.

thanks

Kedar

[Hagai David]

Hey Kedar, 

I'm adding a picture of ExcelDnaUtil.Application, "Activecell.formula2" not an option to me. 

BTW, you have an example of it? 

Thanks, 

Hagai

ב-יום שני, 25 ביולי 2022 בשעה 21:59:20 UTC+3, kedarku...@gmail.com כתב/ה:

[Kedar Kulkarni]

Hi Hagai,

you could try the following. It is recommended to use Formula2 if user is on office 365 - see https://groups.google.com/g/ExcelDna/c/eqFp2rC5bio. 

The range is defined as dynamic. https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/types/using-type-dynamic 

using Excel = Microsoft.Office.Interop.Excel;

using System.Windows.Forms;

public void FunctionsClick(IRibbonControl control)
    {

      var app = (Excel.Application)ExcelDna.Integration.ExcelDnaUtil.Application;
      dynamic range = app.ActiveCell;
      if (range == null)
        {
            // user has no workbook open or chart / shape selected.
            // ask user to select a valid cell.
            MessageBox.Show("Please select a valid range");
            return;
        }
      range.Formula2 = "=sum(2,2)";

}

The above works for me.

thanks

[Hagai David]

Hey Kedar, 

First of all thank you !!! 

I'm using it, it basically works. 

I mean it does return me a simple value as "Hello World", but when I up to do "=sum(2,2)" or any other Function  it fails I added a picture of the Error, if you got a quick idea I'll be greatful otherwise  I will keep looking for answer =)

P.S

I don't have Formula2 option, just Formula if it connected somehow(might see in the picture)...

Thanks again, 

Hagai =) 

ב-יום שני, 25 ביולי 2022 בשעה 23:35:32 UTC+3, kedarku...@gmail.com כתב/ה:

[Kedar Kulkarni]

if you have office 365 then it would work with .formula2, however if not are good with .formula as well. You might see a different behavior when you run the same code on office 365.

It seems you are using a non english version of office. Can you try doing the following.

1. Set the formula for a cell with keyboard.

2. Get the formula using code. You should see the expected value.

MessageBox.Show(activecell.formula);

In my opinion, you should change your global locale to en-Us and set the formula for addin. Can you let me know your regional settings?

Also check the activecell.Formulalocal property. You can set that as well..

thanks,

Kedar

[Govert van Drimmelen]

Note that there is a big difference between setting a property, like this

    rng.Formula = "=1+1”;

and calling a method, which would look like this

    rng.Formula("=1+1")

 

The second one will never work in this setting.

 

-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/0f04098b-0ccc-4ba0-8b53-f3a1a99a93f6n%40googlegroups.com.

[Hagai David]

Hello Kedar and Govert, 

Kedar, I checked the lanaguge package is fine, I also remember that when I installed the program it was in English and no other lanaguage additionally, So I though maybe it pulling my Operating System Lanagueg  but its English as well, maybe it my other langauge keybaord anyway it just drop me a ERROR in this line that i can't return such a thing "=sum(2,2)", it connected to what Govert is saying "The second one will never work in this setting." and that what I'm trying figure out how doing it. 

I want return a function/ method to the cell . 

can you explain how calling a method also if you got a small example it be helpful ? 

if to recap my the chain of events:

User Enter Value to Riboon --> User Click Submit --> using Microsoft.Office.Interop.Excel to call Formula with the parametrs the user sumbit (this is the part I stuck) -->the Formula send and value back to the excel accroding to the given parametrs 

 ***The formula running in the same project under RTD server***

Thanks you Kedar and Govert, 

Much appreciated,

Hagai

ב-יום רביעי, 27 ביולי 2022 בשעה 17:38:54 UTC+3, Govert van Drimmelen כתב/ה:

[Govert van Drimmelen]

Maybe try this:

 

• Suppose your function is called “MyFunction” and takes a single value.
• Ensure that your function already works fine if you enter it directly into the sheet, e.g. by typing “=MyFunction(123)” into a cell.
• Then give the user an easy formula entry tool that works like this:
• User enters value in ribbon and press submit
• Use the COM object model to set the formula in the “ActiveCell” (Note that you are not trying to calculate or ‘call’ the function here).
• Then when you’re done, Excel will automatically calculate the cell with the new formula, which will return and display the correct value.

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/57c0faaf-7c17-4181-94d6-b9df24fec8b0n%40googlegroups.com.

[Hagai David]

-I have checked my functions direct from the worksheet several times it's works fine =)

-I success gather the enters values from the ribbon. 

-What do you mean "Use the COM object model to set the formula in the “ActiveCell” "- Its excatly the place I stuck on. 

lets say I got 1 paramter and to my function called "MyFunction" - its open a timer and update the value each milisecs that given by the user, the function return the time oclock 

 I am using this code rows: 

using ExcelDna.Integration;

using Microsoft.Office.Interop.Excel;

            Application xlApp = (Application)ExcelDnaUtil.Application;

            Application excelApp = (Application)ExcelDnaUtil.Application;

            Range range = excelApp.ActiveCell;

            /// here I need set the formula like you told///

           ///I used " range.Formula("= MyFunction (2000)"); "

          /// In your idea how I suppose set the formula that it call MyFunction, in the same way I would been call it directly thorugh the worksheet Manualy ?

ב-יום רביעי, 27 ביולי 2022 בשעה 20:36:56 UTC+3, Govert van Drimmelen כתב/ה:

[Kedar Kulkarni]

Can you try the following when a new excel workbook is open?

using ExcelDna.Integration;

using Microsoft.Office.Interop.Excel;

            Application excelApp = (Application)ExcelDnaUtil.Application;

            Range range = excelApp.ActiveCell;

            range.Formula = "=MyFunction(2,2)";

or 

            range.Formula = "=2+2";

Please share the screenshot of the error if you get an error on the highlighted line above.

[Hagai David]

Its works !!! 

I don't get it why suddenly it works, I proabbly missing something, I did the same thing and it failed like the screenshot I supplied above here -_-

ב-יום רביעי, 27 ביולי 2022 בשעה 21:56:51 UTC+3, kedarku...@gmail.com כתב/ה: