About Level3
Search archives
- June 2007
- August 2006
- May 2005
- June 2004
- November 2003
DIT Home

Read postings about this article   |   Post a comment about this article  |  print this article [pdf]

Turning an articulated truck on a spreadsheet

Author - Jim McGovern

[<<previous   |  next>>]

Show/ hide article menu (click icons opposite)


This tiny subroutine yields the use of the computer’s processor to other processes for a period of 100 milliseconds. This provided the time delay between successive views of the articulated truck’s position in the simple animation on the Paths sheet.

VBA code on the Paths sheet

In VBA code that was specific to the Paths sheet a Boolean (or logical) variable TimerOn was declared. The value of this variable (either True or False) determined whether the animation of the plan view of the truck on the sheet was on or off.


When the Advance button on the Paths sheet was pressed, this event procedure set the variable TimerOn to True, it selected the cell on the Paths sheet that contained the position of the truck and proceeded to increment the position by one within an iterative loop that also included a delay of 100 milliseconds (produced by calling TimeTick) as long as TimerOn was True. Whenever the position exceeded 250 it was reset to 1.


Whenever the Advance button was released, this event procedure set TimerOn to False, which has the effect of terminating the iteration loop within CommandButton1_MouseDown and thus stopped the animation.

Organisation of the workbook

The dimensions sheet

The first sheet of the workbook, Dimensions, contained the truck-defining dimensions and the derived dimensions, as in Table 1 and Table 2. The derived dimensions updated automatically whenever one of the defining dimensions was changed. For convenience, variable names were assigned to the cells containing the defining and dependent dimensions, for example, Len_AE for the value ‘King pin to tractor rear axle group centre point’. Variable names like this could be referred to in a formula within a cell anywhere in the workbook.

The ‘Maximum inverse centre-line turning radius’ was the last of the dependent dimensions and was a key parameter in the steering calculations. This corresponded to 100% steering lock (positive for right turn and negative for left turn). It was calculated as the inverse of the ‘Minimum centre-line turning radius,’ which, in turn, was calculated using the following cell formula:
where the symbols had the following meanings:

Steering_Half_Width Half the steering axle width between the tyre contact patch centres
Tractor_Faxle_to_Tractor_RAxle The distance between the tractor front axle centre point and the tractor rear axle group centre point
Max_Steering_Angle The maximum angle between the inside steering axle wheel and the centre-line of the tractor
PI()/180 This was the mathematical constant pi divided by 180. As a multiplier it converted the steering angle in degrees to radians.

The Dimensions sheet also contained a region headed ‘Default truck position (tractor heading straight in Y-direction with rear axle group centre point at Origin)’. Part of this is shown in Table 6. In this region, reference points on the tractor and trailer were defined. Cell notes described each point: for example, the cell note for point E read ‘Centre point of tractor rear axle group’. The first data row provided the coordinates of 21 reference points on the tractor and trailer when the truck was in the default position. The second data row contained the coordinates of the same reference points where the tractor was still in the default position but where the trailer was rotated by a specified angle (the trailer angle) from its default position in line with the tractor. All the data values were automatically updated whenever one of the defining dimensions was changed or the trailer angle specified in the first cell of the row was changed.

[<<previous   |  next>>]