Macro not Visible

[typedef]

I am trying to add macro to my add-in. I took one from the example but it still not visible from the list of macros

        [ExcelFunction(IsMacroType = true)]
        public static object GetTheXllName()
        {
            return XlCall.Excel(XlCall.xlfGetWorkspace, 44);
        }

What I am missing?

[Govert van Drimmelen]

--------------------------------------------------

Excel-DNA is now registered on https://github.com/sponsors/Excel-DNA.

Your monthly contribution encourages further development and support.

--------------------------------------------------

 

To register a macro you would create a method that returns ‘void’ and is possibly annotated with an [ExcelCommand] attribute.

 

        [ExcelCommand(ShortCut = "^h")]  // Ctrl + h

        public static void SayHello()

        {

            XlCall.Excel(XlCall.xlcAlert, "Hello World!");

        }

 

That registers the macro with shortcut Ctrl + h and you can also bind it to a button on the workbook etc.

 

 

Even then, the Excel ‘Macro’ dialog (Alt+F8) does not list macros from add-ins – they’re considered private.

But you can type in the name, and then the ‘Run’ button will light up and you can press it:

 

 

 

-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/3ad35215-6b05-4935-85ca-fd0391811a00n%40googlegroups.com.

[typedef]

Hello Govert.

Thanks a lot for quick and helpful response. It worked perfectly.

Do you mind if I ask few more related questions.

1. Although it not critical is there a way to be able to see those macros I added ? Or mark then public somehow?  

2. Can I add programmatically button from common control, and assign macro that I am adding? So user will see new button and will be able to click on it to execute new added macro?

3.  C# method for macro must be void.  I tried to set and execute formula in cell on macro
XlCall.Excel(XlCall.xlcFormula, "=SUBMITREQUEST(Z545:AD545)", new ExcelReference(550, 31));

Where submit request is ExcelDNA method
        [ExcelFunction(Name = "SUBMITREQUEST", Description = "Sends customer support request")]
        public static IObservable<object>  SUBMITREQUEST (
            [ExcelArgument(Description = "request parameters: Type, Urgency, Date, From, Description, Comments,  ")] object[] requestData)

I am getting exception during call  XlCall.Excel(XlCall.xlcFormula, "=SUBMITREQUEST(Z545:AD545)", new ExcelReference(550, 31));
Not sure if it related that SUBMITREQUEST returns   IObservable<object> 

If i manually set in cell  =SUBMITREQUEST(Z545:AD545) it works fine.

Thanks,

Alex.

[typedef]

Regarding 3
Even though I decorated my  =SUBMITREQUEST(Z545:AD545) with
System.Threading.Tasks.Task.Factory.StartNew(() =>
            {
                System.Threading.Thread.Sleep(2000);
                var sc = new ExcelSynchronizationContext();
                sc.Post(delegate
                {

                    XlCall.Excel(XlCall.xlcFormula, "  =SUBMITREQUEST(Z545:AD545) ", new ExcelReference(550, 31));
                    

                }, null);
            });

It still throwing exception although 
XlCall.Excel(XlCall.xlcFormula, "=TODAY()", new ExcelReference(550, 31)); 

works fine

[typedef]

I figured out number 2.  Number 3 is still an issue

On Monday, March 7, 2022 at 7:15:37 PM UTC-5 typedef wrote:

[Govert van Drimmelen]

--------------------------------------------------
Excel-DNA is now registered on GitHub Sponsors.

Your monthly contribution encourages further development and support.
--------------------------------------------------

1. Macros defined in .xll add-ins are always considered private by Excel. This means they don't appear in the Alt+F8 Run Macro dialog. But you can type in the name there, and the 'Rub' button will light up and you can run the macro.

2. You should be able to assign a macro to a worksheet button or similar control. Again you have to type the name - your .xll add-in macros won't appear on the list.

3. When Excel is evaluating your function during calculation, you generally can't modify the worksheet. So in this context you can just write a formula or values to the sheet. If you really have to trigger changes based on the function evaluation (for example you might want to change the formatting of the calling cell) then Excel-DNA has a helper method that lets you run code in a macro context, after the calculation is complete. For this you call `ExcelAsyncUtil.QueueAsMacro(...)` with a delegate that has your code. Excel-DNA then waits until Excel is ready (after the calculation is complete), and will run this code as a macro. The code can then change the sheet etc. Remember that the context of the function call might not be the context of Excel after the calculation completes, e.g. the active sheet might be a different one to the caller of the function. You can capture local variables in the delegate, so you can pass through the parameters of something like the caller information (which you might have grabbed with `XlCall.Excel(XlCall.xlfCaller)`).

