Once in a while I will be posting on this blog topics about engineering and structural design that are relevant to my job as structural designer. In this post I am going to share techniques in bolted connection calculation using MS Excel and VBA Codes (Visual Basic for Application).
- The INSTANTANEOUS CENTER OF ROTATION Method; and
- The ELASTIC Method.
These methods are discussed in AISC Steel Construction Manual and would be redundant if discussion is repeated here.
To preclude these difficulties, the AISC Steel Construction Manual devised a table to abbreviate the calculation of 'bolt group' strength. By providing the table, the formula for the strength of Bolt Group is simplified or reduced to Rn = C x rn; where Rn is the strength of bolt group, rn is the available strength of single bolt, and C is the coefficient provided for by the Table that corresponds to the effective number of bolts. With load eccentricity greater than zero, the coefficient C is always less than the total number of applied bolts. The reduction in the efficiency of bolt group is attributed to the increased load from the torque created by load eccentricity that tends to rotate the bolt group about the instantaneous center. Though the table makes the analysis and design of bolts much easier, it has apparent limitations which also make the design more difficult.
To use the table, let us consider calculating the capacity of the bolt group in Fig. A.
Assume a load P located 16" from the Center of Gravity (CG) of the bolt group. Also take note, by static equilibrium, load P corresponds to the capacity of bolts (or strength developed by bolt group) for the given eccentricity. By inspection, Table 8-19 (Angle = 0°) matches the bolt group configuration of Fig. A. The value for the coefficient C can then be derived readily from the table by locating on the horizontal row of the table the 'Number of bolts in one vertical Row, n' and then moving down along column 'ex' to locate the lookup value for eccentricity. With 6 vertical bolt rows and eccentricity of 16", the value of C is extrapolated from the table to be 3.24. Having determined the value of coefficient C, bolt capacity can then be calculated from the formula Rn = C x rn as discussed earlier.
The example above illustrates the ease in calculating capacity of bolt group with eccentric loading using tables. The drawback in this method, however, occurs when you have a bolt configuration whose eccentricity does not numerically concur with the lookup values on the table or when load angular position is not defined by any of the available bolt coefficient tables. Here are two scenarios:
- When you have load eccentricity of 16.5” (using the same Fig. A), the value of C can not be readily extracted from the table. It is found somewhere between the values 3.24 and 2.9 that correspond to eccentricity lookup values 16” and 18”, respectively. An interpolation (which happen to be straight line in nature), therefore, is necessary.
- When you have load with angular position other than 0°, 15°, 30°, 45°, 60° or 75°, there is no way one can solve the value of C using the tables. This is the problem shared by most engineers in the entire engineering community when designing bolted connections.
Fortunately, I have solved this problem long time ago by writing a program in Visual Basic that iterates and determines the correct location of the instantaneous center of rotation of a bolt group. With the IC located, the three equations of in-plane static equilibrium (ΣFx=0, ΣFy=0, ΣM=0) will be satisfied and capacity calculated. The program is based on the load-deformation relationship of bolt denoted by the equation,
R = Rult (1 – e-10Δ)0.55
- R = nomimal shear strength of one bolt at a deformation Δ, in kips.
- Rult = ultimate shear strength of one bolt, kips
- Δ = total deformation, including shear, bearing and bending deformation in the bolt and bearing deformation of the connection elements, in.
- e = 2.718…, base of the natural logarithm.
Further discussion regarding this equation can be found on pages 7-6 to 7-8 of the AISC Steel Construction Manual, 13th Edition.
VBA Code: BOLT COEFFICIENT CALCULATOR
Open your excel spreadsheet where you intend to embed the VBA code. Here, for discussion purposes, let us use the data on Fig. A for a sample spreadsheet.
From the Tools menu under Macro as shown, click on Visual Basic Editor to activate the VBA Editor.
The calculated value of C is the result from the following inputs:
- Number of Bolt Rows = 6
- Number of Bolt Columns = 2
- Spacing of Rows = 3
- Spacing of Columns = 3
- Eccentricity = 16
- Load Rotation = 25°
I am using MS Excel 2003. If you are using MS Excel 2007, the pictures, views, pop-up menus and dialog boxes may look different from the samples i've shown.