Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have always heard about the powerful programming capabilities of Microsoft Excel (not VBA, I am referring to the formulas of Excel itself), but have never seen it.

  • Does it really have all that power on formulas or have I misunderstood?
  • If it does, what is the extent of Excel's programming capabilities?
  • How is it done (what does serious, complex programming looks like in terms of syntax, patterns, styles, etc.)?
  • Does it pertain to a paradigm (say, functional programming)?
  • Do Excel programmers exist?
  • What is it mainly used for?
share|improve this question

closed as too broad by MichaelT, gnat, GlenH7, Dynamic, amon Apr 25 at 14:13

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

2  
What part of this: msdn.microsoft.com/en-us/office/ff458124.aspx was confusing? Can you provide specific quotes or links that confused you? –  S.Lott Dec 19 '11 at 19:22
2  
@S.Lott: Actually, I was talking about Excel's built-in functions, it's own IDE, etc, not about .NET using Excel, or VBA on Excel, or Excel's C API, or any other language using Excel. I was inspired to make this question by this link: info.ucl.ac.be/~pvr/paradigmsDIAGRAMeng108.pdf. The green square in the middle of the image counts Excel as a programming language, that's what I was curious about. –  Raphael Dec 19 '11 at 19:34
    
How about this? There are -- seriously -- hundreds of sites with relevant information. microsoft.com/learning/en/us/…. Please read this and then ask specification questions after reading. –  S.Lott Dec 19 '11 at 19:39
1  
@S.Lott The bullet points in the book from your second link make the book appear to be primarily about VBA. The 8 bullet points describe content; of them the only one I'd expect to be something else is #4 generate ad ins (I assume this is VSTO). –  Dan Neely Dec 19 '11 at 20:55
3  
@S.Lott: Seems like you were the only one who couldn't understand the question. I don't want to know ONLY if it is Turing Complete, or what the SUM formula does. Still, there are clever and collaborative people who DID understand the question and, fortunately, you are not the ultimate and only source of knowledge about Excel. If you think the question is bad formulated (I bet it don't), you could just pacifically ask for clarification in a comment. If you think the question is not worth answering, then just don't answer it. Your unecessary aggressiveness is just sad. –  Raphael Dec 20 '11 at 11:50

3 Answers 3

up vote 11 down vote accepted

While most people don't call it programming, technically excel spreadsheets comprise a functional programming language, and like most functional programming languages, it excels (pun intended) at numerical calculations. Each cell contains a constant or a formula, which is a pure function since it has no side effects. Whenever a cell is changed, the "program" executes. Selection is done via functions like IF or HLOOKUP. Sequence is determined by formula dependencies, and "iteration" is done via a fill.

There are also more advanced methods of doing iterative calculations, such as goal seeking. You can assign "variable names" to ranges of cells. There are add-ins like the solver that give you more advanced capabilities. One of my undergrad engineering classes was taught with excel as the official modeling language.

You won't find people advertising on their resume that they're an "excel programmer," but a lot of business people consider it central to their job.

share|improve this answer
3  
Great! I can now scratch "learn at least one functional language" off my bucket list. :) –  Yannis Rizos Dec 20 '11 at 8:46

To get a better notion about the capabilities of Excel, you may read this article

http://www.cpearson.com/excel/ArrayFormulas.aspx

about array formulas. This will give you a short introduction about what is possible without VBA.

The best collection of such formulas I know of is only available in german:

http://www.excelformeln.de/

Sadly, if you don't have a german Excel version, even the formula "source code" has to be translated, so I guess this may only be useful for german Excel users.

share|improve this answer

What is the extent of Excel programming capabilities?

Excel programming can be looked at from 4 different ways:

  1. Code written directly in Excel environment (commonly called macros)
  2. Code written from other tools to access Excel files via its object model
  3. Code written from other tools to access Excel file via an object model similar to Excel but does not require Excel on the run machine
  4. Code written from other tools to access Excel data as CSV files

All of the above are desktop solutions that are capable of manipulating information in files. Solutions using the approach in 1,2,3 can also manipulate graphs inside Excel files and run Excel functions and hence extending the source language functionality if so is desired. As for the code written in Excel environment, it is a large subset of VB6 and it could read/write data files, and access databases and use COM objects as well as manipulate sheets. You could also use it for GUI development but it lacks several important controls such as DataGrid. You could add such components as COM components if you like.

Visual Studio Tools for Office (VSTO) is a set of tools for the C# and VB.NET developer. It primarily aids in extending the user interface of Office 2003, 2007 and 2010 applications. Using the VSTO templates the developer can create managed code COM Add-ins for many Office applications. For Word and Excel, VSTO also offers a managed code alternative to VBA in the form of document-level customization templates. For more information see this link:

VSTO Resources

How is it done (how does serious, complex programming looks like in terms of syntax, patterns, styles, etc)?

See the answer to the above question. Also, you can take a look at Excel Object Model and at VBA Programming

Does it pertains on a paradigm (say, funcional programming)?

This depends on the programming style you use. If you use a C#.NET programming language to manipulate Excel file, then you can use capabilities of C# OO. See this for example: Excel Office Automation

Does Excel Programmers exists?

Yes it does - Lots of organizations have 'macros' and soem developers are hired to write macros in Excel (Usually referred to as Desktop Automation Tools).

What is it mainly used for?

Excel is a very powerful tool. Organizations use it often in every day work without having to go to IT. Some IT departments (and web sites) send CSV files or Excel files to employees and let them work with the data directly for their reports. This may call for the need to create charts, add formulas to reports and manipulate files using merge and complex sorting and formatting operations. In many cases, you store your tabular data in Excel sheets instead of a database, after all, not many non-IT staff know how to do a left outer join. As a result, some automation is called for to do some of the previous tasks.

Two things to watch for when you are programming against Excel:

  1. Excel Version.

  2. Deployment may require MS Office Dlls that may not be installed (but can be obtained for free from Microsoft).

share|improve this answer
    
Nice. I like the way you laid this out. On a sidenote, if I may comment on the last paragraph, it is my opinion that Excel is probably the most overused and * cough * abused software in the business. Many people get to the point in their learning where they use Excel as a database. This is where things start getting funky, Excel does not excel at managing data relations. I have often compared this to planting a nail with a rock, it does the job but is not the best tool. –  stefgosselin Dec 19 '11 at 20:55
1  
You answer is a good one, but you missed the fifth part: Excel formulas, including array formulas, without any VBA. The question was specificially on that case. –  Doc Brown Dec 19 '11 at 20:56
    
@stefgosselin, thank you for your comment. You may be correct indeed. But this leads to the issue of "controlling user tasks with data"...In the early days of SQL, some people thought that SQL will take over IT! In today's world, corporations must have an evidence of data correctness because users could play with them in a harmful fashion either intentionally or otherwise. –  Emmad Kareem Dec 19 '11 at 20:57
    
@DocBrown, Thanks for your comment. How did I miss this? I should be more careful next time. –  Emmad Kareem Dec 19 '11 at 21:01
    
@EmmadKareem, thank you. Althought your answer missed a little the point, its a nice and easy-to-understand overview of how Excel is present besides being a simple spreadsheet. +1 for that. –  Raphael Dec 20 '11 at 11:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.