Excel Files downloaded from the internet causing "Reference isn't valid" error on version 9.1.4620

Add-in Express™ Support Service
That's what is more important than anything else

Excel Files downloaded from the internet causing "Reference isn't valid" error on version 9.1.4620
 
Posts 1 - 10 of 11
First | Prev. | 1 2 | Next | Last 
Tim Overton


Guest


After upgrading our Excel add-in project to version 9 (9.1.4620); we've found that documents freshly opened from the internet are getting a "reference isn't valid" error after clicking on a Hyperlink in Excel.

Repro steps:

  • Download Excel sheet from the internet
  • Open the file
  • Click "Enable" on the protected view warning
  • Click hyperlink
  • Get "Reference isn't valid" error
  • Our code in the ExcelWorksheetEvents gets hit


Based on the fact that the error gets raised before hitting the "ProcessFollowHyperlink" method in our code and rolling back to the last version 8 fixes the issue. Then the last thing I narrowed it down to is Add-in Express version 9.
Posted 23 Sep, 2018 23:17:52 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Hello Tim,

I can't reproduce this issue. To reproduce it I've created a simple COM add-in printing a debug message when the SheetFollowHyperlink event occurs: 

private void adxExcelAppEvents1_SheetFollowHyperlink(object sender, object sheet, object hyperlink)
{
    System.Diagnostics.Debug.WriteLine("!!! adxExcelAppEvents1_SheetFollowHyperlink. " + (hyperlink as Excel.Hyperlink).Address);
}


Then I download a workbook containing a link to the Add-in Express web site: www.add-in-express.com. For me Excel doesn't show it in a protected view window so I do this myself. I click the link before or after enabling editing: if before, I get a message box titled "Microsoft Excel Security Notice" notifying me that the link can be harmful. If I click the link after enabling editing, my web browser opens the web site; the debug message is printed in this case only.


Andrei Smolin
Add-in Express Team Leader
Posted 24 Sep, 2018 04:09:42 Top
Tim Overton


Guest


Given your info, I was able to dig into it further and have found that the hyperlink is pointing to a named range; who in turns points to =INDIRECT(CurrentCell()). The CurrentCell() function is a UDF in our XLL Module which I have pasted below. 


            public static object CurrentCell()
            {
                try
                {
                    return Module.ExcelApp.ActiveCell.Address;
                }
                catch
                {
                    return ADXExcelError.xlErrorRef;
                }
            }


Debugging, I have found that the ExcelApp is returning null when you open an Excel sourced from the internet (and shows with the protected view). This continues to return null until Excel is reopened.

I can send a sample project where I have reproduced it.

Sorry for the misleading initial set of repro steps, unfortunately it was hiding the actual issue here.
Posted 24 Sep, 2018 18:02:12 Top
Tim Overton


Guest


The below link should take you to a Google drive with a zip of the sample project.

https://drive.google.com/file/d/1mQmSHoH5xTISVva1Vc__Sd3iuZ95bNzg/view?usp=sharing
Posted 24 Sep, 2018 18:27:19 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Hello Tim,

Thank you for the project and extra details.

I've created a sample workbook containing a named range (Address=C3, Name=NamedRange). C3 contains this formula: =INDIRECT(CurrentCell()). A1 contains a link to NamedRange. If I enter something in e.g. D4 and press {F9}, C3 displays the data entered. If I click the hyperlink, this makes C3 active.

Now I save the workbook and open it in a protected view window: File | Open | Browse | {select the file and see the dropdown of the Open button}. The result is: C3 displays the last value; clicking the hyperlink makes C3 active. If I enable editing: C3 displays #REF!, the hyperlink works as expected, pressing {F9} returns everything to the normal state.

I use an Insider build of Excel 2016 32bit: Version 1810 (Build 10910.2007 Click-to-Run).


Andrei Smolin
Add-in Express Team Leader
Posted 25 Sep, 2018 03:33:49 Top
Tim Overton


Guest


Sorry, perhaps I wasn't clear in my last post that this issue can be better replicated without named ranges, nor hyperlinks.

Using the demo project; if you create a blank workbook, open it with Protected View, Enable it and then enter =TestCurrentCell() then you should find that you will get #REF!

However, doing the same thing, but opening it normally (i.e. no protected view) then you will get the current selected cell range.

