Sunday, 16 February 2014

Summary of Posts on my Journal about Software

Most of my spreadsheets can be found at ExcelCalc's, with sample printouts of some of the spreadsheets found over on my scribd profile. Since my spreadsheets largely make use of visual basic for applications (vba) via the use of a central add-in technical library (schTechLIB), and functions also use data access objects (DAO), along with MS Access databases for section properties as well as a central Excel spreadsheet, the spreadsheets are not so easy to set up and use. Consequently I have written some posts over on my Metamorphs Journal {Metamorphs = Beyond Structures} to provide further background. I have also been making other spreadsheets available via my blog. ExcelCalc's is really for presentation of calculations via the use of XLC, where as my primary interest is putting the calculations to work behind the scenes.  Presentations of calculations on a printed page has relatively limited options, where as user interfaces and user experience requirements produces calculations in a multitude of different forms. {ExcelCalc's policies therefore largely prevent me from replicating what is already there: seemingly trivial stuff I don't need to download, as been there done that, and created myself many years ago, but also not able to upload my variations either. Well actually I probably can upload, but that may then spawn a debate about replication: I don't want to go there. ExcelCalc's is largely a repository of calculations and/or formula rather than tools to perform the calculations.}

Designing the user interface is also not my domain, its the behind the scenes number crunching that I focus on. Also it isn't one-off calculations or point-value calculations which interest me, but calculations across some domain. So not the point-value calculations to design a single structure which is of interest, but the limitations of the available structural materials for a given structural form. For example what is the maximum envelope which can be provided by the available cold-formed c-sections in the form of a rigid moment frame? What is the largest practical parallel chord roof truss that can be fabricated and installed? How do these limitations relate to manufacturing economics? These are interests and questions, actually answering them is another matter.

Any case here are some links to the posts written so far on spreadsheets and software:

Plane Frames

  1. Plane Frame Analysis
  2. Plane Frame Analysis Front End
  3. Plane Frame Analysis Alternative Front End
  4. Plane Frame Analysis Front End Version 4
  5. Plane Frame Analysis Back End

Other Structural

  1. Barriers Design
  2. Steel Design AS4600
  3. Materials Library
  4. Simple Shed Calculator for fixed base Gable Frame, using Kleinlogel Formula
  5. Soil Heave Calculator
  6. Contents of schTechLIB

Technology and Calculations

Created a new blog to provide additional background information and usage instructions for my spreadsheets over on ExelCalcs. Just about all my spreadsheets make use of schTechLIB (.xls or .xla) at least the ones uploaded to ExcelCalc's. This is a function library, it contains Excel user defined functions (udf) for various Australian materials and loading standards. These functions deal with all the conditional tests in the codes and select the appropriate expression to evaluate.

I use vba because it is more readable and easier to maintain than complex worksheet formula, additionally many of the functions started their life written in either Turbo C or Turbo Pascal. I like solutions which are usable across multiple applications. A worksheet formula can only be really used in Excel, whilst a vba function can also be used in MS Access or Word, or in various CAD packages, and functions are not so difficult to translate to other programming languages such as Delphi, and typically can be used with out change in  As I have written in many other posts calculations are a means to an end. For me presentation of calculations in an Excel worksheet or else where is a pretty useless end. Whilst I was studying and when I graduated, I wanted to present the calculations, and something like XLC would have been great tool to have. However, calculations are typically an imposition placed on people by regulations and an obstruction and hindrance to people getting things done. An obstruction which was not there in the past. Whilst I consider it is necessary to demonstrate something is fit-for-function and suitable-for-purpose before it is made, I still consider that the imposition and delay caused by getting others to produce calculations is an obstruction to be removed. Even building and testing prototypes is dangerous if no rational assessment has been carried out based on scientific knowledge: which at the end of the day is little more than past experience and observations of the world and nature with possibly some mathematics thrown in to provide to measures to quantify what would otherwise simply be qualitative understanding.

So I create spreadsheets and write software as a means to break down that barrier, that obstruction to getting things done. However thus far all my effort has been into cutting the time frame, I still do the structural design work, and I still represent a barrier to be removed. Not removed altogether, but removed from the individual project, removed from the individual question that someone may have.

Answers to engineering questions delay drafters getting on with producing drawings and documenting a project, and delay fabricators and builders getting on with building. For something which is truly original and novel this is not an issue: however the vast majority of engineering calculations are not applied to anything which is truly unique and original. Unique and original requires determining the calculation or mathematical model, it does not involve multiple iterations of known mathematical models: it does not involve writing expressions down, stuffing numbers into and evaluating the numerical result. Unique and original requires developing a new mathematical model based on available fundamental theory or otherwise doing the science to develop a theoretical basis for design. So my basic view is: if doing real engineering then starting with a blank slate and no national standard and no formula in any handbook is of any use.

