Problem in using Excel.Application Object in VB.Net Shared Add-in

[VictorCC]

Hi,

I currently need to build a Automation Add-in for Excel. This add-in will
load a recordset from Database to Excel. I would like to have function A()
that I input in Cell as a formular. This function A() call a the add-in to
load the data from Database to Excel. The data should be a matrix. So that
means I would like to set in Excel worksheet a range of data by just calling
single function in a cell. (I am sure there should be some way to implement
it)
Now the key point here is how can I modify multiple cells in worksheet by
only calling the formular(or a function) in ActiveCell.(I know it can't be
done by pure VBA)

As I know if I develop a shared Add-in by VB(or VB.Net) and in the add-in, I
can get an Object of Excel.Application. By this Excel.Application object, I
can get full control of Excel inside the Add-in. That means if I define a sub
that load datamatrix from database and using the Excel.Application object I
can populate the data matrix to the Excel.

I have done much research on internet and I now have built a shared Add-in
for excel. I should say this add-in works fine without invoke the
Excel.Application Object that I have tested.
But when I call the Add-in function that invode Excel.Application Object
from Excel I got an error as below:Run-time error '2147467262'(*)
Unable to cast object of type 'System.String' to type Excel.Application
Here is my VBA code:

Function LOADMATRIX() As Variant
Dim sum As Integer
Dim object8 As MyExcelAddin_Try8.Connect
Set object8 = CreateObject("MyExcelAddin_Try8.Connect")
object8.ModifyAnyCell (Excel.Application)
LOADMATRIX = sum
End Function

And here is my VB code for Shared Add-in:

imports Extensibility
imports System.Runtime.InteropServices

#Region " Read me for Add-in installation and setup information. "
' When run, the Add-in wizard prepared the registry for the Add-in.
' At a later time, if the Add-in becomes unavailable for reasons such as:
'   1) You moved this project to a computer other than which is was
originally created on.
'   2) You chose 'Yes' when presented with a message asking if you wish to
remove the Add-in.
'   3) Registry corruption.
' you will need to re-register the Add-in by building the $SAFEOBJNAME$Setup
project,
' right click the project in the Solution Explorer, then choose install.
#End Region

<GuidAttribute("720CA51A-E5B1-4863-A67E-2E9B4524D80D"),
ProgIdAttribute("MyExcelAddin_Try8.Connect")> _
Public Class Connect

Implements Extensibility.IDTExtensibility2

Public applicationObject As Object
Public addInInstance As Object

Public Sub OnBeginShutdown(ByRef custom As System.Array) Implements
Extensibility.IDTExtensibility2.OnBeginShutdown
End Sub

Public Sub OnAddInsUpdate(ByRef custom As System.Array) Implements
Extensibility.IDTExtensibility2.OnAddInsUpdate
End Sub

Public Sub OnStartupComplete(ByRef custom As System.Array) Implements
Extensibility.IDTExtensibility2.OnStartupComplete
End Sub

Public Sub OnDisconnection(ByVal RemoveMode As
Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements
Extensibility.IDTExtensibility2.OnDisconnection
End Sub

Public Sub OnConnection(ByVal application As Object, ByVal connectMode As
Extensibility.ext_ConnectMode, ByVal addInInst As Object, ByRef custom As
System.Array) Implements Extensibility.IDTExtensibility2.OnConnection
applicationObject = application
addInInstance = addInInst

MsgBox("Hello World!" & applicationObject.ToString)
End Sub

Public Function MyAdd(ByVal x As Integer, ByVal y As Integer) As Integer
MyAdd = x + y
End Function
' A Test Sub
Public Sub ModifyAnyCell(ByRef ApplicationO As Object)
Dim obj As Excel.Application
obj = CType(ApplicationO, Excel.Application)
' A test Try
obj.ActiveCell.Offset(1, 1).Cells(1, 1).value = 999
End Sub

End Class

when VBA called "object8.ModifyAnyCell (Excel.Application)", I debugged into
the add-in code at line:
"obj = CType(ApplicationO, Excel.Application)"
I found that the Object ApplicationO is a String type with value "Microsoft
Excel".

I guess I have made a mistake on how to pass a Excel.Application Object into
Add-in.

Could anyone tell me where I made the mistakes?
Thank you for your help in advance!