-1

In an engineering company, there are many engineers that edit in Excel/VBA Spreadsheets to perform some engineering calculations.They all do more or less similar things: calculate, manage engineering stability, buildings, infrastructure etc.. They perform calculations based on data and formulas. Or draw a picture of uncommon case based on the given data in the sheet.

Surely, Excel Spreadsheets is easy to write, copy-paste, print. But they are unsafe and hard to maintain or centralize.

Some of that "home-grown" applications became mature, and the head office should decide to transform them into real application(s), to be able to protect and better manage them.

Another problem is often the authors of such Excel files are the only people that understands what is happening behind the scenes... and if the application became popular inside a company, this can create problems if that person moves from the company in a way or another... a popular corporate application can't be maintained by one single person.

So the problem is, how can I better transform these kind of applications? Here are some options:

  • transform one by one, one sheet to one application, or
  • group in one all together
  • as a desktop or
  • a web-application...

Now, there may be a way to let the authors for this Excel data to be allowed to contribute (without the need to learn additional programming language).

Does anyone face similar problems, maybe reflected by the architecture of the transformation of Excel/VBA "application" to real "applications"...

3
  • 3
    My limited experience is that your application won't do something they want, so they'll write a new Excel spreadsheet to do it... Dec 18, 2020 at 18:47
  • 5
    Also your reasons to not use Excel spreadsheets are not as important as you think they are. Dec 18, 2020 at 18:48
  • @user253751 like security reasons? there is not possible nor to protect an excel sheet from modifications, nor from unauthorized distribution. also the maintainability, version control etc...
    – serge
    Dec 19, 2020 at 12:36

2 Answers 2

4

You tackle this problem like you would tackle any other software engineering problem: by gathering requirements, designing the software, writing it, testing it, and so forth. Having spreadsheets that already do this just means that some of the requirements gathering has already been done for you.

The manner in which you organize the software is where the design process comes in. Collectively, this is known as the application's architecture. As you've pointed out, there are various ways to organize software, but unless your spreadsheets demarcate different departments in the company (and even if they do), it's most likely going to be a single application. It really just depends on what the size and scope of the project is.

I wouldn't rely on the folks who created the spreadsheets to help you write the software. If they were software engineers, they would have already transformed the spreadsheets into an application. Use them as your stakeholders/business analysts.

2
  • the idea of contribution of the excel sheets creators was not about participation to the portal building or architecture, but rather to the portal "extensions", if they have new ideas of "sheets", to implement them as "plug-ins" to the existing portal, or something in this genre
    – serge
    Dec 18, 2020 at 17:32
  • 1
    Making software modular and extensible is almost always a good thing. Dec 18, 2020 at 17:33
4

I would question the very premise of the question that Excel/VBA is "unsafe", that it is a "home" application, or that it is hard to maintain when applied to appropriate problems.

Excel and VBA is widely used by professionals in the largest corporations, and (due to the ubiquity of Excel and its built-in VBA editor) it is amongst some of the quickest and easiest code to produce and maintain for one-trick-pony type calculators, utilities, or reports.

One of Excel's main advantages is its accessibility to people whose main role is not programming, but whose role has some routine or repetitive data processing element which can be automated, or indeed some graphical rendering element you also mention.

So on conversion to a "proper" application, which is often slower, more bloated, with a UI that is perhaps more badly done, and actually far harder for the end-user to maintain or have changed, one may ask why you would wish to do it?

Has the complexity grown to a point where only experienced analysts and developers can be expected to handle the code at all, rather than supporting the individual or department within the terms of existing practices using Excel?

Are there any other specific problems emerging, rather than a vague sense that existing practice is unsafe?

If you do decide to convert, realise that reproducing the full UI functionality of Excel in a desktop application will be difficult, and to do so via a web page more difficult again.

And if there are still some Excel tools used, then now your staff have two places to look for the relevant tool rather than one, using two user interface languages, and based on two different coding platforms.

26
  • 2
    I upvoted this. Excel/VBA is not "unsafe" or "hard to maintain" per se. But to be fair, Excel sheets created by "people whose main role is not programming" have a certain risk to become hard to maintain (not because of Excel, but because of people who are not trained in writiing maintainable software).
    – Doc Brown
    Dec 19, 2020 at 13:45
  • @DocBrown, agreed, I've encountered spaghetti code in Excel many a time. In such cases, a proportionate solution might be to share good practices with the creator, to refactor to a better standard, or to formalise and document any muddled analysis. Or it might just be to verify that there is a fallback (such as a paper calculation or a written procedure) if their code fails, and let them get on with doing their best.
    – Steve
    Dec 19, 2020 at 15:14
  • 1
    I think firms often over-estimate the value of code written in lightweight ways by non-IT staff such as with Excel, as against the swingeing costs of banning automation tools or of having everything done "properly" by a central IT department, and against all the other ways in which supposed trade secrets can already leak out of the firm. When firms become obsessed with controlling the information necessary for the execution of people's jobs, they more often seem to forget their own trade secrets and fail to reproduce their expertise internally. (2/2)
    – Steve
    Dec 20, 2020 at 9:45
  • 1
    @Serge: tell me something I don't know. I am not saying Excel should be abused as a replacement for proper application development where proper application development is required. I am just telling you Steve's answer is 100% on spot - one should not convert any Excel based spreadsheet with some VBA code overhastily for ideological reasons into something which does not really fit to the requirements of the business any more. This is a step which has a cost, and the cost must be worth it, not just from the IT side, but also from the business side.
    – Doc Brown
    Dec 26, 2020 at 23:18
  • 1
    @Serge, the thing to understand is that the process and knowledge of key staff members is also not versionable or editable by a group of people (except by directly working with the relevant staff member), and almost nobody in a business will even know what GitHub is. If an Excel sheet is popular, creating and maintaining it may already be consuming a significant percentage of the creator's working time, not just on the programming side (which may be sloppy and done with very little overhead), but on the analysis side (which will tend to be in their heads and also with little overhead). (1/2)
    – Steve
    Dec 27, 2020 at 6:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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