Reference Excel from Class Library

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

Reference Excel from Class Library
 
Jeremy McClanathan




Posts: 10
Joined: 2017-02-09
Hello,
How do I reference Excel from a class library? I am testing the process of using class libraries to store code that will be used among multiple add-ins. I created a class library with a simple function that passes some text to my add-in and got that to work. Then, I tried creating a sub that directly adds some text to cell A1 from the class library and got an exception that I haven't been able to find an answer to yet. I am guessing that I don't have some reference that I need. So, my questions are:

1. Which references do I need in a class library to modify ranges and tables in Excel?


2. More generally, how do I find out what references I need for code I would like to write?

Below is the code from my sample class library. It builds successfully, but throws an exception when called from Excel.


Imports excel = Microsoft.Office.Interop.Excel

Public Class clibTestClassLib4

    Public Shared Sub main()
    End Sub

    Public Sub TestingExcelSub()
        Dim e As New excel.Application
        e.Range("A1").Value = "This text is directly from the class library which references excel"
    End Sub

End Class




Partial Exception Message:


Exception Source: Microsoft.Office.Interop.Excel
Exception Type: System.Runtime.InteropServices.COMException
Exception Message: Exception from HRESULT: 0x800A03EC
Exception Target Site: get_Range


Thanks for your help!
Jeremy
Posted 12 Feb, 2017 14:24:41 Top
Andrei Smolin


Add-in Express team


Posts: 14129
Joined: 2006-05-11
Hello Jeremy,

A COM add-in should only use the Application object that Office passes to it at startup. In Add-in Express that object is available via the HostApplication object; the project wizard also generates a host-specific property such as ExcelApp.

That is, you shouldn't write Dim e As New excel.Application.

Instead, you can pass the value of the ExcelApp property to the constructor of a class from the class library; in the code fragment you supplied, this is the clibTestClassLib4 class.

Consider this raw sketch:


Imports Excel = Microsoft.Office.Interop.Excel 
 
Public Class clibTestClassLib4 

    Dim theExcelApp as Excel.Application

    Public Sub New(anExcelApp as Excel.Application)
        theExcelApp = anExcelApp
    End Sub 
 
    Public Sub TestingExcelSub()         
        theExcelApp.Range("A1").Value = "This text is directly from the class library which references excel" 
    End Sub 
 
End Class 


Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 13 Feb, 2017 04:18:36 Top
Jeremy McClanathan




Posts: 10
Joined: 2017-02-09
Thank you. I tried the code you provided and it builds successfully, but gets the same exception. See below. Can you provide some insight as to why it is throwing this exception?


  • Exception Source: Microsoft.Office.Interop.Excel
  • Exception Type: System.Runtime.InteropServices.COMException
  • Exception Message: Exception from HRESULT: 0x800A03EC
  • Exception Target Site: get_Range
Jeremy
Posted 13 Feb, 2017 18:10:33 Top
Andrei Smolin


Add-in Express team


Posts: 14129
Joined: 2006-05-11
Hello Jeremy,

This project works fine for me:
http://temp.add-in-express.com/support/MyAddin31-VbClassLibraryExample.zip

I suppose you perform this action in a moment when Excel isn't ready to accept/access data - say, when Excel starts etc.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 14 Feb, 2017 03:36:21 Top