The problem of coding with Python in VBA Excel program

Viewed 28

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
0 Answers
Related