| title | Partition | |
|---|---|---|
| parent | Interaction Module | |
| permalink | /tB/Modules/Interaction/Partition | |
| redirect_from |
|
|
| vba_attribution | true |
{: .no_toc }
Returns a Variant (String) labelling which of a series of equal-width numeric ranges a value falls into.
Syntax: Partition( number , start , stop , interval )
number : required The value to evaluate against the ranges.
start : required The number that begins the overall range. May not be less than 0.
stop : required The number that ends the overall range. May not be less than or equal to start.
interval : required The width of each individual range. May not be less than 1.
The return value identifies the particular range in which number falls, formatted as "<lowervalue>: <uppervalue>". Partition is most useful in queries --- for example, an SQL SELECT that groups orders by freight-cost band.
The following table shows how the ranges are determined for three sample sets of start, stop, and interval. The Before First column is what Partition returns for a number below start; the After Last column is what it returns for a number above stop.
| start | stop | interval | Before First | First Range | Last Range | After Last |
|---|---|---|---|---|---|---|
| 0 | 99 | 5 | " :-1" |
" 0: 4" |
" 95: 99" |
" 100: " |
| 20 | 199 | 10 | " : 19" |
" 20: 29" |
" 190:199" |
" 200: " |
| 100 | 1010 | 20 | " : 99" |
" 100: 119" |
"1000:1010" |
"1011: " |
In the third row, start and stop don't divide evenly by interval: the last range extends to stop (covering 11 numbers) even though interval is 20.
If necessary, Partition pads each end of the range with leading spaces so that there are the same number of characters to the left and right of the colon as there are characters in stop, plus one. This keeps the labels in the right order under a plain text sort.
If interval is 1, the range collapses to number:number, regardless of start and stop.
Any argument may be a decimal value, but is rounded to the nearest even integer before processing. If any argument is Null, Partition returns Null.
This example passes a series of freight costs to Partition with start = 0, stop = 500, and interval = 50, and prints the range each one falls into. The first range is " 0: 49", and so on up to 500. Note that 12.5 is rounded before the range is chosen, and that a value below start or above stop gets one of the open-ended forms.
Dim Freight As Variant
For Each Freight In Array(-1, 12.5, 49, 50, 275, 499, 500, 501)
Debug.Print Freight & " -> [" & Partition(Freight, 0, 500, 50) & "]"
Next Freight
' -1 -> [ : -1]
' 12.5 -> [ 0: 49]
' 49 -> [ 0: 49]
' 50 -> [ 50: 99]
' 275 -> [250:299]
' 499 -> [450:499]
' 500 -> [500:500]
' 501 -> [501: ]