Introduction to Python for VBA users-Calling Python from Excel with xlwings-

I will explain how to call Python from Excel using xlwings with the aim of eliminating Excel VBA.

** Note **: xlwings itself is compatible with macOS, but at the time of posting this article, UDFs (user-defined functions) can only be used on Windows.

1.First of all

1.1. Target audience

I'm making various EUC tools with Excel VBA,

――I want to make tools more efficiently ――I want to improve my programming skills --I'm sick of VBA --Creating a dll in C ++ or .NET seems to be troublesome or difficult --I want to distribute a program made in Python to users

It is intended for people who like. If you've only used VBA, you may not be accustomed to running on the command line, which is common in Python's description, or running py files directly. But with the Python library xlwings, you can start using Python in a VBA-like way.

First, let's aim to try using Python.

1.2 What is Python?

It is a programming language like VBA. If you mention the difference from VBA

-** Simple grammar : Also used as a programming language for beginners. - Modern features : Even arrays are completely different in usability. VBA hasn't evolved from VBA6 in 1999 for more than 20 years, so it can't be helped. - Extensive library **: Advanced scientific calculations, data analysis, AI development, etc. can be greatly expanded.

And so on.

1.3 What are xlwings?

It is a package that calls Python from Excel and manipulates Excel from Python. Other packages that handle Excel include openpyxl,

--Similar to VBA in writing --You can effectively use existing macros

That's why I'm sick of VBA but I have to keep using it ~~ I think it's suitable for stepping up VBA users. The ability to call Python from Excel using xlwings also includes Run main and Run Python, but since UDF includes them, I will only explain UDF.

2. Preparation

With VBA you can get started quickly with Alt + F11, but with Python you need to do a lot of preparation.

2.1. Installing Python

Install the classic Anaconda of the Python distribution (Python body + set of external libraries). Download the installer from here and install it with administrator privileges. The more you google, the more ways you can do it.

In the screen below, it is deprecated, but it is easier to check it later:

Even if you don't check it, you can set the path by running the following in Powershell:

> $newSystemPath = [System.Environment]::GetEnvironmentVariable("Path", "User")
> $newSystemPath += ";C:\ProgramData\Anaconda3"
> $newSystemPath += ";C:\ProgramData\Anaconda3\Scripts"
> $newSystemPath += ";C:\ProgramData\Anaconda3\Library\bin"
> [System.Environment]::SetEnvironmentVariable("Path", $newSystemPath, "User")

When setting for each terminal, set "User" on the first and last lines to "Machine" (administrator authority required).

2.2. Installing xlwings

Anaconda comes with it from the beginning, so no work is required. If not, install with pip:

> pip install xlwings

2.3 Change Excel settings

Open File> Options> Security Center> Security Center Settings> Macro Settings. Enable macros in Macro Settings and put ✓ in Trust access to VBA project object model.

2.4 Add-in installation and configuration

2.4.1 Add-in installation

Install it command line or manually. Installing the add-in will add an xlwings tab to the Excel ribbon.

Command line

Execute the following with PowerShell etc. If the path of the Python installation destination (Anaconda defaults to C: \ ProgramData \ Anaconda3) does not pass, specify xlwings.exe with the full path.

xlwings addin install

When you update xlwings, run xlwings addin update.

Manual

On Excel, select ʻExcel Add-in from the pull-down below File> Options> Add-ins and press Settings`. Select xlwings.xlam in the dialog box that appears when you press the Browse'' button. Use one of the following for xlwings.xlam.

--Download the installed version of xlwings.xlam from the Release Page on Github of xlwings. --Xlwings.xlam located in (Anaconda installation destination) \ pkgs \ xlwings-0.16.0-py37_0 \ Lib \ site-packages \ xlwings \ addin

2.4.2 Add-in settings (global)

You can leave it as it is for the time being. The explanation is as follows. Set as necessary. The settings here are applied on a per-user basis.

-** ʻInterpreter**: Set the path of Python.exe (egC: \ ProgramData \ Anaconda \ python.exe). If you do not enter anything, it will search from the environment variable, so if you have set the path, you do not need to set it. -** ʻUDF Modules : The name of the called Python file (excluding the .py extension). When setting more than one, separate them with ";". If nothing is entered, the file with the same name (extension .py) in the same directory as the Excel spreadsheet will be imported. It is easier to use this item if you set it in the workbook described later. - Debug UDFs : Check this when debugging later. - Restart UDF Server **: Shuts down the server running the UDF. The server will restart at the next UDF run. Changes in the called Python file itself will be reflected automatically, but changes in the module imported in the Python file will not be reflected, so press this button to reflect it.

If there is a workbook setting, that will be given priority.

2.4.3 Add-in settings (workbook)

You can skip it for the time being. You can configure add-ins on a workbook-by-workbook basis in the xlwings.conf sheet within the workbook. As for the xlwings.conf sheet, there is a _xlwings.conf sheet in the workbook created by quickstart described later, so copy and rename the entire sheet. Enter the value corresponding to the item in column A in column B to set it. Unnecessary setting lines can be deleted, and if there is a setting line, the setting of the workbook will be reflected, otherwise the setting of the ribbon will be reflected. Is it a practical use to set only ʻUDF Modules` and delete the other lines?

