Is there a BETWEEN function? 1

rustychef · Jul 21, 2009

Tried to find one, I know its simple enought to create, but I'm trying to get away from creating everything from scratch and dont want to use the IIf() function.

Looking to see if an equivalent function exists that mimics the keyword "Between" in queries.

If I have a number X, I want to know if it lies somewhere in the bounds of range A through B. For example:

Is 234 higher than 13 and less than 100? No
Is 55 higher than 13 and less than 100? Yes

The values Im using are calculated KEY_ID values that don't exist in a table yet, and Im testing to prevent an overlap. This is the reason Im not using queries.

I don't need to know HOW to write the code, just need to know if it already exists since I cant find one.

PHV · Jul 21, 2009

Select Case X
Case 13 To 100
...
End Select

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

fumei · Jul 21, 2009

Seriously though, while indeed Select Case - in this case (pun intended) - does exactly what you are asking about (and seems to me the way to go)...what is your problem with using IIf?

Code:

Option Explicit
Function CheckIt(TestMe As Long) As String
    CheckIt = IIf(TestMe > 13 And TestMe < 100, _
         "Yes, it is", "No, it is not")
End Function

Sub YaddaYadda()
Dim j As Long
j = 255
MsgBox CheckIt(j) [COLOR=red]RESULT?  "No, it is not"[/color red]
End Sub

Sub YaddaYadda2()
Dim j As Long
j = 55
MsgBox CheckIt(j) [COLOR=red]RESULT?  "Yes, it is"[/color red]
End Sub

Heck, if you wanted to you could add parameters for your bottom and top numbers - 13 and 100. So you could pass those values dynamically.

Code:

Option Explicit
Function CheckIt(TestMe As Long, _
   Bottom As Long, _
   Top As Long) As String
   
    CheckIt = IIf(TestMe > Bottom And TestMe < Top, _
         "Yes, it is", "No, it is not")
End Function

Sub YaddaYadda()
Dim j As Long
j = 255
MsgBox CheckIt(j, 13, 100) [COLOR=red]RESULT?  "No, it is not"[/color red]

End Sub

So again, I have to ask, why do you not want to use IIf? Seems to me that using input parameters like above, you essentially HAVE a Between function. You could make the Function return a boolean rather than a string....whatever.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

rustychef · Jul 21, 2009

Since no one has come right out and said "YES!" I'll just assume it doesn't exist.

PHV:
I'll give you a star since the simplicity of a SELECT CASE statement never crossed my mind... thanks.

As I said I wasn't looking for a solution, was just tired of making code when something already existed (my last fiasco was creating a function that mimics the RegExp class in Excel

... never knew it was out there already)

For anyone looking for another solution to the problem, I put this together to use:

Function IsBetween(hTestValue As Long, hRngMIN As Long, hRngMAX As Long) As Boolean
If (hTestValue >= hRngMIN) And (hTestValue <= hRngMAX) Then IsBetween = True
End Function

'Call with...
If IsBetween(X, A, F) Then ...

fumei:
To answer your question, code execution/optimization is the reason. Everything that I've read talks about using plain old IF over the IIf(). I never bothered with using it before, but my last few projects have been big enough that I've been trimming anywhere I can. Even fractional seconds add up. If you search for code optimization you can see what I mean. The explanation usually given is that if you use an IF/THEN/ELSE statement, the code only has to execute one path, the True or False (based on your logic test). Whereas IIf(), Choose() and Switch(), all paths have to be evaluated because they are functions.

Its along the same lines as assigning vbNullString over "" (empty string) X = vbNullString... something about when using "", the system has to evaluate the entire length of the data type (10 bytes + number of characters used)? before it can determine if the value is an empty string.

While using IIf() looks more elegant and conserves space, I'll take optimization.

Thanks for the inputs!

DaveInIowa · Jul 21, 2009

No, there isn't a built-in function; you'll have to write your own. But since the BETWEEN operator in SQL in effect returns a Boolean result, why would you need to use the IIf() function?

Code:

Public defaultLowerLimit As Integer
Public defaultUpperLimit As Integer

Public Sub Main()
    defaultLowerLimit = 13
    defaultUpperLimit = 100
    
    MsgBox IsBetween(234)
    MsgBox IsBetween(55)
    
End Sub

Public Function IsBetween(value As Integer, Optional lowerLimit As Variant, Optional upperLimit As Variant) As Boolean
    If (IsMissing(lowerLimit)) Then lowerLimit = defaultLowerLimit
    If (IsMissing(upperLimit)) Then upperLimit = defaultUpperLimit
    IsBetween = (lowerLimit < value And value < upperLimit)
End Function

HughLerwill · Jul 21, 2009

