Another reason you should learn to code: Python for Excel

http://gigaom.com/2012/06/08/another-reason-you-should-lean-to-code-python-for-excel/

by joxie • 14 years ago

74 30 14 years ago

fromhet2• 14 years ago

On the front page of a website named Hacker News, there is a link saying that people should learn to code?

Excuse me, but shouldn't we expect hackers to already know that (or at least be knee deep in Thinking in Java or it's like)?

gigantor• 14 years ago

It refers to the controversy started a few months ago by NYC mayor Bloomberg saying people should learn to code, and then Jeff Atwood insisting the opposite (http://www.codinghorror.com/blog/2012/05/please-dont-learn-t...), and the countless follow up blog posts debating the issue

fromhet2• 14 years ago

Ah, drama! :)

greggman• 14 years ago

I've never actually scripted with VBA but I have looked at the VBA environment. Does using IronPython mean basically giving up that environment? Is that environment (easy form creation with point and click event code attachment) important for VBA devs? It has certainly always looked compelling but since I'm not a VBA dev I have no idea.

bobroberts• 14 years ago

Been available in perl for years.

asjo• 14 years ago

You got any links?

pasbesoin2• 14 years ago

At a glance

https://www.google.com/#q=perl+excel

seems to surface both a bunch of links from the beginning of the last decade, and some current stuff. I would be more familiar with the former, so it may be best to form your own opinion on the current state of things. But I'm commenting here, to the effect that there is much information readily available.

P.S. At least of a decade ago, it helped to have a good understanding of Excel and its programmatic interface. One or more Perl modules made it easy to call into this interface, but you still needed to know what was available and what it would do.

pasbesoin2• 14 years ago

Yeah, I was doing that well over a decade ago.

At that time, I found it most convenient to use Perl for some things, e.g. condensing a "ton" of raw data down into an Excel representation, and VBA for others (manipulating extant worksheets/workbooks).

I also wrote a object-oriented API wrapper in VBA. VBA's fine for doing real work, when you're in the MS Office environment -- or when you have a "plain Jane" Windows machine with no ability/authority to add another toolset.

lunchbox• 14 years ago

Also see Pyvot, a similar tool that was created by Microsoft:

http://pytools.codeplex.com/wikipage?title=Pyvot

stephengillie2• 14 years ago

Previous discussion: http://news.ycombinator.com/item?id=4085052

lifeisstillgood• 14 years ago

I am fairly sure that the first oReilly python book had several chapters devoted to COM and excel/word. I know I wrote articles on it, got a job based on scripting PowerPoint. Almost every dynamic scripting language I can think of has COM automation ( or whatever it is called these days)

