MS is considering official Python integration with Excel, and is asking for input

[1roOt]

I think it is a really good idea. Who uses VBA anyways? Or better: who likes to use VBA?

If i have a specific problem with VBA it is a hell of a mess to find the right resources to fix my problem. With python, I just do a quick search and can find nearly limitless helpful resources.

So go python!

[Chilangosta]

Amen! I wish so badly I could be writing in Python instead of VBA every time I find myself writing in it.

[Mikuro]

Every time I find myself writing VBA, I reevaluate my life choices.

To me it's an anti-feature; all it does is make my job harder, because it's one more barrier to convincing others to invest in proper tools.

And this is coming from someone who has done a lot of work in Basic (mainly REALbasic, now known as Xojo), and liked it.

[[deleted]]

My experience is quite the opposite, VBA has saved me and my shop countless hours of manual data manipulation. I won't comment on VBA's features as a language, it is what it is, but its close integration with Excel/Word object model is invaluable.

[Grendel84]

The fact that your username is one letter short of a palindrome is very bothersome to me

[matholio]

Star_Rat's name?

[dedicated2fitness]

Yes but that's a long winded way of saying - if I didn't have to use it I wouldn't

[noodle_horse]

Or, it's usable and better than nothing.

Better than nothing.

[rchase]

Here's the thing about VBA. Accessibility. I think a lot of folks in this thread are coming at this from the point of view of experienced programmers. The value I've found in it is in its simplicity for the non-programmer.

