CODEWITHSUNDEEP
vb.netvbacom
dior001
dior001

Reputation: 761

VBA to COM Exposed .NET assembly - Object doesn't support this property or method

Are shared properties accessible from a COM exposed .NET assembly?

VBA

Dim appExcel As Object
    Dim objAppSingleton As Object
    Set objAppSingleton = CreateObject("Pitchbook.CommonUtils.Application.PitchbookAppSingleton")
    appExcel = objAppSingleton.CurrentPitchbookExcelApp

VB.NET

<ProgId("Pitchbook.CommonUtils.Application.PitchbookAppSingleton")> _
Public Class PitchbookAppSingleton

    Private Shared _currentPitchbookExcelApplication As PitchbookAppExcel
    Private Shared _syncLockExcel As Object = New Object()

   Public Shared ReadOnly Property CurrentPitchbookExcelApp As PitchbookAppExcel
        Get
            If _currentPitchbookExcelApplication Is Nothing Then
                                    SyncLock [_syncLockExcel]
                    If _currentPitchbookExcelApplication Is Nothing Then
                        Dim currPitchbookExcelApplication As New PitchbookAppExcel()
                        _currentPitchbookExcelApplication = currPitchbookExcelApplication
                    End If
                End SyncLock
            End If
            Return _currentPitchbookExcelApplication
        End Get
    End Property

End Class


Public Class PitchbookAppExcel
    Inherits PitchbookApp

    Protected Friend Sub New()
        MyBase.New()                        
    End Sub

End Class

The line appExcel = objAppSingleton.CurrentPitchbookExcelApp gives the error: Run-time error '438': Object doesn't support this property or method

Upvotes: 0

Views: 2072

Answers (2)

dior001
dior001

Reputation: 761

This is a very simple problem but if anyone doesn't know the answer already it's no you cannot access shared properties from a COM exposed .NET assembly. At least not directly... you can however access shared properties if you create an instance wrapper.

http://www.xtremevbtalk.com/showthread.php?p=1326018
http://support.microsoft.com/Default.aspx?kbid=817248

In my case I used the Facade pattern to create an object specifically for use by VBA that exposed the relevant shared utility functions via instances. Thanks to everyone for their comments. http://en.wikipedia.org/wiki/Facade_pattern

Upvotes: 0

Ben
Ben

Reputation: 35613

You need to say Set in VB to set an object variable.

Set appExcel = objAppSingleton.CurrentPitchbookExcelApp

If you don't say "Set" then VBA will look for the default method, and call that, to obtain a non-object type. If there is no default method defined (dispid=0) then it will fail with the error you are getting.

Upvotes: 1

Related Questions