When you're trying to update formulas in from a macro (either called directly from a button or shortcut, or via `QueueAsMacro`) it's probably better to use the COM object model instead of the C API calls (anything `XlCall.Excel(...)`). The COM object model is much more comprehensive, with recent Excel features added along the way, and you'll find help more easily (it works like VBA). The only trick you need for this is to get hold of the root Application object in you add-in code. For this you call `ExcelDnaUtil.Application` - this returns a COM object of type Microsoft.Office.Interop.Excel.Application. You can easily reference the COM interop assembly from your add-in by installing the ExcelDna.Interop package from NuGet. Then you can cast ExcelDnaUtil.Application to the right type and get early-binding and help etc.

If you really want to use the C API to set a formula, that can work too. But I think for references in the formula you need to use the R1C1 convention. But even this seems to have some problems - see this discussion: XlCall string formula with cell reference (google.com)

-Govert

[typedef]

Hello Govert

Thanks for 1 and 2.   With 2 I was able to implement exactly as I want.

Regarding 3, I think you few steps ahead of me.
Let say without using macro, when I just put formula in the cell manually it calls method
=SUBMITREQUEST(Z545:AD545)
that returns   IObservable<object> 

So when request is let say accepted, I see in cell value "Request Accepted"
If customer let say cancels request cell is getting updated same way via Rx observable and sets value to "Customer cancelled request". 
All works just fine.

When I calling macro method I decided to call ExcelDNA method directly BUT store returned object ( IObservable<object> ) in the cell.

So in C# in macro method body I  am calling C# method    IObservable<object>  retVal   =  SUBMITREQUEST(...) ; 
then I call
XlCall.Excel(XlCall.xlfCreateObject, retVal, new ExcelReference(550, 31));

//Not sure  XlCall.xlfCreateObject is appropriate type.

I don't know is this is best approach vs set formula in the cell using xlfFormula, anyway I am not sure how to set  IObservable<object>  to the cell ? The same way if I would do it manually in excel.

Thanks,

Alex.

[Govert van Drimmelen]

HI Alex,

You can't directly register a streaming function that returns IObservable<T> with Excel.

But Excel-DNA has some helper to enable that - you can look at these examples:

Samples/RtdClocks at master · Excel-DNA/Samples (github.com)

The last two (with the "Rx" in the name) are based on IObservable. The function that you register will look like this:

    public static class RtdClock
    {
        [ExcelFunction(Description = "Provides a ticking clock")]
        public static object dnaRtdClock_Rx()
        {
            string functionName = "dnaRtdClock_Rx";
            object paramInfo = null; // could be one parameter passed in directly, or an object array of all the parameters: new object[] {param1, param2}
            return ObservableRtdUtil.Observe(functionName, paramInfo, () => GetObservableClock() );
        }

        static IObservable<string> GetObservableClock()
        {
            return Observable.Timer(dueTime: TimeSpan.Zero, period: TimeSpan.FromSeconds(1))
                             .Select(_ => DateTime.Now.ToString("HH:mm:ss"));
        }
    }

You might be confused by what a function should be doing (returning a value) and what a macro might do (set a formula in a cell).

xlfCreateObject can be called inside a macro to add lines, buttons etc to a worksheet or chart.

-Govert

[typedef]

Hello Govert.

Thanks a lot for all responses and help.

I think this is perfect example.   All I wanted in macro body set formula or rerun value from  dnaRtdClock_Rx() in to the cell

[ExcelCommand(ShortCut = "^h", Name = "My")]
public static void MyMacroMethodBody()
{

 //Either set in cell formula

//XlCall.Excel(XlCall.xlcFormula, "= dnaRtdClock_Rx ()", new ExcelReference(550, 31));

// OR set return value in cell

// var returnValue = dnaRtdClock_Rx();    XlCall.Excel(xlcObject, returnValue,  new ExcelReference(550, 31));

}

// xlcObject -no exist, just as example

I guess it is impossible.