Range as optional parameter should default to full worksheet of calling cell. Howto?

Question

I wrote a UDF that takes as optional parameter a range. It then calculates the last row of the range being used. If the range is not passed as an argument, the UDF should default to the full worksheet of the calling cell. Somehow, the straigthforward approach didn't work - the UDF Returns 0.

Public Function hrLastRow(Optional r As Range = Nothing) As Long
    If r Is Nothing Then
        hrLastRow = hrLastRow(ThisWorkbook.Worksheets(Application.Caller.Parent.Name).Cells())
    Else
        If Application.WorksheetFunction.CountA(r) <> 0 Then
            hrLastRow = 1 - r.Rows(1).row + _
                        r.Find(What:="*", _
                            After:=r.Cells(1, 1), _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).row
            hrLastRow = Application.WorksheetFunction.Max(hrLastRow, 0)
        Else
            hrLastRow = 0
        End If
    End If
End Function

As I said, if I call this in a cell "=hrLastRow()" the result is zero, despite there being values all over the place :) - so the result should definitely be positive.

Maybe I don't properly use Application.Caller...? Any help would be much appreciated. Or does VBA not allow for the recursive call somehow? Then, why zero?

PS: Works fine on ranges.

PPS: I just noticed, that Excel warns about a circular reference in the calling cell. Maybe this is the underlying issue - but then, how to get around it?

Update: So the target is, to get this working without side effects, like iterative calculation. Someone suggested to just search below the UDF caller to avoidthe circular reference, which sounds like a clever idea to me, simple and on target. Somehow, my code for this seems Buggy though, the results are just off... Here's the current state of the UDF. Just look at the If r is nothing part:

Public Function hrLastRow(Optional r As Range = Nothing) As Variant
    If r Is Nothing Then
        Dim callerRow   As Long
        Dim callerWS    As Worksheet
        Dim searchRange As Range
        Set callerWS = Application.Caller.Parent
        callerRow = Range(Application.Caller.Address).row
        With callerWS
            Set searchRange = .Range(.Cells(callerRow + 1, 1), .Cells(.UsedRange.row, .UsedRange.column))
            hrLastRow = searchRange.Find(What:="*", _
                            After:=searchRange.Cells(1, 1), _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).row
        End With
    Else
        If Application.WorksheetFunction.CountA(r) <> 0 Then
            hrLastRow = 1 - r.Rows(1).row + _
                        r.Find(What:="*", _
                            After:=r.Cells(1, 1), _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).row
            hrLastRow = Application.WorksheetFunction.Max(hrLastRow, 0)
        Else
            hrLastRow = 0
        End If
    End If
End Function

The else part of the outer if Statement works fine.

