State of Play 2018/wk48

As at week 48 day (25/11/2018 Sunday). At some point between week 43 and now, experimented with VBA editor objects and extracting data from VBA project. Typically experimenting with VBA techniques to sever structural calculations from MS Excel, starting by shifting some data to database management system (DBMS), and converting worksheet calculations to VBA code. Then converting the VBA code to VBScript, which can be run from the command prompt using Windows Scripting Host (WSH/ cscript): this can then be placed on my computer that runs Multiframe structural analysis software (netbook running Windows XP, with limited memory, and no MS Office).

My days now besides working on my spreadsheets involve researching health issues, which itself has resulted in a growing health workbook monitoring various issues. Worksheets for:

  1. when need to replenish medications
  2. blood pressure readings
  3. Warfarin Dose and INR readings
  4. Water requirements
  5. Body Mass distribution
  6. Appointments
  7. Exercise/Activity, achievements walking
  8. Weights/Exertion limits

Basically putting human factors data to use, and researching medical physics and biomechanics.

Looked into various apps for android phone:

  1. Counter and Timer
  2. Stopwatch and Interval Timer
  3. Time study Stopwatch
  4. Multi Timer

I haven’t been doing laps round garden since started rehab at the gym, in between I use an exercise band. So not put lap counter to work yet. We did dig out the totem tennis, so that is another option for some exercise: as supposed to get around 30 minutes each day. If I can walk into town daily that would have that covered.

I got some from google earth/maps. The 800m from home to edge of town has a 26m rise. the town then drops 6m, and then rises 7m to the office. The total distance to office and back (2.848 km) is approximately equal to 20 laps around the garden, which took 40 minutes last time I tried. The 800 m to edge of town takes around 9 minutes. Whilst walking a lap around the main street takes around 15 minutes, with distance from north to south taking around 8 minutes. It took 21 minutes to get to office (21-9=12 minutes south to north), then after 10 minute break it took 24 minutes to get home, making total 45 minutes (excluding the rest). Using Naismith’s rule the time to get to edge of town is estimated to be 12.2 minutes: so maybe I climbed uphill too fast. Basically the 20 laps wasn’t exhausting (just dull), but the trip to the office was, so the uphill climb requires significantly more effort. A daily trip to the office would require carrying a bag, or pulling one on wheels.

So at present time, not been driving to office, as wasn’t permitted to drive during first 6 weeks after stent. Since 6 weeks up neither driven nor walked to office, been working from home (mostly developmental work, as have little billable work: hopefully will find and/or build a new market soon.).

Week 43

Started heart rehab program (phase 2) at the local gym, two days a week: Tuesdays and Thursdays.

Week 44

Week 2 of heart rehab.

Week 45


Got some billable work, a canopy to assess and certify.

Whilst working on the canopy got the idea needed something to automate Multiframe on my netbook, which doesn’t have MS Excel. Decided could use VBScript started to convert some VBA code to VBScript.


Week 3 of heart rehab. The day started cool, so wasn’t much into drinking water simply because it’s a good idea to drink water. One article suggests 35 ml for every kilogram of mass: so at approximately 80 kgs, I need to intake 2.8 Litres/day. This can be reduced by water content of foods, though I cannot remember the allowance for this.

Anyway potentially had a lack of water and lack of food, before going to the gym, also the day got hotter. I increased the weights of some exercises. Typically looking to get exercises at Borg modified scale of 3 to 4. I set leg press almost equal to body weight, not possible to set equal to. Power builders apparently can push 2.5 times body weight, not aiming for that, but given that both legs can support body weight, and can stand on one leg, so each leg can support body weight, seemed reasonable that can push body weight. Though legs can support more than body weight, issue is can leg muscles raise and lower body weight. Leg muscles can raise and lower that mass above the knee, which from available data comes out at about 88% of body weight: so muscles should be able to move 70.4 kg. The weight I used was less than 80, and feasible, though possibly pushed rating upto 5.

Anyway I walked from the leg press machine to the arm ergometer, adjusted it from standing operation to seated operation and sat and rested. I talked for a while with the nurse, I indicated things were ok, and just resting. She went away, I rested for a minute or less, then I started gasping for air. I called the nurse back. I wasn’t recovering so she nurse called the ambulance, and otherwise had me lie on the floor with my feet up, and the nurse attempted to take my blood pressure. The ambulance came, and also the doctor on call for the hospital was nearby so he also checked things out. I was given glucose and the ambulance took me up the road the local hospital. At the hospital they gave me water to drink and sandwich to eat, made regular checks of my blood pressure. Then when happy blood pressure was stable they let me home. The doctor also reduced the medication I have which is typically used to reduce blood pressure: but that is not the reason given on my medication list for taking such medication. Doctor also suggested monitor own blood at home.

