Tell me more ×
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.
up vote 3 down vote favorite

I have always heard about powerfull programming capabilities on Microsoft Excel (not VBA, Excel itself with all its formulas), but have never seen it.

  • Does it really got all that power on formulas or I misunderstood?
  • If it does, what is the extent of Excel's programming capabilities?
  • How is it done (how does serious, complex programming looks like in terms of syntax, patterns, styles, etc)?
  • Does it pertains on a paradigm (say, funcional programming)?
  • Does Excel Programmers exists?
  • What is it mainly used for?
share|improve this question
1  
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
1  
@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
1  
@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
show 2 more comments

4 Answers

up vote 6 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
1  
Great! I can now scratch "learn at least one functional language" off my bucket list. :) – Yannis Rizos♦ Dec 20 '11 at 8:46
up vote 3 down vote

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
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
show 1 more comment
up vote 2 down vote

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
up vote 0 down vote

http://mumnet.easyquestion.net/tools/tools002.htm

If you have a Turing Machine on a spreadsheet, it is a Turing-Complete programming language.

share|improve this answer
That spreadsheet contains VBA macros, so all that shows is that VBA is Turing complete, which nobody doubted in the first place. I'm pretty sure that Excel without macros is not Turing complete. – sepp2k Dec 19 '11 at 20:59
@sepp2k: I remember looking at it once and thinking it was probably Turing-complete, but that was a long time ago, and I don't remember my reasoning, or for that matter the assumptions I was making. Given that Excel has hard limits on the allowable number of cells, it can't model a true infinite Turing tape. – David Thornley Dec 19 '11 at 21:26
@DavidThornley: By that reasoning, no implementation of any language is Turing Complete. If we take the same assumption on infinity used to separate finite implementation from infinite language, I think Excel is clearly Turing complete. – S.Lott Dec 19 '11 at 21:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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