钢结构设计用：EXCEL表格自定义函数

Option Explicit '提取型钢截面信息 [工型截面、圆管截面、箱型截面] ' '【函数名】：★Gins ' Public Function ★Gins(Section, cx As String) As Single Dim dd As Single Select Case Left(Section, 1) Case Is = "H" Call 工型截面1(Section, cx, dd) Case Is = "D" Call 圆管截面2(Section, cx, dd) Case Is = "口" Call 箱型截面3(Section, cx, dd) Case Is <> "口" dd = "" End Select ★Gins = dd End Function Sub 工型截面1(Section, cx, dd) Dim h, b, tw, tf As Single h = Mid(Section, 2, InStr(Section, "x") - 2) b = Mid(Section, InStr(Section, "x") + 1, InStr(InStr(Section, "x") + 1, Section, "x") - InStr(Section, "x") - 1) tw = Mid(Section, InStr(InStr(Section, "x") + 1, Section, "x") + 1, InStr(InStr(InStr(Section, "x") + 1, Section, "x") + 1, Section, "x") - InStr(InStr(Section, "x") + 1, Section, "x") - 1) tf = Mid(Section, InStr(InStr(InStr(Section, "x") + 1, Section, "x") + 1, Section, "x") + 1, 3) Select Case cx Case Is = "H": dd = h Case Is = "B": dd = b Case Is = "tw": dd = tw Case Is = "tf": dd = tf Case Is = "A": dd = b * h - (h - 2 * tf) * (b - tw) Case Is = "Ix": dd = b * h ^ 3 / 12 - (b - tw) * (h - 2 * tf) ^ 3 / 12 Case Is = "ix": dd = ((b * h ^ 3 / 12 - (b - tw) * (h - 2 * tf) ^ 3 / 12) / (b * h - (h - 2 * tf) * (b - tw))) ^ 0.5 Case Is = "Iy": dd = (2 * tf) * b ^ 3 / 12 + (h - 2 * tf) * tw ^ 3 / 12 Case Is = "iy": dd = (((2 * tf) * b ^ 3 / 12 + (h - 2 * tf) * tw ^ 3 / 12) / (b * h - (h - 2 * tf) * (b - tw))) ^ 0.5 Case Is <> "iy": dd = "" End Select End Sub Sub 圆管截面2(Section, cx, dd) Dim d, t As Single d = Mid(Section, 2, InStr(Section, "x") - 2) t = Mid(Section, InStr(Section, "x") + 1, 3) Select Case cx Case Is = "D": dd = d Case Is = "t": dd = t Case Is = "A": dd = Application.Pi() * (d ^ 2 - (d - 2 * t) ^ 2) / 4 Case Is = "Ix": dd = Application.Pi() * (d ^ 4 - (d - 2 * t) ^ 4) / 64 Case Is = "ix": dd = (d ^ 2 + (d - 2 * t) ^ 2) ^ 0.5 / 4 Case Is <> "ix": dd = "" End Select End Sub Sub 箱型截面3(Section, cx, dd) Dim h, b, th, tb As Single h = Mid(Section, 2, InStr(Section, "x") - 2) b = Mid(Section, InStr(Section, "x") + 1, InStr(InStr(Section, "x") + 1, Section, "x") - InStr(Section, "x") - 1) th = Mid(Section, InStr(InStr(Section, "x") + 1, Section, "x") + 1, InStr(InStr(InStr(Section, "x") + 1, Section, "x") + 1, Section, "x") - InStr(InStr(Section, "x") + 1, Section, "x") - 1) tb = Mid(Section, InStr(InStr(InStr(Section, "x") + 1, Section, "x") + 1, Section, "x") + 1, 3) Select Case cx Case Is = "H": dd = h Case Is = "B": dd = b Case Is = "tH": dd = th Case Is = "tB": dd = tb Case Is = "A": dd = b * h - (b - 2 * th) * (h - 2 * tb) Case Is = "Ix": dd = b * h ^ 3 / 12 - (b - 2 * th) * (h - 2 * tb) ^ 3 / 12 Case Is = "ix": dd = ((b * h ^ 3 / 12 - (b - 2 * th) * (h - 2 * tb) ^ 3 / 12) / (b * h - (b - 2 * th) * (h - 2 * tb))) ^ 0.5 Case Is = "Iy": dd = b ^ 3 * h / 12 - (b - 2 * th) ^ 3 * (h - 2 * tb) / 12 Case Is = "iy": dd = ((b ^ 3 * h / 12 - (b - 2 * th) ^ 3 * (h - 2 * tb) / 12) / (b * h - (b - 2 * th) * (h - 2 * tb))) ^ 0.5 Case Is <> "iy": dd = "" End Select End Sub '《高层民用建筑钢结构技术规程》JGJ 99-2015,表4.2.1 “设计用钢材强度值（N/mm2）” ' '【函数名】：★GStr ' Public Function ★GStr(Stp, Str_type As String, B_thick As Integer) As Integer Dim arr_fu, arr_fy, arr_f, arr_fv, arr_fce As Variant, i, j, k As Integer '建立钢材各类强度值数组 arr_fu = Array(370, 370, 370, 370, 370, 470, 470, 470, 470, 470, 490, 490, 490, 490, 490, 520, 520, 520, 520, 520, 490, 490) arr_fy = Array(235, 225, 215, 215, 215, 345, 335, 325, 315, 305, 390, 370, 350, 330, 330, 420, 400, 380, 360, 360, 345, 335) arr_f = Array(215, 205, 200, 200, 200, 305, 295, 290, 280, 270, 345, 330, 310, 295, 295, 375, 355, 320, 305, 305, 325, 300) arr_fv = Array(125, 120, 115, 115, 115, 175, 170, 165, 160, 155, 200, 190, 180, 170, 170, 215, 205, 185, 175, 175, 190, 175) arr_fce = Array(320, 320, 320, 320, 320, 400, 400, 400, 400, 400, 415, 415, 415, 415, 415, 440, 440, 440, 440, 440, 415, 415) '通过钢材牌号、钢材厚度计算其在强度值数组中的定位 [调试 Debug.Print] i = IIf(Stp = "Q235", 0, IIf(Stp = "Q345", 5, IIf(Stp = "Q390", 10, IIf(Stp = "Q420", 15, IIf(Stp = "Q345GJ", 20, "?"))))) If Stp = "Q345GJ" Then j = IIf(B_thick > 16 And B_thick <= 50, 1, IIf(B_thick > 50 And B_thick <= 100, 2, "?")) Else j = IIf(B_thick <= 16, 1, IIf(B_thick > 16 And B_thick <= 40, 2, IIf(B_thick > 40 And B_thick <= 63, 3, IIf(B_thick > 63 And B_thick <= 80, 4, IIf(B_thick > 80 And B_thick <= 100, 5, "?"))))) End If '通过计算的定位获得其在强度值数组中的单元值 Select Case Str_type Case Is = "fu": k = arr_fu(i + j - 1) Case Is = "fy": k = arr_fy(i + j - 1) Case Is = "f": k = arr_f(i + j - 1) Case Is = "fv": k = arr_fv(i + j - 1) Case Is = "fce": k = arr_fce(i + j - 1) Case Is <> "fce": k = "" End Select ★GStr = k End Function '《高层民用建筑钢结构技术规程》JGJ 99-2015,表4.2.4 “设计用焊缝的强度值（N/mm2）” ' '【函数名】：★HStr ' Public Function ★HStr(Stp, Str_type As String, hf As Integer) As Integer Dim arr_fu, arr_fwc, arr_fwt12, arr_fwt3, arr_fwv, arr_fwt As Variant, i, j, k As Integer '建立钢材各类强度值数组 arr_fu = Array(370, 370, 370, 370, 370, 470, 470, 470, 470, 470, 490, 490, 490, 490, 490, 520, 520, 520, 520, 520, 490, 490) arr_fwc = Array(215, 205, 200, 200, 200, 305, 295, 290, 280, 270, 345, 330, 310, 295, 295, 375, 355, 320, 305, 305, 325, 300) arr_fwt12 = Array(215, 205, 200, 200, 200, 305, 295, 290, 280, 270, 345, 330, 310, 295, 295, 375, 355, 320, 305, 305, 325, 300) arr_fwt3 = Array(185, 175, 170, 170, 170, 260, 250, 245, 240, 230, 295, 280, 265, 250, 250, 320, 300, 270, 260, 260, 275, 255) arr_fwv = Array(125, 120, 115, 115, 115, 175, 170, 165, 160, 155, 200, 190, 180, 170, 170, 215, 205, 185, 175, 175, 185, 170) arr_fwt = Array(160, 160, 160, 160, 160, 200, 200, 200, 200, 200, 220, 220, 220, 220, 220, 220, 220, 220, 220, 220, 200, 200) '通过钢材牌号、焊缝厚度计算其在强度值数组中的定位 [调试 Debug.Print] i = IIf(Stp = "Q235", 0, IIf(Stp = "Q345", 5, IIf(Stp = "Q390", 10, IIf(Stp = "Q420", 15, IIf(Stp = "Q345GJ", 20, "?"))))) If Stp = "Q345GJ" Then j = IIf(hf > 16 And hf <= 50, 1, IIf(hf > 50 And hf <= 100, 2, "?")) Else j = IIf(hf <= 16, 1, IIf(hf > 16 And hf <= 40, 2, IIf(hf > 40 And hf <= 63, 3, IIf(hf > 63 And hf <= 80, 4, IIf(hf > 80 And hf <= 100, 5, "?"))))) End If '通过计算的定位获得其在焊缝强度值数组中的单元值 Select Case Str_type Case Is = "fu": k = arr_fu(i + j - 1) Case Is = "fwc": k = arr_fwc(i + j - 1) Case Is = "fwt1-2": k = arr_fwt12(i + j - 1) Case Is = "fwt3": k = arr_fwt3(i + j - 1) Case Is = "fwv": k = arr_fwv(i + j - 1) Case Is = "Jfwt": k = arr_fwt(i + j - 1) Case Is <> "Jfwt": k = "" End Select ★HStr = k End Function '《混凝土结构设计规范》GB 50010-2010 ,4.1.3、4.1.4 “混凝土材料强度值表（N/mm2）” ' '【函数名】：☆CON_Str ' Public Function ☆CStr(Cgrade, Str_type As String) As Single Dim arr_fck, arr_ftk, arr_fc, arr_ft, arr_Ec, arr_Efc As Variant, i As Integer,