Python in Excel vs. VBA - What You Should Learn in 2024!

Don't waste time! Learning Python in Excel or VBA should match your career goals. Python in Excel and VBA are powerful but address very different scenarios for Microsoft Excel users.
In this video, I discuss choosing the right option to achieve your goals.
☕ If you found this content useful and would like to support the channel, you can buy me a coffee: www.buymeacoffee.com/DaveOnData
--------------------------------------------------------------------------------------------
VIDEO CHAPTERS
--------------------------------------------------------------------------------------------
00:00 Intro
00:37 VBA Overview
04:16 Python in Excel Overview
07:33 The Question is Answered
--------------------------------------------------------------------------------------------
FREE MACHINE LEARNING CRASH COURSES
--------------------------------------------------------------------------------------------
If you're ready to see what Python in Excel skills make possible, be sure to check out my FREE on-demand machine learning crash courses!
Here's what you get with any of these courses:
1️⃣ On-Demand: Watch the course when it fits your schedule
2️⃣ PDF of all slides
3️⃣ All Python code as a Jupyter Notebook
4️⃣ All data so you can experiment
5️⃣ Resources to continue your learning.
Machine learning with Python in Excel. You've got this if you want it:
💻 Decision Trees with Python:
bit.ly/DecisionTreesWithPython
💻 Tuning Decision Trees with Python:
bit.ly/TuningDecisionTreesWit...
💻 Logistic Regression with Python:
bit.ly/LogisticRegressionWith...
💻 Cluster Analysis with Python:
bit.ly/ClusterAnalysisWithPython
#pythoninexcel #pythonexcel #pythonforexcel

[@DaveOnData]

Stuck waiting on Microsoft to grant you access to Python in Excel? Don't wait! Check out my video and start building skills NOW: kzbin.info/www/bejne/Zpq5ZXmqpbiln7s

[@phandongexcel]

I like this video ❤❤❤

[@peterk6215]

Thanks David. Been wondering about this question for a while. I now know the answer.

[@DaveOnData]

My pleasure! I hope you found the content useful.

[@engineeringinspectionsirel1385]

Can't wait for more. Excellent again, so glad I found you 😀

[@DaveOnData]

Thank you!

[@shabchique7149]

Thank you Dave for the this great video with clear explanation. Love the shirt!

[@DaveOnData]

Glad you liked the video and the t-shirt! 😁

[@stevewolfe6096]

Well spoken David. I grew up with Excel’s own automation language in the 90s - it could do a lot but was a pain to use. I was pleased when Excel VBA came in as I had some familiarity with it in MS Access. I have always maintained that a major problem “power” Excel users have is trying to use Excel as a database application. Although it has improved over the years it still does very little on its own to enforce data and calculation integrity as many companies and individuals (including my late brother) found out to their chagrin and $ loss.

[@DaveOnData]

Thank you for taking the time to share your experiences. Any thoughts on Power Pivot as a database substitute?

[@michaelt312]

Nice insight. I'm heavily in the PQ house. But watching your videos on Python has opened up some possibilities in my little brain. Thanks as always.

[@DaveOnData]

Wow! Thank you for taking the time to write these words - they are much appreciated. As I will discuss in my next video, I'm a huge fan of PQ in the right situations. In others, technologies like SQL and Python in Excel are the ticket.

[@michaelt312]

@@DaveOnData, for me PQ has one advantage of being available going back to Excel 2010. Also a lot of hospitals don't allow Macros. But following you for Python and dipping my toes further into SQL. One of the hospitals here in Chicago I work the most with has Office 2016, very limited access to SQL and PBI. They allow one Director one folder so I can do somethings in VBA for her. But I agree with you.

[@user-dn5gd1rn9f]

Thank you for that information- excellent!

[@DaveOnData]

You are welcome. I hope you find my future videos useful as well.

[@ElegantSolutions]

Excellent! Very clearly explained. Thanks

[@DaveOnData]

Thank you! I appreciate the kind words and I am glad you found the video useful.

[@anonymoususer4356]

Excellent explanation. Thank you!

[@DaveOnData]

You are welcome! Glad you found the video useful.

[@tadmarshall2739]

Nice! I'd never heard of Python In Excel and you pretty well told me what I need to know. I've written some VBA for Excel and for me it was all about writing scripts that automated long sequences of steps I'd been doing by hand and finding tedious. It's an annoying programming language if you come from any of the "real" programming languages but usable if you need it.

[@DaveOnData]

Excellent! Glad that you found the video's content useful.

[@tomoleusz]

