8

UPDATE: After spending 20+ hours trying to get a simple example working, I have realized that this is not as simple as it seems. Articles like this reveal the "gotchas" - and this was written before Windows 7 (which handles manifests differently). I'm sticking with exposing .NET assemblies to VBA via VSTO.

I made a simple COM-Visible .NET assembly and am trying to call it from Excel VBA. If I "Register for COM Interop" during my .NET build, I can call it from VBA successfully.

Sub VBA()    
    Dim obj As Object
    Set obj = actCtx.CreateObject("ComTest.Main")
    MsgBox obj.Test() '<-- Displays "Hello"
End Sub

However, I want to do registration-free.

Updated per advise from Hans:

I unchecked Register for COM Interop, and set my app.Manifest to: 

<?xml version="1.0" encoding="utf-8"?>
<asmv1:assembly 
  manifestVersion="1.0" 
  xmlns="urn:schemas-microsoft-com:asm.v1" 
  xmlns:asmv1="urn:schemas-microsoft-com:asm.v1"
  xmlns:asmv2="urn:schemas-microsoft-com:asm.v2" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <assemblyIdentity
    type="win32"
    version="1.0.0.0" 
    name="ComTest" 
    publicKeyToken="a36a7110110d7bd7" />

  <clrClass
      clsid="{975DC7E0-4596-4C42-9D0C-0601F86E3A1B}"
      progid="ComTest.Main"
      threadingModel="Both"
      name="ComTest.Main"
      runtimeVersion="v4.0.30319">
  </clrClass>

  <file name = "ComTest.dll"></file>   
</asmv1:assembly>

I created a dummy "client.manifest" like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<asmv1:assembly 
  manifestVersion="1.0" 
  xmlns="urn:schemas-microsoft-com:asm.v1" 
  xmlns:asmv1="urn:schemas-microsoft-com:asm.v1" 
  xmlns:asmv2="urn:schemas-microsoft-com:asm.v2" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    manifestVersion="1.0" 
    xmlns="urn:schemas-microsoft-com:asm.v1" >
    <assemblyIdentity
        name="xxx"
        version="1.0.0.0" />
    <dependency>
        <dependentAssembly>
            <assemblyIdentity
                type="win32"
                name="ComTest" 
                version="1.0.0.0"
                publicKeyToken="a36a7110110d7bd7" />
        </dependentAssembly>            
    </dependency>
</asmv1:assembly>

I modified my VBA to use client.manifest when creating my object:

Sub VBA()    
    Dim actCtx As Object
    Set actCtx = CreateObject("Microsoft.Windows.ActCtx")
    actCtx.Manifest = "C:\Users\me\Desktop\COM Test\ComTest\ComTest\bin\Debug\client.manifest"

    Dim obj As Object
    Set obj = actCtx.CreateObject("ComTest.Main")  '<-- Fails here.
    MsgBox obj.Test()    
End Sub

It fails on CreateObject with the less-than helpful error Method 'CreateObject' of object 'IActCtx' failed.

sxstrace shows that it reads client.manifest and creates the Activation Context. Process Monitor shows that it accesses ComTest.dll and searches the Registry for class 975DC7E0-4596-4C42-9D0C-0601F86E3A1B.

What am I missing?

Here's the .NET Code:

<ComVisible(True)>
<Guid("EB6AA207-ECC7-413B-9A9B-9D142FF2701D")>
<InterfaceType(ComInterfaceType.InterfaceIsIDispatch)>
Public Interface IMain
    Function Test() As String
End Interface

<ComVisible(True)>
<Guid("975DC7E0-4596-4C42-9D0C-0601F86E3A1B")>
<ProgId("ComTest.Main")>
<ClassInterface(ClassInterfaceType.None)>
<ComDefaultInterface(GetType(IMain))>
Public Class Main
    Implements IMain
    Public Function Test() As String Implements IMain.Test
        Return "HELLO"
    End Function
End Class

I'm running 64-bit Windows 7.

mr_plum
  • 2,448
  • 1
  • 18
  • 31
  • Use clrClass instead of comClass. – Hans Passant Mar 20 '13 at 00:32
  • Trying to do the exact same in Microsoft Access. I am not finding a solid solution to deploying it in a production environment as I cannot get it to register on other machines when trying to execute regasm via C#. I thought RegFree would be a great solution to resolve this. Did you by chance ever get this working? – Anthony Griggs Sep 11 '18 at 18:07
  • 1
    @AnthonyGriggs I don't think it's possible, but I've struggled with the same problem. You can, however, dynamically register the DLL using the technique I described [here](https://stackoverflow.com/a/53592322/7296893). – Erik A Dec 03 '18 at 13:08

1 Answers1

0

To use registration-free COM, both the server (the assembly) and the client need to be in the same folder. See:

http://msdn.microsoft.com/en-us/magazine/cc188708.aspx

user1610015
  • 6,561
  • 2
  • 15
  • 18
  • I get the error even if I copy everything to my Office12 folder (where EXCEL.exe lives) and run it from there. – mr_plum Mar 19 '13 at 15:37
  • 1
    Oh, I missed that you were trying to consume it from VBA. Well, I don't think you can make reg-free COM work in this case, because that would require creating an application manifest for Excel. Plus it doesn't seem like a good idea to copy stuff into an application's installation folder. I guess reg-free COM is designed for ease of deployment when you are the developer of both the server and the client, but it doesn't work well when the client doesn't have knowledge of the server, because the client needs to reference the server in its application manifest. – user1610015 Mar 19 '13 at 16:00