VBA Function to find Activecell Table Row

Question

As a learning exercise & possible use in future code I have created my first Excel VBA function to return the activecell row number in any Excel Table (as opposed to the sheet itself) . Essentially it simply finds the active row in the sheet, then finds the row number of the table header which is then subtracted from the cell row number to return the row number of the table which can then be used in subsequent code. However, while it works, it dosen't look the most efficient Can anyone improve it?

Sub TableRow()
Dim LORow As Integer
Dim TbleCell As Range
Set TbleCell = Activecell
Call FuncTableRow(TbleCell, LORow)
MsgBox LORow
End Sub


Public Function FuncTableRow(ByRef TbleCell As Range, LORow As Integer) As Range
    Dim LOName As String
    Dim LOHeaderRow, Row As Integer
    LOName = Activecell.ListObject.Name
    Row = Activecell.Row
    LOHeaderRow = ActiveSheet.ListObjects(LOName).HeaderRowRange.Row
    LORow = Row - LOHeaderRow
    Debug.Print (LORow)
End Function

Answer 1

This question will probably get closed for not being specific enough but the most obvious item (to me) is your usage of a custom function. Your function is not actually returning anything, it's only running a debug print. To have your function actually return the row number, you would set it as a type Long (not integer) and include the function name = to the number.

I didn't actually test your function but assuming LORow is dubug printing the proper answer then it should work like this:

Public Function FuncTableRow(ByRef TbleCell As Range, LORow As Integer) As Long
    Dim LOName As String
    Dim LOHeaderRow, Row As Integer
    LOName = Activecell.ListObject.Name
    Row = Activecell.Row
    LOHeaderRow = ActiveSheet.ListObjects(LOName).HeaderRowRange.Row
    LORow = Row - LOHeaderRow
    Debug.Print (LORow)
    FuncTableRow = LORow
End Function

• You also don't Call a function, you can just insert it as itself in a subroutine.
• You are using LORow as an input variable but then changing it. That's typically a bad practice.
• You should not be using ActiveSheet grab the worksheet from TbleCell.Worksheet
• You would almost never use activecell as part of a Custom Formula.
• Dim LOHeaderRow, Row As Integer should actually be Dim LOHeaderRow as Long, Row As Long. As you currently have it LOHeaderRow is undefined/Variant.

There's probably more. I would restart your process with a simpler task of returning the last used cell in a worksheet. There's a dozen ways to do this and lots of help examples.

Answer 2

Take a look at this TheSpreadsheetGuru.

Here are some variables that might help you.

Sub TableVariables()
    Dim ol As ListObject: Set ol = ActiveSheet.ListObjects(1)
    Dim olRng As Range: Set olRng = ol.Range                            ' table absolute address
    Dim olRngStr As String: olRngStr = ol.Range.Address(False, False)   ' table address without absolute reference '$'
    Dim olRow As Integer: olRow = ol.Range.Row                          ' first row position
    Dim olCol As Integer: olCol = ol.Range.Column                       ' first column position
    Dim olRows As Long: olRows = ol.Range.Rows.Count                    ' table rows including header
    Dim olCols As Long: olCols = ol.ListColumns.Count                   ' table columns
    Dim olListRows As Long: olListRows = ol.ListRows.Count              ' table rows without header
End Sub