GEOMATICS AND ENVIRONMENTAL ENGINEERING • Volume 4 • Number 4 • 2010

El¿bieta Jasiñska*, Jan Ruchel*

Using a Spreadsheet for Surveying Computations**

1. Visual Basic for Applications – Easy and Effective

Programming Language
The computations related to surveying works require a proper application
guaranteeing the execution of the tasks. We have a choice therefore either to pur-
chase a program that can execute our computations or to create a new application
from the ground up. Or we can make use of a standard calculating program (e.g.
a spreadsheet), which can provide capabilities to define all details of the task real-
ization as well as the method and scope of presenting the results.
A properly designed spreadsheet becomes an application that can realize vari-
ous tasks from the simplest computations to complex analyses. On our decision
depends what functions the application can execute.
The commercial programs available nowadays at the market usually present
the final results only, without providing any view into indirect results of computa-
tions – which are often indispensable for executing the analysis, especially in case
of any computation problems. While making our own application in a form of
a spreadsheet realizing the wanted task we can ensure the access to the indirect
results at any stage of the computations.
There are many ways for creating an application capable of performing the
computations we need:
– Creating a software from the ground up, using some existing program-
ming tools (for example C++, Delphi, Java, Visual Basic or others).
– Using the popular Excel spreadsheet program. Apart from its wide calcu-
lating capabilities (including the matrix algebra) the software features em-
bedded elements for constructing macros with the use of Visual Basic for
Applications (VBA). With these capabilities it allows to create a complete
application sufficient for a given scope of computation tasks.

* AGH University of Science and Technology, Faculty of Mining Surveying and Environmental
Engineering, Department of Geomatics, Krakow
** The paper has been worked out within University research program no. 11.1.150.006

77
78 E. Jasiñska, J. Ruchel

Below we provide an exemplary procedure of employing the Excel spread-

sheet and VBA language for creation of a computing application capable of the ad-
justment of a classical leveling net.
In the first years of development of informatics the programmers were re-
garded as immensely talented individuals and the programming as inaccessible
for common people. Along with the hardware development the programming lan-
guages were also evolving, starting from a low-level ones (assemblers) to the high-
-level languages such as C++. At present they are less complicated, feature many
mechanisms making the programming easier and they became more accessible for
common computer users. The BASIC language was created in 1963 as a very sim-
ple programming language designed for beginning programmers. The break-
through came with Visual Basic 1.0. It appeared at the time of releasing the
Microsoft Windows operating system. In this version it was already a visual lan-
guage in which a great part of programming relied on graphical arrangement of
objects in the application window (called a form). There was no need any more to
construct very enlarged source codes in which most of the programming work
was employed to create a shape of the application. In Visual Basic a programmer
can quickly create fully working application with the help of a graphical user in-
terface (IDE). Visual Basic for Applications (VBA) is a Visual Basic based pro-
gramming language implemented into Microsoft Office applications and a few
others (e.g. AutoCAD). This version of Visual Basic serves primarily to automatize
the work with documents through the use of macros. The main difference be-
tween VBA and VB is that VBA does not allow to create independent complex ap-
plications of the EXE type. The code written in VBA is always enclosed in the doc-
ument created with a program capable of using the VBA – for example in the
*.XLS file of the MS Excel spreadsheet. A program of this type therefore requires
a runtime environment, such as an application supporting the given document
format, installed on the programmer’s computer. Since version 2000 the MS Office
package is furnished with a separate VB editor – the well known for programmers
VB 6.0. This feature makes the coding work much easier.

2. Creating an Event-Driven Application

(Program Form and Code)
An application written in Visual Basic language consists of two elements:
A. Graphical user interface, or a window system called a form, and objects as-
signed to it. In easy tasks singular objects – most commonly buttons – can
be used instead of a form.
B. Code of the program, containing procedures with a set of instructions to be
executed.
Using a Spreadsheet for Surveying Computations 79

Applications written in VB or VBA are event-based programs, what means the

proper procedure is triggered when a certain event occurs. By an event one should
understand an activity made by the user, such as clicking the mouse button, press-
ing a proper key on the keyboard, marking with the cursor, etc. Events can be also
linked with some peripherals (for example measuring instruments) or with a cer-
tain time periods. Occurring or not occurring of the wanted event determines the
sequence of the code executed. This is why during programming we should take
care of the proper sequence of events. In creating applications one general rule
should be primarily observed – do not empower the user to make an error, for if
you do they will certainly make it.
The event has to be linked to a particular object from the user interface within
our application if it is to trigger a proper reaction such as executing the instructions.
The procedure of handling an event has the following syntax:

Private Sub Object_event (optional parameters of the procedure)