Great video, I have just subscribed your channel. Thank you for expanding the differences between Python run in Excel and on PC. I wanted to use Python on my company PC for some automations, however I don't feel comfortable with it having access to everything (as a beginner I might make some mistakes resulting in unexpected outcome). Therefore I will limit its use to safe boundaries within Excel and try to look for automations in Power Automate/Apps (not big fan of VBA).

[@DaveOnData]

@tomoleusz - Thank you for taking the time to leave a comment and I am glad to hear you have found my content useful!

[@YouAreTheRaidBoss]

Great video! Thank you! Subbed! :)

[@DaveOnData]

You are welcome! Glad you enjoyed the video.

[@Ugk871]

Thanks for the info

[@DaveOnData]

You are welcome! Unfortunately, Microsoft hasn't done a good job of making it clear how Python in Excel adds value to Microsoft Excel.

[@whosestone]

Python for the data analysis, VBA for controlling getting input data in environments that don't connect Data Verse or other outside data sources. (Think ETL actions across SharePoint/OneDrive locations.) _WS

[@mjawadmirza]

Very well explained.

[@DaveOnData]

Thank you for the feedback, it is greatly appreciated!

[@peterwooldridge7285]

Very good. Your "700 million users.. " etc etc., sum the question in a nutshell. Cheers and thanks

[@DaveOnData]

Thank you! I am glad you enjoyed the video.

[@nikhilgoyal007]

thanks very much!

[@DaveOnData]

You are welcome! Glad you enjoyed the video.

[@richardcarlin1332]

Best to use Python to externally perform data analysis and to then create spreadsheets. From within python, you can run macros from within Excel. You can also pass formulas to Excel as well. VBA is best used when you are already in Excel and want to run automation.

[@DaveOnData]

Many professionals don't have the option of running a local Python install. Python in Excel can be an easy way for these professionals to unlock advanced analytics.

[@jhkim156]

Excellent. Thank you for the explanation. I have one more question. Is it possible to insert or execute Python formulas in a cell using VBA? If this is possible, it seems like additional automation could be achieved.

[@DaveOnData]

I checked with my contact at Microsoft and here's the response: Yup, you just write out the formula using range.formula = “=PY(, )” In beta, you currently have to have users click the try preview first otherwise it might throw an exception

[@mcwahaab]

Hi David, thank you for this. I have a different question for you. Between using Python in Excel and Power Query, which one would you recommend? If possible, would you create a video explaining the differences and benefits of each option? Thank you in advance.

[@DaveOnData]

@mcwahaab - I cover your question in this video: kzbin.info/www/bejne/qH7NpZapoq2Zb8k

[@mcwahaab]

@@DaveOnData - Thanks a lot. Appreciate

[@KriegAdler09]

But… I thought I had recently read that Microsoft is no longer supporting VBA. And are there not existing Python libraries that can “read” or “write to” Excel files? (Kinda a noob here, but wanting to add to my skill set and seeing how Python might help - my college “programming” was in MATLAB and Fortran77 (digital compiler), and I took a bit of Python3 during the early months of the pandemic. I essentially work as a mechanical product engineer.)

[@DaveOnData]

Great questions! First, regardless of Microsoft trying to migrate folks off VBA (e.g., to Office Script), there is a huge installed base of VBA code and VBA developers. It's not going anywhere anytime soon. Second, programming languages like R and Python have libraries for reading in, manipulating, and writing out Microsoft Excel files. However, this isn't always an option for many Excel users for two primary reasons: 1- They don't want to install and maintain a programming language on their laptop. Python, in particular, is a pain in this regard. 2- If they wanted to do this, they couldn't because of restrictions put in place by their IT department. Many of my clients are excited by Python in Excel because it provides a quick and easy path to advanced analytics within Excel.

[@jasongins]

@DaveOnData what's your take on anaconda cloud notebook as an option for people with IT department restrictions on a local install?

[@DaveOnData]

@jasongins - In general, I'm a big fan of Anaconda. For example, I use Anaconda in my Python courses at TDWI conferences. That being said, I'm not super familiar with Anaconda Cloud. If I put on my former Enterprise Architect hat, the things I would be curious about the costs and security.

[@geerliglecluse5297]

Python in Excel as implemented by Microsoft does not satisfy data privacy requirements in Europe as required by GDPR regulations. As shown in this video, Microsoft only promises "not to persist data in the Microsoft Cloud". That does not preclude MS from copying your data or metadata to one of their non-cloud servers, etc. Not surprising, MS has a reputation for dragging its feet when it comes to complying with (data) privacy regulations instead of toeing the line as they should.

[@DaveOnData]

A fair criticism! We shall see how Microsoft will address (or not) what you mention here.

