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 FunctionMax 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 FunctionSeismic 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 FunctionInelastic 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