API
@-Formulas
JavaScript
LotusScript
Reg Exp
Web Design
Notes Client
 
Subset Function
In the quest to mimic more @-functions in LotusScript, here is a subset function. However, writing this function is not as straightforward as you might think. Obviously, Subset(array, 4) should give you the first four elements in the array and Subset(array, -4) should give you the last four elements in the array. But in formula language, you're dealing with lists and don't have to worry so much about array positions. (At least until Version 6 when you can access list elements through their position directly). But in LotusScript you do have to worry about this.

And, when making a generic function, you also have to worry about the lower bound of the array. Most developers like using arrays that start off with element zero, but some developers set Option Base 1 in their script, and a generic function will have to account for that. Furthermore, there may be cases when it's more convenient to define an array with "strange" bounds, like Dim a(5 To 10). A generic function has to account for this.

Next, a decision has to be made when returning subsets from the right (a negative number). For example, let's say you have an array of elements 0, 1, 2, and 3. If you want to return the rightmost 2 elements (-2), you would be expecting the values from 2 and 3, but what positions are they in the returned array? Are they elements 0 and 1, or elements 2 and 3? We'll show code that does either, so you can decide which one to implement.

Lastly, we want to mimic @Subset as much as possible. If you ask for 15 elements in an array that has 4 elements, all 4 elements are returned. If you ask for 0 elements, an error is returned. We'll be nicer and return an empty string (which may or may not cause an error, depending on the calling function). And doing a quick test on @Subset reveals that if you don't pass in an array, the entire thing you passed in was returned. For example, @Subset("Hello"; 2) = "Hello". So we'll want to mimic that in our function.

Function Subset(array As Variant, n As Integer) As Variant
   Dim retVal As Variant
   Dim i As Integer
   If Not Isarray(array) Then
      Subset = array
      Exit Function
   End If
   If n = 0 Then
      Subset = ""
      Exit Function
   End If
   If Abs(n) >= (Ubound(array) - Lbound(array) + 1) Then
      Subset = array
      Exit Function
   End If
   If n > 0 Then
      Redim retVal(Lbound(array) To (Lbound(array) + n - 1)) As Variant
   Else
      Redim retVal((Ubound(array) - Abs(n) + 1) To Ubound(array)) As Variant
   End If
   For i = Lbound(retVal) To Ubound(retVal)
      retVal(i) = array(i)
   Next
   Subset = retVal
End Function

The tricky part is defining the bounds of the returned array. If n > 0 then we define the return array to be the existing lower bound to the new upper bound (so the return array will have "n" elements). If n < 0 then the upper bound should be the same as what was passed in, and the lower bound should adjust so there will be "n" elements). Then just move everything over in the same position.

Note: The code above returns everything in the same position. So, this code snippet:
Dim arr(4) As String
Dim result As Variant
arr(0) = "A"
arr(1) = "B"
arr(2) = "C"
arr(3) = "D"
arr(4) = "E"
result = Subset(arr, -2)

will end up with result(3) = "D" and result(4) = "E".

If you would rather have that same code snippet end up with result(0) = "D" and result(1) = "E", then you'll need one more variable called adjust (an Integer) defined at the top of the function. Then the first three "If" blocks are the same and the rest of the code becomes:
   Redim retVal(Lbound(array) To (Lbound(array) + Abs(n) - 1)) As Variant
   For i = Lbound(retVal) To Ubound(retVal)
      adjust = 0
      If n < 0 Then
         adjust = (Ubound(array) - Lbound(array) + 1) - Abs(n)
      End If
      retVal(i) = array(i+adjust)
   Next
   Subset = retVal
End Function

In this code, the lower bound is always going to be the same as what was passed in, and the upper bound is always going to adjust so there's "n" elements returned. But we have to adjust the position of the source element when placing it in the returned array. If n is greater than zero, then no adjustment is necessary. If n is less than zero, then we need to know how many elements, counting from the left, are not going to be used. That is the value of adjust.