.....
‘a list of instructions assigned to the object and the event
......
End Sub

As it can be seen, the name of the procedure contains the name of the object
and the event associated with it. It can also include certain parameters proper for
the type of objects and events involved. The headers of the procedures are auto-
matically generated by VBA.
Procedure is a very important component of the program, because for the code
to be executed it must be enclosed into a procedure. A procedure makes the smallest
part of the code which can be executed independently from other parts of the code.
Module contains one or more procedures and a declaration section in which
declarations common for all the procedures for the given module are placed.
Project consists of all the modules, forms and objects of the home application
of the document being created and the document itself.
VBA has a very complex (multi-window) and at the same time user friendly
interface, providing convenient ways of creating applications. The most important
windows are:
– Project Explorer window, containing all elements of the spreadsheet and of
the project.
– View Designer window, which allows to design the look of our form in the
graphical mode.
– Toolbox window, containing the list of available objects that can be used in
the projecting mode.
80 E. Jasiñska, J. Ruchel

– Properties window, displaying present values of all the properties for the
object active in the Project Explorer. In this window we can modify the
properties of the active object. If needed, the values can be changed also
from the code level in time of executing certain procedures.
– Code window, containing the procedures and declarations created for the
project.

3. Writing the Event Procedures

Writing the procedures is quite easy. To write an event procedure for

a spreadsheet do the following:
– Run the Visual Basic Editor.
– If the Project window is not displayed, call it from the View menu choos-
ing option ProjectExplorer.
– In the Project window double-click on the name of the spreadsheet you
want to create the event procedure for. This should display the Code win-
dow for this spreadsheet.
– In the Code window at the Object drop-down list (top left part of the win-
dow) choose the Worksheet object. After choosing the object MS Excel will
automatically create a template for default event procedure, proper for the
given object type. The default event for the worksheet is the
SelectionChange event.
– We can also choose an event other than default from the Procedure drop-
-down list (top right part of the Code window). The list shows all possible
events associated with the chosen one. After selecting a certain event the
proper template for the event procedure will be created (Fig. 1).

Adding objects to a worksheet is done by placing them on the sheet with the
mouse cursor and defining their size and position with the mouse and the mecha-
nisms of the graphic environment of MS Windows. With the Properties window
we can determine values for the chosen object’s characteristics (Fig. 2).
Visual Basic for Applications is a user friendly programming language on ac-
count of the embedded error control feature. The errors occurring during writing
a program can be divided into several groups:
– Text editing errors – these can be avoided by a careful examination of what
has been written. These errors are not dangerous for the application.
– Errors occurring in time of compilation – making the compilation of the
program impossible – such as: syntax mismatches for declarations and in-
structions, or wrong identification of certain program elements.
Using a Spreadsheet for Surveying Computations 81

– Execution errors – occurring while running a part of algorithm. To prevent

this kind of errors certain safety mechanisms (error traps) are applied
while writing the code.
– Logic errors – the most dangerous kind of errors, for they evoke wrong
computation results. They are also the most difficult to find, because no
messages signalize them. Usually logic errors come from executing
a wrongly written algorithm.

Tu wpisujemy
Here enter kod
the procedure’s code
procedury

Fig. 1. The program code window – an event procedure template (Excel 2003)

Before the application is written the requirements of the program need to be

carefully determined and the algorithm leading to wanted results defined and
checked. Preparing the application to computations requires designing (and re-
serving proper memory resources for them) certain structures in which our data
will be stored. The structure consists mainly of arrays and records, structural
types containing many elements. Arrays contain elements of one type only (but
this can be also a structural type), records may contain elements of various types.
Creating these structures is called declaration. In case of arrays we can use dy-
namic declaration, i.e. change the array’s size accordingly to needs.
82 E. Jasiñska, J. Ruchel

Fig. 2. The program code window – creating a worksheet (Excel 2007)

Declaring of an array with the elements’ type only determined:

Dim matrixA () As Single
This is a two-pointer array containing elements of the real type with a single
precision. When the array is going to be used we can predeclare it along with de-
termining the size and number of pointers:
ReDim matrixA (w, k)
as a new matrix, or:
ReDim Preserve matrixA (w, k)
as a matrix with a different size but the content unaltered.

3.1. An Exemplary Application

for Adjusting the Classical Leveling Net
(within the Basic Scope)

Basic programming knowledge is enough to make use of VBA to create an ap-

plication capable of running any process of surveying computations. Of course in
order to create such application the knowledge concerning the task itself is also re-
quired; one must know the algorithm resolving the problem.
Using a Spreadsheet for Surveying Computations 83

