Will Python Kill Excel VBA?

👉 Ready to master VBA?
(Note: If the download page doesn't work then make sure to turn off any popup blockers)

Will Python Kill Excel VBA?

Python has become very popular in the last few years. It is often used with Excel instead of VBA. In this video I take a look at Python and see how it compare to VBA. The videos cover the following:

* A Brief history of Python and VBA
* What are Python and VBA used for?
* Writing Code - Python vs VBA
* Filter data with a few lines of Python
* Running Python from Excel
* 3 Simple Pros of using Visual Studio to write Code
* Installing Python applications on other machines
* Pros and Cons of Python
* Will Python Kill VBA?

#PythonExcel #PythonVBA #xlWings


Useful VBA Shortcut Keys
========================

Debugging:
Compile the code: Alt + D + C OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)

Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R

Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard)

Table of Contents:

00:00 - Introduction
00:27 - Brief history of Python and VBA
01:46 - What are Python and VBA used for?
02:34 - Coding Task - Python vs VBA
06:36 - Run Python from Excel
08:02 - 3 Simple Pros of VS Code
09:34 - Installing our Python Application
10:44 - Advantages of Python over VBA
12:01 - Will Python Kill VBA?

[lucienay]

An important advantage of VBA over Python is that user can "record" their actions and Excel will "translate" it to Macros. So user needs not build it from scratch. User needs only a bit VBA knowledge and it's sufficient enough to run it from time to time. May not sound nice to hear, but it is really a nice feature when beginners are making their applications.

[dimonovych]

1. You can write your own function in VBA and call it with "one line of code only"
2. There are extensions for VBE for additional functionality, such as grouping parts of code.
3. There is also spell check in VBE, it makes spaces after commas automatically)

I don't say Python is bad, but those advantages didn't argue me)

[logic]

I've already created so many functions/sub routines in VBA that keeps me from writing out common code that writing new subs in my workbooks is pretty simple. Plus I work in an environment where the big factor of installing python on end users computers that use my workbooks is just not reasonable for me. So no, the convenience of VBA being already part of an excel workbook I'm sharing that the user doesn't have to install a 3rd party to use is a big factor for me.

[JHatLpool]

The big advantage of Excel (+ VBA) is that it is just everywhere. With VBA, you can get started quickly and make a quick assessment of time to be spent/ importance of your proposed application. VBA and Python will live side-by-side for a few years to come.

[PiroMat]

"Company program restrictions..." That's how my adventure with VBA begins :) And it's great due to easy installation or "no installation needend".

[richardgilbert]

Python has some of the same advantages as R, but there's a lot more to Excel applications than just a little data read/filter/write. I have written apps in VBA that dynamically modified the UI in Excel, changing menu options based on queries that are based on initial user selections. The user then made additional choices from the dynamically populated lists, and those choices were used to build SQL strings, query an external DB, process the query results, and populate reports and charts in the workbook. Complicated? Well, yes, but I wouldn't have wanted to try it with Python. All the necessary code and structure was encapsulated in the saved Excel workbook without 3rd party appendages to be installed/managed. Power Query wasn't available back then, but I'd probably look at integrating that with VBA in the applications I wrote before messing with Python/Excel hybrids.

[CodingIsFun]

Great video, as always! Totally agree with your points. I personally like to use VBA & Python depending on the use case. When it comes to deploying solutions to co-workers, I usually prefer VBA.
The conversion from a Python File to a standalone executable can sometimes be a solution. Yet, in a corporate environment, mailing .exe is often not allowed.

[fischziege]

Corporate desk slave here: our part time student team member likes to work in Python and his results are great. We as an organisation rely on basic office tools, so deploying solutions as Python scripts or apps is cumbersome and we lack people who can support it. Would love to see Python being integrated in Office, would get me off my lazy behind and learn some more...

[Zavyyn]

When automating and optimizing for others, I find the user wants to open as few applications as possible, leading to preference for VBA as macro/module solutions in Excel and complex Access databases (Especially if those databases already exist). Often, if I consider doing something in Python instead of using VBA it is because it is a one-time task that will not require training somebody.

[excelrobot]

Actually, you can do the same thing as results = df.loc[df['Geography'] > 60] in VBA in one line of code if you use Ctrl-T to make your range an Excel table named "marks". VBA would be: results = Application.Filter([marks], Evaluate("marks[Geography] > 60")).

[jimclay]

I use Excel and VBA to review geochemical data sets for groundwater monitoring. Recently I have been doing data processing in Excel followed by a handoff to R for data visualization graphics. My data sets aren't big, but they are too large to simply do a visual inspection of the numbers and draw confident conclusions. This video has inspired me to take a look at Python. Thanks!

[rahulrock]

I am using both python and VBA as of now. But will stick to one which you are teaching in near future 🙂

[NZBruceman]

Thanks. Great summary. My company sells Excel tools that integrate with Oracle EBS, mostly in the US. We have employed VBA now since Excel 97 and there are up to 50, 000 lines of code in some of the tools. Never underestimate the reluctance of IT to install stuff on their networks/PCs. Having VBA available without an install has been an incredibly important advantage. We have new C# based software creating an Addin and the install is a new barrier we have to fight to overcome, every single time. VBA will never die. Look what happened to Microsoft when they dropped it in Office 10 for Apple! They were burned.

[alanhood]

For me it was, " Is it worth learning VBA, to do something that python can do just as well (or better), when I want to learn python anyway". You convinced me, I'm doing it in python!

[pshamrock]

I use both. Now that I've learned about the excel wings, I'll use both a lot more.

[simonghislain]

I had 3 moments during which I was like "yeah but then you would need to do this" and then you immediately talk about it. This shows your great mastery on the topic. I completely agree with your conclusions. Thank you for this video !

[krisvantuerenhout]

Once Python will be installed on as many machines as Office, it will take over i think. For IT people for sure. For NON-IT people i don't think so (not yet). Recording a macro is so easy for the majority of tasks you want to automate. Far from optimized code, but it gets the job done.

[Thunderin]

Very interesting video. But... As a VBA die-hard who has been making a living out of it for almost two decades, I have to shout out loud that VBA is here to stay! Maybe Python has some advantages when it comes to analyzing data but VBA is used for much more than analyzing data. It is used most commonly to automate Excel and other Office software. It was not referenced in the video how easy / useful it is for Python to automate Excel environment objects for instance (e.g. charts, shapes, pivot tables and list objects). This debate "Will Python Kill VBA" reminds me how in Excel forums between 2010 to 2012 people strongly argued that Excel would disappear soon because Google Sheets is potentially a much more superior product to Excel. Well reality proved it otherwise. Today Excel is by far the most superior spreadsheet product without any competition whatsoever in sight.

[JLPT-AIsensei]

My company puts hard restrictions on any software that is excluded from the acceptable software list which is provided by IT Dep, so that if I (maybe anyone other than me) want to use python instead of VBA then I need to take some approval from IT department (very very hard to get it). Even MS Access is not accepted for my department, and MS Office Suite only contains 4 applications: Excel, Word, Powerpoint, Outlook.
So in my opinion, Excel VBA is the best stuff for data manipulation in office environment. I totally agree with Paul.

[moekyawthu]

I leraned a lot from u. Thanks a lot.
Now I use Excel VBA for details process in Excel. Python for other general things.