I am trying to write a method for Excel using Python and use it, but when I call the method and use it, It recognized my function but it did not work. I wanted to know where I wrote the code wrong? This is the Python code I wrote :
import pythoncom
import numpy as np
import win32com.client
import win32com.server.register
class PythonObjectLibrary:
# this will create a unique id for register using by windows
_reg_clsid_ = pythoncom.CreateGuid()
# register the obj as an exe file
_reg_clsctx_ = pythoncom.CLSCTX_LOCAL_SERVER
# this is the program id, this is the name of our object library.
# we will use this to create the object in vba.
_reg_progid_ = "Python.ObjectLibrary"
# this is the description of our library.
_reg_desc_ = "This is our python object library"
# a list of strings that indicate the public methods for the object.
# if they aren't listed they are considered private.
_public_methods = [
'pythonSum',
'pythonMultiply',
'addArray'
]
# return the sum of two values
def pythonSum(self, x, y):
return x + y
# return the product of two values
@staticmethod
def pythonMultiply(x, y):
return x * y
# adding a range of values
@staticmethod
def addArray(my_range):
# create an instance of the range object passed through
rng1 = win32com.client.Dispatch(my_range)
# convert a range into a numpy array
rng1val = np.array(list(rng1.value))
# return the sum of that numpy array
return rng1val.sum()
if __name__ == "__main__":
win32com.server.register.UseCommandLine(PythonObjectLibrary)
And this is my Excel code that I wrote in the insert module section:
Function pythonSum(x As Long, y As Long)
pythonSum = VBA.CreateObject("Python.ObjectLibrary").pythonSum(x, y)
End Function