Sub vs Function to pass variable or parameter

koehlerc14 · #6 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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Pass Arguments to Function Through Ribbon	inagalaxyfarfarawry	Word VBA	11	03-25-2022 09:28 PM
Repeat Function By Changing One Variable	abbani	Excel	3	10-04-2017 12:41 AM
How can I pass a field as a parameter via a URL?	kelvinlewisuk1	Outlook	0	06-30-2016 08:30 AM
Can a function or sub be called using a variable name?	omahadivision	Excel Programming	18	12-05-2013 08:14 PM
Can pass .expression as a parameter?	tinfanide	PowerPoint	4	12-28-2012 06:14 PM