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

Dave should I learn Python and Excel or VBA this is a common question that I get asked via direct messages on LinkedIn and via email now if you're familiar with any of my YouTube content my answer to this question might surprise you however before I can get into my answer I need to make sure that we're level set in terms of understanding what Python and Excel and VBA are for so first up what I'm going to do is do a quick overview of VBA and then I'll do a quick overview of python and Excel if you're already familiar with VBA go ahead and skip it head find the video Chapter below and Skip ahead to the python and Excel section and if you're not familiar with VBA buckle up because we're off to excel here I am in trusty old Microsoft Excel and we're going to talk about VBA or Visual Basic for applications VBA is a programming language embedded in Excel it's one of many programming languages for example there's the M programming language that you use with power query there's Dax there's Office script there's a whole bunch of them what makes VBA different is it's the granddaddy it's been in Excel for I don't know how long the first time I got into First Time I Ever Saw VBA code was probably 20 years ago and the point of VBA back then and now is to automate Microsoft Excel if you want to think about it in this way VBA allows you to build applications on top of excel as a platform and that's a bit abstract so let me show you some of the things that VBA can do now this is a very contrived example so if you're a VBA expert please don't flame me in the comments we're just trying to build an intuition here for example many things are going on in C right I can click on all kinds of stuff I can click on this column I can do things like add a worksheet so I can add VBA code to listen for events like adding a new worksheet and then do something in response to that event so if I click on this little guy right here I'm going to go ahead and invoke that VBA Automation and what you can see here is that I've done a couple things first up I've set it up so that anytime a new sheet is added to this workbook it is automatically added to the end of the worksheet list and I also pop up a a message box that says don't forget to rename the worksheet because this is a pet peeve of mine people don't rename their worksheets they don't rename their tables they don't actually move the worksheets around anyway it's pet peeve of mine so I wrote a little automation to do this now if I click okay notice that every single time I add a new worksheet this is what's going to happen same thing so let's go ahead and take a look at the VBA code here so this is the VBA code so this VBA code right here says look I am listening for an event where a new worksheet is added to the workbook and hey Excel whenever that happens fire off this code run this code for me and you can see here I'm moving the worksheet to the end of the workbook and then I'm popping the message box and this is the essence of what VBA is for there are many many events inside of Microsoft Excel and you can write VBA code to listen for all of them and then do stuff in response to those events and you can do all kinds of things you can create new worksheets you can create tables you can overwrite cells with values you can do anything that you would like so essentially this allows Excel as I said to be an application development plan platform with VBA let's say you're a small business you could Implement invoicing accounts payable accounts receivable all in a robust way using VBA you would use VBA code to make sure that the data is clean and that all the processes are followed and all that kind of thing so VBA is super powerful but it goes beyond that you can also use VBA to write your own functions your own functionality because VBA is a general purpose programming language for example I can create a function like this called calculate profit and this code it will be available everywhere in my workbook I can use it in my formulas just like a normal outof thebox Excel function it'll show right up in the list with everything else this is super powerful so I mean this is kind of a contrived example again but imagine if you will you could write VBA code to implement various types of analytics like a machine learning algorithm or a cluster analysis you could certainly write code to do that I wouldn't advise you to do that because there's a better way Python and Excel but it is possible so this is our level set VBA is all about making Excel essentially a application platform so you can automate it you can control it you can interact with it all using Code now that's actually quite a bit different than Python and Excel so let's talk about what Python and Excel actually is and what it's for the best way to understand what Python and Excel is for is to understand the design limitations that Microsoft put on the feature the first of which as you can see here from Microsoft's documentation is highlighted Python and Excel code does not run locally on your computer which VBA code does Python and Excel code actually runs in the Microsoft cloud so literally what happens is is that your data is packaged up along with your python code pushed up to the Microsoft cloud it's run there and the results are returned back and here's the reason why Microsoft did this Microsoft completely controls VBA it's been around for a really really long time they have they have all the source code for it it's their product python is completely different python is this large open source ecosystem and Microsoft needs to provide some sort of assurance to its customers that Python and Excel is secure and the only way to do that is to have it run in the Microsoft cloud at least at the time of this recording anyway they might fix that later on but right now it's the cheapest and easiest way for Microsoft to achieve this goal so look what they're doing here they're saying look you're running in the cloud on a secure container so they control all the code you're using a curated set of secured libraries provided by Anaconda your python code does not have access to your computer your devices or your account that includes your hard drive it does not have network access does not have access to your user token so on and so forth so they're locking python down they have to quite frankly and this is a good thing I'll explain why in a second now moving on here's what's really super important python code does not have access to other properties in the workbook such as formulas charts pivot tables macros or VBA code so what this is trying to tell you is look everything that VBA can do for example listening to new worksheet events and then run code and response to those events cannot be done with python and Excel Python and Excel kind of sits in this like isolated Block in if you want to think of it this way inside of your workbook it can't do the things that VBA can do by Design now the next thing that we need to understand then is okay well how does python interact with the Excel environment if it can't talk to the network and it can't talk to the workbook and it can't talk to VBA code and so on and so forth and that is via the new Excel function so the Excel function supports the ability for you to essentially push data into Python and Excel which then gets sent up to the cloud run and then back down and the Excel function is limited to what it can Source data from it can Source data from ranges so for example these cells in this particular worksheet from tables and from Power query connections this is super important Python and Excel can interact with your workbook the only way that you can interact with it is via the new Excel function and you can only pass it in data so that tells you exactly what Python and Excel is for it's for data analysis it's not for automating your workbooks that's what VBA is for python in Excel is designed to allow you to have access to Advanced analytics and data visualization techniques and statistical functions and all that kind of stuff that just don't come without of the Box Excel sure maybe you could buy an addin or something like that but for most users that's not realistic in my experience Python and Excel grants you all the power of python in a controlled environment that makes it as easy as possible for your it Department to say yes you can have access to Python and Excel it runs in the cloud it's secure we've edited it's all good you don't have to install python locally on your machine we wouldn't like that so Python and Excel is the easiest way for you to actually do real world Advanced analytics and data science in Microsoft Excel all right with that background I can now answer the question hey Dave should I learn Python and Excel or VBA and the answer is for the vast majority of excel users neither okay circle of trust time between you and me think about it 700 plus million Microsoft Excel users worldwide what percentage of them actually need to build automations using VBA it's a tiny percentage let's also be even more honest out of all those 700 plus million Excel users how many of them are really doing analytics really analyzing data also a small percentage the vast majority of Microsoft Excel users do what are known as descriptive analytics they do reporting what happened last week last month last quarter last year not that that's not important it certainly is Executives need to know that sort of thing for example but actually analyzing data going well beyond pivot tables and charts not very many that's why VBA and Python and Excel are for a small select group of Microsoft Excel users and if you fall into that group that needs more power than what out of the box Microsoft Excel might provide in terms of like pivot charts and that sort of thing then you have to ask yourself this am I going to Delight my stakeholders and managers by automating processes in Excel so that we have faster response times faster throughput better quality that sort of thing if the answer is yes then VBA is your thing now if it's more around creating new data and insights then what is possible using traditional outof thebox Excel functionality then Python and Excel is the ticket for you and my channel is really dedicated to folks in the second category not the first I don't have any content about VBA and I never plan on having any content about VBA however all of my content almost all of it is about analytics in some shape or form so for example I have a video showing how you can use Python and Excel to combine cluster analysis and a predictive machine learning model to derive new insights if you're interested in that sort of thing I'll put a card up here you can click on it and you can watch that video and that just shows you what Python and Excel provides you okay time to switch gears that's it for this video you've got the answer to the question my next video in this Python and Excel series is going to talk about whether or not you should learn SQL SQL or structured query language the language of databases or you should learn power query for Python and Excel because at a certain level they do the same thing but which one you should learn is actually highly dependent on your situation so when that video is ready it'll show up here on the screen as a tile and until then I'll put one of my other Python and Excel videos in the place just in case you want to watch something that you might find interesting all right until next time please stay healthy and happy day to slothing

Channel: David Langer

Views: 6,954

Keywords: pythoninexcel, python excel, python in excel, python for excel, pythonforexcel, pythonexcel, excel vba, vba, python vs vba, python or vba

Id: fH-_O9_SHqo

Length: 10min 4sec (604 seconds)

Published: Wed Mar 06 2024