[@RedShiftGalaxy]

@@DaveOnData Meanwhile All of us could store/share our most valuable resource, private and/or company data stored in our Excel workbooks and databases over Power Query connections with Microsoft over Microsoft Cloud.

[@DaveOnData]

@RedShiftGalaxy - Do you mean to say that organizations should never use cloud providers?

[@geerliglecluse5297]

@@DaveOnData Depends on the exact use and the nature of data stored in the external cloud on the one hand, and how well the cloud service provider complies with GDPR regulations on the other hand.

[@RedShiftGalaxy]

@@DaveOnData Sometimes some companies intentionaly for some of their data use local/private datastores like local databases and/or local excels and/or send part of their data/knowledge in form of excels exclusivly to well known parties.

[@dontown-lb5ke]

My 1st ver. was ver. 5 (1993) which included VBA for 1st time. It came on 11 floppy discs.

[@DaveOnData]

Yes! I remember the days when I would install Office using 30+ 3.5" floppy disks. 🤣

[@santoshgujar5237]

Sir, 🙏🏼 i dont know much but i think python helps to process data very fast✨

[@DaveOnData]

It does, indeed! Although "fast" is a relative term. You may find my video interesting. It demonstrates importing 330,000 rows of data into Python in Excel: kzbin.info/www/bejne/ZnqmdWywgrKtkJY

[@santoshgujar5237]

@@DaveOnData ✨🙏🏼✨

[@user-rr7yi3ru2p]

Hello! Could Excel 2023 with the addition of Python and Copilot recognize the data type and approximate it correctly, and then find the transition points from one type of pattern to another?

[@DaveOnData]

Based on my experience, this is possible. The caveat would be that crafting the right prompt(s) can be tricky depending on the data.

[@user-rr7yi3ru2p]

@@DaveOnData I have several formulas that I use, can neural network combine them into one and output them as one formula?

[@DaveOnData]

Deep neural networks can learn very complex relationships from data. The trick is that they need a lot of examples from which to learn. So it is possible in theory.

[@Jay-fn1xt]

@Daveondata for a novice data analyst, do you recommend learning VBA or Python+Excel given the other tools available?

[@DaveOnData]

This is my opinion. Please take it as one perspective among many. I'm going to make an assumption that Microsoft Excel is your data analysis tool of choice. As a novice Excel Data Analyst, it is unlikely that you will need VBA anytime soon - maybe never. Python in Excel is really for more advanced analytics, so I would first focus on the fundamentals using out-of-the-box features. For example, my exploratory data analysis (EDA) with Excel tutorial series: kzbin.info/aero/PLTJTBoU5HOCRFQhfU1gg2ciNpS_evWKR7

[@japethstevens8473]

TBH, you shouldn't use Excel as a database! That was declared years ago by the VBA project manager, Joel Spolsky. (VBA was introduced to Excel - before being migrated to other Office apps - in 1993. Blimey, that dates me!) Nothing stopping you using Excel as a front end to large data. It's only worthwhile to use Python if you've got a large volume of data and that should naturally reside in separate DBMS, so you would be writing dedicated Python apps against THAT data. Sounds like a 'Me too' token gesture. Anyone done a latency test against, say, the max rowcount of a wide worksheet uploaded to Python?

[@DaveOnData]

Thanks for the comment! I would humbly offer the following for consideration: 1 - I 100% agree that Microsoft Excel shouldn't be used as a database. However, I've been in tech for a long time and know that Excel will always be used in suboptimal ways. It's not that Excel is an inherently bad tool. It's just that Excel is very flexible and ubiquitous, making it ripe to be the proverbial hammer in search of a nail. 2 - Performing advanced analytics on "Excel-sized" data is nothing new. For example, the books of Dr. Wayne Winston has taught Excel users for years how to conduct logistic regression and market basket analyses using Solver. Python in Excel is arguably a logical addition to Excel to allow users a better alternative to hand-rolling analyses using Solver. Check out my KZbin video comparing Solver to Python in Excel for logistic regression: kzbin.info/www/bejne/m5y3ZXeuZaljp7s 3 - While it doesn't use a wide dataset, I have a KZbin video showing the performance of Python in Excel using 330,000 rows of data that might interest you: kzbin.info/www/bejne/ZnqmdWywgrKtkJY

[@peterxxl1244]

Learn VBA! This language has been developed specifically for Office application development, while Python has only recently been "forced" into Office because this seems to be a new advertising strategy of the Microsoft masterminds. Python is not more powerful than VBA in anything, apart from the function libraries (written in other languages) that Python accesses; if NumPy or Panda were made directly available for Office, there would simply be no reason to use Python in Office. Learning VBA is also a good idea because VB (even if no longer developed by MS) is an excellent bridge language to Xojo, B4X and Mercury - an investment in the future.