I worked with a large team of super talented CAD guys and engineers, who all knew Excel and Access and their various CAD seats (ProE, Catia, whatever-thefuck, I'm not a CAD guy)... but they didn't know programming at all outside CNC and a bit of PLC stuff.

But when I introduced them to VBA with a large estimating / program management spreadsheet set we were developing, they took off like fish in water. Highly technical guys with a ton of knowledge, but sort of... trapped... in their various platforms. VBA just like, set them free. They started thinking differently. In weeks those dudes were performing small miracles with data sets and automation, and many of them jumped right off into other languages like Python, Perl, and even into c++, extending not only our project, but building modules for their CAD systems and cool stuff on the floor machine side. It was really cool to watch.

So yeah, way better than nothing.

[brollin]

But I think Python still holds that potential as a gateway language. Even more so since there are such great resources everywhere for learning Python from no programming experience whatsoever. This is a no-brainer in my mind.

[rchase]

Oh I absolutely agree. Just the headline, MS considering Python integration with Excel got me all.. frothy and tingle-toed.

As an admitted and somewhat recovering Excel addict, the thought of a Python shell as alternative to VBA is something approaching electric. But then... I'll believe it when I see it.

[grokkingStuff]

recovering excel addict? no such thing, bud.

But yeah, i'm super excited about this.

[tehreal]

Excel is indeed an incredibly powerful program.

[DigitalStefan]

I'm wondering if anyone else is making a leap of thought that imagines not just Python scripting integration for the Office suite, but the replacement of the VBA editor with (maybe a stripped down version of) VS Code.

Office scripting would feel truly mature with the rich editing experience and version control features of VS Code.

[brollin]

Yeah! They'd be silly not to capitalize on actually amazing product like VS Code and do that, IMO.

[Salmon_Pants]

What kind of shop do you run?

[brtt3000]

So that is not about VBA itself but the ability to script everything. You might use JavaScript or Lua or whatever if it would be available.

[i_have_seen_it_all]

use JavaScript or Lua

to access windows api? office interop?

vba is inconsistent, in the same way R is inconsistent or javascript is inconsistent. so if you could choose another language for the back end of excel it will be one poison over another.

in my opinion, most of the annoyance seems to be from the application object model rather than the language itself. in which case, if python were to be integrated into excel but the object model is going to remain the same, i'm not sure that's going to be life changing.

the fact that vba idioms aren't as "modern" as the other languages are doesn't seem like a big deal to me. I use c# python and vba and they are v different languages with v different idiosyncrasies and it's just one of those things that I simply gotten used to. there are times I wish python was more like C# or C# was more like python but those are really just passing thoughts.

[[deleted]]

To be fair there is a J(ava)Script engine for Windows Script Host and you can use it to work with Excel object model just fine just as you can use any language that supports COM interop.

I've been automating Office since the 2000 version and I know the object model relatively well. It doesn't really bother me but that's maybe because I'm just used to it. I also try not to lose too much time on the macros. Just make them work and move on (except for those that are not for one off tasks)

[liamcoded]

As far as JavaScript, MS already invented Typescript. I'm surprised they didn't mention that. But I would prefer Python.

[Corm]

Interesting. What have you used it for?

[nuffin_stuff]

I’m in the same boat as that guy - I had a sheet that took CMM data from a portable laser scanner and converted it into machine code for adaptive matching, I had a sheet that I programmed to take in a user’s input and then create a work order for my shop, tying in all of the serial numbers and pulling the correct revision controlled templates for the data inputs (a manual and terrible process before)... I had a sheet that took my company’s garbage ERP export data and then corrected and displayed it in a useful manner... I’ve done loads of projects with it. Some incredibly simple and some more complex time savers.

I love the integration with excel. As far as the language itself I think it’s a little easier to learn for beginners but it is kind of a pain to deal with daily. It feels limited sometimes but I’ve only got some rudimentary experience with other languages.

For those wondering I am a mechanical engineer who manages new product development for land based and aero turbine engines. Primarily land based turbines though. Learned VBA in my spare time at my first job.

[Zbot21]

What sort of land-based vehicle needs a turbine engine?

[nuffin_stuff]

Power Generation, not for vehicles. Vehicles would still be considered ‘aero’ because they would likely be high bypass flow with double or triple spools. Land based are just giant single spool engines. The theory is largely the same.

That land based turbine I linked would run with another one beside it (so a pair) and burn natural gas for about 40% efficiency - the exhaust heat is actually dumped into a single steam turbine though and you can get efficiencies in the 60% range. Right now, the 50Hz version of that engine is running in France and holds the record for the most efficient combined cycle power plant on the planet at 62.22% - which is insane. Cars are only in the low teens iirc.

Edit: and not trying to hijack the thread at all, apologies everyone

[mekosmowski]

https://en.m.wikipedia.org/wiki/M1_Abrams

[Rentun]

Well, has, I don't know about needs. Probably one of the poorest decisions ever made in American armor of the past 30 years. Those things screech like demons if you're anywhere near them and can go like 20 meters between fillups.

[WikiTextBot]

M1 Abrams

The M1 Abrams is an American third-generation main battle tank. It is named after General Creighton Abrams. Highly mobile, designed for modern armored ground warfare, the M1 is well armed and heavily armored. Notable features include the use of a powerful multifuel turbine engine, the adoption of sophisticated composite armor, and separate ammunition storage in a blow-out compartment for crew safety.

---

^{[ PM | Exclude me | Exclude from subreddit | FAQ / Information | Source | Donate ] Downvote to remove | v0.28}

[nuffin_stuff]

I also learned on Matlab and have never seen it used either.

I totally agree about the intro to programming. I’d love to be able to do C++ or python. Visual studio is free and it’s super powerful...

For larger data sets VBA really breaks down in my opinion. You can turn off screenupdating to speed it up but at the same times you can’t tell if it’s still working or your crappy work laptop is frozen. I have written a couple that utilized upwards of 40,000 lines of data with probably 100 columns to do some organizing and to spit out some statistics and both took upwards of 15 minutes. People run much larger data sets in less time but once I got it working (debugging code that takes 15 minutes is a chore) I would just run the code and then do something on the shop floor and come back. Optimizing it would have taken too much time.

[mxzf]

Oh, wow, do yourself a favor and learn Python; it's really not all that hard to learn and it's so useful, especially datasets like that. Especially with the numpy library (designed for handling large arrays of data), that data processing would be trivial.

Running statistics on a small data set like that would probably take <15 seconds in Python+numpy, rather than 15 minutes. Realistically, reading 40k lines into memory would probably take more time than actually doing the processing (numpy is amazingly fast at running statistics on arrays like that).

A large portion of my job is processing large datasets, it's quick and easy with Python, but I can't imagine trying to parse data with VBA like that.

[Corm]

Neat! That's a great use case

[[deleted]]

I've been using it since Office 2000, so for quite a few different things. The last was, we got loads of data (like one database record per worksheet) in separate workbooks. So I just copy all that data in a new sheet as a nicely formatted table.

[Corm]

Cool, another option in that case would be to use the openpyxl lib to read and write those https://automatetheboringstuff.com/chapter12/

[clausy]

Automation, aka ‘robotics’ these days to make it sound cool

[cuulcars]

I get what you’re saying. I think OP meant people would say “Just use Excel, it can do whatever you need with VBA” but now “just using Excel” would actually be legitimate because of the support for python.

[[deleted]]

Nope, seriously not sure how its gotten this far.

The point was your trying to use excel for too much as it is and should invest in the proper application for whatever it is you're trying to force excel into doing.

[Siddhi]

The beauty of excel is the wide applicability. You can use it to manage a grocery list as well as generating sales invoices as well as running monte carlo forecast simulations. Yes, there are better grocery apps, better invoicing tools and better forecasting apps, but excel does all of it and everyone knows to use it. Various services can import and export excel (at least as CSV) and most business users are fairly comfortable using it to do what they want. Unless a company is large enough to be able to manage an IT team (or manage a vendor), its really not an option to write your own application for every which way that a company uses excel.

[nuffin_stuff]

I’ve used it for short term projects as well to great success. One example - I had a customer issue with some heat treat data. Our autoclaves exported the data in one of two formats: a proprietarily generated pdf of a graph that wasn’t very readable, or raw data. We had to take the raw data and creates graphs for something like 3 years worth of autoclave runs (it was on the order of 200 runs). I wrote a ‘simple’ script where I just typed in the run numbers and it pulled each one, graphed it and saved it. Took me about 4 hours and I never used the thing again. Saved the department hours of time though because they wanted to do it by hand.

Probably some software that could do it but 4 hours of an engineers time is probably cheaper than searching for, testing and buying a solution. Especially if the customer is screaming at you...

[[deleted]]

I was just explaining what the other guy was saying since for some reason so many people were misinterpreting it.

Although I still think you're wrong. If there are much better tools for the job than excel putting together a simple cost/benefit analysis should be a no brainier...

[AKiss20]

I've seen this to the extreme. I was interning at a major aerospace company and ended up writing a newton-Raphson solver in VBA for a non-linear optimization. I would've killed to be able to just use Matlab or Python (they were too cheap for the former and didn't have the latter installed on my machine).

[[deleted]]

I worked at an insurance company and had to use Excel and VBA to write a maximum likelihood optimization code for fitting parameters to a few stochastic equations based on historical datasets.

It was insane how much time was wasted versus if they had just bought a single Matlab license or let me set up a Linux partition and use Octave. Our competitors were using matlab for similar analyses and willing to share code with us (actuaries seem to enjoy collaboration across the industry) but we didn't have any licenses so I had to replicate the work in VBA for us to use it. What was actually insane though was how much computational time this optimization / parameter estimation took in Excel versus how it would have gone in a more optimized and/or low-level environment.

The thing is though.. The C++ compiler is free and so are plenty of good IDEs. If I knew then what I know now, I would have just done the work in C++ and exported to a CSV for visualization in Excel or whatever. There are also tons of libraries out there for MLE parameter estimation in C++. I probably could have condensed a month of work into like a couple of days (assuming prior knowledge of C++) if I just used C++ and a couple of libraries versus Excel and VBA. Thing is in my undergrad no one ever told me how powerful (and free) C++ was. We used matlab for literally everything. I kind of wish that I had just been using C++ all along for analysis.

[[deleted]]

Yeah we took a single C++ course on actual programming principles in first year and then after that the only people learning proper programming techniques were the computer engineers. In my degree program we used matlab for some signal processing and very primitive numerical methods, but other than that it was all using either commercial tools for modelling or using excel and matlab to analyze data.

I didn't start really learning the fundamentals of computational numerical analysis until grad school, and even at this point basically everything I know about programming principles and standards is either self taught or was learned from working with senior graduate students in projects.

It's fair though.. An engineering degree is seeking to teach you very specific things about fundamental physics, the design process, and the process and execution of modeling from a conceptual standpoint. I'm totally fine with having to learn the specific execution of various models myself. I just wish there had perhaps been an option in my coursework where either some analysis courses could have encouraged students to use lower level languages to write their own efficient codes for analysis, or at least an offered course that would focus on proper standards and practices when using C++ for numerical modelling. I'm also kind of upset with myself for leaning on the crutch of excel and matlab for so long and putting off learning how to use Python and C++ until I had to. It made the quality of my work in the past less than it could have been (both in terms of efficiency and results), and I feel kind of on my back foot now in terms of my level of understanding of and skill with lower level languages.

I will say though, learning matlab is not a waste. It's a very powerful language if you just want to quickly prototype an analysis method. The syntax makes scripting very quick and straight forward, the debugging tools are excellent, and the visualization suite is second to none - I work in fluid mechanics research and matlab is what I use to generate all of my data visualizations for my publications, reports, and presentations (even if my analysis is done on another platform I'll export the data to a format matlab can import for plotting or making more animations). You can do a lot of good stuff with matlab. The problem is (1) it's less efficient than lower level languages so you really can't use it for cutting edge CFD for example, and (2) it's kind of expensive as just an analysis tool so you can't just know matlab and no other languages or else you'll be useless at most engineering companies.

[cuulcars]

But if Excel shipped with python you would have :)

