The functions discussed in Part 1 are outlined below.
Click here to go to the GitHub repository with the full code and some extras. Or simply cut and paste from this page.
In Part 3 we’ll cover an example and uses, like creating a plot of the full design spectrum like the examples shown below in just a few easy steps, the rest is up to you: –



Onto the various VBA functions…
Spectral shape factor – Ch(T)
Function Loading_C_h_T(T_1 As Double, Site_subsoil_class As String, Optional ESM_case As Boolean = True, _ Optional D_subsoil_interpolate As Boolean = False, Optional T_site As Double = 1.5) 'Function to calculate the spectral shape factor (Ch(T)) based on site subsoil soil class (A/B/C/D/E) type 'and first mode period '________________________________________________________________________________________________________________ 'USAGE '________________________________________________________________________________________________________________ '=Loading_C_h_T(T_1,Site_subsoil_class,ESM_case,D_subsoil_interpolate,T_site) 'T_1 = FIRST MODE PERIOD 'Site_subsoil_class = SITE SUBSOIL CLASS, i.e. A/B/C/D/E (ENTERED AS A STRING) 'OPTIONAL - ESM_case is optional (ESM = Equivalent Static Method), when set to false the Response Spectrum Method (RSM) ' shape factor will be calculated. 'OPTIONAL - D_subsoil_interpolation is optional - TRUE/FALSE - consider interpolation for class D soils 'OPTIONAL - T_site is optional - Site period when required for considering interpolation for class D soils ' (note default value of 1.5 seconds equates to no interpolation) '________________________________________________________________________________________________________________ 'set entry to uppercase Site_subsoil_class = UCase(Site_subsoil_class) Dim C_h_T_shallow 'test for ESM vs RSM spectrum analysis If ESM_case = True Then 'ESM case 'case based on soil type Select Case Site_subsoil_class 'values/formulas taken from the NZS1170.5 commentary Case "A", "B" If T_1 >= 0 And T_1 < 0.4 Then Loading_C_h_T = 1.89 If T_1 >= 0.4 And T_1 <= 1.5 Then Loading_C_h_T = 1.6 * (0.5 / T_1) ^ 0.75 If T_1 > 1.5 And T_1 <= 3 Then Loading_C_h_T = 1.05 / T_1 If T_1 > 3 Then Loading_C_h_T = 3.15 / T_1 ^ 2 'additional check because at just above T=0.4 the value spikes above 1.89, this smooths out this blip If Loading_C_h_T >= 1.89 Then Loading_C_h_T = 1.89 Case "C" If T_1 >= 0 And T_1 < 0.4 Then Loading_C_h_T = 2.36 If T_1 >= 0.4 And T_1 <= 1.5 Then Loading_C_h_T = 2 * (0.5 / T_1) ^ 0.75 If T_1 > 1.5 And T_1 <= 3 Then Loading_C_h_T = 1.32 / T_1 If T_1 > 3 Then Loading_C_h_T = 3.96 / T_1 ^ 2 'additional check because at just above T=0.4 the value spikes above 2.36, this smooths out this blip If Loading_C_h_T > 2.36 Then Loading_C_h_T = 2.36 Case "D" 'check if interpolation can be adopted If D_subsoil_interpolate = True And T_site >= 0.6 And T_site <= 1.5 And T_1 >= 0.1 Then 'interpolate between C & D site subsoil classes considered 'min T=0.4 seconds for ESM case with interpolation If T_1 < 0.4 Then T_1 = 0.4 'Class C values for NITH case If T_1 >= 0.4 And T_1 <= 1.5 Then C_h_T_shallow = 2 * (0.5 / T_1) ^ 0.75 If T_1 > 1.5 And T_1 <= 3 Then C_h_T_shallow = 1.32 / T_1 If T_1 > 3 Then C_h_T_shallow = 3.96 / T_1 ^ 2 Loading_C_h_T = C_h_T_shallow * (1 + 0.5 * (T_site - 0.25)) 'additional check because at just above T=0.4 the value spikes above 3, this smooths out this blip If Loading_C_h_T > 3 Then Loading_C_h_T = 3 Else 'No interpolate considered, or able to be considered If T_1 >= 0 And T_1 < 0.56 Then Loading_C_h_T = 3 If T_1 >= 0.56 And T_1 <= 1.5 Then Loading_C_h_T = 2.4 * (0.75 / T_1) ^ 0.75 If T_1 > 1.5 And T_1 <= 3 Then Loading_C_h_T = 2.14 / T_1 If T_1 > 3 Then Loading_C_h_T = 6.42 / T_1 ^ 2 'additional check because at just below T=0.56 the value steps abruptly, this smooths out this blip If Loading_C_h_T > 3 Then Loading_C_h_T = 3 End If Case "E" If T_1 >= 0 And T_1 < 1 Then Loading_C_h_T = 3 If T_1 >= 1 And T_1 <= 1.5 Then Loading_C_h_T = 3 / T_1 ^ 0.75 If T_1 > 1.5 And T_1 <= 3 Then Loading_C_h_T = 3.32 / T_1 If T_1 > 3 Then Loading_C_h_T = 9.96 / T_1 ^ 2 Case Else 'error? End Select Else 'RSM case 'case based on soil type Select Case Site_subsoil_class 'values/formulas taken from the NZS1170.5 commentary Case "A", "B" If T_1 >= 0 And T_1 < 0.1 Then Loading_C_h_T = 1 + 1.35 * (T_1 / 0.1) If T_1 >= 0.1 And T_1 < 0.3 Then Loading_C_h_T = 2.35 If T_1 >= 0.3 And T_1 <= 1.5 Then Loading_C_h_T = 1.6 * (0.5 / T_1) ^ 0.75 If T_1 > 1.5 And T_1 <= 3 Then Loading_C_h_T = 1.05 / T_1 If T_1 > 3 Then Loading_C_h_T = 3.15 / T_1 ^ 2 Case "C" If T_1 >= 0 And T_1 < 0.1 Then Loading_C_h_T = 1.33 + 1.6 * (T_1 / 0.1) If T_1 >= 0.1 And T_1 < 0.3 Then Loading_C_h_T = 2.93 If T_1 >= 0.3 And T_1 <= 1.5 Then Loading_C_h_T = 2 * (0.5 / T_1) ^ 0.75 If T_1 > 1.5 And T_1 <= 3 Then Loading_C_h_T = 1.32 / T_1 If T_1 > 3 Then Loading_C_h_T = 3.96 / T_1 ^ 2 'additional check because at just above T=0.3 the value spikes above 2.93, this smooths out this blip If Loading_C_h_T > 2.93 Then Loading_C_h_T = 2.93 Case "D" 'check if interpolation can be adopted If D_subsoil_interpolate = True And T_site >= 0.6 And T_site <= 1.5 And T_1 >= 0.1 Then 'interpolate between C & D site subsoil classes considered 'Class C values for NITH case If T_1 >= 0.1 And T_1 < 0.3 Then C_h_T_shallow = 2.93 If T_1 >= 0.3 And T_1 <= 1.5 Then C_h_T_shallow = 2 * (0.5 / T_1) ^ 0.75 If T_1 > 1.5 And T_1 <= 3 Then C_h_T_shallow = 1.32 / T_1 If T_1 > 3 Then C_h_T_shallow = 3.96 / T_1 ^ 2 'additional check because at just above T=0.3 the value spikes above 2.93, this smooths out this blip If C_h_T_shallow > 2.93 Then C_h_T_shallow = 2.93 Loading_C_h_T = C_h_T_shallow * (1 + 0.5 * (T_site - 0.25)) If Loading_C_h_T > 3 Then Loading_C_h_T = 3 Else 'No interpolate considered, or able to be considered If T_1 >= 0 And T_1 < 0.1 Then Loading_C_h_T = 1.12 + 1.88 * (T_1 / 0.1) If T_1 >= 0.1 And T_1 < 0.56 Then Loading_C_h_T = 3 If T_1 >= 0.56 And T_1 <= 1.5 Then Loading_C_h_T = 2.4 * (0.75 / T_1) ^ 0.75 If T_1 > 1.5 And T_1 <= 3 Then Loading_C_h_T = 2.14 / T_1 If T_1 > 3 Then Loading_C_h_T = 6.42 / T_1 ^ 2 End If Case "E" If T_1 >= 0 And T_1 < 0.1 Then Loading_C_h_T = 1.12 + 1.88 * (T_1 / 0.1) If T_1 >= 0.1 And T_1 < 1 Then Loading_C_h_T = 3 If T_1 >= 1 And T_1 <= 1.5 Then Loading_C_h_T = 3 / T_1 ^ 0.75 If T_1 > 1.5 And T_1 <= 3 Then Loading_C_h_T = 3.32 / T_1 If T_1 > 3 Then Loading_C_h_T = 9.96 / T_1 ^ 2 Case Else 'error? End Select End If End Function
Near fault factor – N(D,T)
Function Loading_N_T_D(T_1 As Double, Fault_distance As Variant, Return_period_factor As Double) 'Function to calculate N(T,D), the near fault factor 'calculation is based on R_u or R_s (Return Period Factor) rather than the probability of exceedance as its 'easier to follow when written like this 'N(T,D) = 1.0 for R <= 0.75 'N(T,D) = varies for R > 0.75 '________________________________________________________________________________________________________________ 'USAGE '________________________________________________________________________________________________________________ '=Loading_N_T_D(T_1,Fault_distance,Return_period_factor) 'T_1 = FIRST MODE PERIOD 'Fault_distance = THE SHORTEST DISTANCE (IN kM's) FROM THE SITE TO THE NEAREST FAULT LISTED IN TABLE 3.6 OF NZS1170.5 ' IF NOT RELEVANT USE "N/A" OR A NUMBER >= 20 'Return_period_factor = RETURN PERIOD FACTOR Ru OR Rs '________________________________________________________________________________________________________________ If Return_period_factor <= 0.75 Then Loading_N_T_D = 1 If Return_period_factor > 0.75 Then If Fault_distance <= 2 Then Loading_N_T_D = Loading_N_max_T(T_1) If Fault_distance = "N/A" Or Fault_distance > 20 Then Loading_N_T_D = 1 If Fault_distance > 2 And Fault_distance <= 20 Then Loading_N_T_D = 1 + (Loading_N_max_T(T_1) - 1) * (20 - Fault_distance) / 18 End If End Function
Max near fault factor – N(D,T)MAX
Function Loading_N_max_T(T_1 As Double) 'Function to calculate Nmax(T), the maximum near fault factor '________________________________________________________________________________________________________________ 'USAGE '________________________________________________________________________________________________________________ '=Loading_N_max_T(T_1) 'T_1 = FIRST MODE PERIOD '________________________________________________________________________________________________________________ If T_1 <= 1.5 Then Loading_N_max_T = 1 If T_1 >= 5 Then Loading_N_max_T = 1.72 If T_1 > 1.5 And T_1 <= 4 Then Loading_N_max_T = 0.24 * T_1 + 0.64 If T_1 > 4 And T_1 < 5 Then Loading_N_max_T = 0.12 * T_1 + 1.12 End Function
Elastic site spectra – C(T)
Function Loading_C_T(T_1 As Double, Site_subsoil_class As String, Hazard_factor As Double, Return_period_factor As Double, _ Fault_distance As Variant, Optional ESM_case As Boolean = True, _ Optional D_subsoil_interpolate As Boolean = False, Optional T_site As Double = 1.5) 'Function to calculate C(T), the elastic site spectra '________________________________________________________________________________________________________________ 'USAGE '________________________________________________________________________________________________________________ '=Loading_C_d_T(T_1,Site_subsoil_class,Hazard_factor,Return_period_factor,Fault_distance,ESM_case,D_subsoil_interpolate,T_site) 'T_1 = FIRST MODE PERIOD 'Site_subsoil_class = SITE SUBSOIL CLASS, i.e. A/B/C/D/E (ENTERED AS A STRING) 'Hazard_factor = HAZARD FACTOR Z 'Return_period_factor = RETURN PERIOD FACTOR Ru OR Rs 'Fault_distance = THE SHORTEST DISTANCE (IN kM's) FROM THE SITE TO THE NEAREST FAULT LISTED IN TABLE 3.6 OF NZS1170.5 ' IF NOT RELEVANT USE "N/A" OR A NUMBER >= 20 'OPTIONAL - ESM_case is optional (ESM = Equivalent Static Method), when set to false the Response Spectrum Method (RSM) ' shape factor will be calculated. 'OPTIONAL - D_subsoil_interpolation is optional - TRUE/FALSE - consider interpolation for class D soils 'OPTIONAL - T_site is optional - Site period when required for considering interpolation for class D soils ' (note default value of 1.5 seconds equates to no interpolation) '________________________________________________________________________________________________________________ Dim C_h_T Dim N_T_D Dim Z_R_product 'Spectral shape factor C_h_T = Loading_C_h_T(T_1, Site_subsoil_class, ESM_case, D_subsoil_interpolate, T_site) 'Near fault factor N_T_D = Loading_N_T_D(T_1, Fault_distance, Return_period_factor) 'check if Z x R > 0.7, if so limit product to 0.7 for calculating C_T If Hazard_factor * Return_period_factor > 0.7 Then Z_R_product = 0.7 Else Z_R_product = Hazard_factor * Return_period_factor End If 'Elastic site spectra Loading_C_T = C_h_T * Z_R_product * N_T_D End Function
Seismic coefficient – Cd(T)
Function Loading_C_d_T(T_1 As Variant, Site_subsoil_class As String, Hazard_factor As Double, Return_period_factor As Double, _ Fault_distance As Variant, mu As Double, S_p As Double, Optional ESM_case As Boolean = True, _ Optional D_subsoil_interpolate As Boolean = False, Optional T_site As Double = 1.5) As Variant 'Function to calculate C_d(T), the seismic load coefficient for a single period T, or series of T periods arranged in a column range '________________________________________________________________________________________________________________ 'USAGE '________________________________________________________________________________________________________________ '=Loading_C_d_T(T_1,Site_subsoil_class,Hazard_factor,Return_period_factor,Fault_distance,mu,S_p,ESM_case,D_subsoil_interpolate,T_site) 'T_1 = FIRST MODE PERIOD 'Site_subsoil_class = SITE SUBSOIL CLASS, i.e. A/B/C/D/E (ENTERED AS A STRING) 'Hazard_factor = HAZARD FACTOR Z 'Return_period_factor = RETURN PERIOD FACTOR Ru OR Rs 'Fault_distance = THE SHORTEST DISTANCE (IN kM's) FROM THE SITE TO THE NEAREST FAULT LISTED IN TABLE 3.6 OF NZS1170.5 ' IF NOT RELEVANT USE "N/A" OR A NUMBER >= 20 'mu = DUCTILITY 'S_p = STRUCTURAL PERFORMANCE FACTOR 'OPTIONAL - ESM_case is optional (ESM = Equivalent Static Method), when set to false the Response Spectrum Method (RSM) ' shape factor will be calculated. 'OPTIONAL - D_subsoil_interpolation is optional - TRUE/FALSE - consider interpolation for class D soils 'OPTIONAL - T_site is optional - Site period when required for considering interpolation for class D soils ' (note default value of 1.5 seconds equates to no interpolation) '________________________________________________________________________________________________________________ Dim arr_temp As Double Dim k As Integer 'convert to array 'check if single value provided (i.e. one cell), and convert to 2D array If T_1.Rows.count = 1 Then T_1 = Array(T_1.Value2) arr_temp = T_1(0) ReDim T_1(1 To 1, 1 To 1) T_1(1, 1) = arr_temp Else 'convert to 2D array T_1 = T_1.Value2 End If Dim C_d_T ReDim C_d_T(LBound(T_1) To UBound(T_1), 1 To 1) For k = LBound(T_1) To UBound(T_1) C_d_T(k, 1) = Loading_C_d_T_intermediate(CDbl(T_1(k, 1)), Site_subsoil_class, Hazard_factor, Return_period_factor, _ Fault_distance, mu, S_p, ESM_case, D_subsoil_interpolate, T_site) Next k 'return results Loading_C_d_T = C_d_T End Function Private Function Loading_C_d_T_intermediate(T_1 As Double, Site_subsoil_class As String, Hazard_factor As Double, Return_period_factor As Double, _ Fault_distance As Variant, mu As Double, S_p As Double, Optional ESM_case As Boolean = True, _ Optional D_subsoil_interpolate As Boolean = False, Optional T_site As Double = 1.5) 'Function to calculate C_d(T), the seismic load coefficient for a single period T '________________________________________________________________________________________________________________ 'USAGE '________________________________________________________________________________________________________________ '=Loading_C_d_T_intermediate(T_1,Site_subsoil_class,Hazard_factor,Return_period_factor,Fault_distance,mu,S_p,ESM_case,D_subsoil_interpolate,T_site) 'T_1 = FIRST MODE PERIOD 'Site_subsoil_class = SITE SUBSOIL CLASS, i.e. A/B/C/D/E (ENTERED AS A STRING) 'Hazard_factor = HAZARD FACTOR Z 'Return_period_factor = RETURN PERIOD FACTOR Ru OR Rs 'Fault_distance = THE SHORTEST DISTANCE (IN kM's) FROM THE SITE TO THE NEAREST FAULT LISTED IN TABLE 3.6 OF NZS1170.5 ' IF NOT RELEVANT USE "N/A" OR A NUMBER >= 20 'mu = DUCTILITY 'S_p = STRUCTURAL PERFORMANCE FACTOR 'OPTIONAL - ESM_case is optional (ESM = Equivalent Static Method), when set to false the Response Spectrum Method (RSM) ' shape factor will be calculated. 'OPTIONAL - D_subsoil_interpolation is optional - TRUE/FALSE - consider interpolation for class D soils 'OPTIONAL - T_site is optional - Site period when required for considering interpolation for class D soils ' (note default value of 1.5 seconds equates to no interpolation) '________________________________________________________________________________________________________________ Dim C_T Dim k_mu Dim C_d_T Dim Z_R_product 'check if Z x R > 0.7, if so limit product to 0.7 for calculating C_T If Hazard_factor * Return_period_factor > 0.7 Then Z_R_product = 0.7 Else Z_R_product = Hazard_factor * Return_period_factor End If 'Elastic site spectra C_T = Loading_C_T(T_1, Site_subsoil_class, Hazard_factor, Return_period_factor, _ Fault_distance, ESM_case, D_subsoil_interpolate, T_site) 'Inelastic spectrum scaling factor k_mu = Loading_k_mu(T_1, Site_subsoil_class, mu) 'Horizontal design action coefficient C_d_T = C_T * S_p / k_mu 'Check for minimum level design coefficient for ULS/SLS cases If C_d_T < Z_R_product / 20 + 0.02 * Return_period_factor Then C_d_T = Z_R_product / 20 + 0.02 * Return_period_factor End If If C_d_T < 0.03 * Return_period_factor Then C_d_T = 0.03 * Return_period_factor End If 'return results Loading_C_d_T_intermediate = C_d_T End Function
Inelastic spectrum scaling factor – kμ
Function Loading_k_mu(T_1 As Double, Site_subsoil_class As String, mu As Double) 'Function to calculate the inelastic spectrum scaling factor '________________________________________________________________________________________________________________ 'USAGE '________________________________________________________________________________________________________________ '=Loading_k_mu(T_1,Site_subsoil_class,mu) 'T_1 = FIRST MODE PERIOD 'Site_subsoil_class = SITE SUBSOIL CLASS, i.e. A/B/C/D/E (ENTERED AS A STRING) 'mu = DUCTILITY '________________________________________________________________________________________________________________ 'set entry to uppercase Site_subsoil_class = UCase(Site_subsoil_class) 'calculate based on minimum of T_1 = 0.4 as per NZS110.5 CL5.2.1.1 If T_1 < 0.4 Then T_1 = 0.4 Select Case Site_subsoil_class Case "A", "B", "C", "D" If T_1 >= 0.7 Then Loading_k_mu = mu Else Loading_k_mu = (mu - 1) * T_1 / 0.7 + 1 End If Case "E" If T_1 >= 1 Or mu < 1.5 Then Loading_k_mu = mu Else Loading_k_mu = (mu - 1.5) * T_1 + 1.5 End If End Select End Function