Microsoft Office Forums - View Single Post - Sub vs Function to pass variable or parameter

koehlerc14 · 10-19-2019, 12:30 PM

Thank you Pike! That was a huge help and I think that I might finally be understanding the difference of Functions vs Subs as well as the parameters and arguments that they pass from one to another. I've revised my code to fit my goal of using the value to create a range of all data on a worksheet, as well as the optional arguments of last cell, last row, and last column in case I'd need to reference them separately.

Code:

Public Function Lastcell(Optional lcell As String, Optional nrow As Long, Optional lcol As String)
'Function finding last cell of data on worsheet
'Find Last row with data in any column
    Dim Lastrow As String
    nrow = ActiveSheet.UsedRange.Rows.Count
'Finding the Far Column in active worksheet with data in any row
'Must have headers/data in Row 1
    Dim Farcol As String
    Dim ncol As Long
    ncol = ActiveSheet.UsedRange.Columns.Count
    Farcol = Split(Cells(1, ncol).Address(True, False), "$")(0)
    lcol = Farcol ' populate lcol
'Combine Lastrow & Farcol
    Lastcell = lcol & nrow
    lcell = Lastcell
    lcell = lcol & nrow
End Function

Testing with MsgBox

Code:

Sub Display_LastCell()
    Dim lcell As String
    Dim nrow As Long
    Dim lcol As String
'Display the last cell with data using Function
    MsgBox "Last Col = " & Lastcell(lcell, nrow, lcol)
End Sub

Implementing into code, currently just changes fill color to yellow:

Code:

Sub Display_LastCell()
    Dim lcell As String
    Dim nrow As Long
    Dim lcol As String
    Call Lastcell(lcell, nrow, lcol)
    allcells = "$A$1:$" & lcol & "$" & nrow
    Set allcells = Range(allcells)
    With allcells.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Stop
End Sub