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
Hi
I tried to copy the code to the excell and run but it does not run or show up in the macro can you please share an excel sheet with this function
Hi Khaled,
They are user defined functions, not macros that you explicitly run. All I can suggest is to follow the examples as it should be clear how these functions are intended to be used.