IsFunctionWizard

[alexs...@gmail.com]

Hello,

I use FunctionWizard for some of my UDF functions. The form is opened, i choose parameters. If i press "OK" button in FunctionWizard, everythings goes well - function calculates. But if i press cancel -my UDF formula in cell is not deleted and recieves nullable arguments and then displays error. How can i detect if user choosed "Cancel"?

I've seen addins with UDF functions, when user presses "Cancel" in Function Wizard the cell is cleared.

Here is my code:

  dynamic Application = ExcelDnaUtil.Application;

            dynamic startCell = Application.ActiveCell;

            startCell.FormulaR1C1 = "=PrimeReportUBalance()";

            startCell.Select();

            startCell.FunctionWizard();

..................

  public static string PrimeReportUBalance([ExcelArgument(AllowReference = true, Name = ", Description = "")]

                                                System.DateTime date1, 

            [ExcelArgument(AllowReference = true, Name = "", Description = "")]

                                                System.DateTime date2,

              [ExcelArgument(AllowReference = true, Name = "", Description = "")] object cell = null,

           [ExcelArgument(Name = "", Description = ")] bool head = true

.................

            if (ExcelDnaUtil.IsInFunctionWizard()==true)

            {

                return "";

            }

[alexs...@gmail.com]

Still no suggestions how to do it?

понедельник, 21 мая 2018 г., 19:18:32 UTC+3 пользователь alexs...@gmail.com написал:

[alexs...@gmail.com]

If I write  UDF function in VBA, call FunctionWizard and choose cancel, the cell is cleared. How can i implement the same functional using excel dna?

понедельник, 21 мая 2018 г., 19:18:32 UTC+3 пользователь alexs...@gmail.com написал:

Hello,

[alexs...@gmail.com]

The Function Wizard works normally, when i call it by click in excel (type the name of function and click "fx") - it clears the cells if i click cancel. But if i call it from c# code in xll it doesn't see that user pressed "Cancel" and doesn't clear cell, the calculation of my function just continues and goes to error messages...

понедельник, 21 мая 2018 г., 19:18:32 UTC+3 пользователь alexs...@gmail.com написал:

Hello,

[Govert van Drimmelen]

Can you show the VBA code?

-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 post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[alexs...@gmail.com]

Actually don't have such VBA code. I have an excel dna addin.

It has a ribbon menu. When i select a menu item a sub which refers to this item inserts a name of function in the selected excel cell and the launches UDF. The code i've already posted.

The sub launches from ribbon.

  <button id="Button1" label="PrimeReportUBalance" onAction="ShowUBalance" />

             

       public void ShowUBalance(IRibbonControl ctl)

        {

            dynamic Application = ExcelDnaUtil.Application;

            dynamic startCell = Application.ActiveCell;

            startCell.FormulaR1C1 = "=PrimeReportUBalance";

            startCell.Select();

            startCell.FunctionWizard();

        }

понедельник, 21 мая 2018 г., 19:18:32 UTC+3 пользователь alexs...@gmail.com написал:

Hello,

[alexs...@gmail.com]

I' m sorry , here is the correct code:

   <button id="Button2" label="PrimeReportGAPLiq" onAction="ShowGap" />

            

    public void ShowGap(IRibbonControl ctl)

        {

            dynamic Application = ExcelDnaUtil.Application;

            dynamic startCell = Application.ActiveCell;

            startCell.FormulaR1C1 = "=PrimeReportGAPLiq()";

            startCell.Select();

            startCell.FunctionWizard();

        }

понедельник, 21 мая 2018 г., 19:18:32 UTC+3 пользователь alexs...@gmail.com написал:

Hello,

[Govert van Drimmelen]

Excel treats .xll functions different to built-in and VBA functions.

For example, entering =PrimeReportGAPLiq without parentheses or parameters will return the registration id, while built-in functions return #Name or something.

Not sure there's anything you can do in the add-in if you're not happy with Excel's behaviour.

-Govert 

[alexs...@gmail.com]

OK, but is there any way to detect if the user pressed "Cancel" in the function wizard? Then I clear the cell in the UDF function, return empty string.

понедельник, 21 мая 2018 г., 19:18:32 UTC+3 пользователь alexs...@gmail.com написал:

Hello,

[alexs...@gmail.com]

OK, but is there any way to detect if the user pressed "Cancel" in the function wizard? Then I clear the cell in the UDF function, return empty string.

вторник, 22 мая 2018 г., 20:40:06 UTC+3 пользователь Govert van Drimmelen написал:

[alexs...@gmail.com]

For example this add-in , was developed in excel-dna , as can be seen in config files.

And it also inserts a function in the list from the ribbon menu, amd shows Function Wizard. But if "Cancel" button in FunctionWizard is pressed the cell is cleared from formulla

понедельник, 21 мая 2018 г., 19:18:32 UTC+3 пользователь alexs...@gmail.com написал:

Hello,

[Govert van Drimmelen]

You could try to identify the window handle of the cancel button, install a windows message handler on the function wizard window, and then try to monitor the mouse or keyboard messages.

For me I'd estimate it would take a week or two to figure something like that out.

It's not easy.

-Govert

[Govert van Drimmelen]

Or maybe you can just check what Range.FunctionWizard returns.

-Govert

[alexs...@gmail.com]

Thank you very much, Govart, for good advice. In general it's working idea...This code is working...

Now I'm thinking how to get the result of FunctionWizard inside the udf function, because excel working in such way, if i press cancel in Wizard:

1) calculates the function (and it can be error because it should'n be calculated in case of Cancelling wizard)

2) returns "false" to procedure which inserted UDF in cell and called FunctionWizard..

 Global1.funcwiz= startCell.FunctionWizard();

            if (Global1.funcwiz != null)

            {

                if ((bool)Global1.funcwiz == false)

                {

                    Global1.funcwiz = null;

                    startCell.Clear();

                    startCell.Value2 = "FW cancel";

                }

            }

понедельник, 21 мая 2018 г., 19:18:32 UTC+3 пользователь alexs...@gmail.com написал:

Hello,