Solutions for Soil and Structural Systems Using Excel and VBA Programs

Part 1 - Excel Spreadsheets and the Visual Basic (VBA) Macro Programming Language 1. Microsoft Excel Spreadsheet 1.1 History of Spreadsheet Development 1.2 File Conversion and Compatibility 1.3 Transmitting Cell Values not Formulae 1.3.1 Security Level Required to Open VBA Macros in Excel 1.3.2 Accuracy 1.4 Saving 1.5 Implementation of Excel Features 1.5.1 Fonts 1.5.2 Math Functions 1.5.3 Drawing 1.5.4=IF Statements 1.5.5 Charting 1.5.6 Shortcuts 2. Microsoft Excel Visual Basic for Applications (VBA) Macro Programming Language 2.1 History of Basic Computer Language 2.1.1 Stage I - Basic with DOS 2.1.2 Stage II - Visual Basic with Windows 2.1.3 Stage III - VBA & Excel with Windows 2.2 Justification for using Excel with VBA Macros 2.3 Difference between an Excel Spreadsheet and a VBA Macro 2.4 VBA Macro Nomenclature 2.5 Implementation of VBA Macro Programming in Excel 2.5.1 VBA Statements Differing from Previous BASIC Versions 2.5.2 Running an Excel VBA Macro Program 2.5.2.1 Form Command Button 2.5.3 Type & Size Declaration of Variables for Subs and Functions 2.5.3.1 Deftype and Variable Accuracy 2.5.3.2 Dimensioning Variables 2.5.3.3 Option Explicit Statement 2.5.3.4 Redim Statement 2.5.4 Memory Requirements 2.5.5 Transferring Parameters to Subs and Functions 2.5.6 Inputting Data to a VBA Procedure 2.5.6.1 From a Data File 2.5.6.1.1 Creating a Sequential Input Data File 2.5.6.1.2 Statements for Inputting Data from Sequential File 2.5.6.2 From Spreadsheet Cells 2.5.6.2.1 Using Spreadsheet to Preprocess Input Data 2.5.7 Outputting Data from a VBA Procedure 2.5.7.1 To a Data File 2.5.7.2 To Spreadsheet Cells 2.5.7.3 Plotting Lines to a Spreadsheet Part 2 - Structures 3. Structural Analysis Theory using 2-d Beam-Bar Finite Elements 3.1 Matrix Methods 3.2 Element Stiffness Matrix 3.2.1 Bar Elements - Linear (1-d) 3.2.2 Beam Elements - Quadratic (2-d) 3.2.3 Betti's Reciprocal Theorem and Maxwell's Law 3.3 Global Stiffness Matrix - Assembling Element Stiffnesses 3.3.1 Square, Diagonal, Symmetric, Banded 3.4 Loads - Applied to Joint 3.5 Boundary Conditions 3.6 Simultaneous Equation Solution 3.6.1Matrix Inversion 3.6.2 Gauss Elimination with Back Substitution 3.7 Example Load Distribution to Rebar and Concrete in a Column 4. Finite Element Analysis Program - PFrame 4.1 Sign Convention 4.1.1 Global & Local Coordinate System 4.1.2 Applied Member Loads 4.1.3 Member Forces and Displacements 4.2 Input 4.2.1 Load Input by: 4.2.2 Joint Loads 4.2.3 Member Loads - Point, Distributed (Body Force DL) 4.2.3.1 Fixed End Forces and Moments for Fixed - Fixed End Beam 4.2.3.2 Fixed End Forces and Moments for Fixed - Pinned End Beam 4.2.3.3 Fixed End Forces and Moments for Pinned - Pinned-End Beam 4.2.3.4 Fixed-End Forces and Moments 4.2.4 Supports 4.2.4.1 Imposed Joint Displacements 4.2.4.2 Elastic 4.3 Output 4.3.1 Joint Displacements 4.3.2 Member Forces (M, V, P) and Displacements 4.3.3 Weight of Structure 5. Beams 5.1 Member Axis - Moment of Inertia Conversion 5.2 Pinned-End Beams 6. Frames 6.1 Rigid Joints 6.2 Pinned-End Internal Beam 6.3 Varying EI within Frame 6.4 Supports 6.4.1 Inclined or Skewed 6.4.2 Elastic - example with arch frame and elastic side support 6.5 Stability, P- Effect 7. Trusses 7.1 Bar Members 7.2 Members having: 7.2.1 Initial Elongation 7.2.2 Pre-stressed Condition 7.2.3 Fabrication Errors 7.3 Support Displacements 8. Reinforced Concrete 8.1 Concrete and Reinforcing Steel Properties 8.1.1 Strength Representation - Elastic Modulus (Linear) 8.1.2 Spacing and As/ft, p and lb/cy Quantities 8.1.3 Balanced Design 8.1.4 Lap Splice Length 8.2 Capacity by Strength Design 8.2.1 Beams Excel Spreadsheet - Beam-LFD 8.2.2 Beam-Columns - Interaction Diagram 8.2.2.1 Rectangular Sections Excel Spreadsheet - with VBA Macro Function- Beam-Col ID 8.2.2.2 Rectangular & Circular Sections Excel Spreadsheet - with VBA Macro Subs - PMEIX 8.3 Cracked Section Properties 8.3.1 Moment of Inertia - Cracked-Sections 8.3.1.1 Importance in SSI Analysis 8.3.1.2 Beams - Rectangular Sections PCA Approach - Rectangular sections ACI Approach - PMEIX Approach - Rectangular or circular sections Excel Spreadsheet - Beam-LFD Excel Spreadsheet - with VBA Macro Subs - PMEIX Part 3 - Soils 9. Soil Classification Excel Spreadsheet - no Macro - ClassR1 Nested If Statements Excel Spreadsheet - with VBA Macro in Module- Class-VBA-CodeR1 If-GoTo branching Excel Spreadsheet - with VBA macro in Spreadsheet - Class-VBAr2 10. Soil Strength Properties 10.1 Continuum - Elemental Level 10.1.1 Soil Strength in Active & Passive Failure States 10.1.1.1 Mohr-Coulomb ( -c) 10.1.1.2 Rankine Excel Spreadsheet with Function Statement Macro - Ka-GenR6 10.1.2 Stress-Strain Relations 10.1.2.1 Modulus of Elasticity 10.1.2.1.1 Nonlinear Secant, Tangent Moduli Approximation 10.1.2.1.2 Richard and Duncan-Chang Representation 10.1.2.1.3 Plane Strain, Plane Stress 10.1.2.1.4 Confining-Pressure Dependency 10.1.2.1.5 Dynamic Relations 10.1.2.2 Poisson's Ratio 10.2 Discrete - Large-Scale Level 10.2.1 Stress-Deformation Relations 10.2.1.1 Coefficient of Vertical Subgrade Reaction 10.2.1.2 Coefficient of Horizontal Subgrade Reaction 10.2.1.2.1 Modulus of Subgrade Reaction 10.2.1.2.2 Strength Variation with Depth (z/L)n 10.2.1.2.3 Derived Lateral Soil Pressure 10.2.1.2.4 In-Situ Stress - ko z Table of Material Properties - MatlPropTbl ?????????????? 10.3 Curve Fitting for Strength Parameters 11. Vertical and Lateral Stresses in Elastic Half-Space 11.1 Point Loading - Classical Elasticity Solutions - Bousinesq, Westergaard & Mindlin Excel Spreadsheet - Lat&VertStress 11.2 Solution of Stress and Displacement Equations Excel Spreadsheet with VBA Macro - Hspace 12. Foundations 12.1 Shallow 12.1.1 Stresses Beneath Rigid and Flexible Strip Footing 12.1.1.1 Uniform Distributed and Point Loadings 12.1.1.1.1 Footing Stiffness 12.1.1.1.2 Footing Roughness - Full- and No-Slip Condition 12.1.1.2 Eccentric Loadings 12.1.2 Vertical Bearing Capacity Excel Spreadsheet - Ftg-BrgCap2 - Ftg-EccentricLd 12.2 Deep 12.2.1 Pier Vertical Load Capacity 12.2.1.1 Factor of Safety 12.2.1.2 Settlement 12.2.1.3 Uplift 12.2.1.4 Bearing Capacity Factors Excel Spreadsheet - CuvFit(BC)Relations Excel Spreadsheet - Pier-BrgCap4 12.3 Combined Shallow & Deep Excel Spreadsheet - Mat-Pier Foundation 13. Lateral Soil Pressures against Rigid Walls 13.1 Vertical Wall Subject to Point, Line and Strip Loading Excel Spreadsheet - Lat&VertStress 13.2 Retaining Walls Active or Passive Failure Pressure Distribution 13.2.1 Rotation about base - Plane failure surface 13.2.1.1Hydrostatic Pressure Conditions - Saturated Soil - Weep Holes Excel Spreadsheet - Ka-GenR6 Excel Spreadsheet - RetWall-USD 13.3 Extension to Unsupported & Infinite Slopes 14. Slope Stability 14.1 Circular slip surface - Bishop's Method of Slices Excel VBA Macro program - SLOPE Excel VBA Macro program - STABR 14.2 Non-circular slip surface - Spencer's Procedure Excel VBA Macro program - SLOPE8R 15. Seepage Flow thru Porous Media 15.1 Theory - Solution using 2-d Triangle Finite Elements Excel VBA Macro program - FlowNet 15.1.1Anisotropic Media 15.1.2 Non-homogeneous Media 15.1.3 Minimizing Bandwidth 15.1.4 Plot of Flow & Head Potentials 15.1.5 Applications 15.1.5.1 Sheet piles 15.1.5.2 Earth Dams 15.1.5.3 Undefined Phreatic Surface Part 4 - Soil-Structure Interaction 16. Beam-on-Elastic Foundation 16.1 Classical Elasticity Formulation - Hetenyi 16.1.1 Characteristic of Differential Equation 16.1.1.1 Soil-Structure Interaction Principles 16.1.1.2 Soil & Structure Stiffness Representation - Importance 16.2 Finite Element Method - Beam-Bar Model Formulation 16.2.1 Soil Continuum Model using Discrete Bar Elements 16.2.2 Bandwidth Minimization by Node Numbering 17. Strip Footing & Mat Foundations 17.1 Beam-Bar Finite Element Model 17.1.1 Self Contained Excel Spreadsheet ???????needed for this application of different loads? Excel Spreadsheet - BEF-Excel 17.1.1.1 Matrix Operations - Multiplication and Inversion 17.1.1.2 Accuracy of and Size Limits on Matrices 17.1.1.3 Single & Double Precision 17.1.2 Excel VBA Macro Program Excel Spreadsheet - with VBA Macro Pframe 17.1.2.1 Load input using Fixed End Moments 17.1.3 Results of Analyses - Plot of Moments, Pressures & Deformations 17.1.4 Soil-Structure Interaction Curve 17.1.5 Analysis Comparison to Measured Mat Deformation 17.1.6 Application to RR Track and Pavement Interaction with Subgrade 18. Laterally Loaded Piers ?confirm with 18 18.1 Beam-Bar Finite Element Model 18.2 Self Contained Excel Spreadsheet Excel Spreadsheet - LLP-Excel-Gen 18.2.1 Pile Top Support Conditions of Fixed, Pinned & Free 18.3 Excel VBA Macro Program - LLP(5) Excel Spreadsheet - with VBA Macro Pframe 18.3.1 Development of Input Data in Excel Spreadsheet 18.4 Results of Analyses - Plot of Moments, Pressures & Deformations 18.5 Soil-Structure Interaction Curve 18.6 Analysis of Bridge Bent Systems 18.6.1 Biaxial Bending 18.7 Structural Design of Drilled Shafts 18.7.1 P-M Interaction Diagram Excel VBA Macro program - PMEIX 18.8 Full-Scale ADOT Field Load Test Results 18.8.1 Back-calculated Strength Properties 19. Anchored Sheet Piles 19.1 Classical Solutions based on Test Results - Rowe & Tschebotarioff Excel Spreadsheet - Anchored Sheet Pile 19.2 Sheet-Pile Properties Excel Spreadsheet - SP Prop 19.3 Beam-Bar Finite Element Model Excel VBA Macro program - ASP(2) 19.3.1 Create FEF State for Unbalanced Initial In-situ Lateral Stresses 19.3.2 Incremental Construction - Excavation and Backfill 19.3.3 Limitations - Construction Sequence & Interface 19.4 Soil-Structure Interaction Principles 19.4.1 Interaction Curve 19.4.2 Arching of Soil 19.5 Structural and Operating Curves 19.6 Results of Analyses - Plot of Moments, Pressures & Deformations 19.6.1 Parameter Study Excel Spreadsheet - ParamPlot 19.7 Comparison of Results from Classic Solution to Finite Element Solution 20. Buried Arch Culverts (Tunnels) 20.1 Classical Elasticity Formulation for Soil-Structure system - Burns and Richard 20.1.1 Soil-Structure Interaction Principles Excel Spreadsheet - Burns&RichardEqns 20.2 Beam-Bar Finite Element Model Formulation Excel VBA Macro program - ArchSlab(4) 20.2.1 Plot of Moment, Thrust, Shear, ...