#Value Error Using UDF Within Worksheet

I have a simple UDF which returns a boolean result of either true or false. It works properly and consistently when run within the VBA module; however, when I include the UDF in the actual worksheet it returns a #VALUE error.

Code:

---

Sub testFunction()
  Dim myRow As Integer
  myRow = 34

 MsgBox RowCompleted(myrRow)
 
End Sub

---

Code:

---

'FUNCTION TO DETERMINE LAST ROW WITH DATA
Function LastRow() As Integer

  LastRow = Sheets("BIBLE").Range("I:I").Find(what:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

End Function

---

Code:

---

'FUNCTION TO DETERMINE IF ROW IS COMPLETED
Function RowCompleted(r As Integer) As Boolean

  If r <= LastRow - 2 Then
    RowCompleted = True
      Else: RowCompleted = False
  End If
 
End Function

---

When I utilize the function within the worksheet, I enter the following formula:

Code:

---

=RowCompleted(Row())

---

What might be causing the #Value error?

Columns can be dimmed as integers. Rows should be dimmed as long. Check to make sure that you get a result. Nothing is not a long value.

Code:

---

Sub testFunction()
  Dim myRow As Long
  myRow = 34
  MsgBox RowCompleted(myRow)
End Sub

'FUNCTION TO DETERMINE LAST ROW WITH DATA
Function LastRow() As Long
  Dim lr As Range
  Set lr = Sheets("BIBLE").Range("I:I").Find(what:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
  If Not lr Is Nothing Then LastRow = lr.Row
End Function

'FUNCTION TO DETERMINE IF ROW IS COMPLETED
Function RowCompleted(r As Integer) As Boolean
  Application.Volatile False
  If r <= LastRow - 2 Then
    RowCompleted = True
      Else: RowCompleted = False
  End If
End Function

---

Thanks. Did you intentionally leave the "r as Integer" in the RowCompleted function?

Your changes got rid of the #Value error. And, the correct value seems to be returned when running the test Sub within the VBA module. However, it is returning a result of "False" for all rows, including the rows that are actually completed, when I utilize the function within the worksheet. I can't understand why it works in VBA and not in the Worksheet.


----EDIT-----
After checking further, it's not working in VBA with the "r as Integer" in the RowCompleted function. I think what I did was temporarily change it to "Long" and then it worked. Went back and checked it again after changing it back to "r as Integer" and am receiving a type mismatch error.

I think I have narrowed down at least one thing that seems to be going on. With the "r as Integer" changed to "r as Long" within the RowCompleted function, when the test Sub is run within VBA, the LastRow function is returning the proper last row value. Hence, the RowCompleted function is returning True or False correctly. However, when the RowCompleted function is run from within the worksheet, the LastRow function is returning a value of 0 for some reason. Hence, all of the rows are returning False.

Ken's code works for me in Excel 2010.

The only way I could the wrong / unexpected result is to try and .Find on an empty Range, since if lr was Nothing, then 0 would be returned

(I also took out the .Volatile false)

Code:

---

'FUNCTION TO DETERMINE LAST ROW WITH DATA
Function LastRow() As Long
    Dim lr As Range
    Set lr = Sheets("Sheet1").Range("I:I").Find(what:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    If Not lr Is Nothing Then
        LastRow = lr.Row
    Else
        MsgBox "Not Finding right"
    End If
End Function

'FUNCTION TO DETERMINE IF ROW IS COMPLETED
Function RowCompleted(r As Long) As Boolean
    Application.Volatile
    If r <= LastRow - 2 Then
        RowCompleted = True
    Else
        RowCompleted = False
    End If
End Function

---

Paul

Thanks. And, I should have mentioned in my original post that I am running Excel 2000. Sorry about that.

I tried your code and it went back to the #Value error. I think the problem is definitely with the Find code. For whatever reason, the LastRow function is finding the correct range when called from within VBA, but when "RowCompleted" is used within the Worksheet the LastRow function not finding the range and, consequently, returning a 0 value.

I don't know how well it would work in 2000. Post your workbook and maybe someone could test it.

I came up with a work-around to Find, which is what was causing the problem. Instead of searching for the last row in VBA, I found a convenient cell within the worksheet to include the following formula:

Code:

---

=SUMPRODUCT(MAX((ROW($I$1:$I$1000))*($I$1:$I$1000<>"")))

---

I named that cell "LastRow" and compare the value of that cell in row in question in the RowCompleted function and it is returning the correct value now. Thanks to both of you for your help.

'complete FUNCTION TO DETERMINE IF ROW(r), Column"I", IS COMPLETED
Function RowCompleted(r As Long) As Boolean
RowCompleted = (r <= Sheets("Sheet1").Cells(Rows.Count, 9).End(xlUp).Row - 2)
End Function