Debugging COM server connection to VBA / Excel

Question

I have followed the documentation provided for the Python comtypes module (http://pythonhosted.org/comtypes/server.html) and built a simple COM server.

It seems to be working correctly. I can see that the typelib and interface are registered (using Oleviewer) and from a Python script I can call the (single) method that adds two integers.

However, I cannot make it work with Excel (Office 2007, running on Win7). The following simple function does not work (execution stops when MyMethod is called)

Function test(a, b)
    Dim x As New MyTypeLib.MyObject
    ab = x.MyMethod(a, b)
    Debug.Print "d" & CStr(ab)
    test = ab
End Function

Is there any way to debug this?

More comments

There is indeed light at the end of the tunnel. The suggestion to use VBScript has been helpful. The following short script does work (providing independent confirmation that the COM server works).

Dim x,ab
Set x = CreateObject("MyTypeLib.MyObject")
ab = x.MyMethod(1, 2)
MsgBox CStr(ab)

However, VBScript is calling the server using late binding and I was trying to get the early binding to work.

So, I changed my Excel VBA function to use late binding and expected things to work, but they do not.

Here is the modified VBA:

Function test(a, b)
    Dim x As Object
    Set x = CreateObject("MyTypeLib.MyObject")
    gadd = x.MyMethod(1, 5)
End Function

So my server can be used with late binding from Python and VBScript, but not Excel!

Using Process Monitor I can see that in both late and early binding cases Excel tries unsuccessfully to load msvcr90.dll, even though this DLL is installed on the system.

Here is a screenshot from Process Monitor, showing where Office Excel 2007 on 64-bit Windows 7 Home Edition starts to try to load msvcr90.dll

Answer 1

When I posted this question, I needed to know a bit more about how to debug COM servers (well a lot more actually!). It turns out that there were several problems that stopped my server from working. I think finding out about the tools is probably as much of interest as the actual problems, so this answer will try to cover both.

Initially, it was helpful to use VBScript to provide an independent verification that the server was registered and working (the simple code is shown above). VBScript uses late binding and I wanted early binding, but it was nevertheless helpful because I then realised that Excel did not work with the late binding VBA code either (on machine A)!

Something odd happened too with VBScript. When I first wrote scripts they ran by default (clicking on the file in Windows Explorer), but then for no apparent reason they stopped working. I found that I needed to specify the full path to the 32-bit version (C:\WINDOWS\SysWOW64\cscript.exe) for them to work.

Also worth noting is that by using cscript.exe, instead of wscript.exe, debugging messages embedded in my Python modules appear on the Windows CMD window.

The next breakthrough came by using the Dependency Walker program depends.exe. This is often mentioned in posts like mine where people are trying to figure out why DLLs are not working.

Dependency Walker is available from it's own website, but that version does not work properly with Windows 7 (see Profiling x86 executable with Dependency Walker hangs on Windows 7 x64) so I found that I needed to install WDK 8.1 (from here: http://msdn.microsoft.com/en-US/windows/hardware/gg454513).

DW is very powerful and a bit overwhelming at first. However, by reading its logging output it became clear that Excel 2007 uses msvcr80.dll and that the server's attempt to load msvcr90.dll was failing (even though the DLL was properly installed on the machine). This was not a problem on machine B because Excel 2013 uses msvcr90.dll.

Now, in these tests I was creating the server using Python comtypes interactively (there is no server DLL involved). But there is also the possibility of creating a DLL by using the py2exe tool. I was trying to do this too, the DLL failed to register using regsvr32.

DW helped debug this problem too. Happily, in the logging output I noticed a stack trace of a Python exception. It was the GetModule() in the example. It turns out that this call is needed only once, to create some Python classes that are then cached on the machine. Removing this command fixed the problem.

Also, although it may not have caused a problem, the official current version of py2exe is 0.6.9 but this caused some files that are no longer available to be bundled (specifically zlib.pyd). The unofficial version 0.6.10 (available here: http://www.lfd.uci.edu/~gohlke/pythonlibs/#py2exe) solved that issue.

So I now have the possibility of compiling a COM server DLL, in which I can include the required msvcr90.dll, and, yes, it works on both machines :-)

In summary then. Windows 7, with its mixture of 32-bit and 64-bit libraries is a nightmare. VBScript helps, by providing an independent environment for testing a server. Dependency Walker really is amazing, but takes ages to learn to use (other useful tools in the WDK include oleviewer, process-monitor and process-explorer).