Building a Structural (Engineering) Design eWorkbook – Part 8

Seems going to be a delay for a while, whilst make some major changes to the VBA code. Relative to existing workbooks the following are of importance:

  1. Workbook Design Engine

The designEngine workbook has two user forms as illustrated here. The main form contains options to:

  1. Analyse gable frame (Kleinlogel Frame III)
  2. Produce Height vs Span Tables/Charts (gable frame)
  3. Generate Acad script to generate framing plans/elevations (gable frame)
  4. Create Data file for plane frame analysis program.
  5. Create Data file for use with Microstran structural analysis software

The objective is to modify the application so that it uses the plane frame analysis program rather than Frame III Kleinlogel formula, and that its scripts for CAD are more extensive (eg. workshop details), and that this is also connected to bill of materials (bom) workbooks.

The problem is that with my Windows XP computer, running Office 2003, I hit some limit in MS Excel/VBA. I had added more user forms, and more routines, and everytime I opened and ran the main form, the workbook would crash and become corrupted. Hence I wanted to move away from using Excel/VBA and go back to stand alone application written in VB. Only when I got Visual Studio, I ended up with vb.net: which required some changes to the original VBA source.

So I did start to translate the technical library from VBA to VB.net several years back (eg. VS 2003/2005), but other work interrupted. As part of the exercise I started writing a simple command line application, it is this vb.net code I am currently converting to VBScript and VBA.

A few years back around 2016, I attempted to transfer the Technical library to LibreOffice Calc and Basic (LOB). Since the development environment for LOB doesn’t have an immediate window, tracing the program is cumbersome. Anyway strange things were happening with the code, and since my VS license wouldn’t install on Windows 10, and VS 2013 express is too slow to open, I opted for use of FreeBasic. So I started translating the technical library code to FreeBasic. The purpose being that FreeBasic has more strict requirements than VBA and LOB. Doing this I merged several modules together, and also reordered the procedures so that none is called before it is defined. Most of my could follows such principle, but occasionally a new procedure is added in the wrong place. This exercise also got interrupted.

Neither the vb.net nor FreeBasic code is directly useable, as the original VBA library has been modified with changes to wind loading functions. therefore need to merge all three VBA, vb.net, FreeBasic back into a common and uniform library.

History

The design engine started as Quattro Pro spreadsheet and Turbo C program. The Turbo C program generated a script which drew framing plans for a shed. It was a MS DOS command line program which used standard input and wrote to a file. Input data could therefore be stored in a file and piped into the program (Refer to: Building a Structural (Engineering) Design eWorkbook – Part 2 for example of piping data).

On moving to Windows, I merged several QPro worksheets into a single QPro Windows V1 workbook for shed/structural design. It was linked to more specific worksheets: AS1538 (cold-formed steel), AS1250 (steel), AS1170 (loading). There were three main workbooks: engine, pframe, cosmos. The “cosmos” workbook was supposed to be a centralised data workbook: the data cosmos. Whilst “engine” drove everything, and “pframe” was specifically for sheds and similar.

The workbook generated data files for our in-house plane frame analysis program, it could be used for canopies of sheds. If canopy was chosen then load was removed from wall and internal pressure coefficients increased. It provided a good starting point and files could be manually edited later for a more specific assessment of canopy. It could also handle gable or skillion roof, it just adjusted the heights of the ridge and right hand knee to suite. since the plane frame program only handles one loadcase per file, the spreadsheet allowed selecting loadcase and writing the appropriate file. When we got Microstran, the spreadsheet was also modified to write Microstran arc files: such file containing all the loadcases.

Parallel to the development of the QPro application was development of application in Delphi. Early development I used Turbo Pascal then Turbo C. Whilst I preferred C syntax for number crunching, moving away from numbers I found C syntax less readable than Pascal. Though I did use the book: Al Stevens (1987), Turbo C: Memory-resident Utilities, Screen I/O, and Programming Techniques, to create a user interface for applications at DOS, the prime tested bed and example being my soil heave program. However I liked Turbo Pascals object model, and was in conflict which language to adopt.