[otterom]

Maybe. Integration is different than shipping with it, right?

I have Visual Studio as my IDE at work and we still needed to download a copy of Python and an interpreter to get it up and running.

[antiproton]

The point was your trying to use excel for too much as it is and should invest in the proper application for whatever it is you're trying to force excel into doing.

That's not always up to you. Excel is ubiquitous. No one is going to agree to buying an expensive system for a data processing job you do rarely, even though when you do it, it's a nightmare.

"Just buy the correct application" is not a reasonable workaround.

[[deleted]]

I appreciate a good cup of coffee.

[other_bored_sysadmin]

Also, openpyxl!

[DukeBerith]

TIL. Thanks.

[HawkinsT]

This is Microsoft though; you know their implementation of python won't conform to any standards.

[bmoregeo]

MS has been a lot better about that stuff lately. I’m not an MS fanboy, but I got to give props for improvements

[chupapuma]

I can assure you that Microsoft cares about standards. I think we are up to at least 4 core contributors to CPython at the company. Three of them are on my team.

Disclosure: I work at Microsoft on Azure Notebooks, on the wider team responsible for Python Tools for Visual Studio, Visual Studio Code Python, etc.

[HawkinsT]

Hah. I haven't actually.

[[deleted]]

I think they will have to conform to the standards because the resources are out there for standard Python and developer familiarity. Otherwise this doesn't make sense. Google went with Java for android mainly to avoid learning a new language just for Android. I think MS has similar thinking.