Couldn't you use `hrLastRow = ThisWorkbook.Worksheets(Application.Caller.Parent.Name).UsedRange.Rows.Count` ? — Damian, Sep 03 '19 at 10:50
Well, that depends of course. UsedRange considers cell as used e.g. if formatted. I would of course want to use consistent measure for both cases. Anyway, I tried it: Same result, 0. — YeOldHinnerk, Sep 03 '19 at 10:54
Ok then we need another approach, thought I used it and it gave me the last row so something is wrong... In your code. On the other hand... if you take for example range `A5:A265941` won't give you the row 265941 even if they are all filled. — Damian, Sep 03 '19 at 10:56
Well, it Counts the rows, it is not the row number in Excel. If you Offset your range by A5, it will be 265941 - 5 + 1. — YeOldHinnerk, Sep 03 '19 at 10:58
the question is more about how to get to the worksheet range, than about last row. I think this is an issue which may come up occasionally... — YeOldHinnerk, Sep 03 '19 at 10:58
Have to walk back my first comment. It does not work with UsedRange, but gives a #VALUE error. Sorry, I edited wrong function (hrLastColumn)... — YeOldHinnerk, Sep 03 '19 at 11:01
One more comment: It is not about the method of determining the last row. There's an excellent thread on this already: https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba — YeOldHinnerk, Sep 03 '19 at 11:15
@SJR: Why? I tried, it does not seem to make a difference. And in General I prefer to initialize my variables. — YeOldHinnerk, Sep 03 '19 at 11:17
@YeOldHinnerk these methods are easy, but they will give you the excel row, not the amount of rows you are having on the range. Would work different if you wanted just the excel row. But which column will be the one you want to count rows in? — Damian, Sep 03 '19 at 11:17
This UDF references the cell with this UDF, so a cyclic reference. If you enable _iterative calculation_ you will get expected results. — BrakNicku, Sep 03 '19 at 11:19
@Damian The function is not limited to a specific column. It does give the proper result for ranges, independet on which column the last used value is. It also handles the Offset properly. The only cases where it gives the wrong value is, when I do not pass an Argument and call it from a cell. — YeOldHinnerk, Sep 03 '19 at 11:22
@YeOldHinnerk under File->Options-Formulas->Enable iterative calculations (it's a checkbox right on the first options right column) — Damian, Sep 03 '19 at 11:23
Not sure what you mean by _If the range is not passed as an argument, the UDF should default to the full worksheet of the calling cell_. The function returns a `Long` so it can only return a row number? Maybe just use `hrLastRow = Application.Caller.Parent.Rows.Count` if r is nothing. — Darren Bartrup-Cook, Sep 03 '19 at 11:23
Just saw my comment was pretty much the same as the first by @Damian. `Worksheets(Application.Caller.Parent.Name)` - will try and return the sheet from the currently active book, while `Application.Caller.Parent` will return a reference to the worksheet in the referenced workbook. — Darren Bartrup-Cook, Sep 03 '19 at 11:30
@BrakNicku So iterative calculation does indeed work. If I change the Option, the function Returns the right result. I also tried to just enable iterative calculation from begin to end of the UDF, and that didn't work. Well, enabling iterative calculation for good seems a fairly strong side effect. Note that I'm essentially building a library of UDFs, which of course should not have such side effects on other users. Is there a way of "breaking" this circular reference? — YeOldHinnerk, Sep 03 '19 at 11:31
@YeOldHinnerk write a workbook event when activated force the iterative calculation. Should do the trick — Damian, Sep 03 '19 at 11:32
@DarrenBartrup-Cook So you are saying, I could just use ´hrLastRow = hrLastRow(Application.Caller.Parent.Cells)`. True. I fixed it. Still requires iterative calculation, which I want to avoid by all means. — YeOldHinnerk, Sep 03 '19 at 11:35
You do not need to search whole worksheet - it is enough to start from the row below UDF. If `Find` does not find anything - return UDf's row. — BrakNicku, Sep 03 '19 at 11:36
@BrakNicku - nice one! I do that and repost the function once it works. — YeOldHinnerk, Sep 03 '19 at 11:39
Now the `searchRange` is defined wrong - as the bottom right corner you pass top left corner of UsedRange. You do not check if `Find` returns anything, also there are some corner cases to check - like UDF entered in row 1048576 — BrakNicku, Sep 03 '19 at 13:43

score 0 · Accepted Answer · answered Sep 04 '19 at 07:51

Thank you to all contributors.

Special thanks to BrakNicku for the simple but clever idea of just searching below the calling cell to avoid circular references.

Feel free to use the code below. Here's the final function, working as desired:

Public Function hrLastRow(Optional r As Range = Nothing) As Long
    Application.Volatile True
    If r Is Nothing Then
        Dim callerRow   As Long
        Dim callerWS    As Worksheet
        Dim searchRange As Range
        Set callerWS = Application.Caller.Parent
        callerRow = Range(Application.Caller.Address).row
        With callerWS
            Set searchRange = .Range(.Cells(callerRow + 1, 1), .Cells(callerRow + .UsedRange.Rows.Count, .UsedRange.Columns.Count))
            If Application.WorksheetFunction.CountA(searchRange) <> 0 Then
                hrLastRow = searchRange.Find(What:="*", _
                                After:=searchRange.Cells(1, 1), _
                                LookAt:=xlPart, _
                                LookIn:=xlFormulas, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False).row
            Else
                hrLastRow = callerRow
            End If
        End With
    Else
        If Application.WorksheetFunction.CountA(r) <> 0 Then
            hrLastRow = 1 - r.Rows(1).row + _
                        r.Find(What:="*", _
                            After:=r.Cells(1, 1), _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).row
            hrLastRow = Application.WorksheetFunction.Max(hrLastRow, 0)
        Else
            hrLastRow = 0
        End If
    End If
End Function

Damian · Answer 2 · 2019-09-03T11:37:04.170

Write this on your workbook module:

Option Explicit
Private Sub Workbook_Activate()
    With Application
        .Iteration = True
        .MaxIterations = 1000
        .MaxChange = 0.001
    End With
End Sub
Private Sub Workbook_Deactivate()

    Application.Iteration = False

End Sub

And add this to your function:

Option Explicit
Public Function hrLastRow(Optional r As Range = Nothing) As Long

    If Application.Iteration = False Then End
    If r Is Nothing Then
        hrLastRow = hrLastRow(ThisWorkbook.Worksheets(Application.Caller.Parent.Name).Cells())
    Else
        If Application.WorksheetFunction.CountA(r) <> 0 Then
            hrLastRow = 1 - r.Rows(1).Row + _
                        r.Find(What:="*", _
                            After:=r.Cells(1, 1), _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
            hrLastRow = Application.WorksheetFunction.Max(hrLastRow, 0)
        Else
            hrLastRow = 0
        End If
    End If

End Function

This way, iteration calculation will only be activated on this workbook, and if it's not activated won't recalculate your function.

Range as optional parameter should default to full worksheet of calling cell. Howto?

2 Answers2