I have a simple .Net 2.0 class which I am trying to access from Excel
(Office XP).

<InterfaceType(ComInterfaceType.InterfaceIsDual) _
Public Interface IComClass
Property Value() As Object
End Interface

<ClassInterface(ClassInterfaceType.None) _
Public Class ComClass
Implements IComClass
Public Property Value() As Object Implements IComClass.Value
Get
Return Me._Value
End Get
Set(ByVal value As Object)
Me._Value = value
End Set
End Property
Private _Value As Object

Public Sub New()
Me._Value = 1234
End Sub
End Class

I have produced the .tlb, registered the class and added the
apprioriate reference in VBA. However, when I try to run the following
code I get an "object required" error at the line ' CC.Value = "abcd"
'

Dim CC As New ComClass
MsgBox (CC.Value)
CC.Value = "abcd"
MsgBox CC.Value

Any idea what I am missing?

Thanks
Akash

Akash,

I have not done this before so I'm just guessing. But I think the problem
is that you have the Value property declared as an object

Property Value() As Object

and in your VBA code, you try to assign a string value to it which is not an
object.

CC.Value = "abcd"


Maybe you can change the Value Property to String? Or assign an object to
the Value property when you use it.. For example, will it give an error when
you try something like this?

Set CC.Value = CreateObject("Excel.Sheet")


--
Hope that helps.

Vergel Adriano


"akash" wrote:

I have a simple .Net 2.0 class which I am trying to access from Excel
(Office XP).

<InterfaceType(ComInterfaceType.InterfaceIsDual) _
Public Interface IComClass
Property Value() As Object
End Interface

<ClassInterface(ClassInterfaceType.None) _
Public Class ComClass
Implements IComClass
Public Property Value() As Object Implements IComClass.Value
Get
Return Me._Value
End Get
Set(ByVal value As Object)
Me._Value = value
End Set
End Property
Private _Value As Object

Public Sub New()
Me._Value = 1234
End Sub
End Class

I have produced the .tlb, registered the class and added the
apprioriate reference in VBA. However, when I try to run the following
code I get an "object required" error at the line ' CC.Value = "abcd"
'

Dim CC As New ComClass
MsgBox (CC.Value)
CC.Value = "abcd"
MsgBox CC.Value

Any idea what I am missing?

Thanks
Akash

Vergel,

AFAIK, a .Net object maps to a COM variant, so I have tried:

Dim v as variant
v = "abcd"
CC.Value = v

and that also gives the same error.

Unfortunately I can't just have the property declared as a string due
to the values it needs to take.

I tried your idea of Set CC.Value = CreateObject("Excel.Sheet") and
that worked, which is interesting. I'm not sure how to go from there
to being able to pass doubles/strings/booleans etc into the property,
but thanks for getting me one step closer!



On May 2, 2:04 pm, Vergel Adriano
wrote:
Akash,

I have not done this before so I'm just guessing. But I think the problem
is that you have the Value property declared as an object

Property Value() As Object

and in your VBA code, you try to assign a string value to it which is not an
object.

CC.Value = "abcd"

Maybe you can change the Value Property to String? Or assign an object to
the Value property when you use it.. For example, will it give an error when
you try something like this?

Set CC.Value = CreateObject("Excel.Sheet")

--
Hope that helps.

Vergel Adriano

Some more info:

I've extended the interface to include

Function GetValue() As Object
Sub SetValue(ByVal V As Object)

and I am able to do CC.SetValue("abcd"). This is even more puzzling,
as I thought .Net properties got converted to setter and getter
methods by the compiler, which is basically what I am replicating in
the above interface.

Akash

I think it's because .NET is truly object oriented and a string is an object.
Whereas it would not work in VBA because a string is not an object and so
when you try to do the assignment in VBA, it tells you that you need an
object to assign to another object... something like that...


--
Hope that helps.

Vergel Adriano


"akash" wrote:

Some more info:

I've extended the interface to include

Function GetValue() As Object
Sub SetValue(ByVal V As Object)

and I am able to do CC.SetValue("abcd"). This is even more puzzling,
as I thought .Net properties got converted to setter and getter
methods by the compiler, which is basically what I am replicating in
the above interface.

Akash