Next day doctored phoned, and requested I go to clinic for ECG, which I did, and I was given the all ok!.

Week 46


Week 4 of heart rehab. Nurse monitored my blood pressure approximately every 10 minutes during the Tuesday session. From start of session to end of session my blood pressure decreases, as I remember. Typically exercise should cause it to increase. Thursday session nurse monitors blood pressure less frequently, it peaked during the exercises, but dropped by the end of the session. I reduced weights back to around where they had reached by about session 4. {The nurses keep the exercise charts, so I have to go from memory.}


Decided if going to use VBScript to write structural programs for my netbook, then also need data file other than MS Excel. So experimented with MSXML2, to read xml files. Went back to MS Excel workbook and modified my routines for exporting named ranges to xml files. Then experimented with MS Access files using DAO. Also checked standard input/output (stdio) when using VBScript, so used piping to get data file into the script.  Then downloaded and installed driver for SQLITE, and tested that.

Amongst other things also revisited the calculation comparison and used MS Access, MYSQL, and LibreOffice Base(HSQLDB) and queries to do the series of calculations. Only a single query was required in MS Access where as everything else required a series of saved views, one calculation sequence per view. A labelled calculated result can only be used in a new view, it cannot be used in current view or query. Used DB browser for sqlite to input data into sqlite and experiment with queries.

It took a while to get the driver for sqlite to work, and from the examples it didn’t look much use or benefit. All the examples referred to getting data with reference to numbered columns, completely ignoring field names, thus voiding the benefit of a database. Anycase once discovered it was ODBC Driver, I went back to working in Excel/VBA and used the object browser to get properties and experiment with more suitable properties to get the required data. Starting with DAO to access MS Access, then ADO which required change from .mdb file to .accdb file. Once discovered how to get field data by name from MS Access, then wrote VBA examples for sqlite, then converted to VBScript.

I didn’t experiment with VBScript to get data from MYSQL, but was going to experiment with WAMP and phpMyAdmin, and php. I did do something for php, but not sure what at this moment: by the looks of it probably didn’t get beyond reading up how to access MYSQL from php.

Anycase as a starting point I am assuming that all MS Windows computers have access to DAO, irrespective of whether have MS Access installed, and irrespective or whether have office. Also haven’t attempted to use VBScript to run LibreOffice Base, but it seems viable to convert LibreOffice Basic to VBScript, just a matter of creating the appropriate starting object.

So order of preference would be MS Access (.mdb followed by .accdb), LibreOffice Base, sqlite, then mysql: as largely working on stand alone desktop applications.

Week 47


Week 5 of heart rehab: still monitoring my blood pressure before and after the session. Its still low, but not changing as much between start of session and end. So other than unusual blood pressure, I seem reasonably fit and healthy: that and the stent in the heart and a stack of medication to stop it being rejected and blocking.


Had another look at : Automatically Converting MS Excel worksheet to VBA subroutine. Started with my shed design spreadsheet but too complicated, so stepped back to the workbook used for comparing calculations. On saturday (24/11/2018) evening got it to convert the spreadsheet to a VBA subroutine, loaded into Excel/VBA editor compiled and run.

Not expecting to be able to fully convert a workbook or individual worksheet, just to create a skeleton program which represents the basic concept of the workbook. This can then be manually edited and fully translated into a working program. But very simple spreadsheets may be able to fully convert without manual intervention.

Such conversion provides another insight to what the spreadsheet is doing. The VBA code also highlights, why calculations are done using a spreadsheet rather than by writing source code in high level programming languages. For example formatting the presentation of the report is faster and less cumbersome in a spreadsheet than writing source code. Whilst my converter produces code for the report, such code likely to be deleted as producing reports is not the purpose, using the calculations to drive something other than a report writer is the objective.

Week 48

Its just starting. Objective is to write blog posts discussing data file options for Building a Structural (Engineering) Design eWorkbook: that is data stored outside the eWorkbook. This data could be reference material, or it could be the inputs and outputs of the eWorkbook calculations. The idea is that the eWorkbook does the calculations, rather than be the repository of the data. So can store the data on a project, not the whole eWorkbook. Though if modified the workbook to suit the project may want to store the workbook on the project, but typically shouldn’t be necessary.

So basically intend to spend week explaining the above outlines in more detail.


  1. [25/11/2018] : Original