Multiple Apps

Test if ?X? is between two values, X1 and X2

Ease of Use

Intermediate

Version tested with

2000

Submitted by:

Description:

IsBetween is a boolean function that tests if X is between X1 and X2. The actual test is if X is between Min(X1,X2) and Max (X1,X2)

Discussion:

Checking if a value, X, is between a pair of numbers, say X1 and X2 is a common need in VBA applications. The Boolean function IsBetween performs this task. Although it seems reasonable that X1 < X2, IsBetween does not really care and really tests if X is between Min ( X1 , X2 ) and Max (X1 , X2 ). IsBetween has the ability to check for the two basic cases: if Xmin <= X <= Xmax or if Xmin < X < Xmax. The first case is often called ?inclusive? because the end points are included in the test. The second case is often called ?exclusive? because the end points are excluded from the test. Note that for an exclusive test, if X = X1 or X = X2, IsBetween would return ?False?. The argument IncExc tells IsBetween which test to perform. IncExc is optional; the default is ?inc?, i.e., the end points are included in the test. IsBetween can be used in any VBA application. The demo is Excel-based.

Code:

```			Option Explicit

Function IsBetween( _
X, _
X1, _
X2, _
Optional IncExc = "inc") As Boolean
'
'****************************************************************************************
'       Title       IsBetween
'       Target Application:  any
'       Function;   determines if X is between X1 and X2
'                    either X1 or X2 can be the min; the other is the max;
'                       if X1 = X2 and test is inclusive, IsBetween will be true IFF
'                           X = X1 = X2
'                       if X1 = X2 and test is exclusive, IsBetween is always False
'       Passed Values:
'           X       [in, numeric]
'           X1      [in, numeric]   one side of test
'           X2      [in, numeric]   other side of test
'           IncExc  [in, string, optioal] Inclusive or Exclusive flag
'
'****************************************************************************************
'
'
Dim Xmax
Dim Xmin
'
'           detmine min and max values
'
If X1 <= X2 Then
Xmin = X1
Xmax = X2
Else
Xmin = X2
Xmax = X1
End If
Select Case LCase(IncExc)
Case "inc" ' test includes both X1 and X2:    Xmin <= X <= Xmax
If X >= Xmin And X <= Xmax Then
IsBetween = True
Else
IsBetween = False
End If
Case "exc" ' test excludes both X1 and X2:    Xmin < X < Xmax
If X > Xmin And X < Xmax Then
IsBetween = True
Else
IsBetween = False
End If
Case Else
End Select

End Function

```

How to use:

1. Copy the above code.
2. Open any workbook.
3. Press Alt + F11 to open the Visual Basic Editor (VBE).
4. In the left side window, hi-lite the target spreadsheet [it will likely be called VBAProject(filename.xls) where filename is the name of the spreadsheet]
5. Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert | Module.
6. Paste the code into the right-hand code window.
7. Close the VBE, save the file if desired.
8. See ?Test The Code? below

Test the code:

1. Open the example
2. The example/demo spreadsheet contains a single sheet, the IsBetween procedure and a demo procedure to read values from the spreadsheet and call IsBetween.
3. Enter values for X, X1, X2 and IncExc in the appropriate cells
4. Click on the command button
5. The demo should return an appropriate answer.
6. NOTE: the worksheet is protected so that only the green cells may be changed. The IncExc input cell is also controlled via Excel?s validation protocol to encourage the entry of either ?inc? or ?exc?

Sample File:

Approved by mdmackillop

This entry has been viewed 93 times.

Copyright @2004 - 2020 VBA Express