Monday, March 14, 2011

Analysis of Bolts: Modified Visual Basic Code for Calculating Bolt Coefficient Using a Method Developed by Donald Brandt

Another blog post for Structural Engineers and Designers…

When I wrote and posted the VBA (Visual Basic for Application) Code for the analysis of eccentrically loaded bolt group in my blogpost, “Eccentrically Loaded Bolt Group: Analysis and Design using 'INSTANTANEOUS CENTER of ROTATION METHOD' with VBA for Excel Implementation”, I was aware of the difference between the values returned by the program and that of the table of C coefficients provided by AISC Steel Construction Manual. I never believed, however, that the values returned by the program are wrong. How could it be when the VBA code is based on the work by Crawford & Kulak as presented in AISC Steel Construction Manual 13th Edition, pages 7-6 to 7-8? Despite the differences, though, I still decided to post the VBA Code.

Not long afterwards, engineers who happened to follow my blog shared and expressed the same observations and 'demanded' an explanation. Here are some of their comments:

  1. Blog comment from Josh Gionfriddo, EIT, Structural Engineer, Structures Workshop, Inc. on November 19, 2010:

    Thanks for this great code. One thing though... The values in the AISC manual seem to hover around 98% of the values I get from your code. Any Idea why the differences?

  2. Comment from Gregory Vidgop via AISC SteelTOOLS on October 11, 2010:

    Hello Redem,

    Thank you so much for your effort in developing the very nice and useful program as "SteelPro". Just would like to ask you a simple question: why is the C coefficient for the bolt group analysis slightly different from the book value and C' coefficient is exactly right?

    Please let me know when your program will be finally issued or any revisions are planned in the future.

    Thank you again,

    Gregory

  3. Blog comment from Steven Vicha, P.E., Jacobs Engineering Group, Inc. on February 9, 2011:

    Great code, but I have a couple of questions. In the code line "Rn = 74 * (1 - Exp(-10 * 0.34)) ^ 0.55" what is the 74? Also the answers are a little off from the tables, any idea why?

    I need to study it some more.

The three comments above have something in common. All are asking why the values returned by the VBA Code are a little off from that provided by the table in AISC Manual. I was very hesitant to respond to the first two comments above until I received another one from Steven Vicha. Fortunately, he got a reply:

Hi Steven... You've got the same predicament with josh. Actually, I planned not to respond to the question while still looking for the answer -- until i got another question from you.

I did a review on the equation provided for by AISC Steel Construction Manual 13th Edition on pages 7-6 to 7-8 and ran more analyses -- but still the results are a little bit off from the table. Despite the difference, however, the results returned by the code are still correct: the code SATISFIES the in-plane static equilibrium up to 4 decimal places of accuracy. We may throw the same question to AISC why the differences...

Please give me more time to look into this matter.

As to your question, 'what is 74?' -- the number is the ultimate shear strength of 3/4-in. diameter ASTM A325 bolt, 74 kips, which corresponds to its maximum deformation of 0.34 inches. You may refer to pages 7-6 to 7-8 of the AISC Steel Construction Manual, 13th Edition for a thorough discussion.

Shortly after replying to Steven’s question, I found myself indebted to him for his effort in finding the real answer to his question and of the others. He conducted his own research on the topic and pin-pointed out, bull’s-eye, the precise reason. In his February 14, 2011 follow-up comment to the same blogpost, Steven added:

Thanks for the response. I did find the 74, finally. I think that I may have found the reason for the difference in the tables and the results of your VBA code. There is a paper posted on AISC.org that may explain the difference. I have the paper on my other PC at home. I will try and send the excerpt.

