Add Hyperlink or Color to cell in which function is called

[bhaumik chauhan]

Hi all,

            I am trying to add hyperlink and interior color to excel cell from which function call is made using COM object. I am able to add comments to the cell. But adding hyperlink of interior color does not happen. Below is my code for adding comment which works perfectly.

                Excel.Application app = (Excel.Application)ExcelDna.Integration.ExcelDnaUtil.Application;

                Excel.Range range= app.ActiveCell as Excel.Range;

                range.AddComment("comment");

But when i do 

                range.Interior.ColorIndex = 15;

it gives exception. The same happens for hyperlink. Can anyone please tell me what the issue is.

Thanks,

Bhaumik

[Govert van Drimmelen]

Hi Bhaumik,

Excel does not allow changes to be made to the sheet from inside a user-defined function.

(The AddComment call is unusual, in that it seems to work when called from functions.)

So what you want to do is quite hard.

You need to set up the work to run in a macro context after the calculation is complete, where you can then do the formatting updates.

Excel-DNA has a helper to achieve this - ExcelAsyncUtil.QueueAsMacro(...).

But then there are further complications. You need to remember where the function was called from, so that you can use it later in the macro.

(The ActiveCells that you shown in the example is not always the one you want, it refers to the cell currently selected in the sheet and this might be different from the "Current" cell, which is the one busy calculating your function.)

I prefer to use the C API (and the ExcelReference) in the function part itself, so I convert to a Range only when the macro is running.

So you also need to convert the ExcelReference object you get as the 'caller' inside the function to a COM Range object to make the update easy.

The code might look like this:

        public static double SetGrayIfLarge(double input)

        {

            int colorIndex = input > 100 ? 15 : 0;

            

            ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

            if (caller != null)

            {

                // We were called from a cell (and not VBA or something etc.)

                ExcelAsyncUtil.QueueAsMacro(() =>

                    {

                        // This is running in a macro context, after the calculation has completed.

                        // The caller and colorIndex variables have been captured and passed along

                        // as part of the delegate that was enqueued.

                        Application Application = (Application)ExcelDnaUtil.Application;

                        string callerAddress = (string)XlCall.Excel(XlCall.xlfReftext, caller, true);

                        Range callerRange = Application.Range[callerAddress];

                        callerRange.Interior.ColorIndex = colorIndex;

                    });

            }

            return input;

        }

-Govert

[bhaumik chauhan]

Hi Govert,

                 For add comment i had tried using ExcelAsyncUtil.QueueAsMacro but it did not work so i tried it the way it is working now. Also i am not much familiar with C API. Anyways i tried the way u said. But as before, did not work, gives error "SynchronizationManager is not registered"

[Govert van Drimmelen]

Hi,

You need to use the latest version of Excel-DNA for the code to run.

Previous versions required a call to ExcelAscynUtil.Initialize() in an AutoOpen() implementation, but that is no longer required.

The easiest way to make a project with the current Excel-DNA version is to make a new "Class Library" project, and then install the "Excel-DNA" NuGet package.

-Govert

[bhaumik chauhan]

Hi Govert,

                 I downloaded the latest version and did range.Interior.ColorIndex in ExcelAsyncUtil.QueueAsMacro and it is working. But i am still confused why AddComments, Range.Font.Bold and couple of other methods work without ExcelAsyncUtil.QueueAsMacro but rest do not. Anyways thanks my problem is resolved and Excel-DNA has helped a lot.

- Bhaumik