Re: ExcelDna.Integration.ExcelDnaUtil.Application returns nothing

[Govert van Drimmelen]

Hi Craig,

This sounds like a problem with how Excel-DNA deals with the Excel
2013 change to and SDI interface.

I don't have easy access to an Excel 2013 installation to test, but I
suggest you try the following workaround:

Add a class that implements the IExcelAddIn interface, and calls
ExcelDnaUtil.Application in the AutoOpen method. This will ensure that
Excel-DNA grabs a reference to the Application object when it is
loaded, even if it will only be used later.
Internally, Excel-DNA caches the COM reference in the first call to
ExcelDnaUtil.Application, so you need not keep a copy yourself.

Your code might look like this:

Public Class MyAddIn
Implements IExcelAddIn

Public Sub AutoOpen() Implements IExcelAddIn.AutoOpen
Dim unused As Object = ExcelDnaUtil.Application
End Sub

Public Sub AutoClose() Implements IExcelAddIn.AutoClose
End Sub
End Class


If you try this, please let me know whether it works.

Regards,
Govert


On Jan 22, 1:52 am, csm <cmillsh...@gmail.com> wrote:
> I am having a problem getting Excel application object.  This line, from
> the code below, is my problem.
> Dim app As Object = ExcelDna.Integration.ExcelDnaUtil.Application
>
> It is not consistently returning the application object.
>
> I am using VS 1012 with Excel 2013 32 bit.
>
> This is a clean project I set up from your NuGet package.  The sample
> function worked great.  I didn't get problems until I tried to modify it to
> get the application object.
>
> The only pattern I can see is that it appears to work fine on the default
> workbook.  However, I don't use it on that first workbook and then open
> another one (creating a new SDI window), the function in that workbook
> doesn't work.  The applicaiton object is nothing.
>
> Please let me know what I am doing wrong.
>
> Thanks,
>
> Craig
>
> Imports ExcelDna.Integration
>
> Public Module MyFunctions
>
>     Private Function ReferenceToRange(xlref As ExcelReference) As Object
>         'app object is often nothing.
>         Dim app As Object = ExcelDna.Integration.ExcelDnaUtil.Application
>         If app Is Nothing Then
>             MsgBox("app is nothing")
>         End If
>
>         ReferenceToRange = app.Range(XlCall.Excel(XlCall.xlfReftext, xlref,
> True))
>
>     End Function
>
>     <ExcelFunction(Description:="My second .NET function",
> IsMacroType:=True)> _
>     Public Function
> HelloDNARangeAddress(<ExcelArgument(AllowReference:=True)> rng As Object)
> As String
>
>         If TypeOf rng Is ExcelReference Then
>             Dim rngObj As Object = ReferenceToRange(rng)
>             Return rngObj.Address
>         Else
>             Return Nothing
>         End If
>
>     End Function
>
> End Module

[Govert van Drimmelen]

Hi Craig,

I'm not sure what shutting down the add-in would mean, but if Excel is
crashing we're not going to have a chance to do much. For example,
just make a .dna file with the following:

Do you have problems with a minimal add-in that loads up the
Application object:

<DnaLibrary RuntimeVersion="v4.0" Language="VB" >
<![CDATA[

Public Class MyAddIn
Implements IExcelAddIn
Public Sub AutoOpen() Implements IExcelAddIn.AutoOpen
Dim unused As Object = ExcelDnaUtil.Application
End Sub
Public Sub AutoClose() Implements IExcelAddIn.AutoClose
End Sub
End Class

]]>
</DnaLibrary>

then run together with a copy of the v.0.30 ExcelDna.xll. If you have
a reproducible problem, I can have a look.

Another way to get the Application object would be to load a ribbon
interface. Do you have a ribbon, and if not, could you try to add this
to your add-in?

Otherwise we should try to get to a reproducible problem, and I can
look further. I've not had other reports of issues with Excel 2013
(apart from a CTP issue related to the SDI interface) and didn't
notice any instability in my initial testing.

Regards,
Govert


On Jan 24, 6:12 pm, csm <cmillsh...@gmail.com> wrote:
> Oops!  I spoke too soon.  ExcelDNA getting the application object appears
> to be causing Excel 2013 to crash under certain scenarios.  This crashing
> is causing Excel to want to disable other add-ins it thinks are the
> culprits and is creating a mess.
>
> Is there a way to safely shut down the add-in from AutoOpen (or some other
> way) when certain conditions are met in order to avoid crashing Excel
> before ExcelDNA tries to access the application object?
>
> Thanks,
>
> Craig

[Govert van Drimmelen]

Hi Craig,

The fact that the class derived from ExcelRibbon is what makes Excel-
DNA load the ribbon.

Do you have a problem with the sample Distribution\Samples\Ribbon
\TestRibbon.dna with a matching copy of ExcelDna.xll renamed to
TestRibbon.xll ?

If there is no ribbon, and you call ExcelDnaUtil.Application in the
AutoOpen, is there then a problem?

How are you loading the add-in?
* Double-click on the .xll
* File->Open in a running Excel instance
* Adding it to the Excel Add-in list (which you also get by pressing
Alt+t, i)?

-Govert