Steven is referring to the paper by Larry S. Muir, P.E. and William A. Thornton, P.E., PhD, both of Cives Steel Company, “Exploring the True Geometry of the Inelastic Instantaneous Center Method for Eccentrically Loaded Bolt Groups”. The paper presented the true location of the instantaneous center (I.C.) of a bolt group. In the paper, the authors argued that the I.C. is not necessarily located along the axis perpendicular to the applied load. The assumption that the I.C. of a bolt group lies along the axis perpendicular to the applied load is incorrect for all but a few very specific instances. Finally, the authors concluded that the search for instantaneous center cannot be limited to the one-dimensional line, but must include all points in the two-dimensional plane.

The authors are actually embracing and employing Donald Brandt’s method in determining the instantaneous center. The Brandt’s method is the very same method being used to calculate the values presented in AISC Manual. The authors have the following remarks as to the calculation of the values presented in the table. Excerpts:

THE AISC COEFFICIENTS C FOR ECCENTRICALLY LOADED BOLT GROUPS

Finding errors in the presentation of the theory underlying the calculation of the instantaneous center calls into question the values in Tables 7-17 through 7-24 (Tables 7-7 through 7-14 in AISC Manual 13th Edition) presented in the AISC Manual. Fortunately these values were produced by a program based on Brandt’s work. Brandt’s procedure never restricts its search for the instantaneous center to the line perpendicular to the applied load and passing through the centroid of the connection, though from a programming standpoint this would seem to be the most efficient approach. Instead both the location of the instantaneous center and the angle of the resultant are allowed to drift off of the values implied by Crawford and Kulak’s theory until equilibrium is satisfied. Being based solely on the load-deformation relationship and equilibrium, the C-values presented by AISC are correct. The authors have checked numerous cases and are satisfied that both the load-deformation relationship and equilibrium are satisfied using Brandt’s approach.

So, finally the question has been answered through Steven's effort and resourcefulness. Grateful for Steven’s effort, I responded:

Thanks, Steve, for the excerpts...

To complete the reading, I downloaded the same article written by Larry Muir and Thornton from the AISC website. After reading, I now understand why the differences between the values returned by the VBA code and that of the table provided by AISC Steel Construction Manual. It is all about Crawford & Kulak's method against that of Brandt’s approach. The table is derived using Brandt's while the VBA Code is based on Crawford and Kulak's.

Having learned that the values in Tables presented in AISC Manual were produced by a program based on Brandt’s work, I will try to write another VBA Code based on the same approach -- on my subsequent blogposts...

Graphically, the figures below show how the 'Crawford & Kulak's Approach' and 'Donald Brandt's Approach' differ from each other:

Instantaneous Center in Crawford and Kulak's Approach Instantaneous Center in Donald Brandt's Approach
In Crawford and Kulak's Approach, the instantaneous center is searched and limited only to the one-dimensional line that passes thru the centroid of the bolt group and perpendicular to the applied load. In contrast, the search for instantaneous center in Brandt's Approach includes all points in a two-dimensional plane. The two methods, however, would agree on results when the geometry of the bolt group is symmetrical about its centroidal axis parallel to the applied load.

And finally, here's the Modified VBA Code for calculating the Coefficient C for Eccetrically Loaded Bolt Group. With the VBA Code now modified based on Brandt's Approach, the resulting values for bolt coefficient C now hover between 98% and 99.9% relative to the values provided by the tables in AISC Manual with values returned by the VBA Code being the higher ones.

