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

pike · 10-19-2019, 04:33 PM

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

Code:

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

Code:

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

Code:

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

Code:

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

Code:

Sub testtwo()
     Colour_Selected_Cells_or_Clear
End Sub

Code:

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