When moved to Windows did have VB but never really experimented with it: it was Basic: don’t write real programs in BASIC. Also had Turbo Pascal for Windows, didn’t do much with that either. But then again I didn’t have regular access to a computer either. When I finally got my own computer I got Borland C++ Builder and Borland Delphi. I had problems getting C++ to compile anything other than the examples, Delphi on the other hand was easy. The primary problems I had with Delphi, I would have typically also had with either language and possibly other development tools, these were:

  1. The borland database engine (BDE) and how to install and setup on someone else’s computer. (DAO is easier, it’s there already). {Newer Borland books were less informative and less helpful than the Turbo Pascal 3 book}
  2. What file format to use? How to create on screen previews and print to printer? Should use rich text format (rtf) objects, or html for reports or stick to plain text? Plain text was obvious starting choice, and status quo, but improved presentation of reports needed something better.

So I adopted Delphi, and translated my Turbo C shed drawing program into Delphi, and started to create engine in Delphi. Thus pursuing parallel development in Qpro and Delphi. The input forms in QPro were different than those in Delphi, because QPro supported fewer features. Programming Kleinlogel formula was first done in Delphi, as was generating height span tables. Most wind loading functions were originally written in Delphi, then translated into QPro macros. This was because I knew how to do in Delphi, but uncertain how to do in QPro. So the Delphi app became a check against QPro, and a guide as to how to approach in QPro. I looked into extending QPro with Delphi, but all looked to complicated.

I then discovered a book on Office 97 and VBA. I got Office 97 and system developer kit. I started converting QPro to Excel 97, but continued parallel development in Delphi. Also the plain frame program was converted from Turbo Pascal to Delphi. As all this development is in my spare time around billable structural projects, the development isn’t continuous, and my notes get buried. More importantly the spreadsheets get modified on the fly to match the requirements of current projects.

I had written the generation of height span tables in Delphi, it was checked against spreadsheet calculations in Excel, and otherwise compared against Microstran results. Three things then occurred:

  1. Wind loading code was revised, and I needed to update the technical library in Excel, before Delphi.
  2. I wanted the Delphi application to control Excel, and generate xy-charts. However Delphi couldn’t read and convert the Excel type library, too many keyword conflicts. So working in Delphi, I was working blind, and uncertain as to whether input to an Excel object required array notation or function notation. So I needed to write and test code in VBA first before writing in Delphi.
  3. Borland had abandoned, QPro, Paradox, and WordPerfect, then went on to abandon Delphi and other development environments. They hadn’t integrated the IDE’s, with each other their office products which seemed obvious they should. It was now less convenient to buy and cost more.

The result was I quit further development in Delphi and continued with VBA. I didn’t like Basic, but VBA was a lot like Pascal in its syntax and some of it slightly better (eg. dot notation used in the with statement).

So the office had moved from QPro, after having determined that Corel QPro had too many bugs, and that VBA was a better macro language: not the least of which it was consistent across all office products, whilst QPro, Paradox and WordPerfect each had their own unique application language.

Secondly I considered that there would be less work to convert from VBA to VB, than to convert VBA to Delphi. More over possibly could develop everything using VBA. Not the least of which is that using MS Excel removes the problem of deciding how to format, preview, and print reports from scratch using a general programming language.

