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

Turning an articulated truck on a spreadsheet

 Author - Jim McGovern

 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.

Rotate

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.

Trailing

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.

Steer

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.

SteerAndTrail

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.