You can do a lot with Excel. Even without Visual Basic Script. But the data and the logic is in one file, whereas normal programming languages usually have another file (or a database) for the data.

This feels wrong to me, but it might only be that I'm not used to it.

Is this mixture of data and logic a problem? If so, where exactly is the problem?

  • 3
    I would say it depends on what you are doing. Can you explain the real problem you are trying to solve? – Stewart Ritchie Jul 26 '17 at 7:31
  • So when you download excel, do you expect it to come with your data? – JimmyJames Aug 25 '17 at 14:56
  • @JimmyJames I don't know what you are talking about. With "Excel", do you mean the excel file? By "download", do you mean when I open the file? Or do you actually mean downloading the program? Why should I expect the program to come with my data? Why should I not expect the file when I open it to contain the data? – Martin Thoma Aug 25 '17 at 15:01
  • When you download the program 'excel' do you expect it to come filled with data? Clearly you don't. How do you expect MS to develop that piece of software if they don't separate the data from the logic? Reductio ad absurdum – JimmyJames Aug 25 '17 at 15:05

Excel has VBA, Visual Basic for Applications, not Visual Basic Script. There are differences.

Your question is quite hypothetical. Excel is an application that can be used to work with large amounts of data in one file and process that data in that one file.

That does not mean that data and processing cannot be separated. In fact, good spreadsheet architecture will indeed separate data from processing.

In modern Excel versions, Power Query (Get & Transform in Excel 2016 and up) can be used to load data from various sources, clean it, shape it, and load it into the Excel data model for further processing with Power Pivot or with worksheet formulas. Data and processing are perfectly separated.

But Excel can also handle much simpler models where end consumers manually enter data and use formulas to do data processing. For example an income and expense ledger with a running total. Given a starting balance, manually entered data for income and expenses will calculate a current balance, all in one sheet.

In that case, the mix of data and processing logic is not a problem at all.

Without proper guidance, many Excel users will put data and processing on one sheet, for example, sales activities for one month. They will happily create a sheet for each month and then struggle to find a way to report across the many sheets (or many workbooks for many years with many months).

But that is a problem only because it is bad data architecture, not a fault of Excel. With good data architecture in Excel, all sales data can go into one sheet and the processing and reporting can be done in other sheets.

Excel is wonderfully versatile. You can use it to bung things up royally. Like enter only text into cells and use manual cell fill, say a shopping list, and manually fill cells green for stuff that you have bought. Then try to calulate the total of all green cells, which you can't do without VBA. That's the most basic level.

But you can also make it calculate in the blink of an eye what a single person would take months to calculate manually.

So, the answer to your question "Is this mixture of data and logic a problem" is "not really". It depends on the person who is creating models in Excel. Their experience and understanding of data and processing, and why it needs to be separated, and how it can be separated.

The problem is in people's Excel skills. Only a small fraction of Excel users even understand what I've written up above, before we even get to their ability to apply it.

| improve this answer | |

One obvious problem with mixing data and logic in a single file is that it makes version control more difficult.

Whether or not this is a problem depends on the scale of the project and the frequency of changes.

Changes to the logic might be hard to find among the probably more frequent changes to the data.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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