VBA Code: BOLT COEFFICIENT CALCULATOR Employing Brandt's Approach

 
''' 
'''Redem Legaspi Jr 
'''March 14, 2011 
'''Current Location: Manila, Philippines 
'''Hometown: Pio V. Corpus, Masbate, Philippines  

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 Fm, Fyy As Double     
   Dim xRo, yRo As Double     
   Dim Mo, Fy As Double     
   Dim Fx, Fxx 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 MapFunc As Double     
   Dim MirrFlag As Integer          
   Rv = Bolt_Row     
   Rh = Bolt_Column     
   Sv = Row_Spacing     
   Sh = Column_Spacing          
   ReDim BoltLoc(Rv * Rh - 1)          
   
   On Error Resume Next          

   Rot = Application.WorksheetFunction.Radians(Rotation)     
   Ec = Abs(Eccentricity * Cos(Rot))     
   MirrFlag = IIf(Sgn(Cos(Rot)) = -1, -1, 1)          
   
  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)) * MirrFlag  '''Rotate Horizontal Coordinate             
           End With             
           n = n + 1         
       Next     
    Next          
    
    xRo = 0     
    yRo = 0     
    Stp = False          
    ''Rn = 74 * (1 - Exp(-10 * 0.34)) ^ 0.55     '''Original Code     (inaccurate)
    Rn = 74                                      '''Per David Fanning (very accurate) 
    Do While Stp = False         
       rmax = 0         
       For i = 0 To Rv * Rh - 1             
          xi = BoltLoc(i).Dh + xRo
          yi = BoltLoc(i).Dv + yRo
          rmax = Application.WorksheetFunction.Max(rmax, Sqr(xi ^ 2 + yi ^ 2))             
          DoEvents         
       Next                  
       Fx = 0:  J = 0
       Fy = 0: Mo = 0
       For i = 0 To Rv * Rh - 1
          xi = BoltLoc(i).Dh + xRo
          yi = BoltLoc(i).Dv + yRo
          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) * (xi / ri)       '''Vertical Force            
          Fx = Fx + (iRn / Rn) * (yi / ri)       '''Horizontal Force             
          J = J + 0.015531555 + 0.441786467 * ri ^ 2
          DoEvents
       Next
          
       Fm = Mo / (Ec + xRo)
       Fyy = Fm - Fy                             '''Vertical Unbalanced Force
       Fxx = -Fx                                 '''Horizontal Unbalanced Force
       
       Stp = Abs(Fyy) <= 0.00001 And Abs(Fxx) <= 0.00001
        
       MapFunc = J / (Rv * Rh * Mo)              '''Mapping function
       xRo = xRo + Fyy * MapFunc                 '''I.C. location on x-axis from bolt group C.G.
       yRo = yRo + Fxx * MapFunc                 '''I.C. location on y-axis from bolt group C.G.
             
       DoEvents
    Loop
    BoltCoefficient = (Fm + Fy) / 2
    
    Exit Function
ForcedExit:
    BoltCoefficient = Rv * Rh
End Function
Note:

To implement and run the VBA Code in Microsoft Excel Environment, please read my previous blogpost, "Eccentrically Loaded Bolt Group: Analysis and Design using 'INSTANTANEOUS CENTER of ROTATION METHOD' with VBA for Excel Implementation", for instructions.


ALTERNATE VBA Code for BOLT COEFFICIENT CALCULATOR (Updated)

'''   
'''Redem Legaspi Jr
'''June 24, 2015
'''Current Location: Manila, Philippines
'''Hometown: Pio V. Corpus, Masbate, Philippines  

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 P, uFy As Double
   Dim xRo, yRo As Double
   Dim M, Ry As Double
   Dim Rx, uFx As Double
   Dim xi, yi As Double
   Dim ri, x1 As Double
   Dim y1, Rot As Double
   Dim Ru, iRn As Double
   Dim Rv, Rh As Integer
   Dim Sh, Sv As Double
   Dim Ec As Double
   Dim Delta, LiMax As Double
   Dim BoltLoc() As BoltInfo
   Dim Stp As Boolean
   Dim J As Double
   Dim MapFunc As Double
   Dim ro As Double
   Dim Py As Double
   Dim Px As Double
   Rv = Bolt_Row
   Rh = Bolt_Column
   Sv = Row_Spacing
   Sh = Column_Spacing
   ReDim BoltLoc(Rv * Rh - 1)
   
   On Error Resume Next

   Rot = Application.WorksheetFunction.Radians(Rotation)
   Ec = Abs(Eccentricity)
   
  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 = y1
              .Dh = x1
           End With
           n = n + 1
       Next
    Next
    
    xRo = 0
    yRo = 0
    Stp = False
    Ru = 74
    Do While Stp = False
       LiMax = 0
       For i = 0 To Rv * Rh - 1
          xi = BoltLoc(i).Dh + xRo
          yi = BoltLoc(i).Dv + yRo
          LiMax = Application.WorksheetFunction.Max(LiMax, Sqr(xi ^ 2 + yi ^ 2))
          DoEvents
       Next
       Rx = 0:  J = 0
       Ry = 0:  M = 0
       For i = 0 To Rv * Rh - 1
          xi = BoltLoc(i).Dh + xRo
          yi = BoltLoc(i).Dv + yRo
          ri = Sqr(xi ^ 2 + yi ^ 2)
          Delta = 0.34 * ri / LiMax
          iRn = 74 * (1 - Exp(-10 * Delta)) ^ 0.55
          M = M + (iRn / Ru) * ri                       '''Moment
          Ry = Ry + (iRn / Ru) * (xi / ri)              '''Vertical Force
          Rx = Rx + (iRn / Ru) * (yi / ri)              '''Horizontal Force
          J = J + ri ^ 2
          DoEvents
       Next
       ro = (Ec + xRo) * Cos(Rot) + yRo * Sin(Rot)
       P = M / ro
       Py = P * Cos(Rot)
       Px = P * Sin(Rot)
       
       uFy = Py - Ry                                    '''Vertical Unbalanced Force
       uFx = Px - Rx                                    '''Horizontal Unbalanced Force
       
       Stp = Abs(uFy) <= 0.00001 And Abs(uFx) <= 0.00001
        
       MapFunc = J / (Rv * Rh * M)                      '''Mapping function
       xRo = xRo + uFy * MapFunc                        '''I.C. location on x-axis from bolt group C.G.
       yRo = yRo + uFx * MapFunc                        '''I.C. location on y-axis from bolt group C.G.
             
       DoEvents
    Loop
    BoltCoefficient = P
    
    Exit Function
