State of Play: 2017/wk48

I mentioned previously I gave up on the idea of developing Android Apps, the available software requires too much ram, and I had already been using MS Excel to prototype displays, therefore opted for the use of Google sheets for Android Apps.

I’m now considering another situation. Mostly I do calculations in MS Excel, and also use a lot of vba, I probably spend as much time in the vba editor as I spend in the worksheet. So I keep looking at alternatives like FreeMat, SciLab, and python. FreeMat scripts echo calculations as they run, though this can be untidy as no need to echo simple variable assignments. The display interface in both FreeMat and SciLab is untidy, too much vertical space, and too much unnecessary echoing. Python on the other hand, echos calculations in the interpreter but doesn’t when scripts are run, to display results from a script, something has to be printed out. Programs written in FreeMat/SciLab are limited due to the way files are used for storing procedures and functions. Python offers better programming environment. However I don’t really like the syntax of python nor of FreeMat/SciLab for that matter.

Now whilst I would have preferred to program in languages like Turbo Pascal and Turbo C, practicality lead to most of my programming taking place using Excel/VBA. So Pascal/Delphi source and C source all converted to VBA, whilst Paradox/DBase gave way to MS Access. The benefit is that VBA provided a single programming language for a multitude of applications, so instead of learning multiple application languages as I was when I started out, I could just learn the one language and different object models. Programming applications saves time, simply choose the application which offers the best starting point for the task at hand and then  automate that application. I always considered that Paradox/Dbase simplified creation of data input forms, reading/writing of data files, and creation of reports: whilst most places I worked were focused on Lotus 123 or Excel: using spreadsheets for things they weren’t meant to be used for. On the other hand with Excel/VBA along with DAO, database management systems (DBMS) like Paradox/Dbase and MS Access are not as important as once were. Previously one of the benefits of DBMS was that it worked with files, line by line, whilst Lotus 123 required all the spreadsheet in RAM. So in the past I have created spreadsheets for people where by we had to grab RAM from other computers in the office just to create a computer able to build and run the spreadsheet model: a problem that wouldn’t have happened if we had used Foxbase. MS Excel however makes use of virtual memory, and so spreadsheets no longer limited to the same extent by RAM. The result is that Excel/VBA is a versatile work horse. Thus we have a lot of heritage as it were in VBA.

So the question is: is there any benefit in the FreeMat and python environments over Excel/VBA? Is it worth the effort converting the VBA code?

At this point in time the answer seems to be : No! FreeMat is for mathematics and as limited other capability. Python is a general purpose programming language, however it is a scripting language. I could just as easily convert all my VBA code into VBScript, by removing all type declarations and simply using variants throughout. I could then save to vbs files and run using Windows Scripting Host (WSH). Running WSH is not as useful as running the python interpreter, but then again I can do similar in the VBA editor (VBE) immediate window.

So the issue is: I spend a lot of time crunching numbers, and I was thinking that with FreeMat I could easily copy the working and post on the website. So when I don’t have time to write I could post the number crunching I’ve been doing. But I actually do the number crunching in Excel, so the proposed approach would require a change. Also the VBA, VBScript and chain seems like the most productive tool chain.

So I was thinking the better option would be to embed live spreadsheets in the posts, rather than static output. With the spreadsheets being saved to Google Sheets, and thus potentially viewable on an Android phone as well as useable in website posts.

So how does that work?

Embedding appears simple enough, it doesn’t however appear to be active. So good no extra effort to lock and make the calculations static. Just extra effort, if possible, to make it active. {Ok! Modified the previous and made public and editable. The embed still doesn’t appear to be editable but the link can be if I choose it to be so. With view only still possible to save file to MS Excel format onto local hard drive}

Heres another example, with the default size of the display frame adjusted:

Still inactive, as far as calculations go, but can move between tab sheets.

So it seems if I want to make online calculators available then I have to share the spreadsheets, making them public and editable. Probably just better to make them downloadable.

For the other task of displaying data, and calculations been messing around with, embedding the spreadsheet in a post seems acceptable. Only problem is I don’t use Google sheets when I am doing the work, as I work offline, using MS Excel. Still it may be useful to break the individual MS Excel workbooks up into individual worksheets. Converting from MS Excel to google sheets is simple enough, via google drive.

And as for python, and FreeMat, may aswell just use VBScript/VBA, and present such code instead of converting to python/FreeMat. So need syntax highlighter. Also as far as SMath (or MathCAD) goes, it is similar to FreeMat, good for mathematics but not much else. My interest is moving beyond presentation of calculations, to getting the calculations to do something: control machines tools etc…. For such purposes programming languages are better than mathematical typefacing, which is more for documenting calculations than performing calculations. Mathematical typefacing is also just a symbolic notation, not necessarily any more meaningful or clearer than programming language source code. Is the sigma symbol any more meaningful than the word sum? It maybe more compact than a for loop, but it also less useful, and loses meaning in its compactness. Also I don’t understand why people start with an integral and convert into a summation, when the derivation of the integral is a summation in the first place. Start at the beginning and toss the integral in the bin, no need to go there.

Probably get a lot more people interested in mathematics if adopted a new more readable notation. Well actually written formula were the traditional way: long confusing descriptive formula, hence algebra. I keep writing M=wL^2/8 all over this website, it is a meaningless formula, but relatively simple, I certainly wouldn’t want to put it into words. But none the less it is important to describe and define each of the variables. After a while however, ignore writing it out in full, and just use the formula, assuming all parties concerned understand the correct meaning. It saves time and makes things more efficient. However with computers, only need to write out the detail once. So if start using FreeMat, SMath, or python at school, then will ultimately end up with a useful box of calculation tools. Further it all becomes, heritage, so sure can download someone else’s electronic calculation pad (eCalcPad), but still need to know what it does and understand it before employ on a much bigger task: because heritage just becomes a foundation for building bigger and/or better things, not repeating more of the same.

Using MathML and other systems for rendering mathematical expressions seems dependent on unreliable “software as service” facilities rather than being built into browsers. Similarly syntax highlighting may also be a problem for presentation of source code. So some way to go, before can easily, draw, and do calculations online in a post, just as I do on paper. I could scan handwritten stuff and insert an image: though most of the time I now use Excel straight away, and otherwise draw in Acad Lt or ProgeCAD. Already seem to have too many plugins loaded for wordpress, so don’t really want to add anymore. So need to achieve objectives by adapting the tools already have.


  1. [02/12/2017] : Original
  2. [09/12/2017] : Removed edit capability from test spreadsheet