Common VBA functions

Some common boilerplate VBA functions.

GetLastRowWithData gets the last row number with actual data in a named worksheet.
LastRowWithData alternate approach to get the last row with data, it can look at whole worksheet or just a range .
MyDocsPath gets the path to the users Documents folder.
IsWkBkOpen checks if a workbook is open.

' Declare a function that takes a worksheet name as an argument and returns a long
Private Function FindLastRowWithData(ByVal SheetName As String) As Long

    Dim Ws As Worksheet
' Declare a variable to store the worksheet object
    Dim LastCell As Range
' Declare a variable to store the last cell in the worksheet
    
    Set Ws = Worksheets(SheetName)
' Set the worksheet object to the worksheet with the given name
    Set LastCell = Ws.Cells.Find(What:="*", After:=Ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
' Use the Find method to find the last cell in the worksheet that has data

    If Not LastCell Is Nothing Then
' Check if the last cell is not Nothing
        If LastCell.Comment Is Nothing Then
' Check if the last cell has a comment
            FindLastRowWithData = LastCell.Row
' Return the row of the last cell
        Else
            Set LastCell = Ws.Cells.FindPrevious(LastCell)
' Use the FindPrevious method to find the previous cell that has data and no comment
            FindLastRowWithData = LastCell.Row
' Return the row of the previous cell
        End If
    Else
' Return 0 if no cell has data
        FindLastRowWithData = 0
    End If
End Function

Function LastRowWithData(sht As Worksheet, Optional searchRange As Range) As Long

        sht.UsedRange.Calculate
'Avoid the used range bug.  Looking at it recalculates it, in most cases.

    If searchRange Is Nothing Then
        Set searchRange = sht.UsedRange
    End If
'Search a range or the whole worksheet

    With searchRange
        Dim LastCell As Range
        Set LastCell = .Find(What:="*", _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlFormulas, _
                        LookAt:=xlPart, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious)
 'Search using a wildcard
        If Not LastCell Is Nothing Then
                LastRow = LastCell.Row
            Else
                LastRow = 1
        End If
'If there is no data return 1 otherwise the last row number.
    End With
End Function

Public Function MyDocsPath() As String
    
Dim WshShell As Variant
    
    Set WshShell = CreateObject("WScript.Shell")
    MyDocsPath = WshShell.SpecialFolders("MyDocuments")
    
End Function

Public Function IsWkBkOpen(ByRef CrntWkbk As String) As Boolean

Dim WkBk As Workbook

    IsWkBkOpen = False
    
    For Each WkBk In Application.Workbooks
      If WkBk.Name = CrntWkbk Then
        IsWkBkOpen = True
      End If
    Next WkBk

End Function