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.

--------------------------------------------------------------------------------------------
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:

💻 Tuning Decision Trees with Python:

💻 Logistic Regression with Python:

💻 Cluster Analysis with Python:

#pythoninexcel #pythonexcel #pythonforexcel

[spongebobby]

Ok...this is the first video on python that made any sense to me! Well done on your style of teaching.

[stevewolfe]

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.

[peterk]

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

[engineeringinspectionsirel]

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

[PS-zwgi]

I am stopping half way through your video to tell you that the way you communicate this information in a conversational manner is PURE GOLD. Now onto the rest of this video and others that you have produced 😇

[shabchique]

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

[dngdrnf]

Thank you for that information- excellent!

[ElegantSolutions]

Excellent! Very clearly explained. Thanks

[tadmarshall]

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.

[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).

[peterwooldridge]

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

[richardcarlin]

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.

[KeithJBrett]

Great video. Very informative to me with no coding/programming experience.

[michaelt]

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.

[geerliglecluse]

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.

[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

[jhkim]

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.

[Jay-fnxt]

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

[japethstevens]

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?

[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.