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.
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)
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.
Your right, python is powerful in data Analytic but can't be compared with vba that was built specifically for Microsoft software. vba is obviously more powerful in excel again python cannot replace it completely
@@wisetank135 It seems to me Microsoft is trying to push all to the cloud. M365.No Vba there, not sure about python. All hype about power apps, power automate and powerFX as underlying logic facilitator. On the other hand it is still very 'raw' and more bot, teams orientated Just my impression. Big companies will be 'nudged' there. Nevermind 'success' of sharepoint that tried that few years ago
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.
So true. I work in a worldwide operating company and they allow no extra software here. Even Visual studio is forbidden, I have checked today. If you request an exception, you are personally responsible for the security of the software. I will never sign that.
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...
Rather I want to see python integrated everywhere, not only in office products, just BTW! You should kill your lazyness and start as soon as possible with python, caused by python is an allrounder. It's amazingly powerful and can be used for AI as well! Beste Grüße 🤟😎
@@biskutnation 2 words. Monthly billings. Honesty if i were to start a business I would seek of a way to to it exclusively with open-source. If the company gets big, private software will cost you millions per month.
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.
I agree completely. Data analysis is secondary for what we use Excel at work. Charts, pivot tables, regular tables, userforms, custom functions, events,... the list goes on.
We all know it: in most companies, you are considered the (only) „Excel-freak“ in the team if you mention your VBA-skills. Some people even still don‘t know what VBA is. My Boss, Professor/Director of a big science institute, never heard if it. That are the people you have to design your xlsm-files for in many cases. For some of them, it‘s a challenge to make those small adjustments on their Excel to run all of those files. Now we shall ask them to set their systems up to enable Python-code in Excel? Haha, good joke. I would change right away, if MS would implement Python to MS Office. But since that will never happen…
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")).
Nice - I know there are many ways to filter the worksheet data. Filter and Evaluate functions tend to be slow with a lot of data. But what I was demonstrating here was to filter an array rather than the worksheet data. Arrays are very fast to use in VBA but sadly lacking in functionality.
@@Excelmacromastery Since FILTER is one of the new dynamic array functions, it is blazing fast in Office 365. I just did a test with 200,000 records returning 14,000 records in the filtered set and it was pretty much instant (
@@excelrobot Hey, I just tried your suggestion and it gives me an error2015. I have a table named "Stammdaten" with a column "Provisionsanteil". My code looks like this: Sub NeuerFilter() Dim results As Variant results = Application.Filter(["Stammdaten"], Evaluate("Stammdaten[Provisionsanteil]>3")) End Sub Is there something I did wrong?
In fact like many coding langage, there as highly specialized libraries / routines that one need to learn to get things done. I am not sure the syntax is really what makes the difference.
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.
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.
My company blocks mailing .exe as well, so I get around it by zipping the file, then zipping the zip with a password. If the mailing server can't see the contents then it allows it through.
Most end users are not allowed to install software. Only approved software may be loaded onto machines, usually there is a committee that approves the software that gets on the machines....and it can take YYEEAARRSS for anything to get approved....there are some companies I know who still maintain everyone should be using IE and Chrome / Edge is completely banned. These companies are few and far between now but it is usually the case the larger the company, the slower change will be. The only thing that looks poised to kill VBA is the adoption rate of teams based Office 365 thanks to the fludemic.
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.
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!
Python will never replace VBA. There are too much cases when you cannot install new software (due to IT policy, security rules, etc.) but excel and VBA exist on almost each PC with Windows
Python ALONE would not replace VBA perhaps. Its the entire cloud or hybridize cloud would remove the feasibility of running codes on PCs. For medium to large companies, using excel is a decrease in productivity. You get data from server, manipulate it then analyze. Why not do it on the source (server-side)? For small companies, cloud equivalents or even Google Sheet can do without installing anything. Most company can survive without Excel tbh (our do). As a bonus of server-side deployments, you can run these in any OS flavor you want.
@@boschmitch I'm agree at the point of using cloud tools, but there are a lot of cases when excel/vba still useful in big companies. We use VBA to automate a lot of processes in SAP which could take a lot of time to do it manually and too expensive to handle it with ABAP-developing.
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!
One thing was missed out: Big Data and Machine Learning and connection to database-servers. VBA is quite useless for ML or big data analysis in databases, while Python offers many libraries for machine-learning, computer-vision, simulations and even 3D-data visualisation.
yes - but ML is big data not real learning. Show an amoeba enough pictures of a cat and it can do the task. ML is interesting but it's not learning or intelligence or with the whole world networked the singularity would have blown us away by now.
@@Excelmacromastery That's really another strength of Python. The amount of quality learning material, especially for getting started with Python - Corey Schaefers channel is my favorite. For Excel - well there is this channel :-) which is awesome, but in general there is not that much out the for improving VBA skills.
@@TheMcHonky this 100℅!! I'd love to master vba but can't find enough decent materials. I've a number of books but they more show you what you can do with vba rather than teaching it. Whereas Crash course python is just an amazing introduction to python
@@danielcook1271 I learn by doing. I suggest you try this way too. Think of a problem, real or imaginary, in Excel. Then begin writing code for solving it. Whenever you are stuck, search the internet for the solution. You will spend a lot of time in the searching and testing out which solution works best, but this experience will prove very useful.
To use VBA with Excel all you need is just Excel: no additional IDE (+1), no additional components (+1) - totally +2. Using Python for Excel: additional IDE & components - totally -2. This is very important in case you deploy your project to any customer (inside your company or outside it) - no additional prerequisites and maintenance fees. I don't see any reason to replace one scripting language with 2 plusses with another one with 2 minuses. Actually, higher performance of Python with Excel could give it a huge plus. But author don't take it in a count, because, highly likely, there is no such a difference in their execution performance. So, Python or VBA - is just a matter of a personal preferences (except if your boss is a fan of Python :-)).
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 !
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.
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.
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.
Both are fine for me it depends on what you need to do. Pandas is a great tool that’s being said excel can be seen also as a ready to go graphical interface. whereas in Python you also need to create it … so my dream would be to get a more dedicated Python integration with excel and MS office that would be so great …
I've written VBA for some time. My experience shows that some companies are already apprehensive about allowing employees to use the available VBA. I had one employer disable access to macros, until they realized our ADI's were dependent on them. Staff are generally not hired for programming skills and the push to construct workbooks for basic excel users will continue to be prevalent. With increased cloud usage and SharePoint; MS appears to be focusing on Typescript(Javascript). The prospect of bringing on an enterprise application for Python; is not promising. The recent ransomware news might further diminish employee access to automation features.
Thanks for sharing. You make some interesting points. TypeScript has still a long way to go to compete with VBA and it's not clear if that is Microsoft's aim.
Thx Great video. The problem with python is that commercial organization refused to install open software because it is not supported. In terms of efforts - rewards I think that python is much more better than VBA Excel. I think with a few hours of coding python will allow you to do much more than VBA Code.
no disrespect for Python lovers, but I hope they leave MS Office and VBA alone. VBA is accessible and offers a way to implement easy solutions for users no w/no elevated permissions needed.
@@norpriest521 I can't agree with that at all. It's much more intuitive than C, LUA or Python. To the point where I don't understand why we even use C style syntax anymore.
@@norpriest521 Fully agree. As you can see from the example in this video: import pandas as pd df = pd.read_excel(myXlsFile, sheet=mySheet) dfx = df[df['Result']>60] pd.write_excel(newXlsFile, sheet=mySheet) Can you do it with VBA? Yes, but... how long does it take? How easy to read and maintain is your code? And that was a trivial example, imagine a real world situation (I deal with this kind of stuff on daily basis). The more complicated your task, the more disadvantageous it becomes to rely to what whas released by Microsoft in the 90's and remained unchanged since then. VBA will have been a top-notch technologies at the end of the last century, but now it is falling behind. Microsoft should really deal with it. Either they come up with something new (and I know they are developing something similar to PowerQUery language to make it work with Excel), or they'd rather adopt something new, and Python could be a very good candidate.
I started using python because the tables of data I was working on were 2.5 - 3 million rows. That's a massive benefit python has over excel. I found the learning curve to be far easier than VBA - python is much more intuitive. For installation purposes, the Anaconda environment is an easy starting point for new python users. Numpy handles calculations on large arrays effortlessly, so enriching data is simple. I still use excel, but I now always do the heavy lifting with python.
You can use Power Query and Power Pivot to work with millions of rows effortless. They're a bit harder to master, but already built in Excel, which has several benefits.
Thanks a lot, really interesting. I would love to see a video where you compare power query with python. I would also like to see some real live cases where Excel reaches its limits and Python can take over..
Simple example (not real python fan yet): Python can read 2GB csv file with millions of rows. Excel will load 1mln 48k rows and cut off the rest or throw error....
@@macedoniojr In reality if you are doing that type of work in Excel then you are wasting your time. A relational database can handle that data a lot quicker and with more efficient results. You can use Excel as a frontend and have the database do the grunt work.
Python looks great, but VBA comes integrated in Office. Your conclusion does a great job of laying out why many people, including myself, won't be switching to Python any time soon. I create things for other people to use, it can't require them to set up Python and then fail when they move to a new machine, or re-install Office.
I didn't know Pandas was able to read and write in Excel files, that's good news for me. I tried to include an add-on for python application in Excel in the past (don't recall which one), but it let the program quite slow, so I gave up trying to use them together. I'll rethink about this possibility in future soon. Thanks for the video!
In a spreadsheet scenario Python can be simpler to learn algorithm and deal with Big Datas, but VBA it is not as bad as people say, It is just need a good update. By the way, in an office environment if you aim for automations and optimizations, mainly for daily tasks and for other people to use, then go VBA. It is way easier to create a simple, useful and visual application and send to other people.
VBA isn't easier, it's just allready integrated, whereas in python, you currently have to connect to Excel from the outside (and you don't get autocompletion because of that)
Thank you for the great video, always great content. 2 questions: 1) isn’t VBA much faster to run than Python for Excel programs? 2) Also, doesn’t VBA give us much wider library of Excel objects (such as conditional formatting), at least so far? I build enterprise VBA apps and I’ve felt that coding in Python (and JavaScript also) is inferior for speed and control, though I could be wrong. Wud appreciate your opinion.
Those Excel Objects you're talking about have nothing to do with VBA but with the Excel API-Object. The thing is, VBA is fully integrated in Excel with its own editor that can access the API-Object and thus the Excel-library, whereas in python, you need to connect to it from the Outside. But that would (hopefully) change, if MS were to implement Python in their Office products. But I think even more important would be an Update to the VBE-Editor...that thing is just a PAIN in the nuts to work with..
The only reason why python has’t replaced VBA completely is that too many people have been using VBA and it works sufficiently for many problems. It’s the same kind of people that have opposed against computers bc they feel uncomfortable with it for being something new. The video underlines the inferiority of VBA. If companies focused less on short-term success, python would be the standard today.
Well for the examples you showed here, you don't really need VBA at all. You can simply use IF, MATCH, VLOOKUP to extract data, you can use conditional formatting and then there is the option to record VBA. In many cases for data analysis, you don't need VBA at all. The functions are so powerful. If you compared pivot tables to python as another example you can simply build a pivot table in minutes with no VBA compared to doing the same in python, python cannot compare.
Excellent video! I work with a team that develops tools in Excel and Access. VBA and SQL are king (and queen, I guess?). There has been some discussion about using Python, but it becomes an issue of support. I am the only one on the team with any Python experience and if I were to leave, they would be forced to backfill with someone who has Python experience. The elephant in the room is that Python does not come preinstalled on Windows machines like it does on Mac machines. This is a huge problem and some of the comments here have already mentioned this. Until that happens, if ever, I think you will see adoption of Python as an alternative to VBA be painfully slow - at least within companies that use VBA like mine does.
can you make a vba code that open the browser and go to a page reads a captcha fill it in a text box in the web page and press enter and the code should work at a certain time
You make excellent and convincing points for why Python is important and could (perhaps, should?) be part of any extensive Excel development. However, my employer doesn't want me to install or use anything outside of the Office environment. They have several reasons, none of which are essential to this discussion. The bottom line is that any development work I do for my employer must be done with whatever installs with Microsoft Office and works inside the Office environment. That means I must use VBA and not Python.
Shocked to see if VBA is still around. Must b nice working for bigger companies. Never seen it in the small niche companies I been working with over the years
My workplace uses excel extensively. And we had developed so many VBA applications over 5-6 years. Once we switched to python, an entire new world was unlocked. We have used async, pandas, django, openpyxl, sqlalchemy, pysimplegui and so many other libraries in python which makes our developers life so much easier. Yes VBA definitely has its benefits and there are some shortcomings in python, but the benefits of python massively outweigh the shortcomings, atleast for my workplace. Also most of the comments mention about installing python for end-user, but we have never done that. We use pyinstaller to generate executable codes that needs no installation for end-user. And it runs on any pc just as it does on my computer, without having to install anything for others.
Rather than Microsoft purchasing Python, why don’t they simply incorporate the best of Python into VBA? The steep learning curve, time-consuming organizational software installation, user privileges, IT regulations...Python is simply too expensive as an organizational tool (for now). The code is beautiful, though-this was my first glimpse of it!
Great video. Right now python is my favorite language. I'm still a newbie, but its actually pretty amazing to see the things you can do with it. I'm trying to get to where programming in python is just natural.
I used to work with vba due to installation restriction. Later on i get to work for excel reporting from unix server with built-in python. I get green light from the IT security to install pandas offline and it really replace a lot of manual spreadsheet process.
Intresting vid - I'm a VBA programmer, among other languages, so i've got most of this pretty quickly. I think the main take away point is Python integration, which I think may get rid of VBA (eventually). Otherwise it's just a library. Not only that I can shell out to exe files i've created in another language, or write to an ini set of instructions then do the same. Ultimately Python (in context of VBA) is little more than another library, and there's a lot of libraries, including access to the windows API where I think a lot of power lieth. I'm interested enough to take this seriously though especially as I think everyone will need Python eventually. Moreover - if VBA is rich Italian, Python and others are barked military instructions in a very accurate manner, a far more limited lingo for arguably greater function. VBA programmers may lament the fact they've learned zillions of commands! I'd also be interested to know about the advanced "big data" applications. VBA can do everything in this video and greater. So i'm guessing that if you import 2 million cells and start zooming over them in Python, then VBA may be a frozen spreadsheet and the other may be a quick but two step process then a populated spreadsheet. Cheers for the vid.
I don't think TypeScript is set to be a replacement for VBA. Microsoft have said "VBA isn't going anywhere" and TypeScript only works online and with Power Automate at this time.
It is easy to build some classes in VBA to do what you show with Python. You didn't show that Python is better than VBA but you said that someone can find Libaries (to import) in Python for better work.
And similar libraries exist for VBA. Realistically, they are pretty equal. The only benefits shown are use of VSCode, which you can also use to code VBA :)
I totally agree with you. VBA is part of Excel and those of us who have used it for many years, as you said, we already have written codes that will not work if VBA is eliminated
4:26 wait a minute, you just have to supply the sheet to it? You don’t have to specify a region? What if there’s an unrelated table off on the right of this one? When you tell python to print the 3rd line, will it print the results of the grades table, a bunch of blank spaces, and print the same line of the other table?
For the work I am doing VBA is simple and gets the job done. Having to install Python would result in having to setup another tool. For now I will stick with VBA.
hello from Brazil !!!! Thank you for amazing videos that you posted. It's a hard decision for MS to integrate the phyton to VBA, because they created the typesript, but... It's a question of time, I think...
I'll agree with the other commenters, the biggest advantage of VBA is distribution. Users have Excel and are familiar with it. No additional installs are required. They access one file and it just works. I would consider Python or something else if there weren't distribution challenges associated with them, but embedding VBA in our controlled documents is a no brainer.
To read an Excel range into an array, this statement is already enough: marks = shmark.Range("A1").CurrentRegion Given the variable "marks" is a variant, when you assign a 2-dimensional range to a variant, this variable will automatically be a 2-dimensional array. That's enough. You need not write codes to copy a cell of the range to an element of an array one-by-one like other traditional programming languages. That's completely redundant. Although you already have a bias against VBA, in making a comparison, please be fair to it (and to Python too). Also, I would like to point out that, in Python you CANNOT declare variables, whereas in VBA you NEED NOT declare variables. So in making a comparison, make sure you don't declare variables in VBA too in order to make it fairer.
I felt that the example used in this video could have easily been performed using formulae rather than vba which is what I do these days because vba requires troublesome signatures from our ICT security department on my company laptop. The main reason I have used vba was if I needed to execute event based calculations or actions which formulae can't. In fact, because vba is quite inefficient in some aspect of interchanging data, I tend to use a worksheet as a form of registry to keep the last known dataset rather than keep it as a variable in code. My question to this longwinded comment is whether the Python add in is capable of these. Thanks.
Fantastic video! I'm currently in the process of automating not only excel spreadsheets but also web and file operations at the same time. Granted, you can do all of this with Excel and relevant reference libraries, but it seems Python would be more robust and flexible. Most probably I'll be using both in conjunction :) I hope VBA will remain popular forever as I like this coding language very much. Thank you for putting all of the essential information in such a concise yet very informative clip!
Nice video. I'm a heavy user of excel and I'm not very good at VBA, but i learning it. However, this video has got me thinking about switching to Python. The disadvantage is as you stated, installing the application on my work laptop
My opinion: I think that a more realistic scenario is desktop applications like excel being gradually replaced by their online versions. Excel Online is getting better and better and I think that Office Scripts (that runs only on Excel online) will eventually replace VBA because there will be no need to have the Excel Desktop installed anymore.... but this would take some time of course...maybe 5-10 years ....
The advantage of VBA is that company departments can create something "quick" and "dirty" without need of oversight from IT. For example, if I am a manager and want to report on turn-around-time metrics to show my boss how well my team is performing, I can quickly put together a GUI using MS Access/Excel and write some code in VBA to do it. I think the above reason is why Microsoft has decided to keep VBA; the use-cases for it remain too valuable to discount!
Hi Paul. Thanks for the view of Python with EXCEL. I've tinkered with it in a small way, but VBA (and Power Query) can currently handle the things I need to automate. Probably, that is true for many people. But, you never can discount an alternate way of getting work done. So, worth keeping an eye on the options (Python, Office Script, etc.). As always, thanks for your interesting perspectives and insights. Thumbs up!!
You can download the code(both Python and VBA) from the description below the video. *Quick note* on the VBA Array code in the video that many have commented on. I am aware that I can read the row directly from the range. What I was showing is this: Accessing ranges directly in VBA is extremely slow and gets exponentially slower if you are dealing with large data. That's why I use arrays. You can write from a range to an array in one line of code. It is fast and efficient. Reading through arrays is also very fast and unlike ranges the performance remains consistent as the size grows(see ruclips.net/video/JzALsdQvjr8/видео.html) The problem is that once your data is in the array there is no sort, filter, slice etc. functionality. You have to write your own code to do this. (That's why I created an Array Class - see ruclips.net/video/ZYxa-Q-nSX0/видео.html). In Python and Typescript(and most modern languages) it is trivial to do this. I see this as a big problem in VBA if you are dealing with large datasets. A new user writes code and it is running extremely slow. They either conclude VBA is slow and give up or they discover that using arrays will make the code much faster. Once they switch to an array they have now lost the range functionality that allows filtering, slicing rows/columns, sorting etc. They then have to write the code to do this or find it somewhere.
there is no sorting and filtering in python either, it's in pandas library. what stops me from writing pandas for VBA and then claiming it's advantage of VBA. not trying to argue against or for either of the languages, just pointing out that often claimed advantage of python is libraries and those are really not part of the language. a real thing to consider in world of office apps (spreadsheets, small db) is ease of use and overall performance, and VBA wins, and even survives it's owner's intention to kill it.
I would say there is easier code in VBA to achieve what you chose as an example. May be the example chosen was wrong. No doubt that Python is much more stronger than VBA, in many aspects, including the stability of the code and application as well. One thing you may not have considered is the easiness to use. For a small repetitive data manipulation/formatting, even a non-programmer could do it with the help of macro recording and power query in excel while it needs some learning to do the same in Python. My verdict is VBA will remain for some more time and even if it is killed, may not be because of Python.
There is one single reason Microsoft won't replace VBA with Python: it can't be easily run on the cloud version of Office. They are instead trying to replace VBA with Typescript. Personally, while I love Visual Studio Code, Python and its interactivity, I would never exchange the comfort of having the VBA code embedded in the Excel file. Having Python inside of Office would be great, but in my humble opinion, Microsoft should instead update VBA and the editor.
I don't see how can Python be recorded like VBA. VBA is not only for Excel. Many people has used it for Word, PowerPoint and most importantly Microsoft Access. I don't think people can create a form in Access then click a button to run Python code but VBA,
Before VBA was XL4 macros, and they are still supported after being replaced by VBA almost 30 years ago. Even if Python or TypeScript replaces VBA, it will live on for another 30 years from now.
You don’t need to loop through the cells to put the values of a range into an array in Excel. Just set the variable equal to the Value property of the range. The reverse works for saving back to the sheet as well.
@@Excelmacromastery It may seem like you looped through an array, but you actually looped through a variant Range named "marks" because CurrentRegion returns a range not an array. You can reduce your 11 lines of code to just 6 by just setting your variant "row" to shMarks.Range("A1").CurrentRegion.Rows(2).Value. The Value property of a Range object returns an array that you can set directly to a variable without needing to Redim the variant. The same approach works for saving data back to a range on the sheet as long as the range is the same size as the array. Again, no looping necessary in either case.
@@excelrobot Yes, it's a good point. The only code that is a little verbose in VBA is the writing to the console (print). That is something that is unlikely to occur outside of testing too. If you were outputting to a cell, for example, you could simply use: Range("L2").Resize(UBound(row,1),UBound(row,2)) = row so you would really only have three lines of code for normal case operations, which wouldn't be a huge time saving if you moved to Python. In this case of course, I'm sure there are many more uses!
You can use PowerShell to access Excel/Office via Com Objects, and it's found everywhere Windows is installed. I'd like to see Office natively support development in PowerShell given it's easier to write than VBA and also a Microsoft technology.
Interesting video. But the for the analysis the current Microsoft solution is PowerBI, and the client - PowerBI desktop. For the Worksheet interaction, the main VBA power is the ability to record the macro(that is missing in Python)
Thank you for this video. It's a "food for thought" now......VBA is classic. Python is not that specifically explained and applied. It looks like an old MS DOS screen to be honest. The question from me is how much data manipulation Python requires. Excel has capability of combining VBA with SQL and Power Query, as well as creating models in Power Pivot for Power BI.....
I think the biggest advantage VBA has is the ability to share it to other colleague, imagine you in a coorperate company, and u want to make certain task as aumated, if using python, you either need to ask them to install python themselve or install the exe that you make, for excel you can just create and share, no setup whatsoever from the user
I'm about 2 years into VBA and feel like I'm pretty adept with it, but learning a real coding language like Python scares me. Are they significantly different? It feels like VBA is a clumsier coding language with less flexibility to do what you want it to do.
It's all about the right tool. . One common thing between Data Science, MIS & Business Analyst is "Data Analysis". . MIS & BI doesn't deal with huge ammount of data or they don't too much focus on data. So Excel, SQL & BI software like Power BI or, Tableau is enough for them. They focus on business solutions rather than data. . In other hand Data Science people focus on data too much. They do machine learning & so on so forth. For them Python is best.
This was really useful, thanks! I'm one of those people who makes macros in a large organisation. While I'm sure everyone could install Python on their laptops, it's just not practical to get them to do it and maintain it. Also, my code resources and knowledge are so mature now, I'm just too invested to make a change worthwhile. I'd also have to use a mix of Python and VBA to do things like formatting and emails.
@@Excelmacromastery After talking to a colleague, I think it should be seriously asked if Alteryx will kill VBA in the near future. I'm seriously afraid of Low/No Code solutions taking the spotlight soon.
OK I have no idea what you are doing in that first VBA example but literally all you have to do is this: 1. format the table as a table and give it a sensible name, I named my "data". (you don't even have to do this) then do this For Each r In Range("data").Rows(1).Cells Debug.Print r.Value Next r 3 rows, thats it. Or am I missing something here? I haven't tested the speed, this approach might be slower but I usually don't work with such huge data sets that speed becomes an issue for me.
I am a heavy Office user and I would not use Phyton to simplify my daily job tasks. First and foremost because programming is not my background and VBA is so easy to use and the API is built in all of the Office applications. I can share my Modules and Functions with co-workers and they don't need to know Python either. No additional software is needed to start programming in VBA.
VBA gives you the ability to troubleshoot the running code line by line with F8, breaks points and dragging the resume point at will. This is really powerful, especially when you're dealing with 100's of lines of code in one module.
@@Malignvs I am a beginner at python too, but had a lot of experience with VBA. VS code can do debugging similar to VBA, ONLY if you add breakpoints to the lines of your code where you want to pause. Then you press F5 to proceed from one breakpoint to another. You can add the variable you want to check by putting it into the WATCH list.
@@Malignvs Debugging Python with VSCode is pretty similar to doing it in VBA! You can see all the local variables, you get an immediate window, etc, plus lots of nice extras like updated locals being highlighted and seeing the values in the code itself.
@@johncheung4905 @M Taylor Thanks for the explanations! Still, it looks like you can't go line by line (which helps tremendously) without inserting the break points yourself. However those break points should suffice in most cases. :) I need to familiarize myself with VSCode. :)
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.
You are more than correct.
Another advantage of VBA and Python is that you should not touch these, at all, ever.
@@morgadoapi4431 why?
I'm working as VBA developer since last 4 years and honestly I'm feeling that now this language is becoming obsolete. :(
@@luvlycan I would even say it's obsolete since 6 years.
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)
Exactlty!! X2
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.
Thanks for sharing! That is a factor for many people.
Your right, python is powerful in data Analytic but can't be compared with vba that was built specifically for Microsoft software. vba is obviously more powerful in excel again python cannot replace it completely
@@rushrush6754 Same here.
@@Excelmacromastery is Microsoft releasing a MS version of python like script code in excel?
@@wisetank135 It seems to me Microsoft is trying to push all to the cloud. M365.No Vba there, not sure about python. All hype about power apps, power automate and powerFX as underlying logic facilitator.
On the other hand it is still very 'raw' and more bot, teams orientated
Just my impression. Big companies will be 'nudged' there. Nevermind 'success' of sharepoint that tried that few years ago
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.
No need to install additional tools. I can be sitting in a meeting and start to code.
"Company program restrictions..." That's how my adventure with VBA begins :) And it's great due to easy installation or "no installation needend".
Same here
Exactly. Develop and throw away
This
So true. I work in a worldwide operating company and they allow no extra software here. Even Visual studio is forbidden, I have checked today.
If you request an exception, you are personally responsible for the security of the software. I will never sign that.
unfortunately also.
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...
This is the single reason VBA is still so widely used. I develop in VBA for this exact reason.
Rather I want to see python integrated everywhere, not only in office products, just BTW! You should kill your lazyness and start as soon as possible with python, caused by python is an allrounder. It's amazingly powerful and can be used for AI as well!
Beste Grüße 🤟😎
Office script will be available as an alternative.
@@biskutnation 2 words. Monthly billings. Honesty if i were to start a business I would seek of a way to to it exclusively with open-source. If the company gets big, private software will cost you millions per month.
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.
I agree completely. Data analysis is secondary for what we use Excel at work. Charts, pivot tables, regular tables, userforms, custom functions, events,... the list goes on.
I use Excel VBA to automate web pages. But now I am stuck with the dead of Internet Explorer....
We all know it: in most companies, you are considered the (only) „Excel-freak“ in the team if you mention your VBA-skills. Some people even still don‘t know what VBA is. My Boss, Professor/Director of a big science institute, never heard if it. That are the people you have to design your xlsm-files for in many cases. For some of them, it‘s a challenge to make those small adjustments on their Excel to run all of those files. Now we shall ask them to set their systems up to enable Python-code in Excel? Haha, good joke. I would change right away, if MS would implement Python to MS Office. But since that will never happen…
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")).
Nice - I know there are many ways to filter the worksheet data. Filter and Evaluate functions tend to be slow with a lot of data.
But what I was demonstrating here was to filter an array rather than the worksheet data. Arrays are very fast to use in VBA but sadly lacking in functionality.
@@Excelmacromastery Since FILTER is one of the new dynamic array functions, it is blazing fast in Office 365. I just did a test with 200,000 records returning 14,000 records in the filtered set and it was pretty much instant (
@@excelrobot Hey, I just tried your suggestion and it gives me an error2015. I have a table named "Stammdaten" with a column "Provisionsanteil". My code looks like this:
Sub NeuerFilter()
Dim results As Variant
results = Application.Filter(["Stammdaten"], Evaluate("Stammdaten[Provisionsanteil]>3"))
End Sub
Is there something I did wrong?
@@KaiWeissmann Try [Stammdaten] instead of [“Stammdaten”].
In fact like many coding langage, there as highly specialized libraries / routines that one need to learn to get things done.
I am not sure the syntax is really what makes the difference.
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.
Can you teach me VBA. With real uses.
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.
Thanks for sharing your experience.
Yes but you can put the executable on a shared drive and run it from there
My company blocks mailing .exe as well, so I get around it by zipping the file, then zipping the zip with a password. If the mailing server can't see the contents then it allows it through.
@@PeterGeras cool tip!!!
Most end users are not allowed to install software. Only approved software may be loaded onto machines, usually there is a committee that approves the software that gets on the machines....and it can take YYEEAARRSS for anything to get approved....there are some companies I know who still maintain everyone should be using IE and Chrome / Edge is completely banned. These companies are few and far between now but it is usually the case the larger the company, the slower change will be. The only thing that looks poised to kill VBA is the adoption rate of teams based Office 365 thanks to the fludemic.
Not for malware. VBA is still really handy for executing code on unsuspecting victims via macros.
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.
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!
⁸
Wise choice
Python will never replace VBA. There are too much cases when you cannot install new software (due to IT policy, security rules, etc.) but excel and VBA exist on almost each PC with Windows
согласен!
True
Python ALONE would not replace VBA perhaps. Its the entire cloud or hybridize cloud would remove the feasibility of running codes on PCs. For medium to large companies, using excel is a decrease in productivity. You get data from server, manipulate it then analyze. Why not do it on the source (server-side)? For small companies, cloud equivalents or even Google Sheet can do without installing anything. Most company can survive without Excel tbh (our do). As a bonus of server-side deployments, you can run these in any OS flavor you want.
@@boschmitch I'm agree at the point of using cloud tools, but there are a lot of cases when excel/vba still useful in big companies. We use VBA to automate a lot of processes in SAP which could take a lot of time to do it manually and too expensive to handle it with ABAP-developing.
I am using both python and VBA as of now. But will stick to one which you are teaching in near future 🙂
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!
I use both. Now that I've learned about the excel wings, I'll use both a lot more.
Exactly the video I needed
Glad you like it.
Hello father
One thing was missed out: Big Data and Machine Learning and connection to database-servers.
VBA is quite useless for ML or big data analysis in databases, while Python offers many libraries for machine-learning, computer-vision, simulations and even 3D-data visualisation.
yes - but ML is big data not real learning. Show an amoeba enough pictures of a cat and it can do the task. ML is interesting but it's not learning or intelligence or with the whole world networked the singularity would have blown us away by now.
I would love to see your video where you teach Python!
I may do more in the future.
@@Excelmacromastery That's really another strength of Python. The amount of quality learning material, especially for getting started with Python - Corey Schaefers channel is my favorite. For Excel - well there is this channel :-) which is awesome, but in general there is not that much out the for improving VBA skills.
@@TheMcHonky this 100℅!! I'd love to master vba but can't find enough decent materials. I've a number of books but they more show you what you can do with vba rather than teaching it.
Whereas Crash course python is just an amazing introduction to python
@@Excelmacromastery Please do. Your explanation is superb.
@@danielcook1271 I learn by doing. I suggest you try this way too. Think of a problem, real or imaginary, in Excel. Then begin writing code for solving it. Whenever you are stuck, search the internet for the solution. You will spend a lot of time in the searching and testing out which solution works best, but this experience will prove very useful.
I personally feel that it is easier to use python with excel files. Many people don't realise it...
To use VBA with Excel all you need is just Excel: no additional IDE (+1), no additional components (+1) - totally +2. Using Python for Excel: additional IDE & components - totally -2. This is very important in case you deploy your project to any customer (inside your company or outside it) - no additional prerequisites and maintenance fees. I don't see any reason to replace one scripting language with 2 plusses with another one with 2 minuses. Actually, higher performance of Python with Excel could give it a huge plus. But author don't take it in a count, because, highly likely, there is no such a difference in their execution performance. So, Python or VBA - is just a matter of a personal preferences (except if your boss is a fan of Python :-)).
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 !
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.
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.
I leraned a lot from u. Thanks a lot.
Now I use Excel VBA for details process in Excel. Python for other general things.
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.
Thanks for shasring Bruce.
Both are fine for me it depends on what you need to do. Pandas is a great tool that’s being said excel can be seen also as a ready to go graphical interface. whereas in Python you also need to create it … so my dream would be to get a more dedicated Python integration with excel and MS office that would be so great …
dream coming true...
I've written VBA for some time. My experience shows that some companies are already apprehensive about allowing employees to use the available VBA. I had one employer disable access to macros, until they realized our ADI's were dependent on them. Staff are generally not hired for programming skills and the push to construct workbooks for basic excel users will continue to be prevalent.
With increased cloud usage and SharePoint; MS appears to be focusing on Typescript(Javascript). The prospect of bringing on an enterprise application for Python; is not promising. The recent ransomware news might further diminish employee access to automation features.
Thanks for sharing. You make some interesting points. TypeScript has still a long way to go to compete with VBA and it's not clear if that is Microsoft's aim.
Another excellent video from one of the very best around, thank you so much. So funny because yesterday I finally decided to start learning Python.
Thx Great video. The problem with python is that commercial organization refused to install open software because it is not supported. In terms of efforts - rewards I think that python is much more better than VBA Excel. I think with a few hours of coding python will allow you to do much more than VBA Code.
I think they know python as snake rather than a programing language😁
no disrespect for Python lovers, but I hope they leave MS Office and VBA alone. VBA is accessible and offers a way to implement easy solutions for users no w/no elevated permissions needed.
Nice point, I hadn't thought of that.
@@logic3686
Unfortunately, VBA language is not so intuitive to me when it comes to writing it.
Really bad designed language
@@norpriest521 I can't agree with that at all. It's much more intuitive than C, LUA or Python. To the point where I don't understand why we even use C style syntax anymore.
I think VBA will be available for a while but they are moving to JavaScript/Typescript in Office365
@@norpriest521 Fully agree. As you can see from the example in this video:
import pandas as pd
df = pd.read_excel(myXlsFile, sheet=mySheet)
dfx = df[df['Result']>60]
pd.write_excel(newXlsFile, sheet=mySheet)
Can you do it with VBA? Yes, but... how long does it take? How easy to read and maintain is your code? And that was a trivial example, imagine a real world situation (I deal with this kind of stuff on daily basis). The more complicated your task, the more disadvantageous it becomes to rely to what whas released by Microsoft in the 90's and remained unchanged since then.
VBA will have been a top-notch technologies at the end of the last century, but now it is falling behind. Microsoft should really deal with it. Either they come up with something new (and I know they are developing something similar to PowerQUery language to make it work with Excel), or they'd rather adopt something new, and Python could be a very good candidate.
I started using python because the tables of data I was working on were 2.5 - 3 million rows. That's a massive benefit python has over excel. I found the learning curve to be far easier than VBA - python is much more intuitive. For installation purposes, the Anaconda environment is an easy starting point for new python users. Numpy handles calculations on large arrays effortlessly, so enriching data is simple. I still use excel, but I now always do the heavy lifting with python.
You can use Power Query and Power Pivot to work with millions of rows effortless. They're a bit harder to master, but already built in Excel, which has several benefits.
Please start teaching us to Python... Thanks P.Kelly
Thanks a lot, really interesting. I would love to see a video where you compare power query with python. I would also like to see some real live cases where Excel reaches its limits and Python can take over..
Great suggestion!
Time series analysis. For instance how do you make a rolling covariance matrix? Doing it with excel will cause a dimensionality nightmare
Simple example (not real python fan yet): Python can read 2GB csv file with millions of rows. Excel will load 1mln 48k rows and cut off the rest or throw error....
@@macedoniojr with power query in Excel this is not an issue
@@macedoniojr In reality if you are doing that type of work in Excel then you are wasting your time. A relational database can handle that data a lot quicker and with more efficient results. You can use Excel as a frontend and have the database do the grunt work.
Great video
Please more videos on Python with Excel.
”Python is so powerful and can does all this with just one row”
Well yeah that’s how functions work. Sorta cheating if you call a function “one row”
Python looks great, but VBA comes integrated in Office. Your conclusion does a great job of laying out why many people, including myself, won't be switching to Python any time soon. I create things for other people to use, it can't require them to set up Python and then fail when they move to a new machine, or re-install Office.
Excellent video. Thanks for breaking this down. Was curious what the pros and cons of both were and this answered my questions!
I didn't know Pandas was able to read and write in Excel files, that's good news for me. I tried to include an add-on for python application in Excel in the past (don't recall which one), but it let the program quite slow, so I gave up trying to use them together. I'll rethink about this possibility in future soon. Thanks for the video!
Brief and concise is your explanation in a single short video . Thanks
In a spreadsheet scenario Python can be simpler to learn algorithm and deal with Big Datas, but VBA it is not as bad as people say, It is just need a good update. By the way, in an office environment if you aim for automations and optimizations, mainly for daily tasks and for other people to use, then go VBA. It is way easier to create a simple, useful and visual application and send to other people.
That's for sharing your experience Matteus.
VBA isn't easier, it's just allready integrated,
whereas in python, you currently have to connect to Excel from the outside
(and you don't get autocompletion because of that)
Thank you for the great video, always great content. 2 questions: 1) isn’t VBA much faster to run than Python for Excel programs? 2) Also, doesn’t VBA give us much wider library of Excel objects (such as conditional formatting), at least so far?
I build enterprise VBA apps and I’ve felt that coding in Python (and JavaScript also) is inferior for speed and control, though I could be wrong. Wud appreciate your opinion.
Those Excel Objects you're talking about have nothing to do with VBA but with the Excel API-Object.
The thing is, VBA is fully integrated in Excel with its own editor that can access the API-Object and thus the Excel-library,
whereas in python, you need to connect to it from the Outside.
But that would (hopefully) change, if MS were to implement Python in their Office products.
But I think even more important would be an Update to the VBE-Editor...that thing is just a PAIN in the nuts to work with..
The only reason why python has’t replaced VBA completely is that too many people have been using VBA and it works sufficiently for many problems. It’s the same kind of people that have opposed against computers bc they feel uncomfortable with it for being something new. The video underlines the inferiority of VBA. If companies focused less on short-term success, python would be the standard today.
Well for the examples you showed here, you don't really need VBA at all. You can simply use IF, MATCH, VLOOKUP to extract data, you can use conditional formatting and then there is the option to record VBA. In many cases for data analysis, you don't need VBA at all. The functions are so powerful. If you compared pivot tables to python as another example you can simply build a pivot table in minutes with no VBA compared to doing the same in python, python cannot compare.
Nothing can kill VBA...
Microsoft is working on replacing it with typescript know as Office Scripts
@@RajuMouryaa wow that would be so great but what about speed. TS is not known as a good lang for data processing
@@RajuMouryaa sad 😞 I love VBA
@@RajuMouryaayeah and it’s 2023 and VBA is still kicking. 😂
Excellent video!
I work with a team that develops tools in Excel and Access. VBA and SQL are king (and queen, I guess?). There has been some discussion about using Python, but it becomes an issue of support. I am the only one on the team with any Python experience and if I were to leave, they would be forced to backfill with someone who has Python experience.
The elephant in the room is that Python does not come preinstalled on Windows machines like it does on Mac machines. This is a huge problem and some of the comments here have already mentioned this. Until that happens, if ever, I think you will see adoption of Python as an alternative to VBA be painfully slow - at least within companies that use VBA like mine does.
Thanks for sharing Todd. You make a very good point.
can you make a vba code that open the browser and go to a page
reads a captcha fill it in a text box in the web page and press enter
and the code should work at a certain time
I thought than after VBA I will go into the TypeScript direction, but now I should also consider Python.
I have stopped using Excel for 2 years now. Managed to exit gracefully and use python for doing my LET. L=Load (MBs of csv
You make excellent and convincing points for why Python is important and could (perhaps, should?) be part of any extensive Excel development. However, my employer doesn't want me to install or use anything outside of the Office environment. They have several reasons, none of which are essential to this discussion. The bottom line is that any development work I do for my employer must be done with whatever installs with Microsoft Office and works inside the Office environment. That means I must use VBA and not Python.
It's unfortunately one of the drawbacks of python.
Shocked to see if VBA is still around. Must b nice working for bigger companies. Never seen it in the small niche companies I been working with over the years
Yes there are corporations which use VBA and they don't know python...😔
My workplace uses excel extensively. And we had developed so many VBA applications over 5-6 years. Once we switched to python, an entire new world was unlocked. We have used async, pandas, django, openpyxl, sqlalchemy, pysimplegui and so many other libraries in python which makes our developers life so much easier. Yes VBA definitely has its benefits and there are some shortcomings in python, but the benefits of python massively outweigh the shortcomings, atleast for my workplace.
Also most of the comments mention about installing python for end-user, but we have never done that. We use pyinstaller to generate executable codes that needs no installation for end-user. And it runs on any pc just as it does on my computer, without having to install anything for others.
Thanks for sharing Anish. Great to hear your experience of using Python.
@@Excelmacromastery thank you for the great video
Rather than Microsoft purchasing Python, why don’t they simply incorporate the best of Python into VBA? The steep learning curve, time-consuming organizational software installation, user privileges, IT regulations...Python is simply too expensive as an organizational tool (for now). The code is beautiful, though-this was my first glimpse of it!
The only problem is bureaucracy and people like you.
@@dollarbill8959 😂
Great video. Right now python is my favorite language. I'm still a newbie, but its actually pretty amazing to see the things you can do with it. I'm trying to get to where programming in python is just natural.
Keep going and you'll get there.
Excellent information, how about a comparison of python vs typescript for excel?
It's a good idea. I'll keep it in mind.
I used to work with vba due to installation restriction. Later on i get to work for excel reporting from unix server with built-in python. I get green light from the IT security to install pandas offline and it really replace a lot of manual spreadsheet process.
Intresting vid - I'm a VBA programmer, among other languages, so i've got most of this pretty quickly. I think the main take away point is Python integration, which I think may get rid of VBA (eventually). Otherwise it's just a library. Not only that I can shell out to exe files i've created in another language, or write to an ini set of instructions then do the same. Ultimately Python (in context of VBA) is little more than another library, and there's a lot of libraries, including access to the windows API where I think a lot of power lieth. I'm interested enough to take this seriously though especially as I think everyone will need Python eventually. Moreover - if VBA is rich Italian, Python and others are barked military instructions in a very accurate manner, a far more limited lingo for arguably greater function. VBA programmers may lament the fact they've learned zillions of commands! I'd also be interested to know about the advanced "big data" applications. VBA can do everything in this video and greater. So i'm guessing that if you import 2 million cells and start zooming over them in Python, then VBA may be a frozen spreadsheet and the other may be a quick but two step process then a populated spreadsheet. Cheers for the vid.
I'm interested in Python but I still think MS will continue to push Typescript.
I don't think TypeScript is set to be a replacement for VBA. Microsoft have said "VBA isn't going anywhere" and TypeScript only works online and with Power Automate at this time.
@@Excelmacromastery TypeScript is now in Online Excel as well.
It is easy to build some classes in VBA to do what you show with Python. You didn't show that Python is better than VBA but you said that someone can find Libaries (to import) in Python for better work.
And similar libraries exist for VBA. Realistically, they are pretty equal. The only benefits shown are use of VSCode, which you can also use to code VBA :)
Really you are awesome. I watched your all videos and learning VBA.
The frustrating part is that most companies won’t let users download python on a work machine.
I totally agree with you. VBA is part of Excel and those of us who have used it for many years, as you said, we already have written codes that will not work if VBA is eliminated
Well that's actually part of development. As languages depreciate in value and newer ones start to replace them it warrants a rewrite/recode
4:26 wait a minute, you just have to supply the sheet to it? You don’t have to specify a region?
What if there’s an unrelated table off on the right of this one?
When you tell python to print the 3rd line, will it print the results of the grades table, a bunch of blank spaces, and print the same line of the other table?
For the work I am doing VBA is simple and gets the job done. Having to install Python would result in having to setup another tool. For now I will stick with VBA.
hello from Brazil !!!! Thank you for amazing videos that you posted. It's a hard decision for MS to integrate the phyton to VBA, because they created the typesript, but... It's a question of time, I think...
I'll agree with the other commenters, the biggest advantage of VBA is distribution. Users have Excel and are familiar with it. No additional installs are required. They access one file and it just works.
I would consider Python or something else if there weren't distribution challenges associated with them, but embedding VBA in our controlled documents is a no brainer.
To read an Excel range into an array, this statement is already enough:
marks = shmark.Range("A1").CurrentRegion
Given the variable "marks" is a variant, when you assign a 2-dimensional range to a variant, this variable will automatically be a 2-dimensional array. That's enough. You need not write codes to copy a cell of the range to an element of an array one-by-one like other traditional programming languages. That's completely redundant.
Although you already have a bias against VBA, in making a comparison, please be fair to it (and to Python too).
Also, I would like to point out that, in Python you CANNOT declare variables, whereas in VBA you NEED NOT declare variables. So in making a comparison, make sure you don't declare variables in VBA too in order to make it fairer.
Great video Paul, I had been evaluating this myself, I had heard, but not qualified that Python was significantly slower than vba at runtime.
I haven't done any benchmark testing yet so I can't say for sure. For the Filter task in the video it does run slow compared to VBA.
I felt that the example used in this video could have easily been performed using formulae rather than vba which is what I do these days because vba requires troublesome signatures from our ICT security department on my company laptop. The main reason I have used vba was if I needed to execute event based calculations or actions which formulae can't. In fact, because vba is quite inefficient in some aspect of interchanging data, I tend to use a worksheet as a form of registry to keep the last known dataset rather than keep it as a variable in code. My question to this longwinded comment is whether the Python add in is capable of these. Thanks.
Fantastic video! I'm currently in the process of automating not only excel spreadsheets but also web and file operations at the same time. Granted, you can do all of this with Excel and relevant reference libraries, but it seems Python would be more robust and flexible. Most probably I'll be using both in conjunction :) I hope VBA will remain popular forever as I like this coding language very much. Thank you for putting all of the essential information in such a concise yet very informative clip!
VBA will be around for a long time to come.
Nice video. I'm a heavy user of excel and I'm not very good at VBA, but i learning it. However, this video has got me thinking about switching to Python. The disadvantage is as you stated, installing the application on my work laptop
My opinion: I think that a more realistic scenario is desktop applications like excel being gradually replaced by their online versions. Excel Online is getting better and better and I think that Office Scripts (that runs only on Excel online) will eventually replace VBA because there will be no need to have the Excel Desktop installed anymore.... but this would take some time of course...maybe 5-10 years ....
The advantage of VBA is that company departments can create something "quick" and "dirty" without need of oversight from IT. For example, if I am a manager and want to report on turn-around-time metrics to show my boss how well my team is performing, I can quickly put together a GUI using MS Access/Excel and write some code in VBA to do it.
I think the above reason is why Microsoft has decided to keep VBA; the use-cases for it remain too valuable to discount!
Hi Paul. Thanks for the view of Python with EXCEL. I've tinkered with it in a small way, but VBA (and Power Query) can currently handle the things I need to automate. Probably, that is true for many people. But, you never can discount an alternate way of getting work done. So, worth keeping an eye on the options (Python, Office Script, etc.). As always, thanks for your interesting perspectives and insights. Thumbs up!!
You're welcome Wayne. Definitely a lot of options nowadays.
Great explanation sir. Indeed you have cleared many doubts.❣❣
You can download the code(both Python and VBA) from the description below the video.
*Quick note* on the VBA Array code in the video that many have commented on.
I am aware that I can read the row directly from the range. What I was showing is this:
Accessing ranges directly in VBA is extremely slow and gets exponentially slower if you are dealing with large data. That's why I use arrays.
You can write from a range to an array in one line of code. It is fast and efficient. Reading through arrays is also very fast and unlike ranges the performance remains consistent as the size grows(see ruclips.net/video/JzALsdQvjr8/видео.html)
The problem is that once your data is in the array there is no sort, filter, slice etc. functionality. You have to write your own code to do this. (That's why I created an Array Class - see ruclips.net/video/ZYxa-Q-nSX0/видео.html).
In Python and Typescript(and most modern languages) it is trivial to do this.
I see this as a big problem in VBA if you are dealing with large datasets. A new user writes code and it is running extremely slow. They either conclude VBA is slow and give up or they discover that using arrays will make the code much faster. Once they switch to an array they have now lost the range functionality that allows filtering, slicing rows/columns, sorting etc. They then have to write the code to do this or find it somewhere.
there is no sorting and filtering in python either, it's in pandas library. what stops me from writing pandas for VBA and then claiming it's advantage of VBA. not trying to argue against or for either of the languages, just pointing out that often claimed advantage of python is libraries and those are really not part of the language. a real thing to consider in world of office apps (spreadsheets, small db) is ease of use and overall performance, and VBA wins, and even survives it's owner's intention to kill it.
I would say there is easier code in VBA to achieve what you chose as an example. May be the example chosen was wrong. No doubt that Python is much more stronger than VBA, in many aspects, including the stability of the code and application as well.
One thing you may not have considered is the easiness to use. For a small repetitive data manipulation/formatting, even a non-programmer could do it with the help of macro recording and power query in excel while it needs some learning to do the same in Python.
My verdict is VBA will remain for some more time and even if it is killed, may not be because of Python.
Excellent, thank you Sir.
Just getting into VBA & Python and this has helped guide me to my next learnings
I don’t think that VBA and Python are comparable. I’ll use VBA for MS Office tasks and I’ll check if I can use Python for other programming needs
There is one single reason Microsoft won't replace VBA with Python: it can't be easily run on the cloud version of Office. They are instead trying to replace VBA with Typescript. Personally, while I love Visual Studio Code, Python and its interactivity, I would never exchange the comfort of having the VBA code embedded in the Excel file. Having Python inside of Office would be great, but in my humble opinion, Microsoft should instead update VBA and the editor.
This would be my dream. Even just an updated modern editor would be fantastic. VBA IDE is stuck in last century
But it isn't going to happen unfortunately.
I don't see how can Python be recorded like VBA. VBA is not only for Excel. Many people has used it for Word, PowerPoint and most importantly Microsoft Access. I don't think people can create a form in Access then click a button to run Python code but VBA,
As usual, fantastic video! I guess that Python won't be able to "kill" VBA in a short term, but eventually is likely to do so
Before VBA was XL4 macros, and they are still supported after being replaced by VBA almost 30 years ago. Even if Python or TypeScript replaces VBA, it will live on for another 30 years from now.
I use python when I have to search through 100s (sometimes 1,000s) of files, make column changes, etc. It works great!
Good to hear Robert.
You don’t need to loop through the cells to put the values of a range into an array in Excel. Just set the variable equal to the Value property of the range. The reverse works for saving back to the sheet as well.
I didn't loop through a range. I looped through the row of an array.
@@Excelmacromastery It may seem like you looped through an array, but you actually looped through a variant Range named "marks" because CurrentRegion returns a range not an array. You can reduce your 11 lines of code to just 6 by just setting your variant "row" to shMarks.Range("A1").CurrentRegion.Rows(2).Value. The Value property of a Range object returns an array that you can set directly to a variable without needing to Redim the variant. The same approach works for saving data back to a range on the sheet as long as the range is the same size as the array. Again, no looping necessary in either case.
@@excelrobot Yes, it's a good point. The only code that is a little verbose in VBA is the writing to the console (print). That is something that is unlikely to occur outside of testing too. If you were outputting to a cell, for example, you could simply use:
Range("L2").Resize(UBound(row,1),UBound(row,2)) = row
so you would really only have three lines of code for normal case operations, which wouldn't be a huge time saving if you moved to Python. In this case of course, I'm sure there are many more uses!
You can use PowerShell to access Excel/Office via Com Objects, and it's found everywhere Windows is installed. I'd like to see Office natively support development in PowerShell given it's easier to write than VBA and also a Microsoft technology.
Interesting video. But the for the analysis the current Microsoft solution is PowerBI, and the client - PowerBI desktop. For the Worksheet interaction, the main VBA power is the ability to record the macro(that is missing in Python)
I had to stop this video half way to break off and tell you how taken aback I am. Enlighteneing and Very well presented.
Thanks (VBA author)
Thank you for this video. It's a "food for thought" now......VBA is classic. Python is not that specifically explained and applied. It looks like an old MS DOS screen to be honest. The question from me is how much data manipulation Python requires. Excel has capability of combining VBA with SQL and Power Query, as well as creating models in Power Pivot for Power BI.....
It does look like DOS actually:-) Most programming is done nowasays with this type of setup and a blackground.
I think the biggest advantage VBA has is the ability to share it to other colleague, imagine you in a coorperate company, and u want to make certain task as aumated, if using python, you either need to ask them to install python themselve or install the exe that you make, for excel you can just create and share, no setup whatsoever from the user
Would love a video series teaching python
You cannot kill something that's already dead
This was immensely helpful in clarifying how to use python in excel
Great explanation, please upload python with excel manipulation more videos 👍👍
If Microsoft incorporates Python into Office, then Python + Excel would be great, otherwise VBA is great as it is.
I'm about 2 years into VBA and feel like I'm pretty adept with it, but learning a real coding language like Python scares me. Are they significantly different? It feels like VBA is a clumsier coding language with less flexibility to do what you want it to do.
Try and find out. I don't think it will be too hard, however the two are quite different
It's all about the right tool.
.
One common thing between Data Science, MIS & Business Analyst is "Data Analysis".
.
MIS & BI doesn't deal with huge ammount of data or they don't too much focus on data. So Excel, SQL & BI software like Power BI or, Tableau is enough for them. They focus on business solutions rather than data.
.
In other hand Data Science people focus on data too much. They do machine learning & so on so forth. For them Python is best.
This is exactly what Ive been wondering for a while! Thank you!
This was really useful, thanks! I'm one of those people who makes macros in a large organisation. While I'm sure everyone could install Python on their laptops, it's just not practical to get them to do it and maintain it. Also, my code resources and knowledge are so mature now, I'm just too invested to make a change worthwhile. I'd also have to use a mix of Python and VBA to do things like formatting and emails.
Thanks for sharing Benjamin.
@@Excelmacromastery After talking to a colleague, I think it should be seriously asked if Alteryx will kill VBA in the near future. I'm seriously afraid of Low/No Code solutions taking the spotlight soon.
OK I have no idea what you are doing in that first VBA example but literally all you have to do is this:
1. format the table as a table and give it a sensible name, I named my "data". (you don't even have to do this)
then do this
For Each r In Range("data").Rows(1).Cells
Debug.Print r.Value
Next r
3 rows, thats it. Or am I missing something here? I haven't tested the speed, this approach might be slower but I usually don't work with such huge data sets that speed becomes an issue for me.
1 year later.. i AM watching again this video and now makes more sense why Py will be the #1!!
I am a heavy Office user and I would not use Phyton to simplify my daily job tasks. First and foremost because programming is not my background and VBA is so easy to use and the API is built in all of the Office applications. I can share my Modules and Functions with co-workers and they don't need to know Python either. No additional software is needed to start programming in VBA.
VBA gives you the ability to troubleshoot the running code line by line with F8, breaks points and dragging the resume point at will. This is really powerful, especially when you're dealing with 100's of lines of code in one module.
You can debug Python code.
@@Excelmacromastery Can you do it in a similar way, though? I only wrote some code in IDLE, and didn't see any options to do it.
@@Malignvs I am a beginner at python too, but had a lot of experience with VBA. VS code can do debugging similar to VBA, ONLY if you add breakpoints to the lines of your code where you want to pause. Then you press F5 to proceed from one breakpoint to another. You can add the variable you want to check by putting it into the WATCH list.
@@Malignvs Debugging Python with VSCode is pretty similar to doing it in VBA! You can see all the local variables, you get an immediate window, etc, plus lots of nice extras like updated locals being highlighted and seeing the values in the code itself.
@@johncheung4905 @M Taylor Thanks for the explanations! Still, it looks like you can't go line by line (which helps tremendously) without inserting the break points yourself. However those break points should suffice in most cases. :) I need to familiarize myself with VSCode. :)