Pass the variable `Nothing` using Python's `win32com`

Question

I am trying to pass the variable Nothing in VBA using Python's win32com. I tried to use None but it returned 'Type dismatch.'

Could anyone please help?

Thank you!

An example:

' Book1.xlsm!Module1
Function test(arg As Object) As String
    If arg Is Nothing Then
        test = "Success"
        Exit Function
    Else
        test = "Failure"
        Exit Function
    End If

End Function

And in Python:

import win32com.client
import os
import pythoncom


xl = win32com.client.Dispatch('Excel.Application')
xl.Visible = True
xl.Workbooks.Open(Filename=os.path.abspath('Book1.xlsm'))
test_str = xl.Application.Run('Book1.xlsm!Module1.test', pythoncom.Empty)

The REPL says:

runfile('C:/Users/shwang/Downloads/untitled0.py', wdir='C:/Users/shwang/Downloads')
Traceback (most recent call last):

  File "<ipython-input-22-301693920f2c>", line 1, in <module>
    runfile('C:/Users/shwang/Downloads/untitled0.py', wdir='C:/Users/shwang/Downloads')

  File "C:\Anaconda3\lib\site-packages\spyder\utils\site\sitecustomize.py", line 866, in runfile
    execfile(filename, namespace)

  File "C:\Anaconda3\lib\site-packages\spyder\utils\site\sitecustomize.py", line 102, in execfile
    exec(compile(f.read(), filename, 'exec'), namespace)

  File "C:/Users/shwang/Downloads/untitled0.py", line 16, in <module>
    test_str = xl.Application.Run('Book1.xlsm!Module1.test', pythoncom.Empty)

  File "<COMObject <unknown>>", line 14, in Run

  File "C:\Anaconda3\lib\site-packages\win32com\client\dynamic.py", line 287, in _ApplyTypes_
    result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args)

com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352561), None)

Answer 1

In VBA, Nothing is used as the uninitialized default value Object type which usually references COM objects (i.e., Excel object library or external application's object library). VBA's primitive integer, long, and string types cannot be assigned the Nothing value. And usually programmers use this at the end of code block to release objects from memory.

Hence, there is no strict translation between VBA's Nothing (a special value for COM types) and Python's value types including None which crudely means empty or no value for any type.

With that said, your code will not err out if you pass a COM object which you already initialize with xl. Below outputs FAILURE. If somehow you can declare an uninitialized COM object which then will carry Nothing then you can pass that into your function. But to call Dispatch requires a named object. Catch-22!

import win32com.client
import os

try:
    xl = win32com.client.Dispatch('Excel.Application')
    xl.Visible = True

    wb = xl.Workbooks.Open(Filename=os.path.abspath('Book1.xlsm'))
    test_str = xl.Application.Run('test', xl)
    print(test_str)
    # FAILURE

    wb.Close(False)
    xl.Quit

except Exception as e:
    print(e)

finally:
    wb = None
    xl = None

Answer 2

Here is a working example (in python) that passes VBA Nothing object to SolidWorks API.

import win32com.client
import pythoncom
swApp = win32com.client.Dispatch('SldWorks.Application')
Part = swApp.ActiveDoc
boolstatus = Part.Extension.SelectByID2("", "SKETCHCONTOUR", 75e-3, -4e-3, 0, False, 0, pythoncom.Nothing, 0)
print(boolstatus)

Here is the output in Sublime Text 3:

True

[Finished in 0.7s]

The script tries to select a sketch contour with the coordinates (75mm, -4mm). The "Callout" argument of the SolidWorks API "SelectByID2" is assigned as pythoncom.Nothing. For the script to work, the premise is that you are editing a sketch as shown in the attached figure below.

The attached figure shows what happens in SolidWorks (before | after).

Pass Nothing to SW API SelectByID2

Some background. I want to draw some electric motor geometry in SolidWorks via python because I already have the working python codes with other drawing tools (such as JMAG Designer, FEMM, or PyX).

Thanks for the post! Your discussion helps me find this.