Wiley
Wiley.com
Print this page Share

Solutions for Soil and Structural Systems using Excel and VBA Programs

ISBN: 978-1-119-95155-1
312 pages
October 2012
Solutions for Soil and Structural Systems using Excel and VBA Programs (1119951550) cover image

A practical guide to analyzing soil and structural systems using Excel spreadsheets and VBA macro programs (in open-source code) that are provided on the accompanying CD.

This book gives readers the tools to understand the methods such as finite element analysis used to analyze common problems in structural engineering, foundation engineering and soil-structure interaction.  The book has value just based on its instructions in Excel spreadsheets and the Visual Basic for Applications (VBA) macro programming language alone. By providing an expert system and guidance to the reader in its use through examples, the author shows the methods and simple modelling techniques that demystify soil-structure applications by presenting the essentials in a clear and concise way.

The book also addresses some of the disappointments in geo-engineering by providing tools to calculate deformations, implement soil-structure interaction procedures,   provide simple computer solutions, while incorporating proper soil and rock properties in the analyses. 

  • Can be used by students or practicing professional engineers as a hands-on self-study guide as prewritten complete Excel spreadsheets and VBA programs are applied to many different Civil Engineering example problems
  • VBA code techniques and its use and programming are explained but a working knowledge is not required to use the spreadsheet and programs provided
  • Computations are performed using VBA macro programs getting input data from worksheet cells (whereby the spreadsheet functions as a pre-processor) or from input data files  

Robert L. Sogge has a background which includes training, teaching, research and practical consulting in the area of soil-structure interaction. He achieved his PhD in Civil Engineering at the University of Arizona, USA, and practices in that state and California.  He has developed many of these computer programs in the pursuit of his work as a consultant.

See More

About the Author xxi

Preface xxiii

Acknowledgments xxv

PART ONE COMPUTER SOFTWARE 1

1 Microsoft Excel Spreadsheet 3

1.1 History of Spreadsheet Development 3

1.2 Excel 2010 4

1.3 Transmitting Cell Values Not Formulas 5

1.4 Accuracy 5

1.5 Saving 6

1.6 Implementation of Excel Features 6

2 Microsoft VBA Programming Language 13

2.1 History of the BASIC Computer Language 13

2.2 Justification for Using Excel with VBA Macros 15

2.3 Difference between aWorkbook and a VBA Macro 16

2.4 VBA Macro Nomenclature 16

2.5 Generating a Procedure 17

2.6 Security Level Required to Open VBA Macros 19

2.7 VBA Code Statements that Differ from Previous BASIC Versions 19

2.8 Implementation of VBA Macro Programming 20

2.9 Inputting Data to a VBA Procedure 26

2.10 Output Data from a VBA Procedure 30

2.11 Running a Macro 32

2.12 Code Debugging 33

2.13 Charting in a Worksheet 34

2.14 Line Plots in a Worksheet 34

2.15 Macro Sub Program Showing Output toWorksheet 35

2.16 Computer Hardware/Software Requirements 36

PART TWO STRUCTURES 41

3 Finite Element Method – The Theory 43

3.1 Theory 43

3.2 Developing the Element Stiffness Matrix 44

3.3 Creating the Global Stiffness Matrix by Assembling Element Stiffnesses 47

3.4 Solving Simultaneous Equations for Displacements 47

3.5 Element Displacements and Forces 48

3.6 Flowchart of Steps 49

4 Finite Element Analysis VBA Program PFrame 51

4.1 Program PFrame – Finite Element Analysis (FEA) of Beam–Bar Structural Systems 51

4.2 Creating an Input Data Worksheet 52

4.3 Input Data 52

4.4 Joint Numbering and Dimensions 56

4.5 Load Application 58

4.6 Imposed Joint Displacements 59

4.7 Unstable or Improperly Supported Configurations 60

4.8 Running Program PFrame 60

4.9 Output Data 62

4.10 Alternate Solution Approach to Macro Program PFrame 63

4.11 Significant Aspects of Excel Worksheet & VBA Macro Program Construction 63

5 Beams 65

5.1 Beam Member Types 65

5.2 Bar Members as Pinned-End Beams 65

5.3 Moment of Inertia Conversion for Different Member Axis Orientation 67

5.4 Load Application 69

6 Frames 71

6.1 Analysis of Frames 71

