Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-18-2019, 03:34 PM
koehlerc14 koehlerc14 is offline Sub vs Function to pass variable or parameter Windows 10 Sub vs Function to pass variable or parameter Office 2016
Novice
Sub vs Function to pass variable or parameter
 
Join Date: Feb 2019
Posts: 6
koehlerc14 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 10-18-2019, 04:18 PM
NoSparks NoSparks is offline Sub vs Function to pass variable or parameter Windows 7 64bit Sub vs Function to pass variable or parameter Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 759
NoSparks has a spectacular aura aboutNoSparks has a spectacular aura about
Default

This Ron de Bruin page may be of interest to you
Find last row, column or last cell
Reply With Quote
  #3  
Old 10-18-2019, 04:40 PM
koehlerc14 koehlerc14 is offline Sub vs Function to pass variable or parameter Windows 10 Sub vs Function to pass variable or parameter Office 2016
Novice
Sub vs Function to pass variable or parameter
 
Join Date: Feb 2019
Posts: 6
koehlerc14 is on a distinguished road
Default

Quote:
Originally Posted by NoSparks View Post
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.
Reply With Quote
  #4  
Old 10-18-2019, 06:55 PM
koehlerc14 koehlerc14 is offline Sub vs Function to pass variable or parameter Windows 10 Sub vs Function to pass variable or parameter Office 2016
Novice
Sub vs Function to pass variable or parameter
 
Join Date: Feb 2019
Posts: 6
koehlerc14 is on a distinguished road
Default

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
Sub Macro
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
Next is to use as Cell Reference to the range...
Reply With Quote
  #5  
Old 10-18-2019, 08:09 PM
pike pike is offline Sub vs Function to pass variable or parameter Windows 7 64bit Sub vs Function to pass variable or parameter Office 2010
Novice
 
Join Date: Jul 2019
Location: Alstonville
Posts: 4
pike is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 10-19-2019, 12:30 PM
koehlerc14 koehlerc14 is offline Sub vs Function to pass variable or parameter Windows 10 Sub vs Function to pass variable or parameter Office 2016
Novice
Sub vs Function to pass variable or parameter
 
Join Date: Feb 2019
Posts: 6
koehlerc14 is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 10-19-2019, 04:33 PM
pike pike is offline Sub vs Function to pass variable or parameter Windows 7 64bit Sub vs Function to pass variable or parameter Office 2010
Novice
 
Join Date: Jul 2019
Location: Alstonville
Posts: 4
pike is on a distinguished road
Default

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
Reply With Quote
Reply

Tags
functions, parameters

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
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
Pass Arguments to Function Through Ribbon inagalaxyfarfarawry Word VBA 8 03-15-2015 04:01 PM
Sub vs Function to pass variable or parameter Can a function or sub be called using a variable name? omahadivision Excel Programming 18 12-05-2013 08:14 PM
Sub vs Function to pass variable or parameter Can pass .expression as a parameter? tinfanide PowerPoint 4 12-28-2012 06:14 PM


All times are GMT -7. The time now is 11:35 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft