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, Soil Pressure & Deformation
20.2.2 Soil-Structure Interaction Principles for Optimized Design
20.2.2.1 Interaction Curve ks(S+H)4/(4EI)
20.2.2.2 Structural and Operating Curves
20.2.2.3 Structural Solution without SSI
20.2.3 Comparison of Results of Finite Element Analysis to Full-Scale Tests Conducted by HITEC
21. The Arch Form
21.3 Spreadsheets for Analysis of Specific Arch Subjects
21.3.1 Arch Shapes
20.3.1.1 Equations of Geometrical Configurations for:
20.3.1.2 Circular, Elliptical (3-centered arch), Parabolic, Catenary (Inglis)
20.3.1.3 Plot (graphical charting)
Excel Spreadsheet - GeomShapes
21.3.2 Thrust & Moment
Excel Spreadsheet - T-Cir,Ellipse,Cat&Par
Excel Spreadsheet - T&MparLeontovichEqns
21.3.4 Stability and Buckling
21.3.5 Seismic Evaluation under Earthquake Loadings
Part 5 – Engineering Applications (or Appendix?)
22. Dome Structures
Excel Spreadsheet - Dome
23. Critical Path Method
Excel VBA Macro program - CPM
24. Financial Analysis
Excel Spreadsheet – LoanProg
25. Unit Conversions
Excel Spreadsheet – UnitCnvrsn