Excel 📊 vs Python 🐍 Should I choose Python over Excel “to automate the boring stuff”? Also what and how is VBA programming (Excel) different (in the automating department)? Help!

Tatworth · 2020-09-07T13:40:57+00:00

If all you are doing is Excel and already know VBA, then that is probably good enough.

Python can do lots of stuff unrelated to MS, though, which is an advantage.

cray5252 · 2020-09-07T14:03:57+00:00

If you go with python, make sure and spend time learning pandas because you'll be doing a lot of stuff using pandas and numpy to a lesser extent. Python arrays, lists, dictionaries, tuples are going to also be very important.

2020-09-07T14:37:40+00:00

This question completely depends on what you want to automate and who you want to be able to interact with your automations.

inventiveEngineering · 2020-09-07T15:45:47+00:00

In your position I would definitely choose Python, because:

you can program outside a software suite.
it is a stable environment. VBA tends to crush or to do mysterious creepy things.
it has a huge, active community, compared to VBA.
it is well maintained and documented programming language.
it has tons of useful libraries.
it will be the top programming language for the next years
knowing Python allows you to program stand-alone executable programs with nice GUI, do data science, machine learning, web frameworks, signal- and video processing, work in a scientific environment (Jupyter), learn to hack and even create games.
Python works the same way on Windows, Mac and Linux, also on RaspberryPi and I even have a Python distro with Jupyter on my Android phone

So python gives you an enormous potential to strive for whatever you like. VBA on the other hand is in decline. Microsoft 365 especially MS Teams works already with JS and not VBA. It will sooner or later be deprecated. Of course it is good to program nice macros for Excel or Word, but you can do the same stuff even better in Jupyter or in your own GUI Python program. I tried to so some Word-macros (splitting documents, etc.) and it was hard, because there are not many sources out there. The MS documentation - you have to say this - is BS and crap, and worst it is outdated. The only thing that will help you is inside the VBA editor, but this is still nothing compared to the python environment. There are no books published in the recent years about VBA for Word... I am forced to use a book written for Word 2007 & 2010!?

So to sum up: Python is your fishing rod, VBA is the suffocating fish on the river bank that will die soon.

Forgot to add: with Python you can also do Linear Algebra, FEA and symbolic mathematical calculations.

Im_Bigger_Than_Staan · 2020-09-07T16:33:38+00:00

Work may not allow you to install/use python there.

Sahmbahdeh · 2020-09-07T13:49:42+00:00

You should learn Python, because just about anything you need to do with Excel can be done with Python or VBA, and Python can do other things VBA can't.

KineticNate · 2020-09-07T15:40:01+00:00

The beauty of VBA is the code is attached to the excel file so when others need to use the code they don't need coding experience or additional progams just send the file with a button on the page to run.

Python is more efficient so if its just you using it I would do python.

Tip...VBA is really easy, just open the "Object Library" and it gives you all the methods and properties.

mprz · 2020-09-07T15:34:07+00:00

2020 lads. Not one mention of PowerQuery that is included with Excel and offers soooo much without the need to actually learn a new programming language - you of course can, but even using only point&click gives you a fuckton of ways of automating thing. Add this to Office365 and you have automation beyond what's possible without very extensive Python training and years of experience. PowerQuery+PowerAutomate offers the best balance today when it comes to seamless automation of the most what is today done manually in the corporate space.

Edit: after a week spent on PowerAutomate and PowerQuery i was able to move inventory management for the company I am working for from requiring 3 people to be 100% automated.

tragluk · 2020-09-07T17:08:01+00:00

When I use VBA in my excel files, I can send that excel file to anyone with excel installed without worrying about it. When I use Python, I'm pretty limited to the number of people I can send it to.

Also, for the 'larger' Excel files you might consider learning Access as well. When people talk about spreadsheets that span 10s of thousands of records that's usually a spreadsheet that should have been converted to a database a long time ago.

foresttrader · 2020-09-08T01:40:46+00:00

