Use Python to replace VBA in Excel
Vložit
- čas přidán 12. 11. 2020
- Did you know that you can completely replace VBA in Excel with Python?
Script and automate Excel using nothing but Python, and write macro functions (Subs) and worksheet functions (UDFs) using PyXLL.
PyXLL
www.pyxll.com
Resources for VBA developers
www.pyxll.com/learn-python - Věda a technologie
As a Python dev, I really do appreciate staying in 1 language for development, especially when I produce some of my reports in Excel using Python. Now it looks like I can also include "embedded Python" aka Excel macros written in Python in my reports! Excellent!
Many thanks for this video. I wanted to study Python and since I use excel spreadsheets daily, I then switched to VBA about 6 days ago. This was because I was unaware that one could "import" PYXLL into excel. My main goal was learning Python since I'm interested in both Data Analysis and Machine Learning. Your video just switched me over to Python.
Once again, THANK YOU SO MUCH. I'm now a subscriber
Great to hear! Thank you :)
One of the many: "Thank you!"
Finally we got Python presented in Excel itself. Basically a hurdle less to embrass Python.
This is simply amazing! Having the power of Pandas, Numpy, lists, dicts, regex, requests, multithreading, ML, ... and the easier than VBA Python syntax ... ALL now available inside Excel. I hope there will be something like this for MS Access as well someday. This is a game changer and you get the best of both worlds.
Some wise man once said: "Learn a new language, get a new life."
Hope you keep making many more videos like this.
Glad you enjoyed it!
1. VBA does not usually index from 1 !
2. This is still miles from being as convenient and shareable as things made in VBA.
There is 'option base' isn't there. You can set the base number for arrays.
@@andyrobertson4096 yes, but 0 is still the standard
@2. maybe you are right in terms of sharing, but in my opinion nowadays nothing beats the readability and extensibility of python code. It would be cool to see native Python-support embedded into Office Macros.
Have you tried xlwings?
Totally agree. Not understanding how this is better than having the code written into the workbook you are giving someone to use.
It is a very interesting extension... but I don’t get the reason why it is offered as subscription, and why it is so expensive.
Dude there is a market demand where u can use python with excel in area of auditing /accounting where no one is teaching. I hope u start a series a based on those topic which would be so useful
Thank you for sharing that
Are you serious? There is a market for python/excel in accounting?
@@stevenmoore9805 yup but sad no one teaching these type of programming area for accountants
It's great video
Can you give me reference on your statement. Because what I am hearing from you is unique. Just wanted to confirm that how you arrive on this statement. and eager to discuss with you if allow
Have you done a speed comparison?
Do you know if it's possible to use an alternative VBA editor in Microsoft Office applications? The problem with the default VBA editor for Office is that on high resolution monitors it is impossible to increase the font size to the code area. I tried changing the font size on the menu TOOLS - OPTIONS - EDITOR FORMAT, but it didn't work. The command wasn't accepted. Somehow the font size to code area in default VBA editor is directed related to monitor resolution.
WOW, this is awesome! 🚀🚀 I need to try that immediately 😄
Quick question, if the code were to run directly from python, do the user still need to enable macro in the Excel instance?
I have successfully installed pyxll and even included the pyxll add-in in my excel, but anytime I try to launch the jupyter note book in excel
, I get a 'PyXLL: Ribbon Error' that states ' Error calling pyxll_jupyter.pyxll.open_jupyter_notebook'.
Where am I getting it wrong please?
EXCELLENT - That was great and VERY well made. Thank you very much.
Thanks! Glad you liked it :)
I see a lot of examples of using commands that reference changing the cells directly which is the slowest way of changing sheet data. I'm struggling to see how this is a replacement of VBA especially seeing how this is requiring an end user to use another type of program that they don't inherently have installed with windows whereas I can easily have buttons for them to run code in the spreadsheet of the same workbook I give them.
Well done...excel might be the preferred platform for executing program code if it isn't already. That is good for me considering I have made a career around the program.
Espero poder aprender mucho mas con lo.que realiza ...gracias por.compartir sus conocimiento
This is amazing. Thanks for posting this!
Can python code be written to do the kinds of things that can be done using VBA but for Excel online?
VBA is just fine - if it aint broke then dont mess with it - the final python structure is not easily readable - VBA is well suited to Xcel - it's inbuilt and right there to start using with no add in requirements or seperate packages and buttons or loading modules, all very time consuming and more things to go wrong - if microsoft ever drop VBA it will be the worst decision they ever make.
I agree. I do a lot of scripting in VBA and although it is no where near as flexible and easy to use as Visual Studio (Visual Basic) at let we have the tool within Excel to do these things. The video only helps someone that has Python experience and doesn't want to learn VBA.
If Python were integrated to Excel you wouldn't say that. After a few programs maded in Python you can notice the diferences.
@@AxelTag - Yea, well Microsoft won't change that. VBA is antiquated, but it does the job for what the user wants to do. They just need to know how to do it. Of course a Python user would prefer it in there as they are used to coding in it. I would prefer that C# was supposed, but it isn't.
Python is beautiful, VBA works well in excel and I think that MS will not drop VBA by python both can and should coexist.
But think of the thousands of libs python has and the power it can give to excel ...
Agree 100%. I managed a whole factory dried fruit intake for two seasons with VBA. Programming is situational and VBA works out of the box in the office with Office. Python of course has its place and is very powerful, I run a Kivy App that measures productivity and sends the data to an off-site server, but unless your IT department starts installing Python on all the office pc's VBA can do everything you need. There is a guy from Wise Owl tutorials that even wrote a game in VBA that runs on a Spreadsheet.
Wondering for a person not knowing much about Codes, which code is easier to learn and use specially if the main interest is creating Relational Databases and or CRMs? VBA or Python or any other .
Suscribed. Gonna curious inside the channel . Great vid, from a Python begginer.
If I built a huge library of Visual snippets. Is there any apps out there help converting from Visual to that¿
Is there a possibility to choose which one we want to use every time we enter it? I'm working with vb in collage but I would like to try working with python cause I think it gives more possibilities? Anyone knows?
why do I get ipython into excel, when a jupyter notebook can make all that and more?
i did not find a reason why need to use Python to replace VBA in this case, which scenario we are talking abut to apply this?
how to call/execute excel macro using ( Java, .Net, Python) on server-side, where ms office is not installed??
29$/month ... really? thats to much for playing arround
try LibreOffice with APSO plugin.
An absolute rip-off and hostage taking. Use xlwings. It's free.
Interesting. But I'm familiar with VBA and I think I'll stay with it. No need to use many different tools to get one thing working.
Anyways, Python users might like that way of doing things.
If I were to send my excel file which includes python code, would all the recipients also need to buy/install python platform?
Any functions will need your Python code to be able to run, and so will also need the PyXLL add-in installed. If the person you are sending the file to doesn't need to recalculate anything just view the results then they don't need anything. Check out support.pyxll.com/hc/en-gb/articles/360036822253-I-want-to-share-my-work-with-others-Do-they-need-PyXLL-and-Python- for more information about this.
@@python-excel Thanks.
So python can do the same as VBA, why is python special?
@@scottparker6869 It is only special if you already know Python and don't have time to learn VBA but wouldn't mind dropping a bit of money. The reasonable course of action would be to learn VBA tbh, but if you really can't stand it, I would understand going to any lengths to avoid it.
@@archardor3392 Python has an incredible array of powerful libraries available to it that go above and beyond what VBA is capable of doing natively. One thing I immediately thought about after this video is that I could use the AWS Wrangler library with Python and push a spreadsheet directly to the S3 layer without having to exit the spreadsheet, call the python script, then reload the spreadsheet. Your workflow can exist solely in the Excel environment. That's very powerful. Other features of Python such as numpy and scipy for statistical analysis and scripting would allow for the creation of functions that would be too unwieldy for Excel's engine to handle well. Or you could do web-scraping and dump tables directly into a worksheet. This is a neat tool.
I will give VBA priorty over python for excel use because u can record the codes even without knowing it.... We can not do recording in python we have to learn codes....
Recording should be banned. Macro recorder is responsible for a lot of garbage code.
@@krzysztofgie7936 LOL, I would agree but they can be useful for beginners staring out using the VBA interface.
And here is that other video I promised czcams.com/video/uEgJLLHBlZU/video.html
This one automatically organizes music album files into their respective and automatically created folder. And it does this for all the alubms. So amazing to see it execute and do it all automatically. Hopefully I have this walk through video for other users and anyone who is interested in Windows Automation. Let me know if you get any questinos that I can help with.
Can't wait till they have this for SAP
Incase you mean interacting with sap gui via python, there already is. Just search for SAP Gui scripting python.
python has the db connector for sap java since nw03 and python odbc, now we have the db connector for python 3.8 for hana.
I write a lot of VBA Excel code. The problem with VBA is that it doesn't run in online or smartphone versions. Will this solve this problem?
use Office Scripts for office online
Doesn't the Add-ins written in JavaScript solve that problem?
MS going to Javascript/Typescript.
Does anyone know if a similar thing is available for PowerPoint?
Great video!!!!
But I have one problem. My clients needs VBA code to execute code. I can convince him to run run it with python but he needs to install python, jupyternotebook, pyxll etc.
Is there any easy way to do this?
You can build a single installer that includes your code, Python and anything else you need. Take a look at www.pyxll.com/docs/userguide/distribution.html#building-an-installer.
@@python-excel Thank you for your reply. I will try to do the same thing. Hope it will help😀
Still do not see reason to use python instead of vba for this. Vba is installed in basically every computer, easy to use. Python just complicates things
Thank You, Sir!
I have a complex excel workbook with many macros that updates lots of cells. Everytime I run one of these macros, it takes a lot of time to finish. Is pyxll faster than vba in such scenario? Also, if I send a pyxll enabled workbook to someone, the person would have to install pyxll also? Or it goes embedded in the file? Thank you.
Hi! It depends on what is slow about your current code, but Python can be much faster than VBA in some situations, especially with numerical calculations using numpy or pandas. Other users would also need PyXLL in order to call your functions, though any calculated values would be saved in the workbook (see support.pyxll.com/hc/en-gb/articles/360036822293-How-can-I-distribute-my-PyXLL-functions-to-other-users-)
Porterero: I am going to guess that you need need to turn off auto-calculate near the beginning of your VBA module(s), and then turn it back on at the very end of the macro. That might give you better speed. Good luck.
since you said we can replace VBA with Pyxll (python), how about user form in excel which controlled using VBA, can we execute that user form response using python also? similar with VBA?
Yes, you can create user interfaces using PyXLL too. Please see www.pyxll.com/docs/userguide/ctps/index.html for details!
@@python-excel - I will check it. Thanks for sharing your knowledge.
Interesting would the file work on other pcs without any installations
Nope. And that's one of the main reasons why I still use VBA almost daily, it's super transportable. I wish MS would make it so JavaScript or something could natively run from within a file. They already allow it as a web app, but that then requires hosting and other permissions and keys from IT.
You just need to have a python interpreter installed on the machine and all the modules installed as well
I always hear this argument but people can always install extra programs, same way they would have installed Excel. Python brings more modern capabilities
the famous djx:
Yes, people can install other software if enterprise IT allows it. That is not always the case.
What are the benefits to replace VBA with Python in Excel? Better performance?
Why?
Why should i replace native VBA, a powerful, robust and yet simple tool with this?
because VBA was fine in the 90s but it's really awful now
This is so cool, thanks for sharing
I love the possibilities of PXLL but the pricing on the license is on annual basis and that becomes pretty steep at $240/ seat in a team of 10 and it stops working as soon as the licenses expire...got to make the pricing more attractive but at the same time I understand you're trying to make a living too. Great product with great use case but may be lower the pricing? Modify the licensing model? Tough call.
Does this work if the excel file is sent to someone else who does not have pyxll addin installed?
Any functions will need your Python code to be able to run, and so will also need the PyXLL add-in installed. If the person you are sending the file to doesn't need to recalculate anything just view the results then they don't need anything. Check out support.pyxll.com/hc/en-gb/articles/360036822253-I-want-to-share-my-work-with-others-Do-they-need-PyXLL-and-Python- for more information about this.
Ultimate buddy.! 😍😍😍
Why should I do this in python if I can do it easily in vba? I didn't get the answer of this question...
I use excel in windows but python (and others) in Ubuntu. I see in the video you posted that Excel appears to be running in linux. How is this possible?
Wine
Assuming this only works on Windows and not on Linux/MacOS?
What is that Qt button for?
Any chance to get this in R?
Very educational....but why use Python over VBA in a workbook?
Just looks like additional work and learning to me
Very Nice! Please do some tutorials on graphs!
Thanks! Take a look at the PyXLL docs here www.pyxll.com/docs/userguide/plotting/index.html for details of how you can use Python graphs in Excel.
Im using excel for school, If i change it to use python instead of VBA, what happens when I send the excel to the prof? Will it only work if he has the same software? Or will the excel file I sent to them just have python in it?
Good concept pyxl awesome sir
This is amazing. Thanks for posting this!
But I have a question I have huge VBA code and I want to convert it
into python -- Reason because VBA is very slow it takes time. please help me to convert this VBA to python .. need your help ..
Hi Sir, After you develop your own macro in Python how do you go about using this macro on other machines? Or your co-workers to use it as well. Thank u in advanced.
Hi! There are a few different good ways to do that. This FAQ article explains more support.pyxll.com/hc/en-gb/articles/360036822293-How-can-I-distribute-my-PyXLL-functions-to-other-users-
Your font is so small it not visible too please make font little bigger . Is it possible to do with Windows 7 ?
Thanks a lot, you're very very clear. Just a question: if I want deploy python stuff to install as a package on other pc different than mine ... what I have to do? Can you explain it with a video on this subject? Thank you again.
Hi Luigi! This FAQ article should help with that support.pyxll.com/hc/en-gb/articles/360036822293-How-can-I-distribute-my-PyXLL-functions-to-other-users-. If you need any more help with that then just reach out to our support via the website 👍
Muito bom! Obrigado por compartilhar!!
I thought I heard a few years ago a rumor that JavaScript was going to replace VBA in Excel. Python seems like a better fit.
Excel now incorporates Typescript(Javascript).
@@goombakiwi Interesting. Thanks for the clarification.
@@goombakiwi Really, does it need an add on? Just wondering if my company would allow it. My mind is racing at all the shadow IT I and many others in the company could create.
Just did a quick check and you have to have scripts switched on by an administrator. So that is a big fat NO from my company, allowing us to use typescript, Boooo 😂.
Wondering for a person not knowing much about Codes, which code is easier to learn and use specially if the main interest is creating Relational Databases and or CRMs? VBA or Python or any other ? Any chance you might have a sample file to see how it looks?
Mike Lennon:
You might want to research Access, the desktop database that refuses to die. Like Excel, it has a huge installed base of applications in many companies and a corps of devoted developers. Sort of like COBOL, except that COBOL practitioners are retiring in droves with no one to replace them.
This is beautiful...
I don't think Python will ever replace VBA in Excel. When I first studied VB in the mid 2000's VB6 came in... and the that was the last of the VBs because .NET came. VB.NEt which was very different although similar to VB6. It didn't even replace VB6. There were good arguments there that experts said that there was no urgent reason to jump from VB6 to VB.NET. Python won't be any different. Power Query adds to VBA in Excel and SQL has always been there to manipulate data with Excel(only a few knows... and those who know won't ever feel there's a need for Python in Excel). Last reason, You have to convince those who have invested in VBA since time immemorial and tell them Python would be better while I go grab my popcorn and watch. These were similar to those people who didn't even budge when VB.NET came. But great creation mate... kudos.
Does this work on macOS?
Really good. Thanks for posting.
Great video! Can you clarify what the big benefit of using Python over vba is? Is it faster to execute, or can it run i paralell etc?
I guess python provides more opportunities to interact with different platforms. Moreover if yoyr are used to python its much easier to continue instead of learning vba which could be quite uncomfortable as i think.
Concise, easy to read.
If you want to do everything inside Excel, VBA is great. But if you want to connect things to Excel, for example, getting data from web scraping, putting it into a SQL database, putting it into excel, transform data, create an interactive visual report or interactive GUI, then compared to VBA, Python is better.
TLDR; If you only care to do things inside Excel, VBA is fine. If you want to do something outside and inside Excel, Python is the way to go.
Do workbooks still have to be macro-enabled using PyXLL? Because if this can avoid macro enabling, that would add lots of security to Excel.
There's no need for the workbooks themselves to be macro enabled as the PyXLL macros run as part of the PyXLL add-in. As long as the PyXLL add-in is trusted then its macros will be enabled.
@@python-excel so I assume this wouldn't run if shared with someone else?
@@uncommonsense360 You would also need to share your Python code and the other user would need PyXLL. Please see this FAQ for more details on this support.pyxll.com/hc/en-gb/articles/360036822253-I-want-to-share-my-work-with-others-Do-they-need-PyXLL-and-Python-
Даёт ошибку в строке "xl = Dispatch('Excel.Application')". Возможно, из-за старой версии Excel (i have v2007)
I am not a programmer, but i need to run the macros.
import win32com
from win32com.client import constants
#xl=Dispatch("Excel.Application")
# вместо Dispatch лучше вызывать EnsureDispatch
xl = win32com.client.gencache.EnsureDispatch('Excel.Application')
Are there any other programming software that works like VBA? Anybody who knows?
Does this also work on Mac OS?
Do you need to have PyXLL installed to just run a python macro?
Yes, you will need to have the PyXLL add-in installed in order to run any Python code in Excel, including macros.
Is there a difference between the two languages? Is python better than VBA for excel? Im just wondering because im currently learning vba and was thinking about learning python instead. Great video btw!
Haha well that's a very good question :) I would of course say that Python is much better but I am biased! What I would say is that there is far more of an ecosystem of packages available for Python than for VBA, and Python is more 'generally' useful since you can run it both inside and outside of Excel, whereas VBA you're stuck with using it in Excel (or other Office tools). Thanks, glad you enjoyed the video :)
@@python-excel I agree with you for your preference of Python over VBA but my perception is little different, in excel VBA is very easy and I learned by my own without anyone's help but for Python we need to go for proper course to learn and more over VBA is very easy for excel we can do anything. Now I am using python just to get the live data updates, which is also available in VBA............. But still we can not compare Python and VBA both are completely different...
The loss of intellisense features is a huge drawback. Is there a work around?
VBA is extremely powerfull, but you will hardly find any libraries like you do on Python.
Nevertheless I did not discover any problem so far, that I couldn't solve by VBA. The only thing that comes on my mind is that VBA isn't able to use mulitthreading. There is a workaround, but it's not stable!
Hello excellent ! I have a question please, let's Say I want to send m'y file with my code to someone who doesn't have python install in his PC, all the calculation in my excel file with my code will work in his PC ?
Hi, thanks for your question! Yes your end user will need to have the PyXLL add-in set up, but that is not hard and can be scripted for them. This link explains more, or contact us via the PyXLL website if you need help with this support.pyxll.com/hc/en-gb/articles/360036822293-How-can-I-distribute-my-PyXLL-functions-to-other-users-
It's very interesting, but I still like VBA better, sorry. And I'm sure there is a point where VBA is not replaceable.
Wow that's awesome 😎❤️
I can't see anything you're typing in the cmd/terminal..
I'm a python web scraper and I have good grip on python, so, Should I go for VBA or continue with the python to handle excel problems?
For web scraping Python is far more capabale, IMHO. Imagine trying to reimplementbs4 in VBA!!
Why would anyone choose to use Python instead VBA?, without the VBA IDE you have to remember in memory each property and method and usage of the object model, besides to write by hand all those kilometric commands in the console. Is the same you choose to use Linux instead Windows for PRODUCTIVITY WORK.
Excelent video... explicit "qb" in the right dosage...
?are there any other languages that work with Excel... and with Jupyter notebooks... (Javascript, C#-Script, ???)
C#, VB.NET and I think C++. Can all write Add-ins, Google VSTO addins for guides on how.
JavaScript and any other web language can also be used, Microsoft have simply confusingly decided to call these simply 'Add-ins', they require web hosting and online, and appear as panes on right side of excel.
I came to Python from VBA. My opinion is if you are beginner and you need just some macros to make your life easier in Excel, you don't need Python. I miss a lot VBA debugging tools, believe me when you start, you will need them. If you are average user I don't think that you will reach VBA limits. Where Python shines are array operations, this is at least what I see for now, after my short Python experience. May be later I'll could say more.
Now what is your view can I do incredible report generation using python?
thank you for this antastic tutorial! hope you can create a similar tutorial with a real use case. e.g. a function that can scrape data from a web page and then append to existing data in am Excel file.
Many many Thanx
If I'm going to share the Excel file with other users, then obviously I need to have Pyxll add-ins installed in all their PCs as well. Don't see why I should do that.
The problem with this paradigm is that it will limit you to a the Single Apartment Threading limitation to the Excel Com Object
With PyXLL you can write multi-threaded Excel add-ins, including UDFs that run in parallel. Please see support.pyxll.com/hc/en-gb/articles/360037298034-Can-Excel-call-Python-functions-in-parallel- for details.
Python is like an improved version of Basic, so it is inevitable python would come onboard oneday.
Why bother python coding. Already macro function is there. So isn't enough to use with a mouse click. Please share the use of python🤔
I’m getting an error message when I attempt the xl.range. #The method range is actually a propert....
Python is case sensitive so you need to use xl.Range with a capital R.
@@python-excel I’m also getting an error when I try to work through the add in. Will trying to do code in two separate terminals cause an error?
Vba is faster (only if in python you don't use numpy ) , simple and accessible with no restriction in lot of company
Nice. will Phython work for Excel 365?
As I understand it Excel 365 can't do VBA macros, so I'd expect not.
Is it possible to write python code in vba?
You can write Excel macros in Python, which can then be called from VBA using the "Run" method. It's common to do this when migrating a large code base from VBA to Python as you can re-write just some parts in Python rather than having to do everything at once. This page explains more www.pyxll.com/docs/userguide/macros.html.
What version of Excel are you using?
Hi Michael! That video was made using Office 365, but PyXLL supports all version of Excel for Windows from 2003 through to the latest.
I thought Office 365 use Office Script or is VBA also available in 365?
@@CasparusBadenhorst I believe you're thinking of Office Online. Office 365 refers to the subscription version of Office, which is available for Windows, macOS and online. PyXLL requires the Windows version of Excel. This video was made using Office 365 for Windows. Hope that clears things up!
Intéressant je travail avec Excel je veux apprendre plus sur ce sujet
VBA is much much convenient. Doesn't need to install any plugins
Is Python faster?
Tyvm!!
iam getting above thing after running following code
xl=Dispatch("Excel.Application")
xl
kindly help in this
That's the COM Excel.Application object, but it's using a dynamic wrapper class (late bound) instead of a generated one (early bound). You can use it in the same way as the one in the video, or you can use "win32com.client.gencache.EnsureDispatch" instead of "win32com.client.Dispatch" to force win32com to generate the specific wrapper class. You can read more about late bound vs early bound com objects here www.icodeguru.com/WebServer/Python-Programming-on-Win32/ch12.htm.
This is fucking awesome
I prefer ExcelDNA and using C# to write custom addins - it's the dogs nuts
What’s the point of that? You’re just using the Excel API.
What would be a real life scenario (in an actual enterprise) where using Python could bring some benefits?
Honestly everything what you're allowed to do in your operating system. Not only what MS allowed you to do in vba sandbox. You can extend Excel capabilities using python's statistical or AI libraries with easy integration with Excel's data model. You can create automated, interactive, easy to read reports and so on. Yeah of course, if you want to take some data from spreadsheet or put them into, you need to use Excel's API, but in between you can utilise full python potential. Finały - as a script language python is more user friendly and flexible. You've got tons of libraries. Typing is dynamic. You've got lists, dictionaries, sets, tuples, implement objec oriented approach if you want. You can create generators, iterate via multiple stuff at once.
Thanks for answering! There is no doubt that python is way more powerful in any sense than VBA, but I still think that if you need such power for your use case, you’re much better off importing the data from the Excel sheet using pandas in a Jupiter notebook (or Google collab). If you need presentation, python has tons of libraries for that, and you can still export your output data to excel.
If you just want to do simple stuffs inside Excel, usually vba is enough.
openpyxlは？
They need a mac version of pywin32.