Using user-defined functions on the worksheet

[tiredofit]

I have a function as follows:

Public Function abc (a, b, c)

    abc= a + b + c

End Function

I can use it in Excel by typing:

=abc(1, 2, 3)

which returns the value of 6, as expected.

I have another function which finds the last row on a worksheet:

Public Function LRow(ByRef wks As Worksheet) As Long
    On Error GoTo Correction
        With wks
        
            LRow = .Cells.Find(What:="*", _
                               After:=.Cells(Rows.Count, Columns.Count), _
                               SearchDirection:=xlPrevious, _
                               SearchOrder:=xlByRows).Row
            
        End With
    
        GoTo Exitpoint
    
Correction:
        LRow = 1
Exitpoint:
    On Error GoTo 0
End Function

I tried typing:

= LRow(Sheet1)

but it returned #VALUE !

What is wrong?

Thanks

 

[VBA Geek]

you cant pass a worksheet object from a worksheet formula but you can pass a Range, so this should work:

Public Function LRow(ByRef R As Excel.Range) As Long
On Error GoTo Correction


With R.Worksheet
    LRow = .Cells.Find(What:="*", _
                       After:=.Cells(Rows.Count, Columns.Count), _
                       SearchDirection:=xlPrevious, _
                       SearchOrder:=xlByRows).Row
End With
    
Exit Function
    
Correction:
        LRow = 1
End Function

=LRow(G10)

 

[tiredofit]

you cant pass a worksheet object from a worksheet formula but you can pass a Range, so this should work:

Public Function LRow(ByRef R As Excel.Range) As Long
On Error GoTo Correction


With R.Worksheet
    LRow = .Cells.Find(What:="*", _
                       After:=.Cells(Rows.Count, Columns.Count), _
                       SearchDirection:=xlPrevious, _
                       SearchOrder:=xlByRows).Row
End With
    
Exit Function
    
Correction:
        LRow = 1
End Function

=LRow(G10)

Thanks, indeed it did work.

 

[tiredofit]

you cant pass a worksheet object from a worksheet formula but you can pass a Range, so this should work:

Public Function LRow(ByRef R As Excel.Range) As Long[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]On Error GoTo Correction[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]With R.Worksheet[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    LRow = .Cells.Find(What:="*", _[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]                       After:=.Cells(Rows.Count, Columns.Count), _[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]                       SearchDirection:=xlPrevious, _[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]                       SearchOrder:=xlByRows).Row[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Exit Function[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Correction:[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        LRow = 1[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End Function[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]

=LRow(G10)

So instead of writing:

= Sum (G1:G100")

can I write:

= Sum (G1:G) & LRow(G10)

expecting to sum from G1 to the last cell in column G?

 

[RoryA]

No. You would have to use either INDIRECT or INDEX, like this:

=Sum(G1:INDEX(G:G,LRow(G10)))

or:

= Sum(INDIRECT("G1:G" & LRow(G10)))

 

[Rick Rothstein]

Here is another LRow function for you to consider.... pass the sheet name into it as a quoted text string or a cell reference containing the cell name. Note that it has an optional Boolean argument that defaults to False... True returns last row even if it contains formulas display the empty text string ("") whereas False returns the last row displaying data even if there are formulas displaying the empty text string after it. For cells containing no formulas, it makes no difference what the optional argument is set to.

[table="width: 500"]
[tr]
	[td]Function LRow(SheetName As String, Optional IncludeFormulaBlanks As Boolean) As Variant
  Dim SearchType As Long
  On Error GoTo EmptySheet
  SearchType = IIf(IncludeFormulaBlanks, xlFormulas, xlValues)
  LRow = Sheets(SheetName).Cells.Find("*", , SearchType, , xlRows, xlPrevious, , , False).Row
  Exit Function
EmptySheet:
  LRow = 1
End Function[/td]
[/tr]
[/table]

 

[tiredofit]

No. You would have to use either INDIRECT or INDEX, like this:
=Sum(G1:INDEX(G:G,LRow(G10)))
or:
= Sum(INDIRECT("G1:G" & LRow(G10)))

Here is another LRow function for you to consider.... pass the sheet name into it as a quoted text string or a cell reference containing the cell name. Note that it has an optional Boolean argument that defaults to False... True returns last row even if it contains formulas display the empty text string ("") whereas False returns the last row displaying data even if there are formulas displaying the empty text string after it.

[/FONT][/COLOR][/LEFT][TABLE="width: 0"]
<tbody style="border-collapse: collapse; width: auto;">[TR]
[TD]Function LRow(SheetName As String, Optional IncludeFormulaBlanks As Boolean) As Variant
  Dim SearchType As Long
  On Error GoTo EmptySheet
  SearchType = IIf(IncludeFormulaBlanks, xlFormulas, xlValues)
  LRow = Sheets(SheetName).Cells.Find("*", , SearchType, , xlRows, xlPrevious, , , False).Row
  Exit Function
EmptySheet:
  LRow = 1
End Function[/TD]
[/TR]
</tbody>[/TABLE]
[LEFT][COLOR=#222222][FONT=Verdana]

​

Thanks for both tips.