Turning an articulated truck on a spreadsheet
| 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
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.
RotationByRadius
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.
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.
|