>all paths have to be evaluated because they are functions

I'm guessing all paths have to be evaluated IF they are functions; if they are Constants (as in Gerry's examples)they are already evaluated.

rustychef · Jul 21, 2009

DaveInIowa:
Good point. I overlooked that direct assignment. Code already updated. Thanks.

HughLerwill:
Maybe someone knows more on the IIf() etc. (Im certainly not the expert), but everything I've read so far talks about dumping IIf for optimization (especially in queries).

fumei · Jul 21, 2009

Code:

Option Explicit
[COLOR=red]'  if the paramters are NOT dynamic[/color red]
Function IsBetween(num As Long) As Boolean  
    IsBetween = IIf(num > 13 And num < 100, _
         -1, 0)
End Function

Sub YaddaYadda()
   MsgBox IsBetween(255)  ' Result = "False"
   MsgBox IsBetween(55)   ' Result = "True"
End Sub

[COLOR=red]'  if the paramters ARE dynamic[/color red]
Function IsBetween2(num As Long, _
   Bottom As Long, _
   Top As Long) As Boolean
   IsBetween2 = IIf(num > Bottom And num < Top, _
         -1, 0)
End Function

Sub YaddaYadda2()
   MsgBox IsBetween2(255, 13, 100)  ' Result = "False"
   MsgBox IsBetween2(55, 13, 100)   ' Result = "True"
End Sub

Is there any gain by having to declare Public variables:

Code:

Public defaultLowerLimit As Integer
Public defaultUpperLimit As Integer

and then having to set the values?

Code:

Public Sub Main()
    defaultLowerLimit = 13
    defaultUpperLimit = 100
    
    MsgBox IsBetween(234)
    MsgBox IsBetween(55)
    
End Sub

As for optimization...perhaps, but if the parameters ARE constant then they do not need to be evaluated, and if they are not constant, then:

Code:

    If (IsMissing(lowerLimit)) Then lowerLimit = defaultLowerLimit
    If (IsMissing(upperLimit)) Then upperLimit = defaultUpperLimit

is still TWO instructions that have to be actioned.

However, as I know diddely squat about SQL, I am probably - again - talking waaaaay outside my knowledge.

Rusty, yes I can see that there could indeed be optimization issues if you are dealing with large amount of processing.

Code:

Function IsBetween(hTestValue As Long, _
     hRngMIN As Long, _
     hRngMAX As Long) As Boolean
    If (hTestValue >= hRngMIN) And _
        (hTestValue <= hRngMAX) Then IsBetween = True
End Function 

'Call with...
If IsBetween(X, A, F) Then ...

processes one instruction and my:

Code:

Function IsBetween2(num As Long, _
   Bottom As Long, _
   Top As Long) As Boolean
   IsBetween2 = IIf(num > Bottom And num < Top, _
         -1, 0)
End Function

' Call with...
If IsBetween2(X, A, F) Then....

processes one instruction as well...BUT...IIf does in fact always evaluate both truepart (the -1), and falsepart (the 0).

So yeah, I can see why you are trying to avoid IIf if you are doing this a lot.

But, as was stated, PHV's Select Case is probably the easiest way to do it.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

DaveInIowa · Jul 21, 2009

Is there any gain by having to declare Public variables:

In retrospect, no. It was quickly done code trying to simulate an overloaded function. Since I didn't want to assume any default values for the optional parameters, I cobbled that together. After thinking about it, I'd force all 3 parameters to be passed.

Code:

Public Function IsBetween(value As Long, lowerLimit As Long, upperLimit As Long) As Boolean
    IsBetween = (lowerLimit < value And value < upperLimit)
End Function

[green]' This may be faster since VBA does not have a short-circuit And operator ...[/green]
Public Function IsBetween(value As Long, lowerLimit As Long, upperLimit As Long) As Boolean
    If (lowerLimit < value) Then IsBetween = (value < upperLimit)
End Function

[green]' I'd suggest a more descriptive name if the limits are static ...[/green]
Public Function IsBetween13And100(value As Long) As Boolean
    IsBetween = (13 < value And value < 100)
End Function

[green]' And if you're really concerned with performance, ditch the function call for inline code ...[/green]
[green]' Instead of ...[/green]
If (IsBetween13And100(myValue)) Then
[green]' Use ...[/green]
If (13 < myValue And myValue < 100) Then

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Is there a BETWEEN function? 1

rustychef

Technical User

PHV

MIS

fumei

Technical User

rustychef

Technical User

DaveInIowa

Programmer

HughLerwill

Programmer

rustychef

Technical User

fumei

Technical User

DaveInIowa

Programmer

Similar threads

Part and Inventory Search

Sponsor