PREFACE
Microsoft Excel learning is perceived as more attractive from time to time and it is
probably the most widely software‐learning topic written into books, websites, courses,
tutorial videos, groups, etc. Favored by many people because Excel is relatively easy to
operate and giving "completely" results by showing spreadsheet form (rows and columns),
images, text, tables, charts, and so on. Talking about Excel for applied engineering
calculation cannot be separated from the discussion on Visual Basic for Application (VBA)
macro, which is the programming language of Microsoft Visual Basic for the automation of
certain tasks. This is due to macro like any other programming language capable of doing
iterative calculation or repeating the calculation process with ease. There is a unique
combination between worksheet as user‐interface and VBA, which turned out to be a lot
makes it easy for users to create a program.
This book also discussed the depiction in AutoCAD software. Why? Because the drawing
creation process can be done through Excel formulas or macros, and this will enhance a
series of producing program. The advantage of an AutoCAD drawing creation is no doubt
that relies on high image accuracy with a myriad of features it will certainly be a challenge
to create drawings. With the ease of working with Excel, coupled with a lot of given
examples in this book, it is expected to increase the interest of the reader to create new
original application programs. Thus, each model or even a specific model of calculation will
be an exciting challenge for a programming job is already enjoyable.
Happy Excel programming!
Jakarta, November, 2015
Gunthar Pangaribuan

Gunthar Pangaribuan
Graduated from Indonesia Institute of Technology and earned a bachelor degree in Civil
Engineering. Getting started with a career in geotechnical engineering services and became
his major work which he has spent over 10 years. During the time, he has created numerous
computer programs especially for completion of geotechnical problems using Excel­VBA­
AutoCAD ­ the magic trio he relies on. Some of the programs are presented in this book. In
2012, he joined the oil and gas company as a facility engineer. The current activities and
interests include, traveling, social media, tea, music, band, and rock guitar solo.
vi

CHAPTER 1
BASICS OF EXCEL

The work performed by Excel is basically the job of entering data which is then processed
to obtain the desired results. It is, in principle, the same as entering data into an electronic
calculator. However, the data entered here consists of various types and coupled with
existing Excel facilities makes it possible to present the appearance of numbers, text,
associated tables, graphs and a database. This makes Excel becomes well integrated to
create a reporting text and the problem analysis as well.
Each version of Excel to be developed to always make changes and additions of new
facilities, while is still maintaining compatibility with previous versions. But the changes do
not alter the basic features of this software as a worksheet for computing applications. In
this book, we will be working with Microsoft Excel 2007.

1.1 WORKSHEET AND WORKBOOK
When opening Excel, by default Book1 is the name of the first workbook. This workbook
consists of 3 worksheets named Sheet1, Sheet2 and Sheet3. Excel worksheet is also
referred to as a spreadsheet that is the sheet for processing text and numbers.
Figure 1.1 shows the elements of a workbook. At the top of the page there is a title bar
displays the workbook name. Underneath, there is a Ribbon, new interface intoduced in
Excel 2007, which is a navigation tool replaces the menu and tool bars in earlier Excel
version as a tool of access to Excel commands. All commands are grouped and placed into
tabs for particular purposes, thus a tab contains groups of commands.
Worksheet is divided into rows and columns. In Excel 2007, the number of columns and
rows have been improved from previous versions. Columns are from A to Z, then AA, AB to
XFD (16,384 columns), while the rows start from 1 to 1,048,576. The intersection point of
column and row forms a cell as a place to fill data. Each cell has an address referred to by a
column and a row, for example cell B4 is a cell in column B and 4th row. The address of
selected cell can be seen in the Name Box below the Ribbon.

1

A collection of cells is called a cell range, forming an array extending horizontally or
vertically. For example range A1: A5 is a collection of cells from A1 to A5, forming an array
of 5 x 1 or the range A1: C5 form a 5 x 3 array, and so on.
The name of the worksheet can be replaced by other names, by clicking twice on the sheet
tab, press Delete to clear the name, then write the new name; or by right‐clicking on the
tab and then on the shortcut menu click Rename to change its name.

1.2 DATA TYPE
The input data in the spreadsheet can be divided into some types that are:
a. Text:

alphabet characters and text: A, B, ‐Z, AB, A2, Computers ...

b. Numbers: numerical data: 1,2,3,0, ‐1, ‐2,4,5.85...
c. Date:

the date data typically refers to the setting of a computer calendar or
formulated in the calculation.

d. Hours:

Data of hours generally are referred to computer time setting or formulated
in the calculation.

e. Formula: mathematical expressions that calculate two or more values produce a new
value.
f. Function: a function that is used for various applications such as calculations, finance,
mathematics and trigonometry, statistics, database, logic and others.

SSave a file with
w
the naame of the workbook
k
t
that is being
g done

Alt+FA

SStore the filee with anotther name in
nto a folderr
a
and
directo
ory, or to a
a disc. By default filee
e
extension
o .xlsx is given (free macros). Iff
of
t file conttains macro
the
o (VBA) an
nd needs to
a
activate eac
ch time it is opened, an
n extension
n
o
of .xlsm exte
ension mustt be given.

New
w

O
Open a new
workbook

Opeen

O
Open an exis
sting workb
book

Prin
nt

P
Print a work
ksheet throu
ugh the prin
nter device

Closse

C
Close the wo
orkbook (Cllose Window
w icon)

An Introd
duction to E
Excel for Civiil Engineerss

3

Exitt

C
Close the pr
ogram and exit Excel (iicon Close)

Und
do

C
Cancel the la
ast job

Red
do

R
Repeat the l
ast job

Copy the daata of a celll or range of cells to
C
a
another
loccation. To select
s
the data to bee
c
copied or cu
ut, then clicck on the ceell or rangee
o
of cells wher
re the data is then presss Ctrl+C

Quick
Too
olbar:

Access

Tab
b HOME

Cop
py

Cutt

Move the data to
M
o another location.
C
Commands
such as Cu
ut, Copy and
d Paste can
n
b
be found in
the pop‐up
p menu by cclicking thee
r
right
mousee button on
n the highliighted cells
o
or by pressi
ng Ctrl+X

Put the dataa into a new
P
w cell or ran
nge of cells.
P
Paste
comm
mand is don
ne after preeceding Cutt
o Copy. To
or
o place thee data, clicck the new
w
location, th
hen press Enter
E
or by
b pressingg
C
Ctrl+V

Inseert > Cells

Alt+IE

Display Inseert dialog box.
D
b
Used to insert a
a
n
new
cell, row
r
or column at th
he pointerr
p
position (hig
ghlighted ceell)

1.3 FORMULA
By definition, a formula is a mathematical expression to calculate the results of two or more
values. Formula can consist of numbers, mathematical operators, functions, reference cell
or range of cells. Cells and cell range are often given a name, for example "A" to B2, or "B"
for a range of D2: D6. Naming cells will be discussed later in this section. All formulas begin
with an equal sign (=). For example, in cell B4 is written the following formula:

=B2+B3 summing the data in cell B2 to cell B3
=B4*B5 multiplying the data in cell B4 to cell B5
=SUM(D2:D4) summing the number of cells D2, D3 and D4

An Introduction to Excel for Civil Engineers

5

When a formula to be copied, it needs to be considered what is the type of cell in the
formula. Relative cell is the cell that will adjust to its new place when copied. Cell C1 that
contain formula =A1+B1 will be =C2+D2 in Cell E2. The result is as shown in Figure 1.2.
Absolute cell is a cell that does not change the address if copied to another place. The
notation is to add $ before the name of a column or row number, for example: $A$4. Adding
$ in the name of any column or row number will only change one address. Column A in $A4
will remain when copied, but the row number will adjust to its new location. And vice versa
with cell A$4. Such cells are called semi‐absolute cell.

A

B

C

D

E

F

1

20

30

=A1+B1

2

=C2+D2

3

=$A$1+B1

4

=$A$1+D2

5

=$A1+B1

6

=$A2+D2

7

=A$1+B7

8

=C$1+D8

9

10

Figure 1.2: Copied formula and the results in spreadsheet
Formula stating cell relationships or range of cells, for example, =B4+C30 or =SUM (A4:
C20) are more difficult to read than the mathematical relationship with the more practical
variable name, for example, =x+n or SUM(B). Name of cell or cell range is created by
clicking Formula > Define Name. This name applies to all sheets in a workbook. If it only
represents the specific sheet, for example, Sheet1, the writing is Sheet1!x, i.e. to the variable
x in Sheet1. The use of variables is highly recommended because it will simplify a formula.
The names used and the locations can be seen in Name Manager and you can also create,
edit and delete a name.

An Introduction to Excel for Civil Engineers

6

1.4 BUILT­IN FUNCTION
Excel has many built‐in functions to build complex formulas, some of them are shown in
Table 1.3. Functions such as mathematic and trigonometry or statistic, for example, are the
most common functions used in engineering practices e.g. to produce calculation data sheet
in laboratory soil testing. Before using a function, it is advised to know well the function
and its arguments. The reference could be found the Help menu or press F1.
Table 1.3 Excel Built­in Functions
A. Math and Trigonometry
ABS(num)
ACOS(num)
ACOSH(num)
ATAN(num)
EXP(num)
FACT(num)
INT(num)
LOG(num, base)
LOG10(num)
MDETERM(array)
MINVERS(array)
MMULT(array1,array2)
PI()
RAND()
SIGN(num)

SIN(num)

An Introduction to Excel for Civil Engineers

Returns the absolute value of a number
Returns the arccosine of a number
Returns the inverse hyperbolic cosine of a
number
Returns the arctangent of a number
Returns e raised to the power of a given
number
Returns the factorial of a number
Rounds a number down to the nearest
integer
Returns the logarithm of a number to a
specified base
Returns the base‐10 logarithm of a number
Returns the matrix determinant of an array
Returns the matrix inverse of an array
multiplying of 2 arrays
Value of pi = 3.141592654
Random value between antara 0 dan 1
Sign of number. Sign 1 or 0 or ‐1 if the
number is positive, zero or negative,
respectively.
Returns the sine of the given angle

Returns the hyperbolic sine of a number
Returns square root of a number
Add the numbers
Multiplies corresponding components in the
given arrays
Returns the tangent of a number
Returns the hyperbolic tangent of a number

TRUE if value is empty
TRUE if value is logical value (TRUE or
FALSE)
TRUE if value is a number
TRUE if value is a text

Average value of the numbers
Counts the number of cells that contain
numbers within the list of arguments
Counts the number of cells that are not
empty within the list of arguments
Returns the parameters of a linear trend.
Const and stats are logical values (see in
Excel Help)
Maximum value in a list of arguments
Minimum value in a list of arguments
Returns the slope of linear regression line
Returns the intercept of the linear
regression line
Returns values along a linear trend. Const is
a logical value specifying whether to set the
constant b = 0 in y = mx + b relationship

Returns TRUE if all of its arguments are
TRUE
Specifies a logical test to perform
Reverses the logic of its argument:
NOT(TRUE) = FALSE
TRUE if one of its argument is TRUE

Returns the character specified by the code
number
Checks between two text strings and
returns TRUE if they are exactly the same,
otherwise returns FALSE
Finds one text value within another with
start number
Returns the first character based on the
specified number of characters
Returns the number of characters in a text
string
Returns the last character or characters in a
text string, based on the specified number of
characters
Removes spaces from text except for single
spaces between words
Converts text to uppercase

1.5 ARRAY FORMULA
By Excel definition, array formula is a formula that can perform multiple calculations and
then return either a single result or multiple results. Array formulas act on two or more
sets of values known as array arguments. Each array argument must have the same
number of rows and columns. For an example, the built‐in LINEST returns two results

An Introduction to Excel for Civil Engineers

9

which are Slope and Y‐Intercept. This function gives linear regression lines that fit with the
known x and y‐values, as shown in Figure 1.3.

A

B

1

x

y

2

‐1.0

‐5.0

3

‐0.5

0.0

4

1.0

5.0

5

2.0

4.0

6

3.0

0.5

7

4.0

‐5.0

8

5.0

‐12.0

9

Slope

Y‐
intercept

9

Slope

Y‐intercept

10

‐1.204

10

={LINEST(B2:B8,A2:A8)}

{=LINEST(B2:B8,A2:A8)}

0.537

Figure 1.3: Array formula for linear regression and the results
Array formula is entered by pressing Ctrl+Shift+Enter and Excel will automatically insert
the formula in brackets ({}). To display values returned by LINEST function, make LINEST
formula in cell A10 as shown in Figure 1.3, and then select the range A10: B10 > press F2 >
Ctrl+Shift+ Enter.
Here are some other examples of array formula:
{=SUM(A2: B2*A3:B3)}

is a single result

{=TREND(A2:A6,B2:B6)}

is a separately 5 results

Alternatively, the INDEX function can be used to return the values, as shown below. LINEST
has two results indexed sequentially that are Slope and Y‐intercept.
9

Slope

10

‐1.204

Y‐
intercept
0.537

9

Slope

Y‐intercept

10

=INDEX(LINEST(B2:B8,A2:A8),1)

=INDEX(LINEST(B2:B8,A2:A8),2)

An Introduction to Excel for Civil Engineers

10

1.6 DATA FORMATTING
Data and cells formatting can be done through the Home tab > Format > Cells. In the
Format Cells dialog box, there are several sheet tabs which are Number, Alignment, Font,
Border, Fill and Protection. Number formatting in Number tab is divided into several
categories such as general, number, date and so on. For example, for custom category or
user preference is shown in Table 1.4. Alignment is the setting for layout of text in a cell
such as vertical and horizontal alignment, control and orientation of text in a cell. Fonts are
the settings that relate to the character, such as the font face used (Arial, Times New
Roman, Tahoma, ...), style (italic, bold ...), size, color and so on. Border is to make the
margins, line types (straight, dotted, thin, thick), and color. Fill to create a displayed cell
with shading (horizontal, diagonal, dot, ...), shading color and the color of the cell
background. Protection is used to select the condition (locked or not) of data and formulas
(hidden or not) in a cell when worksheet protection is enabled. To protect a worksheet
click Review tab > in Changes group click Protect Sheet.
Table 1.4: Custom format for numbers
Format

Displayed

0.00

2343.00

0.00E+00

2.34E+03

##0.0+0

2.3E+3

#,##0.00

2,343.00

#,##0.00%

234,300.00%

0.0 ”m”

2343.0 m

Column width and row height can be adjusted by: first way, by dragging the mouse (hold
the left mouse button while moving) when the pointer position at the boundaries of the
column and row headings (there is a sign "J" or "L"), or second way is by right‐clicking the
mouse on the column or row heading and from the shortcut menu click Row Height or
Colomn Width, then enter desired value in the textbox. The other way is through Home >
Format.

1.7 ERROR MESSAGE
Error message will be appeared if a formula is not working as it should be, for examples,
arguments in a formula are not complete, the data type does not match, or wrong in
developing the logic. Another case, for example is division by 0 (zero) or the width of the
An Introduction to Excel for Civil Engineers

11

column is not wide enough to display a result. The message started with the # sign
followed by the type of error. Table 1.5 shows some error messages that may appear.
Table 1.5 Error Messages
Messages

Remarks

#####

Cell contains number, date or time that is wider than the cell
width or minus result in the date and time format

#N/A

(Not Available) when a value is not available to a function or
formula.

#DIV/0!

Error due to division by zero (0).

#NAME?

Excel does not recognize text in a formula

#REF!

When a reference cell is not valid

#VALUE!

The types of arguments used in the function are wrong. The
argument can be a numerical value, text, name, label, reference
cell, cell range and function.

1.8 PRINTING
Before printing the worksheet to paper, you can set the area by blocking the area that will
be printed (press Shift + "J" or "L" or drag the mouse). Afterward, click the Page Layout
tab > Print Area > Set Print Area. The print area can be seen in

Office Button > Print

> Print Preview or to press the shortcut key Ctrl+F2. Printing then performed through
> Print > OK or pressing Ctrl+P.

The limits of printing page can also be specified through the View tab > Page Break
Preview instead current Normal view. This will show all the data in worksheet to be
printed with a page number. You can also adjust the boundaries of the printing page by
dragging the mouse on the blue boundary line.
Page configuration is then set through the Page Layout tab. Click the Print Titles in the
Page Setup group, and a Page Setup dialog box will appear with several sheets tabs which
are Page, Margins, Header/ Footer, and Sheet. Page is to select the orientation and size of
paper, and the scale of worksheet on paper; Margins is to set the boundaries of the text in
the paper; Header/Footer is to make custom header and footer; and Sheet is to set print
area, showing grids, row and column headings, and the print order.

An Introduction to Excel for Civil Engineers

12

1.9 MAKING CHARTS
A set of data can be well delivered and communicated through a chart image shown the
correlation between the data. In Excel there are many types of chart depictions such as Bar,
Column, Line, Pie, Area and so on, plus a 3‐dimensional view. Chart is created through the
Insert tab > Chart, and there will be Charts group as shown in Figure 1.4. For this example,
we will use the data from Figure 1.3. Select Scatter > Scatter with Smooth Lines and
Markers because smooth line between points to be made. Click Select Data on the Ribbon
interface to display the Select Data Source dialog box as shown below.

Enter x and y‐values data in the Chart Data Range by clicking a button at the right side of
the input box and then point the pointer to cell A2 and drag the mouse from cell A2 to cell
B8. Click OK and a chart will be created as shown in Figure 1.5. In the chart, the regression
line is also shown with the associated equation. Regression line is created by clicking
mouse on the data in the chart (at point or a line), then right click > Add Tendline > Linear
> checked the Display Equation on Chart. Furthermore, if you want to work with the chart
thoroughly, click the graph area to display Chart Tools tab where commands are grouped
in accordance with their task name, which are Type, Data, Chart Layouts, Chart Styles
and Move Chart.

An Introduction to Excel for Civil Engineers

13

Figure 1.4: Charts group displays chart type options

Figure 1.5: Example of Scatter with smooth lines chart type
Note:
As designed for office applications, Excel offers various types of charts. However, we will
not discuss many different types of charts, and but only focused on Scatter chart type
with straight lines that frequently used in this book.

An Introduction to Excel for Civil Engineers

14

1.10 ENGINEERING DRAWING
Chart type of XY Scatter is suitable for use in Civil engineering practices in giving a
drawing presentation that forms lines or elements of structure. The reason is that every
line can stand alone, so that easily modified and formulated for the intended drawing.
A straight line of XY Scatter chart type is determined by the given coordinates of both ends
required for its input data. If both ends of the line called joint, then a line segment is formed
by the coordinates of two joints. Thus, there will be a series of joint data put into a
worksheet table to create straight lines.
To make it easier to understand the intent and purpose of this discussion, below is given
examples and the followed steps.
Example 1
DDRCreate 3 continuous lines drawing through 4 points (1 to 4), as shown in figure below.

The joint coordinates are:
Joint 1

An Introduction to Excel for Civil Engineers

x
0

y
0

15

Joint 2

1

1

Joint 3

2

3

Joint 4

3

4

To create continuous lines as the figure above, it takes the following steps:
1. First step: click on the Insert tab > Scatter > Scatter with Straight Lines and
Markers.

2. Chart Area displays nothing because no data on it as shown below. Click Select
Data on the Ribbon interface to display the Select Data Source dialog box.

An Introduction to Excel for Civil Engineers

16

3. A Select Data Source dialog box is displayed as shown in the figure below (left
side). Click Add to display the Edit Series dialog box (right side). Each series that
added (clicking Add) represents a line that requires a pair of x and y values as
inputs. Thus, a line series shall consist of the coordinates of the both ends, the one to
be (x1, y1) and the other is (x2, y2).

4. Following Step 3 is to enter the line coordinate values i.e. series x and series y‐
values. When needed, give a name for the series in the Series name input box.
5. Click OK to end.
The intended joint coordinate and lines for chart depiction can be summarized as below:
Coordinate
Line

Joint

x1

y1

x2

y2

1

1