using IronPython to access COM components seems another unnecessary layer. And in my experience what is useful is calling from excel to python and then using vba to manipulate it. (VBA inside excel is fine. Might I suggest that if you are Reading this and thinking "great, anoter way I can program exclusively in Pythin everywhere" you have fallen victim to the yet to be named syndrome and should forcibly use any other language. I still get relapses

jlarocco• 14 years ago

Two articles on HN, yet still no answer to "How is this better than using Python and Excel with COM?"

I'm almost disappointed that so many people think this is an impressive new thing.

stephengillie2• 14 years ago

Maybe many people were unaware this was possible. My coworker was certainly excited.

maxerickson• 14 years ago

Hello Cell is far simpler with IronSpread.

No instantiating a COM object, no opening a workbook, no selecting a worksheet, etc.

Bootvis• 14 years ago

It might not be very impressive technically but that is not the major selling point (to me). I like it because Ironspread allows me to use Python in Excel without having to learn COM. This is great because interacting with Excel would be the only reason to learn COM for me (and my co-workers).

Akram• 14 years ago

The article does not focus on how IronSpread is doing a cool or unique job but it focuses on how beautiful Python code can be. Just a few lines of it can do so many cool things that make non coders want to learn to code.

wycx• 14 years ago

I can see what the author is trying to get at, but I can't help but think the example is not so good.

If you use Excel and want to take it a bit further I would suggest that RExcel is probably a better choice. The R environment has abundant, relevant functionality for those who use Excel frequently.

When I encountered tasks that were tedious in Excel or beyond the abilities of Excel I went searching for a solution. After looking at VBA I eventually tried RExcel. I barely used RExcel as I found it more convenient to work in R itself. Now I use R and a database via ODBC far more than I use Excel. I can see using R as a gateway to other programming languages.

hudibras• 14 years ago

"I can see using R as a gateway to other programming languages."

So true. A fairly common path is Excel to VBA to RExcel to R to C++. R is great for exploring and prototyping but then C++ is often used to hard-wire the final R code for speed.

pietro• 14 years ago

VBA is one of the best languages and environments for beginners ever. Why using Python instead would give the beginner anything, is beyond me.

Here's a shortlist:

- VBA is case-insensitive. If you don't know the significance of this, you've never taught beginners - VBA hides event binding and namespaces for the user. Major obstacles removed - VBA has full autocomplete - VBA has F1 context-sensitive help - VBA automatically formats and indents your code - VBA is more verbose, and verbosity aids comprehension - VBA finishes most of what you type, so you don't actually have to type much more than in Python - VBA was designed for Excel, and vice versa. The object models fit - There's tons of online resources for using VBA in Excel

And finally, VBA is dead-easy to learn, even for Microsoft-hating open source hackers. You just have to learn to get over yourself.

gbog• 14 years ago

With the same argument you would easily arrive to the conclusion that the best way to learn grammar is a spell checker.

Actually it depends, if those beginners are supposed to become programmers then you're wrong, if they just want to write one piece of software once in their life and never do it again then maybe you're right.

freehunter• 14 years ago

An important part of teaching a beginner is making sure they don't get bored or frustrated. Their first language may not give them all the experience they'll need down the road, and may not be something they'll ever use again, but what you need is something that's not frustrating and gives them the idea that they themselves can actually make something. Spark the interest, and they'll want to press on.

cabalamat• 14 years ago

> VBA is more verbose, and verbosity aids comprehension

I find this unlikely; are there any studies that back up your point?

gregcor• 14 years ago

I think statically typed languages are generally easier for people less experienced with debugging, too. It's much easier to have your editor blinking "THIS LINE IS WRONG" than to try to make sense of a stack trace after the fact.

yangyang• 14 years ago

Also see PyXLL http://pyxll.com, which has been around for a while and is more comprehensive.

It lets you write Excel addins in Python in a very straightforward way. Python functions can implement functions, menu items, macros. Supports asynchronous functions in the latest Excel.

See examples here: http://pyxll.com/introduction.html

dbecker• 14 years ago

The author is trying to shoehorn this post into the "everyone should learn to code" debate, but that is a totally unrelated issue.

This post talks about switching from (VB to Python). That only happening after they've learned to code.

aaronharnly• 14 years ago

I find it interesting that the piece doesn't cite Hacker News as how the author discovered Iron Python. There is only this: "I’m glad I stumbled on to this project." For whatever reason, authors are generally good about citing other blogs as sources – even just for links, not commentary – but aggregators like HN are almost never acknowledged.

TechNewb• 14 years ago

Agreed. I imagine bloggers like John Gruber find a good bit of material on HN, and never reference it, even though he's adamant on citing other bloggers. Strange.

coroxout• 14 years ago

This is probably a net good: new ways for people to apply their Python knowledge are good, and new means and incentives for people to get into coding or make life easier are good, and Python is looking more future-proof than VBA at this point.

But the main selling point for this on the linked page and video is that the resulting code is shorter, and the code is only really shorter because the VBA code declares its variables. The video narrator even says that writing VBA is slower because you have to declare variables before use. I'm sure HN has had plenty of debates about declaring variables in the past, but personally I don't feel it slows me down much more than just thinking of a variable. Anyway, by default you don't have to declare variables in VBA, though it is good practice, especially for larger projects.

Also, that 40-line+ heap of VBA they show being changed into 14 lines of Python is rather unfair. They've written a function for finding the minimum in a range and then loop through a range when they could use worksheet functions (also accessible via VBA) for both. I make it around 12 lines of VBA if you do it that way. Or you could just use 3 worksheet functions and no macros at all.

OK, so these hypothetical beginners may not know the best way to do things in VBA - but they probably aren't going to use lambdas in Python as in the example, either. (Though I do like the possibilities of lambdas for various Excel tasks I've done in the past!)

yashchandra• 14 years ago

I absolutely love Python and absolutely love VBA for Excel apps. Eevn though it is interesting to see how Python fits in with excel, I am totally fine to keep using VBA for MS-Office apps.

Another reason you should learn to code: Python for Excel

Related Stories