Multiple Apps

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

Ease of Use


Version tested with


Submitted by:



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) 


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. 


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: 17.78KB 

Approved by mdmackillop

This entry has been viewed 93 times.

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