2

0.0

0.0

1.0

1.0

2

2

3

1.0

1.0

2.0

3.0

3

3

4

2.0

3.0

3.0

6.0

Thus it can be stated that the first line (no.1) is a line created by given joint 1 and 2
coordinates (refers to the figure).
In step 4, what we do is to enter x1 and x2 range of values (colored yellow) into the Series
X values input box, while the Series Y values is filled by column y1 and y2 range of values
(colored green). To enter x‐coordinates, press Ctrl+left mouse button click at x1 column
and repeat left mouse button click at x2 column to get the range of cells. Do the same way
to enter y‐coordinates.
Repeat step 3 to 5 to make 2nd and 3rd line. The result will be shown as below:

An Introduction to Excel for Civil Engineers

17

Example 2
Draw a simple picture of one floor building as below:

As in Example 1, to simplyfy the portrayal of the drawing it needs to produce joints and
lines coordinate tables. The buiding is composed of 6 joints and 6 straight lines that can be
built up with the following numbering system:

An Introduction to Excel for Civil Engineers

18

Based on
n the picture above, thee following ttables can b
be formed:

The join
nt coordinattes for assiggning lines on the righ
ht table is obviously
o
t repetitio
the
on of
inputtingg task of th
he previous joint coord
dinates on the left tab
ble. To avoid
d repeated data
entry maanually, VLO
OOKUP fun
nction can b
be used by aadopting thee x, y joint ccoordinates data
of the leeft table as a referencee table to obtain
o
x1, y1
y and x2, y2
y coordinaates of the right
table. Th
he formulas in the work
ksheet are sshown as folllows:

To get th
he drawing, do the sequ
uence steps as in Example 1.
An Introd
duction to E
Excel for Civiil Engineerss

19

Example 3
Draw the following truss structure that consists of 21 lines and 12 joints. The joint
coordinates are tabulated as below:

Joint

x

y

1

1.0

0.0

2

1.5

2.5

3

2.0

5.0

4

2.5

10.0

5

3.0

15.0

6

3.5

20.0

7

4.0

15.0

8

4.5

10.0

9

5.0

5.0

10

5.5

2.5

An Introduction to Excel for Civil Engineers

20

11

6.0

0.0

12

3.5

2.5

Before creating the drawing of truss, it is convenient to form lines coordinates as required
for Series X and Series Y input data. The coordinates of truss lines are tabulated as the
following:
Line

Joint

x1

y1

x2

y2

1

1

2

1.0

0.0

1.5

2.5

2

2

3

1.5

2.5

2.0

5.0

3

3

4

2.0

5.0

2.5

10.0

4

4

5

2.5

10.0

3.0

15.0

5

5

6

3.0

15.0

3.5

20.0

6

6

7

3.5

20.0

4.0

15.0

7

7

8

4.0

15.0

4.5

10.0

8

8

9

4.5

10.0

5.0

5.0

9

9

10

5.0

5.0

5.5

2.5

10

10

11

5.5

2.5

6.0

0.0

11

2

12

1.5

2.5

3.5

2.5

12

12

10

3.5

2.5

5.5

2.5

13

3

12

2.0

5.0

3.5

2.5

14

12

9

3.5

2.5

5.0

5.0

15

3

8

2.0

5.0

4.5

10.0

16

3

9

2.0

5.0

5.0

5.0

17

4

9

2.5

10.0

5.0

5.0

18

4

8

2.5

10.0

4.5

10.0

19

4

7

2.5

10.0

4.0

15.0

20

5

8

3.0

15.0

4.5

10.0

21

5

7

3.0

15.0

4.0

15.0

An Introduction to Excel for Civil Engineers

21

As the previous examples, the line coordinates data entry used VLOOKUP function. The
formulas writing is also to follow the same manner as the previous example. To obtain the
truss drawing, do the sequence steps as in Example 1.
Next example is to draw truss structure subjected to horizontal load so that each joint will
be shifted as far as n unit. Suppose that the displacement at the joints will be from 0 at
supports to maximum +0.5 unit at joint 6 (at the top), so it will be in a range of 0 < n < 0.5 to
the right direction. The table of joints coordinates is then modified and labeled for two
conditions, before and after loading, where n is used in the calculation. It will show as
follow:

It is noticed that only the x‐values will be changed by adding the horizontal displacement
value of n, from 0 at Joint 1 to a maximum of 0.5 units at the Joint 6. Joint 1 and 11 are fixed
supports so their positions remain in place before and after loading.
After forming the joints coordinates table, next is to create lines coordinates table of Series
X and Y. There are now 42 Series X and Y to be created for the condition of before and
after loading. Table below shows lines coordinates before and after loading:

An Introduction to Excel for Civil Engineers

22

The reference range of data table in VLOOKUP function is now range A7 to E18. Thus, the
coordinates of the lines before and after loading are respectively referred to column BC and
DE or column index of 2.3, and 4.5.
The drawing of truss lines before and after the horizontal loading are shown in chart
below, which are in blue and red, respectively.

An Introduction to Excel for Civil Engineers

23

1.11 VISUAL BASIC FOR APPLICATION
One of the strong points of Excel is its macro capability of using macro programming
language to shorten and simplify the repetitive works (automation task). It can not be
separated, however, from the presence of Microsoft Visual Basic application which is by
default embedded in as an Excel command menu. The Visual Basic application for the
repetitive works or macros in an application program (e.g. Excel or Word) is called Visual
Basic for Applications (VBA). In addition to Microsoft Office, many of non‐Microsoft
software also use VBA, such as AutoCAD, CorelDraw, Visio and Norton.
VBA can also serve as a programming language to solve many problems in science and
technology field, for instance to solve a complex iterations and the analysis of civil
structures that are not easily or can not be done rely on the built‐in functions and
spreadsheet standard commands. In this respect, the analogy of using macro is therefore a
repetitive calculation in an effort to get new output when new input is entered.
An Introduction to Excel for Civil Engineers

24

Microsofft Visual Baasic as well as Excel VBA
V
is an Object Orieented Progrramming, which
w
reads an
nd analyzes objects exp
posed by Exxcel through
h object librrary. The reference to tthese
objects is stored in
n Microsoftt Excel Objject Library
y (EXCEL12
2.OLB). In a programm
ming
structure for a calcu
ulation appllication, Exccel exposes the properrty of an objject which iis the
value of input data that to be p
processed by
y VBA and tthen returns the resultt to a worksheet.
So, in wrriting inputts what we have actuallly done is sstoring objeect value into a variablle, or
the valuee of the variable = valu
ue of cell pro
operty of th
he worksheeet (= Range object, thatt will
be mentiioned next).

1.11.1
1 CREATING MACR
RO
To creatte macros, activate
a
thee Developerrs tab throu
ugh
Show De
eveloper ta
ab in the Riibbon, so it looks like tthis:

> Ex
xcel Option
ns > Popullar >

To go to
o the window where th
he VBA proggramming language is created, cliick Visual B
Basic
to open Visual Basiic Editor (V
VBE) windo
ow > click th
he Insert m
menu > Mod
dule to show
w the
following window:

ogramming llanguage is written in a module th
hat has been added before by Inse
ert >
VBA pro
Module.. Below is an examp
ple of VBA
A programm
ming language (progrram code) in a
procedure in a mod
dule:

An Introd
duction to E
Excel for Civiil Engineerss

25

