Helpneeded in writing special function, "IsBetween"

[John Wirt]

I want to write a VBA function that compares the numeric values in cell1
against the value is cell2 and cell 3, and reports "Yes" if cell1 is between
cell2 and cell 3, and otherwise "No." Here is a cut & paste of the function
I;ve written in PERSONAL.XLS (see below).

WHen I enter =IsBetween(A1, B1,C1) in D1 with numeric values in A1, B1, and
C1 I get a #NAME error in D1. How come?

Function IsBetween(cell1, cell2, cell3) As String
If cell1 > cell2 Then
If cell1 < cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
ElseIf cell1 < cell2 Then
If cell1 > cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
End Function


Thanks.

John Wirt

 

[Dan E]

John,

Your missing an End If

Function IsBetween(cell1, cell2, cell3) As String
If cell1 > cell2 Then
If cell1 < cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
ElseIf cell1 < cell2 Then
If cell1 > cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
End If 'MISSING
End Function

Dan E

 

[Bob Phillips]

John,

Why use VBA when you can do it with a worksheet function

=IF(OR(AND(A1>B1,A1<C1),AND(A1<B1,A1>C1)),"Yes","No")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[J.E. McGimpsey]

Unless your Personal.xls is an add-in, you need to explicitly
reference Personal.xls:

=Personal.xls!IsBetween(A1,B1,C1)

You also had an Endi If missing.

 

[J.E. McGimpsey]

Bob - that doesn't do what the OP's function does when A1=B1 or
A1=C1. One alternative:

=IF(OR(A1=B1,A1=C1),"",IF(AND(A1>B1,A1<C1),"Yes","No"))

 

[Tom Ogilvy]

Function IsBetween(cell1, cell2, cell3) As String
IsBetween = _
IIf((cell1 > cell2 And cell1 < cell3) Or _
(cell1 > cell3 And cell1 < cell2), "Yes", "No")
End Function

 

[John Wirt]

How do I make Personal.xls an Add-in? Thanks.

John

 

[John Wirt]

Because I get tired of typing this long formula in and changing the cell
address many times. Block copy won't work in my sitiuation.

 

[J.E. McGimpsey]

Oops - dropped the rest of your function:

=IF(OR(A1=B1,A1=C1),"",IF(OR(AND(A1>B1,A1<C1),AND(A1<B1,A1>C1)),
"Yes","No"))

 

[Tom Ogilvy]

select it in the project explorer and select ThisWorkbook. Then go to the
properties window and select the isaddin property and set it to true.

 

[Bob Phillips]

JE,

You're right of course, but I find it difficult to believe that the OP wants
to show a blank if A1 is on one of the boundaries. I may be (probably am?)
wrong, but I would venture that it is a case that he didn't think of all the
possibilities as you did.

Regards

Bob

 

[John Wirt]

It works, lads. Thank you.

Will be used a lot in checking statistical tables for the National Center
for Education Statistics.

John

 

[John Wirt]

You're right, I didn't think of the boundaries. I wnat the function to show
"Err? if any of the entries are equal (exactly equal), so I've added that to
function.

John

 

[John Wirt]

Yes, this is a bit slicker.

John

Function IsBetween(cell1, cell2, cell3) As String
IsBetween = _
IIf((cell1 > cell2 And cell1 < cell3) Or _
(cell1 > cell3 And cell1 < cell2), "Yes", "No")
End Function

 

[Bob Phillips]

John,

Why do you need to keep typing it? Copy/paste will update the references to
relative to the target cell.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[John Wirt]

I started out using this function and found that I was spending most of my
time getting just the right columns in the formula every place I need it.
The columns are not always adjacent to each other.

Sometimes a VBA function is better.

John