The Old Joel on Software Forum: Part 4 (of 5)

Gotta learn Excel.

At the risk of feeling stupid, I would like to ask everyone. I need to learn excel as part of my job, while I'm ok at doing the basic data spreadsheeting, I needed to start doing more serious work on excel (risk management, etc). My background is mostly Unix/Java/Script. Is there a book or place I can start learning this stuff?
From the project plan, it seems that we are doing an complete application using that ties Excel to our unix numeric processing application. I was thinking to learn VBA to tie our C/Python module. Is this even doable?

Thanks

Unix2M$
Saturday, May 15, 2004

If you have some exposure to Functional Programming, Hard Core Excel should not too difficult to pick-up. You will have to get to speed on VBA if you want to expose any of the data outside the sheets.

But whatever you do, exploit Excel's abilities to the fullest. No point writing VB functions fro Binomial Distributions.

Resources I've used include MS's support forums at MSDN plus Excel's help itself.

KayJay
Saturday, May 15, 2004

Thanks, yes, I don't plan on writing numeric processing part in VB, most of them are done on unix server. I don't think I'll have a problem learning the language. I found VBA and Excel is easy yet very bad programming style. There are several books on amazon, but none of them seems to help you with programming VBA and link up with other modules (C or C++).

Unix2M$
Saturday, May 15, 2004

Compile the C code into DLLs and call them from VBA.

KayJay
Saturday, May 15, 2004

On rewriting functions for binomial distributions: that's actually not a bad idea, as Excel doesn't necessarily do it correctly!

http://www.stat.uni-muenchen.de/~knuesel/elv/excelacc.pdf

scruffie
Saturday, May 15, 2004

You can automate Excel from any programming language that can call COM objects. You can also get Visual Studio Tools for Office, which lets you automate Excel from .Net and tie the underlying dll to the spreadsheet, making it a "smart" spreadsheet.

That may be overkill for what you need to do - I'm just sharing the info.

On the lighter side, you can also point Excel at all kinds of data sources - that may be all you need, if you can get at a data source that's close enough to perfect.

Not sure if this helps - hope it does.

Philo

Philo
Saturday, May 15, 2004

Scruffle,
http://support.microsoft.com/default.aspx?kbid=828888&product=xl2003

Seems to have been addressed.

KayJay
Saturday, May 15, 2004

This is a real handy MSDN page that shows how to automate a lot of common tasks in Excel:

http://tinyurl.com/2dl4o

Also, since the statistical calculation bugs in Excel were fixed a long time ago, it would be a waste of effort to write your own, IMHO.

yet another anon
Saturday, May 15, 2004

You can automate Excel directly from Python using PythonCom

http://www.boddie.org.uk/python/COM.html

Ged Byrne
Sunday, May 16, 2004

Heres the course of action I would recommend as a quick start:

1) Install Excel and load it up. Make sure you do a custom install so that you can include the programmers VBA help files. They are invaluable.
2) Do the task manually.
3) Record yourself doing the task using the recorder in Tools/Macros.
4) View the generated VBA. Mess with it, paramaterising it into a general function.
5) Convert the VBA code into Python code using Python COM.
6) Repeat and rinse.

Ged Byrne
Sunday, May 16, 2004

And of course, don't forget to consider OpenOffice.org as an alternative to Excel.

Ged Byrne
Sunday, May 16, 2004

Here are some good weblinks for learning Excel/VBA.

http://www.vbforums.com/attachment.php?s=310151b93e1002de54668f75b218c29a&postid=1691068

Sathyaish Chakravarthy
Sunday, May 16, 2004

Our company has an Excel add-in that where most of the "meat" is in C DLL's. We use the same C code on UNIX.

Our VBA layer is very thin (which it can be because most of the work is in the portable C libraries).

njkayaker
Monday, May 17, 2004