6
\$\begingroup\$

I want to create a COM server to expose scipy to VBA code, as performing integration is kinda difficult and I don't know of any good VBA/COM libraries for this. My objectives are:

  • As portable as possible
  • Fast
  • Maintainable

Here's the python:

# Requires C:\Users\...\anaconda3\Library\bin on the PATH
from scipy import integrate
from win32com.client import Dispatch
from typing import Callable
import comtypes


class ScipyWrapper:
    # pythoncom.CreateGuid()
    _reg_clsid_ = "{A621AB64-9378-4B54-A7D3-20711C0B72DB}"
    _reg_desc_ = "Python Scipy COM Server"
    _reg_progid_ = "PythonCOM.ScipyWrapper"
    _reg_clsctx_ = comtypes.CLSCTX_LOCAL_SERVER # don't want inproc because conda not supported on 64 bit 
                                                # see https://stackoverflow.com/q/67462529/6609896

    _public_methods_ = ["quad",]
    # _public_attrs_ = []
    # _readonly_attrs_ = []

    def quad(self, vbFunc, a: float, b: float) -> float:
        vb_dispatch = Dispatch(vbFunc)
        func: Callable[[float], float] = vb_dispatch.Evaluate  # Callable[..., float]?
        return integrate.quad(func, a, b)[0]  # only return val, not uncertainty


if __name__ == "__main__":
    import win32com.server.register

    win32com.server.register.UseCommandLine(ScipyWrapper)

Called from VBA (twinBasic) like:

Module Main
    Public Sub doStuff()
        Dim integrator as Object = CreateObject("PythonCOM.ScipyWrapper")
        Dim func as IOneDimensionalFunction = New XSquared 'this is what we'll integrate
        Debug.Print integrator.quad(func, 0., 1.) 'integrate x^2 from 0->1, returns 0.333333...
    End Sub
End Module

'@Description("Interface for a f(x) -> y function, with optional parameters")
Class IOneDimensionalFunction
    Public Function Evaluate(ByVal x As Double, ParamArray parameters() as Variant) As Double
    End Function
End Class

Class XSquared
    Implements IOneDimensionalFunction
    Private Function IOneDimensionalFunction_Evaluate(ByVal x As Double, ParamArray parameters() as Variant) As Double
        Return x^2
    End Function    
End Class

Questions:

  • Not sure how to Type the python, vbFunc is a PyIDispatch instance but not sure what that means for typing/mypy.
  • I'd like to use strong typing to protect my python, right now it's duck typed and I could easily pass an Interface without the Evaluate method, or anything else for that matter.
  • No idea how to handle python errors and turn them into COM errors.
  • Is this ref-count safe?

OFC any other tips on general style, use of comments and naming etc. are welcome :)

\$\endgroup\$
2
  • \$\begingroup\$ What problem did you intend to solve with this? Python can do a lot of direct communication with Office software, ignoring VBA altogether. \$\endgroup\$ Commented May 14, 2021 at 19:15
  • 1
    \$\begingroup\$ @Mast I need the integration to do convolution of probability distributions, which I'm after for a code profiling library in VBA. I could do post processing of data in a python script but I think it's cleaner if I can import a VBA library without any kind of file IO to communicate with python, going through COM is smoother \$\endgroup\$ Commented May 14, 2021 at 19:24

1 Answer 1

Reset to default
2
+50
\$\begingroup\$

Deferred Imports

Why is import win32com.server.register only imported during __main__? It should be inexpensive and clean to just leave it at the top with everything else. I'd also de-namespace from win32com.server.register import UseCommandLine but that's mostly a matter of taste.

Stubbing

You ask:

vbFunc is a PyIDispatch instance but not sure what that means for typing/mypy

If PyIDispatch is an importable type, then simply

def quad(self, vbFunc: PyIDispatch, a: float, b: float) -> float:

If it's not importable at all (this can happen for some libraries that are aggressively dynamic), then you basically have two options:

  • Declare an opaque, named NewType to make it clear to callers that this is not just "anything"
  • Do the work that the library should have done for you, and stub it yourself using a custom .pyi

Call protocols

# Callable[..., float]?

You're right to be confused, and mypy is still fairly terrible at annotating variadic callables. Here you should consider using Protocol:

class DispatchCall(typing.Protocol):
    def __call__(self, x: float, *parameters: COMCompatibleTypes) -> float:
        ... # literally an ellipsis

# Later:

func: DispatchCall = vb_dispatch.Evaluate

Unpacking

Re.

return integrate.quad(func, a, b)[0]  # only return val, not uncertainty

it's worth noting that uncertainty is not the only thing quad returns; there's actually y, abserr, infodict, message and explain.

It's minor, but I typically prefer to do at least a partial unpack to make it a little more clear what's happening:

y, *_ = integrate.quad(func, a, b)

Given the tuple length I don't think I'd do this, but you can if you think it adds clarity:

y, abserr, infodict, message, explain = integrate.quad(func, a, b)
\$\endgroup\$
9
  • \$\begingroup\$ I'm still not entirely sure how to typehint func inside quad. if quad is quad(func, a:float, b:float, args:Tuple) then func must accept a Tuple arg (Callable[[float,Tuple],float]. If a tuple is not passed to quad then func may accept a Tuple arg Callable[[float,Tuple],float] | Callable[[float],float] | Callable [[float, *args],float]. There may be some @overload I can do... Moreover Evaluate really looks like__call__(self, x: float, *args: COMCompatibleTypes) -> float: where COMCompatibleTypes does not include Tuple... \$\endgroup\$ Commented May 19, 2021 at 18:05
  • \$\begingroup\$ ... Since the *args can be left out, the function sig is compatible with Callable[[float],float] for func, but not Callable[[float, Tuple],float]. This means when I pass a tuple to quad(), I want mypy to warn me that func is expected to be Callable[[float, Tuple],float] which is not compatible with __call__(self, x: float, *args: COMCompatibleTypes) -> float even though that is compatible with the signature of func when a Tuple is not passed \$\endgroup\$ Commented May 19, 2021 at 18:07
  • 1
    \$\begingroup\$ Scipy quad accepts (optional) arguments and forwards them onto func as a tuple. However my func implementation is defined in VBA to accept arguments in the variadic form with ParamArray. Consequently, as these two definitions for arguments are incompatible, I want mypy to enforce that no additional arguments are passed, as the variadic signature cannot be invoked with a tuple so the only way to make the 2 signatures compatible is to remove any optional arguments. COMCompatibleTypes is a Union type of a few things, for the sake of this, let's say it is typing.Any except Tuple (pseudo code) \$\endgroup\$ Commented May 19, 2021 at 23:58
  • 2
    \$\begingroup\$ Crucially, whereas scipy quad accepts extra arguments as a tuple, it does not pass them on as a tuple. It unpacks them. This calls for a plain *args: float. \$\endgroup\$ Commented May 20, 2021 at 14:39
  • 1
    \$\begingroup\$ This is what I was hoping for with the type hinting stackoverflow.com/q/67649533/6609896. I think it may require the new PEP 612 -- Parameter Specification Variables but not sure. The problem with Protocol is that it doesn't seem to play nicely with Callable, so I can't pass a custom callable that matches the protocol - e.g. a python function or lambda, and must manually typehint callables to match the protocol which is a bit weird. It's maybe fine here since the Evaluate function needs to be typehinted in python anyway, however it arbitrarily restricts the sig of my scipy stub method \$\endgroup\$ Commented May 24, 2021 at 12:54

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.