#1
|
|||
|
|||
Sub vs Function to pass variable or parameter
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! Code:
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 |
#2
|
|||
|
|||
This Ron de Bruin page may be of interest to you
Find last row, column or last cell |
#3
|
|||
|
|||
Quote:
|
#4
|
|||
|
|||
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 Code:
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 Code:
End Function Sub Display_LastCell() 'Display the last cell with data using Function Call Lastrow Call Farcol MsgBox "Row = " & nrow & " Column = " & Farcol End Sub |
#5
|
|||
|
|||
maybe his will be more explanatory
Function don't need to ne called and usually return values Code:
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 |
#6
|
|||
|
|||
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 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 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 |
#7
|
|||
|
|||
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 |
Tags |
functions, parameters |
|
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 |