none
Move User Defined Type in VBA to and from a VB.Net Com Library RRS feed

  • Question

  • I need to send a user defined type from VBA to and From VB.Net (using COM interop). Where can I find an example illustrating how this is done?
    Monday, August 22, 2011 2:24 PM

Answers

  • Hi Infrazee,

    I tested the code on my side and also reproduced the issue. The cause of the scenario is that you defined the struct Composite twice, one is in the interface ClassInterface and another is in class MyComClass, after compiling one is convert to Composite_2 in order to avoid repetitiveness. If I removed the interface, there will be just one Composite in VBA intelligence:

    Sub test()
      Dim client As New ClassLibTest.MyComClass
         Dim a As ClassLibTest.Composite
         Dim b As ClassLibTest.Composite
         a.Real = 22.5
         a.Imaginary = 2.89
         b.Real = 1.52
         b.Imaginary = 2.5
         a = client.AddSubtract(a, b)
         MsgBox "a = " & a.Real & " " & a.Imaginary & ", b= " & b.Real & " " & b.Imaginary
    End Sub

    Hope this can explain the scenario and wish you a nice day.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by infrazee Wednesday, August 24, 2011 11:18 AM
    Wednesday, August 24, 2011 6:08 AM

All replies

  • Hi Infrazee,

    I think you wan to pass the user defined type variable to the COM visible function, if I have misunderstood you just feel free to let me know.

    Please refer to this thread about the similar problem:

    http://stackoverflow.com/questions/2027758/pass-an-array-from-vba-to-c-using-com-interop

    which involves how to pass the User Defined Type in VBA to call .Net function.

    Hope this can help you and just feel free to follow up after you have tried.

    Best Regards, 


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, August 23, 2011 6:41 AM
  • The following works but I dont know why. Can anyone throw any light on this?

    First the VB.Net Library:


    Public Interface ClassInterface
        Function AddSubtract(ByRef A As Composite, ByRef B As Composite) As Composite
        Structure Composite
            Dim Real As Double
            Dim Imaginary As Double
        End Structure
    End Interface
    <ComClass(MyComClass.ClassId, MyComClass.InterfaceId, MyComClass.EventsId)> _
    Public Class MyComClass
    #Region "COM GUIDs"
        Public Const ClassId As String = "a5df38c2-53e2-4a98-92db-610aa4c61978"
        Public Const InterfaceId As String = "9759ba81-9ac9-413f-b9d7-f28d37ae63bd"
        Public Const EventsId As String = "d6144e43-9659-4d05-b1a9-da61e25e4d68"
    #End Region
        Public Structure Composite
            Dim Real As Double
            Dim Imaginary As Double
        End Structure
        Public Sub New()
            MyBase.New()
        End Sub
        Public Function AddSubtract(ByRef A As Composite, ByRef B As Composite) As Composite
            Dim Result As New Composite
            Result.Real = A.Real + B.Real
            Result.Imaginary = A.Imaginary + B.Imaginary
            B.Real = A.Real - B.Real
            B.Imaginary = A.Imaginary - B.Imaginary
            Return Result
        End Function
    End Class


    The VBA Code is as follows:

        Dim client As New ClassLibrary1.MyComClass
        Dim a As ClassLibrary1.Composite_2
        Dim b As ClassLibrary1.Composite_2
        a.Real = 22.5
        a.Imaginary = 2.89
        b.Real = 1.52
        b.Imaginary = 2.5
        a = client.AddSubtract(a, b)
        MsgBox "a = " & a.Real & " " & a.Imaginary & ", b= " & b.Real & " " & b.Imaginary


    This seems to work properly. However, Where does the type "Composite_2" come from? If I use the type "Composite" the VBA complains of an "argument type mismatch" in the AddSubtract Function call!

    Any help on this would be most appreciated.

     

    Tuesday, August 23, 2011 8:33 PM
  • Hi Infrazee,

    I tested the code on my side and also reproduced the issue. The cause of the scenario is that you defined the struct Composite twice, one is in the interface ClassInterface and another is in class MyComClass, after compiling one is convert to Composite_2 in order to avoid repetitiveness. If I removed the interface, there will be just one Composite in VBA intelligence:

    Sub test()
      Dim client As New ClassLibTest.MyComClass
         Dim a As ClassLibTest.Composite
         Dim b As ClassLibTest.Composite
         a.Real = 22.5
         a.Imaginary = 2.89
         b.Real = 1.52
         b.Imaginary = 2.5
         a = client.AddSubtract(a, b)
         MsgBox "a = " & a.Real & " " & a.Imaginary & ", b= " & b.Real & " " & b.Imaginary
    End Sub

    Hope this can explain the scenario and wish you a nice day.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by infrazee Wednesday, August 24, 2011 11:18 AM
    Wednesday, August 24, 2011 6:08 AM