The illustration applied for this paper will be a computation of the classical
leveling net according to the adjustment algorithm by means of the least squares
method.
The basic formulas used in the program are listed below:
– Observation weights:
1
pi = 2 .
mi

– The system of correction equations:

• for linear observations:

w i = dDi + Dapprox. - Dmeasured ,

where:
dx p dy p dx k dy k
dDi = ,
- cos( Ap - k ) - sin( Ap - k ) cos( Ap - k ) sin( Ap - k )
2

p – starting point,
k – ending point,
Di – observation measured,
wi – distance correction,
Dapprox. = Dx 2 + Dy 2 – approximated distance
Ap–k – azimuth for the side measured
dxp, dxk, dyp, dyk – partial coordinates;
• for angular observations:

vi = dái + áapprox. – ámeasured ,

where:
dx l dy l dx p dy p dx c dy c
da i = ,
Al Bl - Ap - Bp -( Al - Ap ) -( Bl - Bp )
1

ái – observation measured,
vi – angle correction,
a 0i – approximated angle,
Al, Bl, Ap, Bp – gradients respectively for the left and right
arm of the angle:
Dx Dy
A= × r, B= × r,
Dx + Dy 2
2
Dx + Dy 2
2

dxl, dxc, dxp,

– partial coordinates.
dyl, dyc, dyp
84 E. Jasiñska, J. Ruchel

– The correction equations in the matrix form:

V = A× x + w.

– The solution of the system of standard equations:

( AT × p× A) × X + ( AT × p× w ) = 0,
-x = ( AT × p× A) -1 × ( AT × p× w ).

– Computation of adjusted coordinates:

Xi = Xi0 + dx i ,
Yi = Yi 0 + dy i .

– Basic analysis of accuracy:

[ pvv]
m0 = ± , cov( x) = m02 × ( AT × p× A) -1 .
n-u

The program code enables to display the results on the monitor in the desired
form and to save them into files (e.g. text files).
Exemplary windows from the application thus created (Figs 3–6).

Fig. 3. The start window of the finished application designed for adjusting the classical
leveling net – it requires entering data and running by pressing the WYRÓWNAJ (‘adjust’)
button
Using a Spreadsheet for Surveying Computations 85

Fig. 4. A worksheet with a draft of the net adjusted

Fig. 5. A worksheet with computations (a fragment) of the net adjusted

86 E. Jasiñska, J. Ruchel

Fig. 6. The result window with results of the leveling net adjustment computations

4. Summary
Choosing the Visual Basic for Applications to create an application provides
many advantages. The project can be expanded (new features added) with no lim-
itations. It provides access to the indirect results and gives possibility to display
the results in any desired form. We bear no additional costs – assuming we own
the MS Office application packet. An element of a very high importance here is
a proper decimal separator character (for a given Windows installation). Visual
Basic for Applications (and Visual Basic) requires the “.” (dot) as a decimal separa-
tor. In the Windows systems changing the decimal separator can be made through
the Control Panel (regional settings or international settings – depending on a sys-
tem version).
The text files created during the computations are very useful as they are easy
to view and edit. They can also be employed in other programs. In particular, the
cooperation with AutoCad becomes convenient and easy, as the program also has
the implementation of VBA language embedded.

References
[1] Czaja J.: Modele statystyczne w informacji o terenie. Wydawnictwa AGH,
Kraków 1996.
[2] Hausbrandt S.: Rachunek wyrównawczy i obliczenia geodezyjne. Vol. 1 and 2.
PPWK, Warszawa 1971.
Using a Spreadsheet for Surveying Computations 87

[3] Hyde R.: Profesjonalne programowanie. Czêœæ 1: Zrozumieæ komputer. Helion,

Gliwice 2005.
[4] Hyde R.: Profesjonalne programowanie. Czêœæ 2: Myœl niskopoziomowo, pisz wy-
sokopoziomowo. Helion, Gliwice 2006.
[5] Instrukcja techniczna O-1: Ogólne zasady wykonywania prac geodezyjnych. War-
szawa 1988.
[6] £oœ A.: Rachunek wyrównawczy. PWN, Warszawa 1973.
[7] Mysior M.: 20 lekcji z Visual Basic dla aplikacji. Wprowadzenie do programowa-
nia. Mikom, Warszawa 2002.
[8] Roman S., Petrusha R., Lomax P.: VB.NET Almanach. Helion, Gliwic 2003.
[9] Walkenbach J.: Excel 2003 PL. Programowanie w VBA. Vademecum profesjonali-
sty. Helion, Gliwice 2004.
[10] Willet E.C., Cummings S.: ABC Visual Basic dla aplikacji w Office XP. Helion,
Gliwice 2002.