ForcedExit:
    BoltCoefficient = Rv * Rh
End Function

ALTERNATE VBA Code for BOLT COEFFICIENT CALCULATOR

(Updated Code based on Mark Lasby's suggestion)

 
'''
'''Redem Legaspi Jr
'''Aug 8, 2015
'''Current Location: Manila, Philippines
'''Hometown: Pio V. Corpus, Masbate, Philippines

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 P, uFy As Double
   Dim xRo, yRo As Double
   Dim M, Ry As Double
   Dim Rx, uFx As Double
   Dim xi, yi As Double
   Dim ri, x1 As Double
   Dim y1, Rot As Double
   Dim Ru, iRn As Double
   Dim Rv, Rh As Integer
   Dim Sh, Sv As Double
   Dim Ec As Double
   Dim Delta, LiMax As Double
   Dim BoltLoc() As BoltInfo
   
   Dim Stp As Boolean
   Dim Stp1 As Boolean
   Dim Stp2 As Boolean
   Dim Stp3 As Boolean
   
   Dim J As Double
   Dim MapFunc As Double
   Dim ro As Double
   Dim Py As Double
   Dim Px As Double
   Rv = Bolt_Row
   Rh = Bolt_Column
   Sv = Row_Spacing
   Sh = Column_Spacing
   ReDim BoltLoc(Rv * Rh - 1)
   
   Const CntMax = 5000
   Dim Cnt As Double
   Dim Pprev As Double
   Dim Pp As Double
   
   On Error Resume Next

   Rot = Application.WorksheetFunction.Radians(Rotation)
   Ec = Abs(Eccentricity)
   
  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 = y1
              .Dh = x1
           End With
           n = n + 1
       Next
    Next
    
    xRo = 0
    yRo = 0
    Stp = False
    Ru = 74
    Do While Stp = False
       Cnt = Cnt + 1
       LiMax = 0
       For i = 0 To Rv * Rh - 1
          xi = BoltLoc(i).Dh + xRo
          yi = BoltLoc(i).Dv + yRo
          LiMax = Application.WorksheetFunction.Max(LiMax, Sqr(xi ^ 2 + yi ^ 2))
          DoEvents
       Next
       Rx = 0:  J = 0
       Ry = 0:  M = 0
       For i = 0 To Rv * Rh - 1
          xi = BoltLoc(i).Dh + xRo
          yi = BoltLoc(i).Dv + yRo
          ri = Sqr(xi ^ 2 + yi ^ 2)
          Delta = 0.34 * ri / LiMax
          iRn = 74 * (1 - Exp(-10 * Delta)) ^ 0.55
          M = M + (iRn / Ru) * ri                       '''Moment
          Ry = Ry + (iRn / Ru) * (xi / ri)              '''Vertical Force
          Rx = Rx + (iRn / Ru) * (yi / ri)              '''Horizontal Force
          J = J + ri ^ 2
          DoEvents
       Next
       ro = (Ec + xRo) * Cos(Rot) + yRo * Sin(Rot)
       P = M / ro
       Py = P * Cos(Rot)
       Px = P * Sin(Rot)
       
       uFy = Py - Ry                                    '''Vertical Unbalanced Force
       uFx = Px - Rx                                    '''Horizontal Unbalanced Force
       
       Pp = (uFy ^ 2 + uFx ^ 2) ^ 0.5                       ''<--Code courtesy of Mark Lasby
       
       Stp1 = Abs(uFy) <= 0.00001 And Abs(uFx) <= 0.00001
       Stp2 = (Cnt > CntMax)                                ''<--Code courtesy of Mark Lasby
       Stp3 = IIf(Cnt > 1, Pp > Pprev, False)               ''<--Code courtesy of Mark Lasby
       Stp = (Stp1 Or Stp2 Or Stp3)                         ''<--Code courtesy of Mark Lasby
        
       Pprev = Pp                                           ''<--Code courtesy of Mark Lasby
         
       MapFunc = J / (Rv * Rh * M)                      '''Mapping function
       xRo = xRo + uFy * MapFunc                        '''I.C. location on x-axis from bolt group C.G.
       yRo = yRo + uFx * MapFunc                        '''I.C. location on y-axis from bolt group C.G.
             
       DoEvents
    Loop
    BoltCoefficient = P
    
    Exit Function
ForcedExit:
    BoltCoefficient = Rv * Rh
End Function

27 comments:

  1. I like this post thanks you are sharing

    ReplyDelete
  2. Excellent resource, thanks for sharing.

    ReplyDelete
  3. thanks for sharing but my values still do not match the aisc table:
    for instance
    # of bolt rows: 6
    # bolt column: 2
    Spacing of row: 3
    Spacing of column: 5.5
    eccentricity: 16
    load rotation: 0

    coef. C (from vba - Prandt) = 3.62
    coef. C from aisc table 7-9 (page 7-46 13th Ed.)= 3.55

    ReplyDelete
  4. We have downloaded your VBA and are trying to incorporate it, but it seems as if C' is excluded although it is included in "Pro Tools" and your excel spreadsheet available on steel tools. Do you have VBA code you can provide for standalone excel programming to create a excel formula for C' ??

    Thanks.

    ReplyDelete
  5. Hi,

    Here is the code for calculating C' of bolt group:

    Function BoltCPrime(Bolt_Row As Integer, Bolt_Column As Integer, Row_Spacing As Double, Column_Spacing As Double) As Double
    Dim i, k, n As Integer
    Dim xi, yi As Double
    Dim ri, x1 As Double
    Dim y1 As Double
    Dim Rv, Rh As Integer
    Dim Sh, Sv As Double
    Dim rmax As Double
    Dim BoltLoc() As BoltInfo
    Dim CPrime As Double
    Dim xPrime As Double

    Rv = Bolt_Row
    Rh = Bolt_Column
    Sv = Row_Spacing
    Sh = Column_Spacing
    ReDim BoltLoc(Rv * Rh - 1)

    On Error Resume Next

    n = 0
    rmax = 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 = y1
    .Dh = x1
    End With
    rmax = Application.WorksheetFunction.Max(rmax, Sqr(x1 ^ 2 + y1 ^ 2))
    n = n + 1
    Next
    Next

    For i = 0 To Rv * Rh - 1
    xi = BoltLoc(i).Dh
    yi = BoltLoc(i).Dv
    ri = Sqr(xi ^ 2 + yi ^ 2)

    xPrime = ri * (1 - Exp(-10 * ri * 0.34 / rmax)) ^ 0.55
    CPrime = CPrime + xPrime
    Next
    BoltCPrime = CPrime
    End Function


    Regards,

    RedemLegaspiJr

    ReplyDelete
  6. The weld strength calculator utilizing IC method at steel tools is a great tool but differs from the value in AISC 13th Ed. by providing phi*Rn values that are approximately 0.875 * Rn than 0.75 * Rn. Can you please clarify?
    Thanks.

    ReplyDelete
  7. Redem,

    The difference in your C values from those in the AISC 13th Edition Manual are due to dividing by Rn in these three lines of your code:

    Mo = Mo + (iRn / Rn) * ri '''Moment
    Fy = Fy + (iRn / Rn) * (xi / ri) '''Vertical Force
    Fx = Fx + (iRn / Rn) * (yi / ri) '''Horizontal Force

    If Rn is replaced by 74, as shown below, then you will get the same C values as given in the AISC manual.

    Mo = Mo + (iRn / 74) * ri '''Moment
    Fy = Fy + (iRn / 74) * (xi / ri) '''Vertical Force
    Fx = Fx + (iRn / 74) * (yi / ri) '''Horizontal Force

    In the AISC manual, the most highly stressed bolt contributes 0.9815 to the value of C, while in your code the most highly stressed bolt contributes a value of unity to the value of C. The AISC method states that the bolt most remote from the instantaneous center can be determined by applying a maximum deformation DeltaMax (0.34 inch) to that bolt. Putting delta=0.34 into the load-deformation relationship results in a value of 0.9815. This means that no bolt can be stressed beyond 98.15%. In your VBA code you adjusted the R values for all the bolts by dividing by 0.9815 (iRn/Rn). This ratio is unity for the most highly stressed bolt and seems very logical. However, the AISC manual does not divide iRN by Rn.

    Following the AISC method gives somewhat illogical results, in my opinion, as eccentricity approaches zero. Since the AISC method limits bolts to 0.9815 of their ultimate, C values approach 0.9815 times the number of bolts as eccentricity approaches zero (try an eccentricity of 0.001 in the modified code). Your unmodified code, results in C values approaching the number of bolts as eccentricity approaches zero; this seems much more logical than the AISC results. In my opinion, the equations should work for any eccentricity so adjusting R by dividing it by Rn (0.9815) is logical. (Note: When I coded a similar routine, I left the 74 out of the equations for Rn and iRn since they cancel out. That's why I refer to Rn=0.9815 above when in your code it is actually 0.9815*74).

    Thanks for submitting your code for others to use.

    ReplyDelete
  8. Dave, thank you so much for this; for your very remarkable effort. I think you have found the solution to RECONCILE the values returned by the 'code' and that of the values from the table provided by 'AISC 13th Edition Manual'. I will be doing some tweaks on the ‘code’ based on your findings.

    Many, many thanks…

    ReplyDelete
  9. When you have large angles with single bolt rows the code seems to get caught in a loop. For example:
    Bolt Rows=2
    Bolt Columns=1
    Row Spacing =3
    Column Spacing =0
    Ecc=6
    Angle =75

    Any ideas on how to fix this?

    ReplyDelete
  10. Do you also have VBA code you are willing to share to calculate Weld Coefficients? I see you have a program here, http://www.steeltools.org/resources/viewdocument/?DocumentKey=b8166b34-727f-4b7e-b4c1-9f05d74b0940, but I need it in Excel. Thanks!

    ReplyDelete
  11. Hi Tim,

    I have posted an excel version of the program here:
    http://www.steeltools.org/resources/viewdocument/?DocumentKey=40e83a4a-7d05-4e72-9417-fb0bcfea78bb

    You may also opt to send me an email at
    redemlegaspi@gmail.com
    for an updated copy of the program.

    ReplyDelete
  12. Thank you! I see why you couldn't just post the code here.

    ReplyDelete
  13. Hi Tim,

    I had been considering posting the code for weld analysis here. I wanted to implement the code (visual basic) the way the code for bolt analysis is implemented here. Unfortunately, the code's complexity makes the implementation impossible.

    There are at least two observations why the code may not be implemented as USER DEFINED FUNCTION in excel:

    1.) The time the program (or code) takes to converge at equilibrium is not predictable; it sometimes takes more than a minute. The longer the weld input, the more differential weld elements are created necessary for the analysis, and the longer time the analysis will take. The complexity is further amplified by load angle input.

    2.) When implemented as USER DEFINED FUNCTION, the code unpredictable behavior causes your MS Excel to crash. I don't want that to happen. That's the reason why I implemented the program as stand-alone with intervention from the user.

    ReplyDelete
  14. Hi All,

    I'm so sorry for not mentioning this early on: The VBA code for BOLT COEFFICIENT employing Brandt's Approach has already been updated almost a year ago. It now returns 99.99% consistent with the AISC-provided tables --- courtesy of David Fanning who exerted remarkable effort to correct the results.

    Dave's solution was to replace this line:
    Rn = 74 * (1 - Exp(-10 * 0.34)) ^ 0.55

    with this:
    Rn = 74

    Many thanks, Dave.

    ReplyDelete
  15. Hi Redem,

    first of all thanks for all the spreadsheets you so kindly share online.
    Are you planning to revise the Excel Spreadsheet that calculates the coefficient C of a Bolt Group of ANY GEOMETRY that you shared in steel TOOLS to account for the differences in values of the C coefficients?

    ReplyDelete
  16. Hi Doug,

    I already have the revised version of the Excel Spreadsheet that calculates the coefficient C of a bolt group of any geometry. I will be posting very soon the update in AISC Steel Tools. I'm just a bit busy right now.

    In the meantime, if you want an advanced copy of the revised spreadsheet, please feel free to email me at: redemlegaspi@gmail.com or rlegaspi@crystalsteel.com.

    Regards and Godbless...

    ReplyDelete
  17. Artesia Springs is the locally owned and operated water bottle company in Corpus Christi ,San Antino in Texas, USA where the Best Price is always Guaranteed. For energy efficient water cooler for your office Call Us @ (210) 637-5554

    ReplyDelete
  18. Redeemer
    I am trying to follow the equations in your code for the Brandt method of calculating the Instantaneous Center. The equation that you use to calculate J (J = J + 0.015531555 + 0.441786467 * ri ^ 2) contains 2 terms that I cannot follow. Where do 0.015531555 and 0.441786467 come from? I cannot find them in Brandt's paper.
    Mark Lasby

    ReplyDelete
  19. Hi Mark,

    First of all, please be reminded that the load-deformation relationship of a bolt from which the formula R = Rult*(1 - e^(-10*Δ)^0.55 originates -- is based upon the data obtained experimentally for 3/4" diameter ASTM A325 bolts. (See pages 7-6 to 7-8 of AISC Steel Construction Manual 13th Edition)

    The term 0.015531555 + 0.441786467 * ri ^ 2 is simply the polar moment of inertia of 3/4" diameter bolt with respect to the presumed instantaneous center of rotation where ri is the radial distance of bolt from that instantaneous center.

    In variable form, the term 0.015531555 + 0.441786467 * ri ^ 2 is equivalent to (pi * D^4)/64 + (pi * D^2)/4 * ri^2 where D is the diameter of bolt (D = 3/4-inch). The term (pi * D^4)/64 is the moment of inertia of bolt about its own centroidal axis. The term (pi * D^2)/4 * ri^2, on the other hand, is the transfer moment of inertia of the same bolt which is equal to its cross-sectional area multiplied by the square of its distance to the instantaneous center.

    Regards,
    RedemLegaspiJr

    ReplyDelete
  20. Redeemer
    I am having difficulty getting some combinations of bolt layouts and eccentricities to converge. Try 2 columns of 3 bolts spaced at 3 inches each way with an eccentricity of 5.6 through 5.8 inches and an angle of 0 degrees.
    I added code to stop the iterations after 1999 calculations. If you want this code, please let me know.
    Mark

    ReplyDelete
  21. Mark, I got the code. Many thanks for your remarkable effort. This finally solves the problem with what we called the 'magic numbers' or input combination that traps the program in an infinite loop without the possibility of converging or approaching to an equilibrium.

    RedemLegaspiJr

    ReplyDelete
  22. nrows 2
    ncolumn 1
    srows 2.5
    scolumn 3
    ecc 2.3925
    rot 47

    coff c #VALUE! ( =BoltCoefficient(E7,E8,E9,E10,E11,E12) )

    Excel stops (or is it computing, idk) when I have the above said value above. Why? Is it because of the function? CPU? memory? or...?

    ReplyDelete
  23. Your excel crashes not because of your CPU -- but because of the function; or, I may say because of the program code. The programming loop that calculates 'C' did not terminate because bolt forces did not converge to equilibrium. I've been expecting this to happen for some special 'input combination' like yours. I call such 'input combination' as 'Magic Numbers'.

    This has been a cause of my headache and of the others who encountered similar predicament. One of those 'others' was an engineer named Mark Lasby who, himself, is a programmer. He exerted so much effort helping me finally solved this problem.

    Many, many thanks, Mark...

    Please replace your code with the 'updated code based on Mark Lasby's suggestion' posted above. I am very, very, very, very sure this finally solves the problem...

    RedemLegaspiJr


    ReplyDelete
  24. Hahaha sorry for my English in my last comment, didn't noticed.

    And.. WOW its really working. Thank you guys! Thank you very much Redem and Mark.

    ReplyDelete
  25. While Mr. Lasby's code solves the problem with magic numbers I still wanted to write to you as I learned something about VBA while reviewing the code that is different from my expectation. In particular the DIM statement works unexpectedly. I'm running Office 2010 and this is taken from the help file.
    ' Multiple declarations on a single line. AnotherVar is of type Variant because its type is omitted.
    Dim AnotherVar, Choice As Boolean, BirthDate As Date

    This means the statement :
    DIM i, k, n As Integer
    only defines n as an integer but leaves i and k as variant. By using a breakpoint just after the Dim statements and the locals window I confirmed this. However by putting a breakpoint near the end of the code I also confirmed that VBA had managed to figure out the context and change them to variant/integer when they were used. It seems the correct VBA statement should be :
    Dim i As Integer, k As Integer, n As Integer
    In the case of the bolt group coefficient program everything worked as intended, but I thought I would pass this along for you to consider for any other VBA programs you publish.

    Thank you for publishing your VBA code, and continuing to support/modify it.

    Geoff

    ReplyDelete
  26. I am in doubt on what code I use in my spreadsheet, because if I use the code (Redem Legaspi Jr '' 'June 24, 2015) I have difficulty setting some settings that excel does not converge. However if I use the code (Redem Legaspi Jr '' 'Aug 8, 2015) the values do not match equal to the AISC table.

    Could you tell me which code is most appropriate and if the above codes have been changed.


    In code have problem (Redem Legaspi Jr '' 'Aug 8, 2015), the value are not same the table AISC for angle bigger than 45°

    For code (Redem Legaspi Jr '' 'June 24, 2015) have any configurancion that the excel don't converge

    If you want talk about the code, you can talk with me through the my adress e-mail: tcastelani86@gmail.com

    ReplyDelete