My Excel 2010 do not have the built-in SORT worksheet function, so I used "System.Collections.ArrayList" to sort the "steps" array and needed to transpose the array at the end, without making any other changes.
Hence, it seems working on Excel 2010 (sample file is attached)
Function Loading_generate_period_range(period_range_limit As Double, period_step As Double, Optional T_site As Double = 1.5)
'Function to return array of period values at all points of interest for calculating values at all transition points
'and a distributed time step at 'period_step' intervals
'______________________________________________________________________________________________________________
'USAGE
'______________________________________________________________________________________________________________
'=Loading_generate_period_range(period_range_limit,period_step)
'period_range_limit = upper limit of generated period sequence, starting at zero and ending at 'period_range_limit',
' and incoporating points of interest where variaous factors result in transition points in the
' resulting spectrum. The maximum step is taken as 'period_step' in the generated period sequence
'period_step = period increment in the generated period sequence
' =Loading_generate_period_range(2,0.2) would return
' 0
' 0
' 0.1
' 0.1
' 0.2
' 0.3
' 0.4
' 0.4
' 0.56
' 0.6
' 0.8
' 1
' 1
' 1.2
' 1.4
' 1.5
' 1.6
' 1.8
' 2
' 2
' 2
'NOTE - there will be some duplicates as shown above, but this does not impact on plotting spectrum based on
'resulting period range
'______________________________________________________________________________________________________________
Dim T_1_arr
Dim num_steps As Long
Dim num_points_of_interest As Long
Dim i
Dim steps
num_steps = period_range_limit / period_step + 1
num_points_of_interest = 12
'resize results array for all regular intervals and points of interest
ReDim steps(1 To num_steps + num_points_of_interest - 1)
'create array of 'points of interest' where transitions or abrupt steps of various factors occurs
T_1_arr = Array(0, 0.1 - 0.00000000001, 0.1, 0.3, 0.4, 0.56, 1, 1.5, 3, 4, 5, _
1 / (((3 / (1 + 0.5 * (T_site - 0.25))) / 2) ^ (1 / 0.75)) * 0.5)
'populate regular period intervals starting at zero and max of 'period_range_limit'
steps(1) = 0
For i = 2 To num_steps - 1
steps(i) = steps(i - 1) + period_step
Next i
steps(num_steps) = period_range_limit
'populate points of interest into results array, limiting to max of 'period_range_limit'
For i = 0 To num_points_of_interest - 1
If T_1_arr(i) > period_range_limit Then
steps(num_steps + i) = period_range_limit
Else
steps(num_steps + i) = T_1_arr(i)
End If
Next i
'transpose array to single column of results
steps = WorksheetFunction.Transpose(steps)
'sort results into numerical order
' ********************************************************************************
Dim myList
Set myList = CreateObject("System.Collections.ArrayList")
For i = 1 To UBound(steps)
myList.Add steps(i, 1)
Next
myList.Sort
'return results
steps = WorksheetFunction.Transpose(myList.toArray)
' ********************************************************************************
'return results
Loading_generate_period_range = steps
End Function
Bookmarks