Functions for calculating the New Zealand seismic coefficient Cd(T) and associated parameters (Part 2)

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: –

C soils, RSM spectrum
D soils, ESM spectrum with interpolation between C & D soils based on TSITE = 0.67 seconds
E soils, ESM spectrum

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

2 Comments

  1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *