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)

VBA Code Module

Simulating the turning of an articulated truck was a matter of plotting the loci of the paths of various points on the tractor and trailer in the two-dimensional space of the ground. The spreadsheet application came with standard trigonometric functions such as SIN or ATAN. It did not come with built-in functions for rotating one point about another, or for the more particular needs of modelling the turning of an articulated truck, for example calculating the path of a point that ‘trailed’ another. For efficiency in performing the calculations and to facilitate checking, it was necessary to write some functions in Visual Basic for Applications (VBA) code.

Describing the position of a point, such as a reference point on a truck, requires the specification of a coordinate pair. However, functions that could be used in the spreadsheet application only returned single-value results, and not coordinate-pair results. A simple workaround to address this issue was to write a subroutine that could give a required coordinate-pair result and then call this separately from two functions, one for the X-ordinate result and the other for the Y-ordinate result. In this way coordinate pair results could readily be written to the cells on the spreadsheet, using two cells for each result.

Such workarounds are commonly used in spreadsheet work. The wastefulness of carrying out the same calculations multiple times is counterbalanced by the convenience and the ease of use of the spreadsheet paradigm. In particular, once formulae and functions had been set up within a particular column corresponding to one position of an articulated truck, they could be rapidly copied to many columns, thereby automating the calculations for up to about 250 truck positions (the spreadsheet application allowed a maximum of 256 columns per worksheet).

A general subroutine that modelled a particular type of movement of an articulated truck might yield multiple result values, for example the final position of a reference point on the tractor (a coordinate pair), the orientation of the tractor (a single value) and the angle between the tractor and the trailer (a single value). Here again, the approach used was to define a single subroutine to carry out the calculations and call it from a separate function for each result value. For the example mentioned, there would therefore have been four separate functions yielding the final X-ordinate, Y-ordinate, tractor orientation and the angle between the tractor and the trailer.

The custom VBA procedures that were written for the truck-turning spreadsheet are described in Table 5 (the complete code listing is included in Appendix A). Some notes and comments relating to these are given below.


This subroutine allows a point to be rotated about another point and makes use of trigonometric principles in a straightforward way. Separate functions RotatedX and RotatedY are used to call the subroutine for the X- and Y-ordinates.


This subroutine has been written to calculate the new position of the tail end of a trailing link when the head is moved through a distance in a straight line. The underlying principle is that, for an infinitesimal displacement of the head in any direction, the new position of the tail will lie on a straight line that joins the original position of the tail to the new position of the head, as illustrated in Figure 5. For a finite displacement of the head in a straight line, the locus of the tail is generally a curve. Dividing the path of the head into a number of small subpaths and recalculating the position of the tail at the end of each can approximate this curve. For modelling the movement of an articulated truck, a subpath length of 0.01 metres was found to be adequately small and so, within the subroutine, the travel of the head was automatically divided into a number of subpaths that made the length of each less than or equal to this value. Five separate calling functions were written to allow the individual results from the subroutine to be inserted into cells of the spreadsheet: RotationByRadiusX, RotationByRadiusY, RotationByRadiusXCentre, RotationByRadiusYCentre and RotationByRadiusOutDir.


For the tractor of an articulated truck – or indeed for any non-articulated, front-axle-steered vehicle – there will be a specific turning radius associated with any particular position of the steering wheel (or amount of steering). As can be seen in Figure 1, the turning centre (or centre of rotation) lies on the centre-line of the rear axle if there is only one rear axle, or on a line that is parallel to the rear axles and passes through the centre of the rear axle group. The turning centre also lies, or should lie, on the axis of each of the steered front wheels. In a well-designed vehicle the outside steered wheel, which is at a greater radius from the turning centre, will always be at a smaller angle to the vehicle’s centre-line than the inner steered wheel. If this ideal is not exactly achieved, tyre wear will be increased, but there will nonetheless be an equivalent effective turning radius for each amount of steering.
Subroutine RotationByRadius calculates the new position of a reference point that is rotated about a centre with a given radius and through a given path distance. It also calculates the coordinates of the centre of rotation and the direction in which the path is pointing at the end of the travel. Separate calling functions – RotationByRadiusX, RotationByRadiusY, RotationByRadiusXCentre, RotationByRadiusYCentre and RotationByRadiusOutDir – are used to insert the outputs of the subroutine into cells on the spreadsheet. It should be noted that it is the inverse of the turning radius, rather than the turning radius directly, that is used in the subroutine.


Subroutine Steer builds on the functionality of subroutine RotationByRadius, which it calls, and allows two inverse turning radii to be specified: one at the beginning and the other at the end of the travel. Within the subroutine it is assumed that the inverse turning radius varies linearly with the distance travelled: this is a fair approximation if the steering wheel is being turned at a steady rate with respect to distance travelled (turning radius itself could not be considered to vary linearly with the distance travelled). Separate calling functions, SteerX, SteerY, SteerXCentre, SteerYCentre and SteerOutDir are used to insert the outputs of the subroutine into cells on the spreadsheet.


Subroutine SteerAndTrail, like subroutine Steer, builds on the functionality of subroutine RotationByRadius, which it calls, and allows beginning and ending inverse turning radii to be specified. However, SteerAndTrail calculates the new position and direction of the trailer at the end of the movement by using the functionality of Trailing, which it also calls. This is a good example of how quite a powerful calculation can be built up from simpler components. Whereas Trailing strictly applies for straight-line motion, it is called within an iterative process where the incremental travel does not exceed 0.01 metre and the coordinates of each successive position of the tractor towing point are calculated precisely by RotationByRadius. Separate calling functions, TrailingX, TrailingY and TrailingDirDeg are used to insert the outputs of the subroutine into cells on the spreadsheet.

[<<previous   |  next>>]