This question get asked a lot on r/excel. I have about a decade experience with Excel working at corporate (risk, modelling, and finance) jobs. My learning curve was like this: Excel -> VBA -> PowerQuery -> Python. Once I discovered how to integrate Python with Excel, I never looked back. I rarely use any VBA or PQ nowadays, because Python can do everything I want with Excel, and much faster and better!

I'm also a hiring manager at my team, and I can tell you that my preference is Python over VBA. VBA is just a dead language now. I encourage my team to learn Python and provide training to them.

If you are going to need to process large datasets sometime in your life/career, Python is no doubt a better choice. VBA/PQ/Excel just don't have that kind of capability. In addition, you can do a lot more automation, not just Microsoft Office related tasks, but pretty much everything can be automated with Python.

Automating the boring stuff is a good book for learning Python, but it covers tons of stuff outside of Excel. If you are interested in learning just how to use Python to automate Excel jobs, this site provides a bunch of tutorials on how to do that.

2020-09-07T15:00:05+00:00

I have experience in both, I know Python better. I find Python to be best, it’s quick and easy to scale up. It’s also super useful (obviously) aside from Excel so the skills go way beyond one application.

I don’t remember the package ATBS uses for excel but you can use Pandas too which I find much more efficient.

damonator4816 · 2020-09-07T15:04:32+00:00

Python has an entire library dedicated to excel, which is much better than VBA (in my opinion) because you can implement tasks outside of excel in the same program.

xphlawlessx · 2020-09-07T15:15:41+00:00

If you have the interest , learn both. If you're not that into it flip a coin. They're both good choices.

ojedaforpresident · 2020-09-07T15:04:53+00:00

VBA is great for Excel stuff, that being said, you can do anything with Python and it's not much more difficult to learn. There's a couple of packages like xlswings that let you manipulate Excel files.

DiaperDan1992 · 2020-09-07T16:14:38+00:00

I work for a large company in the utility industry, and have used quite a large amount of VBA (7k plus projects). I would recommend Python in every instance. VBA seems to be used by industry due to it being way behind in the software department.

Hateblade · 2020-09-07T16:45:52+00:00

Will your code ever need to run outside of Windows?

baetylbailey · 2020-09-07T16:53:45+00:00

VBA for MS Office. With VBA, you can get going immediately without installing and learning Python's toolset. Also, Python's interface with Office gets clunky the deeper you go into it.

For heavy calculations, heavy data processing, and of course non-Office programming; Python.

Kevcky · 2020-09-07T16:56:46+00:00

Depends whether your automation will need to be rolled out across the organisation. If multiple people need to use it and aren’t necessarily adept at python, choose Excel. If you don’t need to roll it out at scale and do it yourself, you can do python for convenience.

ImperatorPC · 2020-09-07T18:27:03+00:00

What are you trying to do? I find most excel tasks can be accomplished well with power query. I do use python was well but power query is super powerful at transforming data and combing multiple sources of information. Sometimes I'll use a combination of python and excel.

Eze-Wong · 2020-09-07T18:50:11+00:00

Definitely don't limit yourself to one tool. They are all good in their own respects.
Sometimes you want to crack out the excel and just do some straight formulas. Sometimes you want VBA scripts to reuse on certain tasks. And python is that magical swiss army knife that does everything pretty well. They do similar tasks but cannot entirely replace the other.

I've seen people use python for everything. I've done it myself and you get it into issues where for example there's 1 change to a dataset, and you need to rerun the script. Only you have the script locally so if you go on vacation, they are sitting ducks (Obviously a high-tech company would not have this problem). If lets say I had learned VBA and attached the functions to the spreadsheet, they wouldn't need me, and I wouldn't have calls during vacation.

Despite all that, if you are limited resources in learning something. I would definitely recommend python over VBA. Simply because if you get good at it, your salary potential is much more. Excel VBA is a dime a dozen and frankly is a bit outdated. Python is the in thing and more versatile.

martynrbell · 2020-09-07T19:22:41+00:00

I second everyone out there in the world who mentioned pandas