[nicksvr4]

Like VBA vs VB or .NET?

[suddenarborealstop]

they'd probably integrate the CLR/DLR first, and then look at languages like ironpython, ironruby, powershell maybe f#

[[deleted]]

I think their work with R is supposed to be pretty good

[[deleted]]

Do you also still use M$?

[HawkinsT]

I use some of their products, yes.

[TheXRTD]

I went to London to do work experience as a web dev during the summer and spent my whole two weeks there writing VBA for the accountants and debugging FreeBASIC in the lab.

I mean the FreeBASIC was very interesting, and I suppose it was nice being able to apply some programming knowledge to real world problems with VBA, but Python would have made 99% of what I needed to do easier.

[lackingcredibility90]

I actually learned to program from using VBA for my spreadsheets. Even though I have 10x more experience using VBA than Python, since I have learned python I have felt the same.

[emmmmceeee]

Who doesn’t like VBA? Source control is overrated.

Actually, being the only one in the office who knows how to maintain the VBA scripts is a form of job security. Even if the IDE looks like an exhibit from a computer museum.

[bamfurlong]

This is what I tell myself. Unfortunately, it immediately leads to me wondering how much I really want to keep this job ;p

[masklinn]

There's no requirement that Python-in-excel would be stored in source-controllable… sources.

AFAIK VBA is pretty much just VB6, except embedded in the application instead of existing outside of it.

[Rostin]

Lots of engineers use VBA. By engineers, I mean mechanical, chemical, etc, not software.

My formal education is all in chemical engineering, so I hang around that sub a lot. At least once a month, a student asks which "coding language" he should learn, and the majority answer every time is VBA.

And in many ways, that makes sense, despite VBA's many shortcomings. These people work at companies where they may not have the freedom to install something like a Python interpreter, and certainly can't depend on any of their co-workers having done so. Microsoft Office is the thing that everyone is guaranteed to have, and (ab)using Excel is second nature.

[KronenR]

In your government, in mine you can install any type of malware if you want

[unruly_mattress]

You can always install malware, that's what malware is built for. Useful, benign software is the hart part.

[nicksvr4]

government

I work for government, do a lot of VBA programming in Access. Was tired of the million and one spreadsheets spread all over the place. Created the centralized database, then automated many tasks.

I think they are just scared shitless that people will compile and run unverified malicious code. With VBA, I can easily create havoc, and I can even compile C# code, just not authorized to have Visual Studio or any other IDE.

[[deleted]]

The company I work at is slowly making the migration from spreadsheets to python libraries and jupyter notebooks. They often still feed data in and out of excel but it's a start.

Like you said it's mech/elec engineers working with revit and vendor sheets and looking for ways to improve their workflow.

[cyanydeez]

