Sub vs Function to pass variable or parameter

[koehlerc14]

I've created a few lines of code that works great and I use to determine the last cell of data in excel. The code works great, but I'm having to write it every time I want to use in my code. Ideally, I would be able to call the function and return the parameters to use as values in subsequent code. On the active worksheet, I could call the code, use values within a range to create a table and for example filter out particular rows.



As an amateur coder, any advice on best practices would be greatly appreciated!

Sub Display_LastCell()
'Determine last cell with data
'Display the last cell with data

Dim nrow As Long

nrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ncol = ActiveSheet.UsedRange.Columns.Count
lcol = Split(Cells(1, ncol).Address(True, False), "$")
lcol = lcol(0)

MsgBox "Last Cell is " & lcol & nrow

End Sub



Private Function LastCell()
'Function finding the last cell in active worksheet with data
    
    nrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    ncol = ActiveSheet.UsedRange.Columns.Count
    lcol = Split(Cells(1, ncol).Address(True, False), "$")
    lcol = lcol(0)
    
End Function

[NoSparks]

This Ron de Bruin page may be of interest to you
Find last row, column or last cell

[koehlerc14]

Originally Posted by NoSparks

This Ron de Bruin page may be of interest to you
Find last row, column or last cell

Thanks for tip, I may look into the code to see if it os better, however right now the code itself is not the issue, it is just that I would like to be able to call the function and store as a variable to the macro running. For example I would activate Sheet 2 in code, call function then pass variable to the macro being run to be the end of a range and make it a table. Then activate Sheet 3 and do the same, by calling function which would then pass the new last cell values to be used in the macro.

[koehlerc14]

Thanks to tips and other threads to lead me to a solution, I'm not entirely sure why it works, but I'm still testing some things out. I'm able to reference it in the Sub. If anyone has any tips to declare parameter from a function to a sub I'd still appreciate it.

Function Row & Column

Public Function Lastrow() As Long
'Function finding the last row in active worksheet with data
    Dim nrow As Long
    nrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
Lastrow = nrow
nrow = Lastrow

End Function

Public Function Farcol() As String
'Function finding the Far Column in active worksheet with data
    ncol = ActiveSheet.UsedRange.Columns.Count
    lcol = Split(Cells(1, ncol).Address(True, False), "$")
    lcol = lcol(0)

Farcol = lcol

Sub Macro

End Function

Sub Display_LastCell()
'Display the last cell with data using Function

Call Lastrow
Call Farcol

MsgBox "Row = " & nrow & " Column = " & Farcol

End Sub

Next is to use as Cell Reference to the range...

[pike]

maybe his will be more explanatory
Function don't need to ne called and usually return values

Option Explicit
Dim nrow As Long
Public Function Lastrow() As Long
'Function finding the last row in active worksheet with data
    Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    nrow = Lastrow ' populate nrow
End Function

Public Function Farcol() As String
'Function finding the Far Column in active worksheet with data
    Dim ncol As Long
    ncol = ActiveSheet.UsedRange.Columns.Count
    Farcol = Split(Cells(1, ncol).Address(True, False), "$")(0)
End Function

Sub Display_LastCell()
'Display the last cell with data using Function
    MsgBox "Row = " & Lastrow() & " Column = " & Farcol()
End Sub

Sub Display_nrow()
'Display the last cell with data using nrow variable
    MsgBox "   nrow = " & nrow
End Sub

[koehlerc14]

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.

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

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:

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

[pike]

In the code editor window highlight "Function" and click F1 function or Help button to go to MS Development Centre. It has info on difference between sub and function.


Over use of optional but, consider how you pass variables form sub to function or sub to sub to function


Use functions where possible or appropriate to return a value

option Explicit

Public Function Lastcell(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 lngCol As Long
    If nrow = 0 Then nrow = ActiveSheet.UsedRange.Rows.Count
    'Finding the Far Column in active worksheet with data in any row
    'Must have headers/data in Row 1
    If lcol = vbNullString Then
        lngCol = ActiveSheet.UsedRange.Columns.Count
        lcol = Split(Cells(1, lngCol).Address(True, False), "$")(0)
    End If
    'Combine Lastrow & Farcol
    Lastcell = lcol & nrow
End Function

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

Sub Colour_usedCells_Yellow()
    Dim allcells As String
    allcells = "A1:" & Lastcell
    With Range(allcells).Interior
        .Color = 65535
    End With
End Sub

Sub testone()
     Colour_Selected_Cells_or_Clear 10, "J", vbBlue
End Sub

Sub testtwo()
     Colour_Selected_Cells_or_Clear
End Sub

Sub Colour_Selected_Cells_or_Clear(Optional cRow As Long, Optional cColumn As String, Optional cColour As String)
    Dim allcells As String
    allcells = "A1:" & Lastcell(cRow, cColumn)
    With Range(allcells).Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
        If Not cColour = vbNullString Then .Color = cColour
    End With
End Sub