[@DaveOnData]

Thank you for offering your perspective! What do you think about Python in Excel for helping users perform analytics?

[@Ing_Luis_Quintero]

NumPy and Panda are Python, you can't say there is no reason to use Python in Office xD

[@VastCNC]

What about Office Scripts? I think that’s the new VBA that Microsoft is pushing users towards.

[@DaveOnData]

Office Scripts is where Microsoft wants people to go. That being said, there is a large installed base of VBA code/users. In my experience, VBA isn't going anywhere anytime soon.

[@brodriguez11000]

@@DaveOnData Being just 365 hurts it as well.

[@japethstevens8473]

It's a glorified version of the code recorder, which has been in Office products for years. The recording facility was removed from Powerpoint some years ago because processing the actions became too difficult to code! So maybe that's MS' thinking about future Office products. Nothing you can't do in VBA - even making calls to the .Net and Windows libraries if you need lower level control.

[@DaveOnData]

I respectfully disagree with this. Python in Excel does not compete with VBA or Office Scripts in any way. It is designed specifically to enable data analyses that are difficult or impossible to do with out-of-the-box Excel. For example, you could code up a random forest algorithm in VBA, but why would you do that when Python in Excel gives you access with just a few lines of code? Also, Python in Excel addresses a huge shortcoming in doing analytics with Excel - having to hand-roll worksheet templates using Solver. I have a video that discusses this: kzbin.info/www/bejne/m5y3ZXeuZaljp7s

[@strayferal]

Thank you for the explanation. I don't like they that the data flies to the cloud. It should stay local. Anyway. I'm not gonna use it in Excel.

[@DaveOnData]

This is a complaint I frequently hear, and I understand it. There are also many organizations invested in Azure and they see the ease of use as a big win over maintaining local Python installations.

[@strayferal]

@@DaveOnData yes - I totally understand this is for the Azure users. It basically integrates with Azure, so from that point of view it makes perfect sense. However most users won't use Azure. And since most users also won't be into Data Engineering/Analysis, it does not make sense for them. Still - the news titles that "Python is available in Excel" come misleading, as people understand that it would be available as VBA and would do similar things and they think they could use it to a degree. Also providing a full-blown Python in Excel would be a total security compromise, but hey isn't VBA a security risk too? I remember the 2000s era when you would get each week at least one email with attached word/excel file with some VBA malware. So what. I guess solution is to still - limit the Python, therefore maybe provide a Python fork, with MS Office-specific functions, but all of it to function locally. Then MS would get more people into MS Office customizations. If anyone really needs it. Maybe the Libre Office folks would get this idea faster than MS guys and provide this for us, as sometimes it would be significantly faster to provide a Spreadsheet file + attached script for a task, instead to code a GUI from scratch and provide custom functionality... No idea how many would need it, but guess there is a market for such things.

[@DaveOnData]

I would offer this for consideration. In my experience, most Excel users do not immediately associate Python in Excel with VBA/Excel automation. In fact, my experience has been most Excel users don't really know what the point of Python in Excel is. 🤣 The best way I've found to explain Python in Excel is that it allows for advanced analytics that used to be hand-rolled using Solver or just weren't possible unless you purchased an Excel Add-in.

[@kyoujinko]

When Python for Excel came out, I looked into it and just didnt see the use of it. I use Python for Data Analytics using the pandas library and tend to only use Excel just for data output for people who would never understand any programming, they just want to see the end result. So I fail to see Python for Excel as a use to anyone, Especially if for example you extract data from SQL, are you wanting to upload your servers credentials to Microsoft ??? If you can use Python your just use Python otherwise use power query within Excel, Python for Excel just doesnt fit in any situation.

[@DaveOnData]

If I may be so bold, I would offer the following for consideration: 1 - Python in Excel uses a Power Query connection as an external data source. The authentication to the data source happens locally. 2 - Excel users have been conducting advanced analytics for many years. For example, using the Solver to implement logistic regression. Python in Excel offers a better way for these users to conduct data analyses. I have a video comparing Solver vs statsmodels for linear regression: kzbin.info/www/bejne/m5y3ZXeuZaljp7s 3 - Python in Excel offers a very low barrier to entry for Excel users. Regarding #3, I have clients that are highly interested in Python in Excel due to their IT departments preventing local Python installations. However, these clients are already using Azure and they see this as very easy way to unlock advanced analytics in their work. It is worthy to note that these clients are not IT folks (e.g., developers), but are data-savvy business professionals.

