If you have this feature which is currently free to use in Microsoft 365 Insiders, you will find Python in the Insert tab in the Excel Ribbon. You get a few Python libraries already ready for you to use in Excel. These are the pandas, matplotlib, seaborn, statsmodels and NumPy. You can import other libraries as you need using the import statement - for example import re module for REGEX calculations. How can you use Python in Excel in practical terms? I share a few ideas in the videos. You can use the pandas library and turn your data into a dataframe which you can then use for more complex tasks, like query the data, unpivot the data with python, create charts using matplotlib - make a line, area or even small multiples. You can grab URLs from long text by using the regex library. You can also use python together with Power Query. We'll only scratch the surface and even that is pretty awesome.
I mentioned this in the Python forum a few days ago, with little response, but happy to move the discussion here
I'm definitely interested in this, but reluctant to re-join the insiders program, and have beta updates being installed all the time.
If anyone has tried it, I'd be interested in their opinions.
From the limited information I've seen, there certainly seem to be aspects that are less than optimal for my way of working. All on-line and coding only on the worksheet being the main two I don't like the look of.
Now obviously the author (Tony Roberts) has a biased viewpoint, since the new Microsoft product will be in direct competition with his product (pyxll). Nonetheless, he gives reasoned arguments for each of the ways in which pyxll does a better job than the new Python in Excel.
The main reasons I am now focussing on using Python + pyxll with Excel, in place of VBA, are:
1. The ability to write user defined functions that can be called from any workbook.
2. Improved performance and better libraries for maths intensive functions such as solution of large sets of linear algebra equations.
3. Better maintenance and control of code, especially that shared with multiple users.
In all 3 respects, it looks like the new Excel+Python is a step backwards, even compared with VBA.
Vba has some reputation as a security risk. Specifically people are (perhaps understandably) very reluctant to run the vba code in a spreadsheet that was programmed by someone else. And MS has put some barriers in place to prevent unwanted code execution (xlsx files don't allow embedded vba, and MS asks whether code should be allowed to run when opening xlsm file from the internet). The whole real/perceived threat is a big problem imo.
I wonder how/whether those MS barriers will change (can python only be embedded in xlsm file?). And whether it be easier to produce and distribute code that will not pose a threat (or be perceived as a threat)?
Excel still supports the old on-sheet macros, and I assume they will do the same as a minimum for VBA.
I would actually be quite happy to have VBA replaced for new applications, as long as they replaced it with something that had at least equal functionality. It seems the Python add-in doesn't meet that requirement though.
