Multiple Apps

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

Ease of Use

Intermediate

Version tested with

2000 

Submitted by:

MWE

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:

instructions for use 

			
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 
        MsgBox "bad call to IsBetween" 
    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:

IsBetween.zip 17.78KB 

Approved by mdmackillop


This entry has been viewed 94 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express