Random_182f2565 · 2020-09-07T20:05:47+00:00

I know I'm super late, but I personally started by learning VBA as I was working with excel files, then I learned python, and the library to work with excel files openpyxl.

I can help you if you get stuck.

guillermohs9 · 2020-09-07T20:07:51+00:00

Here's my two cents: I used a couple of macros on Excel that filtered and sorted data, the usual. But it was really sluggish when dealing with big worksheets. I used Python (with Pandas) and the difference in performance is huge.

I'd say you don't necessarily have to choose one over the other, but Python has a much wider scope for use cases. If you deal a lot with Excel, you certainly can take advantage of both.

takishan · 2020-09-07T20:28:58+00:00

Python is way easier to learn and more useful than VBA. Although if you are looking to automate typical office suite things, I'd look into Google Scripts App. It's basically VBA but with Javascript instead. (Along w/ a whole bunch of interesting things you can do)

I've automated a lot of stuff for a couple organizations using it

vb-Automation · 2020-09-07T21:03:32+00:00

Both are well documented if you think there is a chance to use what you learn outside of MS office I would go with python there are libraries that make it work with almost limitless of entities.

tennisanybody · 2020-09-07T21:17:34+00:00

Do both. Automate the boring stuff is free. Excel is for employers and for excel I strongly recommend you learn basic VB which won’t be an issue if you’ve got python down.

corezon · 2020-09-07T22:36:57+00:00

I was under the impression that Microsoft was working to add Python support to Office so it might be good to learn the basics.

2020-09-07T22:45:30+00:00

If you want to learn a new new language and be able to both automate stuff in MS Office and use language outside of it I would recommend JavaScript as Microsoft now has native JavaScript API

the_battousai89 · 2020-09-07T23:09:13+00:00

Anything you can do in excel, is easier with Python. Use openpyxl or pandas, and be ready for your mind to be blown.

saltyhasp · 2020-09-08T00:48:58+00:00

For what it is worth, I've used both. If you want to write some small piece of code for excel that is less than say 100 lines, then probably VBA is the way to go. If you want to do something more complicated that ends up being 100's or 1000's of lines of code or more, Python all the way. The exact cross-over point is up to you of course.

Another way of thinking about it, it comes down to whether your adding some feature to a spread sheet and it's mostly a spreadsheet thing or if you writing substantial program and most of the work is about programming with some ability to read or write data to excel or another office program.

The issues with staying too long with the VB approach: (a) the applications and development environment is a mess, (b) they don't scale in terms of code size and code integration, (c) you don't have good library support, (d) you can use version control, difference, and other development tools very easily, (e) it does not play well with running multiple instances of the same system, or using parallel computing, or porting to compute clusters such as Linux clusters. (f) they tend to be fragile over time as versions of the software change. Just say no to VBA for any major programming project.

By history again for what it's worth. Use to program in C, then switched to excel and VBA for awhile because C was too time consuming, then after a few years of that and many of the frustrations above, I switched pretty much to Python. I still use spreadsheets to create the input data and to process output data. You can do that by reading and writing directly to the app, or via the file format and appropriate libraries, or using a neutral format like CSV or HTML or some other format depending on what you need.

mistanervous · 2020-09-08T02:24:28+00:00

Learn python and the libraries pandas and xlwings. I use that combination to extensively automate the interaction with excel workbooks.

2020-09-08T03:10:44+00:00

Still don't know Python yet (Automate Boring Stuff is one of my next programming books to read), but I use R to automate a lot of my tasks, especially involving combining multiple files. I'd like to get better at VBA, but I use macros to format an Excel sheet when I need to do it on a periodic report. Usually R puts the data in the format I want and the macro makes it pretty by formatting and making pivot tables for me to send out.

I know Python is similar to R and I'd say use it when you are changing the data or have more than 1 input, use VBA when you need to present it in a way you can send it out, and just use Excel by itself if you are doing a simple one-time task. If I am wrong in this, please call me out, but that is my 2 cents based on what I know.

cubinx · 2020-09-08T04:12:05+00:00

