IsBetween - a helpful UDF function

[MrKowz]

There have been many times when I have had to use if statements to determine if a value is between another range of values, and I hated having to create the AND() statements over and over for this, so I finally created an IsBetween UDF that works like a charm, and oddly enough it seems to be able to determine if a single character (a, b, c, etc) is between two other characters!:

Syntax:

=ISBETWEEN([Value],[LowerBound],[UpperBound],[EqualType - (Optional)])

Where Value is the number to determine if it lies between LowerBound and UpperBound. EqualType is a boolean argument where 0, FALSE, or null will return GREATER THAN and LESS THAN (no equal to) and a Number other than 0 or TRUE will return GREATER THAN OR EQUAL TO and LESS THAN OR EQUAL TO.

Public Function IsBetween(value As Variant, lower As Variant, upper As Variant, Optional equaltype As Boolean) As Boolean
    Select Case equaltype
        Case False
            If value > lower And value < upper Then
                IsBetween = True
            Else
                IsBetween = False
            End If
        Case True
            If value >= lower And value <= upper Then
                IsBetween = True
            Else
                IsBetween = False
            End If
    End Select
End Function

Hope some people are able to find use of this!

 

[Scott Huish]

I wouldn't name your variable value as Value is a VBA keyword, and UPPER and LOWER are spreadsheet functions.

Your statements with And will return True or False values so you can just assign this to IsBetween

Public Function IsBetween(testvalue As Variant, lwr As Variant, upr As Variant, Optional equaltype As Boolean) As Boolean
If equaltype Then
    IsBetween = (testvalue >= lwr And testvalue <= upr)
Else
    IsBetween = (testvalue > lwr And testvalue < upr)
End If
End Function

 

[MrKowz]

Awesome! I knew there had to be a shorter, sweeter, way to code this. Thank you for the contribution, Hotpepper!