6.2 Rigid Joints 71

6.3 Joint Numbering 71

6.4 Pinned-End Beam 73

6.5 Supports 74

6.6 Varying EI of Members Comprising a Frame 75

6.7 Stability – The P–Delta Effect 76

6.8 Load Case Combinations of Load Groups 76

6.9 Interior Member Forces 77

6.10 Examples 77

7 Trusses 81

7.1 Theory for Bar Members 81

7.2 Analysis of Bar Assemblage 81

7.3 Load Application 82

7.4 Initial Member Length Changes 82

7.5 Support Displacements 82

8 Reinforced Concrete 83

8.1 Concrete and Reinforcing Steel Properties 83

8.2 Design Capacity and Reinforcing Requirements 84

8.3 Strength Properties for a Soil–Structure Interaction Analyses 89

8.4 Cracked-Section Concrete Properties 90

8.5 Excel Workbooks 91

8.6 Notation 92

PART THREE SOILS 95

9 Soil Classification 97

9.1 Field Geotechnical Processes 97

9.2 Soil Description 100

9.3 Field and Laboratory Tests for Soil Identification 103

9.4 Soil Classification Systems 106

9.5 Excel Workbooks and VBA Programs 108

9.6 Soil Mechanics Symbol Nomenclature 109

10 Soil Strength Properties 115

10.1 Discrete and Elastic Finite Element Models 115

10.2 General Elasticity Equations Relating Stress and Strain 115

10.3 Modulus of Elasticity and Poisson’s Ratio 118

10.4 Coefficient of Subgrade Reaction 135

10.5 Mathematical Descriptions of Curves Using Program Curve Fit 138

11 Stresses in an Elastic Half-Space 141

11.1 Closed-Form Elasticity Solutions 141

11.2 Lateral Stresses against a Wall Restrained from Movement due to Point, Line, and Strip Loading 141

11.3 Boussinesq Equation 141

11.4 Westergaard Equation 142

11.5 Mindlin Equation 142

11.6 Chart Solutions 142

11.7 Excel Workbook – Lat&VertStress 143

11.8 VBA Program HSpace 143

11.9 Significant Programming Aspects 144

11.10 VBA Program HSpace – Program Documentation 144

12 Lateral Soil Pressures and Retaining Walls 149

12.1 Lateral Earth Pressure – Sloped Backfill Acting on Inclined Retaining Wall 149

12.2 Slope Stability 150

12.3 Stability of a Vertical Cut 150

12.4 Retaining Wall Movements 151

12.5 Retaining Walls – Factor of Safety 151

13 Shallow and Deep Foundation Vertical Bearing Capacity 153

13.1 Shallow Foundations 153

13.2 Vertical Bearing Stress Capacity 153

13.3 Soil Pressure Distribution 154

13.4 Settlement-Based Bearing Capacity 155

13.5 Excel Workbooks 156

13.6 Deep Foundations 156

13.7 Capacities Based on Displacement Limits 157

13.8 Capacities Based on Stress Limits 158

13.9 Limitations on Capacities 160

13.10 Load Testing 161

13.11 Pier Settlement 161

13.12 Excel Workbook 161

13.13 Combined Foundations – Shallow and Deep 161

14 Slope Stability 165

14.1 Workbook Program Slope – Slope Stability by Bishop’s Modified Method of Slices 165

14.2 Workbook Program STABR – Slope Stability by Bishop’s Modified Method of Slices 166

14.3 Workbook Program Slope8R – Slope Stability by Spencer’s Procedure for Non-circular Slip Surfaces 167

15 Seepage Flow through Porous Media 169

15.1 Program Flownet for Analysis of Seepage Flow through Porous Media 169

15.2 Program Input – from Data file 170

15.3 Program Output – to Data File 171

15.4 Input Data Description 172

15.5 Output Data Description 172

15.6 Example 172

15.7 Significant Aspects of Excel Workbook and VBA Macro Program Construction 174

PART FOUR SOIL–STRUCTURE INTERACTION 177

16 Beam-on-Elastic Foundation 179

16.1 Theory–Classical Differential Equation Solution 179

16.2 Beam–Bar Finite Element Model 180

16.3 Soil Strength – Coefficient of Vertical Subgrade Reaction 182

16.4 Structural Stiffness 183

16.5 Soil–Structure Interaction 183