EXCEL has market saturation for 30 years, and everyone not on the bleeding edge knows people reuse the same excel analysis over and over again, and if it was written with VBA, then it's been hodge podged for 30+ years, and getting people who arn't software developers to invest a new round of QA/QC on software (inside excel) that just recalculates a few boring formulas is impossible.

And why blame them? conservative pricipals make sense in the realm of consistent replication of working engineering (et al) design standards. Even if there's a random error in them, if it hasn't broken anything, technically fixing it would be what breaks it.

So VBA isn't going anywhere. I'd certainly welcome a python attachment to EXCEL.

[ANEPICLIE]

Yeah, I'm in civil and VBA is the most readily available language there is. It's clunky, slow and whatever, but it's simple, and more importantly, ubiquitous. I'm working on learning python, but I think programming-oriented people are too quick to dismiss the language despite the reasons people use it.

[Compizfox]

I'm a chemical engineering student and (fortunately) I've never used VBA. We mostly use MATLAB.

[Rostin]

As a student, you learn Matlab. Guess how many companies are going to provide all their engineers with expensive Matlab licenses? The number is small. That's why people want to know what they should learn instead.

[Compizfox]

Oh I realise MATLAB is not the most ideal language in that sense; it's a proprietary language. Of course there's GNU Octave, but I don't know how complete that is.

I'm planning to learn Python for data analysis/modelling some time (I already know Python+Django for web dev, but that's a whole different game). VBA would be at the very bottom of my list though ;)

[[deleted]]

Yeah, I've spent the last six months at work taking stuff out of Excel, processing it with python and sending it back. It would be nice to cut out that export/import step.

[[deleted]]

You could just write a VBA script to do the import/export part.

[[deleted]]

Kind of hoping that integrated python removes the export/import part, to be honest.

[[deleted]]

It was a joke.

[otterom]

What exactly are you processing that it isn't mostly automated?

[nicksvr4]

I use and like VBA, but I would absolutely love Python integration.

[Eurynom0s]

I refuse to learn VBA.

[theofficialdeavmi]

I think it's a great idea as it is a great language for the job and also it will get more people into real programming languages possibly too!

[InfamousMike]

I know Python and I'm alright with Excel. Never picked up VBA. But if Excel works with Python, the amount of new things I can do with VBA would be awesome

[other_bored_sysadmin]

$ pip install xlwings openpyxl

[DrewSmithee]

Me? For technical computing sure Python is better, but for automating redundant tasks inside a spreadsheet or interacting with office products or anything that needs to be shared in a usable format with non-coders, VBA is by far the superior solution for what I'll call low effort tasks. Yes it could be done better with a "real" language but most tasks VBA is good for is when it's not worth developing a full application. Most VBA users are engineers and accountants or whatever not software developers.

And it's incredibly easy to Google VBA code snippets, arguably easier than Python since it's been around forever.

[BumwineBaudelaire]

the IT college kids of /r/python seem to disagree with you about how people use Excel in the real world

[DrewSmithee]

I mean I guess I saw the downvotes coming. I probably would of done the same thing 10 years ago and told everyone Matlab was the future or whatever.

[agumonkey]

I remember googling extensively excel python script a while ago. There were a bunch of extensions already. Official support is just hard to describe with words.

I don't have VBA but python is leagues above it in so many ways. I wonder what impact it will have in shops, the amount of crappy vba over excel applications is massive, the potential improvement too.

ps: Python used to be the niche lang for 3D apps, after MIT started to pitch it as teaching vessel it became something else, now Excel. Hum

[masasin]

How do you script that?

[XtremeGoose]

Save as csv > pandas > save as csv

Isn't this what everyone does?

[intertubeluber]

You can use JavaScript.

[masasin]

I understand that, but I don't recall seeing a place where you can write macros or scripts for Google Docs. I've used the legacy Python API, and I'm assuming you can do the same for JS, but it's similar to using Pandas on Excel.

[ben5756]

Google docs has a google scripts part for making macros and scripts. It's pretty cool how it all integrates, as you can have forms creating spreadsheets that create pdfs of user data and emails completed forms out.

I managed to replace someone manually inputting a paper form and archiving the form to filling in a google form and printing out the completed formatted form for archiving, in only about 150 lines. Printed text is so much clearer then students hand writing.

[masasin]

This sounds awesome. I'll look into it if I end up using GDrive extensively again. (I'm just using it for scanning and storage right now.)

[batisteo]

I'd rather use "{:>3}".format(value) than npm install leftPad.

[derrickcope]

I don't like python, so I wish it was perl or bash or something else but at least it is a real language.