用VBA实现定期检验中的计算.docx

1、用VBA实现定期检验中的计算用VBA实现压力容器压力管道定期检验中的计算云南云天化无损检测有限公司 陈兴友摘要：Excel，VBA，压力容器，压力管道，定期检验，计算。 VBA是Visual Basic For Applications的简称，它是微软公司开发，建立在Office中的一种应用程序开发工具，是一种程序语言。在Excel中，利用VBA可以有效扩展Excel的功能，设计和构建人机交互界面，打造自己的管理系统，帮组Excel用户更有效地完成一些基础操作、函数公式等很难完成的任务。在压力容器、压力管道定期检验中，经常遇到各类计算，当从现场采集各类数据后，要迅速的得到结果，就需要借助VBA

2、。本文介绍通过Excel VBA实现快速计算功能。定期检验中的计算主要有：压力容器:1、强度校核；2、凹坑G0计算；3、非圆形缺陷定级；压力管道：1、强度校核；2、未熔合定级；3、局部减薄计算。下面分别介绍如下：1、 压力容器强度校核分别按应用较多的部件进行设计:内压圆筒、内压球壳、内压椭圆封头。椭圆封头把标准封头和非标准封头分别列开计算。其他按顺序分别计算即可。下图为典型的计算过程：2、 压力容器无量纲G0计算先设计输入项：条件判断与计算1-4项为手工判断，5-8项为程序自动判断。G0=C/T*(A/(RT)0.5，G0通过需程序自动计算，G00.1,则凹坑允许存在。程序部分代码：Range

3、(Q15).Value = 通过Range(Q15).Font.Bold = TrueRange(Q15).Font.color = RGB(255, 0, 0)G = c * a / (t * (R * t) 0.5) 计算G0Range(Q16).Value = G 为单元格Q16复制Range(Q16).Font.Bold = TrueRange(Q16).Font.color = RGB(255, 0, 0)Range(Q17).Font.Bold = TrueRange(Q17).Font.color = RGB(255, 0, 0)If G = 0.1 ThenRange(Q17)

4、.Value = 不允许End If3、 压力容器非圆形缺陷定级非圆形缺陷定级主要指：未熔合、未焊透、条状夹渣。分别按一般压力容器和有特殊要求的压力容器进行计算。我们看一般压力容器纵缝未熔合定级（其他类同），H0.1t且H2,L2t时定3级（H为缺陷自身高度，L为缺陷长度，t为板厚）。那么超出上述尺寸应该定4级，低于上述尺寸应该定2级。我们可以用一个函数来实现：Public Function 容器未熔合a(H, L, t As Single) As StringIf H = Then容器未熔合a = -级GoTo EscElseIf H = 0.1 * t And H = 2 And L =

5、0.67 * t Then 定2级的情况容器未熔合a = 2级ElseIf H = 0.1 * t And H = 2 And L 0.5 * c ThenFusionx = GC1级管道，当单个焊接接头未熔合的总长度大于焊接接头长度的50%时，定4级。ElseIf GC = ThenFusionx = -ElseIf GC = GC1 Or GC = GC2 Or GC = GC3 ThenSelect Case tCase 0 To 2.49If L 0 ThenFusionx = 存在未熔合时，定4级End IfCase 2.5 To 3.99If H = 0.15 * t And H

6、0.5 ThenFusionx = 不超过0.15t且不超过0.5mm不影响定级ElseFusionx = 超过0.15t或0.5mm，定4级End IfCase 4 To 7.99If H = 0.15 * t And H 1# ThenFusionx = 0.15t与1.0mm中的较小值ElseIf H = 0.2 * t And H 1.5 ThenFusionx = 0.20t与1.5mm中的较小值ElseFusionx = 超过0.20t与1.5mm中的较小值End IfCase 8 To 11.99If H = 0.15 * t And H 1.5 ThenFusionx = 0.

7、15t与1.5mm中的较小值ElseIf H = 0.2 * t And H 2# ThenFusionx = 0.20t与2.0mm中的较小值ElseFusionx = 超过0.20t与2.0mm中的较小值End IfCase 12 To 19.99If H = 0.15 * t And H 2# ThenFusionx = 0.15t与2.0mm中的较小值ElseIf H = 0.2 * t And H = 20If H 3# ThenFusionx = 超过3.0mmElseIf H = 0.2 * t And H 5# ThenFusionx = 0.20t与5.0mm中的较小值Els

8、eFusionx = 超过0.20t与5.0mm中的较小值End IfEnd SelectEnd IfEnd Function操作界面如下：6、 压力管道局部减薄计算局部减薄计算是所有计算中最为复杂的计算。涉及的参数多，过程复杂。首先看看输入参数（7个）：下面是过程参数与计算结果：通过函数score实现定级：Public Function score(GC, p, b As String, t, c, H As Single) As StringApplication.Volatile TrueSelect Case GCCase Is = score = -级GC1管道Case Is = G

9、C1P0.3PL0条件If p = P0.3PL0 ThenSelect Case bCase Is = B/(D)0.25If H = (0.3 * t - c) Thenscore = 2级ElseIf H = (0.35 * t - c) Thenscore = 3级Elsescore = 4级End IfCase Is = 0.25B/(D)0.75If H = (0.2 * t - c) Thenscore = 2级ElseIf H = (0.3 * t - c) Thenscore = 3级Elsescore = 4级End IfCase ElseIf H = (0.15 * t

10、- c) Thenscore = 2级ElseIf H = (0.2 * t - c) Thenscore = 3级Elsescore = 4级End IfEnd SelectEnd If*0.3PL0P0.5PL0条件If p = 0.3PL0P0.5PL0 ThenIf b = B/(D)0.25 ThenIf H = (0.15 * t - c) Thenscore = 2级ElseIf H = (0.2 * t - c) Thenscore = 3级Elsescore = 4级End IfElseIf b = 0.25B/(D)0.75 Or b = 0.75B/(D)1.00 The

11、nIf H = (0.1 * t - c) Thenscore = 2级ElseIf H = (0.15 * t - c) Thenscore = 3级Elsescore = 4级End IfEnd IfEnd If*GC2或GC3管道Case Is = GC2, GC3P0.3PL0条件If p = P0.3PL0 ThenSelect Case bCase Is = B/(D)0.25If H = (0.33 * t - c) Thenscore = 2级ElseIf H = (0.4 * t - c) Thenscore = 3级Elsescore = 4级End IfCase Is =

12、 0.25B/(D)0.75If H = (0.25 * t - c) Thenscore = 2级ElseIf H = (0.33 * t - c) Thenscore = 3级Elsescore = 4级End IfCase ElseIf H = (0.2 * t - c) Thenscore = 2级ElseIf H = (0.25 * t - c) Thenscore = 3级Elsescore = 4级End IfEnd SelectEnd If*0.3PL0P0.5PL0条件If p = 0.3PL0P0.5PL0 ThenIf b = B/(D)0.25 ThenIf H = (

13、0.2 * t - c) Thenscore = 2级ElseIf H = (0.25 * t - c) Thenscore = 3级Elsescore = 4级End IfElseIf b = 0.25B/(D)0.75 Or b = 0.75B/(D)1.00 ThenIf H = (0.15 * t - c) Thenscore = 2级ElseIf H = (0.2 * t - c) Thenscore = 3级Elsescore = 4级End IfEnd IfEnd IfCase Elsescore = -级MsgBox 请用下拉菜单输入管道级别！End SelectEnd Fun

14、ctionPublic Function scoreN(GC, p, b As String, t, c, H As Single) As StringApplication.Volatile TrueGC1管道If GC = ThenscoreN = 无End IfIf GC = GC1 ThenP0.3PL0条件If p = P0.3PL0 ThenSelect Case bCase Is = B/(D)0.25If H = (0.3 * t - c) ThenscoreN = 0.30t-CElseIf H = (0.35 * t - c) ThenscoreN = 0.35t-CEls

15、escoreN = 超标End IfCase Is = 0.25B/(D)0.75If H = (0.2 * t - c) ThenscoreN = 0.20t-CElseIf H = (0.3 * t - c) ThenscoreN = 0.30t-CElsescoreN = 超标End IfCase ElseIf H = (0.15 * t - c) ThenscoreN = 0.15t-CElseIf H = (0.2 * t - c) ThenscoreN = 0.20t-CElsescoreN = 超标End IfEnd SelectEnd If*0.3PL0P0.5PL0条件If

16、p = 0.3PL0P0.5PL0 ThenIf b = B/(D)0.25 ThenIf H = (0.15 * t - c) ThenscoreN = 0.15t - CElseIf H = (0.2 * t - c) ThenscoreN = 0.20t-CElsescoreN = 超标End IfElseIf b = 0.25B/(D)0.75 Or b = 0.75B/(D)1.00 ThenIf H = (0.1 * t - c) ThenscoreN = 0.10t-CElseIf H = (0.15 * t - c) ThenscoreN = 0.15t-CElsescoreN

17、 = 超标End IfEnd IfEnd IfEnd If*GC2或GC3管道If GC = GC2 Or GC = GC3 ThenP0.3PL0条件If p = P0.3PL0 ThenSelect Case bCase Is = B/(D)0.25If H = (0.33 * t - c) ThenscoreN = 0.33t-CElseIf H = (0.4 * t - c) ThenscoreN = 0.40t-CElsescoreN = 超标End IfCase Is = 0.25B/(D)0.75If H = (0.25 * t - c) ThenscoreN = 0.25t-C

18、ElseIf H = (0.33 * t - c) ThenscoreN = 0.33t-CElsescoreN = 超标End IfCase ElseIf H = (0.2 * t - c) ThenscoreN = 0.20t-CElseIf H = (0.25 * t - c) ThenscoreN = 0.25t-CElsescoreN = 超标End IfEnd SelectEnd If*0.3PL0P0.5PL0条件If p = 0.3PL0P0.5PL0 ThenIf b = B/(D)0.25 ThenIf H = (0.2 * t - c) ThenscoreN = 0.20

19、t-CElseIf H = (0.25 * t - c) ThenscoreN = 0.25t-CElsescoreN = 超标End IfElseIf b = 0.25B/(D)0.75 Or b = 0.75B/(D)1.00 ThenIf H = (0.15 * t - c) ThenscoreN = 0.15t-CElseIf H = (0.2 * t - c) ThenscoreN = 0.20t-CElsescoreN = 超标End IfEnd IfEnd IfEnd IfEnd Function结束语 通过Excel VBA很好的实现了，压力容器、压力管道定期检验中的自动计算，减轻了我公司检验员的工作量，提高了效率。