Generic function procedure working with different workbooks

Question

I am trying to get better coding practice and using generic function.
I am working with several workbooks from a master file. For example if I want to get the last row I am using the following line of code.

LastRow=Range("A" & Rows.Count).End(xlUp).Row 

To retrieve the value with a function I build the function:

-Function 1

Function GetLastRow() As Integer
GetLastRow = Range("A" & Rows.Count).End(xlUp).Row
End Function 

Now from my Sub Main() I want to use GetLastRow() for different workbooks or worksheets. I think it is not a good thing to Activate the workbook before calling my function.

Then should I transmit each time the workbook name and worksheet number to my function and change my function to:

-Function 2

Function GetLastRowIn(sWb As String, iWs As Integer) As Integer
GetLastRowIn = Workbooks(sWb).Worksheets(iWs).Range("A" & Rows.Count).End(xlUp).Row
End Function

Or is there a better/simpler way to transmit the workbook and worksheet in which I want to apply the function while keeping it with no argument as in Function 1?

Thanks for your answers!

Answer 1

To make a function more generic you can allow for some flexibility,
but also impose some rulles for the function calls

---

Generic Function

Option Explicit

Public Function GetLastRow(ByRef ws As Worksheet, Optional ByVal fromCol As Long = 1) As Long
    Dim invalidWS As Boolean, lastRow As Long

    If Not ws Is Nothing Then   'check 1st param

        On Error Resume Next    'check that ws reference is valid (delted WS invalidates ws)
            invalidWS = Len(ws.Name) > 0
            invalidWS = Err.Number <> 0  'No error if Err.Number = 0
        On Error GoTo 0

        If Not invalidWS Then
            If fromCol > 0 And fromCol <= ws.Columns.Count Then 'validate 2nd param
                lastRow = ws.Cells(ws.Rows.Count, fromCol).End(xlUp).Row
                'check if WS.fromCol is empty
                If lastRow = 1 And Len(ws.Cells(1, fromCol)) = 0 Then lastRow = 0
            End If
        End If
    End If
    GetLastRow = lastRow
End Function

---

Test Sub

Public Sub TestGetLastRow()

    'show results in the Immediate Window (VBA Editor: Ctrl+G)
    Debug.Print GetLastRow(Sheet1, 1)                               'CodeName of WS
    Debug.Print GetLastRow(Workbooks(1).Worksheets(1))              'WS Index
    Debug.Print GetLastRow(ActiveWorkbook.Worksheets("Sheet3"), 3)  'WS name (string)
    Debug.Print GetLastRow(ThisWorkbook.Worksheets(1), 0)           'invalid column (or -3)

    Dim ws As Worksheet
    Set ws = Sheet3
        Application.DisplayAlerts = False
        ws.Delete                           'invalidate ws variable
        Application.DisplayAlerts = True
    Debug.Print GetLastRow(ws, 1)           'function call with invalid ws object
End Sub

---

• Always use Option Explicit to allow the compiler to catch spelling mistakes in variable names
• Validate all input
  • The function call may not include a valid Worksheet, or column number
• Allow the Worksheet to be specified by CodeName, WS Index, or WS Name (string)
• Allow a default column ID by using Optional for the 2nd parameter
• Impose the call to send only a Worksheet object as the first parameter
  • If you accept strings for it you need to first check that Worksheet("Invalid") exists
• Impose the call to request column by Index
  • If you allow strings in column ID you need to check that the string is between "A" and "XFD"
    • String length between 1 and 3, and also not allow strings like "XYZ"
    • This would require a separate function that checks each letter in the string
    • Strings also create potential for more maintenance if MS decides to increase max columns
• Make the function for one purpose (like you did) - don't include other functionality later on
• The function should be self contained
  • Able to detect and handle all possible errors and unexpected input
  • And generate the most generic and usable output
• By returning a 0 in this particular function, calls that expect a valid number will error out for row 0
  • So you may want to change it to return 1 even if the sheet is empty
    • and check the top cell if blank after the call returns

As a note:

several workbooks from a master file

• A Workbook is a file
• A Worksheet is a tab inside a file (a file can contain multiple sheets / tabs)
• Always be explicit with all object
  • Range("A" & Rows.Count).End(xlUp).Row implicitly uses ActiveWorkbook.ActiveSheet
  • Translates to ActiveWorkbook.ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
  • If you need to work with several Workbooks and Worksheets, fully qualify your calls
    • `Workbooks("Book1").Worksheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row
    • `Workbooks("Book2").Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
  • so Excel can access the exact object you need to work with

If you use full references your code doesn't depend on active objects - If a user activates a different Workbook, or Worksheet the code will continue to work without errors

Hope this helps

PS. When using row variables always declare them as Long to be able to handle more than the Integer maximum of 32,767 - currently Excel has a max of 1,048,576 rows (in the future this max can increase even higher)