I just want to share an enhancement to the BOLT COEFFICIENT CALCULATOR VBA Code made by an engineer friend from Canada.
Martin Boisclair, a project engineer from Canam-Heavy of Canam Group Inc. sent me an email sometime ago requesting for a copy of an updated version of the BOLT COEFFICIENT CALCULATOR VBA code. I gave him the code and then an exchange of emails ensued. Fortunately, our exchange of emails turned out to be more than just formalities -- but an exchange of bright ideas. I was so lucky to find that Martin is himself a good programmer. It was good to know that we are on the same level of thinking; basically on the 'same brainwave frequency'. In effect, he helped me improve the code.
Martin found out that the issue about magic numbers has not been fully resolved. The problem in convergence to equilibrium still exists for some input combinations regardless of the modifications previously made to the code. Magic numbers are not really magical as it is just a term being used to describe an input combination that causes the force system not to converge to equilibrium -- that brings about the problem that we have been trying to solve for quite a long time. Martin's enhancement to the code finally solved the problem as he explains in one of his lengthy emails below:
First, I want to thank you again for sharing your work and I hope you find my work useful. I hope your mother is better. I also want to add that in no way I was expecting that you would respond during that time or during weekends. I only count on your kindness.
My first thought when I looked at the results (with the code revised with the Mark Lasby add)... and after running and comparing with all AISC value, was that I found some non-conservative results and this was not acceptable. I didn't like that. Also, at Canam, we're often challenged by Engineer of Record.
Ok... so... it is not acceptable that a C factor is returned if there's no convergence. The first thing I did was to remove the Stp3 code because this was the reason why values did not converge. Stp3 makes that... in some instances... we were returned with non-conservative values. On the other hand, removing Stp3, made that the function did not converge and stopped and CntMax. Roughly, ~95% of the AISC table worked but we were stuck with some rebellious values (~5% of the table). Magic numbers had to be taken care of.
It took about 2 weeks to come up with the revised VBA code. To make it easier for you to understand the revised code, I attached a pdf file with some comments for you. Note that most of this code revision is based on observation/experiment of the numerical behavior of MapFunc and Pp.
I looked at the behavior of some variables with Debug.Print and figured Magic1 (Bouncing Pp and Bouncing MapFunc). To those values, I wanted out of "MapFunc = J / (Rv * Rh * M)" to try playing with MapFunc by other means. I tried linear interpolation between MapFunc, Pp and MapFuncLast, PpLast but it didn't work. Then, I came up with the idea of manually incrementing MapFunc and changing the direction of the increment as required. It worked but it had to be tweaked and I figured that Incr should not go below a certain value. I figured 0.01 as a minimum value of Incr. Also, sometimes Mapfunc went below 0 and I also had to tweak the VBA code to prevent that from happening.
I ran all the AISC tables and found out that Magic1 solves ~4.5% of the rebellious 5%. I was still stucked with ~0.5% of values that didn't converge. So, I looked at the numerical behavior of the last 0.5% rebellious rebels and figured a way to detect them. After some additional tweaking of the Alternate method of iterating MapFunc, all AISC tables ran and converged.
Iterating for the C factor is always a fragile balance act. It's very sensitive and small differences may make that it does not converge. For instance, with this revised code, if I change all "Dim As Double" to "Dim As Single"... some values don't converge!!
I spoke with the guy that programmed our own Canam code (not as a function) and he told me the same. So even today, our own Canam code made in 1996, even though this code solves all AISC table values, it still don't converge on some combination of inputs. Here's an example where our Canam code don’t converge (2,1,3,0,9.5,78.7). On the other hand the revised function works for this example.
Now, I think it's fair to say that the revision of VBA code I came up with, will not work ALL the time... but it's still an improvement.
Thanks for your support. Awaiting your comments!
Canam-Heavy, a division of Canam Group Inc.
P 450-641-4000; 3555 / M 514-702-4343