Using VBA With Python

[areed1192]

I know many of us use VBA consistently, but I wanted to share a new way we can use the VBA model. Inside Python, we have access to the Win32 library for Windows users. With this library, we can control the same Excel object model, and other object models, that we use inside VBA. What this means is we can do the same things we have done in VBA inside Python, but we will get access to even more objects and tools.

I started a series on YouTube where I'll start recreating everyday VBA tasks inside of Python. Also, we explore how to use Python libraries with the VBA Model. If you want to check out the videos I have so far, check out my playlist on YouTube.

• Link to Python VBA Series
• Copying Between Excel & PowerPoint Using Python
• Running Python Code in an Open Excel Workbook
• Using Pandas With The VBA Model

For example, with this library, we can now run Python code real time in an Excel workbook using the Jupyter Notebook. This means we can leverage different libraries like Pandas & Numpy in Excel! Here was a simple example where I run some code that formats cells in an open workbook.

The Win32 library is not only restricted to the Excel application, but it can also be used with the other Office applications as well.

PowerPoint Example - Aligning Shapes.

Word Example - Formatting Certain Words

 

[ThrottleWorks]

Hi @areed1192 sir, thanks a lot for sharing.

 

[tonyroberts]

Thanks for sharing @areed1192!

I put together some notes on differences between VBA and Python when calling the Excel Object Model from Python - Python as a VBA Replacement. This is based on questions I've received over the years from developers migrating from VBA to Python with PyXLL (a commercial product that lets you write Excel add-ins in Python). I hope it can help people on here too.

Thanks again for sharing your videos!