Things move on.......

Progress this week has been to map out the high level program overview, a view of the decision flow and a start on the data model - pretty impressive work by Roy!

The main aim of the program is to populate a set of event trees related to the frequency and impact of various fire scenarios - as illustrated below.

A combination of user defined macros build the branching frequency and the VBA code to determine the consequence for the various scenarios.

We plough on......

Just when you thought it was safe to come out!

Finally managed to get a day to review where we are with things - Roy has been doing a great job unpicking the code.

We have 12,000 lines of uncommented VB code (that means none... nada.... zip comments!)  on top of that, the code is littered with numerous IF THEN's none of then coded with indents making it a nightmare to follow what is going on. Then you have data items that are renamed and renamed again from routine to routine........arghh!

The convolution is worse that imagined - as illustrated below in a snapshot of one of the main routines! 

Poor software engineering practices have meant that there is no chance of us every being able to maintain this code going forward!

What to do next.......

Came across this...

Been on holiday this week - so limited news - though am assuming 'others' have progressed things - will find out on Monday!

However, in a trawl of Google I came across this article which looked interesting;

From, Heat Transfer Today –R.J. Ribando - web ref

http://faculty.virginia.edu/ribando/modules/xls/VBAPrimer.pdf

with quotes as follows:

" In the past, even mildly complicated engineering calculations have not mixed well with 
spreadsheets because of the very strong tendency to wind up with nearly-impossible-to-debug 
"spaghetti code". .......... However, by using Visual Basic for Applications (VBA) in conjunction with the Excel spreadsheet, the user has the convenience of a spreadsheet for neatly-formatted input/output  and for graphical display of results, i.e., to function as a graphical user interface (GUI).  Meanwhile well-structured, readable, line-oriented code can be used for the more complicated calculations...........


Conclusion

Student response to use of VBA within other courses, both undergraduate and graduate, 
has been excellent.   Since all have had a structured programming course earlier (some in Fortran 
90, some in C++, others in Java), the migration to VBA is very straightforward.   Most welcome 
the opportunity to add another package to their inventory of proficiencies.  Several graduate students in a recent programming-intensive computational fluid dynamics (CFD) elected to do all 
assignments using Excel/VBA."

so there may be hope yet!

Except there is also a disclaimer;

" I am not a computer scientist or programmer, but rather an engineer.   That being the 
case, I have found VBA to be a quick and easy way to do most of the things I want to do in my 
teaching and research.  Code that you will find described here or available on the website is not 
guaranteed to be error free or even to follow good programming practice."

or - beware even expert engineers can muck up using this environment!

The journey continues .... ;)

First contact......

Not by me though :(

Not had time to do much other than a bit of Start Trek management "make it so" - thanks to Roy and Ian for this weeks efforts.

QA on version control started and first view of functional flow of the code starting to appear. This seems to me be a complete rats nest of logic switching between Excel sheet's to macro's (built in and user defined) to VBA code. How on Earth anyone could contemplate this sort of structure in the first place is a mystery to me. What do they teach on spreadsheet training courses! Then again I can probably guess.

Not only that - we have had to write a code to decode the code or in Roy's words;

"Source code formatting poor - created a small utility to auto-format so that (very) excessive conditional logic nesting could be traced"

Engage....

Version - controlled!

We have the server sorted (thank you Roy, Matt and Steve)!


The plan now is to put the various offending codes under strict version control for which Ian

http://www.ianbradshaw.co.uk/

had the answer. In his words;


"Depending what we want to do ... a Free version control such as Subversion may be easiest. This will also link nicely to windows world via TortoiseSVN so that it can be done cross platform. It's a bit less automatic than Microsoft solutions, but can cope with any language."


Sounds a bit geekish to me ;)

Will update on if its any use later......onwards and upwards!

Slow progress.

Slow going this week - lot of other work pressures getting in the way - all good client stuff so can't complain too much!

Just in the process of putting in place the in-house development environment to allow proper version control, documentation and rebuild of the models.

Currently looking for a good auto-documenting method for VB code - they do exist! also need to think through what we are going to convert these models into. Must be web-based - can't be doing with handing out dongles - these days must be a more elegant way of distribution and control.

The language that seems to be rising to the top in our discussions is Perl - know absolutely nothing about this language but am assured its 'very simple'. Need to convinced that it can handle the QRA consequence modelling though - don't want to spend the rest of my life re-writing mathematical library functions (that will also need to be verified!)

All good ideas welcome........

Unbelievable.....

Have now had time to do a bit of research on the topic and a bit shocked to tell the truth at the level of complacency about the use or 'spreadsheets' for complex calculations.

I have started to look into one of codes that I will be looking to convert into a more structured environment and its not pretty. Rather than it actually being a spreadsheet it is in fact a large Visual Basic code! All Excel is being used for is essentially input and output of data. Which in some ways is good because at least there is a chance of understanding the code operation. But on the other hand is bad in that this code has not been 'designed' but, and there is no other way of saying this, hacked out over a period of years. I am now finding that this is a common occurrence!

Why on Earth do people believe this is an appropriate way of developing what has turned out to be a very complex piece of software? It's sort of akin to doing DIY on your house - add a bit here, chop a bit there - but then again for the safety critical jobs eg the electrics you still need a qualified electrician to certify what has been done is OK - not so with hacking out code hidden within an Excel wrapper.

The other issue I have come across is, for some people, if you say a model has been developed in Excel they seem quite happy to accept this without question. It's as if just by using Excel you have proved that what has been done will be OK for use - we all know Excel don't we, come on, can't be that bad - kind of thinking!

Personally, letting people loose on Excel for complex calculations scares the pants off me - and others - check out this link to see the full extent of the damage that can be caused!

http://www.eusprig.org

BUT wrapping, what is essentially a VB code, up under an Excel umbrella - which as noted above, if you say its an Excel model you can go a long way without having to declare what you are really up to - is a bigger crime. Especially when committed by engineers - who should know better!

Stand down off soap box and CONTINUE ;)