i want to write Excel macros to deal with the data, but i am not familiar with VBA language. so i decide to use python instead of VBA.

at the beginning, i find xlrd,xlwt for python Excel operations, but that doesn‘t support Excel macro. Finally i find xlwings.

xlwings - Make Excel Fly!

• Scripting: Automate/interact with Excel from Python using a syntax close to VBA.
• Macros: Replace VBA macros with clean and powerful Python code.
• UDFs: Write User Defined Functions (UDFs) in Python (Windows only)

You can find more details in the offical document links: https://www.xlwings.org/

xlwings have many power functions, i will only introduce how to write Excel macros

1. Installation

The easiest way to install xlwings is via pip:

pip install xlwings

or conda:

conda install xlwings

i suggest that you install Anaconda, it already includes a lot of useful moudles, and xlwings will depend on part of them.

2. Add-in

you need Windows command line to install/remove the add-in in Excel.

xlwings addin install: Copies the xlwings add-in to the XLSTART folder

After installing the add-in, it will be available as xlwings tab on the Excel Ribbon. you need to give the interpreter path.

Interpreter: This is the path to the Python interpreter (works also with virtual or conda envs), e.g. "C:\Python35\pythonw.exe" or "/usr/local/bin/python3.5". An empty field defaults to pythonwthat expects the interpreter to be set in the PATH on Windows or .bash_profile on Mac.

3.Quickstart

you need Windows command line to create necessary files automatically. 

• xlwings quickstart myproject

This command is by far the fastest way to get off the ground: It creates a new folder myprojectwith an Excel workbook that already has the reference to the xlwings addin and a Python file, ready to be used right away:

myproject
  |--myproject.xlsm
  |--myproject.py

4.VBA: RunPython

In the VBA Editor (Alt-F11), write the code below into a VBA module. you can add new module via Insert > Module

Sub HelloWorld()
    RunPython ("import hello; hello.world()")
End Sub

This calls the following code in hello.py:

# hello.py
import numpy as np
import xlwings as xw

def world():
    wb = xw.Book.caller()
    wb.sheets[0].range(‘A1‘).value = http://www.mamicode.com/‘Hello World!‘

You can then attach HelloWorld to a button or run it directly in the VBA Editor by hitting F5.

xlwings: Write Excel macro using python instead of VBA