I have traced this back to the Module.ExcelApp being null and therefore throwing a null reference exception.

https://imgur.com/fLkzTHP
Posted 25 Sep, 2018 08:09:00 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Thank you, Tim!

I've reproduced the issue. It is now filed under #13861 in our issue-tracking DB. We have ideas about what causes ExcelApp to be null and will look for a way to fix this. I'll keep you posted.


Andrei Smolin
Add-in Express Team Leader
Posted 25 Sep, 2018 08:43:31 Top
Tim Overton


Guest


Good news, thanks.

Would you be able to provide an ETA on the fix? (We need to make the decision on whether we perform our next release rolled back to version 8 or if we just wait for the fix)
Posted 25 Sep, 2018 17:28:13 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Hello Tim,

We've investigated the issue.

Background. 1) The Excel SDK doesn't foresee a way for an XLL to obtain a reference to the ExcelApp object. 2) Here's a citation from a now deleted page at http://support.microsoft.com/kb/301443:

[Microsoft says that] a function that is defined in an XLL add-in can be called under three circumstances:

1. During the recalculation of a workbook
2. As the result of Excel's Function Wizard being called on to help with the XLL function
3. As the result of a VBA macro calling the Application.Run Automation method of Excel

Under the first two circumstances, the Excel Object Model does not expect, and is not prepared for, incoming Automation calls. Consequently, unexpected results or crashes may occur. So, you must be prepared for the fact that some calls to the Excel Object model from your UDF may crash or hang Excel.


We assume that deleting this page cannot mean that Excel now solves these issues.

Our findings. The way we use to obtain a reference to Excel.Application stopped working after certain builds of Excel 2013 and 2016 were released. To let you use ExcelApp at least somehow, we started retrieving it asynchronously. In the scenario in question, the issue is: ExcelApp is null when in protected view (this is by Microsoft design); when you enable editing, it is still unavailable by the moment when Excel invokes your UDF. Currently, you are supposed to press {F9} to let all this machinery work. Still, there's an issue in our code that may prevent it from getting an Excel.Application in this situation, too; you may need to press {F9} several times. We are going to fix *this* issue and provide the fix to you in this or that way.

What we will do. As you can see, the above doesn't describe a solution of the issue. On the contrary, we say that it isn't possible to *really* fix this issue. For this reason, the ExcelApp property will be declared obsolete in the next Add-in Express build.

Your ways. To work around this, you use a combination of a COM add-in and XLL add-in. In the COM add-in, you can track the selection and changes and store information for the XLL add-in. The XLL add-in could retrieve this info using the fact that the COM add-in and XLL add-in inhabit the same AppDomain; below is a code line from the add-in described in https://www.add-in-express.com/creating-addins-blog/2010/03/24/addin-xll-rtd-one-assembly/ 

comaddin_xll_rtd_cs.AddinModule addinModule = AddinExpress.MSO.ADXAddinModule.CurrentInstance as comaddin_xll_rtd_cs.AddinModule;


Please remember about this restriction: don't make object model calls in the context of the XLL call. In other words, your UDF cannot invoke ExcelApp.ActiveCell directly or indirectly; it shouldn't access ExcelApp in the first place. You can use the fact that your UDF actually requires the address of the active cell, not the Excel.Range representing that cell: your COM add-in could track selection and store the address of the active cell so that you XLL could invoke a property/method that returns the previously stored address.

If you need your XLL to perform an action that requires accessing the object model, check how we do this in the sample project described at https://www.add-in-express.com/creating-addins-blog/2011/10/11/invoke-excel-addin-xll/.


Andrei Smolin
Add-in Express Team Leader
Posted 26 Sep, 2018 06:26:17 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
One more point. It is possible that Excel invokes your UDF *before* your COM add-in has a chance to find out the selected cell. In this case, you can use ADXXLLModule.CallWorksheetFunction(ADXExcelWorkseetFunction.Caller) as suggested in section Determining the cell, worksheet or workbook your UDF is called from at https://www.add-in-express.com/docs/net-excel-udf-tips.php#determining-cell.


Andrei Smolin
Add-in Express Team Leader
Posted 26 Sep, 2018 06:44:29 Top

Posts 1 - 10 of 11
First | Prev. | 1 2 | Next | Last