Creating Python Functions for VBA

It very much is possible, at least for Windows’ users. However, to do this, we are going to have to create our own VBA Object Library. With this library, we can create our own Python objects that we can call from Excel! The idea behind this object is we can define methods that belong to it. Once we’ve done that we call the object in VBA, and then execute its methods. 

Let’s create two simple formulas, one that will add two numbers and another that will add a range of cells. 

Step One: Import Our Libraries 

We’ll need a couple of libraries to do this; the first one is the Win32 library?so that we can plugin to the Windows’ API. The second one is pythoncom, inside this library is a handy little function to create a GUID. We need to generate a GUID so that we can register the object libraries with Windows. The third and final one is Numpy, and that library will be for the method that will add a range of cells. 

Step Two: Create a Python Class that will house our “Formulas”. 

In the first section of our class, we need to define some information for the registration process. 

class PythonObjectLibrary:
  
    # This will create a GUID to register it with Windows, it is unique.
    _reg_clsid_ = pythoncom.CreateGuid()


    # Register the object as an EXE file, the alternative is an DLL file (INPROC_SERVER)
    _reg_clsctx_ = pythoncom.CLSCTX_LOCAL_SERVER


    # the program ID, this is the name of the object library that users will use to create the object.
    _reg_progid_ = "Python.ObjectLibrary"


    # this is a description of our object 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', 'addArray']

The first one creates our GUID, the second one specifies that we want it stored on our local server, the third is our object library description, the fourth is our programmatic identifier, and finally, the last section defines the methods of our objects.

Step Three: Define the methods 

The first method will take two parameters, both numbers, and return the sum of those two numbers. The second will receive a range object, go and fetch the range object, call the value property to return a tuple of the values in that range, convert it to a list and then a Numpy array? Finally, it will return the sum of that Numpy array. 

# add two cell values
def pythonSum(self, x, y):return x + y


# add a range of cell values
def addArray(self, myRange):


# create an instance of the range object that is passed through
rng1 = win32com.client.Dispatch(myRange)


# Get the values from the range
rng1val = np.array(list(rng1.Value))
return rng1val.sum()

Step Four: Register the Library 

Finally, we will use win32com.server.register as there is an excellent tool that will allow us to register our object using the command line. We need to be able to run the script and then call the object so the if __name__==’__main__’: method will work fine. 

if __name__=='__main__': 

    print ("Registering COM server...") 
    
    import win32com.server.register
    win32com.server.register.UseCommandLine(PythonObjectLibrary) 

Here is the full Python Code: 

# import our libraries
import pythoncom
import numpy as np
import win32com.client


class PythonObjectLibrary:


    # This will create a GUID to register it with Windows, it is unique.
    _reg_clsid_ = pythoncom.CreateGuid()


    # Register the object as an EXE file, the alternative is an DLL file (INPROC_SERVER)
    _reg_clsctx_ = pythoncom.CLSCTX_LOCAL_SERVER


    # the program ID, this is the name of the object library that users will use to create the object.
    _reg_progid_ = "Python.ObjectLibrary"


    # this is a description of our object 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','addArray']


    # add two cell valuesdef pythonSum(self, x, y):return x + y


    # add a range of cell valuesdef addArray(self, myRange):


        # create an instance of the range object that is passed through
        rng1 = win32com.client.Dispatch(myRange)


        # Get the values from the range
        rng1val = np.array(list(rng1.Value))


        return rng1val.sum()


if __name__ == '__main__':
    import win32com.server.register
    win32com.server.register.UseCommandLine(PythonObjectLibrary)

VBA SECTION: 

Okay, we are almost there, let’s jump into VBA. Open the VBA editor, and add the following two functions: 

Function pythonSum(x As Long, y As Long) 
    pythonSum = VBA.CreateObject("Python.ObjectLibrary").pythonSum(x, y) 
End Function 


Function addArray(x As Range) 
    addArray = VBA.CreateObject("Python.ObjectLibrary").addArray(x) 
End Function 

Notice how both of these are using the Create Object method? The reason we have to do this because to access the methods, we need to access the Object library first. Once we have the object, we call the method and pass through the parameters. 

HERE IS THE IMPORTANT PART!

We have to be very careful about what we pass through our functions. For example, if we pass through a number, it needs to be a number, not a range object. That’s why when I define my functions I make sure to set the inputs of the function, that way Python doesn’t misinterpret what we pass through.