16.6 Unbalanced Fixed-End Moment from Triangular Load Distribution 184

16.7 Pressure Distribution 184

16.8 Solution Exclusively in Excel Worksheet without VBA 185

16.9 Examples 187

17 Footings andMat Foundations 191

17.1 Mat Foundations 191

17.2 Slab Section Stiffness and Moment Capacity 192

17.3 Soil–Structure Interaction 192

17.4 Practical Considerations Regarding Slab Reinforcement 193

17.5 Case Study – House Slab Foundations in Tucson, Arizona 197

17.6 Example 17.1 House Slab 197

18 Laterally Loaded Piles 201

18.1 Theory – Classical Differential Equation Solution 201

18.2 Conventional Analysis 202

18.3 Beam–Bar Finite Element Solution 202

18.4 Structural Stiffness 207

18.5 Soil Strength 209

18.6 Soil–Structure Interaction 213

18.7 Soil Pressures on Each Side of Pier 215

18.8 Limitations of a Beam–Bar Analysis 219

18.9 Design Procedure 219

18.10 Solution Exclusively in Excel Worksheet without VBA 221

18.11 Point of Fixity 222

18.12 Pile Groups 222

18.13 Conclusions 222

18.14 Significant Aspects of Excel Worksheet and VBA Macro 223

18.15 Examples 223

19 Cantilevered and Anchored Sheet Piles 229

19.1 Cantilevered Sheet Piles 229

19.2 Beam–Bar Finite Element Model for Cantilevered Piles 229

19.3 Anchored Sheet Piles 229

19.4 Beam–Bar Finite Element Model for Anchored Sheet Piles 230

19.5 Soil Strength Representation 230

19.6 Examples 231

20 Buried Arch Culverts (Tunnels) 233

20.1 Theory: Classical Elasticity Formulation – Burns and Richard Solution 233

20.2 Soil–Structure Interaction 234

20.3 Beam–Bar Finite Element Frame Model 235

20.4 Vertical Loads 237

20.5 Distributing and Attenuating Vertical Live Loads 238

20.6 Horizontal Ko Pressure Load 240

20.7 Load Application 240

20.8 General Elasticity FEA Programs 241

20.9 SSI 242

20.10 Cracked-Section Considerations 243

20.11 Examples 244

21 The Arch Form 247

21.1 History of Arches and Vaults 247

21.2 Arch-Shaped Configurations 247

21.3 Force Determination for Various Shaped Arches 249

21.4 Arch Engineering Considerations 250

21.5 Structural and Hydraulic Efficiency 252

21.6 Soil–Structure Interaction 253

21.7 Flexible versus Rigid Structures 254

21.8 Failure Patterns and Deflections 255

21.9 Load Tests 256

21.10 Design Comments 256

21.11 Buckling of Arches 260

21.12 Seismic Design Considerations 261

PART FIVE ENGINEERING APPLICATIONS 263

22 Domes 265

22.1 Geometry 265

22.2 Membrane Stresses 265

22.3 Stress Computations Using Worksheet Dome 266

23 Critical Path Method 269

23.1 Project Scheduling 269

23.2 VBA Versions 270

24 Financial Analysis 271

24.1 Equations Governing Financial Operations 271

24.2 Excel Worksheets for Financial Calculator and Formulas 272

24.3 Significant Aspects of Excel Worksheet and Macro Functions 272

25 Conversion of Units of Measurement 275

25.1 Unit Systems 275

25.2 Defined Units 276

25.3 Labeling Conventions 276

25.4 Workbook UnitCnvrsn 277

25.5 Excel Conversions 278

25.6 Example 278

Related Workbook on DVD 278

Index 279

See More
Robert L. Sogge has a background which includes training, teaching, research and practical consulting in the area of soil-structure interaction. He achieved his PhD in Civil Engineering at the University of Arizona, USA, and practices in that state and California. He has developed many of these computer programs in the pursuit of his work as a consultant.
See More
Buy Both and Save 25%!
+

Solutions for Soil and Structural Systems using Excel and VBA Programs (US $125.00)

-and- Soil Mechanics Lab Manual, 2nd Edition (US $82.95)

Total List Price: US $207.95
Discounted Price: US $155.96 (Save: US $51.99)

Buy Both
Cannot be combined with any other offers. Learn more.

Related Titles

Back to Top