Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: IsBetween - a helpful UDF function

  1. #1
    Board Regular MrKowz's Avatar
    Join Date
    Jun 2008
    Location
    St. Louis, MO
    Posts
    6,648
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default 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!
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes
    - Please use [CODE] [/CODE] tags when posting your VBA code. It retains spacing, so your code is easier to read, and therefore easier to debug.
    - Please back up your file before using any macros suggested!

  2. #2
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,493
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Office 2010/365

  3. #3
    Board Regular MrKowz's Avatar
    Join Date
    Jun 2008
    Location
    St. Louis, MO
    Posts
    6,648
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default 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!
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes
    - Please use [CODE] [/CODE] tags when posting your VBA code. It retains spacing, so your code is easier to read, and therefore easier to debug.
    - Please back up your file before using any macros suggested!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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