On Jan 24, 6:56 pm, csm <cmillsh...@gmail.com> wrote:
> A few more bits of info since the post:  I am able to get the window handle
> and the excel version number in AutoOpen without crashing.
>
> You were onto something with the Ribbon comment.  I narrowed down why it is
> crashing *after *AutoOpen (when the application object is *excluded* from
> AutoOpen):
>
> Public Class MyRibbon
>     'This is the offending line
>     Inherits ExcelDna.Integration.CustomUI.ExcelRibbon
> End Class
>
> If I comment out *Inherits ExcelDna.Integration.CustomUI.ExcelRibbon*, then
> Excel does *not *crash.  However, I don't get a ribbon.  I tested this even
> without the Ribbon XML in the DNA file and it still crashes.  So it is just
> the presence of that one line in MyRibbon:   *Inherits
> ExcelDna.Integration.CustomUI.ExcelRibbon*
>
> Can you think of a way to get around that?
>
> Thanks,
>
> Craig
>
>
>
>
>
>
>
>

[Govert van Drimmelen]

Hi Craig,

The case where no workbook is open is special, since Excel has not
initialised the COM object model yet. Excel-DNA has some ugly code to
try to deal with this case, presumably that's now going wrong. I'll
try to investigate over the weekend.

Regards,
Govert

On Jan 24, 7:27 pm, csm <cmillsh...@gmail.com> wrote:
> I have a meeting to run to so can't set up a new project, but I can tell
> you what I did to produce this problem.  I was testing ExcelDNA for use
> with CTP and was using your CTP sample.  To narrow down the issue, I
> commented out all of the CTP - related code so I just had the MyRibbon
> class with only the Inherits line.  I also excluded the Usercontrol.
>  Commenting or including the RibbonXML didn't seem to matter regarding the
> crash.
>
> The crash happens when user opens xll and there is no open workbook.
>  Opening through regular addin dialog is okay.  It is just when file...open
> is done and no workbook is open.  This is going to be common on Excel 2013
> because it defaults to the splash screen and no workbook is the default
> setting.
>
> If PowerPivot or other addins are enabled, Excel thinks they did it (I
> assume because they have ribbons) and will try to disable them on next
> startup.  This becomes a mess if user clicks Yes to disable.
>
> I'd really like a solution.  Even if it is just giving a messagebox and not
> showing the ribbon.  Not sure how that would be done because I would still
> need that Inherits line for when I want the ribbon to show.
>
> Thanks,
>
> Craig

[Govert van Drimmelen]

Hi Craig,

OK - I think I can get to a problem with the 32-bit version of Excel
2013 under very particular steps.

Some sequences that cause no problems I could see:
(All the testing is with the .dna file I pasted in the earlier reply
on this thread)

1. If I double-click the .xll, everything is fine.
2. If I select the .xll from the Recent Files list, everything is
fine.
3. If I open Excel, then select 'Open Other Workbooks', then select
the .xll from the Recent Workbooks, everything is fine.
4. If I open Excel, then press Escape, then select File->Open,
Computer, Browse and pick the .xll, everything is fine.
5. If I add the add-in to the Add-Ins list, everything is fine every
time I start Excel.

The only way I can cause a problem is this:
* Open Excel, immediately select 'Open Other Workbooks', then
Computer, Browse and select the .xll. This causes my Excel to has some
repainting problem. I can press Ctrl+N to get a new workbook that will
work, but the ribbon is totally broken, Excel doesn't resize etc.
Pressing Ctrl+N again gets me a new Excel window where things work
fine. The broken Excel window is still at the back, and can be closed
with Ctrl+F4.


Is this consistent with what you see?

To speculate:
Perhaps Excel-DNA is trying to get the Application object before the
Excel ribbon and main window has been initialized, and this attempt
confuses the new Excel's initialization of the ribbon and window
chrome.


Regards,
Govert


On Jan 24, 10:08 pm, csm <cmillsh...@gmail.com> wrote:
> Thanks for your work, Govert.
>
> My experience in using the sample project above is if the xll is started
> through the addin dialog, it can get the application object even though no
> workbook is open.  So it doesn't seem to jibe with:
>
> *The case where no workbook is open is special, since Excel has
>
> > not initialised the COM object model yet.*
>
> I think the reason that the addin dialog approach works is because if you
> open the xll through the back-stage view from Options, Excel leaves the
> backstage view and shows the Excel desktop (ribbon view) when the xll is
> selected, even without a workbook being open.  If you do file...open
> approach from backstage, the Open File dialog pops up but in the background
> the backstage view is still present so the ribbon isn't shown.  So perhaps
> the issue isn't just a workbook being open but if not, the desktop must be
> showing and not the backstage.
>
> I must deal with this in another Excel SDI project and so use the
> application handle and windows API calls to determine if backstage or
> desktop (ribbon) window is showing.  That is the only way I could solve
> that issue.
>
> That is the path I was trying with ExcelDNA.  If a workbook wasn't open and
> the desktop wasn't showing, I was hoping to not start the the ribbon
> somehow.  It would be ugly and a bad workaround but it beats crashing Excel.
>
> I would be happy with something like an override on ExcelRibbon.New() where
> I can tell yes or no to start, or an ExcelDNA property to essentially "turn
> off" the addin internally because no workbook is open.  That would come in
> handy for licensing scenarios too where an addin could be turned off if not
> a valid license.
>
> I hope you can find something.
>
> Best,

[Govert van Drimmelen]

Hi Craig,

It's certainly something that needs to be fixed in Excel-DNA, so if
you come up with a plan, please write back.

Regards,
Govert

On Jan 27, 6:03 am, csm <cmillsh...@gmail.com> wrote:
> Hi Govert,
>
> I looked at your source code and think this is a problem I can fix myself
> as I am already familiar with the Excel windows issues.  Sorry I didn't
> have the courage to look sooner.  Given I presented a peculiar situation
> causing infrequent events, I'd be fine if you wanted to drop the issue and
> let me make my own changes.  I can let you know if I come up with anything
> worth sharing.
>
> Best,
>
> Craig