If we get away from engineering (as I perceive it to be), then we are concerned with established technologies and the need to adapt and modify these technologies to better suit our needs. The more typical task therefore is adopting, adapting and applying established technologies. We ADOPT an existing technology which is near suitable to our needs, we ADAPT it to better suit our needs, and then we APPLY that technology to meet our needs. All established technologies provide a heritage and a foundation up on which we build current and future technologies.

In case not immediately apparent, society itself is a technology. Technology is merely a set of tools and techniques we employ to get something done. To foster cooperation, to achieve economy of effort, humans developed their societies and their cities. But globally we have shortages in the up take, implementation and application of the established technologies. Shortages of water, food, housing, schools and hospitals to name a few. Much of this is caused by poor planning, design and management. But another major obstacle is empire building by occupational cliques. When knowledge isn't shared and it becomes a commodity then lives are placed at risk: buildings, bridges, machines they all start to fail when least expected. The failures are not unpredictable, the failures are typically avoidable if the right decisions had been made by the right people, and those people had access to the appropriate knowledge.

If something is in a national standard then that knowledge is not intellectual property (IP), the actual form and presentation of that knowledge may be copyright and IP, but not the knowledge itself. If someone is hindered in what they want to do by a national standard then they need to be enabled and empowered to remove that hindrance and comply with the standard. Compliance is there for that individuals own good as well as the rest of the community. Profits of publishers should not get in the way of achieving compliance with codes of practice. Job protection of certain professions shouldn't get in the way of achieving compliance. The regulations and resources required to implement were put there to protect the people: not provide an eternal source of income for monopolistic businesses. The primary requirement is to protect the people, such that the otherwise harmless actions of an individual when taken collectively across many people does not cause harm to the population at large and the individual. The resources required to achieve such protection can change at any time.

I therefore do not consider it necessary to get an engineer to "do the numbers". The numbers come from mathematical models which are an abstraction of reality, and unless the mathematical model has been been validated by scientific testing: then the model can be pure fiction. When there is no mathematical model then need an engineer to create the model and manage the testing and validation of such model. For most things however, the mathematical models exist already. Well actually no they don't, that's why we get failures.

For the mathematical models to exist already, we would need to have people in place who manage knowledge and information about technology, rather than just about mathematics. So for example we have beam theory: that is a mathematical model which has had some scientific validation and roughly reflects the behaviour of beams for practical purposes. However a floor beam is not the same as a rafter, nor: a purlin or girt or wall stud or lintel, or drive shaft or crane jib. Each and everyone of these beams has characteristics which are important to the design of the beam concerned which extends beyond beam theory. These other characteristics in the main have zero to do with the formal education of engineers: and society thus far relies on industry to pass on such information. Industry has proven unreliable as guardian of such knowledge. Thus the technology which is industrial society has a major flaw in it.


There was the printing press and then there were computers. The printing press allowed the dissemination of knowledge across large distances and across time. Prior to the printing press, books were rare and held in great and remote libraries, and books had to be written out by hand. Further few people could read and write, thus knowledge was typically passed on by word of mouth from generation to generation and thus highly reliant on human memory. Whether communicating via the spoken word or the written word, communication of the intended message is highly dependent on the interpretation of the transmitted message by the receiver. A book can get the message to people but its not necessarily understood or found useful. A lecture can provide the message to people, but once again its not necessarily understood. The application of knowledge is thus hindered by the limitations of communication and the opportunities to learn and the availability of a good teacher who can properly assist learning. Formal education systems tend to take learning out of the individuals hands and as a result actually hinder learning by imposing schedules which are not compatible with the individuals acquisition of understanding. {Analogously it becomes increasingly difficult to climb a ladder if someone keeps knocking the rungs out.}

Just as machine tools improve the speed and consistency of manufacturing processes, computers increase speed and consistency in the application of mathematical models and calculation. Technology is concerned with developing tools which aid human limitations, which extend and enhance human ability. Education systems seem built around highlighting an individuals inadequacies and then getting in the way of the individual adapting to the imposed necessity. That which the schools call cheating, in in fact the toolmaker humans ability to adapt and improvise. Inventing technology is a human trait: it is not the exclusive domain of some subspecies called engineer.


Interruption probably a good thing, this was meant to stay practical not get idealist. But still that's what I do, so may as well post.