Replacing VBA with Python¶
xlSlim allows you to replace VBA code with Python code. The Python code has access to the same objects as VBA so it is usually straightforward to translate VBA code to Python.
This functionality requires a premium licence as a Python environment with the pywin32 extensions installed is required. Anaconda distributions include pywin32. See Licensing
Let’s start with this very simple VBA subroutine that updates one cell:
Public Sub VBARules() ActiveWorkbook.ActiveSheet.Cells(2, 2).Value = "VBA Rules!" End Sub
Add the subroutine into a Module:
And then add a button calling the VBA macro.
The Developer ribbon can be enabled by editing your Ribbon layout under File->Options->Customize Ribbon
When the button is clicked the value of cell B2 is modified:
Accomplishing the same in Python is straightforward. This Python code defines a function that sets the value of one cell:
XLSLIM_COMAPPFUNC = None def PythonRules(): excel_obj = XLSLIM_COMAPPFUNC() app = excel_obj.Application app.ActiveWorkbook.ActiveSheet.Cells(2, 2).Value = "Python Rules!"
Notice how the highlighted line is identical to the VBA code, other than the initial “app.”. The Python and VBA code access an identical Excel object model, so the code can be very similar. The
XLSLIM_COMAPPFUNC() function is used to gain access to the Excel object model.
Save the Python code as a new file on your PC. I saved the file in my Documents folder.
Register the Python code with this
RegisterPyModule() formula (amending the location to match where you saved the file):
Then add another button to call the Python function:
The names of the Python macro functions do not appear in the list. However, if you type the name correctly the button will be linked to the Python code.
Clicking on the button edits cell B2 to properly reflect the state of the world!
Easier code editing¶
While editing your code it will be easiest to run the code from a Python editor so you can rapidly see the results and amend as required.
Edit your Python code to match the code below, the amended lines are highlighted:
import win32com.client as win32 XLSLIM_COMAPPFUNC = None def PythonRules(): if XLSLIM_COMAPPFUNC is not None: # Running from Excel excel_obj = XLSLIM_COMAPPFUNC() app = excel_obj.Application else: # Running in an editor app = win32.gencache.EnsureDispatch("Excel.Application") app.Visible = 1 app.Workbooks.Add().Activate() app.ActiveWorkbook.ActiveSheet.Cells(2, 2).Value = "Python Rules!" if __name__ == "__main__": PythonRules()
This code will now open a new instance of Excel when run from an external editor. When run within Excel the code will behave as before and modify the active Excel instance. This code modification allows the Python code to be run and edited in a very similar manner to VBA code in the VBA editor.
This code shows how to perform common tasks. See Creating a Pivot Table using Python for an example how to replace VBA to create a pivot table.
import time import pythoncom import win32com.client as win32 from win32com.client import constants XLSLIM_COMAPPFUNC = None def manipulate_excel(): if XLSLIM_COMAPPFUNC is not None: # Running from Excel # Do be careful not to accidentally edit the calling cell excel_obj = XLSLIM_COMAPPFUNC() app = excel_obj.Application else: # Running in an editor # # This could be used instead to connect to a running Excel # app = win32.GetActiveObject("Excel.Application") # however the gencache versions gives a better editing experience # as methods and properties are then known in the editor app = win32.gencache.EnsureDispatch("Excel.Application") app.Visible = 1 app.Workbooks.Add().Activate() # Cells can be referenced in various ways app.ActiveWorkbook.ActiveSheet.Cells(2, 1).Value = "Python Rules!" # Names can be used too, commented out now as Book1 easily becomes Book2 # app.Workbooks("Book1").Sheets("Sheet1").Cells(2, 1).Value = "Python Rules!" app.Workbooks(1).Sheets(1).Cells(2, 1).Value = "Python Rules!" # Creating variables for books and sheets makes the code neater xlBook = app.Workbooks(1) xlSheet = app.Sheets(1) xlSheet.Cells(2, 1).Value = "Python Rules!" # Handling datetimes now = time.mktime(time.localtime()) time_object = pythoncom.MakeTime(now) xlSheet.Cells(3, 1).Value = time_object # Setting a formula xlSheet.Cells(4, 1).Formula = "=UPPER(A2)" # Clear a cell xlSheet.Cells(5, 1).Value = None # Ranges can be set in various ways range1 = xlSheet.Cells(4, 1) # one-cell range range2 = xlSheet.Range("B5:C10") # excel notation range3 = xlSheet.Range(xlSheet.Cells(3, 3), xlSheet.Cells(6, 4)) range3.Interior.ColorIndex = 7 # Set range data range3.Value = (("z", 1), ("y", 2), ("x", 3), ("w", 4)) # Call a function - sort range - note the use of constants range3.Sort( Key1=xlSheet.Range("C3"), Order1=constants.xlAscending, Orientation=constants.xlSortColumns, ) return "Done" if __name__ == "__main__": manipulate_excel()