An Introduction to Excel for Civil Engineers: From engineering theory to Excel practice

387 Pages·2016·28.64 MB·English

by  Gunthar Pangaribuan

An Introduction to

EXCEL
for

Civil Engineers 
From engineering theory
to Excel practice

Gunthar Pangaribuan 
1
- This book is intended to introduce a beginner level of using Microsoft Excel  in civil 
engineering practices 
- A direct translation 

Copyright © Gunthar Pangaribuan 2016. No part of the book may be translated, reproduced or transmitted 
by any form or by any means without permission in writing from the author. Unless for private use, sharing, 
distributing and modifying the associated files for any purpose are prohibited. 
i
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 

ii
CONTENTS 
PREFACE ................................................................................................................................................... ii 
Chapter 1: BASICS OF EXCEL ...............................................................................................................1 
1.1  Worksheet and Workbook ............................................................................................................ 1 
1.2  Data Type .............................................................................................................................................. 2 
1.3  Formula.................................................................................................................................................. 5 
1.4  Built‐In Function ................................................................................................................................ 7 
1.5  Array Formula ..................................................................................................................................... 9 
1.6  Data Formatting .............................................................................................................................. 11 
1.7  Error Message .................................................................................................................................. 11 
1.8  Printing ............................................................................................................................................... 12 
1.9  Making Charts .................................................................................................................................. 13 
1.10  Engineering Drawing .................................................................................................................... 15 
1.11  Visual Basic for Application ....................................................................................................... 24 
1.11.1  Creating Macro ............................................................................................................ 25 
1.11.2  Recording Macro ........................................................................................................ 27 
1.11.3  Procedure ...................................................................................................................... 29 
1.11.4  Running Macro ............................................................................................................ 31 
1.11.5  VBA Dictionary ............................................................................................................ 32 
Chapter 2: EXCEL FUNCTIONS ......................................................................................................... 33 
2.1  Math and Trigonometry Functions ......................................................................................... 33 
2.2  Logical Functions ............................................................................................................................ 36 
2.3  Lookup Functions ........................................................................................................................... 38 
2.4  Text Functions ................................................................................................................................. 40 
2.5  Data Analysis Functions .............................................................................................................. 43 
2.5.1  Linear Regression ...................................................................................................... 43 
2.5.2  Polynomial Regression ............................................................................................ 52 
2.5.3  Interpolation ................................................................................................................ 53 
2.5.4  Statistical Data ............................................................................................................. 59 
2.5.5  Circular Reference ..................................................................................................... 65 
iii
Chapter 3: CREATING MACRO ......................................................................................................... 69 
3.1  Function Procedure ....................................................................................................................... 69 
3.2  Sub Procedure .................................................................................................................................. 74 
3.3  Control Structures .......................................................................................................................... 76 
3.3.1  Looping ........................................................................................................................... 76 
3.3.2  Branching ....................................................................................................................... 79 
3.4  User Defined Function Problems ............................................................................................. 83 
3.5  Structure of Program .................................................................................................................... 97 
3.5.1  Input Output Form ..................................................................................................... 97 
3.5.2  Work With Modules .................................................................................................. 98 
3.5.3  Tips ................................................................................................................................. 100 
3.6  Chart Macro..................................................................................................................................... 102 
3.7  Manipulation on Program Steps ............................................................................................ 108 
Chapter 4: MATRIX PROGRAM ..................................................................................................... 112 
4.1  Matrix Definition ........................................................................................................................... 112 
4.1.1  Types of Matrix.......................................................................................................... 112 
4.1.2  Matrix Operation ...................................................................................................... 115 
4.2  Program for Matrix Operations .............................................................................................. 124 
4.3  Matrix Method for Structural Analysis ................................................................................ 132 
4.3.1   Upper Structure ........................................................................................................ 132 
4.3.2   Sub Structure ............................................................................................................. 134 
Chapter 5: NUMERICAL METHOD ............................................................................................... 135 
5.1  Numerical Integration ................................................................................................................ 135 
5.2  Numerical Differentiation ......................................................................................................... 138 
Chapter 6: PROGRAM FOR 2D FRAME STRUCTURE ANALYSIS ......................................... 144 
6.1  Case Example.................................................................................................................................. 144 
6.2  Sign Convention for Diagram .................................................................................................. 160 
6.3  Application ...................................................................................................................................... 162 
Chapter 7: PROGRAM FOR 2D TRUSS STRUCTURE ANALYSIS .......................................... 163 
7.1  Case Example.................................................................................................................................. 164 
7.2  Application ...................................................................................................................................... 176 
iv
Chapter 8: BEAM ON ELASTIC FOUNDATION .......................................................................... 180 
8.1  Case Example.................................................................................................................................. 182 
8.1  Application ...................................................................................................................................... 187 
Chapter 9: LATERALLY LOADED STRUCTURE ........................................................................ 189 
9.1  Case Example.................................................................................................................................. 189 
9.2  Application ...................................................................................................................................... 196 
Chapter 10: ONE DIMENSIONAL CONSOLIDATION ............................................................... 199 
10.1  Application 1 .................................................................................................................................. 205 
10.2  Application 2 .................................................................................................................................. 207 
Chapter 11: AUTOCAD SCRIPT FILE ........................................................................................... 210 
11.1  Creating Scripts in Worksheet ................................................................................................ 210 
11.2  Creating Scripts in VBA .............................................................................................................. 231 
REFERENCES ...................................................................................................................................... 240 
ATTACHMENT: PROGRAM CODE ................................................................................................. 241 

v
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
C 1  
HAPTER   
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 W W  
ORKSHEET AND  ORKBOOK
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 D T  
ATA  YPE
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. 

An Introduction to Excel for Civil Engineers      2
Quick Access Toolbar Tittle Bar Closee
CCommand Tab Close Window
Commandds related to the tab
Ribboon
Formulla Bar
Name Box
Active Cell
Scroll Bar
Sheet Tab

Figgure 1.1: Exxcel 2007 WWorksheet  

Taable 1.1: Exccel Basic Coommands  
Shhortcut 
Name   Descriiption 
oor Icon 
Offiice Button  Icon to basicc commands below: 

SSave a file wwith the naame of the workbookk 
Savve 
tthat is beingg done 

SStore the filee with anotther name innto a folderr 
aand  directoory,  or  to  aa  disc.  By  default  filee 
eextension oof .xlsx is given (free macros). Iff 
Savve As  Alt+FA 
tthe file conttains macroo (VBA) annd needs to 
aactivate eacch time it is opened, ann extensionn 
oof .xlsm exteension mustt be given. 
Neww  OOpen a new workbook

Opeen    OOpen an exissting workbbook 
Prinnt  PPrint a workksheet throuugh the prinnter device

Closse    CClose the woorkbook (Cllose Windoww icon) 
An Introdduction to EExcel for Civiil Engineerss     3