Programming Excel

Question

I'd like to give my boyfriend a taste of programming. If it was up to me, I'd teach Scheme, Haskell or F# but since he'd rather learn something that can be useful in his job as a fiscal consultant, i.e. programming Excel.

What are the options for programming Excel? Which one would you recommend for someone who's just learning to program and yet wants to get things done? Are there any books / resources you recommend for him (advanced Excel user, beginner programmer) or me (intermediate Excel user, advanced programmer)?

Thanks. I've started a blog about programming Excel to document our findings.

Answer 1

You should do the best of both worlds and teach him to program excel in F#. By the time he catches on that he should have been using VBScript, it will be too late.

Answer 2

Macros are one way to program Excel. Check out this YouTube video: http://in.youtube.com/watch?v=k_fWcffRAG4 It demonstrates how to Record and use Macro.

Answer 3

There's basically two big options: he can write VBA code (macro's) or .NET code.

VBA code, being built on a simple language, has a low threshold. But if he's interested in programming in general, he might be much better off learning .NET (preferably C#). It's much more powerful than VBA and will teach him a lot more about proper software construction and essential concepts behind it.

I think it would be best to have him program some silly little Winforms applications first to get the basics down, and then look in to Excel interop. You can find a bunch of examples of C# / Excel intererop code by googling for "C# Excel". Here is one example:

http://dotnetperls.com/Content/Excel-Interop-Performance.aspx

Answer 4

You can do a great deal with VBA, probably more than someone in finance would ever wish to do. VBA is intended, I believe, for just such people, but it generally gets bad press. I think that if you, a programmer, taught your boyfriend VBA, he would develop some understanding of programming and also have to hand an extremely useful tool for work, far in advance of anything that can be achieved by recording macros. In addition, it is a very short step from VBA to VBScript.

Answer 5

VBA is VB6, in terms of runtime at least. It has enough OO capability to allow understanding of encapsulation and (sorta, without wanting to start a religious war) polymorphism/inheritance, if you'll accept that interfaces provide some aspects of those. If he spends a lot of time in Excel, then I'd expect there to be some repetitive things he does at present that could benefit from recording a macro, then learning to clean it up. That can lead to writing his own worksheet functions, loops, all kinds of useful stuff. If he's genuinely advanced (pop quiz: does he know about putting formulae in names? Is he comfortable with array formulae?) then there will be things that he's annoyed with where VBA can help.

I think I'd ignore .Net for now, it's too big and it will take too long for him to be able to produce his own solutions.

Alternatively, just teach him Ruby. It's what I'll be starting my kids on when they move on from Scratch.

Answer 6

VBA is a great starting point for someone new to development and wants to get a handle on the basics. It is possible to program Excel through many different languages by leveraging the Excel or Word COM libraries. This can be done easily in languages such as C# but is not suitable for someone starting off with programming.

Just word of caution. Although VBA is a fully featured programming language and is capable of supporting complex applications it can encourage poor programming practises. I have seen this occur in large organisations that use Excel extensively for actuarial calculations. Essentially the code was written by actuaries with finacial calculations in mind - not software engineering.

In the long run enterprise programming languages such as Java or C# are better.

Answer 7

As a long time (business) Excel user and programmer, I recommend starting with a good VBA book that focuses on the key features of the Basic language, and how to communicate with the Excel objects (workbooks, sheets, etc). I wouldn't worry about userforms, initially - I haven't used those for years - and I wouldn't worry too much about databases at first either.

I suggest focusing on two areas in particular, starting with the Basic language itself, writing output to the Immediate window with Debug.Print and ignoring the fact that Excel is attached. You can get very good practice in this by solving some of the Euler problems, which favour pure code solutions with no user interface.

After that, I would learn how to talk to the Excel objects, and read as many Excel forums and web pages as possible to pick up tips. For example, it is much faster to read and write many cells at once, than one at a time.

And yes, VBA is not as full featured as most 'professional' languages, but it has one crucial advantage over most of them - it is much easier to write and for the average user to check, modify and maintain - and checkability and flexibility are crucial in the business world. Together with Excel, it is an amazing business tool.