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 R_{n} = C x r_{n}; where R_{n} is the strength of bolt group, r_{n} 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 819 (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 'e_{x}' 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 R_{n} = C x r_{n} 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 inplane static equilibrium (ΣFx=0, ΣFy=0, ΣM=0) will be satisfied and capacity calculated. The program is based on the loaddeformation relationship of bolt denoted by the equation,
R = R_{ult} (1 – e^{10Δ})^{0.55}
where:
 R = nomimal shear strength of one bolt at a deformation Δ, in kips.
 R_{ult} = 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 76 to 78 of the AISC Steel Construction Manual, 13th Edition.
VBA Code: BOLT COEFFICIENT CALCULATOR
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89

Option Explicit Type BoltInfo Dv As Double Dh As Double End Type '''Effective Bolt Coefficient Function BoltCoefficient(Bolt_Row As Integer , Bolt_Column As Integer , Row_Spacing As Double , Column_Spacing As Double , Eccentricity As Double , Optional Rotation As Double = 0) As Double Dim i, k, n As Integer Dim mP, vP, Ro As Double Dim Mo, Fy As Double Dim xi, yi As Double Dim Ri, x1 As Double Dim y1, Rot As Double Dim Rn, iRn As Double Dim Rv, Rh As Integer Dim Sh, Sv As Double Dim Ec As Double Dim Delta, Rmax As Double Dim BoltLoc() As BoltInfo Dim Stp As Boolean Dim J As Double Dim FACTOR As Double Rv = Bolt_Row Rh = Bolt_Column Sv = Row_Spacing Sh = Column_Spacing ReDim BoltLoc(Rv * Rh  1) On Error Resume Next Rot = Rotation * 3.14159265358979 / 180 Ec = Eccentricity * Cos(Rot) If Ec = 0 Then GoTo ForcedExit n = 0 For i = 0 To Rv  1 For k = 0 To Rh  1 y1 = (i * Sv)  (Rv  1) * Sv / 2 x1 = (k * Sh)  (Rh  1) * Sh / 2 With BoltLoc(n) .Dv = x1 * Sin(Rot) + y1 * Cos(Rot) '''Rotate Vertical Coordinate .Dh = x1 * Cos(Rot)  y1 * Sin(Rot) '''Rotate Horizontal Coordinate End With n = n + 1 Next Next Rn = 74 * (1  Exp(10 * 0.34)) ^ 0.55 Ro = 0: Stp = False Do While Stp = False Rmax = 0 For i = 0 To Rv * Rh  1 xi = BoltLoc(i).Dh + Ro yi = BoltLoc(i).Dv Rmax = Application.WorksheetFunction.Max(Rmax, Sqr(xi ^ 2 + yi ^ 2)) Next Mo = 0: Fy = 0 mP = 0: vP = 0 J = 0 For i = 0 To Rv * Rh  1 xi = BoltLoc(i).Dh + Ro yi = BoltLoc(i).Dv Ri = Sqr(xi ^ 2 + yi ^ 2) Delta = 0.34 * Ri / Rmax iRn = 74 * (1  Exp(10 * Delta)) ^ 0.55 Mo = Mo + (iRn / Rn) * Ri '''Moment Fy = Fy + (iRn / Rn) * Abs(xi / Ri) * Sgn(xi) '''Vertical J = J + Ri ^ 2 Next mP = Mo / (Abs(Ec) + Ro) vP = Fy Stp = Abs(mP  vP) <= 0.0001 FACTOR = J / (Rv * Rh * Mo) Ro = Ro + (mP  vP) * FACTOR DoEvents Loop BoltCoefficient = (mP + vP) / 2 Exit Function ForcedExit: BoltCoefficient = Rv * Rh End Function 
Step 1:
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.
Step 2:
Step 3:
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, popup menus and dialog boxes may look different from the samples i've shown.