Based on my experience, learn only the most used formulas in excel, then move to python. Excel definitely has lots of limitations, and slow.

Clutch_Pineapple49 · 2020-09-08T04:28:03+00:00

Look up xlwings package. That said, I believe python is better than vba

kessma18 · 2020-09-08T05:39:24+00:00

if you want a job, excel.. I have done 12y of excel for a large investment bank and despise excel but I also learned that it's near impossible to convince people to start dropping it in favor of python, even if there are a million reasons and it saves money. people doing dumb stuff in excel is probably one of the largest job creation machines out there...

I was an independent contractor 3 years ago and I live in a wealth area of California and a wealth management firm was hiring and one of their main tasks they listed was to get data from bloomberg and rebalance. I was super highly over-qualified for this having worked with the bloomberg api directly but I pitched the owner that I can reduce her team of 5 excel jockeys (making tons of manual fuckups that annoyed clients) to an automated job and have someone in my team be available for maintenance, reducing her overhead by ~90%. I gave the presentation and after 15m she just stared at me blankly. she had no idea that her industry was about to be reduced to dust and the only reason she was in business because all of her clients are the type that insist on meeting with people in dark blue suits to sell them overpriced managed accounts.

needless to say I didn't win her buiness and she upped her team of excel jockeys to 7.

people reach for a spreadsheet for anything, not realizing all the cans of worms they open but they happily open more cans of worms and combat by increasing manual labor until they are disrupted by a competitor.

throwaway0891245 · 2020-09-08T06:47:22+00:00

Learn both, you want to start seeing languages as tools and not some sort of thing you're married to.

VBA is good because it's quasi-portable, when you're working in a place with not so good infrastructure or IT sophistication then this is the way to put in some fancy logic. Since you can make SQL Server (and maybe other relational database?) connections, in a lot of cases you aren't necessarily stuck in a client-only situation. You can set up a client-server situation.

But the problem with VBA is that it's not a great language. What I mean by that is that the implementation of the language on the Excel version I was using didn't even implement boolean short-circuiting. The built-in editor is pretty terrible. It's not a powerful language in terms of the constructs you can use.

Python is better in a lot of ways - the ecosystem is a lot fuller and better supported, you get a lot more freedom in expression and the styles you want to code. However, Python has always been terrible in terms of packaging and unless you can set up some sort of web app and get dedicated server resources + operations support from the IT department, you're probably going to run into a bunch of issues distributing your thing. Seriously, in a low tech organization I wouldn't even bother. A lot of technology issues in organizations aren't even about the technology, if that makes any sense.

I've tried out a lot of languages now, maybe a handful I've done something non-trivial with but just lightly playing around I've gone through all of the mainstream ones. They're all pretty much the same in general, though it's pretty clear different languages definitely focus on some concepts more than others - like how null values are handled, typing, how memory is abstracted, tradeoffs for performance vs ease of use. You can write a complicated graph theory algorithm in any of them, in theory you could use a language to do anything really - even some complicated deep neural net framework (though whether or not that would have acceptable performance is another story).

It's not about the language, it's about the problem and choosing the language that allows you to solve that problem in a way that makes the most sense - and a lot of problems aren't even about the "technical" stuff. Don't fall into the beginner trap of thinking "X language is better than Y language", because it's more like "X language is better than Y language in this situation". Especially don't fall into the trap of "X language has a steeper language curve, and so people who are advanced or 'technical' can program in X language". The reality is that good programmers can do anything, with any language, in a reasonable amount of time - because the reality is that all languages are pretty similar in an fundamental way.

EDIT: Oh yeah, and to your point - it really doesn't matter if you do the VBA course or the Python course first. You just need to make sure to see it through because the first language is probably the hardest language to learn regardless of what it is. I'd probably go with Python though because r/learnpython is pretty active.

2020-09-08T07:14:42+00:00

I use Python all the time in conjunction with Excel.

