Excel(ESteel)自定义函数源代码

PublicFunctionDJ(钢筋直径AsSingle,最小搭接直径AsSingle,搭接类别AsString,机械接头AsString,锚固AsSingle)DimxAsInteger,X1AsInteger,X2AsInteger,aAsIntegerIf钢筋直径>0Thena=1Elsea=0EndIfIf钢筋直径>最小搭接直径ThenIf机械接头="双面焊10D"ThenDJ=钢筋直径+2EndIfIf机械接头="单面焊5D"ThenDJ=钢筋直径/2+2EndIfIf机械接头="直螺纹"ThenDJ=0EndIfEndIfIf钢筋直径<=最小搭接直径ThenIf搭接类别="腰筋G"ThenDJ=钢筋直径*1.5:x=1EndIfIf搭接类别="搭接100%"Or搭接类别="构造柱"ThenDJ=MAX(锚固*1.6,30,0,0)*a:X1=1EndIfIf搭接类别="Q"Or搭接类别="Z"Or搭接类别="搭接25%"ThenDJ=MAX(锚固*1.2,30,0,0)*a:X2=1EndIfIfx+X1+X2=0Or搭接类别=""ThenDJ=MAX(锚固*1.4,30,0,0)*aEndIfEndIfEndFunctionPublicFunctionMG(抗震级别AsSingle,钢筋级别AsSingle,砼号CAsSingle,钢筋直径AsSingle)DimjAsSingleDimkAsSingleDimgAsSingleIf钢筋直径>=28ThenIf钢筋级别>=2Thenj=1.1Elsej=1EndIfElsej=1EndIfIf抗震级别<=2Thenk=1.15EndIfIf抗震级别=3Thenk=1.05EndIfIf抗震级别=4Thenk=1EndIfIf钢筋级别=1And抗震级别<=2ThenIf砼号C<=20Theng=45EndIfIf砼号C=25Theng=39EndIfIf砼号C=30Theng=35EndIfIf砼号C=35Theng=32EndIfIf砼号C=40Theng=29EndIfIf砼号C=45Theng=28EndIfIf砼号C=50Theng=26EndIfIf砼号C=55Theng=25EndIfIf砼号C>=60Theng=24EndIfEndIfIf钢筋级别=1And抗震级别=3ThenIf砼号C<=20Theng=41EndIfIf砼号C=25Theng=36EndIfIf砼号C=30Theng=32EndIfIf砼号C=35Theng=29EndIfIf砼号C=40Theng=26EndIfIf砼号C=45Theng=25EndIfIf砼号C=50Theng=24EndIfIf砼号C=55Theng=23EndIfIf砼号C>=60Theng=22EndIfEndIfIf钢筋级别=1And抗震级别=4ThenIf砼号C<=20Theng=39EndIfIf砼号C=25Theng=34EndIfIf砼号C=30Theng=30EndIfIf砼号C=35Theng=28EndIfIf砼号C=40Theng=25EndIfIf砼号C=45Theng=24EndIfIf砼号C=50Theng=23EndIfIf砼号C=55Theng=22EndIfIf砼号C>=60Theng=21EndIfEndIfIf钢筋级别=2And抗震级别<=2ThenIf砼号C<=20Theng=44EndIfIf砼号C=25Theng=38EndIfIf砼号C=30Theng=33EndIfIf砼号C=35Theng=31EndIfIf砼号C=40Theng=29EndIfIf砼号C=45Theng=26EndIfIf砼号C=50Theng=25EndIfIf砼号C=55Theng=24EndIfIf砼号C>=60Theng=24EndIfEndIfIf钢筋级别=2And抗震级别=3ThenIf砼号C<=20Theng=40EndIfIf砼号C=25Theng=35EndIfIf砼号C=30Theng=31EndIfIf砼号C=35Theng=28EndIfIf砼号C=40Theng=26EndIfIf砼号C=45Theng=24EndIfIf砼号C=50Theng=23EndIfIf砼号C=55Theng=22EndIfIf砼号C>=60Theng=22EndIfEndIfIf钢筋级别=2And抗震级别=4ThenIf砼号C<=20Theng=38EndIfIf砼号C=25Theng=33EndIfIf砼号C=30Theng=29EndIfIf砼号C=35Theng=27EndIfIf砼号C=40Theng=25EndIfIf砼号C=45Theng=23EndIfIf砼号C=50Theng=22EndIfIf砼号C=55Theng=21EndIfIf砼号C>=60Theng=21EndIfEndIfIf钢筋级别=3And抗震级别=4ThenIf砼号C<=20Theng=40EndIfIf砼号C=25Theng=40EndIfIf砼号C=30Theng=35EndIfIf砼号C=35Theng=32EndIfIf砼号C=40Theng=29EndIfIf砼号C=45Theng=28EndIfIf砼号C=50Theng=27EndIfIf砼号C=55Theng=26EndIfIf砼号C>=60Theng=25EndIfEndIfIf钢筋级别=3And抗震级别=3ThenIf砼号C<=20Theng=42EndIfIf砼号C=25Theng=42EndIfIf砼号C=30Theng=37EndIfIf砼号C=35Theng=34EndIfIf砼号C=40Theng=30EndIfIf砼号C=45Theng=29EndIfIf砼号C=50Theng=28EndIfIf砼号C=55Theng=27EndIfIf砼号C>=60Theng=26EndIfEndIfIf钢筋级别=3And抗震级别<=2ThenIf砼号C<=20Theng=46EndIfIf砼号C=25Theng=46EndIfIf砼号C=30Theng=40EndIfIf砼号C=35Theng=37EndIfIf砼号C=40Theng=33EndIfIf砼号C=45Theng=32EndIfIf砼号C=50Theng=31EndIfIf砼号C=55Theng=30EndIfIf砼号C>=60Theng=29EndIfEndIfIf钢筋级别>=4And抗震级别<=2ThenIf砼号C<=20Theng=55EndIfIf砼号C=25Theng=55EndIfIf砼号C=30Theng=49EndIfIf砼号C=35Theng=45EndIfIf砼号C=40Theng=41EndIfIf砼号C=45Theng=39EndIfIf砼号C=50Theng=37EndIfIf砼号C=55Theng=36EndIfIf砼号C>=60Theng=35EndIfEndIfIf钢筋级别>=4And抗震级别=3ThenIf砼号C<=20Theng=50EndIfIf砼号C=25Theng=50EndIfIf砼号C=30Theng=45EndIfIf砼号C=35Theng=41EndIfIf砼号C=40Theng=38EndIfIf砼号C=45Theng=36EndIfIf砼号C=50Theng=34EndIfIf砼号C=55Theng=33EndIfIf砼号C>=60Theng=32EndIfEndIfIf钢筋级别>=4And抗震级别=4ThenIf砼号C<=20Theng=48EndIfIf砼号C=25Theng=48EndIfIf砼号C=30Theng=43EndIfIf砼号C=35Theng=39EndIfIf砼号C=40Theng=36EndIfIf砼号C=45Theng=34EndIfIf砼号C=50Theng=32EndIfIf砼号C=55Theng=31EndIfIf砼号C>=60Theng=30EndIfEndIfIf钢筋直径=0ThenMG=0ElseMG=g*j*kEndIfEndFunctionPublicFunctionTJ(直径AsVariant,前端拐长度AsVariant,斜长AsVariant,成品身长AsVariant,原材长度AsVariant,原材模数AsInteger,NAsInteger)AsVariantDimxAsInteger,CAsIntegerIf前端拐长度>0Thenx=Round(直径*0.2,0):C=1Elsex=0:C=0EndIfIf成品身长>=原材长度-C*(前端拐长度+斜长)+xThenIfN=0ThenTJ=Round(原材长度/原材模数,0)-C*(前端拐长度+斜长)+xEndIfIfN=1ThenTJ=原材长度-Round(原材长度/原材模数,0)-(前端拐长度+斜长)+xEndIfEndIfIf成品身长<原材长度-C*(前端拐长度+斜长)+xThenTJ=0EndIfEndFunctionPublicFunctionRL(构件AsString,LAsSingle,斜长AsVariant,起头AsSingle,原材AsSingle,搭接长AsSingle,项AsString)DimiAsSingle:DimaAsSingleIf(起头+斜长)>0ThenIfL-(起头+斜长)<=原材-搭接长Theni=0:RL=L-(起头+斜长)+搭接长EndIfIfL-(起头+斜长)+搭接长>原材ThenIfL-(起头+斜长)+搭接长-Int((L-起头+搭接长)/(原材-搭接长))*(原材-搭接长)<=搭接长Theni=Int((L-(起头+斜长)+搭接长)/(原材-搭接长))-1:RL=L-起头+搭接长-(Int((L-起头+搭接长)/(原材-搭接长))-1)*(原材-搭接长)Elsei=Int((L-(起头+斜长)+搭接长)/(原材-搭接长)):RL=L-(起头+斜长)+搭接长-Int((L-起头+搭接长)/(原材-搭接长))*(原材-搭接长)EndIfEndIfa=(起头+斜长)+i*原材+RLEndIfIf(起头+斜长)=0ThenIfL<=原材Theni=0:RL=LEndIfIfL>原材ThenIfL-Int((L)/(原材-搭接长))*(原材-搭接长)<=搭接长Theni=Int(L/(原材-搭接长))-1:RL=L-(Int(L/(原材-搭接长))-1)*(原材-搭接长)Elsei=Int(L/(原材-搭接长)):RL=L-Int(L/(原材-搭接长))*(原材-搭接长)EndIfEndIfa=i*原材+RLEndIfIf构件="箍筋"Or构件="拉筋"Thena=0:RL=0Elsea=a:RL=RLEndIfIf项="收尾筋"ThenRL=RLEndIfIf项="根数"ThenRL=iEndIfIf项="料长"ThenRL=aEndIfEndFunctionPublicFunctionF(LAsInteger,直径AsInteger)AsStringDimaAsStringIfL=1Thena="φ"EndIfIfL=2Thena=""EndIfIfL=3Thena=""EndIfIfL=4Thena=""EndIfIfL=5Thena=""EndIfIf直径>0ThenF=a&直径ElseF=""EndIfEndFunctionPublicFunctionTC(构件类别AsString)AsIntegerDimYAsInteger,y1AsInteger,y2AsInteger,y3AsInteger,y4AsInteger,y5AsIntegerIf构件类别="Q"ThenTC=Sheets("参数表").Range("E14"):Y=1EndIfIf构件类别="Z"ThenTC=Sheets("参数表").Range("E12"):y1=1EndIfIf构件类别="B"ThenTC=Sheets("参数表").Range("E10"):y2=1EndIfIf构件类别="L"ThenTC=Sheets("参数表").Range("E8"):y4=1EndIfIf构件类别="JC"ThenTC=Sheets("参数表").Range("E5"):y5=1EndIfIf构件类别="QT"Or构件类别=""OrY+y1+y2+y3+y4+y5=0ThenTC=Sheets("参数表").Range("E4")EndIfEndFunctionPublicFunctionKAG(构件类别AsString)AsIntegerDimYAsInteger,y1AsInteger,y2AsInteger,y3AsInteger,y4AsInteger,y5AsIntegerIf构件类别="Q"ThenKAG=Sheets("参数表").Range("D14"):Y=1EndIfIf构件类别="Z"ThenKAG=Sheets("参数表").Range("D12"):y1=1EndIfIf构件类别="B"ThenKAG=Sheets("参数表").Range("D10"):y2=1EndIfIf构件类别="L"ThenKAG=Sheets("参数表").Range("D8"):y4=1EndIfIf构件类别="JC"ThenKAG=Sheets("参数表").Range("D5"):y5=1EndIfIf构件类别="QT"Or构件类别=""OrY+y1+y2+y3+y4+y5=0ThenKAG=Sheets("参数表").Range("D4")EndIfEndFunctionPublicFunctionWKL(构件类别AsString)AsVariantDima1AsVariant,b1AsVariant,C1AsVariant,DAsVariantIf构件类别=""Then构件类别=0Else构件类别=构件类别EndIfa1=InStrRev(构件类别,"WKL",Len(构件类别)):b1=InStrRev(构件类别,"KZL",Len(构件类别)):C1=InStrRev(构件类别,"QL",Len(构件类别))IfMAX(a1,b1,C1,0)>0ThenIfMAX(a1,b1,C1,0)=C1ThenD=MAX(a1,b1,C1,0)+1ElseD=MAX(a1,b1,C1,0)+2EndIfWKL=Mid(构件类别,1,D)ElseWKL=""EndIfEndFunctionPublicFunctionTS(构件AsString,WKLAsString)AsStringIf构件="吊筋"Or构件="鸭筋"ThenTS="吊筋"EndIfIf构件="悬挑一层"Or构件="悬挑二层"ThenTS="悬挑"EndIfIf构件="插口"ThenTS="插口"EndIfIf构件<>"吊筋"And构件<>"鸭筋"And构件<>"悬挑一层"And构件<>"悬挑二层"And构件<>"插口"ThenTS=WKLEndIfEndFunctionPublicFunctionTSG(构件AsString,构件类别AsString,WKLAsString)AsStringDimaAsIntegerIf构件="吊筋"Or构件="鸭筋"Or构件="悬挑一层"Or构件="悬挑二层"ThenIf构件="吊筋"Or构件="鸭筋"ThenTSG="吊筋":a=1EndIfIf构件="悬挑一层"Or构件="悬挑二层"ThenTSG="悬挑":a=1EndIfIf构件="插口"ThenTSG="插口":a=1EndIfElseIf构件类别="B"Anda<>1ThenTSG=Sheets("参数表").Range("C10")ElseTSG=WKLEndIfEndIfEndFunctionPublicFunctionWd(构件AsString,构件类别AsString,墙梁宽AsInteger)AsIntegerIf构件="拉筋"ThenIf构件类别="L"Or构件类别="Q"ThenWd=墙梁宽+1-Sheets("参数表").Range("F3")EndIfIf构件类别="Z"ThenWd=墙梁宽-Sheets("参数表").Range("F3")EndIfEndIfIf构件="箍筋"ThenWd=墙梁宽-Sheets("参数表").Range("F3")EndIfIf构件<>"拉筋"And构件<>"箍筋"ThenWd=0EndIfEndFunctionPublicFunctionWh(构件AsString,墙梁高AsInteger)AsIntegerIf构件="箍筋"ThenWh=墙梁高-Sheets("参数表").Range("G3")ElseWh=0EndIfEndFunctionPublicFunctionWL(构件AsString,直径AsInteger,宽AsInteger,高AsInteger)AsIntegerDimaAsIntegerIf直径>=8Thena=2*直径Elsea=15EndIfIf构件="箍筋"ThenWL=(宽+高)*2+aEndIfIf构件="拉筋"ThenWL=宽+aEndIfIf构件<>"箍筋"And构件<>"拉筋"ThenWL=0EndIfEndFunctionPublicFunctionWUL(构件AsString,级别AsInteger,直径AsInteger,高AsInteger)AsIntegerDimaAsInteger,bAsIntegerIf级别=1Thenb=1Elseb=0EndIfIf直径>=8Thena=2*直径Elsea=15EndIfIf构件="插口"ThenWUL=高*2+a*bElseWUL=0EndIfEndFunctionPublicFunctionWMX(构件AsString,NAsInteger)AsIntegerDimaAsInteger,bAsIntegerIf构件="腰筋G"Or构件="腰筋N"Or构件="P"Or构件="O"Or构件="拉筋"Or构件="箍筋"Or构件="通长"Or构件="架立筋"Thena=0:b=0EndIfIf构件="A"Or构件="P1"Or构件="长封"Or构件="短封"Or构件="悬挑一层"Or构件="悬挑二层"Or构件="插口"Thena=1:b=0EndIfIf构件="B"Or构件="P2"Thena=0:b=1EndIfIf构件=""Or构件="吊筋"Or构件="鸭筋"Or构件="马凳"Thena=1:b=1EndIfIfN=0ThenWMX=aElseWMX=bEndIfEndFunctionPublicFunctionWMC(板厚AsInteger,直径AsInteger,宽度AsInteger,梁高AsInteger,悬挑底长AsInteger,标记AsString,锚固AsInteger,构件类别AsString,特殊类别AsString)AsVariantIf特殊类别="板"And标记=""ThenIf直径>=板厚-4ThenWMC=板厚-4ElseWMC=直径EndIfEndIfIf特殊类别="外挑板"And标记=""ThenWMC=Round((板厚+7)/2)EndIfIf特殊类别="101-3筏板"And标记=""ThenWMC=1.5*直径EndIfIf构件类别="L"And标记=""ThenIf特殊类别="吊筋"And标记=""ThenWMC=2*直径EndIfIf特殊类别="悬挑"And标记=""ThenWMC=MAX(直径,悬挑底长,0,0)EndIfIf特殊类别="插口"And标记=""ThenWMC=宽度-Sheets("参数表").Range("F3")EndIfIf特殊类别="QL"And特殊类别<>"吊筋"And特殊类别<>"悬挑"ThenWMC=0.8*锚固EndIfIf特殊类别<>"QL"And特殊类别<>"吊筋"And特殊类别<>"悬挑"And特殊类别<>"插口"ThenWMC=1.5*直径EndIfEndIfIf构件类别="L"And标记<>""ThenIf特殊类别="WKL"Or特殊类别=""ThenIfSheets("打印页").Range("J1")=0ThenWMC=梁高-5EndIfIfSheets("打印页").Range("J1")=1ThenWMC=1.7*锚固EndIfEndIfIf特殊类别="KZL"ThenWMC=梁高+锚固-5EndIfEndIfIf构件类别="Z"And标记=""ThenIfSheets("参数表").Range("K4")=1ThenWMC=Sheets("参数表").Range("I4")ElseWMC=1.2*直径EndIfEndIfIf构件类别="Z"And标记<>""ThenIfSheets("参数表").Range("O4")=1ThenWMC=Sheets("参数表").Range("M4")EndIfIfSheets("参数表").Range("O4")=0ThenWMC=1.2*直径EndIfEndIfIf构件类别="Q"And标记=""ThenIfSheets("参数表").Range("K4")=1ThenWMC=Sheets("参数表").Range("I4")ElseWMC=1.2*直径EndIfEndIfIf构件类别="Q"And标记<>""ThenIfSheets("参数表").Range("O4")=1ThenWMC=Sheets("参数表").Range("M4")EndIfIfSheets("参数表").Range("O4")=0ThenWMC=1.2*直径EndIfEndIfWMC=Round(WMC,0)EndFunctionPublicFunctionSOUS(构件类别AsString)AsVariantDima1AsVariant,b1AsVariant,C1AsVariant,D1AsVariantIf构件类别=""Then构件类别=0Else构件类别=构件类别EndIfa1=InStrRev(构件类别,"L",Len(构件类别)):b1=InStrRev(构件类别,"Z",Len(构件类别)):C1=InStrRev(构件类别,"B",Len(构件类别)):D1=InStrRev(构件类别,"Q",Len(构件类别))IfMAX(a1,b1,C1,D1)>0ThenSOUS=Mid(构件类别,1,MAX(a1,b1,C1,D1))ElseSOUS=""EndIfEndFunctionPublicFunctionSOS(构件类别AsString)AsVariantDima1AsVariant,b1AsVariant,C1AsVariant,D1AsVariantIf构件类别=""Then构件类别=0Else构件类别=构件类别EndIfa1=InStrRev(构件类别,"L",Len(构件类别)):b1=InStrRev(构件类别,"Z",Len(构件类别)):C1=InStrRev(构件类别,"B",Len(构件类别)):D1=InStrRev(构件类别,"Q",Len(构件类别))IfMAX(a1,b1,C1,D1)=a1ThenSOS="L"EndIfIfMAX(a1,b1,C1,D1)=b1ThenSOS="Z"EndIfIfMAX(a1,b1,C1,D1)=C1ThenSOS="B"EndIfIfMAX(a1,b1,C1,D1)=D1ThenSOS="Q"EndIfIfMAX(a1,b1,C1,D1)=0ThenSOS=""EndIfEndFunctionPublicFunctionMAX(aAsVariant,bAsVariant,CAsVariant,DAsVariant)AsVariantDimxAsInteger,YAsIntegerIfa>=bThenx=aElsex=bEndIfIfC>=DThenY=CElseY=DEndIfIfx>=YThenMAX=xElseMAX=YEndIfEndFunctionPublicFunctionXTJ(LAsSingle,直径AsInteger,悬挑底长AsInteger,构件类别AsString,梁高AsInteger,NAsInteger)AsVariantDimaAsSingle,hAsInteger,λ1AsInteger,L1AsSingle,bAsSingle,PAsIntegerIf构件类别="悬挑二层"Thenh=5:λ1=1EndIfIf构件类别="悬挑一层"Or构件类别="吊筋"Or构件类别="鸭筋"Thenh=0:λ1=1EndIfIf构件类别<>"悬挑二层"And构件类别<>"悬挑一层"And构件类别<>"吊筋"And构件类别<>"鸭筋"Thenh=0:λ1=0EndIfP=MAX(悬挑底长,直径,0,0)*λ1If梁高>80Thena=λ1*Int(2*(梁高-h-Sheets("参数表").Range("G3"))/Sqr(3)):L1=λ1*(L-悬挑底长-(梁高-h-Sheets("参数表").Range("G3"))/Sqr(3))Elsea=λ1*Int(Sqr(2)*(梁高-h-Sheets("参数表").Range("G3"))):L1=λ1*(L-悬挑底长-(梁高-h-Sheets("参数表").Range("G3")))EndIfIf构件类别="吊筋"Or构件类别="鸭筋"ThenL1=0:P=2*直径ElseL1=L1:P=PEndIfIfN=0ThenXTJ=PEndIfIfN=1ThenXTJ=MAX(a,0,0,0)EndIfIfN=2ThenXTJ=L1EndIfEndFunctionPublicFunctionJMG(LAsInteger,构件类别AsString,锚固AsInteger)AsVariantIf构件类别="P"Or构件类别="P1"Or构件类别="P2"Or构件类别="腰筋G"Or构件类别="腰筋N"ThenJMG=LEndIfIf构件类别="架立筋"Or构件类别=""Or构件类别="吊筋"Or构件类别="鸭筋"ThenJMG=LEndIfIf构件类别="A"Or构件类别="B"ThenJMG=L+锚固EndIfIf构件类别="O"ThenJMG=L+锚固*2EndIfEndFunctionPublicFunctionLJM(构件类别AsString,NAsInteger)AsVariantDimaAsVariant,bAsVariant,CAsVariant,DAsVariant,EAsVariant,FAsVariant,F1AsVariant,b1AsVariant,b2AsVariantIf构件类别=""Then构件类别=0EndIfF=InStrRev(构件类别,"(",Len(构件类别))F1=InStrRev(构件类别,"件",Len(构件类别))a=InStrRev(构件类别,")",Len(构件类别))b=InStrRev(构件类别,"x",Len(构件类别))C=InStrRev(构件类别,"X",Len(构件类别))D=InStrRev(构件类别,"*",Len(构件类别))E=InStrRev(构件类别,"×",Len(构件类别))If构件类别<>""ThenIfb>0Thenb=bElseb=MAX(b,C,D,E)EndIfIfN=0ThenIfb=0ThenLJM=0ElseLJM=Mid(构件类别,a+1,b-a-1)/10EndIfEndIfIfN=1ThenIfb=0ThenLJM=0ElseLJM=Mid(构件类别,b+1,Len(构件类别)-b)/10EndIfEndIfIfN=2ThenIfb=0ThenLJM=0ElseLJM=Mid(构件类别,F+1,F1-F-1)EndIfEndIfElseLJM=0EndIfEndFunctionPublicFunctionGJS(抗震级别AsSingle,层数AsString,间距AsVariant,构件AsString,LAsSingle,LZ高AsSingle,NAsSin