2.5. Text editor

Prepare a debuggable text editor as you will need it for debugging later. We recommend Visual Studio Code. If you google how to install this, you will find a lot. You should also have the Python extension installed.

3. Let's use

We will explain in the following three cases. Let's get used to using UDF by looking at it in order.

3.1. quickstart command

The following command will create a folder containing the Excel and Python files (both named myproject, rename myproject to whatever you like).

> xlwings quickstart myproject

The contents of myproject.py are as follows (excerpts and comments added for explanation):

myproject.py

import xlwings as xw       #① Import of xlwings

@xw.func                   #② Decorator
def hello(name):           #③ Function declaration
    return "hello {0}".format(name)

To briefly explain the code ① Import xlwings so that it can be used in Python. ʻAs xwmakes it available under the aliasxw. ② @ xw.funcis a decorator that allows you to use Python functions as UDFs. Remember to prefix the function you want to use in Excel with `` @ xw.func``. ③ Declare the function withdef. The indented part below : (return ...` in the above) is the content of the function. It has become. Only the decorator is a little difficult to attach, but I think you can understand the simplicity of Python.

Then open myproject.xlsm and press ʻImport Functions on the ribbon xlwigs to import the hello function of myproject.py.

Open the VBA editor with Alt + F11. If the import is successful, xlwings_udfs has been added to the standard module.

Let's try the hello function. Type = hello ("Python") in any cell and it will return hello Python.

I will modify it a little. Open myproject.py and change the return ... part as follows:

myproject.py

@xw.func
def hello(name):
    return "Hello {0}!".format(name)

If you recalculate the cell, you get Hello Python !.

Now let's add a function. Add the following to myproject.py.

myproject.py

@xw.func
def double_sum(x, y):
    return 2 * (x + y)

If you want to import a new function, press ʻImport Functions again. Enter = double_sum (2, 3) in any cell and if 10 is returned, you are successful. It's easy.

3.2. Official sample

Let's look at an example that is a little more complicated than before with the official sample. You can see the strength of cooperation with external libraries.

From GitHub udf.xlsm and udf.py Download (/raw/master/examples/udf/udf.py), save it in the same folder and open udf.xlsm.

Let me explain some.

add_one

@xw.func
@xw.arg('data', ndim=2)
def add_one(data):
    """Adds 1 to every cell in Range"""
    return [[cell + 1 for cell in row] for row in data]

Returns the value of each cell plus one. In the workbook, the cell range E11: G12 is entered with Ctrl + Shift + Enter, which is an array formula. @ xw.arg ('data', ndim = 2) is a decorator for controlling arguments, and is always a two-dimensional list (array) even if the argument data is a single cell or row / column. I will read it as. The [cell + 1 for cell in row] part is called list internal notation, and you can easily create a list (array).

matrix_mult

@xw.func
@xw.arg('x', np.array, ndim=2)
@xw.arg('y', np.array, ndim=2)
def matrix_mult(x, y):
    """Alternative implementation of Excel's MMULT, requires NumPy"""
    return x.dot(y)

Returns the product of matrices. Same as Excel's MMULT function. @ xw.arg ('x', np.array, ndim = 2) causes the argument x to be read as a two-dimensional Numpy Array. Numpy Array is defined in the scientific calculation library Numpy In the list (array), [dot function] of Numpy Array (https://numpy.org/doc/1.18/reference/generated/numpy.ndarray.dot.html ) Is used to calculate the matrix product.

CORREL2

@xw.func
@xw.arg('x', pd.DataFrame, index=False, header=False)
@xw.ret(index=False, header=False)
def CORREL2(x):
    """Like CORREL, but as array formula for more than 2 data sets"""
    return x.corr()

Returns the correlation matrix between time series data. @ xw.arg ('x', pd.DataFrame, index = False, header = False) loads the argument x as a Pandas DataFrame with no index and header. Pandas DataFrame is a library for data analysis Pandas It's like a two-dimensional array defined in, Pandas DataFrame's [corr function](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.corr.html#pandas.DataFrame. Corr) is used to calculate the correlation matrix. Then, @ xw.ret (index = False, header = False) removes the index and header from the DataFrame of the calculation result of the corr function, and returns only the value to Excel.

If you try to do the same thing with VBA, it will be long because you write a for loop. In addition, Numpy and Pandas have various other functions. This is another strength that VBA does not have.

@ xw.arg and @ xw.ret

@ Xw.arg and @ xw.ret that appear in the above function are called converters and are data between Excel and Python. Performs type conversion and so on. I will post about the data input / output method using the converter at a later date. looking forward to.

3.3. Add to existing Excel file

You can also make it possible to call Python from an existing .xlsm. It's easy to forget, so I'll write it first, but you need to ** open the reference settings in the VBA editor and check xlwings **.

Let's make it possible to call udf.py from the existing Excel file "existing .xlsx". First, change the file format. Press F12 and select Macro Enabled Workbook (* .xlsm) as the format.

Put the existing .xlsm and udf.py in the same folder. Open an existing .xlsm, copy the _xlwings.conf sheet from the myproject.sheet created in 3.1., And rename the sheet to xlwings.conf. Enter udf in the UDF Modules.

Open the VBA editor with Alt + F11, open Tools> References in the menu bar and check xlwings.

Press the ʻImport Functions` button on the ribbon. If the import is successful, xlwings_udfs will be added to the standard module on the VBA editor.

Let's try the double_sum function. Enter = double_sum (2,3) in any cell and 10 will be returned.

You can rename the Python file to "existing .py" without copying the xlwings.conf sheet. However, when using the same Python file in multiple Excel files, or when dividing the version by adding v2 at the end of the Excel file, it is convenient to specify the module using the xlwings.conf sheet. ..

4. Debug

Let's debug udf.py. In the case of udf.py, it is included from the beginning, but if you want to debug other code, add the following at the end.

if __name__ == '__main__':
    xw.serve()

Next, set breakpoints etc. on Visual Studio Code. Let's set it in the return statement of the matrix_mult function.

When you press F5 in the editor, a pull-down for selecting the debug configuration will appear. Select "Python File" to execute the debug.

If you click the gear mark on the upper left and select "Python File" as well, a debug configuration file will be created. After that, just press F5 to execute debugging. (You can close the launch.json tab as it is)

Go back to the Excel screen and check Debug UDFs on the ribbon (if there is a row for'Debug UDFs' in the xlwings.conf sheet, set the value True).

If you recalculate the sheet (Alt + Shift + F9), it will stop running at the breakpoint you just saw. You can check the contents of the variable in the variable on the upper left.

It's a trick, but if you serialize the variables from the debug console, you can check the contents with Jupyter Notebook, which is convenient. For example, the Pandas DataFrame variable df can be serialized withdf.to_pickle ('df.pickle')in the debug console and read withdf = pd.read_pickle ('df.pickle')in the Jupyter Notebook cell. For example, you can see various variables being debugged on the Jupyte Notebook.

5. Conclusion

In this article, I explained how to use xlwings. Note that UDF should not be used in a large number of cells like Excel functions. It is slow because it interacts with the UDF server on a cell-by-cell basis and cannot perform multithreading.

Then, how to use it is to register it in the button. At this time, the data is input to the Python side inside the Python function instead of the UDF argument, and the processing result is output to the Excel table or CSV file.

I've posted some advanced content below, including stories about this area (I'll post them one by one in the future, so stay tuned).

-Call Excel VBA macros from Python using xlwings -[Creating] Data input / output between Excel-Python (Pandas DataFrame) -[Under construction] Practical example using xlwings UDF

reference

-Official Document -[Python is installed in Excel? Then-use xlwings](https://qiita.com/yniji/items/b38bc312e860027108ac#python-%E3%81%8B%E3%82%89-excel-%E3%82%92%E6%93% 8D% E4% BD% 9C% E3% 81% 99% E3% 82% 8B)