[@morelearnmore]

The python works on excel sheet, how to work

[@DaveOnData]

I'm not tracking your question. Can you elaborate?

[@guocity]

vba on large dataset is too slow

[@DaveOnData]

That is always a consideration with Excel - make sure it will scale to the size of your data. One great thing about Python in Excel is that it provides a smooth path to scale. For example, moving to Jupyter Notebooks if needed.

[@portlyoldman]

The answer is definitely C# 😁

[@DaveOnData]

Ha! Written a fair amount of C# code in my day. It's my 2nd favorite language for software engineering behind C++.

[@andrewlankford9634]

Any halfway decent language replaces VBA.

[@DaveOnData]

Any suggestions for automating within Excel that IT will commonly allow? Office Script?

[@ThepExcel]

so... the proper question is... Office Script vs VBA M Code vs Python Excel DAX vs Python Excel

[@DaveOnData]

Agreed! I would also add (which will be the subject of my next video): SQL vs Power Query/M

[@DaveOnData]

Oh, and here are a couple more: Solver vs Python in Excel Analysis ToolPak vs Python in Excel

[@slezakmichal]

Dave your T-shirts are great - I also love Iron Maiden….but recently listing Saxon.. My experience with vba is, that this is great tool for connecting with data bases and providing data from there. Ofcourse with little help of SQL. Now PQ cope with that but pure sql is more efficient (you can adjust your sql query using date, or other parameters from spreadsheet - in PQ is more difficult cause of safety issues). Regards and waiting for a new episode 😊

[@DaveOnData]

Saxon! I haven't listened to that band in a very long time. I had a copy of "Crusader" on tape in the 1980s. 😁 My next video, out next Wednesday, will explore the tradeoffs between pure Power Query and pure SQL for Python in Excel.

[@brodriguez11000]

@@DaveOnData Interesting video considering MS access comes with a 365 subscription and easily interfacing with each other.

[@stanislasgrare5046]

the thing is that microsoft does NOT control python. imagine large companies starts using it and in 10 years there is ok we have python 4 or something which is NOT compatible with python 2 (like python 2-3) for me i’m ok but imagine billion dollars banks…. microsoft must control the programming language -> office script but well it’s fat far far behind VBA honestly. with vba alone you can do many things but office script you may need to use power automate aswell which makes everything a big mess

[@DaveOnData]

Wouldn't any organization that uses Python for data analysis (e.g., Jupyter Notebooks) face the same risk?

[@_mikeusa]

VBA is also used for interactive controls, for instance form buttons to add an entry to a worksheet. Unfortunately, Microsoft does not support VBA for iOS devices, which means a lot of Office automation is not available on that platform. iPhones and iPads have grown in the business world. So people are starting to abandon Microsoft as their goto one-stop-shop.

[@DaveOnData]

Interesting perspective! Personally, I'm not seeing this as all of my clients are Microsoft shops. In particular, my SMB clients are heavy Microsoft Excel users on Windows.

[@giorgiobarchiesi5003]

Generally speaking, Python is the new javascript. A scripting language with strong ambitions to become a programming language. As a professional developer, I’m not interested, and this is what I tell KZbin everytime it suggests a video about Python: “not interested”. But it keeps on suggesting… there must be a bug. Perhaps KZbin is written in Python. 😂

[@DaveOnData]

Such a refreshing take on the popularity of Python! What's your favorite language for software engineering? Mine is C++. Unfortunately, I haven't needed to use it for many years.

[@giorgioripani8469]

Python IS a programming language. Much easier than Java, sometimes at the same speed of Java, but with a huge number of libraries and framework for web apps, data science, numerical anaylisis, simulation and so on and so forth.

[@artil1]

are you high?

[@giorgiobarchiesi5003]

@@DaveOnData Most of my customers tell me what language I must use. Just in case I might retire or die, I suppose. So I actually use a dozen of languages, including Python. My favorite languages are those that you can rely upon when developing big projects, so they must provide type-safety and null-safety, as is the case with C# or Dart, but also C++ or Java; which one I prefer in a specific case depends on the application. As far as Excel is concerned, sure Python would be better than VBA. But if the code size increases too much, perhaps Excel itself is not the right tool. Thanks for the video, I hope my first comment didn’t seem impolite.

[@KhoaNguyen-fs6to]

Be hornest. Python performance is bad.

[@DaveOnData]

Compared to what? Performance is always relative

[@vishwanathnb128]

Compared to C++

[@DaveOnData]

Ah, C++ is my favorite software engineering language! To be honest, as much as I love C++, the speed of languages like R and Python really doesn't matter for analytics.