VBA read
ds, analyzess and manip
pulates Exceel's objects tthrough theeir properties and meth
hods.
A VBA co
ode for an o
object is ord
derly compo
osed of the object name, a period, followed by
y the
property
y or method
d. The propeerty is an attribute of an
n object and
d is always aaccompanieed by
an equall sign (=), w
while the method is the aaction carriied out with
h the object.
In the co
ode created
d above, Seleect and CleaarContents are called m
method, wh
hereas Selection
in the seecond row iss an object refers to Raange object in the first rrow. Value o
on the third
d row
is a pro
operty of th
he Range object
o
with the value of
o 10. The code abovee is intendeed to
remove the existingg value in th
he range A1
1 on Sheet1 and replace it with a n
new value o
of 10.
The Worrksheets ("S
Sheet1") cod
de refers to active sheeet name, in tthis case is SSheet1.
To see the
t availab
ble objects in Excel, you can acccess through the Obje
ect Browse
er by
clicking the icon on VBE to
oolbar or press F2. Seleect the Exceel libraries iin the pull d
down
menu as shown belo
ow:

An Introd
duction to E
Excel for Civiil Engineerss

26

Objects are classified into Classes (on Class window) and their Members (on Members
window) that could be a property, a function or a constant. Names, Cells, FormulaArray
for instance, are members of the Range class. Excel’s objects are arranged in a hierarchy;
objects contain other objects from top level to down level. Code below shows an example of
an object hierarchy from the Application object to the Range object:
Application.Workbooks("Book1.xls").Worksheets("Sheet1").Range("A1")

In the code, Range is the property of the Worksheet object that returns a Range object. It
uses Range(arg) syntax where arg is name of the range (A1) to return a Range object
represents cell A1. Moreover, Worksheet is the property of the Workbook object that
returns a Worksheet object, Workbook is the property of the Application object that
returns a Workbook object, and the top level of this hierarchy is the Application object that
is Excel.

1.11.2 RECORDING MACRO
The best way to figure out how VBA communicates with Excel’s objects is to record a
macro. This tool gives a quick introduction of all Excel's object as well as how to write code
in VBA. For instance, we would like to record a macro to delete data on a range of cells, say,
in a range of A1 to E10. We need to create an automation steps to clean the existing data as
the program produces a new one. The steps for recording macro are as follows:
1. Click the Developer tab > Record Macro to display the Record Macro dialog box
with the default name of Macro1 for macro name as shown below:

2. Change macro name to ClearOutput
An Introduction to Excel for Civil Engineers

27

3. Move the pointer to cell A1 and drag the mouse to the cell E10 to assign the range
data that will be removed.
4. Press Delete to delete the contents of range A1 to E10 and then place the pointer in
cell A1.
5. End the process by clicking Stop Recording.

The recorded macro is stored in the workbook, and to see the code you have to open the
Visual Basic Editor window through Developer tab > Visual Basic. The code is created as
the following:.
Sub ClearOutput ()
'
'Macro ClearOutput
'
Range("A1:E10").Select
Selection.ClearContents
End Sub

In the recorded macro above there are VBA statements such as Range (), Select, Selection
and ClearContents. You may a bit familiar with these statements that have been briefly
explained in the previous section.
The recording macro tool is very useful for writing complex code or you may indeed never
imagined before, like how to manipulate a chart object such as to copy, to create a title, to
change the colors, making marker, automation for creating lines, and so on. This can all be
done without the need to understand more about the Excel’s object hierarchy that such
“long” and may be a bit confused.
An Introduction to Excel for Civil Engineers

28

1.11.3 PROCEDURE
VBA Macro is a programming language written in a procedure, for instance, ClearOuput
macro that is written in a Sub procedure. Procedure in VBA is divided into three types as
follows:
•
•
•

Sub procedure, procedure that begins with Sub, its name and ended with End Sub
Function procedure, begins with Function, its name and ended with End Function
Event procedure, works when there is a certain event, for example, open a
workbook, click the button, activate the worksheet, and so on.

The most frequently used of Event procedure is clicking a button (command button) on
the worksheet. Here is the example code:
Private Sub CommandButton1_Click()
'call other procedure
Call Pro_Sub1
End Sub

The steps and examples of the above procedures will be shown in next chapters.

An Introduction to Excel for Civil Engineers

30

1.11.4 RUNNING MACRO
Macros can be executed in the two following ways:
1. Through the Developer tab > Macros > select the macro name > Run. Function
procedure, Private Sub procedure and Sub procedure written with argument, can
not be run by this way.
2. Clicking Button (Form Control) or the Command Button (Active X control) on the
worksheet.
The second way is commonly used because it is done by only one step. However, it can be
done in two ways, which are as follows:
o

Create a button through the Developer tab > Insert > Form Controls > Button >
there is a sign "+" on worksheet, and place it on worksheet by dragging the mouse,
the same way to determine its size as well. The Assign Macro dialog box will be
displayed as below. Enter the name of a macro that to be executed and then click
OK.

o

Macro is executed by clicking a command button on the worksheet which is
associated with an event procedure. To create command button click the Developer
tab> Insert > Active X Controls > Command Button > there is sign "+" at the
pointer, and place it on the worksheet by dragging the mouse, the same way to
determine its size as well. The results is as shown below:

An Introduction to Excel for Civil Engineers

31

Double‐click the command button to write code in the following procedure:

Private Sub CommandButton1_Click()

End Sub

When a command button is created, the code program is inactive or when the Design Mode
is enabled. Before run macro, click the Design Mode to disable (until it looks unselected).

1.11.5 VBA DICTIONARY
Statements or words that are used in Excel‐VBA are too many because the capability VBA
to support Excel for diverse goals. Some statements are similar to those used by Excel, for
examples, operator and mathematical relationships, some strings functions and math
functions. In this book, all statements related to this book will not be placed in a special
section, but it will be discussed respectively, on each topic in its section.

An Introduction to Excel for Civil Engineers

32

CHAPTER 2
EXCEL FUNCTIONS
Almost identical to a function definition in mathematics, a function in Excel works based on
the given arguments and written as follows:
Name

Result

y =

Argument/s

f (a,b,c,…)

Arguments are written after the function name, between parentheses, which are the values
used to perform the operation. The argument can be a numerical value, text, references or
range of cell, name, label or a function.
Excel functions are listed by category such as, math and trigonometry, statistics, finance,
logic, and so on. In addition, Excel also provides macrosheet and Visual Basic for
Applications (VBA) to create a function that is defined by the user (user‐defined function).

INT
Rounds a number down to the nearest integer
Syntax:
INT(number)
Example:
INT(8.9) = 8
INT(‐8.9) = ‐9
TRUNC
Makes a number to an integer by removing the fractional part of the number. To specify the
precision of the truncation, enter a specified number of digits after the number.
Syntax:
TRUNC (number,number_digits)
Example:
TRUNC(8.9) = 8
TRUNC(‐8.9) = ‐8
TRUNC(PI ()) = 3
TRUNC(PI (), 3) = 3,141
ROUND
Rounds a number to a specified number of digits.
Syntax:
ROUND(number,num_digits)
Example:
ROUND(3.25,1) = 3.3

2.2 LOGICALL FUNCT
TIONS
IF
This function has tw
wo values of results, wh
hich are TR
RUE and FAL
LSE, with lo
ogical test, iff met
will do tthe TRUE vaalue. If the result returrns a text, tthen the texxt must be between qu
uotes
("text").
Syntax:
IF(logicaal_test,valuee_ if true,vallue_if_ false))
Examplee 1:
In a class, students which havee exam scorres below o
or equal to ((<=) 55 willl FAIL the eexam,
and abov
ve the valuee will PASS tthe exam.

Studentss who FAIL
L with exam
m score > 45 have chan
nce to takee a supplem
mentary exaam to
increasee their scorees. Use the fu
unction to ffind out the name of stu
udent:

AND
Returns TRUE if alll of its argu
uments are TRUE, and returns FA
ALSE if one its argumeent is
FALSE. G
Generally neested with IF function.
Syntax:
AND(loggical_1, logiccal_2, ...)
Examplee 1:
AND(2*2
2=4,2+2=4)

COUNTIF
Counting the number of cells in a range of cells according to given criteria.
Syntax:
COUNTIF(range,criteria)
Example 1
Looking for the number of students with specified value for Example 2 above:
COUNTIF(D2: D6,60) = 2
COUNTIF(D2: D6,> 60) = 1
OR
The result is TRUE if one of its arguments is TRUE. Generally nested the IF function.
Syntax:
OR(logical1,logical2, ...)
Example:
OR(2*2 = 4,2+2=4)

Returns TRUE

OR(2<100,4<100,102<100)

Returns TRUE

2.3 LOOKUP FUNCTIONS
VLOOKUP
Search for a value based on the column index in the table data arranged vertically
Syntax:
VLOOKUP(value,table,col_index,range_lookup)
Example
The experimental result of laboratory test is affected by the size and weight of the used
tools. This example shows how to use VLOOKUP function to read the size and weight of the
ring based on the Ring Calibration table. Suppose it is intended to search the diameter and

An Introduction to Excel for Civil Engineers

38

weight of ring no. 3 and 7. The table data refers to range A4 to F11, ignoring the column
heading.
A

B

C

D

E

F

Area
(cm2)

Volume
(cm3)

RING
CALIBRATION

1
2

Ring Height Diameter Weight
No.
(cm)
(cm)
(gram)

3

4

1

1.950

5.000

32.850

19.635

38.288

5

2

2.000

5.000

32.000

19.635

39.270

6

3

2.000

5.000

33.440

19.635

39.270

7

4

2.000

5.000

34.980

19.635

39.270

8

5

1.950

5.000

33.700

19.635

38.288

9

6

1.925

4.985

32.000

19.517

37.571

10

7

2.000

5.000

33.000

19.635

39.270

11
8
1.975
VLOOKUP result:

5.125

34.000

20.629

39.711

D

E

F

A
14

B

C

Ring Diameter Weight
No.
(cm)
(gram)

15

3

5.000

33.440

16

7

5.000

33.000

Formula in cell B15 to C16:
A
Ring
14 No.

B

C

Diameter (cm)

Weight (gram)

15

3

=VLOOKUP(A15,$A$4:$F$11,3) =VLOOKUP(A15,$A$4:$F$11,4)

16

7

=VLOOKUP(A16,$A$4:$F$11,3) =VLOOKUP(A16,$A$4:$F$11,4)

An Introduction to Excel for Civil Engineers

39

Range_lookup is a logical value that specifies whether VLOOKUP finds an exact match or an
approximate match. If TRUE (or omitted), the values in the first column of table_array must
be in ascending sort order (‐2, ‐1,0,1,2, ..) because VLOOKUP may return incorrect. If
FALSE, the table_array values do not need to be sorted. For TRUE condition, if the
lookup_value does not match with table_array, VLOOKUP returns the next largest value that
is less than lookup_value. Otherwise, if FALSE, VLOOKUP will find an exact match, if it is not
found, VLOOKUP returns #N/A (error).
HLOOKUP
Search for a value based on the row index in the table data arranged horizontally. The
application of HLOOKUP are almost identical to VLOOKUP, in exception that row index is
needed to lookup the value.
Syntax:
HLOOKUP(value,table,row_index,range_lookup)
Example
Transpose (Copy > Paste Special > Transpose) the Ring Calibration table to arrange table
data as shown below. After that, use HLOOKUP the same way as VLOOKUP example above
to search the diameter and weight of ring no. 3 and 7.
Ring No.

1

2

3

4

5

6

7

8

Height (cm)

1.95

2.00

2.00

2.00

1.95

1.93

2.00

1.98

Diameter (cm)

5.00

5.00

5.00

5.00

5.00

4.99

5.00

5.13

Weight (gram)

32.85

32.00

33.44

34.98

33.70

32.00

33.00

34.00

Area (cm2)

19.64

19.64

19.64

19.64

19.64

19.52

19.64

20.63

Volume (cm3)

38.29

39.27

39.27

39.27

38.29

37.57

39.27

39.71

2.4 TEXT FUNCTIONS
LEFT, RIGHT, MID
Returns characters in a text string based on the specified number of characters
=LEFT(text,num_chars)
Returns the first character

An Introduction to Excel for Civil Engineers

40

=RIGHT(text,num_chars)
Returns the last character
=MID(text,start_num,num_chars)
Returns characters starting at specified position
Example
A

B

1

ENGINEERING

ENG

=LEFT(A1,3)

2

745.56

RING

=RIGHT(A1,4)

3

ENGINEER

=MID(A1,1,8)

4

745.5

=LEFT(A2,5)

CONCATENATE
Combines two or more strings from different cells into one string
Example
A

B

C

D

1
2

Civil

Engineer

Civil
Engineer

Point

Coordinate

3
4
5

x

y

z

6

2.0

4.0

0.0

2,4,0

7

2.0

4.0

‐1.5

2,4,‐1.5

8

2.0

4.0

‐1.5

(2,4,‐1.5)

An Introduction to Excel for Civil Engineers

41

D
1
2

Civil
Engineer

=CONCATENATE(A2," ",B2)

3
4

Coordinate

5
=CONCATENATE(A6,",",B6,",",C6)

6

2,4,0

7

2,4,‐1.5

8

(2,4,‐1.5)

=CONCATENATE(A7,",",B7,",",C7)
=CONCATENATE("(",A8,",",B8,",",C8,")")

Another way is to use the way of writing as the following:
numeric & "text" & numeric
Example
A

B

C

D

1
2

Civil

Engineer

Civil
Engineer

Point

Coordinate

3
4
5

x

y

z

6

2.0

4.0

0.0

2,4,0

7

2.0

4.0

‐1.5

2,4,‐1.5

8

2.0

4.0

‐1.5

(2,4,‐1.5)

D
1
2

Civil
Engineer

=A2&" "&B2

3
An Introduction to Excel for Civil Engineers

42

4

Coordinate

5
6

2,4,0

7

2,4,‐1.5

8

(2,4,‐1.5)

=A6&","&B6&","&C6
=A7&" "&B7&","&C7
="("&A8&","&B8&","&C8&")"

2.5 DATA ANALYSIS FUNCTIONS
Excel has many built‐in functions that are used to analyze data obtained from experimental
result. The objective of the analysis is to obtain theoretical parameters that give the best
relationship between theory and experimental result. Three methods will be discussed
here which are: linear regression, polynomial regression and interpolation.

2.5.1

LINEAR REGRESSION

Linear regression is to determine a straight line that fits or the most closely fits to a
number of points data, providing a linear relationship between two variables. The method
used to obtain the line is called the least squares method. Thus, linear regression consists of
a series of points that fit to a number (n) of points data (xi, yi) written into a straight line
equation:
y = Ax + B

(2.1)

where:
A = slope of the line,
B = the intersection of the straight line to the Y‐axis
The accuracy of the straight line over a number of points data is evaluated by a total
deviation E, which is the sum of squares of the distance e between the points data and the
fitted points:
n

n

E = ∑ e = ∑ ( Axi + B − yi ) 2
i =1

2
i

i =1

An Introduction to Excel for Civil Engineers

(2.2)

43

By making E to minimum, A and B values therefore can be determined to obtain the
equation 2.1. Beside E, R2 value is also used for the accuracy measurement by a relationship
below:
n

R2 = 1−

∑ [y

− f ( xi )]

2

i

i =1

n

∑
i =1

⎞
1⎛ n
y − ⎜⎜ y i ⎟⎟
n ⎝ i =1 ⎠
2
i

∑

2

(2.3)

R2 value varies from 0 to 1. R2 = 1 is when the regression line coincides with the data. In
polynomial regression, the higher‐order of polynomial, the closer R2 value to = 1.
Regression in Excel can be obtained using the TREND function, SLOPE, INTERCEPT and
LINEST or with Trendline, a regression line from data relations in an XY coordinate
system. Trendline is created by the following steps: right‐clicking the mouse when the
pointer is on one of the points data in the graph to display a box menu > click Add
Trendline to display the Format Trendline dialog box > select Linear for linear
regression.
Example 1: Linear Regression
The soil shear strength determination from a laboratory test obtains the result as shown in
Figure 2.1. In general, the depiction of data generated from the test is rarely a straight line
to show failure envelope, therefore, it needs a fitted straight line to represent the data.

An Introduction to Excel for Civil Engineers

44

Regressiion is used to obtain sh
hear strenggth parametters, which are cohesio
on (c) and sshear
angle in (φ) of the so
oil mass.
In directt shear test, the failure envelope is a straight line that is eexpressed in
nto equation
n:

τ = c + σn tan φ
Where,

τ

shear streength

c

cohesion intercept

σn

normal sttress

φ

shear anggle or slope o
of failure en
nvelope

Figurre 2.1: Regression on Excel Charrt

An Introd
duction to E
Excel for Civiil Engineerss

45

Shear strength in this model is a linear function over the normal stress, where c and tan φ
respectively expresses Y‐axis intercept and the slope of the line.
In Chart, a regression can be done with Trendline by the following steps: right clicking
mouse when the pointer is on one of the points data on Chart to display a box menu. From
the box menu select Add Trendline to display Format Trendline dialog box > Linear >
Backward to intercept Y‐axis > click Display Equation on Chart. The result is as shown in
Figure 2.1.

The values of A and B that are shown in line equation, y = Ax + B in direct shear test are
equal to those calculated with the following functions:
A = slope of the line = tan φ = SLOPE(Y,X)
B = Y‐intercept = INTERCEPT(Y,X)
Shear angle φ therefore can be calculated with ATAN function (in degree):
=ATAN(SLOPE(Y,X))*180/PI()

Shear sttrength can also be exxpressed in major σ1 and
a minor σ3 principall stresses in
n the
triaxial test
t
by plottting ½(σ1 – σ3) again
nst ½(σ1 + σ3). The strress condition that fit to a
number of points data
d
is referred to as modified faailure envellope, which
h expressed
d into
equation
n:
½(σ1 – σ3) = a + ½(σ1 + σ3) tan
n α
where a and α are the modifieed shear strength paraameters. Th
he parameteers c and φ are
then giveen through relations beelow:

φ = sin–1(tan α)

c=

a
cos φ

Examplee 2: Logarith
hmic Regresssion
It is often found thaat the data relationship from a testt is presenteed where th
he X‐axis is m
made
wn figure 2.2
2. The equattion 2.1 now
w becomes:
in log scaale as show

Figure 2.2: Regression on Semi­log Liquid Limit Test
Figure 2.2 shows the LL test result and a regression line created through Add Trendline
command. The fitted Wn value (Wn') that represents a number laboratory Wn for each N
value can be obtained through the equation below:
y = ‐10.887ln(x) + 105.972
Using the above equation, at N = 25 blows, the related fitted Wn = 70.93%.
The calculation sheet of LL test in the worksheet can be made as follows:

7 =100*(C7‐D7)/(D7‐B7) =TREND(Wn,LOG10(N),LOG10(E7))
Liquid Limit (%) =TREND(Wn,LOG10(N),LOG10(25))
The relationship of Wn 'and N in sheet above is a regression line as pictured in Figure 2.2.
Example 3: Log‐log Regression
In the third example, we will examine on the data that is formed in log‐log scale in the XY
coordinate system. The given data is the standard sieve size (US) for the particle size
distribution of the granular material as shown in Figure 2.3.

Regression in log‐log data relationship can be obtained using formulas as shown in Figure
2.3, or by adding Trendline by the following steps: click the Add Trendline > Power >
Display equation on chart and the R­squared value on the graph, the results will be
shown as in Figure 2.4. The general equation of Power Trendline is:
y = 10cXa, c and a = constant

(2.5)

Slope and Y‐intercept are respectively the constants a and c in equation 2.5, where the
formulas for this series are, respectively:
=SLOPE(LOG (S),LOG (N)) = ‐1.09022
=INTERCEPT(LOG (S),LOG (N)) = 1.36749

Figure 2.4: Chart from Figure 2.2 data series where TREND is used to obtained the
fitted points

An Introduction to Excel for Civil Engineers

51

2.5.2

POLYNOMIAL REGRESSION

Polynomial regression is used to obtain fitted points of a number of points data which
having non‐linear (curve) trend in XY coordinate system. Thus, polynomial regression
consists of fitted points of a number (n) of points data (x i, yi) to be a polynomial equation
form:
f (x) = amxm + am­1xm­1 + … + a1x + a0

(2.6)

In Equation 2.6, m = a positive integer and the equation is called a polynomial function of
order m. Linear regression is a special form of polynomial regression with m = 1, A = ai and
B = ao.

Figure 2.5: Example of two data series for polynomial regression
Example
Figure 2.5 shows two data series from the relationship between water density and
temperature, where X‐coordinate is the temperature (oC) and the Y‐coordinate is the water

An Introduction to Excel for Civil Engineers

52

density (g/cm3). By plotting Y‐values against X‐values, it is apparently visible that the
result has a curve trend.
Figure 2.6 shows chart of the plotted data from Figure 2.5 and regression lines created
using Trendline that consist of Linear and Polynomial of order 2 and 3. It is apparently
seen that the polynomial regression is much better than linear regression for this data
series. The fitted points produced from order 2 and 3 regression seemed like really
coincides with the data and are only distinguished by the value of R2, where order 3 (red
curve) the value of R2 = 1. Excel 2007 provides the type of polynomial regression up to
order 6.

Figure 2.6: Regression analysis using Trendline for data series in Figure 2.5

2.5.3

INTERPOLATION

In an engineering calculation, sometimes it only needs a data interpolation without resolve
it into regression analysis. Here is to find y‐value that correspond to known x‐value a line
segment, based on a number data (xi, yi) tabulation, where xi may continuously be
increased or decreased. Linear interpolation of y‐value corresponds to x‐value is:

In Excel, the interpolation value can be obtained by TREND function. Data series for this
function is a line segment formed by xi, yi and xi + 1, yi + 1.
Example 1: Linear Interpolation
Given below is two data series of X and Y‐array for interpolation:
Table 2.1: Two Data Series for Linear Interpolation
Data X

Data Y

0

‐1

1

0

2

4

3

5

4

‐1

5

‐2

6

0

7

3

Plotted in chart:
An Introduction to Excel for Civil Engineers

54

Worksheets below show formulas to obtain the interpolation of the data in Table 2.1, using
TREND function and Equation 2.7, respectively.
Using TREND function:

An Introduction to Excel for Civil Engineers

55

Using Equation 2.7:

The interpolation values are in column D, which correspond to new values entered in
column C. The y‐value that correspond to a x‐value that lies between 0 ‐ 1 in cell C3 is
shown in cell D3; the y‐value that correspond to a x‐value that lies between 1 ‐ 2 in cell C4
is shown in cell D4, and so forth.
TREND function and Equation 2.7 can also be used to interpolate x‐values from known y‐
values, by replacing the variable x with y and vice versa in Equation 2.7, or by switching x
and y‐argument in TREND function. The formulas are shown in the worksheet above at row
12, columns C and D.
Example 2: Logarithmic Interpolation
A sands soil sample of 1,000 grams weight is taken and placed in the sieving machine for a
sieve analysis test. The results are presented in Table 2.2, showing sieve size used and
percent finer. The percent finer (or percent passing) is calculated based on cumulative
weight of soil retained on each sieve. Chart of log grain sizes versus percent finer of soil is
presented in Figure 2.7.
An Introduction to Excel for Civil Engineers

56

Ta
able 2.2: Sie
eve Analysiis Results

A

B

1

Sieeve Size

Finer

2

(
(mm)

%

3

9.50

100.00

4

4.75

97.80

5

2.36

95.48

6

1.18

88.50

7

0.60

70.60

8

0.30

24.10

9

0.15

2.40

10

0.00

The nextt step is to o
obtain grain
n size distriibution in percentage o
of the weigh
ht of the sam
mple,
for an example
e
to obtain graiin size valu
ue correspo
onds to 60 percent fin
ner or D60.. The
correspo
onding valu
ue can bee obtained using Eq
quation 2.10. Formulaas for sem
milog
interpolaation are sh
hown in worrksheet as ffollows:

Plotted iin chart:

An Introd
duction to E
Excel for Civiil Engineerss

57

Figure 2.7: Semilog interpolation for the data of Table 2.2
Notes:
•

•

There is however more‐more flexible way to obtain data interpolation which to
resolve them with a macro (custom function), using Equation 2.7 or 2.10. When
entered into macro, formulas can be shortened and does not depend on a line
segment where a known value is placed. Discussion on the custom function macro
will be in the following chapter in the topic of user defined function.
The interpretation on sieve analysis result includes to determine how well the
gradation of sands soil. Gradation of sands or grained particles (silt to gravel) can be
measured by the coefficient of uniformity (CU) and the curvature coefficient (CC) as
defined below.

D60
D 302
CU =
C =
D10 , C D 60 D10

The greater the value of CU, the greater grain distribution range, where well‐graded
particles usually has CC value between 1 and 3.

An Introduction to Excel for Civil Engineers

58

2.5.4

STATISTICAL DATA

Histogram and Cumulative Distribution
In a statistical data presentation, it is convenient to make the data from observation into
histogram to see the data distribution. In histogram, the observed data plotted against its
frequency distribution and thus, we can get a visual summary and give quick impression on
the observed data.
Example
The following is concrete compressive strength data selected randomly from 40 samples
obtained from the test of characteristics compressive strength of concrete:
Concrete compressive strength test result
(kg/cm2)
376.28
399.30
375.41
370.41
394.43
409.47
393.53
371.43
387.19
374.34
386.30
372.44
390.35
392.29
389.46
390.30
401.27
386.78
390.37
384.82
379.95
361.23
379.08
362.28
380.97
402.90
380.10
400.86
389.33
368.22
388.44
366.35
384.64
399.20
383.76
407.46
396.34
365.36
398.83
363.51
To simplify data distribution, the result data divided into several classes within interval
(taken) = 5 kg/cm2, and then tabulated in Table 2.3. The scores from each class of data is
called class frequency, which is represented by height of the bar. To ease data distribution
process into classes can be done by using a function macro or custom function. The
discussion on custom function will be in the next chapter in the topic of user defined
function.

Figure 2.9: Cumulative frequency of concrete compressive strength Range, Mean dan
Standard Deviation
The classification or data distribution is generally defined into three statistical parameters,
which are, range, mean and standard deviation. Range is the difference between the lowest
and the highest values of data. From the concrete compressive strength test result, there is
a range of 48.24, from the lowest 361.23 to the highest 409.47 in kg/cm2 units. The mean
or average is the sum of the data values divided by the total number of data (n). From the
test, the mean is obtained = 384.87 kg/cm2. If a set of data values are divided into M classes,
the mean formula is written as follows:
M

∑m x
i

x=

i =1

n

i

(2.11)

Where,
mi = frequency of class interval i
xi = mid point of class interval i
Mean is a value that commonly used to represent values of a set of data. In consistent with
this, several measurements are made to state deviation, and one of them is the formulation
of mean square deviation (MSD). The MSD is also called the variance, and the formula is:

An Introduction to Excel for Civil Engineers

61

M

∑ m (x
i

i

− x) 2

i =1

n

(2.12)

Another measurement to be used is the standard deviation (S), which is the square root of
MSD:
M

∑ m (x
i

S=

i

− x) 2

i =1

n

(2.13)

Through Equation 2:13, the standard deviation from the test result as in Table 2.3 is
calculated = 12:49 kg/cm2, and with divisor of (n ‐ 1), calculated from the sample, MSD and
S is respectively calculated 394.74 and 16.12 kg/cm2. The standard deviation is a measure
of dispersion of a set of data values from its mean; where a big or small of the value
indicates the quality of job execution.
Normal Distribution
From the histogram and measurement of statistical parameters above, it can now be drawn
a normal distribution curve. The normal distribution function is,

f ( x, S , x ) =

1
S 2π

e

1 ⎛ x− x ⎞
⎟
− ⎜⎜
2 ⎝ S ⎟⎠

2

(2.14)

For log‐normal distribution, it can be obtained by replacing x with ln(x) in Equation 2.14.
Figure 2.10 and 2:11 show normal distribution curve that fits with the percent frequency
data in Table 2.3. Figure 2.12 shows the formulas used in the presentation of the test result.

Figure 2.12: Data presentation from the test result to obtain normal distribution and
the formula used in spreadsheet
Formulas used as follows:
Mean : =SUMPRODUCT(k,n)/SUM(n)

An Introduction to Excel for Civil Engineers

64

S : =SQRT(SUMPRODUCT(n,(k‐Mean)^2)/SUM(n))
Normal : =500/(S*SQRT(2*PI()))*EXP(‐0.5*((k‐Mean)/S)^2)
Normal Cumulative : =NORMDIST(k,Mean,S,TRUE)*100
Mean and standard deviation are respectively, calculated using Equation 2:11 and 2:13.
The actual scale for normal function (Equation 2.14) is determined from the interval used,
in this case were taken 5 kg/cm2. Multiply then by 100 to get the depiction in percent (%).
Excel has also the other ways to make distributions, such as Exponential, Weibull, Gamma
and Poisson.

2.5.5

CIRCULAR REFERENCE

When formulas are connected with their own cell, either directly or indirectly, then this
condition is called a circular reference. Excel by default can not process a formula that
contains a circular reference until we select Enable Iterative calculation by the following
steps: Office Button > Excel Options > Formulas. Iteration is the repetition of numerical
calculation until a condition is met. The process is executed based on iterations number
entered in the textbox Maximum iterations and Maximum Change for the maximum
change resulting from the last two iterations. Excel will stop counting when one of the
conditions is met, or whichever comes first.

Select to allow
iterative calculation

An Introduction to Excel for Civil Engineers

65

Cell with a circular reference in a spreadsheet as shown below:
Example 1

A

B

1

4

2

=B2

=(A1+A2)/3

Result :

1 x iteration

A

B

1

4

2

0

1.33333

5 x iterations

A

B

1

4

2 1.97531 1.99177

100 x iterations

A

B

1

4

2

2

2

In short, it can be explained herein about the iteration method ‐ to describe the
way of Excel executes Example 1. Formulas in cell A2 and B2 are interrelated and
have the form: x = f(x) and calculated by the following equation:
xn+1 = f(xn),

Excel will do this process based on the number of iterations (n) and the maximum change
x n +1 − x n that you specify, and will stop if one is met. This analogy is found in the process
of Newton Raphson Method function in Chapter 3, but of course, the method is different.
Application of Excel iteration can also be applied to solve system of linear equations, to
obtain unknown variables of equations. For an example, to obtain values of x, y which
satisfy the equations below:
2x + 3y = 7
3x ‐ 2y = 4
The solution is firstly to re‐write each of equation to form x and y:
2x + 3y = 7

Æ x = (7 – 3y)/2;

y = (7 – 2x)/3

3x – 2y = 4

Æ x = (4 + 2y)/3;

y = (4 – 3x)/2

Next, convert the formed x and y into formulas and placed them in cell B2 until C3 as
shown in the worksheet below. In order to perform iteration, the cell must be linked which
is in this operation the formula in cell B3 and C2 are to be interrelated.

10 x iterations

11 x iterations

An Introduction to Excel for Civil Engineers

67

Values of x and y that satisfy both equations are obtained at iteration 11, which are: x = 2
and y = 1.
Example 2

A

B

C

1

0

10

2

10

3
4

1 x iteration

A

B

C

10

1

0

10

10

=(B1+B3+A2+C2)/4

=B2

2

10

5

5

10

=(B2+B4+A3+C3)/4

=B3

3

10

3.75

3.75

0

0

0

4

0

0

0

5 x iterations

A

B

C

1

0

10

10

2

10 8.64128 8.64128

3

10 6.18032 6.18032

4

0

0

0

100 x iterations

A

B

C

1

0

10

10

2

10

8.75

8.75

3

10

6.25

6.25

4

0

0

0

In the cell B2 formula, the initial value for B3 and C2 are taken = 0. The value of B2 will be
used as the initial value in the cell B3 formula, where C3 = 0. The values obtained are then
used as a predicted new value in the next iteration.
In its application, for examples, the Excel iterative calculation or circular reference can be
used in the solution of linear simultaneous equations, slope stability analysis using Bishop
method (where the safety factor is on both sides of the equation), and the solution for
seepage below dam using finite difference method.

An Introduction to Excel for Civil Engineers

68

CHAPTER 3
CREATING MACRO
3.1 FUNCTION PROCEDURE
In addition to the functions that already exist in Excel, it is often to create a function to
perform calculations which is determined by the user. Such function is called user‐defined
(UD) function. This function will be added by Excel into a collection of functions and can be
used as well as a built‐in function.
Some UD functions are made by combining formulas and built‐in functions. UD function is
made in macrosheet or using VBA. Macrosheet is a sheet where macro is created by XLM
language, the original Excel macro language that is still maintained, at least until the Excel
2007. Figure 3.1 shows the UD functions to obtain cosine of an angle in macrosheet and
VBA.
A
1

COSD

Function Cosd(x)

2

=RESULT(1)

Cons Pi = 3.141592655

3

=ARGUMENT("X",1)

Cosd = Cos(Pi * x/180)

4

=RETURN(COS(PI()*X/180))

End Function

Figure 3.1: Function in Macrosheet (left) and VBA (right)
Creating Function in Macrosheet
To insert a macrosheet in worksheet, right‐click on the sheet tab to display a command
menu, click Insert > MS. Excel 4.0 Macro > OK, to insert a macrosheet named Macro1 as
shown below:
An Introduction to Excel for Civil Engineers

69

This sheet is seemin
ngly similarr to a workssheet, but th
here are colu
umns that aare wide enough
to write a function. The first row
r
is to write
w
the naame of funcction, for exxample, COSSD in
macro exxample in F
Figure 3.1. T
The second row is to deefine the typ
pe of output refers to T
Table
3.1, for example,
e
nu
umber, textt or an arraay, using RE
ESULT stateement and followed by
y the
data type. Next, is tto define vaariables or input data u
using ARGU
UMENT statement, follo
owed
by the naame and daata type.
User hass to determ
mine formulas used in the operatiion of functtion of the input variaables,
which is defined by ARGUMENT. A variablle can refer a name or ccell referencce of worksheet,
such as "x" of COSD
D for a cell iin the work
ksheet. A forrmula can b
be given a n
name to sim
mplify
writing the next formulas.
f
T
The
processs in writin
ng a function is endeed by RET
TURN
statemen
nt, which reeturns the ffunction vallue. Below iis an examp
ple of macro
osheet macro to
calculatee volume of a cube:

unction is ussed in the w
worksheet as follows:
MVOL fu

An Introd
duction to E
Excel for Civiil Engineerss

70

Tablle 3.1: Data
a Types in M
Macroshee
et
V
Value

Data T
Type

1

Nu
umber

2

Teext

4

Logical

8

Reeference

16

Error

64

Array

Creating
g Function in Visual B
Basic for Ap
pplication (
(VBA)
UD functtion is madee in a module that inserrted througgh Insert tab
b > Module
e in Visual B
Basic
Editor which
w
has been
b
shown
n previously
y in Chapter 1. Functio
on procedure is begin with
word fun
nction, follo
owed by itss name and ends with the word End Functio
E
n. The typee of a
function
n can be Single, Double or all data types of Vaariant, depeending on w
what it woulld be
produced, a numberr or an array. Data typees in VBA arre presented
d in Table 3
3.2.
The seleection on these
t
data types in ad
ddition to define a fu
unction and
d argumentts, or
variables, it is also related to the allocation of comp
puter mem
mory which depends on
n the
he greater memory alllocation). If
I an
range of the data type (the greater the value, th
nt is not deffined, it willl be classifieed as a Variiant and con
nsumes mem
mory more than
argumen
any otheer data typee. Defining d
data type in
n a program
mming is higghly recomm
mended beccause
it saves computer m
memory con
nsumption aand acceleraates the pro
ocess of VBA
A in recogn
nizing
the data type as welll, or in the o
other word to acceleraate VBA to exxecute a maacro.
T
Table 3.1: D
Data Typess in VBA
Data Ty
ype

Such as Excel, VBA also has many built‐in functions that have names similar to Excel built in
functions, say, Cos function in Figure 3.1 or Abs to find the absolute value of a number.
Some Excel built‐in functions can also be used in VBA by using the prefix Application.
Figure 3.2 is an example where Excel built‐in functions are used in UD functions MLIN to
obtain linear regression parameters. MLIN returns an array result (multiple result) which
is the slope of the line and the intersection with Y‐axis (Y‐intercept).
Function MLIN(Y, X) As Variant
Slope = Application.Slope(Y, X)
Intercept = Application.Intercept(Y, X)
MLIN = Array(Slope, Intercept)
End Function

Figure 3.1: Excel built­in functions in VBA
Notes:
•

Module with Option Explicit requires all variables are to be declared. If not, VBA will
show an error message "Variable not defined". To declare a variable use Dim,
ReDim, Private, Public and Static statement.
Example

Dim is to declare a size of variable that already set at the beginning of the program,
while ReDim is to change the size of variable that could be changed during the
execution of program. Variables declared by Dim or Redim at procedure level
(between Sub – End Sub statements) are available only to the procedure where the
variables are declared.
Example
Sub OptEx2()
Dim myVar as Integer
Dim myString(1 to 3) as String
Dim n As Integer
n = 3 'an input

3.2 SUB PROCEDURE
Macro that previously used to create a function can also be used to make an engineering
calculation program to solve more complex problem, such as structural analysis. In VBA,
such program is created in the Sub procedure, that is procedure with syntax as briefly
discussed in Chapter 1. In general, the process of input ‐ output data from a Sub procedure
is:
1. Read input data from the worksheet
2. To execute data input by VBA
3. Print the results to the worksheet
In Excel‐VBA programming, the value of input data is taken from the value of the cell or
range of cells on worksheet or a Range object with value as shown by its Value property. So
typing in the value in the cell in the worksheet with numbers, text, and so on actually is to
set the value on Value property of these cells. Cells property is commonly used to express
the Range object with the syntax Cells (row_index, column_index), i.e. cell (1,1) to return a
Range object of cell A1.
Here is an example to set a number to a cell in VBA languange, which sets the value of cell
A1 = 10:
Worksheets(”Sheet1”).Cells(1,1).Value = 10
Value is the default property of the Range object, therefore negligible or means no need to
be written again. If no object qualifier or an objects before period (Worksheets () above),
then a range of cells are on the active sheet, in this case from which the macro runs, for
example via a command button on the worksheet. Use an object qualifier before the range
of cells if the data is not located on the active sheet with the writing of Worksheet ("sheet
name").
To complete this topic of discussion, the following is a simple example of Sub procedure
named Pro_Sub1:
Sub Pro_Sub1()
Dim Length As Double

The abov
ve procedurre is to calcu
ulate the area of a rectaangle which
h is the multtiplication o
of the
length and width off its sides. T
The variables are declaared using the Dim staatement and
d the
data type is a numb
ber with dou
uble precisiion (Doublee). Cells property is creeated withou
ut an
object qu
ualifier, theerefore, the range of ceells is on th
he active sh
heet, which the active sheet
s
(opened
d) when the code prograam is execu
uted.
Sign apo
ostrophe (') is a sign wh
here prograam ignores tthe commen
nts in it. Thiis sign is used to
insert an
n explanatio
on. The defaault color of the text is ggreen.
The proccess of inpu
ut‐output of Pro_Sub1 p
procedure caan be explained as belo
ow:

If the procedure in the above ffigure is exeecuted, valu
ue of cell A4
4 or Cells (4
4,1) = 30. To
o run
the macrro, perform a sequencee of steps as described iin Chapter 1
1.11.
In creatiing large sccale applicattion prograam, normally, the program is divid
ded into sev
veral
sub‐proggrams or prrocedures SSub and/or with some functions tthat each haandles a speecific
task then they calleed with Calll statementt or the nam
me of functiion. The advantages off this

An Introd
duction to E
Excel for Civiil Engineerss

75

division are to make a programming become more focused, more readable and if an error
occurred it can be quickly addressed. Chapters 6 to 10 show the examples of such
computer programming.

3.3 CONTROL STRUCTURES
A program is created to be able to handle any changes either from the data input or to that
occurred during program execution. Therefore, there are necessary control structures in
order to make program remains on its specified steps. Such controls are looping and
branching or a combination of both.

3.3.1

LOOPING

Do While...Loop or Do…Loop While
Repeats a set of statements while a condition is TRUE. If condition becomes FALSE, the
program will exit the looping and move to the next code.
Syntax:
Do While condition

[statements]

[Exit Do]

[statements]

Loop
Example
Sub exLoop1()
i = 1
Do While i <= 5
Cells(1 + i, 1) = i
i = i + 1
Loop
End Sub

An Introduction to Excel for Civil Engineers

76

Do Until…Loop or Do…Loop Until
Repeats a set of statements until a condition becomes TRUE. If a condition becomes FALSE,
the looping will be discontinued.
Syntax:
Do Until condition

[Statements]

[Exit Do]

[Statements]

Loop
Example
Sub exLoop2()
i = 1
Do Until i >= 5
Cells(1 + i, 1) = i
i = i + 1
Loop
End Sub

For…Next
Repeats a set of statements a given number of times
Syntax:
For counter = start To end [Step step]

If…Then…Else
Executes a set of statements because of certain condition, when it is not appropriate then
move to another state of condition.
Syntax:
If condition (1) Then
statement(1)
An Introduction to Excel for Civil Engineers

79

ElseIf condition(2) Then
statement(2)
Else

statement(3)

End If
Branching is usually nested in a looping. If the condition is met, the program will exit the
looping with Exit command. If...Then…Else branching can also be combined with And, Or, or
Not statement to state the expression or the relationship between two expressions.
Syntax:
For…or Do Until…

statement(1)

If statement(1) And or Or expression(2) Then

statement(2)

Exit For or Exit Do or Exit Function or Exit Sub

End If

Next…or Loop
Goto…Gosub…Return
This branching is used in a procedure consisting of one or several blocks of statements
(subroutines) that is made for particular task or calculation. The block is then called upon
to do the job with GOSUB statements and returned with RETURN statement. GoTo
statement will branch to a code line, subroutines, or line label, and then continue to the
next code.
Example
Sub exBranch()
Dim myNum As Double
myNum = Cells(2, 1)

Sub Pro_Vol()
Dim a As Single, b As Single, t As Single
Dim volume As Single

a = Cells(2, 2)
b = Cells(3, 2)
t = Cells(4, 2)

Call Area(a, b, L)
volume = (L * t)
Cells(5, 2) = volume
End Sub

In addition to looping and branching defined above, there is also useful control structure
that is With statement.
With
Execute a series of statements on the same object
Syntax:
With object
[statements]
End With

3.4 USER DEFINED FUNCTION PROBLEMS
Problem 1
Create a macro to determine rectangular area where area = length x width.
Solution
If a and b express length and width respectively, area in macro can be expressed as a
function of (a, b) = a x b. If the name of the function is Area, the expression is:
Area (a, b) = a x b.
To solve this problem, we will use macrosheet languange. Insert a macrosheet by right‐
clicking on the worksheet Sheet tab > Insert > MS. Excel 4.0 Macro > OK. Type the macro
as follows:

Afterward, we have to define the function name so that it can be recognized by Excel and
incorporated it into Excel functions. The steps are as follows:
1. Click cell A1 where the name of the function (Area) is placed.
2. Click the Formula tab > Define Name to show the New Name dialog box as below.
Enter a macro name in the name text box. Macro name will automatically be
referred to cell A1 with step 1 above.

An Introduction to Excel for Civil Engineers

83

3. Seelect Functtion in the option buttton, so the function (by
f
y default) iss categorizeed as
U
User Define
d or you can choose a ccategory forr your functtion.
4. Click OK.
Now b
back to the worksheet. Area functtion is writteen as follow
ws:

Cells A2 and B2 aree respectively named a and b whicch carried o
out by follow
w step No. 1
1 and
2 above through acctive sheet. In this steep, there is no option (option button) for Macro
M
y in New Na
ame dialog b
box.
category
Problem
m 2
Find the area betweeen a straigh
ht line y = m
mx + c and tthe X‐axis, b
between x =
= a and x = b
b (c ≥
0). This w
will be solved using inttegral with fformula:
b

Function
ns that created in VBA will automaatically be eentered into
o Excel funcctions collecction.
The use of the functtion in work
ksheet is as follows:

If the teaacher wantts to apply a different
a
standard fo
or course sccoring, it is simply don
ne by
changingg value of th
he function arguments.
m 4
Problem
Create a
a VBA macrro to find vertical
v
streess distribu
ution below
w a foundattion with ellastic
equation
n from Boussinesq ‐ Newmark (1
1935). The equation
e
to
o get the veertical stress (σ)
below a corner of a rectangularr area b x l aat depth y as shown in Figure 3.3 is:

The advaantage to co
onvert an eq
quation or fformula into
o a function
n is that it greatly simp
plifies
the calcu
ulation. Thee above equaation with aa condition V1 > V certaainly would be very len
ngthy
and requ
uires a lot attention if
a
b built intto worksheeet formula. It is more easily solveed by
typing itt into VBA co
ode.
Now, giv
ven a found
dation with
h a size of 3 x 3 m is subjected to uniform pressure of
o 10
2
ton/m . The vertical stress diistribution of each intterval depth
h of 0.5 meeters below
w the
center o
of the found
dation is sho
own in the w
worksheet aas below:

Chart is created through Insert tab> Scatters with smoothlines and markers > Select
Data > Add and then select x and y‐data. To modify the chart click once on chart area then
click Chart Tools tab > Chart Layout > Layout 1, and then add chart titles by clicking once
on the title, select Edit Text to type. The depth orientation is made to increase downward
by right clicking the Y‐axis > Format Axis > Axis Option > Values in reverse order.
Problem 5
Find root x in equation: ax4 + bx3 + cx2 + dx + c = 0
Solution
This problem will be solved numerically by Newton‐Raphson method. The process of
calculation is done in an iterative technique, which root x is gradually approached.
Function of equation depicted in chart in Figure 3.4 and exemplified intercepts the X‐axis at
the point p and f (x) = 0 or (p, 0). In the first step, it is taken x1‐value to get tangent line at y
= f(x) which intercepts the X‐axis at the point (x2,0), where x2 is new approach value to p.
The next approaching process is done using x2‐value to get x3‐value and so on until xn → p.

n NRM (stan
nds for New
wton‐Raphsson Method) is now ussed to find tthe value off x in
Function
the equaation below:
‐2.79x3 ‐‐ 2:11 x2 + 2
24.51x + 36
6.68 = 0.
This equ
uation form
m can be fou
und in the geotechnica
g
al engineeriing to find the embedm
ment
depth of retaining wall with Free‐Earth
h Support Method. Th
he use of the functio
on in
worksheeet is as belo
ow:

Problem
m 6
Table X is a numbeer of random
m numeric data from 0
0 ‐ 100. Creeate macro to calculatee the
amount of data in Table X with a range of v
values from
m Y to Z.

Argument X is an array argument, in this example is the name that has been defined for the
table data in a range A2 to G7. Argument array can be also written in the following manner,
range(A2:G7) or a certain range, for example range(B2:C7), range(A7:G7) and so on. One
of the simplicities of using function in Excel because it is well supported by the spreadsheet
to create variable names, writing function and put the result at once.
Problem 7
Create a macro for linear interpolation example in Chapter 2.5.3. Use Equation 2.7 to solve.
Solution
Macro
Function Linter(Xrange, Yrange, nval) As Double
Dim i As Integer
Dim c, x
x = Xrange(1)
i = 1
For Each c In Xrange
If nval = c Then
Linter = Yrange(i)

Character "c" and "r" can not be used for variable names in macrosheet, because C
and R stand for column and row, respectively. However, you can define a variable
names such as "c_" or "r_", "cx" or "rx" and so on.

An Introduction to Excel for Civil Engineers

96

3.5 STRUCTU
URE OF PROGR
RAM
Data pro
ocessing in E
Excel‐VBA iis a sequencce of step; rread – execcution – priint, as descrribed
previoussly in Sectio
on 3.2, which
h is (rewrittten for yourr you convenience):
1. Read input d
R
data from th
he worksheeet
2. To execute d
data input by
y VBA
3. Print the results to the w
worksheet
In large data processsing eitherr from the in
nput data orr from proggram executtion, a comp
puter
program
mming (codiing) must b
be planned aand manageed properly
y. It is inten
nded in order to
make thee program fflow becomes simple (n
not complexx), saving co
oding, and itt is easy to ssolve
if an error is occurrred. In this ssection, thee three of th
he above steeps will be d
discussed th
hat is
how to ccreate Inputt Form, Outtput Form aand writing code in VB
BA module. IIn this topicc, we
are goingg to create aa civil structtural analyssis program
m.

3.5.1

INPUT OUTPUT FORM

In Excel‐VBA progrramming sttructure, in
nput‐output form is worksheet ittself and caan be
made in the same ssheet, e.g. on
n Sheet 1. F
For large inp
put data, for an examp
ple for strucctural
analysis program ass shown in Figure 3.4, it would bee better if th
he input datta extends to the
right, forr an example, the dataa entered heere is consist of numbeer of joint, ccoordinatess and
propertiies of structture elemen
nts. By creaating form widens
w
on the right siide, the locaation
(read: ro
ows) of dataa output is ttherefore caan be specifiied in the prrogram.

Figure 3.4 shows that how much the input data entered in the form does not change the
row numbers for data output. It thus obtains a fixed input‐output form and does not
depend on the size of the input data, so that code of read ‐ print steps can be specified in
the program.
Contrary to the input data, it is possible to extend the output data downward because end
of the row is not a boundary that has to be identified for program input. Therefore, the data
presented here following the common way in presenting the results of the structure
analysis extending downward to improve readability.

3.5.2

WORK WITH MODULES

A large data processing such as for an application program generally consists of several
smaller sub‐programs united into one unit for a goal that is well managed. What is meant
by sub‐program is VBA Sub procedure or function procedure given the scope of their
respective task and then placed into a module.
For the convenience, Author distinguishes each procedure into three modules according to
their tasks as follows:
Module 1: Main Module
Module 1 consists of data provision from input‐output form (see Figure 3.3), which is
mainly from: geometry data, materials data, determination of node support or fixities and
load data. Module 1 is the main module, which controls the process of read ‐ execution ‐
print.
Module 2: Calculation Process (Analysis)
Procedures in module 2 are associated with the theory of the structure and the analytical
methods used, e.g. building stiffness matrix, generate load matrix and compute nodal
displacement. Module 2 works based on data provided by Module 1.
Module 3: Creating Charts
Procedures in module 3 are associated with the presentation of data in graphical form, e.g.
for 1). Input data: geometry of the structure and loads, and 2). Data output: displacement,
moment and shear distribution. Module 3 works based on data provided by Module 1 and
Module 2.
Figure 3.5 shows in general how Excel‐VBA programming structure is depicted in a chart.
Figure 3.6 shows a chart of programming structure in real world; consist of main
procedures (abbreviated to facilitate in writing) that exist in TRUSS2D, program for truss
analysis as presented in Chapter 7.
An Introduction to Excel for Civil Engineers

In desiggning macro
os, the mov
vement bettween work
ksheet and Visual Bassic Editor (V
VBE)
window is often done
d
becausse both win
ndows are interconneccted. To opeen VBE, clicck on
Visual B
Basic icon Developer
D
tab and fro
om VBE clicck Microsoft Excel ico
on to go bacck to
worksheeet, or by pressing sho
ortcut Alt+F
F11 on both
h windows.. Actually, to acceleratee the
process of displayin
ng window iis to place ttogether in tthe monitorr screen as shown in Fiigure
program cod
de is executed, its interraction to th
he worksheeet can be vieewed
3.7. Thuss, if a VBA p
simultan
neously.
Here aree the steps to put a worksheet
w
a VBE in one screen
and
n and simplle techniques to
execute line by line::
1. In
n the worksheet wind
dow click Restore
R
Dow
wn icon in the top rigght corner, then
drag the sheet to the lefft, place it ap
pproximately half size of the moniitor screen. Drag
th
he mouse po
ointer on th
he worksheeet window eends to adju
ust its size.

2. N
Next, go to th
he VBE wind
dow, in the same way p
place it on th
he right side of the monitor
sccreen. There are now aalready both
h these wind
dows simulttaneously.
3. N
Now, go to the
t VBE by clicking itss window. Click
C
Close VBA Proje
ect icon to close
V
VBA Project
window on
n the left so only VBA m
module is no
ow visible ((to display b
back,
cllick icon Prroject Explorer or preess Ctrl+R). To start from
f
the firrst line presss F8
an
nd press F8
8 again to ccontinue to the next lin
ne. If you w
want to jump
p to the dessired
line press Ctrrl+F8 at thee cursor. Eaach line to be executed is marked in
n yellow.
4. Press F9 to sselect progrram line at w
which execu
ution will stop (breakp
point) when
n you
ru
un macro by
b clicking Run Macro
o (F5). Thee other way
y is to click
k the left bar at
program linee that you want
w
to set a breakpoiint until red
d dot appeaars. Click on
n the
reed dot to remove.
5. The variablee value com
mputed duriing program
m execution
n can be seeen its valu
ue by
atttaching a pointer
p
on a variable, as shown in Figure 3.8.
3 This varriable readss the
vaalue of the cell of the worksheet. Hold on a
a second thee pointer until the value is
displayed, which is the ccell value off the worksh
heet on the left.

An Introd
duction to E
Excel for Civiil Engineerss

100

Through step 1 and 2, the movement between worksheet ‐ VBE is simply done by clicking
one window without losing another window. This kind of method is very helpful if you
want to run the program and see the results at once (through step 3 and 4).
The above steps are very helpful in developing flow of program and solve errors as well.
Running step 3 and 4 is actually the process of tracing errors (debugging) by using
commands in the Debug menu and it is also similar to the process of program verification
(testing) by running code line by line. Testing and debugging is a routine activity to build a
program that requires a number of control structures (branching and looping). A condition
where a program is paused for the purpose of developing the code is called break mode.

Figure 3.7: VBA – Worksheet window is placed in one screen
One of error type that may occur is when element of an array smaller or larger than the size
that has been assigned to, so when program is running it will show an error message box
"subscript out of range". For example in a looping where the number of process exceeds the
subscript of an element. To get to the location where an error occurs clicks Debug, and
Reset to stop the program.

An Introduction to Excel for Civil Engineers

101

Figure 3.8: To see a variable value by attaching pointer

3.6 CHART MACRO
Excel chart is an object that can be manipulated by VBA through its methods and
properties, for example, in an aim of adding lines (Add series), changing the line color,
border color, creating line styles and setting the thickness (Weight). By creating macros,
the job becomes very easy, fast, and advances to the most difficult level, which would not
be done manually.
In Chapter 1 we have discussed how to make lines in the worksheet with formulas and
tables. In this section, it will be created using macro instead of what has done manually. For
a case, Example 3 of Chapter 1.10 will be used here ‐ which is to create truss structure
drawing with joint and coordinate tables as shown below:
Table of joints
Joint
1
2
3
4
5
6

By creating macro, the data of x1, y1, x2, y2 of above table do not need to be formulated
because it will automatically be created by the program. The Macro is shown as follows:
Sub MChart1()
Dim npt, nln, i
npt = 12 'no. of joint
nln = 21 'no. of line
ReDim x(npt), y(npt)

Now, we focus on three blocks of statements enclosed by a red line, as below. The blocks
description are given thereafter.

An Introduction to Excel for Civil Engineers

104

Descrription:
•

•

•

Block 1 is th
B
he same to tthat done using VLOO
u
KUP formu
ula in Chaptter 1. This is the
emptied linee coordinatees in the sh
heet table above
a
becau
use it is now processeed by
V
VBA.
Figure out this, fo
ormulas as many as 21
1 rows x 4 columns (8
84 formulass) for
crreating 21 llines be is ssimply replaaced with o
only 6 VBA sstatements without lim
mit to
any a numbeer of lines th
hat will be created, 100
0 or 200, 100
00, etc.!
B
Block 2 is co
de to activaate a Chart tthat to be m
manipulated,, in this casee is Chart1. Note
th
hat, we will never (ev
ver) create a new Charrt (Charts.A
Add) becau
use it will make
m
co
ode longeer. Instead, we will proceed
p
maanually (th
hrough Inse
ert > Charrts >
Scatters) an
nd format th
he chart as d
desired. To iidentify a ch
hart name right‐click on
n the
C
Chart Area >
> Assign Ma
acro, and th
hen see the name in thee Macro Na
ame text boxx.
B
Block 3 is ste
eps that we do with Ad
dd Series in
n the Select Data Sourcce dialog bo
ox for
data entry (ssee Chapterr 1). This VB
BA looping replaces jo
ob for line ccoordinates data
n done manually.
entry which takes a longg time when

ve as below::
Excel input form for the chart macro abov
An Introd
duction to E
Excel for Civiil Engineerss

105

If you want to go further in creating chart it can be continued by setting its properties, such
as line color, marker style and color, or line style (continuous, dashed). The following is
code to set the Chart properties (code enclosed by red lines)

The above examples is about setting the type and color of lines, which is respectively,
continuous (xlcontinuous) and red (color index = 3). This code uses Border property of the
Selection object to return Border object with its properties that are ColorIndex, Weight and
LineStyle. Selection represents SeriesCollection(index) object of ActiveChart object that is
an active chart named Chart 1.

3.7 MANIPULATION ON PROGRAM STEPS
An application program (for specific application) built through a sequence of steps that
took quite long process, start from defining problems, to find solutions, build algorithms, to
write and debug the code. An algorithm is defined as a sequence of logical steps in problem
solving.
Algorithm is closely related to the use of the control structures in program that are
branching and looping that have been discussed earlier. In array data basis such as
structural analysis, it is necessary to have little bit of knowledge how to manipulate of
program steps or flow of array data using control structures. For examples, is to read and
print data, changing the composition of elements of an array and create integer variables
for storing data.
Example 1
Given a simple example; look at an array data in the worksheet as shown below. Build
elements of array on the left side to be as on the right side, where only the main diagonal
value will still remain while the other = 0.

Here is a code to solve Example 1, starts from reading the data, manipulating the data (code
in red line) and print the results:

An Introduction to Excel for Civil Engineers

108

Sub ExSTEP1()
Dim i, j, n
ReDim myArray(6, 6) As Integer

'Read input data
For i = 1 To 6
For j = 1 To 6
myArray(i, j) = Cells(2 + i, j)
Next j
Next i

'Print ouput data
For i = 1 To 6
For j = 1 To 6
Cells(2 + i, 7 + j) = myArray(i, j)
Next j
Next i
End Sub

The code (outlined in red line) is the Author’s version. Every programmer must has own
steps to proceed, based on his/her interpretation in the same problem. Similarly, if you are
a programmer or whose accustomed to use Excel‐VBA.
Example 2
Create an elements of an array in Example 1 in sequence from bottom to top or from the
largest subscript (myArray (6,6)) to the smallest (myArray (1,1)).

Example 3
Assemble the upper triangular array on the left side into the arrangement shown on the
right side, as below.

This example is made as an exercise for readers to make their own code. As previous
examples, the data manipulation in this example is done and completed within two looping.
Use always the Debug menu to verify the program steps. Good luck and hopefully these
examples are helpful to develop your Excel‐VBA skill.
Note:
An algorithm or in the (Author’s) other words is a manipulation on program steps (flow)
is basic procedure to tell computer how the program will be executed step by step.
Although it may formally a guideline to be followed but everything is gained from
experiences and also exercises. So, the coding more depends on instinct or
interpretation of an author on the problems faced. However, it was still agreed that the
fewer steps needed to solve a problem, the better the program built, meaning the faster
execution time by the computer. Thus, it is not because of the fewer program lines
created.
A Note for Beginners:
If you are a beginner in program algorithm, it is fine because your lesson has just started
here. Run the program code of Example 1 and 2 step by step using the Debug menu (for
example by pressing F8) and be focused on the code in the red lines. In break mode, see
how the algorithm for both examples and try to understand the idea behind those code
writing. After you have understood, try to solve Example 3 or any problem you define by
yourself, for example that are developed from Example 1 and 2.

An Introduction to Excel for Civil Engineers

111

CHAPTER 4
MATRIX PROGRAM
The calculation using matrix operation is very suitable and easy to be performed by a
computer. Thus, in line with rapidly growth of computer usage, matrix method is becoming
very popular and widely used instead of the analysis with manual method. It is widely used
in mathematical siences or in civil engineering field.

4.1 MATRIX DEFINITION
A matrix is a rectangular array which involves a series of numbers of constituent
components or elements.
Example:

⎡ a11 a12
⎢a
⎢ 21
⎢a
A = ⎢ 31
⎢−
⎢−
⎢
⎢⎣am1 am 2

. . . . a1n ⎤
⎥
⎥
⎥
⎥
⎥
⎥
⎥
amn ⎥⎦

The notation for a matrix is usually used [] and () or {} for row or column matrix. A matrix
can be written [A] = [aij], where aij is element of the matrix, i = 1,2, .. m and j = 1,2, ... n.
Subscript m indicates the number of rows and n for the number of column, or called has
order (m x n). If m = n, then called a square matrix.

Scalar matrix
Scalar matrix is a diagonal matrix where the diagonal elements are the same number.

⎡4
⎢0
⎢
⎢0
⎢
⎣0

0 0 0⎤
4 0 0⎥⎥
0 4 0⎥
⎥
0 0 4⎦

Unit matrix
Unit matrix is a scalar matrix in which the value of elements = 1. This matrix is also
called identity matrix and generally denoted by [I]. Multiplying by [I] will return the
origin matrix.

⎡1
⎢0
⎢
⎢0
⎢
⎣0

0 0 0⎤
1 0 0⎥⎥
0 1 0⎥
⎥
0 0 1⎦

Band Matrix
Band matrix is a square matrix (n x n) where the non‐zero elements are grouped and
form a diagonal elements band.

Matrix Inverse
Division is not defined in a matrix operation, for it used the inverse of a matrix. Matrix
inversion is only done on square matrix and written with the notation [ ] ‐1 . Multiplication
by matrix inverse is a division by the matrix. For example: [A] x [B] = [C] where [A] is a
square matrix, the equation can be written into a division operation: [B] = [A] ‐1 [C]. [A] ‐1 is
called the inverse of [A].

An Introduction to Excel for Civil Engineers

116

Matrix Transpose
If [A] is a matrix of order (m x n), then the transpose of [A] is a matrix of order (n x m) or
written as [A]T. Rows and columns of [A] become the columns and rows of [A]T.
[B] = [A]T
bij = aji
Example

⎡1 2 3 ⎤
[A] = ⎢
⎥
⎣4 5 6⎦

⎡1
⎢
[A]T = ⎢ 2
⎢⎣ 3

4⎤
5 ⎥⎥
6 ⎥⎦

Some properties associated with matrix transpose:
([A]T)T = [A]
([A]+[B])T = [A]T + [B]T
([A][B])T = [B]T [A]T
System of Linear Equations
Many problems in civil engineering are represented by systems of "n" linear equations with
unknown number "n" and this can be solved using matrix methods. It is often, the number
of equations equal to number of unknowns.
Example: two systems of equations
3x + 2y = 9

Simply to say that the solution of systems of "n" linear equations in matrix form as
expressed in Equations 4.2 is finding {X}, where [A] and [B] are known values. There are
many methods used out there to solve Equation 4.2, some will briefly be discussed in
examples below.
1.

Elimination – Back Substitution
Example

2x + 3y = 7

…(1)

3x – 2y = 4

…(2)

This is an example of two linear equations with 2 unknowns, x and y.

Elimination
Eliminate x from both equations by subtracting Equation 2 from Equation 1 that has
been given a multiplying factor (Eq. B1):

Directly get the inverse of [A] or [A] ‐1
From Equation 4.2:
[A]{X} = {B}
Multiply both matrices by [A]‐1:
[A]‐1[A]{X} = [A]‐1{B}, it is known [A]‐1[A] = [I]
[I]{X}=[A]‐1{B}
{X}=[A]‐1{B}

Thus, if [A] ‐1 is known, Equation 4.3 can be solved.

Example
Given two a system of two linear equations:

2x + 3y = 7
3x – 2y = 4

In this example the matrix inverse will be found using by Gauss‐Jordan method. The
solution is to put the unit matrix [I] next to coefficient matrix [A] such that when the
process is finished [A] become [I] and the previous [I] become matrix inverse [A] ‐1.
The overall process can be just expressed as:

[A M I ] → [ I M [A]-1 ]

An Introduction to Excel for Civil Engineers

119

The process is done through elementary row operation on [A] and [I] as well to
reduce [A] to [I]. Thus, it is a sequence of row operation to turn elements of [A] = 1
on the main diagonal and zero elsewhere. For detail see the following example:
Derive matrix coefficient:

⎧ x ⎫ ⎧2 ⎫
⎨ ⎬=⎨ ⎬
⎩ y ⎭ ⎩1 ⎭
3. Iterative Method
This method is actually an approach and done by taking initial value of the unknown
then corrected or refined to enter the next value. We have examined the iterative
method on the example above in the previous discussion in Chapter 3. Here is now
an example from a system of three linear equations:
5x + 4y +3z = 12

(4.4)

4x + 7y + 4z = 15

(4.5)

3x + 4y + 4z = 11

(4.6)

The iterative process is quite long, so in this example we will be working with help
of a computer through Excel iteration.
Re‐write Equation 4.4, 4.5 and 4.6 into forms below:
An Introduction to Excel for Civil Engineers

121

Equation 4.4:

x = 12/5 – 4y/5 – 3z/5 = (12 – 4y – 3z)/5

Equation 4.5:

y = 15/7 – 4x/7 – 4z/7 = (15 – 4x – 4z)/7

Equation 4.6:

z = 11/4 – 3x/4 – 4y/4 =(11 – 3x – 4y)/4

Next, convert the equations into worksheet formulas and enter in cell B2, C3 and D4.
However, until this stage no iteration to be done because the formulas are still
standing on their own equations.

The iterative process will be carried out after these connections:
The value of y, z of Equation 4.4 = value y, z of Equation 4.5
The value of x, z of Equation 4.5 = value x of Equation 4.4 and z of Equation 4.6
The value of x, y of Equation 4.6 = value x, y of Equation 4.5
In the worksheet, the equations are done as follows:

The iteration steps shown in the worksheet below:
1 x iteration:

An Introduction to Excel for Civil Engineers

122

5 x iterations:

10 x iterations:

20 x iterations:

50 x iterations:

The iteration process of finding {X} of Equation 4.4 is actually to make x, y, z to the same
value on the worksheet columns B, C and D in satisfying the given equations. It appears that
they are very close at 50th iteration and by rounding obtained:
⎧ x ⎫ ⎧1⎫
⎪ ⎪ ⎪⎪
⎨ y ⎬ = ⎨1⎬
⎪ z ⎪ ⎪1⎪
⎩ ⎭ ⎩⎭

An Introduction to Excel for Civil Engineers

123

4.2 PROGRAM FOR MATRIX OPERATIONS
Multiplication and inversion of matrix are two main operations in matrix equation found in
most of engineering calculations using matrix method. In this discussion, it will
demonstrate how to create program code for multiplication and inversion of matrix.
Matrix Multiplication
In the following example is given a program to calculate the multiplication of two matrices.
The program is named MMULT1, input data and the results will be done on Sheet1.
Example
Find the multiplication of two matrices in the following equation:
[C] = [A], [B]
where,
⎡ 2 3⎤
⎡ 3 3⎤
[ A] = ⎢⎢ 4 1 ⎥⎥ , [B ] = ⎢
⎥
⎣ − 1 5⎦
⎢⎣7 4 ⎥⎦

We know that the multiplication of a matrix is the multiplication of element row in one
matrix by element column of other matrix. If done manually as in the example of Section
4.1.2, code to get [C] is:
Sub MMULT1()
Dim MA(3, 2) As Double
Dim MB(2, 2) As Double
Dim MC(1 To 3, 1 To 2) As Double

In this example, [A] has order 3 x 2 and [B] has order 2 x 2 to return [C] of order 3 x 2. Input
data of elemen [A] is made on a range of cells (6,2) to cell (8,3), while element [B] on a cell
range (6.5) to cell (7.6). To run the program, click the command button that has been made

An Introduction to Excel for Civil Engineers

125

in the worksheet. The results are printed on a range of cells (12, 2) to cell (14, 3), using
statement FormulaArray = [C], that shown below:

For a large‐sized matrix, the given MMULT1 example is inefficient in term of wasting time
and coding to write code for all matrix (row) operations one by one. Instead, use For­Next
looping for reading input data, to process and print the results. This way saves a lot of code
writing.
The code program of above matrix multiplication can be modified by giving the row and
column indices (i, j and k) to [A], [B] and [C], which is done in three looping below:

In a looping above, m and n represents number of rows and columns of [A], respectively,
while n and p represents number of rows and columns of [B]. In the following example, we
will use the modified program code to multiply a 6x6 matrix by a 6x3 matrix. The program
is named MMULT2.
An Introduction to Excel for Civil Engineers

For i = 1 To m
For j = 1 To p
Cells(15 + i, 1 + j) = MC(i, j)
Next j
Next i
End Sub

A

B

C

D

E

F

G

H

I

J

K L

1

2

[A] =

mxn

m =

6

3

[B] =

nxp

n =

6

4

[C] =

mxp

p =

3

5

6

[A] =

20

5

0

0

0

0

[B] = 0.25 0 0

7

5

20

0

0

0

0

0.25 1 0

8

0

0

30 10

0

0

‐0.50 1 0

9

0

0

10 30

0

0

‐0.50 0 1

10

0

0

0

0

12

6

0.25 0 1

11

0

0

0

0

6

12

0.25 0 0

12

13

14 [C] =[A].[B]

15

16

[C] =

6.25 5

0

17

6.25 20

0

18

‐20 30

10

19

‐20 10

30

20

4.5

0

12

21

4.5

0

6

22

An Introduction to Excel for Civil Engineers

128

In this program, the printing of the results that used Array Formula has been replaced by
two orders of For‐Next looping. The first and second looping is respectively for printing
rows and columns of [C]. This way is much better in term of automation, because we do not
need to calculate the range of the array that will be generated as it has been known = m x p.
Matrix multiplication can also be done in a worksheet using Excel built‐in function MMULT
(array1, array2). For MMULT2 procedure example, a formula can be written as follows:
= MMULT(B6:G11,J6:L11).
To print the result use INDEX function or you can enter the formula above as an array
formula. See about array formula in Chapter 1.5.
Matrix Inversion
In finding an inverse of matrix, Gauss‐Jordan method is among the most popular method to
be used and programmed, with a process that has been described in the previous section.
The steps are an effort to turn a matrix to be searched its inverse (e.g. [A]) becomes an
identity matrix [I] and at the same time, [I] to [A] ‐1. That is done by elementary row
operation as ever shown in the previous example.
In the program, this can be done by taking one matrix e.g. [C], such that at the end of the
process [C] turns into [A] ‐1. In the beginning is to divide the first row by element a11 of
[A], in an effort to turn a11 = 1 so that c11 = 1 / a11 and this is analogous to that performed
in [I]. If [A] is n x n matrix, then in the first step the first column of [C] is analogous of that
performed in [I], while the second column to column n is analogous of that performed in
[A]. At the end of step 1, the value of [C] of order 3x3 can be written as follows:
⎡ 1
⎢
⎢ a11
a
[C ] = ⎢⎢− 21
a
⎢ 11
⎢ − a31
⎢⎣ a11

a12
a11
a12
a 22 +
. − a 21
a11
a
a32 + 12 . − a 31
a11

⎤
a13
⎥
a11
⎥
a13
a 23 +
. − a 21 ⎥⎥
a11
⎥
a
a33 + 13 − a31 ⎥
⎥⎦
a11

In the second step, the first and second column of [C] is analogous of that performed in [I],
while the third column to column n is analogous of that performed in [A], etc.
Program for finding inverse is created in VBA and provided here. It notes that matrix
inversion requires square size (say, m x m) matrix and the determinant of matrix must not
be zero. For the purpose to create worksheet form, the maximum element of matrix is
limited to m = 6, but it can certainly be made more than 6.

An Introduction to Excel for Civil Engineers

129

Example 1
Find the inverse of [A] = [5].
Solution for Example 1:

Example 2
Find the invers of,
⎡2 3 ⎤
[ A] = ⎢
⎥
⎣ 3 − 2⎦

Solution for Example 2:

Example 3
Find the invers of,
⎡1 3 3 ⎤
[ A] = ⎢⎢1 4 3 ⎥⎥
⎢⎣1 3 4⎥⎦

Solution for Example 3:

An Introduction to Excel for Civil Engineers

130

To find inverse of matrix in worksheet can also be used the built‐in function MINVERSE.
The way to use it is the same as MMULT.
VBA code:
Sub Inverse()
On Error Resume Next
m = Cells(3, 2)
ReDim MA(1 To m, 1 To m) As Double
Range("k5:p10").ClearContents

'Read input data
For i = 1 To m
For j = 1 To m
MA(i, j) = Cells(4 + i, j)
Next j
Next i

4.3 MATRIX METHOD FOR STRUCTURAL ANALYSIS
Structure can be analyzed as a series of structural elements which are connected to each
other at endpoint called node. Solution of this system of arrangement of elements can be
expressed by simultaneous linear equations that are made in a matrix form. In matrix form,
then the calculation of the structure becomes easier to be solved with the help of a
computer.

4.3.1 UPPER STRUCTURE
The method that will be used for structural analysis is direct stiffness method. A brief
introduction to the basics of this method will be given here; readers are encouraged to read
other references to supplement the discussion on this topic. In short, the aim using of this
method of analysis is to obtain force ­ displacement relationship expressed as:
{P} = [K]{X}

where,

{P}

vector of external force acting on node

[K]

structure stiffness matrix

{X}

vector of nodal displacements

An Introduction to Excel for Civil Engineers

(4.7)

132

By deriving [K], thus it can be obtained force ‐ displacement relationship as expressed by
Equation 4.7. One of the processes here is to derive stiffness matrix of an element, then
applied for other elements and put together to form stiffness matrix of the whole structure.
To meet deformation continuity and compatibility between the elements is by superposing
corresponding force ‐ displacement components.
Furthermore, to simplify the calculation we can partition element of the stiffness matrix at
the nodes that are free to displace (free nodes) and fixed (zero displacement at the
supports). If Xb represents displacements vector at the support, Xf represents
displacements vector at free nodes, Pf and Pb are the external forces vector which
correspond to their displacements, Equation 4.7 can be written as follows:

The displacements vector at free nodes is then obtained using Equation 4.9 that becomes
{Xf} = [Kff]‐1{Pf}. If {Xf} is known then the internal forces of the element can be obtained. In
this relation, the internal force of element i is expressed in local coordinate and obtained by
the following equation:

{P}i = {Po} + [K]i{X}i

(4.11)

where,
{Po}

fixed‐end forces: reactions at end of element i due to external load directly
acting on element i

[K]i

stiffness matrix of element i in local coordinate system

{X}i

deformations matrix = [T]i{Xf}

[T]i

transformation matrix of element i

To find the support reactions, it can be directly obtained in the global coordinate of the
structure using Equation 4.10 becomes:
An Introduction to Excel for Civil Engineers

133

{Rb} + {Pb} = [Kbf]{Xf}

(4.12)

or,
{Rb}= [Kbf]{Xf} – {Pb}

4.3.2 SUB STRUCTURE
The structure analysis in this subject is to obtain a force ‐ displacement relationship of soil ‐
structure interaction which is modeled by the means of beam on elastic foundation.
Generally it is solved with the finite element method where the system is built by a series of
finite elements connected at endpoint called node.
Some equations are made in order to find force ‐ displacement relationship at the nodes,
and begins by writing the relationship between external forces at the nodes {P} and
internal forces of element {F} in principle of equilibrium.
The relationship between {P} and {F} is expressed as:
{P} = [A]{F}

(4.13)

The relationship between node displacements {X} and deformations of element {d}:
{d} = [B]{X}, and from reciprocal theorem it can be shown
{d} = [A]T{X}

(4.14)

From elastic behavior of element, the relation between {F} and {d}:
{F} = [S]{d}

Subsituting {X} back to Equation 4.16 to find internal forces {F}.
Equations 4.16, 4.17 and 4.18 are the fundamental equations in the analysis using finite
element method (J.E. Bowles, 1982).

An Introduction to Excel for Civil Engineers

134

CHAPTER 5
NUMERICAL METHOD
Many problems in science and technology involve the use of integral and differential
equations. If the mathematical equation model is quite simple, then it can be solved by
means of analytical method. However, many of those equations are difficult or may be
impossible to be solved analytically. Instead, it must be solved numerically or using
numerical methods.
The result of the numerical method is an approach to its exact value by means of
analytically. Thus, it is just an approximate calculation with estimated accuracy. Numerical
methods will provide many benefits if done by a computer.

5.1 NUMERICAL INTEGRATION
The integral of a function f(x) from a to b is written:
y = ∫ f ( x)dx
b

a

has a broad sense as the area bounded by the curve y = f(x) and the X‐axis, the between x =
a and x = b.
Analytical solution to integral or exact solution is:

y=

b

∫ f ( x)dx = [ F ( x) ] = F (a) − F (b)
b

a

(5.1)

a

where, F(x) is an anti‐derivative of f(x) such that F'(x) = f(x).
Numerical integration to calculate area under the curve uses the concept of approach
whereby the area is divided into smaller pieces of area in such that, if combined the total
area of the pieces approaching the exact result. In this example we will use trapezoidal
method where the area under the curve is divided into small pieces of area with sides f(xn)
and f(xn‐1) that resembles a trapezoid as shown in Figure 5.1.

An Introduction to Excel for Civil Engineers

135

Figure 5.1: Trapezoidal method for approximating area
Trapezoids are having the same width for Δx, if there are n intervals (or n trapezoids) from
a to b, then the Δx = (b ‐ a) / n as shown Figure 5.1. Figure 5.2 shows the comparison
between the area obtained from the total area of the trapezoids and the exact results
obtained using Equation 5.1. The formula to find total area (L) is given by:
Ltotal =
=

Figure 5.2: The comparison of area resulted from trapezoids (left) and the exact one
(right)

An Introduction to Excel for Civil Engineers

136

Figure 5.1 and 5.2 show, the calculation by means of trapezoidal method is getting closer to
the exact result when taking more trapezoids into account, or by making smaller Δx.
Furthermore, to complement this discussion we will examine the given example below via
VBA.
Example
Find the area bounded by the parabola y = 4x ‐ x2, the X‐axis, from x = a and x = b. Find also
the exact value.
Solution
VBA code
Function Trapez(a, b, n)
a = Cells(3, 2)
b = Cells(4, 2)
n = Cells(5, 2)

In this experiment, we take a = 0 and b = 4 respectively, for n = 10 and 100. The values of a,
b and n are then used as function arguments of Integf and Trapez in the above program.
The use of function in the worksheet and the results are shown below:

In the worksheet, the area calculated using trapezoidal method and its exact value is
respectively expressed as A_num and A_exact. This experiment shows that the smaller
width of trapezoids taken with n = 100, the closer A_num to A_exact, compared with n = 10.
However, the more thorough results requires a longer calculation process and it is certainly
suitable done with the help of computer.
The other methods for approaching area, for examples are by the Simpson method and
Gauss quadrature.

Equation 5.4 is Newton's second law of motion that states force (F) acting on an
object = mass (m) of the object multiplied by its acceleration (a) where, acceleration
(a) is the first derivative of velocity (v) with respect to time (t) and the second
derivative of position (x).
Equation 5.5 shows the relationships between bending moment (M) at any point on
the X‐axis and transverse deflection (v) of the beam. EI is flexural rigidity of the
beam.
Equation 5.6 is the partial differential equation of one‐dimensional consolidation,
where u, t, z are respectively, excess pore‐water pressure, time and the depth of clay
layer. Cv is a coefficient of consolidation where the value is assumed to be constant.

Numerical differentiation solution is an approach to continuous differential form into
discrete or finite model. In this discussion we adopt Taylor approach for it is among the
most popular and widely used in introducing numerical approximation. Taylor theorem for
approaching a function is graphically shown in Figure 5.3.

Figure 5.3: Taylor theorem for approximating a function

An Introduction to Excel for Civil Engineers

139

The value of the function at point xi+1 located at a distance Δx from point xi can be
approximated by Taylor series below:
f ( xi +1 ) = f ( xi ) + f ' ( xi )

where,
‐ All derivatives of function through the point (x i, f(xi)).
‐ Rn: truncation error when the series counted up to n‐order.
Taylor series will give an estimation of the function correctly if all derivatives in the series
are taken into account, where n = infinite number. However, in practice, the use of Taylor
series is done by simply taking the first few terms of the series.
Equation 5.7 of the Taylor series can be derived into the discrete form (finite) as shown in
Figure 5.4. The estimation of first derivative of a function f'(x) is approached through
several slope of the line through the value f(x) at x = xi‐1 (point A), x = xi (point B) and x =
xi+1 (point C) as in Figure 5.4. From Equation 5.7, with respect to first derivative:

Subscript i, j on the function f(i, j) is a simplification of f(xi, yj) form. A grid points of
function values in the xy coordinate system is shown in Figure 5.5, it is used to estimate
derivative of partial differential with respect to x and y.

Figure 5.5: Two dimensional grid points of a function

An Introduction to Excel for Civil Engineers

142

Taylor’s discretization method whereby derivative of a function is approached as the
difference value of a function between the given independent variable (e.g. x1) and a small
increment (e.g. x1+Δx), is called the Finite Difference Method (Gilberto E.U, 2004). An
approach using finite difference method implies that the smaller Δx is used the better the
result closer to the exact value as shown from an approach of slope of the line in Figure 5.4.
In Chapter 10, we will build a program using finite difference method for the one‐
dimensional consolidation based on Equation 5.6. In the process, it is necessary to put a
multiplier into input data to divide interval of the independent variables (e.g. Δx) to be
smaller so that the solution is expected close to exact result. Program is created in VBA and
of course, with the advantages of making chart automation to present the result.

An Introduction to Excel for Civil Engineers

143

CHAPTER 6
PROGRAM FOR 2D FRAME STRUCTURE
ANALYSIS
The matrix program as described in Chapter 4 is now implemented for analysis of structure
in this chapter and the next few chapters. It is expected that these series of references for
Excel‐VBA programming will be complementary, as basic knowledge for readers to make
an application program.
Please differentiate between the purpose of programming in Excel and the aim to build an
executable program (using Visual Basic or C++ programming language). Using Excel means
the program as a whole to be convenient and practical for it is created case by case. Each
case is packed in such a way to utilize the facilities on the spreadsheet and then presented
as in creating a technical report. This is consistent with the nature of working with Excel.

6.1 CASE EXAMPLE
Structure of plane frame is composed of 3 members with 4 m long each. The stiffness of
member BC is 2 times of the stiffness of members AB or CD. Point B, and C are the free
nodes, each has 3 displacement components.
1 ton
q = 0.6 ton/m'
0.5 ton

B

C
2 EI

4.0 m

EI

EI

4.0 m

D

A
4.0 m

An Introduction to Excel for Civil Engineers

144

All members of the frame are made of the same linear elastic material with modulus of
elasticity, E = 2.1 x 106 ton/m2. Dimension of member AB = BC = CD = 30 cm.
The given load as the following:
•

Sign convention represents the direction of the forces ‐ displacements with reference to
either the global coordinate or the local coordinate. It is set as follows: axial and shear
forces follow the right‐hand rule and the moments follow the right‐hand screw rule, or in
the easy way it is evident from Figure 6.1, i.e. positive when in the direction of the positive
axis.
8

5

B

C

4
9

6

2

A
3

7

Positive direction
for forces and
displacements

11

D

1
12

Y
10

X
Z

Figure 6.1: Force and displacement components at nodes in global coordinate system

An Introduction to Excel for Civil Engineers

145

5

2

4

1
3

B

6

C

4
5

2

C

B
3

6

1

1

A

2

D
6

3

5

4

Figure 6.2: Members force and displacement components in local coordinate system
5

8
7

4
6

B

9

C
8

5
4

B

7
9

6

2

A

C

11

D

1

3

10

12

Figure 6.3: Members force and displacement components that conform to global
coordinate system
The process of frame analysis consists of the following steps:
1. Stiffness matrix for plane frame member is derived as the following:

Member stiffness matrix is then superposed with other member, which altogether
form global stiffness matrix or called [K] structure. This process is written:
Superpose [K]s Æ [K] structure

(6.4)

4. Displacements at the free nodes to be determined by Equation 4.9 that becomes:
{Xf} = [Kff] ‐1{Pf}

(6.5)

5. Internal forces on each member can be found by firstly transforming displacements
from global coordinate back to local coordinate,
{X}i = [T]i{Xf}
After the displacements of member {X}i are obtained, then internal forces on each
member can be found using Equation 4:11:
{P}i = {Po} + [K]i{X}I

From above sequence of process then we can create a simple program for frame structure
analysis named FRAME2D.
Programming:
In designing matrix program, it is convenient to mastering design‐build in matrix‐based
data. In this relation, is how to make matrix variables of Equation 6.3 to 6.7. There are a few
things in coding that actually require a little imagination of a programmer.
For instance is how to assembly [K] structure. For that purpose, it requires to manipulate
program steps, so that [K] members can be placed on their position in [K] structure. The
idea is simply creating an integer variable, namely dindex, to equalize subscripts of [K]
member. Dindex reads index (numbering) of members joint displacements in the global
coordinate system and uses the subscripts to store [K] members in [K] structure and do the
superposition at once.
The program code is as follows:

'Storing members and superposition
For i = 1 to NM 'NM= no. of member
For j = 1 To 6
For n = 1 To 6
S(Idm(j, i), Idm(n, i)) = S(Idm(j, i), Idm(n, i)) + M(j, n, i)
Next n
Next j
Next i

Notes:

•

•

Idm variable is the same displacement index as stated by dindex variable, but
declared at a different level. Idm is at public‐level, while dindex is at procedure‐
level. For more clearly understand how these variables are proceeded you can see in
FRAME2D code provided in the Attachment.
The above code is for completion of the description (6.4).

The other thing is to partition (separation) of the structure stiffness matrix to form [Kff]
and [Kbf] as written in Equation 4.8. To do this, it uses two integer variables namely Ifr to
form [Kff] and Ifx to form [Kbf].
The sequence of steps to partition the structure stiffness matrix is as follows:
1.

After getting {Xf} then member deformations are calculated by using relationship:
An Introduction to Excel for Civil Engineers

155

{X}i = [T]i{Xf}

Member internal forces are calculated by using Equation 6.6:
{P}i = {Po} + [K]i{X}i
Support reactions are calculated by using Equation 6.7:
{Rb} = [Kbf]{Xf} – {Pb}
The result of the analysis is presented in FRAME2D input‐output form as shown in Figure
6.4 and 6.5 below:

Data input is blue text.
Scale for presenting output data is divided into 2 cases: true scale for beam members
(continuous beam) and auto scale for frame structure.
NM: number of structural members
NJ: number of joints or nodes
FRAME2D distinguishes 3 types of loading: selftweights by inputting material
density (row 16), nodal loads (row 27 to 30), and uniform loads (row 32 to. 33).
When Plot Geometry button is clicked it shows Chart Window of the geometry of
structure to give geometry verification to that inputted before the program is
executed by clicking RUN Program button. It is expected to minimize kind of errors
that caused by numbering system of the members and joints.
Chart Window command used in Plot Geometry is only compatible with Excel 2003
and earlier, which is a version where FRAME2D was created (also the next few
programs). When running it in Excel 2007 version, Chart Window (as shown in
Figure 6.4) will not be displayed. However, readers can see it placed before
displacement chart in the input‐output form.

An Introduction to Excel for Civil Engineers

157

•

Figure 6.5 shows chart of displacement as well as diagrams of moment, shear and
axial forces that are drawn automatically within the program. It is also included
maximum values occurred on nodes. Sign convention used for presenting diagrams
of the moments and internal forces is described in Chapter 6.2.
• The above calculation does not show the optimum result as the displacement of the
second member looks like a straight line, contrary to the fact that the member is
subjected to uniform load of 0.6 t/m'. Therefore, it is necessary to expose the result
thoroughly by dividing all members into smaller members.
• With the intention of the above point, the frame members is now re‐inputted into
smaller members, so that it becomes NM = 12, and NJ = 13. The results are then
shown in Figure 6.6 and 6.7.
The displayed geometry in chart window (only available in Excel 2003) from re‐inputted
data is shown below:

An Introduction to Excel for Civil Engineers

158

Figure 6.6: FRAME2D input­output form – Re­inputted

An Introduction to Excel for Civil Engineers

159

Figure 6.7: Charts of displacement, diagrams of bending moment,
shear force and axial force – Re­inputted
The results shown in Figure 6.7 and 6.8 are much better than in Figure 6.5 and 6.6. Thus, it
is convenient to divide into smaller members and for this example at least 4 smaller
members with the same length for each member. Avoid odd divisors (e.g. 5, 7, 9 etc.) so that
the results in the middle of span can be exposed.

6.2 SIGN CONVENTION FOR DIAGRAM
To depict diagrams of moment, shear force and axial force in members, program refers to
the sign convention as follows:

Positive direction is as shown in the above picture and ruled as follows:
An Introduction to Excel for Civil Engineers

160

‐ Bending moment (M) is given a positive sign if causing the upper fibers of members
is compressed or to curve upward, and is given a negative sign if to curve
downward.
‐ Shear force (V) is given a positive sign if rotates the member clockwise and is given
a negative sign if rotates the member counter‐clockwise.
‐ Axial force (N) is given a positive sign if pulls the member apart, and is given a
negative sign if pushes the member.
This convention is adopted in order to have same interpretation on the chart depiction of
the application programs in the next few chapters, thus it is more easily interpreted and
also design‐oriented. The program automatically differentiates the moments and forces
that occur in members section, which is blue line inside the curve for the positive
direction and red for the negative direction. The example below shows the coloring for
bending moment result:

Red = negative moment
Blue = positive moment

An Introduction to Excel for Civil Engineers

161

6.3 APPLICATION
FRAME2D is now applied to the following beam problem:

The beam can be seen to have three main members that are, roll‐load segment, load‐roll
segment, roll‐fixed node, have lengths 3, 3 and 5 meters, respectively. In practice however
it needs to divide the beam into smaller members for exposing the result more accurately.
FRAME2D is then inputted from which each of main members is divided into 4 equal parts.
So, there are 12 members and 13 joints. When the program has run, the charts will be
shown as below.

An Introduction to Excel for Civil Engineers

162

CHAPTER 7
PROGRAM FOR 2D TRUSS STRUCTURE
ANALYSIS
Member of plane truss is analyzed with consideration that carries only axial force, thus,
there are two associated displacement components at both ends with respect to axial force.
Truss member is generally made of steel or aluminum connected one to another as pin
connection.
Stiffness matrix for plane truss member is derived as the following:

⎡ AE
⎢ L
⎢ 0
⎢
[K]i = ⎢ AE
−
⎢ L
⎢⎣ 0

AE
L
0
AE
L
0

0 −
0
0
0

⎤
0⎥
0⎥
⎥
0⎥
⎥
0⎥⎦

Transformation matrix to convert vector components from local coordinate to global
coordinate is as the following:

⎡ cos α
⎢− sin α
⎢
[T]i = ⎢ 0
⎢
⎣ 0

sin α
cos α
0
0

0
0
cos α
− sin α

0 ⎤
0 ⎥⎥
sin α ⎥
⎥
cos α ⎦

(7.1)

The equation of member stiffness matrix in global coordinate system:

[K]s i = [T]iT[K]i[T]i

[K]s of the member is then superposed on the appropriate force‐deflection components to
get global stiffness matrix or called [K] structure. The internal forces and support reactions
can be determined by firstly partitioning [K] structure at free nodes and at supports
according to Equation 4.8.
An Introduction to Excel for Civil Engineers

163

7.1 CASE EXAMPLE
The following example includes a sequence of steps in the analysis for plane trusses based
on matrix method as described in Chapter 4. It also gives some notes for writing code in
Excel‐VBA. This section is complement explanation for previous FRAME2D example.
Given a truss structure is shown in Figure 7.1, includes numbering system in Figure 7.2 to
7.4. Each of member are made of the same linear elastic material, with E = 2,100,000 kg
/cm2 and cross‐sectional area A = 68.4 cm2.
Truss as a whole has only two degrees of freedom (DOF) which is at vector components 3
and 4, while both supports are pinned‐pinned thus will have no any translation.
20 ton
10 ton

B

4.0 m

A

Y

C

2
3.0 m

X

3.0 m

Figure 7.1: Geometry and loading condition on plane truss
4
3

6

2
1

2

Y
5

X

Figure 7.2: Force and displacement components at nodes in global coordinate system

An Introduction to Excel for Civil Engineers

164

3

1

4

2

4

2
2

1

4

1

3

3

2

Figure 7.3: Members force and displacement components in local coordinate system
4

4
3

3

6

2

5

1
2
1

6
2

5

Figure 7.4: Members force and displacement components that conform to global
coordinate system
The first step is to build member stiffness matrix with reference to either local or global
coordinates. In the program, it is done within one looping through the member number,
and made in a separate procedure in Module 2.
Program code:
Sub Stiff_Mtx(MK() As Double, S() As Double)
Dim sms As Double

The above code is about to build the members global stiffness matrix as shown in
Equation 7.1, and then store them to form [K] structure and do the superposition at
once.
Each member matrix (variable M) has three indexes in it; the first and second states
rows and columns subscripts of the matrix, respectively, while the third index states
matrix of member i.

An Introduction to Excel for Civil Engineers

166

•

Index Idm is an integer variable that reads displacement indexes of ends members,
the same as used in FRAME2D.

The program for truss analysis is modified from FRAME2D and named TRUSS2D. Below are
the results of each step of the analysis.
Member Stiffness Matrix
[K]AB

The next step is to partition [K] structure at free nodes and supports. Manipulation on
program steps to establish [Kff] has been discussed earlier in FRAME2D example.

0.00 ⎤
⎡ 20684 .16
[Kff] = ⎢
36771 .84 ⎥⎦
⎣ 0.00

From Equation 4.9,
{Pf} = [Kff]{Xf}

An Introduction to Excel for Civil Engineers

169

{Xf} = [Kff]‐1{Pf}
⎧ 0.00048 ⎫
⎬
{Xf} = ⎨
⎩− 0.00054⎭

After displacements of free nodes are known, the internal forces of each member can be
determined using equation:
{P}i = {Po} + [K]i([T]i{Xf})
[T]i{Xf} is displacements vector transformed from local to global coordinate system.
In this example, no external load directly acting on each member thus {Po} = 0. The
equation to determine the internal forces of member becomes:
{P}i = [K]i([T]i{Xf})
The values of internal forces of each member are shown below:
{P}AB :

{P}AC :

{P}BC :

4.167

0.00

20.833

0.00

0.00

0.00

‐4.167

0.00

‐20.833

0.00

0.00

0.00

The magnitude of support reactions can be found using Equation 4.12:
{Rb} = [Kbf]{Xf} – {Pb}
There is no external load directly acting on supports thus {Pb} = 0. The equation to
determine the support reaction becomes:
{Rb} = [Kbf]{Xf}
In this stage, we have to establish [Kbf] first. It uses the same way as to establish [Kff], which
to partition using integer variables. The completed code is similar to that in FRAME2D:
'Indexing for matrix subscript
Sub Mindex(index() As Integer, IFr() As Integer, IFx() As Integer)
'Member end-displacements index:

We have known that variable Idj = Ifr, Irj = Ifx, but they are declared at different level which
is at public and procedure level, respectively. With the above code, the subscript i,j of [K bf]i,j
associated with this example are:

The results of the internal forces of members and supports reactions are then plotted on
the truss chart as shown in Figure 7.5. Negative sign is used to indicate compression state
along the member, and positive sign for tension state.

An Introduction to Excel for Civil Engineers

172

20 ton
10 ton

B

-4.167 ton

2.50 ton

-20.833 ton

C

A
3.333 ton

12.50 ton

17.667 ton

Figure 7.5: Depiction of result from 2D truss analysis
Check balance of forces at point B:

Color coding for axial forces is the same as used in FRAME2D. Lines ‐ which are lines
that perpendicular to long section ‐ of member 1 and 3 are red; it means negative
axial forces occurred on members (compression).

An Introduction to Excel for Civil Engineers

175

7.2 APPLICATION
Now, TRUSS2D will be used to analyze a truss bridge with geometry as shown in Figure 7.8.
Given number of bridge joints = 8, number of members = 12, which is supported by a pin
and a roll support. Each member of truss is made of the same linear elastic material with E
= 2,100,000 kg/cm2 and cross‐sectional area A = 132 cm2. The loading data is given as
follows:

-

Vertical load = 40 tons and horizontal load = 2 tons, at the joint as shown in Figure
7.8.
Selfweight of all the members.

Figure 7.8: A truss bridge structure
The data of geometry, material and loading are then inputted in the input‐output form of
TRUSS2D as shown in Figure 7.9. Numbering system of a given truss bridge can be seen in
the chart window (only available in Excel 2003) or in the worksheet shown as below:

An Introduction to Excel for Civil Engineers

176

Figure 7.9: Input­output form of TRUSS2D for given truss bridge

An Introduction to Excel for Civil Engineers

177

Figure 7.10: Charts of displacements and diagram of axial forces for given truss
bridge
For a kind of truss, for instance, a truss tower that requires extended upward lengthwise
chart, you can change a chart aspect ratio by resizing it. To do this, take the following steps:

•
•
•

Click mouse on chart > move pointer on chart border > drag the sizing handles that
located in the border to the size, e.g. like a chart size as shown below.
The other way is to click mouse on chart > Format tab > in the Size group > enter
the value in Shape Height and Shape Width.
If font sizes change during chart resizing, click on the chart or at any text and then
set the font size through Font Size setting on the Ribbon.

An Introduction to Excel for Civil Engineers

178

An Introduction to Excel for Civil Engineers

179

CHAPTER 8
BEAM ON ELASTIC FOUNDATION
Beam loaded on elastic foundation (BOF) is a theory that is often used in force ‐
displacement prediction for the structural analysis that interacts directly with the ground.
Elastic foundation is an approach to soil behavior as a collection of linear elastic springs,
independent and one with another unrelated (Winkler soil). This theory is more solved by
numerical methods, especially by finite element method (FEM).
The fundamental of FEM for BOF used herein are Equation 4.13 until 4.18, and the
notations are used to express the relationship of external force‐displacement components
to those at nodes and internal force‐deformation components on elements of finite element
in Figure 8.1. Elastic foundation is represented by a series of spring constant at element
nodes.
Nodal P-X:

Furthermore, in developing the program it is convenient to give boundary condition that
restricts displacement by means of giving a fixed support at selected node. The magnitude
of displacement at this node is = 0.
The advantage of FEM over classical solution of BOF is that properties from element and
soils that are not constant such as weights, dimensions, flexural rigidities of beam and soil
Ks along the beam can be easily incorporated into the calculation.

8.1 CASE EXAMPLE
An application program for beam on elastic foundation as described above is named BOF,
which is used the same programming structure as previous TRUSS2D or FRAME2D. This
program is now used for a footing analysis above ground with soil modulus Ks. The footing
geometry and loading condition are as shown in Figure 8.2.
Given example:
Modulus of elasticity of the footing, E = 217000 ton/m2
Height x width x length of the footing = 0.3 x 2 x 2 m
Ks = 1800 ton/m3
Vertical load = 20 tons and moment load = 4 ton.m, acting in the column axis, right in the
center of the footing.

An Introduction to Excel for Civil Engineers

182

20 ton

4 ton.m
0.85 m

0.85 m

0.3 m

2.0 m

Figure 8.2: Footing on elastic foundation analysis
The input and output data of the given example is presented in the input‐output form of
BOF in Figure 8.3. The footing structure consists of 10 members, with a continuous
numbering system as shown in the following worksheet chart:

An Introduction to Excel for Civil Engineers

183

Figure 8.3: Input­output form of BOF for footing example

An Introduction to Excel for Civil Engineers

184

Figure 8.4: Charts of displacements and diagram of moment shear and soil pressure

An Introduction to Excel for Civil Engineers

185

Notes:

•

•

In the input‐output program, in Join ‐ Displacement ‐ Pressure column contains
output data of soil pressures. In practice, soil pressures result is to be checked
whether the given loading on the foundation does not exceed the allowable bearing
capacity of the soil (qa). Thus the maximum value should not exceed qa.
Unlike FRAME2D and TRUSS2D, there is iterative process in BOF to check whether
there are nodes that tend to separate from the soil (x < 0), because the soil spring
only accept pressure. Therefore ASAT matrix needs to be rebuilt with Ks = 0 at those
nodes. Here, foundation selfweight shall be incorporated into the calculation. The
result is then checked and if necessary, repeats this step until convergence is
achieved, where,
Convergence Æ number As(n) = number As(n‐1), at iteration nth.
As is active spring at X positive.

•

•

For a case of retaining wall or pile foundation where the soil spring is modeled on
both sides of the structure, the soil spring would always work where the one will be
active and the other will be non‐active. Thus, it is convenient to give an option to
select limits on nodes where iteration will be performed. The intended limits can be
found in Chapter 8, where BOF is modified for vertical structures.
Sign convention of moments and shear forces for graphic despiction is to follow the
sign convention described in Chapter 6.2, which is based on the changes of structure
shape after loading. Also, blue line in the curve is for positive direction and red line
for a negative direction.

An Introduction to Excel for Civil Engineers

186

8.1 APPLICATION
The following example is just a program verification on a beam problem with uniform load
as seen in Figure 8.5, and the results are then compared with the output of FRAME2D for
the same problem. Both programs should provide the same result because they use the
same analysis method and theory of structure as well.
Here, no soil property is involved so that Ks = 0. There are 3 supports of the beam, which is
fixed at point A (R = 1, T = 1), pinned at point B (R = 0, T = 1) and fixed at point C (R = 1, T =
1).
The given data as below:
Beam:
Length = 18 m
Width = 2 m
Height = 0.5 m
Load:
Uniform load = 0.6 ton/m’/m width of the beam
The output of BOF is presented in Figure 8.6.

Figure 8.5: Beam with uniform load of q

An Introduction to Excel for Civil Engineers

187

Figure 8.5: BOF solution for beam example

An Introduction to Excel for Civil Engineers

188

CHAPTER 9
LATERALLY LOADED STRUCTURE
Theory of beam on elastic foundation can also be applied for the analysis of structures
subjected to lateral loads. Here, BOF program was slightly modified in which the
orientation of the beam is now perpendicular to X‐axis, and the external forces ‐
displacement can be set whether lead to positive or negative axis. Load on nodes profile
also to be displayed for a quick verification on inputted pressure magnitudes at both
elements ends. The program for the laterally loaded structure is named XLAT.

9.1 CASE EXAMPLE
Given retaining wall example with geometry model and soil, pile and anchor data shown in
Figure 9.1. The figure also shows finite element (FE) model and numbering system.
Find:
- Magnitude of displacements, distribution of moments and shear forces along the wall for
design purpose.
- Forces and stress acting on anchor for design purpose.
After the FE model (Figure 9.1) is established, now is to find lateral pressure behind the
wall acting from ground surface to the dredge line using active pressure coefficient from
Coulomb or Rankine. You can either input the lateral pressure to 1 node below the dredge
line.

An Introduction to Excel for Civil Engineers

189

Figure 9.1: Model of retaining wall
Slight modifications were made in calculating fixed‐end forces of element to take pressure
profile that increases linearly with depth (triangular distribution) into account. See the
description on this picture:

Equivalent load at node due to pressure on element:
'equivalent joint load due to pressure
For i = 1 To NM
For j = 1 To 4
For n = 1 To 4
Peq(Idm(j, i), i) = -Pa(Idm(j, i), i)
Next n
Next j
Next i
'sum load = joint load + eq.load
For i = 1 To NP
Ps(i) = Pj(i)
Next i

The above code is the same as used in the previous programs to find the equivalent
load on nodes.
Variable Idm states displacement index of elements ends. It has been described
earlier in the previous chapters.

To generalize program where variation in the physical characteristics of the structure can
be incorporated into calculation, thus the input of height of beam, t, in BOF is replaced by
moment of inertia, I, in XLAT:

The following chart window (Excel 2003) will appear by clicking on Plot Geometry button.
It shows finite element geometry and pressure profile that have been inputted into the
program refer to Figure 9.1.

An Introduction to Excel for Civil Engineers

192

Modulus of subgrade reaction or spring constant of the soil (Ks) of sand layer in the front of
the wall is assumed to increase with depth by the approach taken below:
ks = As + Bs.Zn. ton/m3,

= 1000 + 2000.Zn Æ above GWL

= 600 + 1100. Zn Æ below GWL

where,

As = 40(c.Nc+0.5γ.B.Nγ)

Bs = 40(γ.Nq)

z = depth from ground surface

Nc, Nγ, Nq = bearing capacity factors

n = 0.5, adopted exponent for non‐linearity of Ks

Anchor rods are made of steel, with the specification shown in Figure 9.1. The value of rod
anchor spring per meter width of the wall is obtained by the formula:

An Introduction to Excel for Civil Engineers

193

K =

AE
sL

12.6(2.0 × 107 )
= 1400 ton/m.
=
3(6)104

Input‐output form of XLAT is shown in Figure 9.2 below:

Figure 9.2: Input­output form of XLAT for retaining wall
Notes:

•
•

Unit used in this example is ton ‐ meter to equalize the values of calculation
parameters used in this example.
In the form, there is an input for joint excavation (point O) that is J excavation, as
input, in which iterative process done from the top of the wall up to this point. Note
that, iteration is not done below point O because the presence of soil spring on both
sides of the wall. Definition of the iteration and its purpose are described in Chapter
8.

An Introduction to Excel for Civil Engineers

194

•

•

To perform iterative process (to check the soil spring direction) up to joint n, enter J
excavation = n + 1. In relation with this, iteration is not performed if J excavation =
1.
To design the anchor rod, please check the reaction force in anchor and calculated
the stress with steps below:

Check:
Paxial = 3 x 4.852 = 14.557 ton (c.t.c = 3 m)
fs =

Paxial 14 .557
=
= 1 . 155 ton/cm2
12 .6
A

So, the specification chosen for the anchor rod must be adequate to resist the above
force and stress.
The result of moments and shear forces are then made into diagram shown in Figure 9.3.
For the wall design purposes, it can be taken maximum positive bending moment (blue
line) working below anchorage, while negative moments (red line) is located around the
anchor and around the wall tip.

An Introduction to Excel for Civil Engineers

195

Figure 9.3: XLAT output chart

9.2 APPLICATION
Given pile foundation example with a diameter of 0.406 m embedded in sand layer to a
depth of 16 meters. Pile and soil data are as shown in Figure 9.4. Pile head is assumed as
fixed connection against rotation but free to translate.
Find:
- Maximum load applied on the pile head for its limited magnitude of displacement of the
pile head.

An Introduction to Excel for Civil Engineers

196

Figure 9.4: Pile foundation example
XLAT results of this example are shown in charts in Figure 9.5, where horizontal load, H, is
adopted = 100 kPa. Displacement that occurs in the pile head is 0.0054 m (at joint 1) as
shown in the Figure. From load and displacement linear relationship, any increase of load
of 1 kPa will produce pile head displacement of 0.054 mm.
Thus, for a case where pile head displacement is limited to 5 mm, the allowed maximum
load is then = 5/0.054 kPa = 92.6 kPa.

An Introduction to Excel for Civil Engineers

197

Figure 9.5: XLAT chart for pile foundation example

An Introduction to Excel for Civil Engineers

198

CHAPTER 10
ONE DIMENSIONAL CONSOLIDATION
In this chapter we will apply numerical solution using finite difference (FD) method for
Terzaghi’s theory of one dimensional consolidation. This method is based on a grid of time,
t, versus depth, h, as shown in Figure 10.1. The depth of soil layer is divided into m equal
parts of thicknessUz, while the period of time is divided to n equal parts of interval Ut.
The equation of one‐dimensional consolidation according to Terzaghi's theory is:
∂u
∂ 2u
= cv
∂t
∂z 2

(10.1)

Finite difference forms of Taylor series for the first and second order derivatives of
functions have been shown in Chapter 5, and for both sides of Equation 10.1 can be
written:
u
− ui, j
∂u
= i +1 , j
∂t
Δt
u
− 2 u i , j + u i +1 , j
∂ 2u
= i −1 , j
∂z 2
Δz 2

The degree of consolidation (U) at time t can then be obtained by finding area under initial
isochrone (from initial excess pore pressure distribution) and area under isochrone at time
t, given by the following equations:
half closed layer:

∫ u dz
∫ u dz
d

U = 1−

0
d
0

(10.6)

(10.7)

i

open layer:
2d

U = 1−

∫ u dz
u
dz
∫
0
2d
0

i

where d is the thickness of clay layer. Isochrone is a curve of excess pore water pressure,
ue plotted against depth for certain t as shown in Figure 10.2. The blue line represents a
variation of the initial u while the magenta line represents the pressure at time t.
Both Equation 10.2 and Equation 10.5 can be easily programmed, because the analysis is
based on an equilaterally grid. This looks like to build an array of function values (u) with
size of, for example, m x n (see Figure 10.1). The solution is simply taking a looping through
grid points in an array; thus, to find of excess pore water pressure at a specified period of
time (n) can be determined by giving specified value in a looping.

Figure 10.2: Isochrone example at specified time
In practice, the use of Taylor series adopts the first few terms omitting the higher order
derivatives and the error due to truncate the series can be reduced to minimum when β  =
1/6. Seeing this condition, n and m can be then directly determined to satisfy a fixed β  = 1/6
for both layer conditions. If m is an input data, then Equation 10.3 and 10.4 can be re‐
written to determine n as follows:

In the process of finding U, the input data of depth and the initial u are automatically re‐
inputted by the program where is each multiplied by 10 (default by the Author) or m = 10 x
im in the above code. For doing so, the interpolation technique is done to get the adjacent
values. The discussion of numerical methods in Chapter 5 demonstrates that dividing
interval of the independent variables into smaller intervals provides the result closer to the
exact value, and it is similar to what we will be doing here in the process of finding U
(numerical integration) as well as final u (numerical differential).
Manipulation for getting smaller FD grid interval is as follows:
- Code to get initial depth input, dz, and initial u, IP:
For i = 0 To im
dZ(i) = i * h / im
IP(i) = Cells(9 + i, 2)
Next i

Re‐inputted with a divisor 10 and perform interpolation to get adjacent values using
rsdZ and RSIP variables:

Figure 10.2 shows example of open layer isochrone at time t with initial u increases linearly
with depth. The area under isochrone curve between y = 0 and y = 8 is then sought. For
doing so, FDC uses trapezoidal method (in h/m unit) in which the total area is cumulative
sum of h/m segment areas done in a looping over values of re‐input dZ and IP. A detailed
code can be found in FDC program in the Attachment.

10.1 APPLICATION 1
The profile of excess pore water pressure (u) over the depth in saturated clay layer from
impact load is equal to the vertical stress distribution below foundation as in Problem 4 of
Chapter 3, as shown in table below:

An Introduction to Excel for Civil Engineers

205

Depth (m)

u (ton/m2)

0.0

10.00

0.5

9.76

1.0

8.63

1.5

7.01

2.0

5.49

2.5

4.28

3.0

3.36

3.5

2.68

4.0

2.17

4.5

1.79

5.0

1.49

5.5

1.26

6.0

1.08

Suppose the clay layer is half‐closed layer, where water flows across the upper limit. Given
cv = 7.19 m2/year and the thickness of layer is 6 m. Find the distribution of u and average
degree of consolidation (U) at 6 months.
Answer
FDC result is shown as the following:

Depth
(H/m)

Initial After (t)
Pressure Pressure

0.00

10.00

0.00

0.50

9.76

0.55

1.00

8.63

1.06

1.50

7.01

1.52

2.00

5.49

1.89

2.50

4.28

2.17

3.00

3.36

2.35

3.50

2.68

2.46

An Introduction to Excel for Civil Engineers

ave U
46%

206

4.00

2.17

2.51

4.50

1.79

2.51

5.00

1.49

2.50

5.50

1.26

2.48

6.00

1.08

2.47

10.2 APPLICATION 2
From field piezometers measurement on the clay layer beneath embankment, the
distribution of excess pore water pressure, u (after subtracted by its theoretical pressure)
is shown in the table below:
Depth (m)

u (kN/m2)

0.0

39.00

2.0

44.40

4.0

50.50

6.0

45.50

8.0

42.40

10.0

40.00

Answer
The input‐output form FDC is shown below:

An Introduction to Excel for Civil Engineers

207

Notes:

•

•

The advantage of finite difference method is that any pattern of excess pore water
pressure can be incorporated into the calculation. The input data can be based on
the initial field condition or load that works gradually, for examples, in the
completion of the consolidation problem below dam or embankment of soil.
The purpose of FDC programming for one‐dimensional consolidation based on
numerical methods has actually been achieved, that is at the stage where the degree
of consolidation based on areas under isochrone at time t can be calculated.
However, if you wish to go further with the solution, a curve of t versus U as shown
below can also be presented. This curve is created automatically by the program to
include 10 different t values into a looping, starts with t = 0.1 (years), then t = 0.5, 1,
2, and so on, and plotted on a semi‐log chart.

An Introduction to Excel for Civil Engineers

208

An Introduction to Excel for Civil Engineers

209

CHAPTER 11
AUTOCAD SCRIPT FILE
To graphically depict outputs of a program, alternatively, you can plot the outputs in
AutoCAD software instead of using Excel chart. Here, you can work further with charts
drawing with either a 2D or 3D model. The main advantage of AutoCAD is because this
software is specifically built for composing drawings.
Script file meant here is a text file that contains a sequence of commands executed by
AutoCAD. The sequence of commands can be made on a worksheet or VBA to speed up and
simplify the process of creating drawings. The script file is made by a word processor such
as Win. Notepad or Microsoft Word in ASCII format and given with a .scr extension.
In this discussion, we will be working with AutoCAD 2007 version.

11.1

CREATING SCRIPTS IN WORKSHEET

Before creating a script, you should get familiar with AutoCAD commands and associated
sequence of entries through command prompt (not remotely via menu button). For
example, a command to create a line is Line, and followed by entries from coordinates of
the first point, second and so on and ended by pressing Enter.
Example 1
The following is an example of steps sequence in AutoCAD to create a drawing of 3 lines
segments using Line command through command prompt:
Command: line
Specify first point: 0,0
Specify next point or [Undo]: 1,1
Specify next point or [Undo]: 2,3
Specify next point or [Close/Undo]: 3,3
[Enter]

An Introduction to Excel for Civil Engineers

210

From the above steps, it can therefore be made the following text (script) replaces what
was done above:
Line
0,0
1,1
2,3
3,3 [type 2 blank spaces for an Enter, to end Line command]
Open Notepad program, type the script above, and do not forget to type 2 blank spaces
after the coordinates of the last point to end the Line command. Afterward, save it with .scr
extension, for example named Line.scr. Now, open your AutoCAD, type scr at the command
prompt to display the Select Script File dialog box as below:

Select Line.scr file at the location you have placed before and click Open, then AutoCAD
will execute all commands in script file. The result is shown as below:

An Introduction to Excel for Civil Engineers

211

Notes:

•

•

AutoCAD commands consist of prompt entries that include some options that must
be entered in by the user. These options can also be 1). A command related to the
main command and, 2). Attributes to an object, for example, properties of a line, a
circle or a rectangle.
The best way to understand a sequence of a command (e.g. Line) is firstly to trying
out it in AutoCAD, then noted down all entries, including the requested options
associated with the main command. This becomes the basis used for preparing the
script later.
The following is Line command and the required prompt entries:

Command
Entry (Coordinate)

An Introduction to Excel for Civil Engineers

212

O
Options:

•

•

mmand, so lines are drrawn up to p
point
Pressing Entter at the last line is to end the com
co
oordinates of the prev
vious line. Pressing u
u is to do Undo
U
comm
mand; undo
o the
previous linee segment, while c is to
t do Close
e command;; close the last segmen
nt by
co
onnecting itt to the firstt segment.
Pressing Entter on keyb
board is to eend a comm
mand. In scriipt, it is don
ne by givingg one
blank space o
or create a b
blank line.

An Introd
duction to E
Excel for Civiil Engineerss

213

•

•

•

•

Some AutoCAD commands display a dialog box to enter its data. To display prompt
entries entered from keyboard (i.e. line by line through the command line), use a "‐"
before the command, such as: ­Style, ­Block or ­Layer.
To join two or more data between cells on a worksheet, use hyphen "&" (in
quotation marks). It is the same as used in VBA to combine string and numerical
data.
Script can be extended vertically or horizontally. In the extended horizontally script,
any data entered to next data must be separated by an empty space, including an
empty space of pressing Enter.
Every time you run a script file, object snap must be inactive or in off mode, so that it
gives the actual results. Click the Osnap button to make the mode on or off.

The other examples of AutoCAD commands and prompt entries:

•

Below are options in Text command. The letter "j" stands for Justify command,
taken from the first letter.

•

Options in Layer command.

•

Prompt entry for Color properties (Layer > Color).

An Introduction to Excel for Civil Engineers

214

Examplee 1 shows how
h
to draaw a line using script written manually. For line depiction
which co
omprises many
m
segmeents (tens or
o hundredss), the man
nual method
d is rarely used.
u
Creatingg AutoCAD sscript on a w
worksheet iss a way to ssimplify and
d shorten the job of creating
AutoCAD
D drawings, by relyingg on the Excel menu such as Co
opy, Paste, and its bu
uilt‐in
function
ns.
Examplee 2
Examplee 1 is now m
made on the worksheet as follows:

Prior to the first co
oordinates entry of (0,0) after th
he Line com
mmand it must
m
be giv
ven 1
blank sp
pace to execcute the com
mmand, the same is done by presssing Enter iin AutoCAD. The
second ccoordinatess of (1,1) an
nd so on, sh
hould be plaaced in diffeerent line th
hat is on celll C4,
C5, and sso on, so as to be able tto write the next script by copyingg the formula on cell C4
4. The
last coorrdinates of ((3,3) on cell C6, need tto be added
d a blank spaace at the eend of the lin
ne to
end the L
Line command:
=A6&","&
&B6&" "
Next is to create a script file wiith the follow
wing steps:
6, then Cop
py by click
king the Copy icon on
n the toolbaar or
1. Seelect a rangge C3 to C6
prressing Ctrl+
+C.

Example 3
In a pile foundation work, the number of blows from driving hammer to penetrate piles is
noted until it reaches a depth of 28 meters. The obtained data is presented in Table 10.1.
Table 10.1: Number of Blows versus Pile Penetration
Pile
Penetration

Number
of

Cumulativ
e

(m)

blows/m'

Blows

1

18

18

2

12

30

3

9

39

4

11

50

5

15

65

6

23

88

7

29

117

8

30

147

9

28

175

10

38

213

11

34

247

12

40

287

13

47

334

14

48

382

15

73

455

16

90

545

17

81

626

18

78

704

19

78

782

20

74

856

21

82

938

22

91

1029

23

101

1130

24

108

1238

An Introduction to Excel for Civil Engineers

217

25

120

1358

26

152

1510

27

163

1673

28

212

1885

The data in Table 10.1 will be plotted in Cartesian coordinate system, where X‐coordinate
is the number of blows/meter and the Y‐coordinate is the penetration of the pile in meter.
Thus, it is required a graph form, where the data have to fit with the form created. The
penetration depth on the Y‐axis then is made to increase downward to state depth below
ground level, while the number of blows on the X‐axis increases to the right. It looks like
below.

The position of the form is placed at the bottom right of the origin P(0,0) in AutoCAD
drawing area. The scale made is:

•
•

X‐coordinate: 1 unit = 10 blows
Y‐coordinate: 1 unit = 1 meter

With such scale, script for the data on the X‐axis should be divided by 10, while the data on
the Y‐axis is divided by ‐1 for it has a negative direction to the origin P(0,0). Script on the
worksheet is created as follows:

An Introduction to Excel for Civil Engineers

218

Furtherm
more, the sccript will bee then saved
d into a filee with the .sscr extension. When the file
runs, thee following ggraph is sho
own in Auto
oCAD:

An Introd
duction to E
Excel for Civiil Engineerss

219

Example 4
In this example a script will be created, where the cumulative blows will also be plotted on
the graph. Thus, both of number of blows/meter and its accumulation are related to the
same Y‐axis (against pile penetration). But by seeing the cumulative blows has a range of
values up to thousands, then a scale of 1 unit = 100 blows is given so that the values can
conveniently fit in the graph form.
The result of cumulative blows is drawn using Pline with line thickness 0.1 unit. To create
an AutoCAD script, a new column is then created named Script 2 (next to Script), contains
formulas as shown below:

An Introduction to Excel for Civil Engineers

220

Next step is to combine both two scripts from blows count and its accumulation into a
single file. At first you have to select a range E5 to E32, then copy and paste it into Notepad
and then followed by range F5 to F32. The contents of the script file will look as below:
line 1.8,‐1
1.2,‐2
0.9,‐3
1.1,‐4
1.5,‐5
2.3,‐6
2.9,‐7
3,‐8
2.8,‐9
An Introduction to Excel for Civil Engineers

Example 5
In this example, we will create a Layer in AutoCAD for each pile data. For doing so, now is
given 2 piles driving data, with location name PT‐1 and PT‐2, where each pile driving data
will be put into Layer by its location name. You, who are accustomed to using AutoCAD, of
course, will understand the importance of working with Layer.
To create a layer click the menus Layer > Make, and then define the layer name. If
combined with the driving data the script is as follows:
”Layer make PT‐1 line 1.8,‐1”
The letter "m" can stand for Make, thus shortened to:
”Layer m PT‐1 line 1.8,‐1”
To customize each layer, it will be given a unique color. For example, line in layer PT‐1 is
blue, while in PT‐2 is magenta. Now, the script becomes:
”layer m PT‐1 c blue line 1.8,‐1”
Script created above is only written in the first line (row). The next line will only be the
coordinates of points entered into a layer. It is shown as below:

An Introduction to Excel for Civil Engineers

224

To select the script, take these steps, firstly you have to copy the value of cells E5 to E32
and then cells D5 to D32, secondly, select cells E33 to E61 and D33 to D61. By doing so, the
script of number of blows and the cumulative blows of each pile are put into the layer that
corresponds to the location number.
The script in Notepad is shown below:
layer m PT‐1 c blue line 1.8,‐1
1.2,‐2
0.9,‐3
1.1,‐4
1.5,‐5
2.3,‐6
2.9,‐7
3,‐8
An Introduction to Excel for Civil Engineers

Layers ccreated in A
AutoCAD can
n be seen in
n a combo‐b
box for layeers, at the to
op‐middle o
of the
AutoCAD
D drawing area.
a
Each layer can be
b displayed individuaally or togeether with other
o
layer. To
o disable or enable a lay
yer, click the mouse on
n the light icon in thee combo‐bo
ox.
Note:
•

Discussion on
D
o script of AutoCAD will
w take veery long tim
me for theree are such many
m
drawing feattures in Au
utoCAD. The detail is beyond thee scope of this book. It is

An Introd
duction to E
Excel for Civiil Engineerss

230

expected that readers can get the ideas from the all examples above. The next step
could be started by making some drawings as Block, which is then inserted using
the Insert command. You can insert block such as markers, a shading object, an
arrow or a form into a drawing area as many as required.

11.2

CREATING SCRIPTS IN VBA

The advantage of VBA is its ability to create a file with ASCII format that contains text. File
is created using the CreateTextFile method with the following syntax:
object.CreateTextFile(filename[, overwrite[, unicode]]
Description:
object

Name of FileSystemObject or Folder object

filename

String, name of a file to create

overwrite

Optional. Boolean value, if TRUE indicates that file can be overwritten, or vice
versa. If omitted, the value is FALSE.

unicode

Optional. Boolean value, indicates whether that file is created as Unicode file
(TRUE) or ASCII (FALSE). If omitted the value is FALSE.

For accessing a file that has been created, use the Open command. The Open syntax itself
consists of several parts with the descriptions that are quite detailed, however it will not be
fully written here. For the purpose of creating a script file, use the following syntax:
Open "c:\testfile.scr" For Output As #1
Print #1, "text"
Write #1, variable
Print #1, numeric & "text " & numeric
Close #1

Descriptions:

An Introduction to Excel for Civil Engineers

231

•

•

•

•

Open command is used for accessing a file named "testfile", to write script into a file.
At the end of code, is to close the open file using Close command. With the above
program the file will then be automatically named "filename" and stored in the
directory C. Extension scr (* .scr) is to make a script file as an AutoCAD file.
Print and Write statement is used to write data to a file. Sign "&" after the Print
statement is to merge text and numerical characters. Statement of Write #1, var x, y
var, var z is where each variable separated by a comma.
If a file written in the directory does not exist, then a file will be created. By this
default, the Open and Close command is more practical to use than the
CreateTextFile method, because it can create a file and accessing any file at the same
time.
If the existing file is in a folder, it must be fully written the following folder and file
name as well (for example, c:\MyFolder\testfile.scr). If you want to create a new
folder, use MkDir statement.

Example 1
Creating a file and accessing to write text.
Open "c:\testfile" For Output As #1 'open file
Print #1, "write this text" 'write text to file
Close #1

With the above code, thus the testfile file contains text "write this test" can be accessed in
directory C. When opened with Notepad program, it looks like this:

Example 2
Now, we will use VBA for creating script. Example 2 of Section 11.1 will be adopted for this
example, and for your convenience is re‐shown below:

An Introduction to Excel for Civil Engineers

232

By usingg VBA, form
mulas in colu
umn C are then no lon
nger needed
d. Howeverr, the workssheet
form is n
now changed by addingg a file namee and a com
mmand butto
on.

by clicking tthe comman
nd button n
named Prod
duce Script. The
To run tthe above prrogram is b
resultingg script file contains the following text

An Introd
duction to E
Excel for Civiil Engineerss

233

To run the script above, firstly, open your AutoCAD, then type scr in command prompt or
via Tools > Run Script. From the Select Script File dialog box, select myScript.scr, then the
line segments with the coordinates as in the script file will be created in AutoCAD.
Example 3
In creating line, it is highly recommended to create a table of joints so creating lines to be
more convenient, especially when it comprises many segments and not all of them are
continuous. See the previous examples in Chapter 1. Next is an example of worksheet data
for creating lines. Now, it has an input to add a new folder to make your files storage tidy.

The use of AutoCAD
D script in ciivil engineeering field iss quite popu
ular becausee both Excel and
AutoCAD
D could bee found eassily (who does
d
not have?)
h
and became a must stan
ndard
softwarees for civil engineers. Example 4
4 to 7 in th
he next pagges are AuttoCAD draw
wings
produced from scrript files co
ontained larrge amountt of data. They
T
are crreated for their
ve interestss.
respectiv
Examplee 4 (next pagge):

Type Member_Data
J1 As Integer
J2 As Integer
Dens As Double
Ax As Double
E As Double
Iz As Double
EI As Double
lx As Double
ly As Double
Lh As Double
Cx As Double
Cy As Double
End Type

Public ChartWindow As Boolean
Public NP As Integer
Public DOF As Integer
Public NR As Integer
Public NJ As Integer
Public NM As Integer

An Introduction to Excel for Civil Engineers

242

Public Member() As Member_Data
Public Joint() As Joint_Data
Public NS As Integer 'no. of support
Public NUNI As Integer
Public UNI() As Double
Public GS() As Double 'global stiffness
Public Fm() As Double
Public Rs() As Integer
Public DFR() As Double
Public Idm() As Integer
Public Idj() As Integer
Public Irj() As Integer
Public Psum() As Double
Public Pmm() As Double
Public Pj() As Double
Public Xs() As Double
Public Xm() As Double
Public uscale As Integer
Public XAx_Wd As Double
Public YAx_Wd As Double

lx As Double
ly As Double
Lh As Double
Cx As Double
Cy As Double
End Type

Public ChartWindow As Boolean
Public NP As Integer
Public DOF As Integer
Public NR As Integer
Public NJ As Integer
Public NM As Integer
Public Member() As Member_Data
Public Joint() As Joint_Data
Public NS As Integer 'no. of support
Public GS() As Double 'global stiffness
Public Rs() As Integer
Public DFR() As Double
Public Idm() As Integer
Public Idj() As Integer
Public Irj() As Integer
Public Psum() As Double
Public Pmm() As Double
Public Pj() As Double
Public Xs() As Double
Public Xm() As Double
Public Fm() As Double
Public XAx_Wd As Double
Public YAx_Wd As Double

Type Joint_Data
x As Double
y As Double
Ks As Double
Sp As Double
End Type

Type Member_Data
J1 As Integer
J2 As Integer
tm As Double
bm As Double
EI As Double
lx As Double
ly As Double
Lh As Double
Cx As Double
Cy As Double
Km1 As Double
Km2 As Double
Pres As Double
End Type

Public ChartWindow As Boolean
Public NP As Integer

An Introduction to Excel for Civil Engineers

304

Public DOF As Integer
Public NR As Integer
Public NJ As Integer
Public NM As Integer
Public Member() As Member_Data
Public Joint() As Joint_Data
Public Em As Double
Public Km() As Double
Public NS As Integer 'no. of support
Public GASAT() As Double 'global stiffness matrix
Public Rs() As Integer
Public DFR() As Double
Public Idm() As Integer
Public Idj() As Integer
Public Irj() As Integer
Public Psum() As Double
Public Pmm() As Double
Public Pj() As Double
Public Xs() As Double
Public Xm() As Double
Public Fm() As Double
Public mmomen1() As Double
Public mmomen2() As Double
Public shear1() As Double
Public shear2() As Double

Type Joint_Data
x As Double
y As Double
Ks As Double
Spring As Double
Sp As Double
End Type

An Introduction to Excel for Civil Engineers

338

Type Member_Data
J1 As Integer
J2 As Integer
Im As Double
bm As Double
EI As Double
lx As Double
ly As Double
Lh As Double
Km1 As Double
Km2 As Double
Pres1 As Double
Pres2 As Double
End Type

Public ChartWindow As Boolean
Public NP As Integer
Public DOF As Integer
Public NR As Integer
Public NJ As Integer
Public NM As Integer
Public Member() As Member_Data
Public Joint() As Joint_Data
Public Em As Double
Public Km() As Double
Public NS As Integer 'no. of support
Public GASAT() As Double 'global stiffness matrix
Public Rs() As Integer
Public DFR() As Double
Public Idm() As Integer
Public Idj() As Integer
Public Irj() As Integer
Public Psum() As Double
Public Pmm() As Double
Public Pj() As Double
Public Xs() As Double

An Introduction to Excel for Civil Engineers

339

Public Xm() As Double
Public Fm() As Double
Public mmomen1() As Double
Public mmomen2() As Double
Public shear1() As Double
Public shear2() As Double