CODEWITHSUNDEEP
vbaexcelfunctionif-statementboolean
Will Parker
Will Parker

Reputation: 3

Function Subroutine : Boolean

I am using an if statement in my procedure that calls upon my Boolean function; however, when I run the program, it always outputs the message as if the function were true.

Public Function INR() As Boolean
    If Avg < 1.5 & SD < 0.5 Then
        INR = True
    Else
        INR = False
    End If
End Function

Public Sub PatientINR()
    Range("B2", Range("B1").End(xlDown)).Name = "Name"
    Dim cell As Range, reply As String, i As Long, x As Variant, Avg As Long, SD As Long
    reply = InputBox("Enter patient's name", "INR", "Madelyn Carberry")
        i = 1
    x = Range("Name").Cells(i)
    Do Until x = reply
        i = i + 1
        x = Range("Name").Cells(i)
    Loop
    Avg = Range("Name").Cells(i, 17)
    SD = Range("Name").Cells(i, 18)
    Call INR
    If INR = True Then
        MsgBox reply & "'s INR record is satisfactory for the procedure"
    End If
    If INR = False Then
        MsgBox reply & "'s INR record is not satisfactory for the procedure"
    End If

End Sub

Upvotes: 0

Views: 715

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

You need to pass the data to the function. Change the first line of the function to:

Public Function INR(Avg As Double, SD As Double) As Boolean

Then when you call the function pass the variables:

If INR(Avg,SD) Then

As is, the values are not being passed and as such are 0 when it tests. Which will return true.

As you see above you do not need the = True it is a boolean and therefore Already True or False.

Also do not test for False just use Else:

If INR(Avg,SD) Then
    MsgBox reply & "'s INR record is satisfactory for the procedure"
Else
    MsgBox reply & "'s INR record is not satisfactory for the procedure"
End If

Also & is used in Concatenation use the word And:

If Avg < 1.5 And SD < 0.5 Then

The function gets called in the if, there is no need for Call INR, just use it as any other function.

Also it is good practice to name the sheet on which this is run. We can do that with a With Block:

With Worksheets("Sheet1") 'Change to your sheet
    'Do your code
End With

Instead of creating a named range lets use a Range variable:

Set rng = .Range("B:B")

Lets speed things up a little and remove the loop. We can use Match to find the row with:

i = Application.WorksheetFunction.Match(reply, rng, 0)

If the name is found it will return the row in which it was found. If not it will return an error. I have opted to skip if in error and thus i will remain 0 and we can test for that before continuing.

INR's default will be False so we do not need to set it and save some typing:

INR = (Avg < 1.5 And SD < 0.5)

Should be sufficient

So:

Public Function INR(Avg As Double, SD As Double) As Boolean
    INR = (Avg < 1.5 And SD < 0.5)
End Function

Public Sub PatientINR()

    Dim cell As Range, reply As String, i As Long
    Dim Avg As Double, SD As Double
    Dim rng As Range
    reply = InputBox("Enter patient's name", "INR", "Madelyn Carberry")
    i = 0
    With Worksheets("Sheet1") 'Change to your sheet
        Set rng = .Range("B:B")
        On Error Resume Next
            i = Application.WorksheetFunction.Match(reply, rng, 0)
        On Error GoTo 0
        If i <> 0 Then
            Avg = .Cells(i, 17)
            SD = .Cells(i, 18)
            If INR(Avg, SD) Then
                MsgBox reply & "'s INR record is satisfactory for the procedure"
            Else
                MsgBox reply & "'s INR record is not satisfactory for the procedure"
            End If
        Else
            MsgBox "Name not Found"
        End If
    End With

End Sub

Upvotes: 2

Related Questions