Over the years I’ve seen some really great modellers that have been built using Microsoft Excel. I also get told quite often that it would be great if these modellers could be delivered online.
Of course you can share them online with other Excel users either by email or by using a download link on a website, but quite often these modellers contain code and users don’t always trust macro-enabled workbooks.
What, of course, they really want is to deliver the same Excel experience online and so they have two choices which we’ll cover here.
There are places that can take a workbook and with a few modifications deliver it on the web but before you say great we’ll do that then, this is only a reasonable choice if it’s not too calculation intensive. Basically, if opening the workbook in Excel from cold (without any pre-processing), changing an input and recalculating takes a long time then this is too calculation intensive. The amount of time it takes opening, changing one variable and recalculating is the same experience you will be delivering on the web.
This means that your workbook must not contain any macros (VBA) that change the state of the data to be used by the calculations as they will run stateless, i.e. always go through the sequence of opening, changing the variables and recalculating. However, you can have VBA functions that return values to be used in your calculations.
Let us assume that it is not too calculation intensive, you will need to make sure your workbook has clearly defined inputs and outputs. These will normally be added to separate worksheets so it has a sheet of inputs and a sheet of outputs, although charts can be outputs too.
So, if you are prepared to modify the workbook, it is not too calculation intensive and the workbook doesn’t need to cache any data to perform its actions then this is the fastest way to get your Excel modeller online.
The alternative is to have your Excel workbook rewritten as a web app (or mobile app if that’s your preferred delivery mechanism). This obviously takes a lot longer than top and tailing a workbook as a developer will need to convert your structural logic, formula, macros, data entry input, tables of factors and your output (quite often charts) into another language.
This is very time consuming because people that write Excel workbooks tend not to have experience in writing and maintaining software code. There are things you can do to help the developer, which will speed up the development and lower the conversion cost.
- Have a written process of how to use the workbook. This could be telling the user where to paste the data, what buttons to press in what order to perform the functionality that is required. A step by step written process saves the developer from having to work out (or guess) what needs to be done.
- Make sure that nothing is locked/hidden away. If there are parts inaccessible to the programmer they won’t be able to code them. You should either remove all passwords (Workbook, Sheet or Module) or make sure you provide the passwords so the programmer so they can remove them if needed.
- Separate out your logic. If you have discrete areas (sheets) for your inputs, outputs, tables and functions that goes a long way to helping the programmer as that is how they will be coding it up.
- Use names or named ranges rather than cell references. This will take it easier for the programmer to follow the code especially if the cell references or named ranges are given names that have a meaning. Have a look at “Using cell reference names and named ranges in Microsoft Excel“.
- Try to be self-contained using features available natively in Excel or if you need to use another library then provide the code for that library. If this is not possible then the programmer will need to know how to perform the actions from the library manually.
- Remove anything that is not required. Old bits of code that are not required or tables of data kept in the Workbook for historic purposes should all be removed. You should give the developer as clean a copy of the Workbook as possible because they will either be coming back with questions about what to do with the extra stuff that doesn’t fit the normal workflow or they will try to code the extra stuff and that will be costing you more.
I’m sure there are plenty more but it’s been nearly a year since I last had to convert from Excel to the web so I might have forgotten a few. If you have any more tips to add then get in touch and I’ll be happy to include them.