Consulting

Results 1 to 9 of 9

Thread: #Value Error Using UDF Within Worksheet

  1. #1
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location

    Talking #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.

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

    '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

    '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:

    =RowCompleted(Row())
    What might be causing the #Value error?

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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.
    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

  3. #3
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    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.
    Last edited by Opv; 08-01-2013 at 01:52 PM.

  4. #4
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    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.

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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)


    '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
    Last edited by Paul_Hossler; 08-01-2013 at 03:09 PM. Reason: Finish my entry

  6. #6
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    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.

  7. #7
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    I don't know how well it would work in 2000. Post your workbook and maybe someone could test it.

  8. #8
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    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:

    =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.

  9. #9
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    '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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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