VB.net threw a spanner in the works in terms of ease of conversion. But still it is easier than converting to another entirely different language . At the moment however Visual Studio is a relatively slow program to start, and is too connected to the internet for my liking. SharpDevelop is somewhat faster to start, but not fully compatible (it supports C# and vb.net): the main issue being the use of forms.

However if more concerned with command line operation than graphical user interface then an option is to convert to VBscript, and still another option is FreeBasic. So stay with the Basic language but use a different dialectic to match the needs of a specific task.

My current computer has more RAM, and more seems to be the norm, so the limits I hit with previous laptop and Excel 2003, may no longer be a problem with Office 365. So may be able to get back to continuing development in VBA, but keep hearing about VBA maybe dropped. Therefore ultimately may have to adopt vb.net. But if that is the case, I am more likely to adopt C#, has it avoids some of the problems I had with C++, and has the more compact notation for number crunching.

Philosophy

There is also the issue of operating systems, and software. For the most part I consider we should write our own software in so far as the basic tasks we need to do.

To clarify there are certain things we are taught or properly expressed, we learn to do with pencil, paper and pocket calculator. We don’t make our own pencils, paper, pocket calculators, log tables or slide rules: but we do put these tools to use in our own unique ways. In terms of computers we need the basic office products and a CAD package, along with programming tools.

Currently I have a few software tools stuck on Windows XP netbook, these are: Visual Studio 2005, Multiframe, AutoCAD LT 2000. On my Windows 10 computer I have Visual Studio Express 2013, SharpDevelop and FreeBasic to replace VS 2005. I have ProgeCAD 2016 to replace AcadLt 2000. Whilst I have no replacement for Multiframe: I have some open source FEM/FEA software but it is over complicated lacks user manuals, and isn’t simple matrix structural analysis (MSA).

I would consider moving to Linux/Ubuntu but LibreOffice isn’t good enough for my needs yet. Those people just starting out, would probably find LibreOffice fine, and it will develop with them. Besides Office, also need CAD package and structural analysis package, none of the Linux alternatives I have found so far are suitable. Then I have miscellaneous utilities mostly concerned with file management and time management. Therefore moving to Linux would be a step backwards as not adequately developed, even though I always wanted to be able to use Unix operating system on PC. At the moment it doesn’t have enough features to make it worth while spending time using it. If all I needed was SciLab or LibreOffice Calc to crunch numbers then my view would probably be different: but I need more than that, and therefore moving to Linux is not yet practical.

Linux is likely to become more practical once users can develop desktop tools to suit their needs using programming languages other than C/C++: that is once it becomes an operating system suitable for personal computers instead of most suitable for programmers. Such is most likely to happen as people adopt software like LibreOffice on other operating systems, and then discover that all the software they need and use is available on Linux. The other factor which will assist is the use of online software, software as a service (SaaS) in place of desktop software. Many of the larger software products are heading this way, first as online license server and annual subscription, followed by online account and running the software whilst online. The assumption being everyone will be connected to the internet all of the time. If using SaaS in a browser then computer is basically back to being little more than a dumb terminal with a need to be connected to appropriate infrastructure: owned by who and in which country?

So my philosophy may need to be modified to include writing office suite, CAD and MSA software: but one of the pieces of software trapped is the development software.

I get that there is a desire to change things to make things different. However that doesn’t make the change an improvement or progress, it just makes it a change. Such changes shouldn’t be imposed on the population at large. Our society seems to have gone beyond built-in-obsolescence to being deliberately defective. The defective product being passed of as updates and upgrades, and necessary because of most probably bogus claims about security. The typical PC is an over sophisticated clock, calculator and typewriter: when were such stuff a security risk? The risk primarily comes from bloated software with capabilities beyond that necessary and relevant to its primary task.

My current task is not an update or upgrade, it is pursuing a parallel development. As I mentioned earlier, functions defined inside classes are not visible to the worksheet. So such conversion would remove capability to my workbooks not enhance them. However converted to appropriate classes and compiled with vb.net, the technical library can be made into an add-in independent of MS Excel/VBA. Though thus far my experiments have indicated will use dot notation to reference the functions in a worksheet. That I don’t like, but I guess is necessary to identify functions with same names coming from different add-in’s.

Also by sticking with VBA, VBscript and VB.net I am also largely locked to MS Windows. Whilst JavaScript and Java may provide improved portability. Though converting languages isn’t all that big a deal, its inefficient but not overly complicated.

So for the time being will stick with Basic, get VBA converted to command line application in VBscript/FreeBasic. Will then work on converting to LOB and getting LibreOffice working. I can then continue using Multiframe, and return to doing all calculation work on same computer: until I can find a replacement for Multiframe that works on either Windows 10 or Ubuntu.


Revisions:

  1. [27/12/2018] : Original