Thanks:  0
Likes:  0

1. ## IsBetween - a helpful UDF function

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.

Code:
```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!

2. ## Re: IsBetween - a helpful UDF function

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

Code:
```
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```

3. ## Re: IsBetween - a helpful UDF function

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•