Unable to access DNA-Excel library in VBA code

[Android Acct]

I'm trying to access C# objects in Excel VBA using Excel DNA. The only difference is that Excel and the spreadsheet that contains the VBA code are opened using (what I assume is) OLE Automation by a third piece of software over which I have no control or no source-code access to.

I tried with the example code that Michael Katajamaki has kindly provided at http://mikejuniperhill.blogspot.com/2014/03/interfacing-c-and-vba-with-exceldna-no.html.

I can make the example work on a stand-alone basis, i.e. if I manually open the spreadsheet and try to debug through the code it works, but nothing that I try with the OLE Automation-opened sheet works. 

I have tried the following:

- Added code that automatically opens the XLL file using the following call: Application.RegisterXLL ("G:\Code\XLServer.xll") in the Workbook_Open event

- Manually opened the xll file using File->Open, after the Excel sheet was auto-opened. 

- Manually registered the xll file with a call to regsvr32, this operation reports that the xll has been successfully registered but the code still fails with the same error.

In all of these cases, the failure occurs at the line, Dim lib As Object: Set lib = CreateObject("CSharp_functionLibrary") with the exception "Run-time error '429': ActiveX component can't create object'.

On debug, I find that the variable lib is not initialized.

I have tried the Intellisense version as well to see if that would get me past this error, but no luck with that as well.

Any help would be greatly appreciated.

[Govert van Drimmelen]

My first suggestion would be that you confirm that the .xll actually loads in the RegisterXLL call.

Perhaps you can add a small test function in your add-in:

[ExcelFunction(IsHidden=true)]

public static string TestMyAddIn()

{

    return "Hello";

}

and after the RegisterXLL call, you try

Dim check As String

check = Application.Run("TestMyAddIn")

Debug.Print check

This should at least indicate that the .xll loads successfully.

If this works fine, we can think of the next debugging step.

-Govert

[Android Acct]

Govert, Thank you for the very quick reply. Yes, the XLL is loaded, I do see "Hello" printed in the Immediate Window.

[Govert van Drimmelen]

OK, the next step is to check that you have simplified the situation as much as possible:

·         Make sure Excel does not load any add-ins automatically at startup. This is one difference between the normal start and starting through automation.

·         Make a single test workbook that registers the .xll, and has a VBA Sub that tests the COM interop.

·         Start Excel directly, load the workbook, run the test Sub (maybe it makes the COM call and writes something to the sheet).

·         Now start Excel from automation (maybe something like the powershell below), open the workbook and run the test Sub.

 

PS C:\> $app = New-Object -comobject Excel.Application

PS C:\> $wb = $app.Workbooks.Open("c:\Temp\TestCom.xlsm")

PS C:\> $wb.Name

TestCom.xlsm

PS C:\> $app.Run("DoTest")

PS C:\> $app.Range("A1").Value()

Worked!

PS C:\> $app.Quit()

 

-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.

[Govert van Drimmelen]

Maybe it matters whether Excel is visible, and the automation is not making it visible.

You can try to set it to visible (Application.Visible) before your RegisterXLL call.

-Govert

[Android Acct]

Your initial suggestion to use Application.Run got me wondering if I could use the Application.Run call for all the method calls that I needed to make from the spreadsheet.

Turns out that did work. So I changed the [ExcelFunction] annotations in the C# class to [ExcelCommand]. In the Excel VBA code, instead of instantiating a lib object, and calling methods on it, I did away with the lib variable altogether and replaced the lib.<method> calls with Application.Run("method", param1) calls instead and everything runs smoothly.

Does using Application.Run instead of lib.<method> calls have any downsides?

That said, I am still curious as to why the original code didn't work. I am not familiar with PowerShell coding, so will take some time to acquaint myself with it before trying out the tests you suggested.

>> Maybe it matters whether Excel is visible, and the automation is not making it visible.

>> You can try to set it to visible (Application.Visible) before your RegisterXLL call.

Not sure if I understood you correctly here; Excel *is* visible and accessible throughout my use case. The third party application opens the workbook and starts streaming values to a sheet in the workbook. It is the XLL file, which even though accessible to the spreadsheet, cannot be referenced in a VBA variable, for some reason.

[Govert van Drimmelen]

Application.Run should work well. It might be a bit slower, and you don't get the rich object model. For simple calls it's probably better.

You wouldn't need PowerShell - you could make the simple test code in a small C# console app too. The five PowerShell lines could be copied into PowerShell command lone, if you have a recent Windows version - it's just an easy way to execute a few lines of COM automation, if you don't want to do it in VBA.

If it's visible already, then my suggestion is not applicable.

Not sure I have any other suggestions, but it sounds like you have a working plan now.

-Govert

--

[Android Acct]

Govert,

Thank you very much for your suggestions. They've been very helpful indeed.