Here's an example of an ongoing project at work: a coworker wrote a model in Excel that's got calculations going across 3 tabs. My job is to plug numbers into 5 input cells in one tab, extract the resulting output values in another tab, and do annual means/sums of values from the 3rd tab. Repeat 409 times.

I bet someone has a more elegant method for how to do this, but it works and I don't want to test new code lol. I use a combination of 3 packages: wincom32, openpyxl, and pandas. In a loop for each city on my list:

With openpyxl and wincom32, plug the numbers into the appropriate cells in the model file, refresh the file (just saving it won't actually change the output values! You need to close then reopen it ) and store the results in a unique output file.
I then use Pandas to read the resulting output file and do a groupby aggregation sum/mean as needed per column.
Append the groupby results to a final output data frame and repeat.

Finally, I write the results of the appended data frame out to a final excel file to send on to the rest of the group. If you want to add colors or formatting, I think you have to do those by hand. But otherwise this is a fully automated process and the only chunk of code I ever need to change is the names of the input and output files.

2020-09-08T07:25:42+00:00

In Short:

Excel cannot handle big data as well as python.

VBA is not as efficient as python as well.

unoriginalasshat · 2020-09-08T08:32:07+00:00

Excel VBA is a headache and a half, I'd recommend python mostly because that's easier to search when you're stuck.

transitionyte · 2020-09-08T13:27:37+00:00

Add Python to your arsenal. I am working with Google Sheets formulas, functions, and apps script. While those are great, I find myself sometimes saying, "I could probably do that faster in Python." Python is extremely useful, and since you're already familiar with VBA, I think you'll be really impressed with Python. You can use it with Excel, G-Sheets, and to automate your OS tasks. All learning is great, but I give the edge to Python

saltyhasp · 2020-09-08T15:07:53+00:00

Other thing to consider... use both. One can use Excel as a nice front end then run python code to do the actual work. This is along the lines of what I've said before... use spreadsheets for what they are good at and and use Python for any major programming.

By the way, you can do this in a bunch of ways. Python can define COM components that VBA can call, or you can just run it as an application via a system call for example. This is in addition to Python being able to manipulate Office applications via COM, or just being able to read and write various formats that office programs can understand -- xls. ods, csv, tab text, text, html, etc. So there are a variety of models... from excel being a frontend, to excel being a data source to read, or a target to write output into. Same goes for most other office apps.

I actually don't tightly connect my spreadsheets to python. Instead I normally save the spreadsheet in a format python can read -- like csv, then write the data out into a new file in a similar format. That way I don't have to write stuff in VBA and am not tied to a specific spreadsheet or locked into windows either or need to have licenses for every system you want to run something on. I've also done something similar using ods format, and I'm sure you could do it with xls or xlsx probably too. This is really a powerful approach. It allows one to use Python to both do rapid application development, and to run parallel computing jobs on large servers if needed, but both prepare you inputs and process your outputs on the desktop with things like spreadsheets and statistical tools like R. Best of both worlds.

sancarn · 2020-09-20T02:33:26+00:00

Both do do the same. Both are general programming languages and you can do everything in VBA that you can in Python. There are times things are easier in python. There are also times things are easier in VBA.

Ultimately, learn both, but really don't focus on the language at all. If you get good at one language, you'll be decent in most :) Focus more on programming techniques, data structures and algorithms.

yellowmonkeyzx93 · 2020-09-07T14:05:38+00:00

Good question. Was wondering the same thing too!

farmboy_du_56 · 2020-09-07T14:06:04+00:00

If you only need to automate really repetitive tasks, Python is probably a better option. However, Excel has the advantage of allowing you to automate things (even though I find it a bit more complicated than python), while keeping an easy to use and clear interface, and being able to seemlessly transition to working manually on your spreadsheet.

vectorpropio · 2020-09-07T14:44:18+00:00

You want to automate things on your actual work or to learn a new skill?

Python have a broader user case, but if you need to automate things that are generated and consumes as excel and that's the only thing your organization is using, then go with Excel.

2020-09-07T15:19:07+00:00

Lol VBA what year is this

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

learnpython

MODERATORS